數(shù)據(jù)庫-研究生課件：第二部分 數(shù)據(jù)庫系統(tǒng)實現(xiàn)技術(shù)

第二部分?jǐn)?shù)據(jù)庫系統(tǒng)實現(xiàn)技術(shù)主要內(nèi)容第3章存儲與文件結(jié)構(gòu)(簡介+自學(xué))第4章索引與散列(簡介+自學(xué))第5章查詢處理第6章查詢優(yōu)化第7章事務(wù)第8章并發(fā)控制第9章恢復(fù)系統(tǒng)開源代碼數(shù)據(jù)庫管理系統(tǒng)Derby/Cloudscape分析主要任務(wù)有選擇的深入了解并掌握部分DBMS核心模塊的實現(xiàn)細(xì)節(jié)通過對開源代碼數(shù)據(jù)庫管理系統(tǒng)Derby的分析,學(xué)習(xí)實現(xiàn)DBMS核心的相關(guān)技術(shù)原理開源代碼分析方法和技巧基礎(chǔ)收集整理相關(guān)資料對相關(guān)基本原理的深刻理解和掌握原則先總體(宏觀)后局部(微觀)先抽象(接口)后細(xì)節(jié)(實現(xiàn))步驟從系統(tǒng)架構(gòu)分析入手,逐步深入應(yīng)用軟件分析工具動手實驗和閱讀代碼很重要ArchitectureofaDBMSDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseArchitectureofaDBMSDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSFiles&AccessMethodsBufferManagerDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSFiles&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlFiles&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)AccessControlManagerArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)IntegrityManagerAccessControlManagerArchitectureofaDBMS(9/30)ArchitectureofaDBMSArchitectureofaDBMSInterfaceSQLCommandsDBMSArchitectureIntroducingApacheDerbyAnOpenSourscePureJavaRelationalDatabaseEngineWhat

