Linköping University | Department of Computer Science Bachelor thesis, 16 ECTS | Datateknik 2017 | LIU-IDA/LITH-EX-G--17/008--SE An Approach to Achieve DBMS Vendor Independence for Ides AB’s Platform NiklasBlomqvist PhilipJohansson Examiner: OlafHartig Externalsupervisor: KristianSjöström Linköpingsuniversitet SE–58183Linköping +4613281000,www.liu.se Upphovsrätt Detta dokument hålls tillgängligt på Internet – eller dess framtida ersättare – under 25 år frånpubliceringsdatumunderförutsättningattingaextraordinäraomständigheteruppstår. Tillgångtilldokumentetinnebärtillståndförvarochenattläsa,laddaner,skrivautenstaka kopiorförenskiltbrukochattanvändadetoförändratförickekommersiellforskningochför undervisning. Överföringavupphovsrättenvidensenaretidpunktkaninteupphävadetta tillstånd. Allannananvändningavdokumentetkräverupphovsmannensmedgivande. För attgaranteraäktheten,säkerhetenochtillgänglighetenfinnslösningaravtekniskochadmin- istrativ art. Upphovsmannens ideella rätt innefattar rätt att bli nämnd som upphovsman i denomfattningsomgodsedkrävervidanvändningavdokumentetpåovanbeskrivnasätt samt skydd mot att dokumentet ändras eller presenteras i sådan form eller i sådant sam- manhang som är kränkande för upphovsmannenslitterära eller konstnärliga anseende eller egenart. För ytterligare information om Linköping University Electronic Press se förlagets hemsidahttp://www.ep.liu.se/. Copyright The publishers will keep this document online on the Internet – or its possible replacement – for a period of 25 years starting from the date of publication barring exceptional circum- stances. Theonlineavailabilityofthedocumentimpliespermanentpermissionforanyoneto read,todownload,ortoprintoutsinglecopiesforhis/hersownuseandtouseitunchanged for non-commercial research and educational purpose. Subsequent transfers of copyright cannotrevokethispermission. Allotherusesofthedocumentareconditionaluponthecon- sentofthecopyrightowner. Thepublisherhastakentechnicalandadministrativemeasures to assure authenticity, security and accessibility. According to intellectual property law the authorhastherighttobementionedwhenhis/herworkisaccessedasdescribedaboveand to be protected against infringement. For additional information about the Linköping Uni- versity Electronic Press and its procedures for publication and for assurance of document integrity,pleaserefertoitswwwhomepage: http://www.ep.liu.se/. NiklasBlomqvist (cid:13)c PhilipJohansson Abstract Softwarethatisdevelopedwithfewintegrationcapabilitiestodifferentuserinterfaces ordatabasevendorsmightlosemarketshareinthelongrun. Tostaycompetitive,compa- niesthatfindthemselvesinsituationslikethesemightneedtolookatoptionstoincrease their alternatives. This thesis aims to present and evaluate how Ides AB could achieve vendorindependenceasitrelatestodatabaseintegration. Theproposedsolutionisbased onpre-existingcodefromanexistingproductandthusincludestheoryaboutthemethods and details how one can read, understand and analyse code. The outcome is presented withcodeexamplestogivethereaderaclearandconciseunderstanding.Intheevaluation phase,wetakeotherrelatedworkintoconsiderationasitrelatestoourthesisfocus. The proposedapproachpresentedconsistsofaclasstorepresentdifferentdatabasevendors. Italsoconsistsofabstractfunctionshandlingtheinteractionbetweendifferentdatabases. Whichdatabasetheclassinteractswithisdeterminedbytheconnectionestablished. The approachalsoincludeswhatispossibletomakedatabaseagnosticverifiedbyanevalua- tion. Acknowledgments We would like to thank Ides AB for giving us the opportunity to do this thesis. Thanks to everyone at Ides for behind very helpful and inviting. Special thanks go to Kristian Sjöström who has been our supervisor at Ides. Also thanks to Olaf Hartig who has been our examinator and supervisor. Olaf provided us with feedback and guidelines to make this thesis possible. iv Contents Abstract iii Acknowledgments iv Contents v ListofFigures vii 1 Introduction 1 1.1 Motivation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 Aim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 Researchquestions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.4 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.5 Delimitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 2 Theory 3 2.1 Reading,understanding,andanalyzingcode . . . . . . . . . . . . . . . . . . . . 3 2.2 Softwaredevelopmentmodels . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.3 Maintainability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.4 Refactoringcode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.5 Databasemanagementsystems . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.6 Layerarchitecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.7 Object-relationalmapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.8 Softwaretesting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3 Method 16 3.1 Pre-study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.2 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.3 Softwareverification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 4 Results 19 4.1 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 4.2 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 4.3 Analysisofthelayerarchitecture . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 5 Evaluation 30 5.1 Storedprocedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 5.2 Remotecomparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 5.3 Localcomparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 6 Discussion 42 6.1 Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 6.2 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 v 6.3 Sourcecriticism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 7 Conclusion 44 Bibliography 46 List of Figures 2.1 Codeexampleofrefactoringwiththeextractmethod. . . . . . . . . . . . . . . . . . . 8 2.2 Codeexampleofrefactoringwiththeinlinemethod. . . . . . . . . . . . . . . . . . . 9 2.3 Architectureofadatabase. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.4 Exampleofatableinadatabase. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.5 Relationsinatable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.6 Resultfromquery.. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.7 Three-layerarchitecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.8 ORMarchitecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 4.1 ListoverIDbConnectioninterfacepropertiesandmethods. . . . . . . . . . . . . . . 20 4.2 ExampleofparametersintheIDbCommandinterface. . . . . . . . . . . . . . . . . . 20 4.3 CalltothedatabaseusingOracle.DataAccess.Client. . . . . . . . . . . . . . . . . . . . 21 4.4 Enumerationrepresentingdatabasetypes. Couldbeextendedfurther. . . . . . . . 22 4.5 Theconstructoroftheimplementedclass. . . . . . . . . . . . . . . . . . . . . . . . . 22 4.6 TheoverwrittenCommandTextpropertyfortheIDbCommandinterface. . . . . . . . 23 4.7 ThecharacterdifferentDBMSsuseforparameters. . . . . . . . . . . . . . . . . . . . 23 4.8 AccessingOracle’sparametercharacter. . . . . . . . . . . . . . . . . . . . . . . . . . 23 4.9 SnippetfromtheConvertStatementfunctionthattranslatetheparametercharacter. 23 4.10 ExampleofwritingastandardSQLstatement. . . . . . . . . . . . . . . . . . . . . . 24 4.11 StatementwiththeuniqueOraclefunctionsdecodeanddbms_lob.get_length.. . . . . 24 4.12 CommandwithseparatestatementsfordifferentDBMSs. . . . . . . . . . . . . . . . 24 4.13 Listoverfunctionstobetranslated. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 4.14 Implementationafterimplementingparsefunctions.. . . . . . . . . . . . . . . . . . 26 4.15 FunctionheaderofhelperfunctionGetStatement . . . . . . . . . . . . . . . . . . . . 26 4.16 StatementsupportedbymultipleDBMSs. . . . . . . . . . . . . . . . . . . . . . . . . 26 4.17 StatementexpectedtoexecuteonallDBMSs. . . . . . . . . . . . . . . . . . . . . . . 26 4.18 Functionforaddingaparametertoastatement. . . . . . . . . . . . . . . . . . . . . 27 4.19 FunctionsforreadingbinarydatainOracleandSQLServer. . . . . . . . . . . . . . 27 4.20 Functionforreadingbinarydata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 4.21 Functionforreadingdata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 4.22 Calltothedatabaseusingthenewimplementation. . . . . . . . . . . . . . . . . . . 28 4.23 Exampleofastatementintheuserinterface . . . . . . . . . . . . . . . . . . . . . . . 29 4.24 Exampleofapplicationlogicintheuserinterface . . . . . . . . . . . . . . . . . . . . 29 5.1 Snippetfromastoredprocedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 5.2 SnippetfromastoredprocedurewritteninC#. . . . . . . . . . . . . . . . . . . . . . 31 5.3 Chartofstoredprocedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 5.4 Chartofthememoryusageevaluationonaremoteserver . . . . . . . . . . . . . . 33 5.5 ChartofCPUevaluationonaremoteserver . . . . . . . . . . . . . . . . . . . . . . . 34 5.6 Chartoftheexecutiontimeevaluationofthestatement1onaremoteserver . . . . 35 5.7 Chartoftheexecutiontimeevaluationofthestatement2onaremoteserver . . . . 35 5.8 Chartoftheexecutiontimeevaluationofthestatement3onaremoteserver . . . . 35 vii 5.9 Chartoftheexecutiontimeevaluationofthestatement4onaremoteserver . . . . 35 5.10 Chartoftheexecutiontimeevaluationofthestatement5onaremoteserver . . . . 36 5.11 Chartofthememoryusageevaluationonalocalserver . . . . . . . . . . . . . . . . 37 5.12 ChartofCPUevaluationonalocalserver . . . . . . . . . . . . . . . . . . . . . . . . 38 5.13 Chartoftheexecutiontimeevaluationofthestatement1onalocalserver . . . . . 39 5.14 Chartoftheexecutiontimeevaluationofthestatement2onalocalserver . . . . . 39 5.15 Chartoftheexecutiontimeevaluationofthestatement3onalocalserver . . . . . 40 5.16 Chartoftheexecutiontimeevaluationofthestatement4onalocalserver . . . . . 41 5.17 Chartoftheexecutiontimeevaluationofthestatement5onalocalserver . . . . . 41 1 Introduction 1.1 Motivation The ability to choose and customize your software has become something that customers take for granted. Companies that have developed software which was originally designed to only have narrow options are now in a critical phase where they are losing potential customers. Howtheprocesstostoredatafromthesoftwareshouldbedoneisoftenseenas oneofthepartsthatthecustomerwantstochoose. Databases are one way of handling data. Developers of software are facing a choice in where they must choose which database management system (DBMS) to interact with. But everycustomerhastheirownindividualsituationwithauniquesolutionthatpossiblycannot beadaptedtoanothercustomer. Keepingupwiththemarketandbeingabletodeliverwhat thecustomersdemandisanessentialkeyindevelopingsoftware. Onewayofextendingthe flexibilityistomakethesoftwaredatabase-agnostic1. 1.2 Aim Thepurposeofthisthesisprojectistoinvestigateandtoevaluatethemostappropriateway tomakeIdesAB’ssoftwareplatformdatabase-agnosticwithoutsacrificingperformance. 1.3 Researchquestions 1. In what way, could Ides become independent of database provider without affecting thecurrentfunctionality? • How could this be implemented without affecting the performance of execution time,CPUormemorybymorethan10%? 2. How should Ides keep working with their current layer architecture, with respect to multipledatabaseproviders? 1Inaninformationtechnologycontext,agnosticreferstosomethingthatisgeneralizedsothatitiscompatible amongvarioussystems[3]. 1 1.4. Background 1.4 Background IdesABisdevelopingaplatformforversioncontrolofengineeringdata,CADintegrations, applicationsandMicrosoftofficedocuments. ThenameoftheproductisMasterConceptand more about it can be read at http://www.imaster.se/en/. Today, Ides AB can exclu- sively offer their platform to run with Oracles database management system. To increase theirflexibilitytocustomers,Ideswouldliketomaketheirsoftwareplatformfunctionalwith different database managers. The reason is that they have lost procurements, and thereby marketsharesbecausetheycannotofferanotherDBMS. 1.5 Delimitations • Idesisdevelopingtheirsoftwareina.NETenvironmentwiththelanguageC#,therefore thisthesiswillfocusonalternativesrelevantforthe.NETFramework. • OurpartonMasterConceptwillbefocusingondocuments. 2