Embedded database

Introductiontoembeddeddatabase

Inembeddedsystems,theoperationofthedatabasehasthecharacteristicsoftimelimit.Here,thedatabasesystemappliedtotheembeddedsystemiscalledtheembeddeddatabasesystemorEmbeddedreal-timedatabasesystem(ERTDBS).

Thereliabilityrequirementsareunquestionable.Theembeddedsystemmustbeabletorununinterruptedlyforalongtimewithoutmanualintervention.Atthesametime,databaseoperationsarerequiredtobepredictable,andthesizeandperformanceofthesystemmustalsobepredictable,soastoensuretheperformanceofthesystem.Embeddedsystemswillinevitablydealwiththeunderlyinghardware,soindatamanagement,theremustalsobetheabilitytocontroltheunderlying,suchaswhendiskoperationswilloccur,thenumberofdiskoperations,howtocontrol,etc.Theabilityoflow-levelcontrolisthekeytodeterminingdatabasemanagementoperations.

Oneofthecurrentchallengesinthedevelopmentofembeddedsoftwaresystemsisembodiedinwhetherasetofreliable,efficientandstablemanagementmodelscanbeestablishedforthemanagementofvariousdata.Embeddeddatabasescanbesaidtohaveemergedasthetimesrequire.

Embeddeddatabaseisanimportantpartofembeddedsystem,andithasalsobecomeanindispensableeffectivemeansformoreandmorepersonalizedapplicationdevelopmentandmanagement.

Embeddeddatabaseshaveawiderangeofuses,suchasconsumerelectronics,mobilecomputingdevices,enterprisereal-timemanagementapplications,networkstorageandmanagement,andvariousspecialequipment.Thismarketiscurrentlyundergoingrapidgrowth.Togiveasimpleexample,mobilephoneswereoriginallyonlyusedformakingcallsandsendingtextmessages.Nowmobilephoneshaveaddedmanynewfunctions,suchasMMS,music,photography,video,etc.,withmoreapplicationfunctions,thesystembecomesmorecomplicated.

Comparisonofcommonlyusedembeddeddatabases

Progress

ProgresssoftwarecompanyannouncedinBeijingat18:00onApril18,2000,thefullreleaseontheLinuxoperatingsystemTherunningdatabaseanditsdeploymentproducts.Progresshastheworld'snumberonemarketshareintheembeddeddatabasemarket.Morethan2millionpeopleintheworldareusingProgressSoftware'sapplicationsoftware.Currently,thenumberofsitesdeployingProgressproductshasexceeded100,000.ThroughProgressSoftware'sfirstLinuxversionoftheembeddeddatabase,independentsoftwaredevelopersandenduserscanportmorethan5,000commercialapplicationsonthispopularoperatingsystem.

----TheproductlaunchedbyProgressSoftwareatthattimewasProgressVersion8.3,andithasnowreachedversion10.2c.Thisisacompletesetofintegrateddevelopmenttools,applicationserversandrelationaldatabaseproducts,andprovidesscalablemulti-layerLinuxsupport.Progresssoftwarecompany’sLinux-specificproductsinclude:

Progress(r)AppServer(tm):ThisisanapplicationserverProgress(r)EnterpriseRDBMS(tm)thatcandeploysharedapplicationcomponentsinaheterogeneousenvironment:Providesascalablestoragesolutionforthemostdemandingapplicationsthatneedtosupportlargedatabases,multi-processorhardware,andthousandsofconcurrentusers.

----TheProgressVersion8.3deploymentproductforRedHat6.0Linuxisnowfullyavailable.ProgresshaslaunchedProgressVersion9,Progress(r)WebSpeed(r)Version3,Progress(r)Apptivity(tm)andProgress(r)SonicMQ(tm)deploymentproductsforLinux.

----QAD,theworld'stopautomotiveindustryERPsupplier,supportsthelatestPROGRESSversion.

SQLite

ThemainfeaturesofthelightweightdatabaseSQLite:

1.Supportevents,noconfiguration,noinstallation,andnoadministrator;

2.SupportmostSQL92;

3.Acompletedatabaseisstoredinafileonthedisk,thesamedatabasefilecanbeusedondifferentmachines,andthemaximumsupportdatabaseis2T,CharacterandBLOBsupportisonlylimitedtoavailablememory;

4.Theentiresystemislessthan30,000linesofcode,lessthan250KBofmemoryfootprint(gcc),mostapplicationsaremorethanthecurrentcommonclient/Theserver-sidedatabaseisfastandhasnootherdependencies.

5.Thesourcecodeisopen,95%ofthecodehasgoodannotations,andasimpleandeasy-to-useAPI.TheofficialversioncomeswithTCL.

Empress(commercialdatabase)

Featuresofthedevelopmentstage:

1.Programscanbeembedded,thisfeatureenablesapplicationsanddatabasestoworkinaunifiedaddressspace,enhancingImprovethestabilityofthesystemandimprovetheefficiencyofthesystem.

2.Determinetheresponsetime.Empresscanmaketheresponsetimeofthedatarelativelyconsistent.Theusercansetatimeoutlimit.Iftheinsertionormodificationisnotcompletedwithinthespecifiedtime,thesystemwillreportanerror.

3.FastoperationEmpressprovidesakernel-levelCAPI,calledMR,andapplicationswrittenwithMRdonotneedtobeparsedduringexecution.Inaddition,theaccelerationmechanisminMRalsoincludesexcellentlockcontrol,memorymanagementandselectionfunctionsbasedonthenumberofrecords.