isDerby?OpenSourceDatabaseTechnologyPureJavaEasytouseSmallfootprintStandardsbasedCompleterelationaldatabaseengineSecureSub-projectofApacheDBProject/derbyDerbyHistory1996:Cloudscapefounded–Oakland,CA1997:JDBMS1.0ReleasedApr1999:Cloudscape2.0Dec1999:AcquiredbyInformixSoftwareJune2001:Cloudscape4.0ReleasedJuly2001:AcquiredbyIBMDec2001:IBMCloudscape5.0Released2003:IBMCloudscape5.1,5.1FP1&FP2August2004:IBMdonatedCloudscapetoApacheJuly2005:DerbygraduatedfromApacheIncubatorDerby@ApacheSoftwareFoundationIBMcontributedtheCloudscapecodetoApacheSoftwareFoundationasDerbyReleasedundertheApachev2.0LicenseCommunityisgrowing,openinvitationtocontributetowardsthesuccessoftheprojectandcommunityParticipateasauser,contributor,orboth.ContributewithSourcecodeDocumentation(papers,presentationsetc.)Suggestions,ideas,bugreportsorjusthelpothersoutonthemaillistsMay16200521ApacheDerbyvs.CloudscapeDerby:DatabaseEngine+EmbeddedJDBCDriver+NetworkServer+Tools(ij,dblook,import/export)+Usermanuals(web-accessible)Clouldscape:Derby+TechnicalSupport+DB2UniversalJDBCDriver(JCC)+ODBC+DB2Plug-inforEclipse(databasebrowsingTool+Manuals(eclipse-basedandPDF)FeaturesofDerbyPureJavaEmbeddeddatabaseSmallfootprintStandardsbasedCompleterelationaldatabaseengineEasytouseanddeployPureJavaDatabasecodewritteninJavaWriteOnceRunAnywhereRequiresaJ2SE1.3,1.4or1.5virtualmachineAnyhardware,anyoperatingsystem,anyvendorSinglebinarydoesruneverywhereLinux,Windows,MacOs,AIX,Solaris,Z/OS,AS400,OS/390,…Databaseon-diskformatisplatformindependentSmallFootprintEnginejarfileisaround2MbOptionalJarfilesNetworkserver~150kTools~200kRuntimememoryuseDependentonapplication,datacachingetc.CanrunwhenJavaheapmemoryrestrictedto4MbHaveruninmachineswithonly16MbphysicalmemoryEmbeddedDatabaseDatabaseenginebecomesintegralpartoftheJavaapplicationNoadditionalprocess?Application'sJDBCcallsnowjustresultinmethodcallswithinsameJVMJustaJarfiletotheapplicationDatabaseinvisibletoenduserofapplicationEmbedded

DerbywithApplicationDatabaseonlyaccessiblefromsingleJVMJava/JDBConlyNonetworkconnectivityTypicallyissingleapplicationperJVM(butcouldbemultiple)Embedded

DerbywithApplicationEasytouseFastZeroadministrationEmbeddedinApplication

ServerDerbyEmbeddedPros:Invisibleatthedeployedsite–simplystartyourapplicationVerysimpletodeployandmanageStillhavemulti-threaded,multi-connectioncapabilityPerformanceisverygoodCons:Nonetworkingcapability(butnosecurityissues)Only1JVMcanaccessadatabaseatanygiventimeTraditionalClient/ServerJavaVirtualMachineDerbyengineNetwork

ServerDRDAJDBCCLI/ODBCMultipleClientapplications

-RemoteorlocalPHPontopofODBCClientApplications

ClientsprovidedbyIBMDMDatabase(s)ondiskDerby’sClient/ServerModeAccessfromasingleVMinembeddedmodecanbearestrictionNetworkServerallowsDerbytoactasatraditionalclientserverdatabaseIndustrystandardDRDAoverTCP/IPNetworkServeritselfispureJavaUsesembeddedJDBCdriveragainstDerbyIt’saJavaDRDAtoJDBCconverterNetworkServerClientsJDBCusingIBM’sDB2UniversalJDBCClientDifferentdriverandJDBCURLtoembeddedDataSourceandConnectionPoolingDataSourceODBC/CLIusingIBM’sDB2’sUniversalODBC/CLIclientEnablesPHP/Perl/.NETUniversaldriversenabledbyuseofDRDAJavaVirtualMachineDerbyengineNetwork

ServerDRDAJDBCCLI/ODBCEspeciallyusefuldeveloping

&debuggingembeddeddatabaseusage

PHPontopofODBCClientApplications

ClientsprovidedbyIBMDMDatabase(s)ondiskEmbeddedNetworkServerApplicationEmbeddedNetworkServerAddsontoembeddedenginetoprovideaccesstodatabasefromoutsidetheapplication’sVM(fromlocalorremotemachine)AllowsdeveloperstoworkondatabasewhilestandaloneapplicationisrunningAllowsreportingcapabilitytobeaddedontoastandaloneapplicationEnabledbypropertysettingandadditionaljarfile,nocodechangestoapplicationderby.drda.startNetworkServer=trueODBC/DerbyenvironmentInstallDB2Run-TimeClientStartDerbyNetworkServer(Derbymustbeinstalled!)ConfigureDerbyDBasODBCDataSourceCatalognodeanddatabaseviaDB2CommandLineProcessor,CLPCatalogdatabaseasODBCdatasourcedb2=>catalogtcpipnodeDNSremotelocalhostserver1527--DerbyNetworkServerdb2=>catalogdbDDBatnodeDNSauthenticationserverdb2=>catalogsystemodbcdatasourceDDBWriteanODBCApplicationtoaccesstheDerbydatabase,DDBCompleteRelationalEngineSQLTables,indexes,views,triggers,procedures,functions,temptablesforeignkeysandconstraintsjoins,costbasedoptimizerMulti-user,transactions,isolationlevels,deadlockdetection,crashrecovery,backup&restoreDatacaching,statementcaching,writeaheadlogging,groupcommitMultipledatabasespersystemStandardstoAllowMigrationDerbydoesperformwellinitsownrightLackingfeaturesofanenterpriseDBDeveloponDerby,deployonenterpriseDBInitialdeployonDerby,migratetoenterpriseDBasneededStandardsBasedSQLSQL92,SQL99,SQL2003,SQL/XML,…JavaJ2SE1.3,1.4,1.5J2ME/OSGiJDBC2.0&3.0JSR169J2EE–JDBCpassedCTSforJ2EE1.4&1.3DRDAV3OpenGroup(/)DerbyToolsij–SQLscriptingtoolJDBCneutral,canbeusedagainstotherJDBCdriversdblook–schemaextractiontoolforDerbysysinfo–DerbyversioninformationOutputusefulforbugrepoRTINginJiraDerbyIntegrationDerbyisreportedtoworkwithActiveMQ,Cocoon,C-JDBC,Daffodil,db2db,DB2Everyplace,DOTS,DroneIRCBot,Eclipse,Geronimo,Hibernate,iBATIS,IBMDB2JDBCUniversalDriver,IKVM.NET,iSQL-Viewer,SunJ2EESDK,JBoss,JOnAS,JPOXJDO,Jython,Maven,RedHatApplicationServer,RIFE,SQuirreLSQL,Sync4j,TomcatEasytoUseandDeployDerbyiseasytouseDesignedforJavadevelopers(standardJDBCcommands)Simplyloadthedriverandconnecttoadatabaseandyou’reinbusinessDerbyiseasytodeploySimplydeploythejarfile(s)andsettheusersclasspathEmbeddedrequiresnostartingandstoppingofaserverDesignedtobezeroadmin:noupdatestatistics,spacereclamation,logmanagement,etc.requiredEverythingcanbedoneprogrammaticallyincludingdatabasebackups,settingproperties,etc.DerbyArchitectureModuleArchitectureModuleSetofusablefunctionalityWelldefinedAPI–“protocol”ProtocolseparatedfromimplementationTypicallydeclaredassetofJavainterfacesIdentifiedbysingleJavainterface,“factoryclass”?.apache.derby.iapi.services.locks.LockFactoryMayreferenceothermoduleprotocolsZeroormoreimplementationsinarunningsystemImplementationcanimplementcontrolinterfacesto:?Defineadditionalboot&shutdownactions?DefinesuitabilityforrequestedfunctionalityServiceCollectionofco-operatingmodulesprovidingacompletesetoffunctionalitySingleprimarymoduledefiningexternalAPIPersistent?Boot-upstateinsinglepertiesfile,includingtherequiredprimarymoduleidentification(astheprotocol,nottheimplementation)Non-persistent?Purelyrun-timedefinitionModulesalwaysbootedthroughmonitorMonitorManagesDerbysystemBoots&shutdownServicesFindspertiesfilebaseduponservicebootMapsrequestsforamoduleprotocoltoanimplementationBasedupon:?Virtualmachineenvironment(J2ME/J2SE1.3/1.4)?Availableclasses(e.g.JCEencryptionclasses)?SuitabilityforcurrentserviceEnsuressystemisnotgarbagecollectedawayDatabaseEngineSystemSingleActiveThreadper

SessionCache/Lock

ManagersCacheManagercachesobjectsthatimplementaCacheableinterfaceHandlesaging,pruning,etc.Cachespages,statements,stringtranslations,openfiles,dictionaryobjectsLockManagerlocksobjectsthatimplementaLockableinterfaceLockcompatibilitydefinedbyLockable,notmanagerCentralizes“hard”thread-safeissuesSQLCompilationPreparedStatementps=conn.prepareStatement(“SELECT*FROMTWHEREID=?”);1)Lookupincacheusingexacttextmatch(skipto5ifplanfoundincache)2)ParseusingJavaCCgeneratedparser3)Bindtodictionary,gettypes,etc.4)Generatecodeforplan5)CreateinstanceofplanParsePhaseTreeofQueryNodescreatedManyNodes,oneperoperationtype?FromBaseTable,MethodCallNode,DistinctNode,CurrenUserNode,...?BulkofcodefootprintisSQLcompilerNoexecutioncodeinQueryNodes,leadstosimilarsetofexecutionResultSetsDuplicatecheckingofstateinQueryGeneratePhaseGenerateJavabytecodedirectly,intoin-memorybytearrayLoadwithspecialClassLoaderthatloadsfromthebytearraySingleClassLoaderpergeneratedclassAllowsstatementstobeagedoutindependentlyGeneratedclassextendsaninternalclassBaseActivationwhichprovidessupportmethods,commonfunctionalityActivation–InstanceofplanInstanceofgeneratedclasscalledActivationHoldsqueryspecificstate,parameters,etc.ConnectedindirectlytoJDBCPreparedStatementthroughholder/wrapperclassthatimplementsActivationinterfaceHolderallowscompiledplantochangewithoutknowledgeofapplication,transparenttoPreparedStatementActivation–ExecutionPreparedStatement.execute()methodsCreatestreeofinternalResultSetobjectsthatmaptoSQLoperations?ScanResultSet?SortResultSet?IndexScanResultSetGeneratedcodemaingluecodeExpressionsaregeneratedasmethodsingeneratedclassStatementResultsBenefitsofGeneratedCodeNoneedtohaveDerbyspecificinterpreterwritteninJava,justuseJVMGeneratedcodewillgetJIT'edandthusgaintheperformancebenefitsTightintegrationwithJavacallsfromSQL,e.g.SQLfunctionswritteninJava.Nouseof(slow)reflection,justcompilemethodc