11iaSrSSKrSSKrSSKrSSKrSSKrSSKrSSKrSSKrSSK r SSK r SSK J r SSK Jr SrS\\4S\\4SSS 4S S S 4S \\4S.r"SS\R"5r"SS\R"5rSrSS.S\R*S\S\S\R.\S\S\R2\4 SjjrS\R*S\S\R8\R:\\R.44Sjr\ R>"SS 55r \ R>"S!S"\!55r"S(S#\ RS$\R2\S\R:\"4S%jjr#"S&S'\!5r$g))a( SQLite as alternative storage backend for a TableGroup's data. For the most part, translation of a TableGroup's tableSchema to SQL works as expected: - each table is converted to a `CREATE TABLE` statement - each column specifies a column in the corresponding `CREATE TABLE` statement - `foreignKey` constraints are added according to the corresponding `tableSchema` property. List-valued foreignKeys are supported as follows: For each pair of tables related through a list-valued foreign key, an association table is created. To make it possible to distinguish multiple list-valued foreign keys between the same two tables, the association table has a column `context`, which stores the name of the foreign key column from which a row in the assocation table was created. Other list-valued columns work in two different ways: If the atomic datatype is `string`, the specified separator is used to create a concatenated string representation in the database field. Otherwise, the list of values is serialized as JSON. SQL table and column names can be customized by passing a translator callable when instantiating a :class:`Database`. SQLite support has the following limitations: - regex constraints on strings (as specified via a :class:`csvw.Datatype`'s format attribute) are not enforced by the database. N) DATATYPES) TableGroupcU$Nss A/home/james-whalen/.local/lib/python3.13/site-packages/csvw/db.pyidentityr ,s HTEXTINTEGERc"UcU$[U5$r)intrs r r;sqy!,c!f,r c"UcU$[U5$r)boolrs r rr<sqy!-d1g-r REALc"UcU$[U5$r)floatrs r rr?sqy!.eAh.r c8UcU$[R"U5$r)decimalDecimalrs r rr@sqy!8gooa&88r BLOB)stringintegerbooleanr hexBinarycH\rSrSrSS\S\R \S\4SjjrSrg) SchemaTranslatorHNtablecolumnreturncgrr)selfr"r#s r __call__SchemaTranslator.__call__I r rr) __name__ __module__ __qualname____firstlineno__strtypingOptionalr'__static_attributes__rr r r r Hs* c 6??3+? 3  r r c&\rSrSrS\S\4SjrSrg)ColumnTranslatorMr#r$cgrr)r&r#s r r'ColumnTranslator.__call__Nr)r rN)r*r+r,r-r.r'r1rr r r3r3Ms s s r r3c2SRSU55$)N,c3D# UHnSRU5v M g7f)z`{0}`N)format).0names r quoted..Ss;UTGNN4((Us )join)namess r quotedrARs 88;U; ;;r Fsingledb translater"keysrowsrCc |U(aqSR[U"U55[UVs/sH oa"X&5PM sn6SRUVs/sHnSPM sn55nURX5 ggs snfs snf! U(dUHn [ XX#U SS9 M g[ U5 [ U5 e=f)a Insert a sequence of rows into a table. :param db: Database connection. :param translate: Callable translating table and column names to proper schema object names. :param table: Untranslated table name. :param keys: Untranslated column names. :param rows: Sequence of rows to insert. :param single: Flag signaling whether to insert all rows at once using `executemany` or one at a time, allowing for more focused debugging output in case of errors. z"INSERT INTO {0} ({1}) VALUES ({2})r8?TrBN)r:rAr? executemanyinsertprint) rDrEr"rFrCrGk_sqlrows r rKrKVs" 299 9U# $ $7$QYu($7 8 HH4(4ac4( )+  NN3 %  8( C2%s4H c d sA: A?'BB;$B;r$cURSR[U555nURVs/sHo3SPM nnU[ UR 554$s snf)NzSELECT * FROM {0}r)executer:rA descriptionlistfetchall)rDr"cudcolss r selectrYxsU '..ve}= >B.. ).QaD.D ) bkkm$ $$ *sA#cb\rSrSrSr\R "5r\R "SSS9r\R "SS9r \R "SS9r \R "SS9r \R "SS9r \R "SS9r \R "SS9rS rS \S \R$\4S jrS \S \4S jrSrg)ColSpec~z] A `ColSpec` captures sufficient information about a :class:`csvw.Column` for the DB schema. rcU(aU$S$)Nrrrs r rColSpec.s1a>R(>Rr )default converterNr_FcdUR[;a'[URuUlUlUlOKSUl[ URR Ul[ URRUlUR(aURS:waSUlggg)Nr ) csvw_typeTYPE_MAPdb_typeconvertreadr to_string to_python separatorr&s r __attrs_post_init__ColSpec.__attrs_post_init__s| >>X %4>DL!$..1;;DI >>dllf4!DL5>r rEr$ctUR(dgURU"UR5p2/nURc URbSSS.R UR 5nURb]U(a,UR SRX2RU55 O*UR SRX2R55 URb^U(a-UR SRX2RU55 GOUR SRX2R55 O[S URURUR455(aUR(a*UR S RX2R55 UR(a*UR S RX2R55 UR(a*UR S RX2R55 S RU5$)z We try to convert as many data constraints as possible into SQLite CHECK constraints. :param translate: Callable to translate column names between CSVW metadata and DB schema. :return: A string suitable as argument of an SQL CHECK constraint. Ndatedatetime)rorpz{2}(`{0}`) >= {2}('{1}')z `{0}` >= {1}z{2}(`{0}`) <= {2}('{1}')z `{0}` <= {1}c3(# UHoSLv M g7frr)r;ccs r r= ColSpec.check..sO*NB4*Nszlength(`{0}`) = {1}zlength(`{0}`) >= {1}zlength(`{0}`) <= {1}z AND ) csvwr<minimummaximumgetrcappendr:anylength minLength maxLengthr?)r&rEccname constraintsfuncs r check ColSpec.checksyy 99i 25 II !qyy'<&c$..! yy$&&'A'H'HPYPY[_'`a&&~'<'`_ raNr"drop_self_referential_fksr$c U"URURRS9nURRVs1sH!oDR(dMUR iM# nnURR GH nURR(aM![UR5S:XGaAURSU;Ga-[URR5S:Xd?SRURRURR55eUR(a[UR5S:Xd SRUR55e[R!URURSURRR"URRS5UR$URS'GM|U(a1URRR"UR:XaGMUR&R)[+UR5URRR"[+URR545 GM# URRHnUR UR$;dMUR-S5nURR)[/UR U(a UR0OUUR-S5UR-S5UR-S5S 95 M U$s snf) a Create a `TableSpec` from the schema description of a `csvw.metadata.Table`. :param table: `csvw.metadata.Table` instance. :param drop_self_referential_fks: Flag signaling whether to drop self-referential foreign keys. This may be necessary, if the order of rows in a CSVW table does not guarantee referential integrity when inserted in order (e.g. an eralier row refering to a later one). :return: `TableSpec` instance. )r< primary_keyrz)Composite key {0} in table {1} referencedzSTable {0} referenced by list-valued foreign key must have non-composite primary keydatatyperjr)r<rcrjrrt) local_name tableSchema primaryKeycolumnsrjheader foreignKeys referenceschemaReferencelencolumnReferencer:resourcerr<rassociation_tabler many_to_many foreign_keysrxsortedinheritr[base)clsr"rspecr} list_valuedfkrs r from_table_metadataTableSpec.from_table_metadatas((e6G6G6R6RS).):):)B)BR)BAkkxqxx)B R##//B<<///r))*a/B4F4Fq4I[4Xr||;;<A3CJJLL88LL1133A ++D4D4D0E0J8&&,fTYY&78J@I?Z?Z ((+ --44 44Q7 @D%%b&8&8&;< 4 8M8M8T8TX\XaXa8a%%,,r112 --44r||;;<.'00""**Axxt00099Z0 ##G/7hmmXii 4YYz2:. %+ GSs MMcr[SRX55n[SRX455nURUR:Xa*U=RS- slU=RS- slU"SRX5XV[S5/UR/X/4UR/X4/4/S9$)a List-valued foreignKeys are supported as follows: For each pair of tables related through a list-valued foreign key, an association table is created. To make it possible to distinguish multiple list-valued foreign keys between the same two tables, the association table has a column `context`, which stores the name of the foreign key column from which a row in the assocation table was created. z{0}_{1}_1_2context)r<rr)r[r:r<)ratableapkbtablebpkafkbfks r rTableSpec.association_table si&&v34i&&v34 88sxx  HH H HH H!!&1wy12((VU+((VU+  r rEc[R"XR5nURVs/sHo3R U5PM nnUR (aFUR SR[UR Vs/sH oR"U5PM sn655 URHoupgnUR SR[UVs/sH oR"U5PM sn6[U"U55[UVs/sH oQ"Xu5PM sn655 Mq SRU"UR5SRU55$s snfs snfs snfs snf)zC :param translate: :return: The SQL statement to create the table. zPRIMARY KEY({0})z6FOREIGN KEY({0}) REFERENCES {1}({2}) ON DELETE CASCADEz,CREATE TABLE IF NOT EXISTS `{0}` ( {1} )z, ) functoolspartialr<rrOrrxr:rArr?) r&rE col_translatecolclausesr}rrefrefcolss r rO TableSpec.sql s6 ")))YY? 59\\B\c77=)\B    NN-44V,0,<,<=,7# UHoST;=(d UST:Hv M g7f)rNr)r;rorderedr"s r r=schema..Ps*`E_cFg%9#a&E/9E_s#&z7there seem to be cyclic dependencies between the tables) tabledictitemsrrr<rvaluesrrrTrFallrpop ValueError) rrtablestnametatirr"s @@r schemar3s F **, u  ) ) -F * Hqvv..'')B 277O* -%%'G A QW Q&++-(E`VE]E_E_```!'E!2 ) QWRSS  !!r c \rSrSrSrSS\S\R\R\ R\ 4S\R\ S\R\ 4SjjrSS jr\S \R"\ \44S j5r\SS \ S \R\ S \ 4Sjj5rS \R\R.\R244SjrS \4SjrS\ S\ S \R\ 4SjrS \R\R<\ \ S44SjrS \R"\ \R<\R@44Sjr!Sr"SSjr#SSSS.Sjr$Sr%g) DatabaseiZa{ Represents a SQLite database associated with a :class:`csvw.TableGroup` instance. :param tg: `TableGroup` instance defining the schema of the database. :param fname: Path to which to write the database file. :param translate: Schema object name translator. :param drop_self_referential_fks: Flag signaling whether to drop or enforce self-referential foreign-key constraints. .. warning:: We write rows of a table to the database sequentially. Since CSVW does not require ordering rows in tables such that self-referential foreign-key constraints are satisfied at each row, we don't enforce self-referential foreign-keys by default in order to not trigger "false" integrity errors. If data in a CSVW Table is known to be ordered appropriately, `False` should be passed as `drop_self_referential_fks` keyword parameter to enforce self-referential foreign-keys. NrfnamerErcU=(d [RUlU(a[R"U5OSUlUR XS9 SUlgNr)rname_translatorrEpathlibPathr init_schema _connection)r&rrrErs r __init__Database.__init__msC#>h&>&>,1W\\%(t  Qr ctXlUR(a[URUS9Ulg/Ulgr)rrr)r&rrs r rDatabase.init_schemays1MQWW GG/HJ Z\ r r$cZURVs0sHoRU_M sn$s snfr)rr<)r&rs r tdictDatabase.tdict~s$#';;/;a ;///s(r"r#cU=(d U$)a6 A callable with this signature can be passed into DB creation to control the names of the schema objects. :param table: CSVW name of the table before translation :param column: CSVW name of a column of `table` before translation :return: Translated table name if `column is None` else translated column name r)r"r#s r rDatabase.name_translatorsr cUR(a=[R"[R"[ UR555$UR (d[R"S5UlUR $)Nz:memory:)r contextlibclosingsqlite3connectr.rrks r connectionDatabase.connectionsQ ::%%gooc$**o&FG G&z:D r c UbSRU5nOSnSR[URURURSR55[URURURSR55[URUR55U5nUR U5nUR 5VVV s0sHQnUS[USR5USRS55VV s/sH upUcX4OUPM sn n_MS sn nn$s sn nfs sn nnf)NzWHERE context = '{0}'rzgSELECT {0}, group_concat({1}, ' '), group_concat(COALESCE(context, ''), '||') FROM {2} {3} GROUP BY {0}rrz||) r:rArEr<rrRrUzipsplit) r&rDr"r context_sqlrOrVrrMvs r select_many_to_manyDatabase.select_many_to_manys/  188AKK#Vt~~ejj%--2B2G2GHIt~~ejj%--2B2G2GHIt~~ejj12  ZZ_NP[[]\MZ aD"1Q4::<1D1ABDBtq%_A6!3BD DMZ\ \D\s'"}},4r cjURX5nU(aU=(d SRU5$U$)Nr)rjr)r&rr~valueseps r split_valueDatabase.split_values,nnU*+. ""3'9E9r c [R"[5nUR5nURR GHfn00[R"[ 5penURUnURGHnUR[/X@RX8R5'UR[;a5[URSX@RX8R5S'O;[URRX@RX8R5S'UR (dMURS:Xa*UR XPRX8R5'GMSXPRX8R5'GM" UR"R%5HDupUR'X)U5R%5HupXU URX85'M MF [)X RU55upU GHn[R*"5n[-X5Hunn UU;a{U cSUU'MU (d/UU'M$UUS:Xa[.R0"U 5UU'MHU =(d SR3UU5Vs/sHnUUS"U5PM snUU'MU bUUS"U 5OSUU'M UR4(a9[7UR45S:Xa XRX7R4S5OSn UR9UR"Vs0sHnU/_M sn5 UR9UR;U 055 XRU5R=U5 GM GMi SSS5 U$s snfs snf!,(df  U$=f)z :return: A `dict` where keys are SQL table names corresponding to CSVW tables and values are lists of rows, represented as dicts where keys are the SQL column names. rrrjsonNrr)r defaultdictrTrrrdictrrr<r rErcrdrrirjrrrrYrrrloadsrrrupdaterwrx)r&resconnrrfsepsrefsr"rrpkrrXrGrPrWrMv_s r rg Database.reads %%d+ __ $** ')"k.E.Ed.Kt 5)!==C@C(?SGNN5((;<}}0FNs}}F]^_F`uhh ?@C&cmm4>> uhh ?@C}}}==H4DGMMDxx!@ADJDxx!@A) %11779GC!%!9!9$C!H!N!N!P?@R!;<"Q :$D..*?@ C#//1A #D19 y'+!%&')!!%aF!2'+zz!}!EFW"OOTXYZT[D\']D\b 1 b(9D\']!78}71:a=#3$AaD!/!,,U5F5F1G11L>>%1B1B11EFGRVHHU-?-?@-?ae-?@AHHTXXb"-.u-.55a8% 9+` (^ A[ ` s3DOE'O-N7 BO N<AO7 O OcX24$)an Context for association tables is created calling this method. Note: If a custom value for the `context` column is created by overwriting this method, `select_many_to_many` must be adapted accordingly, to make sure the custom context is retrieved when reading the data from the db. :param table: :param column: :param fkey: :return: a pair (foreign key, context) r)r&r"r#fkeys r association_table_context"Database.association_table_contexts |r Fc^UR"SUUUS.URR5D6$)Nforce _exists_ok _skip_extrar)writerrg)r&_forcerrs r write_from_tgDatabase.write_from_tgs5zz!#gglln  r rc ^UR(aKURR5(a,U(d [S5eURR5 UR 5nUR H+nUR URURS95 M- UR S5 UR5 [R"[5nUR GHnURU;aM//pURV s0sHoRU _M n n [XHR5GHupUR (a*[#UR 5S:XaXR SOSn/nUR%5GHunnUUR&;aU(deUR&Un[)UR/URV s/sHoRPM sn -5nU=(d /H0nUR+UUU5unnUUR-UUU45 M2 MUU ;a#U(aM[SR/U55eU Um[1U[5(aVTR2S:Xa/TR4=(d S R7U4S jU55nO-[8R:"U5nOUbTR=U5OSnU S:XaU R-TR5 UR-U5 GM U R-[)U55 GM [?XPRURU /U Q76 GM UR%5H$unn [?XPRUSUSS/U Q76 M& UR5 SSS5 gs sn fs sn f!,(df  g=f) zk Creates a db file with the core schema. :param force: If `True` an existing db file will be overwritten. z3db file already exists, use force=True to overwrite)rEzPRAGMA foreign_keys = ON;rrNz$unspecified column {0} found in datar;c3X># UHnTRU5=(d Sv M! g7f)rN)rf)r;vvrs r r=!Database.write..6s&DKHI" B(=2(=s'*) rexistsrunlinkrrrRrOrEcommitrrrTr<r enumeraterrrrtuplerrxr: isinstancercrjr?rdumpsrfrK)r&rrrrrDr"r rrGrFr}rXrrPr rrMrratkeyrrrrs @r rDatabase.writes ::$**++-- !VWW !!# __ " 599t~~9>?% JJ2 3 IIK**40D[[66&d+,9959a 95'ff 6FA==S-?1-D]]1-.JNF # 1.#%I2!"!2B$)277)rzz6Rz!vvz6R*R$SE()wBw040N0NqRSUW0X g $U 2 2Bg3F G(/ !}#.$,*4(N(U(UVW(X+Z%Z"&q'C)!T22#&==H#<),)=#(C(CDKHIDK)KA)- 1 A67mCKKN Av $ CHH 5"MM!,;!,<KKf .E7Fr>>1664?$?Q!T $zz| tr>>58U12YFF , IIKk 67S+ s-.B*OO.BO O O)rrrrrE)NNTrr)FFF)&r*r+r,r-rrr/r0Unionrrr.r rrrpropertyDictrr staticmethodrr ConnectionrrrrrrjListrrrgrrrr1rr r rrZs*GK;??C    ??6<< c0A#BC  '78  (.t'<  ] 0v{{3 >200 s FOOC,@ C   FLL););Z=O=O)OP \\"-s-3-6??33G-:&,,v{{3?OQTVZ?Z2[:6fkk#v{{63E3E'F"FG6p #u%AAr rr)%rrr/rrrrrrrrtcsvw.datatypesr csvw.metadatarr rdProtocolr r3rAr-r.SequencerTr0rrKTupler.rYr r[objectrrrrr r r5s6  $$   ,-/ .8: # 0 v  v <,1 w!!&%  ??4( D%w!!%#%&,,v{{3?OQWQ`Q`?`2a% @=@=@=Fm;m;m;b?C$"t$"&,ood&;$"GM{{S\G]$"Nkvkr