An Approach to Achieve DBMS Vendor Independence for Ides AB's Platform

55 Pages·2017·0.31 MB·English

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
[phone],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