m DMc@s dZddgZdZdZdkZdkZdkZdkZdkZdk Z dk Z dk Z dk Z dk Z dkZdkZdkZdkZdkZdkZdkZdkZdkZdkZdkZdkZeieieieieifZydk l!Z!Wn%e"j odk#Z#dZ!nXydk$Z$e%Z&Wne"j o e'Z&nXydk(Z(e%Z)Wne"j o e'Z)nXydk*Z*e%Z+Wne"j o e'Z+nXei,dZ-d Z.e i/Z0e i1Z2ei3d Z4ei3d Z5ei3d Z6ei3d Z7ei3dZ8ei3dZ9gZ:ydk;l<Z=e:i>dWnTe"j oHydk=l<Z=e:i>dWn e"j oe-i?dnXnXydk@iAZAe:i>dWn e"j oe-i?dnXydkBZBe:i>dWn e"j oe-i?dnXydkCZCe:i>dWn e"j oe-i?dnXydkDZDe:i>dWn e"j oe-i?dnXydkEZEe:i>dWn e"j oe-i?dnXy'dkFZFe:i>de-iGdWn e"j oe-i?d nXy'dkHZHe:i>d!e-iGd"Wn e"j oe-i?d#nXyGd$kIlJZJdkKZLd%kMlNZNe:i>d&e-iGd'e%ZOWn&e"j oe-i?d(e'ZOnXydkPZPe:i>d)Wn e"j oe-i?d*nXyqd+kQlRZRd,kSlTZUd-kVlWZWlXZXd.kYlZZZd/k[l\Z\e:i>d0d1eUi]fd2YZ^Wne"j onXd3e_fd4YZ`d5e`fd6YZad7eafd8YZbd9ebfd:YZcd;eafd<YZdd=eafd>YZed?eefd@YZfdAeafdBYZgdCeafdDYZhdEehfdFYZidGeafdHYZjdIejfdJYZkdKeafdLYZldMeafdNYZmdOZndPeafdQYZodReofdSYZpdTeafdUYZqdVfdWYZrdXfdYYZsdZesedfd[YZtd\eafd]YZud^e_fd_YZvd`eufdaYZwydkxZxe:i>dbWn e"j oe-i?dcnXddZydeZzdfeufdgYZ{dhZ|ydk}Z}e:i>diWne-i?djnXdkeufdlYZ~hdmeb<dneb<doed<dpee<dqeg<dreh<dsei<dtem<duel<dvej<dwej<dxeo<dyep<dzeq<d{ec<d|ec<d}ef<d~ew<dew<det<de{<de{ License: LGPLv3 (http://www.gnu.org/licenses/lgpl.html) Thanks to * Niall Sweeny for MS SQL support * Marcel Leuthi for Oracle support * Denes * Chris Clark * clach05 * Denes Lengyel * and many others who have contributed to current and previous versions This file contains the DAL support for many relational databases, including SQLite, MySQL, Postgres, Oracle, MS SQL, DB2, Interbase, Ingres Completely refactored by MDP on Dec, 2010 TODO: - create more functions in adapters to abstract more - fix insert, create, migrate - move startswith, endswith, contains into adapters - handle _lastsql (where?) Example of usage: >>> # from dal import DAL, Field ### create DAL connection (and create DB if not exists) >>> db=DAL(('mysql://a:b@locahost/x','sqlite://storage.sqlite'),folder=None) ### define a table 'person' (create/aster as necessary) >>> person = db.define_table('person',Field('name','string')) ### insert a record >>> id = person.insert(name='James') ### retrieve it by id >>> james = person(id) ### retrieve it by name >>> james = person(name='James') ### retrieve it by arbitrary query >>> query = (person.name=='James')&(person.name.startswith('J')) >>> james = db(query).select(person.ALL)[0] ### update one record >>> james.update_record(name='Jim') ### update multiple records by query >>> db(person.name.like('J%')).update(name='James') 1 ### delete records by query >>> db(person.name.lower()=='jim').delete() 0 ### retrieve multiple records (rows) >>> people = db(person).select(orderby=person.name,groupby=person.name,limitby=(0,100)) ### further filter them >>> james = people.find(lambda row: row.name=='James').first() >>> print james.id, james.name 1 James ### check aggrgates >>> counter = person.id.count() >>> print db(person).select(counter).first()(counter) 1 ### delete one record >>> james.delete_record() 1 ### delete (drop) entire database table >>> person.drop() Supported field types: id string text boolean integer double decimal password upload blob time date datetime, Supported DAL URI strings: 'sqlite://test.db' 'sqlite:memory' 'jdbc:sqlite://test.db' 'mysql://root:none@localhost/test' 'postgres://mdipierro:none@localhost/test' 'jdbc:postgres://mdipierro:none@localhost/test' 'mssql://web2py:none@A64X2/web2py_test' 'mssql2://web2py:none@A64X2/web2py_test' # alternate mappings 'oracle://username:password@database' 'firebird://user:password@server:3050/database' 'db2://DSN=dsn;UID=user;PWD=pass' 'firebird://username:password@hostname/database' 'firebird_embedded://username:password@c://path' 'informix://user:password@server:3050/database' 'informixu://user:password@server:3050/database' # unicode informix 'google:datastore' # for google app engine datastore 'google:sql' # for google app engine with sql (mysql compatible) For more info: help(DAL) help(Field) tDALtFieldiiN(s web2py_uuidcCsttiS(N(tstrtuuidtuuid4(((t3/home/camdpams_www/Products_Activation/gluon/dal.pyt web2py_uuidss web2py.dalcCsdS(Ni((((Rtss^(\w+)(\:\w+)*s^[\w_]+\.[\w_]+$sQ(?P[\w\-]+)\.(?P[\w\-]+)\.(?P[\w\-]+)\.(?P\w+)\.\w+$s['"\s;]+s(?tZhZedZedZdZdZRS(NcCs |t_dS(N(tfoldertthread(R2((Rt set_folder@scCsttdpdSnxtiotii}t||t}|i oXt i t i |i}t||i jo|i|it}nt in|ot|dqqWdS(s; to close cleanly databases in a multithreaded environment t instancesNtclose(thasattrR3R5tpoptinstancetgetattrtactiontTruetreallyt pool_sizet sql_lockertacquireR1tpoolsturitpooltlentappendt connectiontFalsetrelease(R;R9RCR=((Rtclose_all_instancesFs$    cCspttdoti|_nd|_t_to5|io+tii|i oti|i ndS(s< this actually does not make the folder. it has to be there R2tN( R7R3R2RRGtostpathtexiststmkdirt_folder(R((Rtfind_or_make_work_folder[s (cCs|ip||_n}|i}ti|tijogti|tfRFRBR?R@R1RAR8RHR7R3R5RE(RRQRB((Rtpool_connectionfs      (R-R.RAt staticmethodR4RIRPRR(((RR1<s  t BaseAdaptercBstZeZeZeZeZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<ddZ'd?Z(d@Z)dAZ*dBZ+ddCZ,ddDZ-ddEZ.ddFZ/ddGZ0ddHZ1dIZ2dJZ3dKZ4dLZ5dMZ6dNZ7dOZ8dPZ9dQZ:ddRZ;dSZ<d6dTZ=dUdVZ>dWZ?dXZ@dYZAdZZBd[ZCd\ZDd]ZEd^ZFdd_ZGdd`ZHdaZIdbZJdcZKddZLdeZMdfZNdgZOdhZPdiZQdjZRdkZSdlZTdmZUdnZVdoZWdpZXdqZYd"ddrZZe dsZ[RS(tNtbooleansCHAR(1)tstringsCHAR(%(length)s)ttexttTEXTtpasswordtblobtBLOBtuploadtintegertINTEGERtdoubletDOUBLERtdatetDATEttimetTIMEtdatetimet TIMESTAMPtids!INTEGER PRIMARY KEY AUTOINCREMENTt referencesAINTEGER REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)ss list:integers list:stringslist:referencecCstii|S(sP to be used ONLY for files that on GAE may not be on filesystem N(RKRLRMtfilename(RRi((Rt file_existsstrbcCsvt||}to\|oU|djoti|tiqr|d joti|tiqrt dn|S( sP to be used ONLY for files that on GAE may not be on filesystem trRktwtwbtasUnsupported file_open modeN(Rlsrb(RmswbRo( topenRitmodetfileobjthave_portalockertlockt portalockertLOCK_SHtLOCK_EXt RuntimeError(RRiRqRtRr((Rt file_opens   cCs8|o-to|oti|n|indS(sP to be used ONLY for files that on GAE may not be on filesystem N(RrRstunlockRuR6(RRrRz((Rt file_closes cCsti|dS(N(RKtunlinkRi(RRi((Rt file_deletesisUTF-8cCs|S(N(R(R((RRsc Csh||_d|_||_||_||_||_dtfdY}||_ ||_ dS(NR$tDummycBstZdZdZRS(NicCsdS(NcOsgS(N((Rotb((RRs((RR#((Rt __getattr__s(R-R.t lastrowidR(((RR~s( tdbRtdbengineRBR>R2tdb_codectobjectR~RFtcursor( RRRBR>R2Rtcredential_decodert driver_argsR~((RRs       cCsd|S(Ns %s_sequence(t tablename(RR((Rt sequence_namescCsd|S(Ns %s_sequence(R(RR((Rt trigger_namesc"Cs"g}h}h}h}|i}x|D]}|i}t |i t o|i i p |i i }n|i ido|i di}|i||i}t|do |id\}}|i|}||}||ijp |io|i|i d td|i}|i oFt |idjo0||joh||d,!i?d+i@|} n |iB} |p| Sn|i8id-o d|_Dnat |tEotFiGi2| ||_Dn5tFiGi2| d.tHiI|i8iJ|f|_D|iDo1tFiGi2| d/|_K|iL|iKd0}nd}|iD p|iN|iD o|iDo5|iOd1tPiPiQiR|iO| d2n|p!|iT| ||iiUn|iDo[|iL|iDd3}tWiX|||iY||o|iOd4qh|iOd5qn|iL|iDd6}ytWiZ|}Wn=t\j o1|iY||iY|t]d7|iDnX|iY|||jo#|i^|||||d8|n|iY|| S(9NRhi t _primarykeyt.i tlengthis reference FKtconstraint_namet table_namet field_namet foreign_keys%s (%s)ton_delete_actions(%s)islist:referenceiRiit,iR Rs$Field: unknown field type: %s for %sRgs NOT NULLs UNIQUEsNOT NULLs%s %st;tmysqlsPRIMARY KEY(%s)s" ENGINE=InnoDB CHARACTER SET utf8;s, s reference TFKs, t foreign_tables#CREATE TABLE %s( %s, %s) %ssCREATE TABLE %s( %s )%ss sqlite:///tutf8t/s sqlite:memorys %s_%s.tablessql.logRostimestamp: %s s Rmsfaked! s success! RlsFile %s appears corruptedt fake_migrate(_tfieldst sql_fieldstsql_fields_auxtTFKttablet _tablenameRtfieldR,tkR(ttypet SQLCustomTypetnativetftypet startswithtstript referencedRRR7tsplitt rtablenamet rfieldnamet_dbtrtabletrfieldRtuniquettypestdictRRDtondeletet_[1]RtintR Rt SyntaxErrortnotnullt ALLOW_NULLtdefaultR$tNOT_NULLtnot_nulltreplaceREtotherRtjointrfieldstpkeystfkeyst PRIMARY_KEYtqueryRBtsystgetfilesystemencodingtlocaletgetdefaultlocalet path_encodingtrfindtdecodetencodetdbpathR2tmigratet_dbtRRKRLthashlibtmd5t hexdigestt _loggernameRytlogfileRjtwriteRettodayt isoformatRtcreate_sequence_and_triggerstcommitttfiletcPickletdumpR{tloadtsql_fields_oldtEOFErrorRxt migrate_table("RRRRt polymodelRRRRRRRRRRRRRRRRR RRRRRRRRRRRR((Rt create_tables    $!   @+ (      %/$2  4           c Cs]|i}td|iD}td|iD}td|iD}|i} x,|D]$}|| jo| i |qpqpW|i djod|} nd} t} ti|} x"| D]}||jo8||| |tsccs*x#|]\}}|i|fVqWdS(N(RRRR(RRR((RRusccs*x#|]\}}|i|fVqWdS(N(RRRR(RRR((RRvstmssqls; ALTER TABLE %s ADD s, ADD sALTER TABLE %s ADD %s %s;s, tsqlitetfirebirdsALTER TABLE %s DROP COLUMN %s;sALTER TABLE %s DROP %s;RhsINT,s INT NOT NULL,sALTER TABLE %s ADD %s__tmp %s;sUPDATE %s SET %s__tmp=%s;sUPDATE %s SET %s=%s__tmp;s#ALTER TABLE %s DROP COLUMN %s__tmp;sALTER TABLE %s DROP %s__tmp;stimestamp: %s s t_lastsqlRms success! sfaked! Rtoracle(R(R(/RRRRRtitemsRRtkeystkeyRERRtnew_addRGtfields_changedtcopytsql_fields_currentRRR$R(RRRtttttR<RRReRRRRt sub_queryRtexecutet_adaptertcommit_on_alter_tableRRyRRRRR{(RRRRRRRRRRRRRRRRRR((RRisn    *  mdd      cCsd|i|S(Ns LOWER(%s)(Rtexpandtfirst(RR((RtLOWERscCsd|i|S(Ns UPPER(%s)(RRR(RR((RtUPPERscCsd||i|fS(NsEXTRACT(%s FROM %s)(twhatRRR(RRR((RtEXTRACTscCsd||i|fS(Ns%s(%s)(RRRR(RRR((Rt AGGREGATEscCsdS(Ns LEFT JOIN((R((Rt LEFT_JOINscCsdS(NsRandom()((R((RtRANDOMscCsd|i||S(NsNOT NULL DEFAULT %s(Rt representRt field_type(RRR((RRscCsd|i|S(NsCOALESCE(%s,0)(RRR(RR((Rt COALESCE_ZEROscCsdS(NRJ((R((RRscCs"d|i||d|dfS(NsSUBSTR(%s,%s,%s)ii(RRRt parameters(RRR((Rt SUBSTRINGscCsd|S(NsPRIMARY KEY(%s)(R(RR((RRscCs d|gS(NsDROP TABLE %s;(R(RRRq((Rt_dropsRJcCs|io|i|id}n|i||}x:|D]2}|io|i |dn|i |q<W|i i |i |i=|i i|i ii|i=|i i|i|io!|i|i|i dndS(NRos s success! (RRRRyRRRRqtqueriesRRRRRRttablestindext_update_referenced_byR}(RRRqRRR((Rtdrops     csFdid|D}did|D}d|||fS(NRccs!x|]\}}|iVqWdS(N(RRQRR,(RRRQ((RRsc#s-x&|]\}}i||iVqWdS(N(RRQRRRR(RRRQ(R(RRssINSERT INTO %s(%s) VALUES (%s);(RRRtvaluesR(RRRRR ((RRt_insertsc Cs|i||}y|i|Wn9tj o-}t||i odSn|nXt |do6t g}|iD]}||||fq~Sn|i|}t|tp|Snt|}|d|_|_|S(NR(RR RRRRt ExceptionteR(tintegrity_error_classR$R7RRRRRRgRt Referencetridt_tablet_record( RRRRRgRRRR((Rtinserts 6 cCs+g}|D]}||i||q ~S(N(RRtitemRRR(RRRRR((Rt bulk_insertscCsd|i|S(Ns(NOT %s)(RRR(RR((RtNOTscCs d|i||i|fS(Ns (%s AND %s)(RRRtsecond(RRR((RtANDscCs d|i||i|fS(Ns (%s OR %s)(RRRR(RRR((RtORscs_t|todi|d fSndidid|DfS(Ns (%s IN (%s))iRc#s'x |]}i|iVqWdS(N(RRRRRR(RR(RR(RRs(R(RRRRRR(RRR((RRRtBELONGSscCs#d|i||i|dfS(Ns (%s LIKE %s)RV(RRRR(RRR((RtLIKE scCs'd|i||i|ddfS(Ns (%s LIKE %s)t%RV(RRRR(RRR((Rt STARTSWITH#scCs'd|i||id|dfS(Ns (%s LIKE %s)RRV(RRRR(RRR((RtENDSWITH&scCs|id jo$dt|iddd}nD|iido0dt|iddiddd }nd |i||i|dfS( NRVRWRs%%slist:s%|t|s||s|%s (%s LIKE %s)(sstringstext( RRRRRRRRR(RRRR((RtCONTAINS)s $0cCsH|djod|i|Snd|i||i||ifS(Ns (%s IS NULL)s (%s = %s)(RR$RRRR(RRR((RtEQ0s cCsH|djod|i|Snd|i||i||ifS(Ns(%s IS NOT NULL)s (%s <> %s)(RR$RRRR(RRR((RtNE5s cCs&d|i||i||ifS(Ns (%s < %s)(RRRRR(RRR((RtLT:scCs&d|i||i||ifS(Ns (%s <= %s)(RRRRR(RRR((RtLE=scCs&d|i||i||ifS(Ns (%s > %s)(RRRRR(RRR((RtGT@scCs&d|i||i||ifS(Ns (%s >= %s)(RRRRR(RRR((RtGECscCs&d|i||i||ifS(Ns (%s + %s)(RRRRR(RRR((RtADDFscCs&d|i||i||ifS(Ns (%s - %s)(RRRRR(RRR((RtSUBIscCs&d|i||i||ifS(Ns (%s * %s)(RRRRR(RRR((RtMULLscCs&d|i||i||ifS(Ns (%s / %s)(RRRRR(RRR((RtDIVOscCs&d|i||i||ifS(Ns (%s %% %s)(RRRRR(RRR((RtMODRscCsd|i||fS(Ns (%s AS %s)(RRRR(RRR((RtASUscCs d|i||i|fS(Ns%s ON %s(RRRR(RRR((RtONXscCsd|i|S(Ns%s DESC(RRR(RR((RtINVERT[scCs d|i||i|fS(Ns%s, %s(RRRR(RRR((RtCOMMA^scCst|tot|Snt|ttfo\|idj o|i|i |iSq|i dj o|i|i Sq|iSnt|o|i ||SnYt|t tfo8dig}|D]}||i ||q~Sn t|SdS(NR(R(t expressionRRt ExpressiontQueryRR$topRRRRtlistttupleRRR(RR2RRR((RRas8cCsti|}|i|ds ORDER BY %sRs%s.%s(sorderbysgroupbyslimitbysrequiredscachesleftsdistinctshaving(Jtsett attributesRRRt new_fieldsRRR(R:RRERRRRORRR)t table_fieldtmatchRttntfnRRDRRRQRtsql_ftcRt _colnamesR@tsql_otsql_stgetRGRVRWRRRQRXRSR<RtcommandR7R6RR3Rtjointtjoinonttables_to_mergeRERtfromkeysRtjoinontR8timportant_tablenamestexcludedtsql_tRtxorifyRR7RRJR,Rtselect_limitby(%RRRR[RR\RRRWRR_RRnRmRdRiRaRhRORjRoRR@R`ReRQRbRQRSRXRRRgRRlRRRV((Rt_selects   33    85:'924<"c CsF|o(|\}}|d|||f7}nd|||||fS(Ns LIMIT %i OFFSET %isSELECT %s %s FROM %s%s%s;(RStlmintlmaxRdReRaRoR@( RReRaRoR@RdRSRtRs((RRq s c sdi||||iddop|d\} }|d=i d}|djo|pt i|i}| |d|}n }t|tot|}n|iddpd }i||dd}i|iS( sG Always returns a Rows object, even if it may be empty csi|iiS(N(RRRCRtfetchall(RC(R(Rtresponse*s RURics S(N(RvRC((RvRC(RR3sRSiN(i(RvRRrRRR[RCRfR$t cache_modelt time_expireRBRRRRtrowsR(R7R6RStrowslicetparseRc( RRRR[RyRCRSRRxRvRw((RRvRCRRI&s ) cCs|i|}|od|i|}nd}di|}|oJt |t t fot |}n|i|}d|||fSnd||fS(Ns WHERE RJRs#SELECT count(DISTINCT %s) FROM %s%ssSELECT count(*) FROM %s%s(RRRRORR@RRoRWR(R6R7Rptsql_d(RRRWR|RORoR@((Rt_count<scCs*|i|i|||iidS(Ni(RRR}RRWRtfetchone(RRRW((RtcountJscCst}t|to|i|inyt|ttfob|i djo|i |i|i }n|i djo|i |i|i }qnt|S(N(RZRR(RRtaddRR3R4RR$tunionRRR6(RRR((RROs #cCs |iiS(N(RRFR(R((RRZscCs |iiS(N(RRFtrollback(R((RR]scCs |iiS(N(RRFR6(R((RR6`scCsdS(N((RR((Rtdistributed_transaction_begincscCs|iidS(N(RRFtprepare(RR((RRfscCs|iidS(N(RRFR(RR((Rtcommit_preparediscCs|iidS(N(RRFR(RR((Rtrollback_preparedlscCsdS(Ns, ADD ((RR((Rt concat_addoscCsd||fS(Ns%s_%s__constraint(RR<(RRR<((RRrscKs|i|dS(N(RRR(RRRtargs((RRuscOs\|d|i_ti}|ii||}|ii i |dti|f|S(Ni( RoRRRRctt0RRRtrett_timingsRE(RRoRRR((Rt log_executexs  'cOs|i||S(N(RRRoR(RRoR((RRscCs't|to |}nt|to|i|Snt|ttfot|Sn|i do8|p g}nt|t t fp |g}qnt|t t fot |}n|djodSn|djo%|d ddddgjodSn|i||}|djo|Sn|d jo:|o+t|d id d gjod SqdSn|djp |djott|Sn|i dot|Sn|i do`|iddjot|Sn)t|ttfot|dSntt|Sn"|djott|Snt|to|i|i}n|djotit|}n |djo@t|titifo|id }qt|}n|djont|tio |id i dd}qt|tio|id d}qt|}nE|djo7t|ti!o|id }qt|}nt|tpt|}ny|i"|iWn"|i"di|i}nXd |i d!d"S(#Nslist:tNULLRJitstttetpatupRUitFRs'T's'F'RgR]RRhRiR_RZRai ReitTR>s 00:00:00Rctlatin1s'%s't's''(#R(tobjt CALLABLETYPESt fieldtypeRtencoderR3RRRR6R7t bar_encodeR$Rtrepresent_exceptionsRltupperRtfindtreprtRowRtfloattunicodeRRtbase64t b64encodeReRaRRRcR(RRRRl((RRsz   *  *      cCsdS(N(R$(RRR((RRscCsdS(N(R$(RR((RRscCs tdS(N(RR$(R((RRscCs|S(sM by default this function does nothing, overload when db does not do slicing N(Ry(RRytminimumtmaximum((RRzsc' Cs5|i}g}g}xt|D]\}} t}xst|D]e\}}| |}ti||pd|jot|d|iPD]3\}}&|||&}tS||| j||strawrows(ZRRt virtualtablestnew_rowst enumerateRyRtrowRtnew_rowtcolnamestjtcolnameR#R]R^tretcompiletselect_as_parsertsearchtnew_column_nameR$tgroupst column_nametsetattrRRR<RRRRR(RRt _db_codecRRRtcolsetRERtdecoderRRtrefereeRRRRR<RRGReRaRRRtytmRRct time_itemsRDthtmitst blob_decodeRt b64decodetdecimalsRRRtbar_decode_integertbar_decode_stringR,RgRt delete_recordRHt referee_tablet referee_nametSettRowstrowsobjt virtualfieldsRtsetvirtualfieldstKeyError('RRyRRRRRRRRgRRRRRRRRRRRRRRRRRRRRR#RRR<RRRRR((RR{s                 *4I!D!IG&;     (\R-R.tINFINITYt maxcharlengthRGRtsupport_distributed_transactiontuploads_in_blobRRjR<RyR{R}R$RRRRRRRRRRRRRRRRRR R RRRRRRRRR R"R#R$R%R&R'R(R)R*R+R,R-R.R/R0R1RR;R=R?RBRERFRNRPRrRqRIR}RRRRR6RRRRRRRRRRRRRRzR{(((RRT}s     [                                               W                 C   t SQLiteAdaptercBsPtZdZedZddddhdZddZd ZRS( NcCsd||i|fS(Nsweb2py_extract('%s',%s)(RRRR(RRR((RRVscCsshdd<dd<dd<d d<d d<dd<}y%||\}}t|||!SWn dSnXdS(Ntyeariitmonthiitdayii thouri i tminuteiiRii(ii(ii(ii (i i (ii(ii(RtlookupRRRRR$(RRRRR((Rtweb2py_extractYs <isUTF-8cCs|S(N(R(R((RRjsc Cs/||_d|_||_||_||_||_|iti pt i d} |i do d} nS|idd} | ddjo.tii|ii| id| } nd |jot|d R2RRPRRRRRRRRRKRLRRRRRGRRRRFRtcreate_functionRR( RRRBR>R2RRRRRR((RRis&        .  RJcCs|i}d|d|gS(NsDELETE FROM %s;s,DELETE FROM sqlite_sequence WHERE name='%s';(RRR(RRRqR((RR=s cCs |iiS(N(RRR(RR((RRs( R-R.RRSRR$RR=R(((RRTs   tJDBCSQLiteAdaptercBs,tZddddhdZdZRS(NisUTF-8cCs|S(N(R(R((RRsc Cs||_d|_||_||_||_||_|iti pt i d} |i do d} nS|idd} | ddjo.tii|ii| id| } n| |d }|i||ii|_|iid d tidS( NRis sqlite:memorys:memory:s://iRRcCs#titiiid||S(Ns jdbc:sqlite:(RRtjavaRCt DriverManagert getConnectionRR(RR((RRsRi(RRRRBR>R2RRPRRRRRRRRRKRLRRRRRRRRFRRRR( RRRBR>R2RRRRRR((RRs"        . cCs|i|d S(Ni(RRRo(RRo((RRs(R-R.R$RR(((RRst MySQLAdaptercBs:tZeiddZdZeZeZ hdd<dd<dd<d d<d d <d d<d d<dd<dd<dd<dd<dd<dd<dd<dd<dd<dd[^:@]+)(\:(?P[^@]*))?@(?P[^\:/]+)(\:(?P[0-9]+))?/(?P[^?]+)(\?set_encoding=(?P\w+))?$sInvalid URI string in DAL: %stusers User requiredRYRJthostsHost name requiredRsDatabase name requiredtportt3306tcharsetRtpasswdcsii|S(N(RtdriverRR(R(R(RRssSET FOREIGN_KEY_CHECKS=1;s$SET sql_mode='NO_BACKSLASH_ESCAPES';(RRRRBR>R2RRPRRRR^RRtgroupRRYRRRRRRERRRRRRFRR(RRRBR>R2RRRRRRRRRYR((RRRsH                cCs$|idt|iidS(Nsselect last_insert_id();i(RRRRR~(RR((RRs (R-R.tglobalsRfR$RRR<RRRRRRRRRRRRR(((RRs        )tPostgreSQLAdaptercBs4tZeZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<dd[^:@]+)(\:(?P[^@]*))?@(?P[^\:@/]+)(\:(?P[0-9]+))?/(?P[^\?]+)(\?sslmode=(?P.+))?$sInvalid URI string in DALRs User requiredRYRJRsHost name requiredRsDatabase name requiredRt5432tsslmodesAdbname='%s' user='%s' host='%s'port=%s password='%s' sslmode='%s's4dbname='%s' user='%s' host='%s'port=%s password='%s'cCsti||S(N(tpsycopg2RtmsgR(RR((RR[stUTF8sBEGIN;s!SET CLIENT_ENCODING TO 'UNICODE';s#SET standard_conforming_strings=on;(RRRRBR>R2RRPRRRR^RRRRRYRRRRRRRRRFtset_client_encodingRR(RRRBR>R2RRRRRRRRRRYR((RR8sF                cCs+|id|it|iidS(Nsselect currval('%s')i(RRRt_sequence_nameRRR~(RR((RRdscCs#d|i||i|dfS(Ns (%s ILIKE %s)RV(RRRR(RRR((RRhscCs'd|i||i|ddfS(Ns (%s ILIKE %s)RRV(RRRR(RRR((RRkscCs'd|i||id|dfS(Ns (%s ILIKE %s)RRV(RRRR(RRR((RR nscCs|id jo$dt|iddd}nD|iido0dt|iddiddd }nd |i||i|dfS( NRVRWRs%%slist:s%|R!s||s|%s (%s ILIKE %s)(sstringstext( RRRRRRRRR(RRRR((RR"qs $0(R-R.R<RRRRRRRRRR$RRRRR R"(((RRs       ,    tJDBCPostgreSQLAdaptercBs#tZddddhdZRS(NisUTF-8cCs|S(N(R(R((RR{scCs||_d|_||_||_||_||_|i|idd}t i di |} | p t dn| id} | p t dn| id} | p d } n| id } | p t d n| id }|p t d n| idpd} d| | |f| | f}||d}|i||iid|ii|_|id|iddS(NRs://is\^(?P[^:@]+)(\:(?P[^@]*))?@(?P[^\:/]+)(\:(?P[0-9]+))?/(?P.+)$sInvalid URI string in DALRs User requiredRYRJRsHost name requiredRsDatabase name requiredRRsjdbc:postgresql://%s:%s/%scCsti||S(N(RRRR(RR((RRsRsBEGIN;s!SET CLIENT_ENCODING TO 'UNICODE';(RRRRBR>R2RRPRRRR^RRRRRYRRRRRRRRFRRR(RRRBR>R2RRRRRRRRRYR((RRzs>              (R-R.R$R(((RRxst OracleAdaptercBsCtZeZhdd<dd<dd<dd<dd<d d<d d <d d <dd<dd<dd<dd<dd<dd<dd<dd<dd %isWHERE w_row > %isg%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;sSELECT %s %s FROM %s%s%s;( RSRsRtRDR@t sql_w_rowReRaRoRd( RReRaRoR@RdRSRtRRs((RRqs  *cCsHti|||}t|djod|d |d f}n|S(Nis%s_%s__constrainti i(RTRRRR<RD(RRR<R((RRscCs |djo!tit|}d|Sn|djoHt|titifo|id }n t|}d|Sn|djovt|tio |id i dd }n8t|tio|id d }n t|}d |SndS( NRZs :CLOB('%s')Rai sto_date('%s','yyyy-mm-dd')ReiRR>s 00:00:00s%to_date('%s','yyyy-mm-dd hh24:mi:ss')( RRRRRR(ReRaRRR$(RRR((RRs          isUTF-8cCs|S(N(R(R((RRsc Cs||_d|_||_||_||_||_|i|idd}d|jot |dR2RRPRRR<RRRRFRR( RRRBR>R2RRRR((RRs          s5[^']*('[^']*'[^']*)*\:(?PCLOB\('([^']+|'')*'\))cCsg}d}xto|ii|}|pPn||id t |||i d}|i |i ddd!i dd|d7}qW|i|d |S(Nitclobiis''Ri(RRR<Rt oracle_fixR^RgRtstartRtendRERRR(RRgRRR((RRs.)cKsW|i}|i}|i}|i||id||id|||fdS(Ns:CREATE SEQUENCE %s START WITH 1 INCREMENT BY 1 NOMAXVALUE;ssCREATE OR REPLACE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW BEGIN SELECT %s.nextval INTO :NEW.id FROM DUAL; END; ( RRRRRt _trigger_nameRRRR(RRRRRRR((RR s     cCs1|i}|id|t|iidS(NsSELECT %s.currval FROM dual;i(RRRRRRRR~(RRR((RRs (R-R.RGRRRRRRRRRqRRR$RRRR RRR(((RRs          t MSSQLAdaptercBs@tZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<dd<dd<d d.+)$s$Parsing uri string(%s) has no resulttdsns DSN requiredsNdGpatch errorsDSN=%sst^(?P[^:@]+)(\:(?P[^@]*))?@(?P[^\:/]+)(\:(?P[0-9]+))?/(?P[^\?]+)(\?(?P.*))?$sInvalid URI string in DAL: %sRs User requiredRYRJRsHost name requiredRsDatabase name requiredRt1433tDRIVERs {SQL Server}turlargss%(?P[^=]+)=(?P[^&]*)targvaluetargkeyRs%s=%ss.SERVER=%s;PORT=%s;DATABASE=%s;UID=%s;PWD=%s;%scCsti||S(N(tpyodbcRtcnxnR(RR((RRs(*RRRRBR>R2RRPRRRR^RRRRRtloggerterrorRRRYRRtargsdictRt argpatterntfinditertargmatchRRRRRtaktavRRt fake_connectRRRFR(RRRBR>R2RRRR%RR"RR$RRRRR#R RRRYRRR((RRPs`              ,@ cCs$|idt|iidS(NsSELECT SCOPE_IDENTITY();i(RRRRR~(RR((RRs cCstiS(N(RtIntegrityError(R((RRscCs$|djo ||Sn|||!S(N(RR$RyR(RRyRR((RRzs  (R-R.RRRRRRRRqRR$RGRRRRz(((RRs        ;  t MSSQL2AdaptercBstZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<dd<dd<d d[^:@]+)(\:(?P[^@]*))?@(?P[^\:/]+)(\:(?P[0-9]+))?/(?P.+?)(\?set_encoding=(?P\w+))?$sInvalid URI string in DAL: %sRs User requiredRYRJRsHost name requiredRi RsDatabase name requiredRRRs%s/%s:%scCs ti|S(N(t kinterbasdbRR(R((RRs(RRRRBR>R2RRPRRRR^RRRRRYRRRRRRERRRRRRFR(RRRBR>R2RRRRRRRRRYR((RRs@                cKsh|i}|i}|i}|i||id||id||id|||fdS(Nscreate generator %s;sset generator %s to 0;screate trigger %s for %s active before insert position 0 as begin if(new.id is null) then begin new.id = gen_id(%s, 1); end end;( RRRRRRRRRR(RRRRRRR((RRs    cCs1|i}|id|t|iidS(Ns&SELECT gen_id(%s, 0) FROM rdb$databasei(RRRRRRRR~(RRR((RRs (R-R.RGRR<RRRRRRRRRqR=R$RRR(((RR*s        % tFireBirdEmbeddedAdaptercBs#tZddddhdZRS(NisUTF-8cCs|S(N(R(R((RR&sc Cs{||_d|_||_||_||_||_|i|idd}t i di |} | pt d|in| id} | p t dn| id} | p d } n| id } | p t d n| id }|p d }nd } |itd| d| d|| d|| d ||d}|i||ii|_dS(NRs://is]^(?P[^:@]+)(\:(?P[^@]*))?@(?P[^\?]+)(\?set_encoding=(?P\w+))?$sInvalid URI string in DAL: %sRs User requiredRYRJRLs Path requiredRRRtdatabasecCs ti|S(N(R+RR(R((RREs(RRRRBR>R2RRPRRRR^RRRRRYtpathdbRRRRERRRRRRFR(RRRBR>R2RRRRRRRR.RYR((RR%s@                (R-R.R$R(((RR,#stInformixAdaptercBstZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<dd<d d<d!ds 00:00:00s%to_date('%s','yyyy-mm-dd hh24:mi:ss')( RR(RReRaRRRR$(RRR((RRus       isUTF-8cCs|S(N(R(R((RRscCs||_d|_||_||_||_||_|i|idd}t i di |} | pt d|in| id} | p t dn| id} | p d } n| id } | p t d n| id }|p t d n|| } || } d|| f}|itd| d| dt||d} |i| |ii|_dS(Ntinformixs://is\^(?P[^:@]+)(\:(?P[^@]*))?@(?P[^\:/]+)(\:(?P[0-9]+))?/(?P.+)$sInvalid URI string in DAL: %sRs User requiredRYRJRsHost name requiredRsDatabase name requireds%s@%st autocommitcCsti||S(N(t informixdbRRR(RR((RRs(RRRRBR>R2RRPRRRR^RRRRRYRRRRRERR<RRRRFR(RRRBR>R2RRRRRRRRYR((RRs<             " cCs,|ddjo|d }n|i|S(NiR(RgRR(RRg((RRscCs|iidS(Ni(RRtsqlerrd(RR((RRscCstiS(N(R5R&(R((RRs( R-R.RRRRqRR$RRRR(((RR/Ks   #  t DB2AdaptercBstZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dd<dd<dd<dd<dd<d d<d!dR2RRPRRRRRRRFR( RRRBR>R2RRRRR((RRs        cCs,|ddjo|d }n|i|S(NiR(RgRR(RRg((RRscCs(|id|t|iidS(Ns-SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM %s;i(RRRRRR~(RR((RRscCs$|djo ||Sn|||!S(N(RR$RyR(RRyRR((RRzs  ( R-R.RRRRqRR$RRRRz(((RR7s      sii***lineitemsequencet IngresAdaptercBstZhdd<dd<dd<dd<dd <d d<d d <d d<dd<dd<dd<dd<dde<dd<dd<dd<dd<d d<d!dR2RRPt_uriRtconnstrtlstripRt database_nameR=R>R$R?RRERRRRRFR(RRRBR>R2RRRR?R>RR=RDRB((RR/ s.           cKst|doOd|idig}|iD]}|d|q-~f}|i |nXd|i}|i t |}|i d||i ||i d|idfdS(NRsmodify %s to btree unique on %ss, s'%s's%s_iisqscreate sequence %sRg(R7RRRRt primarykeyRtmodify_tbl_sqlRRt tmp_seqnameRRtINGRES_SEQNAME(RRRRRRGRRF((RRJ s>  cCs2d|}|id|t|iidS(Ns%s_iisqsselect current value for %si(RRGRRRRR~(RRRG((RR[ s cCstiS(N(R@R&(R((RR` s( R-R.RHRRRRqR$RRRR(((RR9 s     tIngresUnicodeAdaptercBstZhdd<dd<dd<dd<dd <d d <d d <dd<dd<dd<dd<dd<dde<dd<dd<dd<d d<d!d<d"d %isWHERE w_row > %isd%s %s FROM (SELECT w_tmp.*, ROWNO w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNO=%i) %s %s %s;sSELECT %s %s FROM %s%s%s;( RSRsRtRDR@RReRaRoRd( RReRaRoR@RdRSRtRRs((RRq s  *cKsK|id|i|id|i|ii|if|i|dS(NsCREATE SEQUENCE %s;s9ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');(RRRRRRJR,R(RRRR((RR s&isUTF-8cCs|S(N(R(R((RR sc CsC||_d|_||_||_||_||_|i|idd}t i di |}|p t dn|id} | p t dn|id} | p d } n|id } | p t d n|id }|p t d n| | || |d} |i| |ii|_dS(Ntsapdbs://is}^(?P[^:@]+)(\:(?P[^@]*))?@(?P[^\:@/]+)(\:(?P[0-9]+))?/(?P[^\?]+)(\?sslmode=(?P.+))?$sInvalid URI string in DALRs User requiredRYRJRsHost name requiredRsDatabase name requiredcCsti|||||S(N(RMRRRYR-RR(RRYR-RR((RR s(RRRRBR>R2RRPRRRR^RRRRRYRRRRRRFR( RRRBR>R2RRRRRRRYR((RR s4             cCs+|id|it|iidS(Nsselect %s.NEXTVAL from duali(RRRRRRR~(RR((RR s( R-R.RGRRRRqRR$RR(((RRK{ s  tDatabaseStoredFilecBsJtZeZdZdZdZdZdZe dZ RS(NcCs|iidjo tdn||_||_||_|ip|iidt t _nd|_ d|_ |d jod |}|ii|}|o|dd|_ q tii|ot|di|_ q |d jotd |q ndS( NRs>only MySQL can store metadata .table files in database for nowsuCREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(512), content LONGTEXT, PRIMARY KEY(path) ) ENGINE=InnoDB;iRJRltrwRos5SELECT content FROM web2py_filesystem WHERE path='%s'sFile %s does not exist(RlsrwRo(Rlsrw(RRRRxRRiRqtweb2py_filesystemt executesqlR<RNtptdataRRyRKRLRMRptread(RRRiRqRyR((RR s&           cCs3|i|i|i|!}|it|7_|S(N(RRSRRtbytesRD(RRURS((RRT scCsq|iid|id}|djo!|i|i|!|}|_n$|i|it|i}|_|S(Ns ii(RRSRRRRRD(RRRS((Rtreadline s  !#cCs|i|7_dS(N(RRS(RRS((RR scCsZ|iid|id|i|iiddf}|ii||iidS(Ns-DELETE FROM web2py_filesystem WHERE path='%s's>INSERT INTO web2py_filesystem(path,content) VALUES ('%s','%s')Rs''(RRRQRiRSRRR(RR((RR6 s"cCsAtii|otSnd|}|i|otSntS(Ns2SELECT path FROM web2py_filesystem WHERE path='%s'( RKRLRMRiR<RRRQRG(RRiR((RRM s  ( R-R.RGRPRRTRVRR6RSRM(((RRN s     tUseDatabaseStoredFilecBs5tZdZdedZedZdZRS(NcCsti|i|S(N(RNRMRRRi(RRi((RRj sRkcCst|i||S(N(RNRRRiRq(RRiRqRt((RRy scCs|idS(N(RrR6(RRrRz((RR{ scCs+d|}|ii||iidS(Ns-DELETE FROM web2py_filesystem WHERE path='%s'(RiRRRRQR(RRiR((RR} s (R-R.RjR<RyR{R}(((RRW s  tGoogleSQLAdapterc Bs/tZdddddeedhd ZRS(Ns"google:sql://realm:domain/databaseisUTF-8cCs|S(N(R(R((RR sc Cs\||_d|_||_||_||_||_|pdtiiddd|_t i di |it d} | ptd|in| | id} | | id }| | d<|p|| d .*)/(?P.*)$s google:sql://sInvalid URI string in SQLDB: %sR9RR-cCs ti|S(N(trdbmsRR(R((RR. ss CREATE DATABASE IF NOT EXISTS %ssUSE %ssSET FOREIGN_KEY_CHECKS=1;s$SET sql_mode='NO_BACKSLASH_ESCAPES';(RRRRBR>R2RR3RRRR^RDRRRARRR9RRRRRRFRR(RRRBR>R2Rtcheck_reservedRRRRR9RR((RR s.      '%    (R-R.R$R<RGR(((RRX st NoSQLAdaptercBstZdZdZddZdZdZdZdZ dZ d Z d Z d Z d Zd ZddZddZdZdZdZdZdZdZdZdZdZdZdZdZdZdZdZ dZ!d Z"d!Z#d"Z$d#Z%d$Z&d%Z'd&Z(d'Z)d(Z*d)Z+d*Z,d+Z-d,dd-Z.RS(.Nc Cst|to |}nt|to|i|Snt|ttfo tdn|i djo t jnot|t i o|Sqn|ido8|p g}nt|ttfp |g}qn|djo%|d dddd gjodSn|djot|toB|id  o1g} |D]}| |i||qW~ }q|djot|}q|d jot|}q|ido4t|ttfo|d }nt|}q|djo9|o(t|didj o t}qt}q|djot|tip^g} t|ii dD]} | t"| q~ \} }}ti| ||}qt|tio6|i&|i'|i(} }}ti| ||}qq|djot|ti)pg} t|ii dd D]} | t"| qP~ }t+|djo|\}}} n|dg\}}} ti)||| }qq|djot|tipg} t|d ii dD]} | t"| q ~ \} }}g} t|dii dd D]} | t"| qV~ }x%t+|djo|i/dquW|\}}} ti| ||||| }qq|djoq|idoZ|djo$t|ttf o |g}ng} |D]} | t| q,~ Sq|idoZ|djo$t|ttf o |g}ng} |D]} | t"| q~ Sqt|to|i0d}qt|t1pt1|}qn|S(Nsnon supported on GAEsgoogle:datastoreslist:RJiRRRRR6R]RgR_RhRUiRRaRRcRiRei i RZs list:stringR(sintegersid(2R(RRRRRR3RRRRRR*tPropertyRR6R7R$RtoRtlongRRRRRR<RGReRaRRRRRRRRRRRcRRDRRRRERR( RRRRRRRRR]RRRR((RR; s  $  * !1   %   E @ ID& $ )$ )cCsd||fS(Nsinsert %s in %s(RR(RRR((RR  scCsdt|S(Nscount %s(RR(RRRW((RR} scCsdt|t|fS(Nsselect %s where %s(RRR(RRRR[((RRr scCsdt|t|fS(Nsdelete %s where %s(RRR(RRR((RRF scCs#dt|t|t|fS(Nsupdate %s (%s) where %s(RRRR(RRRR((RRB scCsdS(s9 remember: no transactions on many NoSQL N((R((RR scCsdS(s9 remember: no transactions on many NoSQL N((R((RR scCsdS(s9 remember: no transactions on many NoSQL N((R((RR6 scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RRR((RR. scCs tddS(Ns Not supported(R(RRR((RR/ scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RRR((RR  scCs tddS(Ns Not supported(R(RRR((RR) scCs tddS(Ns Not supported(R(RRR((RR* scCs tddS(Ns Not supported(R(RRR((RR+ scCs tddS(Ns Not supported(R(RRR((RR, scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(R((RR scCs tddS(Ns Not supported(R(R((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RRRq((RR  scCs tddS(Ns Not supported(R(RRR;((RR; scOs tddS(Ns Not supported(R(RRoR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(RRR<((RR scKsdS(N((RRRR((RR scOs tddS(Ns Not supported(R(RRoR((RR scOs tddS(Ns Not supported(R(RRoR((RR scCs tddS(Ns Not supported(R(RRR((RR scCs tddS(Ns Not supported(R(RR((RR scCs tddS(Ns Not supported(R(R((RR sicCs tddS(Ns Not supported(R(RRyRR((RRz s(/R-R.RR R$R}RrRFRBRRR6RRR.R/RR R)R*R+R,RRRRRRRRRR R;RRRRRRRRRRRRRRz(((RR[9 sX H                                          tGAEFcBstZdZdZRS(NcCs<|djodp||_||_||_||_dS(NRgt__key__(R,RR5R#tapply(RR,R5R#Ra((RR s  cCs,d|i|it|it|ifS(Ns (%s %s %s:%s)(RR,R5RR#R(R((Rt__repr__ s(R-R.RRb(((RR_ s tGoogleDatastoreAdaptercBsCtZeZhZdZdedZedZddddhdZ ee dd Z dd Z d Z dd Zdd ZddZddZddZddZdZdZddZdZdZdZghdZdZddZdZdZdZdZ RS(NcCsdS(N((RRi((RRj sRkcCsdS(N((RRiRqRt((RRy scCsdS(N((RRrRz((RR{ sisUTF-8cCs|S(N(R(R((RR sc CsS|iihdti<dd<dti<dti<dti<dti<dti<d ti <d t <d ti <d ti <d ti <dd<dti<dd<dd<dd<||_||_d|_||_d|d.+)t namespace(RRRER*tBooleanPropertyt TextPropertyRet BlobPropertytIntegerPropertyt FloatPropertyRt DatePropertyt TimePropertytDateTimePropertyR$RRBRR2RR>RRR^tnamespace_managert set_namespaceR( RRRBR>R2RRRR^((RR s       c Csh} x}|iD]r} t|to| |ijoqn|| }h} t|i t o*|i |i i p |i i | }nt|i tio |i }n|i idoqn|i idoS|i didid\} }t| } t|}t| || }nF|i idoU|iotdt} n|i di}|i |i d |i|}n|i id oN|iotdt} n|i d i}|i |i d | }n}|i id o|i |i | }nP|i |i jp|i |i  otd|i n|i |i | }|| |icCs ||jS(N(RoR(RoR((RRb scCs ||jS(N(RoR(RoR((RRe s( RRRR_R,RRR{R|RR^(RRR((RR'` s*/cCsz|idjo/t|id|i||idgSn8ti|i t |}t|id|dgSdS(NRgs>=cCs ||jS(N(RoR(RoR((RRi scCs ||jS(N(RoR(RoR((RRl s( RRR_R,RRRR{R|RR^(RRR((RR(g s/cCs d|iS(Ns-%s(RR,(RR((RR0n scCs d|i||i|fS(Ns%s, %s(RRRR(RRR((RR1q scCst|ttfp tdn|idjo/t|id|i ||idgSnMg}|D]}|t i|i|qm~}t|id|dgSdS(Ns Not supportedRgtincCs ||jS(N(RoR(RoR((RRx scCs ||jS(N(RoR(RoR((RR{ s(R(RR6R7RRRR_R,RRRRR{R|R(RRRRR((RRt s  /0cCsO|iidp tdnt|id|i||iddgS(Nslist:s Not supportedRzicCs ||jS(N(RoR(RoR((RR s( RRRRR_R,RRR(RRR((RR"} s cCsh|i|i<|i|i<|i|i<|i|i<|i|i<|i|i<}t|t p t dn|i |i d}|pt d|i in||_ |i|S(Ns Not suportedsNot suported %s(RR#R$R%R(R'R&tnopsR(RR4RRfR5R$tnopR-R(RRRR((RR s`  cCs |i|idjidS(Ni(RRRRgRN(RRRq((RR? scCsg}x<|D]4}t|to||i7}q |i|q W|}|o|i |} n8|o'|di } |dii dj}n t d|i | i} | i}|i|}x|D]}|idjo'|idjo|idjoqq|idjo|idjo|idjo g}qSt|ito-| i|i}|o |gpg}qS| i|i}|o |gpg}qt|toKg}|D]3} |it||i|io || qq~}q|idjo|i dn|id|i|if|i}qWt|tpJ|iddo t dn|id do t d n|id t#} | ot| tt%fot&| } nt| t'o|i| } n| i(d }xE|D]9}hd d<ddRRRRR( RRRBR>R2RRRRR((RR^ s       cCs0|o%y|ii|iWq,q,XndS(N(RRRFtcreateRR(RRRRR((RRn s csYtt}i|i}td|D}t ||d<|i ||S(Nc#s9x2|]+\}}|iti||ifVqWdS(N(RRRR,R[RRR(RRR(R(RRx sRJ( RRRgRRFRRtctableRRR Rtsave(RRRRR Rg((RRRu s  c Cst|tp tdnx1t|itdD]} td | q:Wg}x<|D]4}t|t o||i 7}q^|i |q^Wd }d }|}|i|}g}|p |i|D]} || iq~}g}|D]} |d || fq~} dig}|D]} |d ||| fq+~}dtd|d|i|dd|d|} | | fS(Ns Not SupportedRQRRRSRTRURVRWRXsinvalid select attribute: %scCs|djodp|S(NRgRJ(tfd(R((Rtuid scCs.|djot|dp|i|dS(NRgRJ(RRRRfR$(RR((RRf ss%s.%sRs;function(%(t)s){if(%(query)s)emit(%(order)s,[%(fields)s]);}RRRs%s._idR(sorderbysgroupbyslimitbysrequiredscachesleftsdistinctshaving(R(RR4RRZR[RRR\RRR:RRERRfRRPRRRRQR,t fieldnamesRRRRRR`(RRRR[RR\RRfRRRR`RQRRR((RRr} s.   2+:   c Cst|tp tdn|i|||\} } | di dd}|i |}g}|i| D]}||dqp~}|i|| tS(Ns Not SupportediRR#(R(RR4RRRrRR[R`RRRRFRRtcolsRyR{RG( RRRR[RyRRRRRR`((RRI s  .cCs%t|tp tdn|iidjo|i|ijou|i }|ii }||ii jpt |i |}y|t|=dSWq!tiij o dSq!Xnj|i|}|i||i|i gh}|i |}x|D]}|t|i =qWt|SdS(Ns Not SupportedRgii(R(RR4RRRR5RR#RRgRtAssertionErrorRFRRRthttptResourceNotFoundRPRIRRyRRD(RRRRyRRgR((RRN s& &     " c Cst|tp tdn|iidjo|i|ijo|i }|ii }|i |}yi|t|} xA|D]9\}}ti|||i||ii| |i[^\:/]+)(\:(?P[0-9]+))?/(?P.+)$i sInvalid URI string in DAL: %sRsmongodb: host name requiredRsmongodb: db name requiredRiicCsti||S(N(tpymongoRRtdbname(RR((RR s(RRRBRR2RR>RRR^RARRRRRRRRERRRR( RRRBR>R2RRRRRRRR((RR s(         csCii}td|D}|i|t t S(Nc#s7x0|])\}}|i||ifVqWdS(N(RRRRRRR(RRR(RR(RR s( RRFRRRRRR RRRg(RRRRR ((RRRR s cCs tddS(NsNot implemented(Rx(RR((RR$ scCs tddS(NsNot implemented(Rx(RRRR[((RRI' scCs tddS(NsNot implemented(Rx(RRR((RRN* scCs tddS(NsNot implemented(Rx(RRRR((RRE- s(R-R.R<RRRR^RReRaRcR6RR$RRRRIRNRE(((RR s    Rs sqlite:memoryRRRRtmssql2R8R3Rtfirebird_embeddedR<tingresuRMs jdbc:sqlitesjdbc:sqlite:memorys jdbc:postgresR*sgoogle:datastores google:sqlRRc CstpgSn|i|i}}t|to(t|do |i Sqr|i}nt|t pgSng}d}|djo|i ti|n|djo|i tiddn_|djo|i ti|n8|djo|i tid d n|d jo|i tid d n|id o&|i tid d  d d n|djo|i tin|djo|i tinf|djo|i tinB|io|ido|iddjo|d |iijo|i|d }||d}|ip||_t|do{|ioqti|i|i|i}|i oti!|i||_"n|i#|d joti$|Sn|Sq1n9|io|ido|iddjo|d|iijo|i|d}||d}|ip||_t|do2|io(ti|i|i|idt&}nti|i|idt&}|i oti!|i||_"n|Sn1|ido d} |ip| |_n|i o#|i(dti!|i|nddddd d!g}|i*o+|d |jo|i(dti+n?|i* o3|d |jo"|oti$|d"|d"i(RRRR^RRR6tSERIALIZABLE_TYPESRRRRRRRR$R(RRRRRReRaRctdatetime_to_strRR(RRRRRR((RR s&  "((R-R.R/RRRRRRbRRRRRGR(((RR s           cCstti|S(N(RRtloadsRS(RS((Rt Row_unpickler scCs"tti|idtffS(NR(RRtdumpsRSRRG(RS((Rt Row_pickler stSQLCallableListcBstZdZRS(NcCs ti|S(N(RR(R((RRs(R-R.R(((RRsc BstZdZedZedZedZddedeee ee e ed Z dZ d Z eed Z d Zd Zd ZdZdZdZdZedZdZdZee dZdZdZhdddZRS(s an instance of this class represents a database connection Example:: db = DAL('sqlite://test.db') db.define_table('tablename', Field('fieldname1'), Field('fieldname2')) cCsti|dS(s # ## this allows gluon to set a folder for this thread # ## <<<<<<<<< Should go away as new DAL replaces old sql.py N(RTR4R2(R2((RR4scGs|pdSndtitif}g}|D]\}}|d||fq6~}t |}x8|D]0\}}|i i pt d|iqoqoWx(|D] \}}|i i||qWdS(Ns%s.%ss%s.%is*distributed transaction not suported by %s(R5tsockett gethostnamet threadingt currentThreadt thread_keyRRRRRRRRt_dbnameR(R5RRRRR((RRs1   cGs?|pdSnt|}dtitif}g}|D]\}}|d||fqB~}x8|D]0\}}|i i pt d|iqoqoWy/x(|D] \}}|i i||qWWn;x(|D] \}}|i i||qWtdn,Xx(|D] \}}|i i||qWdS(Ns%s.%ss%s.%is*distributed transaction not suported by %ss)failure to commit distributed transaction(R5RRRRRRRRRRRRRt_dbanmeRRRxR(R5RRRRR((Rtdistributed_transaction_commit-s, 1     ssqlite://dummy.dbisUTF-8c  CsY| p d}n d}|o|i|n||_||_||_ d|_ g|_ |oat |ttfo|p|g} d}t}xtdD]}x| D]}yto|id od|}nti|i|_|itjotd|in||||||| phf} t|i| |_ t!}PWqtj o qt"j o }qXqW|oPqt#i$dqW|pt%d|qn/|d d ||f} t&| |_ t}}t)|_*||_+|i+od k,l}||_-n||_.||_/||_1| |_3d S( sR Creates a new Database Abstraction Layer instance. Keyword arguments: :uri: string that contains information for connecting to a database. (default: 'sqlite://dummy.db') :pool_size: How many open connections to make to the database object. :folder: :db_codec: string encoding of the database (default: 'UTF-8') :check_reserved: list of adapters to check tablenames and column names against sql reserved keywords. (Default None) * 'common' List of sql keywords that are common to all database types such as "SELECT, INSERT". (recommended) * 'all' Checks against all known SQL keywords. (not recommended) Checks against the specific adapters list of keywords (recommended) * '_nonreserved' Checks against the specific adapters list of nonreserved keywords. (if available) :migrate (defaults to True) sets default migrate behavior for all tables :fake_migrate (defaults to False) sets default fake_migrate behavior for all tables :migrate_enabled (defaults to True). If set to False disables ALL migrations :fake_migrate_enabled (defaults to False). If sets to True fake migrates ALL tables cCs|S(N(tcred(R((RRdscCs ti|S(N(turllibtunquoteR(R((RRfsRJisjdbc:s+Error in URI '%s' or database not supportedis%Failure to connect, tried 5 times: %sR$i(sADAPTERSN(4tdecode_credentialsRR2RR4RBRAR>t _pool_sizeRRRRR(R6R7turisRRGt connectedRRtis_jdbcRt regex_dbnameR^RRtADAPTERSRRRRR<RRctsleepRxRTRRRRRZtreserved_sql_keywordstRSKt_migratet _fake_migratetmigrate_enabledt_migrate_enabledtfake_migrate_enabledt_fake_migrate_enabled(RRBR>R2RRZRRRRRRRRRRRRR((RRDsd       & "          cCsOxH|iD]=}|i|i|jotd||ifq q WdS(s Validates ``name`` against SQL keywords Uses self.check_reserve which is a list of operators to use. self.check_reserved ['common', 'postgres', 'mysql'] self.check_reserved ['all'] s=invalid table/column name "%s" is a "%s" reserved SQL keywordN(RRZtbackendR,RRR(RR,R((Rtcheck_reserved_keywords  cCs |i|otSntSdS(N(Rthas_keyRR<RG(RR((Rt __contains__sc s |tid}tid}ddd|djoNg}xiD]6} | i dp || ddd d 7}qVqVWnd}x|t |jo||} | id }|d i d oz|i|d of|d |d idd d !d i|d }|| |||d }|t |7}q|d 7}qWd i|djo2thdd<dd<dd<<d|<Snx|D]} d<}} |} d}| d id }t |t |joqnx|D]x} |i| ot| d d !id}|d|d } }| p | |jo#t |djp|ddjo| |||j}n|ddjo| |||j}nT|ddjo| |||j}n'|ddjo| |||j}n|ddjo| |||j}n|ddjo| |||j}n|ddjo"| |i||j}nm|ddjo"| |i||j}n:|dd jo"| |i||j}n|dd!jo"| |i ||j}n|dd"jo"| |i!||j}n|dd#jo"| |i"||j}nn|dd$jo| |i ||}n>|dd%jo| |i#||}nt$d&| t |d'jo|d(d)jo |}n%t |d'jot$d&| n| |} qt$d*| nv|oS|i| oC||| | id jo%| | idd d !} d| jo| id\} }|oyy1| |i'| i(|i)} Wqt*j o5thdd+<d| <dd,<dd<<SqXqg}| i,|i)D]}||i.q~}| |i'|} q| | } n| d-jo| o||}|| joPny'| i,| |d.d=i0}WnAt*j o5thdd+<d| <dd,<dd<<SnX|p2thdd/<d| <dd0<dd<<Sqthdd<d||<d| <Sn| ||joPn| }|d 7}|t |joV| oO|i2d1d2| |fidd \}}y:|d d3jo|d |}n||}Wn8t5j o,thdd+<dd4<dd<<SnXg}| D]}|i6o ||qq~}| i8}ydt9|i2d5dt9|i2d6d7f}|ddjp|d |djo t*nWn8t*j o,thdd+<dd8<dd<<nX||d |djo)thdd+<dd9<dd<<ny| i,d.|d:||}WnAt*j o5thdd+<d| <dd,<dd<<SnXthdd<d|<d| <SqqWqWthdd+<dd;<dd<<S(>s EXAMPLE: db.define_table('person',Field('name'),Field('info')) db.define_table('pet',Field('person',db.person),Field('name'),Field('info')) @request.restful() def index(): def GET(*args,**vars): patterns = [ "/persons[person]", "/{person.name.startswith}", "/{person.name}/:field", "/{person.name}/pets[pet.person]", "/{person.name}/pet[pet.person]/{pet.name}", "/{person.name}/pet[pet.person]/{pet.name}/:field" ] parser = db.parse_as_rest(patterns,args,vars) if parser.status == 200: return dict(content=parser.response) else: raise HTTP(parser.status,parser.error) def POST(table_name,**vars): if table_name == 'person': return db.person.validate_and_insert(**vars) elif table_name == 'pet': return db.pet.validate_and_insert(**vars) else: raise HTTP(400) return locals() sh^{[^\.]+\.[^\.]+(\.(lt|gt|le|ge|eq|ne|contains|startswith|year|month|day|hour|minute|second))?(\.not)?}$s ^.+\[.+\]$RJic sg}x|iD]}|o d||iddf}n)d|idd|iddf}||}|i pqn|i djp d|jp|i i do6|d||f7}|i ||i |d n|i i d o6|d||f7}|i ||i |d nV|i i d p|i i d o<|d ||||f7}|i ||i |d n|i i do6|d||f7}|i ||i |d n|i djo|d||f7}|i ||i |d |d||f7}|i ||i |d |d||f7}|i ||i |d n|i djo|d||f7}|i ||i |d |d||f7}|i ||i |d |d||f7}|i ||i |d n|djofxc|iD]P\}}|d|||f7}|i |||d|d|d7}qQWqqW|S( Ns%s/%sRRs/%s/%sRgtslugRhs/{%s.%s}s/:fieldRUR_R]s/{%s.%s.ge}/{%s.%s.lt}slist:s/{%s.%s.contains}RaRes /{%s.%s.year}s/{%s.%s.month}s /{%s.%s.day}Rcs /{%s.%s.hour}s/{%s.%s.minute}s/{%s.%s.second}is /%s[%s.%s]tbasetdepthi(sdatesdatetime(sdatetimestime(tpatternsRRRRR RttagRQtreadableRRRER RHRRt auto_table( RR R R RQRRRR(RR(RRsh ( 0  &           ,tautotauth_R R iRis:autot[R tstatusitpatternR6RRvRiteqtnetlttgttgetleRRRRRRRtcontainssinvalid pattern: %siitnotsmissing relation in pattern: %sis invalid paths:fieldRSisrecord not foundRs%s.%st~sinvalid orderbytmintmaxisinvalid limitsstoo many recordsRQsno mathcing patternN(ii(<RRRRtre1tre2RR RRRRRDRRttokensR^RRt new_patternsRRR$totableRtdbsetttagsRRRRRRtminutestsecondsRRxRt nested_selectRMRrRJt ValueErrorRRIRRgRRtvarsRftofieldRQRRRRRtlimitsRv(RR RR,RR*R%R$RRRRR&RRR'RR#RvRQR.RRR!RRR"R-RR((RRRt parse_as_rests4  ( (82  $"""""" $ 5 174! '32- .*3 0&*)31c OsjxG|D]?} | dddddddgjotd|| fqqW|io|id|i}|i p|id|i } |idd}|idd} |idd}|idd}|idd}t|tp td nt|}|i} |id p t|| pti|otd |n=| |ijotd |n|io|i|nt|||t d|d| d|} ||<|i"djo| Sn| i#|p|i$i%djoBz0t&i'|i$i(| d|d| d|Wdt&i)Xn d| _*|ii+||| _,| S(NRRERtformatRRRs invalid table "%s" attribute: %ssmissing table nameRsinvalid table name: %sstable already defined: %sR$sgoogle:datastore(NsNone(-RRRRRRRfRRRRRR$R0RRRERR(RRRtlowertablenameRR7tregex_python_keywordsR^RRZRRtRRRRAt_create_referencesRRR?R@RRHRRER(RRRRRRERR0RRRRR1R((Rt define_tablesN"   0     ccs x|iD]}||Vq WdS(N(RRR(RR((Rt__iter__s cCsti|t|S(N(RRRRR(RR((RRscCsti|t||dS(N(RRRRRR#(RRR#((RRscCs||S(N(RR(RR((RRscCs=|d djo||jotd|n|||Rc Osx|D]}|i}|pqq|djodSq|id p|d|ijo tdq|d}||i||||||qWdS(NR;sTABLE isinvalid file format(tifiletlineRRRRRRtimport_from_csv_filetid_maptnullRRR( RR>RARBRRRRR?((RR@!s  %  (R-R.R/RSR4RRR$R<RGRRR R/R4R5RRRRRbRRRRQR R=R@(((RR s, *S   =         -  R:cBs tZdZdZdZRS(s Helper class providing a comma-separated string having all the field names (prefixed by table name and '.') normally only called from within gluon.sql cCs ||_dS(N(RR(RR((RR9scCs1dig}|iD]}|t|q~S(Ns, (RRRRRR(RRR((Rt__str__<s(R-R.R/RRC(((RR:1s  RcBs5tZdZdZdZdZdZRS(NcCs?|ip|it||_n|ip tdndS(Ns;Using a recursive select but encountered a broken reference(RRRRRx(R((Rt __allocateBs  cCs8|djot|Sn|i|ii|dS(NRg(RRRt_Reference__allocateRRfR$(RR((RRHs  cCsF|idoti|||dSn|i||i|Rc s|idd}|idd} |idti}ti|d|d| d|} d } t |t o2i|joh|i/./uuid_key[:2] # directory) to be used as argument of DAL.define_table allowed field types: string, boolean, integer, double, text, blob, date, time, datetime, upload, password strings must have a length of Adapter.maxcharlength by default (512 or 255 for mysql) fields should have a default or they will be required in SQLFORMs the requires argument is used to validate the field input in SQLFORMs RVRGcCsd|_d|_d|_d|_t|tp t dn|i do|dt t }}}n-|i do|dt t }}}nd|jo|idd\}}nt||_}tt|p!|ddjpti|ot d|nt|tod |i}n||_|djotp||_|tjo|pd|_n ||_||_|i|_||_| |_ | |_!||_"||_#| |_$| p d i&d |idD|_%| |_'||_ ||_ ||_||_(||_)| o|djo d}n||_*||_+t,|_-||_.||_/|i%djo@d i&g}|idD]}||i2q~|_%n|djo g|_3n ||_3dS(Nsmissing field nameRiRRziRsField: invalid field name: %ss reference R>ccsx|]}|iVqWdS(N(RRt capitalize(RR((RR5ss list:integers list:stringcCsdid|pgDS(Ns, ccsx|]}t|VqWdS(N(RRR(RR((RR=s(RR(R((RR=s(s list:integers list:string(4R$RRR5RRR(R<RRRRGRtwritableRRRR,R7RtR2R^RRRt MAXCHARLENGTHRNRERTRRRRRLt uploadfoldertuploadseparatetwidgettlabelRtcommentt authorizet autodeleteRRZR<t isattachmentt custom_storetcustom_retrieveRRRR(RR<RRRRTRRRRRLRRRRRRERRRRRRZRRRR((RRsb      1          ,            @  c Csd|io|i|||Sn|p |i}ntii|idti idti }t i di |} | o| idpd} tiddd}ti|i}d |i|i||f} | d d | } t|itoG|ii} h|ii| <| |i<}|iii|n|it jo|onV|i!o |i!}n?|i"i#i$o%tii%|i"i#i$d d }n t&d|i'o0tii%|d|i|if|d }ntii(|pti)|ntii%|| }t+|d}t-i.|||i/n| S(NRs\s\.(?P\w{1,5})$RttxtRRJis %s.%s.%s.%siRs..tuploadss.you must specify a Field(...,uploadfolder=...)s%s.%siRn(0RRtfileRiRLR,RKtbasenameRtsepRRRRRt extensionRtuuid_keyRt b16encodeRtencoded_filenameRt newfilenameR(RLRtblob_uploadfield_nameRTRRRR<RRRR2RRxRRMtmakedirst pathfilenameRpt dest_filetshutilt copyfileobjR6( RRRiRLRRRRRRRRR((RtstoreKs@  0 $  %  0c Cs|io|i||Sndk}|ipt|ito?|i ||ji i }|p|i dqn|io$|i| o|i dnydti|} | p |i otd|nti| idt}tid|}Wnttfj o |}nXt|ito%|ti||ipdfSn"t|itoH|ii} |i|j}|ii || }|ti|fSn|on9|i"o |i"}n"t#ii$|i i%i&dd}|i'oW| id } | id }| id } t#ii$|d | |f| d }n|t+t#ii$||dfSdS(NiisCan't retrieve %sR,RRJs..RRRtuuidkeys%s.%siRk(,RRR,RLRRR(RLRRRIRRtHTTPt regex_contentR^RRRRt b16decodeRR<Ritregex_cleanup_fntsubRt cStringIOtStringIORRRRRSRRKRRR2RRRQtuRp( RR,RLRRRiRRSRQRRRR((RtretrieveqsF  ! %   ! *cCs|djp |i o|Snt|ittfp|ig}n9t|itot|i}nti|i}|ix1|D])}t |do|i |}qqW|S(Nt formatter( R#R$RRR(R6R7RtreverseRR7R(RR#RR((RRs cCs|ip|dfSn|i}t|ttfp |g}nx5|D]-}||\}}|o||fSqKqKW|dfS(N( RRR#R$R(R6R7RR(RR#RRR((RR's   cCs"t|i|iii|ddS(NtCOUNTR](R3RRRR(R((RRscCstS(N(R<(R((Rt __nonzero__scCs1yd|i|ifSWnd|iSnXdS(Ns%s.%ss .%s(RRR,(R((RRCs(R-R.R/R$RNRGR<RRRRR'RRRC(((RRs !NQ& (   R4cBsAtZdZeedZdZdZdZdZRS(s a query object necessary to define a set. it can be stored or can be passed to DAL.__call__() to obtain a Set Example:: query = db.users.name=='Max' set = db(query) records = set.select() cCs(||_||_||_||_dS(N(RRR5RR(RRR5RR((RRs   cCs|iii|S(N(RRRR(R((RRCscCst|i|iii||S(N(R4RRRRR(RR((Rt__and__scCst|i|iii||S(N(R4RRRRR(RR((RR|scCs@|i|iiijo |iSnt|i|iii|S(N(RR5RRRRR4(R((RR~s ( R-R.R/R$RRCRR|R~(((RR4s    s'[^']*'cCs<|pdSn|d}x|dD]}||B}q$W|S(Nii(RQR$torderby2R(RQRR((RRps  RcBstZdZdZdZedZdZdZdZ dZ edZ d Z d Z d Zed ZRS( s a Set represents a set of records in the database, the records are identified by the query=Query(...) object. normally the Set is generated by DAL.__call__(Query(...)) given a set, for example set = db(db.users.name=='Max') you can: set.update(db.users.name='Massimo') set.delete() # all elements in the set set.select(orderby=db.users.id, groupby=db.users.name, limitby=(0,10)) and take subsets: subset = set(db.users.id<5) cCs||_||_||_dS(N(RRRR(RRR((RRs  cCs|t|to|idj}n!t|to|dj}n|iot|i|i|@Snt|i|SdS(Ni( R(RRtRJRR$RRR(RR((RR s cCs|iii|i|S(N(RRRR}RRW(RRW((RR}scOs|iii|i||S(N(RRRRrRRR[(RRR[((RRrscCs1|iii|i}|iii||iS(N(RRRRPRRRF(RR((RRFscKsP|iii|i}|i|i|dt}|iii ||i|S(NRE( RRRRPRRR[RR<RRB(RRRR((RRB scCs|idd S(NRSii(ii(RRI(R((Rtisempty%scCs|iii|i|S(N(RRRRRRW(RRW((RR(scOs|iii|i||S(N(RRRRIRRR[(RRR[((RRI+scCs;|iii|i}|i|iii||iS(N(RRRRPRRtdelete_uploaded_filesRN(RR((RRN.s cKsq|iii|i}|i|i|dt}|p t dn|i ||iii ||i|S(NREsNo fields to update( RRRRPRRR[RR<RRRRE(RRRR((RRE3s   c Cs|i|iii|id} |o|i}n | i}g}|D]G}| |i djo-| |i t jo| |io ||qNqN~}|pdSnxM|ig}|D]}|| |q~D]}x|D] }| |}|i|d}|pqn|o|||joqn|i} | p%tii|iiidd} n|ioE|id} tii| d| d| df| dd } ntii| |} tii| oti | qqWqWdS( NiR\s..RRs%s.%sii(!RRRRRRt upload_fieldsRRRRQRRLR<RRIRQR<RRfR$toldnameRRKRLRR2RRRtoldpathRMR|( RRRRQRRRQR<RRRRR((RR;s8# [/  %  (R-R.R/RRR$R}RrRFRBRRRIRNRER(((RRs           c Cs|\}}} |p t|}tg}|iD]A\}}||i jo%||i djo|||fq6q6~}|i|i| ji|x$|iD]\}}|||cs|idd}|idd} |idti} |idt}ti |d|d| d| }|id|i } |i | d}x|D]}g}x| D]}ti|p|i|i|q|id \}} |i|| } t|i|d ttfo||| }n || }|o| io| i|}n|i||qW|i |qWd S( s export data to csv, the first line contains the column names :param ofile: where the csv must be exported to :param null: how null values must be represented (default '') :param delimiter: delimiter to separate values (default ',') :param quotechar: character to use to quote string values (default '"') :param quoting: quote system, use csv.QUOTE_*** (default csv.QUOTE_MINIMAL) :param represent: use the fields .represent value (default False) :param colnames: list of column names to use (default self.colnames) This will only work when exporting rows objects!!!! DO NOT use this with db.export_to_csv() R_RR`RaRbRRcs|djoSnt|to|idSnqt|tot|SnSt|do|i d i ddSn%t|t t fot |Sn|S(s returns a cleaned up value that can be used for csv export: - unicode text is encoded as such - None values are replaced with the given representation (default ) RRiRR>N(R#R$RBR(RRRRR7RRR6R7R(R#(RB(Rtnone_exception-s RN( RRfR_R`RfRgRbRGRtwriterR<RRtwriterowRRQRtcolR]R^RERRRRQRRR(R$RRR#(RR<RBRRRRRRRR`RRQRbR#RQR_RR((RBRR=s4   " cCsdk}|i|iS(sJ serializes the table using sqlhtml.SQLTABLE (if present) N(tsqlhtmltSQLTABLERtxml(RR((RRPs Rc s%iddgjotdnd} djoRg}|D]:}|tg}|i D]}|| ||qn~qT~}nIg}|D]4}|g}|i D]}|| ||q~q~}t o!t i|d|pt iSndk}|i|SdS(s@ serializes the table to a JSON list of objects Rtarrays#Invalid JSON serialization mode: %scs|id\}}d}ti|p|i |}n9t |i |dt o|||}n ||}djo||fSn|SdS(NRR(RRRRQR$tresR]R^RQRR(RfRRq(RQRRQRR(Rq(Rt inner_loop`s  RN(RqRRRRRRQRRRRRthave_serializerst serializerstjsonRt custom_jsont simplejsonR( RRqRRRRRRQRR((RqRRXs   RH! (R-R.R/R$R<RRRR|RRR{RR5RCRRRRRGRRRR=RR(((RRes*               ; cCs ti|S(N(RRRS(RS((RtRows_unpickler{scCs(tti|idtdtffS(NRR(RRRRSRR<RG(RS((Rt Rows_pickler~scCsdS(s >>> if len(sys.argv)<2: db = DAL("sqlite://test.db") >>> if len(sys.argv)>1: db = DAL(sys.argv[1]) >>> tmp = db.define_table('users', Field('stringf', 'string', length=32, required=True), Field('booleanf', 'boolean', default=False), Field('passwordf', 'password', notnull=True), Field('uploadf', 'upload'), Field('blobf', 'blob'), Field('integerf', 'integer', unique=True), Field('doublef', 'double', unique=True,notnull=True), Field('datef', 'date', default=datetime.date.today()), Field('timef', 'time'), Field('datetimef', 'datetime'), migrate='test_user.table') Insert a field >>> db.users.insert(stringf='a', booleanf=True, passwordf='p', blobf='0A', uploadf=None, integerf=5, doublef=3.14, datef=datetime.date(2001, 1, 1), timef=datetime.time(12, 30, 15), datetimef=datetime.datetime(2002, 2, 2, 12, 30, 15)) 1 Drop the table >>> db.users.drop() Examples of insert, select, update, delete >>> tmp = db.define_table('person', Field('name'), Field('birth','date'), migrate='test_person.table') >>> person_id = db.person.insert(name="Marco",birth='2005-06-22') >>> person_id = db.person.insert(name="Massimo",birth='1971-12-21') commented len(db().select(db.person.ALL)) commented 2 >>> me = db(db.person.id==person_id).select()[0] # test select >>> me.name 'Massimo' >>> db(db.person.name=='Massimo').update(name='massimo') # test update 1 >>> db(db.person.name=='Marco').select().first().delete_record() # test delete 1 Update a single record >>> me.update_record(name="Max") >>> me.name 'Max' Examples of complex search conditions >>> len(db((db.person.name=='Max')&(db.person.birth<'2003-01-01')).select()) 1 >>> len(db((db.person.name=='Max')&(db.person.birth>> len(db((db.person.name=='Max')|(db.person.birth<'2003-01-01')).select()) 1 >>> me = db(db.person.id==person_id).select(db.person.name)[0] >>> me.name 'Max' Examples of search conditions using extract from date/datetime/time >>> len(db(db.person.birth.month()==12).select()) 1 >>> len(db(db.person.birth.year()>1900).select()) 1 Example of usage of NULL >>> len(db(db.person.birth==None).select()) ### test NULL 0 >>> len(db(db.person.birth!=None).select()) ### test NULL 1 Examples of search conditions using lower, upper, and like >>> len(db(db.person.name.upper()=='MAX').select()) 1 >>> len(db(db.person.name.like('%ax')).select()) 1 >>> len(db(db.person.name.upper().like('%AX')).select()) 1 >>> len(db(~db.person.name.upper().like('%AX')).select()) 0 orderby, groupby and limitby >>> people = db().select(db.person.name, orderby=db.person.name) >>> order = db.person.name|~db.person.birth >>> people = db().select(db.person.name, orderby=order) >>> people = db().select(db.person.name, orderby=db.person.name, groupby=db.person.name) >>> people = db().select(db.person.name, orderby=order, limitby=(0,100)) Example of one 2 many relation >>> tmp = db.define_table('dog', Field('name'), Field('birth','date'), Field('owner',db.person), migrate='test_dog.table') >>> db.dog.insert(name='Snoopy', birth=None, owner=person_id) 1 A simple JOIN >>> len(db(db.dog.owner==db.person.id).select()) 1 >>> len(db().select(db.person.ALL, db.dog.name,left=db.dog.on(db.dog.owner==db.person.id))) 1 Drop tables >>> db.dog.drop() >>> db.person.drop() Example of many 2 many relation and Set >>> tmp = db.define_table('author', Field('name'), migrate='test_author.table') >>> tmp = db.define_table('paper', Field('title'), migrate='test_paper.table') >>> tmp = db.define_table('authorship', Field('author_id', db.author), Field('paper_id', db.paper), migrate='test_authorship.table') >>> aid = db.author.insert(name='Massimo') >>> pid = db.paper.insert(title='QCD') >>> tmp = db.authorship.insert(author_id=aid, paper_id=pid) Define a Set >>> authored_papers = db((db.author.id==db.authorship.author_id)&(db.paper.id==db.authorship.paper_id)) >>> rows = authored_papers.select(db.author.name, db.paper.title) >>> for row in rows: print row.author.name, row.paper.title Massimo QCD Example of search condition using belongs >>> set = (1, 2, 3) >>> rows = db(db.paper.id.belongs(set)).select(db.paper.ALL) >>> print rows[0].title QCD Example of search condition using nested select >>> nested_select = db()._select(db.authorship.paper_id) >>> rows = db(db.paper.id.belongs(nested_select)).select(db.paper.ALL) >>> print rows[0].title QCD Example of expressions >>> mynumber = db.define_table('mynumber', Field('x', 'integer')) >>> db(mynumber.id>0).delete() 0 >>> for i in range(10): tmp = mynumber.insert(x=i) >>> db(mynumber.id>0).select(mynumber.x.sum())[0](mynumber.x.sum()) 45 >>> db(mynumber.x+2==5).select(mynumber.x + 2)[0](mynumber.x + 2) 5 Output in csv >>> print str(authored_papers.select(db.author.name, db.paper.title)).strip() author.name,paper.title Massimo,QCD Delete all leftover tables >>> DAL.distributed_transaction_commit(db) >>> db.mynumber.drop() >>> db.authorship.drop() >>> db.author.drop() >>> db.paper.drop() N((((Rttest_allst__main__(R/t__all__RRRRRRKRRReRRcRRfRRtloggingtcopy_regRRRFRRIRRt LambdaTypet FunctionTypetBuiltinFunctionTypet MethodTypetBuiltinMethodTypeRtutilsRt ImportErrorRRuR<RsRGRRRRt getLoggerRRNtRLockR?tlocalR3RRR]RRRR2tdriversRtdbapi2RREtdebugtcontrib.pymysqlR RR RR+R5twarningRMtcom.ziclix.python.sqlRtjava.sqlRt org.sqlitetJDBCRR@tnewRvtgoogle.appengine.extRR*tgoogle.appengine.apiRqRYt$google.appengine.api.datastore_typesR{t!google.appengine.ext.db.polymodelRyR\RRR1RTRRRRRRRR'R*R,R/R7RHR9RIRKRNRWRXR[R_RcRRRRRRRRRRRRRRRRRtpickleR6RRR:RRRGRKRtR3RRR4t regex_quotesRpRRRRRRtSQLFieldtSQLTablet SQLXorabletSQLQuerytSQLSettSQLRowst SQLStoragetSQLDBtGQLDBR-tdoctestttestmod(RXRR ReRRR:RRRRIRRRR]RRRRR+RRRRqR/R RRIRRRRcR RRRRRRKRR RRKRRRTRRRRR{RRRR RRRR3R RuRRpR4RRRFRRRNRRR*R@RRRYR_R1RR[RRRRR,RR*RR3RRMRvR RRRGRHRRWRRR9RRsRRRRRRRRRRfRR7RyRRRRNRRR2RRKR?R'RRcR5RtR((Rt?ls                       $                                      "A6bp&{!i(jM^R:" @     G \    @  (  8) f