翻译文献-数据库结构

外文原文DATABASECONFIGURATIONSQLITEDOESNTHAVEACONFIGURATIONFILERATHER,ALLOFITSCONFIGURATIONPARAMETERSAREIMPLEMENTEDUSINGPRAGMASPRAGMASWORKINDIFFERENTWAYSSOMEARELIKEVARIABLESOTHERSARELIKECOMMANDSTHEYCOVERMANYASPECTSOFTHEDATABASE,SUCHASRUNTIMEINFORMATION,DATABASESCHEMA,VERSIONING,FILEFORMAT,MEMORYUSE,ANDDEBUGGINGSOMEPRAGMASAREREADANDSETLIKEVARIABLES,WHILEOTHERSREQUIREARGUMENTSANDARECALLEDLIKEFUNCTIONSMANYPRAGMASHAVEBOTHTEMPORARYANDPERMANENTFORMSTEMPORARYFORMSAFFECTONLYTHECURRENTSESSIONFORTHEDURATIONOFITSLIFETIMETHEPERMANENTFORMSARESTOREDINTHEDATABASEANDAFFECTEVERYSESSIONTHECACHESIZEISONESUCHEXAMPLETHISSECTIONCOVERSTHEMOSTCOMMONLYUSEDPRAGMASACOMPLETELISTOFALLSQLITEPRAGMASCANBEFOUNDINAPPENDIXATHECONNECTIONCACHESIZETHECACHESIZEPRAGMASCONTROLHOWMANYDATABASEPAGESASESSIONCANHOLDINMEMORYTOSETTHEDEFAULTCACHESIZEFORTHECURRENTSESSION,YOUUSETHECACHE_SIZEPRAGMASQLITEPRAGMACACHE_SIZECACHE_SIZE2000SQLITEPRAGMACACHE_SIZE10000SQLITEPRAGMACACHE_SIZECACHE_SIZE10000YOUCANPERMANENTLYSETTHECACHESIZEFORALLSESSIONSUSINGTHEDEFAULT_CACHE_SIZEPRAGMATHISSETTINGISSTOREDINTHEDATABASETHISWILLONLYTAKEEFFECTFORSESSIONSCREATEDAFTERTHECHANGE,NOTFORCURRENTLYACTIVESESSIONSONEOFTHEUSESFORTHECACHEISINSTORINGPENDINGCHANGESWHENASESSIONISINARESERVEDSTATEITHASARESERVEDLOCK,ASDESCRIBEDEARLIERINTHESECTION“TRANSACTIONS”IFTHESESSIONFILLSUPTHECACHE,ITWILLNOTBEABLETOCONTINUEFURTHERMODIFICATIONSUNTILITGETSANEXCLUSIVELOCK,WHICHMEANSTHATITMAYHAVETOFIRSTWAITFORREADERSTOCLEARIFYOUORYOURPROGRAMSPERFORMMANYUPDATESORDELETESONADATABASETHATISBEINGUSEDBYMANYOTHERSESSIONS,ITMAYHELPYOUTOINCREASETHECACHESIZETHELARGERTHECACHESIZE,THEMOREMODIFICATIONSASESSIONCANCACHECHANGEBEFOREITHASTOGETANEXCLUSIVELOCKTHISNOTONLYALLOWSASESSIONTOGETMOREWORKDONEBEFOREHAVINGTOWAIT,ITALSOCUTSDOWNONTHETIMETHEEXCLUSIVELOCKSNEEDSTOBEHELD,ASALLTHEWORKISDONEUPFRONTINTHISCASE,THEEXCLUSIVELOCKONLYNEEDSTOBEHELDLONGENOUGHTOFLUSHTHECHANGESINTHECACHETODISKSOMETIPSFORTUNINGTHECACHESIZEARECOVEREDINCHAPTER5GETTINGDATABASEINFORMATIONYOUCANOBTAINDATABASEINFORMATIONUSINGTHEDATABASESCHEMAPRAGMAS,DEFINEDASFOLLOWSDATABASE_LISTLISTSINFORMATIONABOUTALLATTACHEDDATABASESINDEX_INFOLISTSINFORMATIONABOUTTHECOLUMNSWITHINANINDEXITTAKESANINDEXNAMEASANARGUMENTINDEX_LISTLISTSINFORMATIONABOUTTHEINDEXESINATABLEITTAKESATABLENAMEASANARGUMENTTABLE_INFOLISTSINFORMATIONABOUTALLCOLUMNSINATABLETHEFOLLOWINGILLUSTRATESSOMEINFORMATIONPROVIDEDBYTHESEPRAGMASSQLITEPRAGMADATABASE_LISTSEQNAMEFILE0MAIN/TMP/FOODSDB2DB2/TMP/DBSQLITECREATEINDEXFOODS_NAME_TYPE_IDXONFOODSNAME,TYPE_IDSQLITEPRAGMAINDEX_INFOFOODS_NAME_TYPE_IDXSEQNCIDNAME02NAME11TYPE_IDSQLITEPRAGMAINDEX_LISTFOODSSEQNAMEUNIQUE0FOODS_NAME_TYPE_IDX0SQLITEPRAGMATABLE_INFOFOODSCIDNAMETYPENOTNDFLTPK0IDINTEGER011TYPE_IDINTEGER002NAMETEXT00SYNCHRONOUSWRITESNORMALLY,SQLITECOMMITSALLCHANGESTODISKATCRITICALMOMENTSTOENSURETRANSACTIONDURABILITYHOWEVER,ITISPOSSIBLETOTURNTHISOFFFORPERFORMANCEGAINSYOUDOTHISWITHTHESYNCHRONOUSPRAGMATHEREARETHREESETTINGSFULL,NORMAL,ANDOFFTHEYAREDEFINEDASFOLLOWSFULLSQLITEWILLPAUSEATCRITICALMOMENTSTOMAKESURETHATDATAHASACTUALLYBEENWRITTENTOTHEDISKSURFACEBEFORECONTINUINGTHISENSURESTHATIFTHEOPERATINGSYSTEMCRASHESORIFTHEREISAPOWERFAILURE,THEDATABASEWILLBEUNCORRUPTEDAFTERREBOOTINGFULLSYNCHRONOUSISVERYSAFE,BUTITISALSOSLOWNORMALSQLITEWILLSTILLPAUSEATTHEMOSTCRITICALMOMENTS,BUTLESSOFTENTHANINFULLMODETHEREISAVERYSMALLTHOUGHNONZEROCHANCETHATAPOWERFAILUREATJUSTTHEWRONGTIMECOULDCORRUPTTHEDATABASEINNORMALMODEBUTINPRACTICE,YOUAREMORELIKELYTOSUFFERACATASTROPHICDISKFAILUREORSOMEOTHERUNRECOVERABLEHARDWAREFAULTOFFSQLITECONTINUESOPERATIONWITHOUTPAUSINGASSOONASITHASHANDEDDATAOFFTOTHEOPERATINGSYSTEMTHISCANSPEEDUPSOMEOPERATIONSASMUCHAS50ORMORETIMESIFTHEAPPLICATIONRUNNINGSQLITECRASHES,THEDATAWILLBESAFEHOWEVER,IFTHEOPERATINGSYSTEMCRASHESORTHECOMPUTERLOSESPOWER,THEDATABASEMAYBECORRUPTEDTHEREISNOPERSISTENTFORMOFTHESYNCHRONOUSPRAGMACHAPTER5EXPLAINSTHISSETTINGSCRUCIALROLEINTRANSACTIONDURABILITYANDHOWITWORKSTEMPORARYSTORAGETEMPORARYSTORAGEISWHERESQLITEKEEPSTRANSIENTDATASUCHASTEMPORARYTABLES,INDEXES,ANDOTHEROBJECTSBYDEFAULT,SQLITEUSESACOMPILEDINLOCATION,WHICHVARIESBETWEENPLATFORMSTHEREARETWOPRAGMASTHATGOVERNTEMPORARYSTORAGETEMP_STOREANDTEMP_STORE_DIRECTORYTHEFIRSTPRAGMADETERMINESWHETHERSQLITEUSESMEMORYORDISKFORTEMPORARYSTORAGETHEREAREACTUALLYTHREEPOSSIBLEVALUESDEFAULT,FILE,ORMEMORYDEFAULTUSESTHECOMPILEDINDEFAULT,FILEUSESANOPERATINGSYSTEMFILE,ANDMEMORYUSESRAMIFFILEISSETASTHESTORAGEMEDIUM,THENTHESECONDPRAGMA,TEMP_STORE_DIRECTORY,CANBEUSEDTOSETTHEDIRECTORYINWHICHTHETEMPORARYSTORAGEFILEISPLACEDPAGESIZE,ENCODING,ANDAUTOVACUUMTHEDATABASEPAGESIZE,ENCODING,ANDAUTOVACUUMINGMUSTBESETBEFOREADATABASEISCREATEDTHATIS,INORDERTOALTERTHEDEFAULTS,YOUMUSTFIRSTSETTHESEPRAGMASBEFORECREATINGANYDATABASEOBJECTSINANEWDATABASETHEDEFAULTSAREA1,024BYTEPAGESIZEANDUTF8ENCODINGSQLITESUPPORTSPAGESIZESRANGINGFROM512TO32,786BYTES,INPOWERSOF2SUPPORTEDENCODINGSAREUTF8,UTF16LELITTLEENDIANUTF16ENCODING,ANDUTF16BEBIGENDIANUTF16ENCODINGADATABASESSIZECANBEAUTOMATICALLYKEPTTOAMINIMUMUSINGTHEAUTO_VACUUMPRAGMANORMALLY,WHENATRANSACTIONTHATDELETESDATAFROMADATABASEISCOMMITTED,THEDATABASEFILEREMAINSTHESAMESIZEWHENTHEAUTO_VACUUMPRAGMAISENABLED,THEDATABASEFILESHRINKSWHENATRANSACTIONTHATDELETESDATAISCOMMITTEDTOSUPPORTTHISFUNCTIONALITY,THEDATABASESTORESEXTRAINFORMATIONINTERNALLY,RESULTINGINSLIGHTLYLARGERDATABASEFILESTHANWOULDOTHERWISEBEPOSSIBLETHEVACUUMCOMMANDHASNOEFFECTONDATABASESTHATUSEAUTO_VACUUMDEBUGGINGTHEREAREFOURPRAGMASFORVARIOUSDEBUGGINGPURPOSESTHEINTEGRITY_CHECKPRAGMALOOKSFOROUTOFORDERRECORDS,MISSINGPAGES,MALFORMEDRECORDS,ANDCORRUPTEDINDEXESIFANYPROBLEMSAREFOUND,THENASINGLESTRINGISRETURNEDDESCRIBINGTHEPROBLEMSIFEVERYTHINGISINORDER,SQLITERETURNSOKTHEOTHERPRAGMASAREUSEDFORTRACINGTHEPARSERANDVIRTUALDATABASEENGINEANDCANONLYBEENABLEDIFSQLITEISCOMPILEDWITHDEBUGGINGINFORMATIONDETAILEDINFORMATIONONTHESEPRAGMASCANBEFOUNDINCHAPTER9THESYSTEMCATALOGTHESQLITE_MASTERTABLEISASYSTEMTABLETHATCONTAINSINFORMATIONABOUTALLTHETABLES,VIEWS,INDEXES,ANDTRIGGERSINTHEDATABASEFOREXAMPLE,THECURRENTCONTENTSOFTHEFOODSDATABASEAREASFOLLOWSSQLITESELECTTYPE,NAME,ROOTPAGEFROMSQLITE_MASTERTYPENAMEROOTPAGETABLEEPISODES2TABLEFOODS3TABLEFOODS_EPISODES4TABLEFOOD_TYPES5INDEXFOODS_NAME_IDX30TABLESQLITE_SEQUENCE50TRIGGERFOODS_UPDATE_TRG0TRIGGERFOODS_INSERT_TRG0TRIGGERFOODS_DELETE_TRG0THETYPECOLUMNREFERSTOTHETYPEOFOBJECT,NAMEISOFCOURSETHENAMEOFTHEOBJECT,ANDROOTPAGEREFERSTOTHEFIRSTBTREEPAGEOFTHEOBJECTINTHEDATABASEFILETHISLATTERCOLUMNISONLYRELEVANTFORTABLESANDINDEXESTHESQLITE_MASTERTABLEALSOCONTAINSANOTHERCOLUMNCALLEDSQL,WHICHSTORESTHEDMLUSEDTOCREATETHEOBJECTFOREXAMPLESQLITESELECTSQLFROMSQLITE_MASTERWHERENAMEFOODS_UPDATE_TRGCREATETRIGGERFOODS_UPDATE_TRGBEFOREUPDATEOFTYPE_IDONFOODSBEGINSELECTCASEWHENSELECTIDFROMFOOD_TYPESWHEREIDNEWTYPE_IDISNULLTHENRAISEABORT,FOREIGNKEYVIOLATIONFOODSTYPE_IDISNOTINFOOD_TYPESIDENDENDVIEWINGQUERYPLANSYOUCANVIEWTHEWAYSQLITEGOESABOUTEXECUTINGAQUERYBYUSINGTHEEXPLAINCOMMANDEXPLAINLISTSTHEVDBEPROGRAMTHATWOULDBEUSEDTOCARRYOUTASQLCOMMANDTHEVDBEISTHEVIRTUALMACHINEINSQLITETHATCARRIESOUTALLOFITSDATABASEOPERATIONSEVERYQUERYINSQLITEISEXECUTEDBYFIRSTCOMPILINGTHESQLINTOVDBEBYTECODEANDTHENRUNNINGTHEBYTECODEFOREXAMPLESQLITEEXPLAINSELECTFROMFOODSADDROPCODEP1P2P30GOTO0121INTEGER002OPENREAD033SETNUMCOLUMNS034REWIND0105ROWID006COLUMN017COLUMN028CALLBACK309NEXT0510CLOSE0011HALT0012TRANSACTION0013VERIFYCOOKIE013414GOTO0115NOOP00STUDYINGTHESEQUERYPLANSISNOTFORTHEFAINTOFHEARTTHEAVERAGEPERSONISNOTGOINGTOFINDAVDBEPROGRAMVERYINTUITIVEHOWEVER,FORTHOSEWHOAREWILLINGTOTRY,THEVDBE,ITSOPCODES,ANDTHEORYOFOPERATIONARECOVEREDINCHAPTER9SUMMARYSQLMAYBEASIMPLELANGUAGETOUSE,BUTTHEREISQUITEABITOFITBUTTHATSHOULDNTBETOOSURPRISING,ASITISTHESOLEINTERFACETHROUGHWHICHTOINTERACTWITHARELATIONALDATABASEWHETHERYOUAREACASUALUSER,SYSTEMADMINISTRATOR,ORDEVELOPER,YOUHAVETOKNOWSQLIFYOUAREGOINGTOWORKWITHARELATIONALDATABASESQLISATHINWRAPPERAROUNDTHERELATIONALMODELITISCOMPOSEDOFTHREEESSENTIALPARTSFORM,FUNCTION,ANDINTEGRITYFORMRELATESTOHOWINFORMATIONISREPRESENTEDINSQL,THISISDONEWITHTHETABLETHETABLEISTHESOLEUNITOFINFORMATION,ANDISCOMPOSEDOFROWSANDCOLUMNSTHEDATABASEISMADEUPOFOTHEROBJECTSASWELL,SUCHASVIEWS,INDEXES,ANDTRIGGERSVIEWSPROVIDEACONVENIENTWAYTOREPRESENTINFORMATIONINTABLESINDIFFERENTFORMSINDEXESWORKTOSPEEDUPQUERIESTRIGGERSENABLEYOUTOASSOCIATEPROGRAMMINGLOGICWITHSPECIFICDATABASEEVENTSASTHEYTRANSPIREONTABLESALLOFTHESEOBJECTSARECREATEDUSINGDATADEFINITIONLANGUAGEDDLDDLISTHEPARTOFSQLRESERVEDFORCREATINGANDDESTROYINGDATABASEOBJECTSFURTHERMORE,ALLOBJECTSINADATABASEARELISTEDINTHESQLITE_MASTERSYSTEMTABLETHEFUNCTIONALPARTOFSQLPERTAINSTOHOWINFORMATIONISMANIPULATEDTHEPARTOFSQLRESERVEDFORTHISISCALLEDDATAMANIPULATIONLANGUAGEDMLDMLISCOMPOSEDOFTHESELECT,UPDATE,INSERT,ANDDELETECOMMANDSSELECTISTHELARGESTANDMOSTCOMPLICATEDCOMMANDINTHELANGUAGESELECTEMPLOYS12OFTHE14OPERATIONSDEFINEDINRELATIONALALGEBRAITARRANGESTHOSEOPERATIONSINACUSTOMIZABLEPIPELINEYOUCANUSETHOSEOPERATIONSASNEEDEDTOCOMBINE,FILTER,ORDER,ANDAGGREGATEINFORMATIONWITHINTABLESTHEINTEGRITYPARTOFSQLCONTROLSHOWDATAISINSERTEDANDMODIFIEDITPROTECTSINFORMATIONANDRELATIONSHIPSBYENSURINGTHATTHEVALUESINCOLUMNSCONFORMTOSPECIFICRULESITISIMPLEMENTEDUSINGCONSTRAINTS,SUCHASUNIQUE,CHECK,ANDNOTNULLSQLITEHASAUNIQUEWAYOFHANDLINGDATATYPES,UNLIKEMOSTOTHERRELATIONALDATABASESALLVALUESHAVEASTORAGECLASSSQLITEHASFIVESTORAGECLASSESINTEGER,REAL,TEXT,BLOB,ANDNULLTHESECLASSESSPECIFYHOWVALUESARESTOREDANDMANIPULATEDINTHEDATABASEFURTHERMORE,ALLCOLUMNSHAVEANAFFINITY,WHICHDETERMINESHOWVALUESARESTOREDWITHINTHEMSQLITEHASFOURKINDSOFAFFINITYNUMERIC,INTEGER,TEXT,ANDNONEWHENSQLITESTORESAVALUEINACOLUMN,ITUSESBOTHTHECOLUMNSAFFINITYANDTHEVALUESSTORAGECLASSTODETERMINEHOWTOSTORETHEVALUEFORTHOSEWHOHAVETOHAVESTRICTTYPECHECKINGONACOLUMN,ITCANBEIMPLEMENTEDUSINGTHETYPEOFFUNCTIONANDACHECKCONSTRAINTIFYOUAREPROGRAMMINGWITHSQLITE,THENYOUSHOULDBEOFFTOAGOODSTARTONTHESQLSIDEOFTHINGSNOWYOUNEEDTOKNOWALITTLEABOUTHOWSQLITEGOESABOUTEXECUTINGALLOFTHESECOMMANDSTHISISWHERECHAPTER5SHOULDPROVEUSEFULITWILLINTRODUCEYOUTOTHEAPIANDSHOWYOUHOWITWORKSINRELATIONTOTHEWAYSQLITEFUNCTIONSINTERNALLYCHAPTER5DESIGNANDCONCEPTSTHISCHAPTERSETSTHESTAGEFORTHENEXTTHREECHAPTERS,WHICHAREEXCLUSIVELYDEVOTEDTOPROGRAMMINGWITHSQLITEITADDRESSESTHETHINGSTHATYOUASAPROGRAMMERSHOULDKNOWABOUTSQLITEWHENUSINGITINYOURCODEWHETHERYOUAREPROGRAMMINGWITHSQLITEINITSNATIVEC,ORINYOURFAVORITESCRIPTINGLANGUAGE,ITHELPSTOUNDERSTANDNOTONLYITSAPI,BUTALSOALITTLEABOUTITSARCHITECTUREANDIMPLEMENTATIONARMEDWITHTHISKNOWLEDGE,YOUWILLBEBETTEREQUIPPEDTOWRITECODETHATRUNSFASTERANDAVOIDSPOTENTIALPITFALLSSUCHASDEADLOCKSORUNEXPECTEDERRORSYOUWILLSEEHOWSQLITEWORKSINRELATIONTOYOURCODE,ANDYOUCANBEMORECONFIDENTTHATYOUAREATTACKINGTHEPROBLEMFROMTHERIGHTDIRECTIONYOUDONTHAVETOCOMBTHROUGHTHEBOWELSOFTHESOURCETOUNDERSTANDTHESETHINGS,NORDOYOUHAVETOBEACPROGRAMMERSQLITESDESIGNANDCONCEPTSAREALLVERYSTRAIGHTFORWARDANDEASYTOUNDERSTANDANDTHEREAREONLYAFEWTHINGSYOUNEEDTOKNOWTHISCHAPTERLAYSTHEMOUTFORYOUOBVIOUSLY,YOUNEEDTOKNOWHOWTHEAPIWORKSSOTHISCHAPTERSTARTSWITHACONCEPTUALINTRODUCTIONTOTHEAPI,ILLUSTRATINGITSMAJORDATASTRUCTURES,ITSGENERALDESIGN,ASWELLASITSMAJORFUNCTIONSITALSOLOOKSATSOMEOFTHEMAJORSQLITESUBSYSTEMSTHATPLAYIMPORTANTROLESINQUERYPROCESSINGBEYONDJUSTKNOWINGWHATFUNCTIONSDOWHAT,YOUALSONEEDLOOKABOVETHEAPI,SEEINGHOWEVERYTHINGOPERATESINTERMSOFTRANSACTIONSEVERYTHINGINVOLVINGASQLITEDATABASEISDONEWITHINTHECONTEXTOFATRANSACTIONTHENYOUNEEDTOLOOKBENEATHTHEAPI,TOSEEHOWTRANSACTIONSWORKINTERMSOFLOCKSLOCKSCANCAUSEPROBLEMSIFYOUDONTKNOWHOWTHEYOPERATEBYUNDERSTANDINGLOCKS,NOTONLYCANYOUAVOIDPOTENTIALCONCURRENCYPROBLEMS,YOUCANALSOOPTIMIZEYOURQUERIESBYCONTROLLINGHOWYOURPROGRAMUSESTHEMFINALLY,YOUHAVETOUNDERSTANDHOWALLOFTHESETHINGSAPPLYTOWRITINGCODETHELASTPARTOFTHECHAPTERBRINGSALLTHREETOPICSTHEAPI,TRANSACTIONS,ANDLOCKSTOGETHERANDLOOKSATDIFFERENTEXAMPLESOFGOODANDBADCODEITSPECIFICALLYIDENTIFIESSCENARIOSTHATCOULDCAUSEPROBLEMSANDPROVIDESSOMEINSIGHTONHOWTOADDRESSTHEMWITHTHESETHINGSINORDER,YOUWILLBEWELLONYOURWAYTOCONQUERINGTHECAPI,ORTHEAPIOFANYLANGUAGEEXTENSIONTHEAPIFUNCTIONALLY,THESQLITEAPICANBESEPARATEDINTOTWOGENERALPARTSTHECOREAPIANDEXTENSIONAPITHECOREAPICONSISTSOFALLTHEFUNCTIONSUSEDTOPERFORMBASICDATABASEOPERATIONSCONNECTINGTOTHEDATABASE,PROCESSINGSQL,ANDITERATINGTHROUGHRESULTSITALSOINCLUDESVARIOUSUTILITYFUNCTIONSTHATHELPWITHTASKSSUCHASSTRINGFORMATTING,OPERATIONALCONTROL,DEBUGGING,ANDERRORHANDLINGTHEEXTENSIONAPIOFFERSDIFFERENTWAYSTOEXTENDSQLITEBYCREATINGYOUROWNUSERDEFINEDSQLEXTENSIONS,WHICHYOUCANINTEGRATEINTOSQLITESSQLDIALECTWHATSNEWINSQLITEVERSION3BEFOREWEGETSTARTED,LETSTALKABOUTSOMEOFTHEFEATURESTHATHAVEBEENADDEDTOSQLITEVERSION3,ASITINTRODUCEDMANYMAJORIMPROVEMENTSTOALLMAJORSUBSYSTEMSTOBEGINWITH,SQLITESAPIHASBEENCOMPLETELYREDESIGNED,ANDHASMANYNEWFEATURESITGREWFROMAPPROXIMATELY15FUNCTIONSINVERSION2TO88FUNCTIONSINVERSION3THENEWAPIINCLUDESNATIVESUPPORTFORUTF8ANDUTF16ENCODINGSALONGWITHMANYNEWUTILITYFUNCTIONSITHASAMOREFLEXIBLEQUERYMODELTHATMAKESPREPAREDQUERIESEASIERANDSUPPORTSNEWPARAMETERBINDINGMETHODSITALSOADDEDUSERDEFINEDCOLLATINGSEQUENCES,CHECKCONSTRAINTS,64BITKEYVALUES,ANDANEWQUERYOPTIMIZERTHEBACKENDHASDRAMATICALLYIMPROVEDCONCURRENCYANDPERFORMANCEITHASANEWLOCKINGSYSTEMTHATINTRODUCESALOCKESCALATIONMODELTHISSYSTEMSOLVESTHEWRITERSTARVATIONPROBLEMINSQLITE2,WHERENEWREADERSCANCONTINUALLYPREVENTWRITERSFROMGETTINGEXCLUSIVEACCESSTOTHEDATABASETHENEWMODELENSURESTHATWRITERSGAINEXCLUSIVEACCESSONAFIRSTCOME,FIRSTSERVEDBASISFURTHERMORE,WRITERSCANEVENBEGINPERFORMINGWORKBEFOREACQUIRINGEXCLUSIVEACCESSTOTHEDATABASEBYSTORINGCHANGESINATEMPORARYBUFFERTHENEWMODELHASBEENSHOWNTOINCREASEPERFORMANCEFORWRITEINTENSIVEAPPLICATIONSBYASMUCHAS400PERCENTOVERVERSION2SQLITE3INCLUDESANIMPROVEDBTREEMODULE,WHICHNOWUSESBTREESFORTABLESBTREESYIELDBETTEROVERALLSEARCHPERFORMANCE,STORELARGERDATAFIELDSMOREEFFICIENTLY,ANDOMITUNUSEDFIELDSFROMDISKASARESULT,DATABASEFILESARETYPICALLY2535PERCENTSMALLERANDOFFERBETTEROVERALLPERFORMANCETHEBTREEMODULEISCOVEREDINMOREDETAILINCHAPTER9SQLITE3SIGNIFICANTLYCHANGEDITSSTORAGEMODELITWENTFROMTEXTONLYINVERSION2TOANEWMODELTHATSUPPORTSFIVENATIVEDATATYPES,INADDITIONTOMANIFESTTYPINGANDTYPEAFFINITYASEXPLAINEDINCHAPTER4EACHTYPEISOPTIMIZEDFORBETTEROVERALLSEARCHPERFORMANCEANDALSOUSESLESSSTORAGESPACEINTEGERANDFLOATINGPOINTVALUES,FOREXAMPLE,ARESTOREDINBINARYFORMATRATHERTHANASCIIANDTHEREFOREDONTHAVETOBECONVERTEDFOREVALUATIONINWHERECLAUSEEXPRESSIONS,ASINVERSION2MANIFESTTYPINGGIVESYOUTHEOPTIONOFDECLARINGACOLUMNSTYPESINAMEANINGFULWAYORNOTATALLTYPEAFFINITYDETERMINESTHEFORMATINWHICHAVALUEISSTOREDINACOLUMN,BASEDONTHEVALUESREPRESENTATIONANDTHECOLUMNSAFFINITYTYPEAFFINITYISDIRECTLYRELATEDTOMANIFESTTYPINGACOLUMNSAFFINITYISDETERMINEDBYITSDECLAREDTYPE,ORLACKTHEREOFINMANYWAYS,SQLITEVERSION3ISACOMPLETELYDIFFERENTDATABASEFROMSQLITEVERSION2ANDOFFERSNUMEROUSADVANTAGESINFLEXIBILITY,FEATURES,ANDPERFORMANCETHEPRINCIPALDATASTRUCTURESASYOUSAWINCHAPTER1,THEREAREMANYCOMPONENTSINSQLITEPARSER,TOKENIZER,VIRTUALMACHINE,ETCBUTFROMAPROGRAMMERSPOINTOFVIEW,THEMAINTHINGSTOKNOWABOUTARECONNECTIONS,STATEMENTS,THEBTREE,ANDTHEPAGERTHEIRRELATIONSHIPSTOONEANOTHERARESHOWNINFIGURE51THESEOBJECTSCOLLECTIVELYADDRESSTHETHREEPRINCIPALTHINGSYOUMUSTKNOWABOUTSQLITETOWRITEGOODCODETHEAPI,TRANSACTIONS,ANDLOCKSFIGURE51SQLITECAPIOBJECTMODELTECHNICALLY,THEBTREEANDPAGERARENOTPARTOFTHEAPITHEYAREOFFLIMITSBUTTHEYPLAYACRITICALROLEINTRANSACTIONSANDLOCKSWEWILLEXPLORETHEIRINVOLVEMENTINTHESEMATTERSHEREINTHISSECTIONANDLATERINTHESECTION“TRANSACTIONS”CONNECTIONSANDSTATEMENTSTHETWOFUNDAMENTALDATASTRUCTURESINTHEAPIASSOCIATEDWITHQUERYPROCESSINGARETHECONNECTIONANDTHESTATEMENTINMOSTLANGUAGEEXTENSIONSYOUWILLSEEBOTHACONNECTIONOBJECTANDASTATEMENTOBJECT,WHICHAREUSEDTOEXECUTEQUERIESINTHECAPI,THEYCORRESPONDDIRECTLYTOTHESQLITE3ANDTHESQLITE3_STMTHANDLES,RESPECTIVELYEVERYMAJOROPERATIONINTHEAPIISDONEUSINGONEOFTHESETWOSTRUCTURESACONNECTIONREPRESENTSASINGLECONNECTIONTOADATABASEASWELLASASINGLETRANSACTIONCONTEXTSTATEMENTSAREDERIVEDFROMCONNECTIONSTHATIS,EVERYSTATEMENTHASANASSOCIATEDCONNECTIONOBJECTASTATEMENTREPRESENTSASINGLECOMPILEDSQLSTATEMENTINTERNALLY,ITISEXPRESSEDINTHEFORMOFVDBEBYTECODEAPROGRAMTHATWHENEXECUTEDWILLCARRYOUTTHESQLCOMMANDSTATEMENTSCONTAINEVERYTHINGNEEDEDTOEXECUTEACOMMANDTHEYINCLUDERESOURCESTOHOLDTHESTATEOFTHEVDBEPROGRAMASITISEXECUTEDINASTEPWISEFASHION,BTREECURSORSTHATPOINTTORECORDSONDISK,ANDOTHERTHINGSSUCHASBOUNDPARAMETERS,WHICHAREADDRESSEDLATERINTHESECTION“PARAMETERBINDING”WHILETHEYCONTAINMANYDIFFERENTTHINGS,YOUCANSIMPLYTHINKOFTHEMASCURSORSWITHWHICHTOITERATETHROUGHARESULTSET,ORASOPAQUEHANDLESREFERENCINGASINGLESQLCOMMANDTHEBTREEANDPAGEREACHCONNECTIONCANHAVEMULTIPLEDATABASEOBJECTSONEMAINDATABASEFOLLOWEDBYANYATTACHEDDATABASESEACHDATABASEOBJECTHASONEBTREEOBJECT,WHICHINTURNHASONEPAGEROBJECTSTATEMENTSUSETHEIRCONNECTIONSBTREEANDPAGEROBJECTSTOREADANDWRITEDATATOANDFROMTHEDATABASESTATEMENTSTHATREADTHEDATABASEITERATEOVERBTREESUSINGCURSORSCURSORSITERATEOVERRECORDS,ANDRECORDSARESTOREDINPAGESASACURSORTRAVERSESRECORDS,ITALSOTRAVERSESPAGESFORACURSORTOACCESSAPAGE,ITMUSTFIRSTBELOADEDFROMDISKINTOMEMORYTHISISTHEPAGERSJOBWHENEVERTHEBTREENEEDSAPARTICULARPAGEINTHEDATABASE,ITASKSTHEPAGERTOFETCHITFROMDISKTHEPAGERTHENLOADSTHEPAGEINTOITSPAGECACHE,WHICHISAMEMORYBUFFERONCEITISINTHEPAGECACHE,THEBTREEANDITSASSOCIATEDCURSORCANGETTOTHERECORDSINSIDETHEPAGEIFTHECURSORMODIFIESTHEPAGE,THENTHEPAGERMUSTALSOTAKESPECIALMEASURESTOPRESERVETHEORIGINALPAGEINTHEEVENTOFATRANSACTIONROLLBACKTHUS,THEPAGERISRESPONSIBLEFORREADINGANDWRITINGTOANDFROMTHEDATABASE,MAINTAININGAMEMORYCACHEORPAGES,ASWELLASMANAGINGTRANSACTIONSINADDITIONTOTHIS,ITMANAGESLOCKSANDCRASHRECOVERYALLOFTHESERESPONSIBILITIESARECOVEREDLATERIN“TRANSACTIONS”THEREARETWOTHINGSYOUSHOULDKNOWABOUTCONNECTIONSANDTRANSACTIONSINGENERALFIRST,WHENITCOMESTOANYOPERATIONONTHEDATABASE,ACONNECTIONALWAYSOPERATESUNDERATRANSACTIONSECOND,ACONNECTIONNEVERHASMORETHANONETRANSACTIONOPENATATIMEBASICALLY,WHENEVERACONNECTIONDOESANYTHINGWITHADATABASE,ITALWAYSOPERATESUNDEREXACTLYONETRANSACTION,NOMORE,NOLESSYOUCANTAVOIDITTHEREFORE,ALLSTATEMENTSDERIVEDFROMAGIVENCONNECTIONOPERATEWITHINTHESAMETRANSACTIONCONTEXTSOIFYOUALLOCATETWOSTATEMENTSFROMTHESAMECONNECTION,ANDTHEFIRSTISSUESABEGINANDTHESECONDISSUESACOMMIT,THENTHEYCOLLECTIVELYCOMPLETEDASINGLETRANSACTIONTHEFIRSTSTATEMENTSTARTEDITANDTHESE