4.Flexibledevelopmentmethods,Empressprovidesmultipledevelopmentinterfacestospeedupthedevelopmentprocesswithoutrequiringdeveloperstorelearnthedevelopmentlanguageandfamiliarizethemselveswiththedevelopmentenvironment.

5.Friendlystoragemethod,Empressdatabasecanbeplacedinanystoragedevicesupportedbytheoperatingsystem,andEmpressformscanevenbesplitandplacedindifferentstoragedevices,suchasmemory,harddiskandCD-ROM.

6.Themicro-kernelstructureEmpressishighlyunitized,andtherequiredunitcanbeselectedaccordingtotheneeds,therebyreducingtheresourcesoccupiedbytheEmpressdatabaseintheproduct.

7.Broadplatformsupport,Empresssupportsavarietyofhardwareplatformsandsoftwareplatforms,andcanalsobeportedtothehardwareplatformoroperatingsystemrequiredbythecustomer.

Technicaladvantages:

1.Micro-kernelstructure,occupyingasmallamountofmemoryspace,especiallysuitableforcompactdesign

2.Workcontinuously24hoursaday,7daysaweek,withoutanyadditionaloperationsandmaintenance-free

3.Kernel-levelCAPIinterfacetomaximizeoperatingspeed

4.HighlyflexibleSQLinterface

5.Excellentpower-downrecoverycapability

6.Strongtransactionandlatchmechanism

7.SupportSCSI,RAID,IDE,RAM,CD-RW,DVD-ROM,CF,andotherstoragemedia

8.SupportUnicodecode

9.Theenginecanbeloadedondiskandmemory

eXtremeDB

CharacteristicsofeXtremeDB:

1.In-memorydatabase,eXtremeDBsavesdatainthemainmemoryinaformatdirectlyusedbytheprogram,whichnotonlyeliminatestheoverheadoffileI/O,butalsoeliminatesthefilesystemThebufferandCachemechanismrequiredbythedatabase.Theresultisalimitspeedofonemicrosecondorlesspertransaction,whichishundredsorthousandsoftimesfasterthanadisk-likedatabase.Asanin-memorydatabase,eXtremeDBnotonlyhashighperformance,butalsohashighdatastorageefficiency.Inordertoimproveperformanceandfacilitatetheuseofprograms,dataisnotcompressedineXtremeDB,and100Mofspacecansaveupto70Mofeffectivedata,whichisunimaginableinotherdatabases.

2.Hybriddatabase,eXtremeDBcannotonlybuildanin-memorydatabasethatrunscompletelyinthemainmemory,butalsoadisk/memoryhybriddatabase.IneXtremeDB,wecallthisoperatingmodebasedondisk,memoryordisk+memoryeXtremeDBFusion.eXtremeDBFusiontakesintoaccountthereal-timeandsecurityrequirementsofdatamanagement,andisastep-by-stepimprovementinreal-timedatamanagement.

3.Embeddeddatabase,theeXtremeDBkernelisincludedintheapplicationintheformofalinklibrary,anditsoverheadisonly50KB~130KB.Whetherinanembeddedsystemorareal-timesystem,eXtremeDBisnaturallyembeddedintheapplicationandworkswithouttheenduser'sknowledge.ThisnaturalembeddednessofeXtremeDBisessentialforreal-timedatamanagement:eachprocessdirectlyaccessestheeXtremeDBdatabase,avoidinginter-processcommunication,therebyeliminatingtheoverheadanduncertaintyofinter-processcommunication.Atthesametime,eXtremeDB'suniquedataformatisconvenientfortheprogramtousedirectly,eliminatingtheoverheadofdatacopyinganddatatranslation,andshorteningthecodeexecutionpathoftheapplication.

4.Theapplication-customizedAPI,theapplication'soperatinginterfacetotheeXtremeDBdatabaseisautomaticallygeneratedaccordingtotheapplicationdatabasedesign,whichnotonlyimprovestheperformance,butalsoeliminatesthedynamicmemoryallocationnecessaryforthegeneralinterface,Therebyimprovingthereliabilityoftheapplicationsystem.Thecustomizationprocessissimpleandconvenient.Theapplicationfeaturessuchastables,fields,datatypes,eventtriggers,andaccessmethodsintheeXtremeDBdatabasearecustomizedbyahigh-levellanguage,andtheC/C++APIinterfaceforaccessingthedatabaseisautomaticallygeneratedthroughtheeXtremeDBpre-compiler.

5.Predictabledatamanagement

TheuniquearchitectureofeXtremeDBensuresthepredictabilityofdatamanagement.eXtremeDBisnotonlyfaster,smaller,andmorecertain.Onaserverwith80dual-coreCPUs,eXtremeDBsaves15Brecordsin1TBofmemory;nomatterhowmanyrecordsare,eXtremeDBcanretrievearecordinone-eightymicroseconds.

Firebirdembeddedserverversion

Firebirdembeddedserverversion(EmbeddedServer),FirebirdderivedfromInterbaseopensource,isfullofvitality.AlthoughitssizeisdozensoftimessmallerthanitspredecessorInterbase,itsfunctionsarenotcastrated.InordertoreflectFirebird'sshortandslickfeatures,thedevelopmentteamaddedanembeddedversionafteraddingasuperserverversion.Thelatestversionis2.0.

TheembeddedversionofFirebirdhasthefollowingfeatures:

1.ThedatabasefileisfullycompatiblewiththeFirebirdnetworkversion.Theonlydifferenceisthattheconnectionmethodisdifferent,whichcanrealizezero-costmigration.

2.Thedatabasefileisonlylimitedbytheoperatingsystem,anditsupportssplittingadatabaseintodifferentfiles,breakingthelimitationofthemaximumfileoftheoperatingsystemandimprovingtheIOthroughput.

3.ItfullysupportstheSQL92standardandmostoftheSQL-99standardfunctions.

4.Richdevelopmenttoolsupport,mostofthecomponentsbasedonInterbasecanbedirectlyusedinFirebird.

5.Supportallfeaturesofrelationaldatabasessuchastransactions,storedprocedures,andtriggers.

6.Youcanwriteyourownextensionfunctions(UDF).

IntroductiontomSQL

mSQL(miniSQL)isasingle-userdatabasemanagementsystem,freeforpersonaluseandchargeableforcommercialuse.Becauseofitsshortsize,theapplicationsystemdevelopedbyitisespeciallypopularamongInternetusers.mSQL(miniSQL)isasmallrelationaldatabase,theperformanceisnotverygood,theSQLlanguagesupportisnotcomplete,butitisenoughinsomenetworkdatabaseapplications.BecausemSQLisrelativelysimple,itisslightlyfasterthanMySQLwhenrunningsimpleSQLstatements,whileMySQLhasworkedhardonthreadsandindexestoruncomplexSQLstatementsfasterthanmSQL,PostgreSQL,etc.Thelatestversionis3.7releasedonMay8,2005.ThelogoofMSQLisadeer,asshowninthefigurebelow.Figure1mSQLLOGOlogo

TechnicalcharacteristicsofmSQL:Intermsofsecurity,mSQLsetstheaccessauthorityofeachuseroneachhostthroughACLfiles,andthedefaultisallread/write.mSQLlacksmostofthefeaturesofANSISQL,itonlyimplementsaminimalAPI,withouttransactionsandreferentialintegrity.MSQLiscloselyintegratedwithLite(aC-likescriptinglanguage,releasedwiththedistribution),andyoucangetawebsiteintegrationpackagecalledW3-mSQL,whichisJDBC,ODBC,PerlandPHPAPI.

SQLitedatabaseexperiment

Learningresources

sqliteChinesewebsite:programmingintroduction,opensourceforum

sqliteofficialwebsite:sourcecodedownload,GettingStarted,C/C++References,variousotherdocuments,developerforums,etc.

InstallandrunSQLiteontheLinuxhost

Downloadsqlite-amalgamation-3.5.9.tar.gzfile

Uploadsqlite-amalgamation-3.5.9.tar.gztomikedirectory

Unzipsqlite-amalgamation-3.5.9.tar.gz

tarzxvfsqlite-amalgamation-3.5.9.tar.gz

Enterthesqlitedirectory:

cdsqlite-amalgamation-3.5.9

Configuration

./configure

Compile

make

Installation

makeinstall

Thefollowingfilesaremainlyinstalled:

/usr/local/include/sqlite3.h

/usr/local/include/sqlite3ex.h

/usr/local/lib/libsqlite3*

/usr/local/bin/sqlite3

Theheaderfileisusedtoprovidethelibraryfunctionprototypeprovidedbysqlite.Theseheaderfileswillbeincludedwhenwritingdatabaseapplications;thelibraryfunctionisthedynamiclinklibraryprovidedbysqlite,iftheapplicationusesIfSQLitelibraryfunctionsareadded,theselibrarieswillbedynamicallylinkedduringcompilation;thesqliteapplicationinthebindirectoryisashellprogramthatprovidesausercommandlineinterface,anduserscanexperiencethefunctionsprovidedbysqlite.

3.2.1Usethehostcommandlinetoexperiencethefunctionsofsqlite,seeattachment1

3.2.2Writetheapplicationprogramcodethatusesthedatabaseasfollows:

#include

#include

staticintcallback(void*NotUsed,intargc,char**argv,char**azColName){

inti;

for(i=0;i

printf("%s=%s\n",azColName[i],argv[i]?argv[i]:"NULL");

}

printf("\n");

return0;

}

intmain(intargc,char**argv){

sqlite3*db;

char*zErrMsg=0;

intrc;

if(argc!=3){

fprintf(stderr,"Usage:%sDATABASESQL-STATEMENT\n",argv[0]);

exit(1);

}

rc=sqlite3_open(argv[1],&db);

if(rc){

fprintf(stderr,"Can'topendatabase:%s\n",sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

}

rc=sqlite3_exec(db,argv[2],callback,0,&zErrMsg);

if(rc!=SQLITE_OK){

fprintf(stderr,"SQLerror:%s\n",zErrMsg);

sqlite3_free(zErrMsg);

}

sqlite3_close(db);

return0;

}

(1)Createasqlite_test.cfilewiththecontentasabove.Putsqlite_test.cThefileisplacedinthemikedirectory.

(2)Compilethesqlite_test.cfile:

gcc-I/usr/local/include-L/usr/local/lib-osqlite_testsqlite_test.c-lsqlite3

Theapplicationthatgeneratessqlite_test.

GccCommandlineparameterexplanation:

-Ldir

Whencompiling,searchThepathofthelibrary.Forexample,youcanuseittomakeacatalogforyourownlibrary,otherwisethecompilerwillonlyfinditinthecatalogofthestandardlibrary.Thisdiristhenameofthedirectory.

-llibrary

Specifythelibraryusedwhencompiling

-Idir

Whenyouuse#include"file",gcc/g++willfirstsearchfortheheaderfileyoumadeinthecurrentdirectory.Ifitisnotfound,itwillreturntothedefaultheaderfiledirectorytofindit.Ifyouuse-Ihasestablishedadirectory,hewillfirstsearchinthedirectoryyoumade,andthensearchintheusualorder.

For#include,gcc/g++willsearchinthedirectorysetby-I,searchIfnot,thensearchinthedefaultheaderfiledirectoryofthesystem

(3)Testthesqlite_testprogram

Thetestprogramacceptstwoparameters:thefirstparameteristhedatabasefilename,ThesecondparameteristheSQLstatementtobeexecuted.TherearefourmainplacesrelatedtotheSQLite3APIintheprogram:sqlite3_open()online27,sqlite3_exec()online33,sqlite3_close()onlines30and38,andsqlite3_free()online36.Pleaserefertoliterature[1]fortheAPIinterfaceofSQLite3.

Thefollowingisthecompleteprocessoftestingthetestprogram.Itshouldbenotedthatduetothelongcommand,eachcommandisdividedintomultiplelinesofinput,soitlooksclearer:

[root@Sitsang2root]$./testxyz.db"createtable

>tbl0(namevarchar(10),numbersmallint);"

[root@Sitsang2root]$./testxyz.db"insertinto

>tbl0values('cyc',1);"

[root@Sitsang2root]$./testxyz.db"insertinto

>tbl0values('dzy',2);"

[root@Sitsang2root]$./testxyz.db"select*

>fromtbl0;"

name=cyc

number=1

name=dzy

number=2

Explainthetestcommandusedabove:Thefirstcommandcreatesatbl0tableinthexyz.dbdatabasefile.Thetablecontainstwofields.Thefieldnameisavariable-lengthstring.Thetypeofnumberissmallint;thesecondcommandinsertsarecord('cyc',1)intothetbl0tableofthedatabase;thethirdcommandinsertsarecord('dzy',2)intothetbl0tableofthedatabase;Thefourthcommandistoqueryallthecontentsofthetabletbl0,asexpected,thiscommandprintsexceptforthetwonewlyinsertedrecordsinthedatabase.Itcanbeconcludedthatthesecommandshaveindeedworkedinaccordancewiththeexpectedgoals.

Atthesametime,afterinsertingthedatashownaboveintothedatabase,youcanseethatthesizeofthedatabasefilexyz.dbhaschanged:

[root@Sitsang2root]$ll-hxyz.db

-rw-r--r--1rootroot2.0kJan100:18xyz.db

Thesizeofthedatabasefilexyz.dbatthistimeIs2K.Thetestresultsshowthatthedatabasecanworknormally.

Developanembeddeddatabaseprogrambasedonsqlite

3.3.1MigrateSQLitetothedevelopmentboardanddeletetheprevioussqlite-amalgamation-3.5.9directory

Unzipsqlite-amalgamation-3.5.9.tar.gz

tarzxvfsqlite-amalgamation-3.5.9.tar.gz

Enterthesqlitedirectory

cdsqlite-amalgamation-3.5.9

Wecreateadirectorysqlite_installunderthe/home/mikedirectoryastheinstallationdirectoryforlateruse.

mkdir/home/mike/sqlite_install

ConfiguresourcefilestogenerateMakefilesuitableforthedevelopmentboard

exportPATH=/usr/local/arm-linux/bin:$PATH

./configure--host=arm-linux--prefix=/home/mike/sqlite_install

Explanation:HowtoinstalllinuxsoftwareTherearegenerallytwoways:rpmmode;use./configureconfiguration,thenmakeandthenmakeinstallmode;pleaserefertorelatedinformation.Here,the--hostparameterreferstothecompiledsoftwaretoberunonthearm-linuxplatform;--prefixreferstothesoftwareinstallationdirectory.TheresultofConfigurerunningistoproduceacorrespondingMakefilefilethatfollowsthetwoparametersspecifiedafterconfigure.

Compile

make

Install

makeinstall

Enter/home/sqlite_installtowatch,Youcanfindthattherearethreedirectories,namelyinclude,lib,bin.Theheaderfilesrequiredforwritingsqlitedatabaseapplications,libraryfunctionsrequiredforapplicationcompilationandruntime,andsqlitecommandlineprogramsthatcanberunonthearmlinuxsystemarerespectivelystored.

3.3.2WritingandcompilingapplicationsusingSQLitefunctionlibraryWeusethecross-developmenttoolchaintorecompiletheprogramsqlite_test.cwrittenin3.2.2sothatitcanrunonthedevelopmentboard.

Compilethesourceprogram:

Confirmthatsqlite_test.cisinthemikedirectoryandenterthemikedirectory

arm-linux-gcc-I./sqlite_install/include/-L./sqlite_install/lib/-osqlite_testsqlite_test.c-lsqlite3

Afterrunning,generatethearmversionsqlite_testfile

Checktheattributesofthesqlite_testfile

filesqlite_test

Copythesqlite_testfiletoadirectoryoftherootfilesystem,andcopyallthefilesinthe/home/mike/sqlite_install/libdirectorytotherootofthelibraryfunctionsneededforsqlite_testtorunUnderthelibdirectoryofthefiledirectory,recreatetherootfilesystemimageincramfsoryaffsformatandburnittotheexperimentalsystem.

Afterthesystemisstarted,youcantestsqlite_testinthesamewayasin3.2.Annex1sqlite3:Acommand-lineaccessprogramforSQLitedatabaseTheSQLitelibraryincludesasimplecommand-lineutilitynamedsqlite3thatallowstheusertomanuallyenterandexecuteSQLcommandsagainstanSQLitedatabase.Thisdocumentprovidesabriefintroductiononhowtousesqlite3.

GettingStarted

Tostartthesqlite3program,justtype"sqlite3"followedbythenamethefilethatholdstheSQLitedatabase.Ifthefiledoesnotexist,anewoneiscreatedautomatically.Thesqlite3programwillthenpromptyoutoenterSQL.TypeinSQLstatements(terminatedbyasemicolon),press"Enter"andtheSQLwillbeexecuted.

Forexample,tocreateanewSQLitedatabasenamed"ex1"withasingletablenamed"tbl1",youmightdothis:

$sqlite3ex1

SQLiteversion3.3.10

Enter".help"forinstructions

sqlite>createtabletbl1(onevarchar(10),twosmallint);

sqlite>insertintotbl1values('hello!',10);

sqlite>insertintotbl1values('goodbye',20);

sqlite>select*fromtbl1;

hello!|10

goodbye|20

sqlite>

Youcanterminatethesqlite3programbytypingyoursystemsEnd-Of-Filecharacter(usuallyaControl-D)ortheinterruptcharacter(usuallyaControl-C).

MakesureyoutypeasemicolonattheendofeachSQLcommand!Thesqlite3programlooksforasemicolontoknowwhenyourSQLcommandiscomplete.Ifyouomitthesemicolon,sqlite3willgiveyouacontinuationpromptandwaitforyoutoentermoretexttobeaddedtothecurrentSQLcommand.ThisfeatureallowsyoutoenterSQLcommandsthatspanmultiplelines.Forexample:

sqlite>CREATETABLEtbl2(

...>f1varchar(30)primarykey,

...>f2text,

...>f3real

...>);

sqlite>

Aside:QueryingtheSQLITE_MASTERtable

ThedatabaseschemainanSQLitedatabaseisstoredinaspecialtablenamed"sqlite_master".Youcanexecute"SELECT"statementsagainstthespecialsqlite_mastertablejustlikeanyothertableinanSQLitedatabase.Forexample:

$sqlite3ex1

SQlitevresion3.3.10

Enter".help"forinstructions

sqlite>select*fromsqlite_master;

type=table

name=tbl1

tbl_name=tbl1

rootpage=3

sql=createtabletbl1(onevarchar(10),twosmallint)

sqlite>

ButyoucannotexecuteDROPTABLE,UPDATE,INSERTorDELETEagainstthesqlite_mastertable.Thesqlite_mastertableisupdatedautomaticallyasyoucreateordroptablesandindicesfromthedatabase.Youcannotmakemanualchangestothesqlite_mastertable.

TheschemaforTEMPORARYtablesisnotstoredinthe"sqlite_master"tablesinceTEMPORARYtablesarenotvisibletoapplicationsotherthantheapplicationthatcreatedthetable.TheschemaforTEMPORARYtablesisstoredinanotherspecialtablenamed"sqlite_temp_master".The"sqlite_temp_master"tableistemporaryitself.

Specialcommandstosqlite3

Mostofthetime,sqlite3justreadslinesofinputandpassesthemontotheSQLitelibraryforexecution.Butifaninputlinebeginswithadot("."),thenthatlineisinterceptedandinterpretedbythesqlite3programitself.These"dotcommands"aretypicallyusedtochangetheoutputformatofqueries,ortoexecutecertainprepackagedquerystatements.

Foralistingoftheavailabledotcommands,youcanenter".help"atanytime.Forexample:

sqlite>.help

.bailON|OFFStopafterhittinganerror.DefaultOFF

.databasesListnamesandfilesofattacheddatabases

.dump?TABLE?...DumpthedatabaseinanSQLtextformat

.echoON|OFFTurncommandechoonoroff

.exitExitthisprogram

.explainON|OFFTurnoutputmodesuitableforEXPLAINonoroff.

.header(s)ON|OFFTurndisplayofheadersonoroff

.helpShowthismessage

.importFILETABLEImportdatafromFILEintoTABLE

.indicesTABLEShownamesofallindicesonTABLE

.loadFILE?ENTRY?Loadanextensionlibrary

.modeMODE?TABLE?SetoutputmodewhereMODEisoneof:

csvComma-separatedvalues

columnLeft-alignedcolumns.(See.width)

htmlHTML

insertSQLinsertstatementsforTABLE

lineOnevalueperline

listValues​​delimitedby.separatorstring

tabsTab-separatedvalues

tclTCLlistelements

.nullvalueSTRINGPrintSTRINGinplaceofNULLvalues

.outputFILENAMESendoutputtoFILENAME

.outputstdoutSendoutputtothescreen

.promptMAINCONTINUEReplacethestandardprompts

.quitExitthisprogram

.readFILENAMEExecuteSQLinFILENAME

.schema?TABLE?ShowtheCREATEstatements

.separatorSTRINGChangeseparatorusedbyoutputmodeand.import

.showShowthecurrentvalues​​forvarioussettings

.tables?PATTERN?ListnamesoftablesmatchingaLIKEpattern

.timeoutMSTryopeninglockedtablesforMSmilliseconds

.widthNUMNUM...Setcolumnwidthsfor"column"mode

sqlite>

ChangingOutputFormats

Thesqlite3programisabletoshowtheresultsofaqueryineightdifferentformats:"csv","column","html","insert","line","tabs",and"tcl".Youcanusethe".mode"dotcommandtoswitchbetweentheseoutputformats.

Thedefaultoutputmodeis"list".Inlistmode,eachrecordofaqueryresultiswrittenononelineofoutputandeachcolumnwithinthatrecordisseparatedbyaspecificseparatorstring.Thedefaultseparatorisapipesymbol("|").Listmodeisespeciallyusefulwhenyouaregoingtosendtheoutputofaquerytoanotherprogram(suchasAWK)foradditionalprocessing.

sqlite>.modelist

sqlite>select*fromtbl1;

hello|10

goodbye|20

sqlite>

Youcanusethe".separator"dotcommandtochangetheseparatorforlistmode.Forexample,tochangetheseparatortoacommaandaspace,youcoulddothis:

sqlite>.separator","

sqlite>select*fromtbl1;

hello,10

goodbye,20

sqlite>

In"line"mode,eachcolumninarowofthedatabaseisshownonalinebyitself.Eachlineconsistsofthecolumnname,anequalsignandthecolumndata.Successiverecordsareseparatedbyablankline.Hereisanexampleoflinemodeoutput:

sqlite>.modeline

sqlite>select*fromtbl1;

one=hello

two=10

one=goodbye

two=20

sqlite>

Incolumnmode,eachrecordisshownonaseparatelinewiththedataalignedincolumns.Forexample:

sqlite>.modecolumn

sqlite>select*fromtbl1;

onetwo

-------------------

hello10

goodbye20

sqlite>

Bydefault,eachcolumnisatleast10characterswide.Datathatistoowidetofitinacolumnistruncated.Youcanadjustthecolumnwidthsusingthe".width"command.Likethis:

sqlite>.width126

sqlite>select*fromtbl1;

onetwo

------------------

hello10

goodbye20

sqlite>

The".width"commandintheexampleabovesetsthewidthofthefirstcolumnto12andthewidthofthesecondcolumnto6.Allothercolumnwidthswereunaltered.Youcangivesasmanyargumentsto".width"asnecessarytospecifythewidthsofasmanycolumnsasareinyourqueryresults.

Ifyouspecifyacolumnawidthof0,thenthecolumnwidthisautomaticallyadjustedtobethemaximumofthreenumbers:10,thewidthoftheheader,andthewidthofthefirstrowofdata.Thismakesthecolumnwidthself-adjusting.Thedefaultwidthsettingforeverycolumnisthisauto-adjusting0value.

Thecolumnlabelsthatappearonthefirsttwolinesofoutputcanbeturnedonandoffusingthe".header"dotcommand.Intheexamplesabove,thecolumnlabelsareon.Toturnthemoffyoucoulddothis:

sqlite>.headeroff

sqlite>select*fromtbl1;

hello10

goodbye20

sqlite>

Anotherusefuloutputmodeis"insert".Ininsertmode,theoutputisformattedtolooklikeSQLINSERTstatements.Youcanuseinsertmodetogeneratetextthatcanlaterbeusedtoinputdataintoadifferentdatabase.

Whenspecifyinginsertmode,youhavetogiveanextraargumentwhichisthenameofthetabletobeinsertedinto.Forexample:

sqlite>.modeinsertnew_table

sqlite>select*fromtbl1;

INSERTINTO'new_table'VALUES('hello',10);

INSERTINTO'new_table'VALUES('goodbye',20);

sqlite>

Thelastoutputmodeis"html".Inthismode,sqlite3writestheresultsofthequeryasanXHTMLtable.Thebeginning

Writingresultstoafile

Bydefault,sqlite3sendsqueryresultstostandardoutput.Youcanchangethisusingthe".output"command.Justputthenameofanoutputfileasanargumenttothe.outputcommandandallsubsequentqueryresultswillbewrittentothatfile.Use".outputstdout"tobeginwritingtostandardoutputagain.Forexample:

sqlite>.modelist

sqlite>.separator|

sqlite>.outputtest_file_1.txt

sqlite>select*fromtbl1;

sqlite>.exit

$cattest_file_1.txt

hello|10

goodbye|20

$

Queryingthedatabaseschema

Thesqlite3programprovidesseveralconveniencecommandsthatareusefulforlookingattheschemaofthedatabase.Thereisnothingthatthesecommandsdothatcannotbedonebysomeothermeans.Thesecommandsareprovidedpurelyasashortcut.

Forexample,toseealistofthetablesinthedatabase,youcanenter".tables".

sqlite>.tables

tbl1

tbl2

sqlite>

The".tables"commandissimilartosettinglistmodethenexecutingthefollowingquery:

SELECTnameFROMsqlite_master

WHEREtypeIN('table','view')ANDnameNOTLIKE'sqlite_%'

UNIONALL

SELECTnameFROMsqlite_temp_master

WHEREtypeIN('table','view')

ORDERBY1

Infact,ifyoulookatthesourcecodetothesqlite3program(foundinthesourcetreeinthefilesrc/shell.c)you'llfindexactlytheabovequery.

The".indices"commandworksinasimilarwaytolistalloftheindicesforaparticulartable.The".indices"commandtakesasingleargumentwhichisthenameofthetableforwhichtheindicesaredesired.Last,butnotleast,isthe".schema"command.Withnoarguments,the".schema"commandshowstheoriginalCREATETABLEandCREATEINDEXstatementsthatwereusedtobuildthecurrentdatabase.Ifyougivethenameofatableto".schema",itshowstheoriginalCREATEstatementusedtomakethattableandallifitsindices.Wehave:

sqlite>.schema

createtabletbl1(onevarchar(10),twosmallint)

CREATETABLEtbl2(

f1varchar(30)primarykey,

f2text,

f3real

)

sqlite>.schematbl2

CREATETABLEtbl2(

f1varchar(30)primarykey,

f2text,

f3real

)

sqlite>

The".schema"commandaccomplishesthesamethingassettinglistmode,thenenteringthefollowingquery:

SELECTsqlFROM

(SELECT*FROMsqlite_masterUNIONALL

SELECT*FROMsqlite_temp_master)

WHEREtype!='meta'

ORDERBYtbl_name,typeDESC,name

Or,ifyougiveanargumentto".schema"becauseyouonlywanttheschemaforasingletable,thequerylookslikethis:

SELECTsqlFROM

(SELECT*FROMsqlite_masterUNIONALL

SELECT*FROMsqlite_temp_master)

WHEREtype!='meta'ANDsqlNOTNULLANDnameNOTLIKE'sqlite_%'

ORDERBYsubstr(type,2,1),name

Youcansupplyanargumenttothe.schemacommand.Ifyoudo,thequerylookslikethis:

SELECTsqlFROM

(SELECT*FROMsqlite_masterUNIONALL

SELECT*FROMsqlite_temp_master)

WHEREtbl_nameLIKE'%s'

ANDtype!='meta'ANDsqlNOTNULLANDnameNOTLIKE'sqlite_%'

ORDERBYsubstr(type,2,1),name

The"%s"inthequeryisreplacebyyourargument.Thisallowsyoutoviewtheschemaforsomesubsetofthedatabase.

sqlite>.schema%abc%

Alongthesesamelines,the".table"commandalsoacceptsapatternasitsfirstargument.Ifyougiveanargumenttothe.tablecommand,a"%"isbothappendedandprependedandaLIKEclauseisaddedtothequery.Thisallowsyoutolistonlythosetablesthatmatchaparticularpattern.

The".databases"commandshowsalistofalldatabasesopeninthecurrentconnection.Therewillalwaysbeatleast2.Thefirstoneis"main",theoriginaldatabaseopened.Thesecondis"temp",thedatabaseusedfortemporarytables.TheremaybeadditionaldatabaseslistedfordatabasesattachedusingtheATTACHstatement.Thefirstoutputcolumnisthenamethedatabaseisattachedwith,andthesecondcolumnisthefilenameoftheexternalfile.

sqlite>.databases

ConvertingAnEntireDatabaseToAnASCIITextFile

Usethe".dump"commandtoconverttheentirecontentsofadatabaseintoasingleASCIItextfile.Thisfilecanbeconvertedbackintoadatabasebypipingitbackintosqlite3.

Agoodwaytomakeanarchivalcopyofadatabaseisthis:

$echo'.dump'|sqlite3ex1|gzip-c>ex1.dump.gz

Thisgeneratesafilenamedex1.dump.gzthatcontainseverythingyouneedtoreconstructthedatabaseatalatertime,oronanothermachine.Toreconstructthedatabase,justtype:

$zcatex1.dump.gz|sqlite3ex2

ThetextformatispureSQLsoyoucanalsousethe.dumpcommandtoexportanSQLitedatabaseintootherpopularSQLdatabaseengines.Likethis:

$createdbex2

$sqlite3ex1.dump|psqlex2

OtherDotCommands

The".explain"dotcommandcanbeusedtosettheoutputmodeto"column"andtosetthecolumnwidthstovaluesthatarereasonableforlookingattheoutputofanEXPLAINcommand.TheEXPLAINcommandisanSQLite-specificSQLextensionthatisusefulfordebugging.IfanyregularSQLisprefacedbyEXPLAIN,thentheSQLcommandisparsedandanalyzedbutisnotexecuted.Instead,thesequenceofvirtualmachineinstructionsthatwouldhavebeenusedtoexecutetheSQLcommandarereturnedlikeaqueryresult.Forexample:

sqlite>.explain

sqlite>explaindeletefromtbl1wheretwo<20;

addropcodep1p2p3

---------------------------------------------------------------

0ListOpen00

1Open01tbl1

2Next09

3Field01

4Integer200

5Ge02

6Key00

7ListWrite00

8Goto02

9Noop00

10ListRewind00

11ListRead014

12Delete00

13Goto011

14ListClose00

The".timeout"commandsetstheamountoftimethatthesqlite3programwillwaitforlockstoclearonfilesitistryingtoaccessbeforereturninganerror.Thedefaultvalueofthetimeoutiszerosothatanerrorisreturnedimmediatelyifanyneededdatabasetableorindexislocked.

Andfinally,wementionthe".exit"commandwhichcausesthesqlite3programtoexit.

BerkeleyDB

Technicalfeatures:

1.BerkeleyDBisanopensourceembeddeddatabasemanagementsystemthatcanprovideTheprogramprovideshigh-performancedatamanagementservices.Toapplyit,programmersonlyneedtocallsomesimpleAPIstoaccessandmanagedata.(DoesnotuseSQLlanguage)

2.BerkeleyDBprovidespracticalAPIinterfacesformanyprogramminglanguages,includingC,C++,Java,Perl,Tcl,PythonandPHP.AlloperationsrelatedtothedatabaseareperformeduniformlybytheBerkeleyDBfunctionlibrary.

3.BerkeleyDBisportableandflexible,andcanrunonalmostallUNIXandLinuxsystemsandtheirvariants,Windowsoperatingsystemsandavarietyofembeddedreal-timeoperatingsystems.BerkeleyDBislinkedtotheapplication,andendusersgenerallydonotfeelthatthereisadatabasesystematall.

4.BerkeleyDBisscalable(Scalable),whichismanifestedinmanyaspects.TheDatabaselibraryitselfisverystreamlined(lessthan300KBoftextspace),butitcanmanagedatabasesupto256TBinsize.Itsupportshighconcurrency,thousandsofuserscanmanipulatethesamedatabaseatthesametime.BerkeleyDBcanrunonembeddedsystemswithstrictconstraintswithasmallenoughspacefootprint.

BerkeleyDBisbetterthanrelationaldatabasesandobject-orienteddatabasesinembeddedapplications.Therearetworeasonsforthis:

(1)BecausethedatabaselibraryisatthesameaddressastheapplicationRuninspace,sodatabaseoperationsdonotrequireinter-processcommunication.Thecostofprocesscommunicationbetweendifferentprocessesonamachineorbetweendifferentmachinesinanetworkismuchgreaterthanthecostoffunctioncalls;(2)becauseBerkeleyDBusesoneforalloperationsGroupAPIinterface,sothereisnoneedtoparseacertainquerylanguage,andthereisnoneedtogenerateanexecutionplan,whichgreatlyimprovestheoperatingefficiency.

DomesticembeddeddatabaseOpenBASELite

OpenBASELiteisanembeddeddatabaseproductdevelopedbyNeusoftGroupCo.,Ltd.Itisatypicallightweightdatabase.Thesizeofthecustomizeddatabaseenginescalesbetween250KBand600KB.Itcansupportmultipledesktopoperatingsystems,mainstreamembeddedsystemplatformsanddifferentprocessors.Asafull-featuredrelationaldatabasesystem,OpenBASELitesupportsstandardSQLsyntax,ACIDtransactionfeatures,backup/restoreandotherfunctions,providesstandardizeddevelopmentinterfacesJDBC,ODBC,andcancontinuetouserelationaldatabaseexperienceinanembeddedenvironment.Forapplicationdevelopment.OpenBASELiteprovidesamemorydatabaseoperatingmode,providinghigh-speeddataaccessandupdatecapabilities.

Productfeatures

1.Completedatamanagementfunctions

OpenBASELiteembeddeddatabasehascompletedatamanagementfunctions,providingasubsetoftheSQL92standardSupport;providesupportforstandarddatatypesandBLOB/CLOBtypes;supportdatabaseintegritycontrol;havecompletedatamanagementcapabilitiesthatcanhandleGB-leveldata;andprovidespatialdatamanagementcapabilities.

2.Broadplatformversatility

OpenBASELiteembeddeddatabasecanrunonWindows2000/2003/XP/Vista/Win7,WindowsMobile5&6,WindowsCE,Linux,EmbeddedLinux,VxWorks,Symbian,Androidandotheroperatingsystemplatforms.

3.Tinycorekernel

OpenBASELiteembeddeddatabasehasamicro-kernelfeature,whichcanbecustomizedandtailoredaccordingtoneeds,andthekernelsizeisscalablebetween250KBand600KB.

4.Truezeromanagement

ThereisnoneedtoconfigurethedatabaseduringtheuseoftheOpenBASELiteembeddeddatabase,and"zeromanagement"isimplementedinmobileterminalapplications.

5.Excellentprocessingperformance

OpenBASELiteembeddeddatabasecanbeusedasamemorydatabasetoachievehigh-speeddataaccessandupdate,andtheprocessingtimeofasingledatadoesnotexceed15us;Intermsofconcurrentprocessingperformance,itprovidesconcurrentaccesscontroloflibrary-levellocksandtable-levellocks,whichimprovestheprocessingperformanceofconcurrentaccesstothedatabasebyprocessesandmultiplethreads.

6.Sufficientsecurityguarantee

OpenBASELiteembeddeddatabasesupportsuseridentityauthenticationandautonomousaccesscontrolofdatabaseobjects,whichcaneffectivelypreventillegalaccesstouserdata;support128-bitAESstorageencryptiontoensurethesecurityofdatabasefiles.

7.Fastfailurerecovery

ProvideslogandfailurerecoverymechanismstoeffectivelyguaranteetheACIDcharacteristicsofthetransaction.Inaddition,italsoprovidesonlinehotbackupandmaster-slavereplicationofthedatabase.,Sothatuserscansimplyandquicklyimprovethereliabilityoftheapplicationsystem.

8.Standardaccessinterface

OpenBASELiteembeddeddatabaseprovidesCAPIinterfaceandJDBC/ODBCstandardaccessinterfaceforthedevelopmentofembeddedapplications.

9.Richpracticaltools

OpenBASELiteprovidesawealthofpracticaltoolsfordatabasemanagement,includingdatabasegraphicmanagementtools,databasebuildingtools,querytools,backupandrecoverytools,importExporttoolsanddatabaseencryptionanddecryptiontools,etc.

Introductiontoembeddeddatabasesthatcanbeusedformobiledevelopment

Embeddeddatabasesarelightweight,independentlibraries,noservercomponents,nomanagement,asmallcodesize,andLimitedresourcerequirements.Therearecurrentlyseveralembeddeddatabasesthatyoucanuseinmobileapplications.Let'stakealookatthesemostpopulardatabases.