$HJjedZddlZddlmZmZmZmZmZddlZddl Z ddl Z ddl Z ddl Z ddl Z ddlmZmZddlZddlZddlmZddlmZmZddlmZd Zej6Gd d Zed ed ed eeeeedeeeej@eddZ!GddeZ"GddeZ#de$de$fdZ%ddde jLde"de$dee$de'deef d Z(de jLde$de)e'e$effd!Z*ej6Gd"d#Z+ej6Gd$d%Z, d-d&ej,d'eede'e,fd(Z-Gd)d*Z.ej6Gd+d,Z/y).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)OptionalUnionProtocolCallableAny)SequenceIterator) DATATYPES) TableGroupDatatype)optcastc|SN)ss Q/Users/ahmed/devFolder/claude-voice/.venv/lib/python3.12/site-packages/csvw/db.pyidentityr-s HcPeZdZUdZeed<eZee ge fed<eZ ee ge fed<y)DBTypez2A DB datatype together with read/write converters.nameconvertreadN) __name__ __module__ __qualname____doc__str__annotations__rrrrrrrrrr1s4< I$,GXseSj !,!)D(C5#: )rrTEXTINTEGERREALBLOB)stringintegerbooleandecimal hexBinaryc(eZdZddedeedefdZy)SchemaTranslatorNtablecolumnreturncyrr)selfr,r-s r__call__zSchemaTranslator.__call__C rr)rrrrrr1rrrr+r+Bs  c 8C= C rr+ceZdZdedefdZy)ColumnTranslatorr-r.cyrr)r0r-s rr1zColumnTranslator.__call__Hr2rN)rrrrr1rrrr4r4Gs s s rr4namesr.c2djd|DS)z=Returns a comma-separated list of quoted schema object names.,c3(K|] }d|d yw)`Nr).0rs r zquoted..Ns2EDavQKEs)join)r6s rquotedr>Ls 882E2 22rFsingledb translater,keysrowsr@c b|rkt|Dcgc] }||| c}}dj|Dcgc]}dc}} dt||d|d| d} |j| |y y cc}wcc}w#|s|D]} t||||| dYy t | t |xYw) 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. r8?z INSERT INTO z (z ) VALUES ()Tr?N)r>r= executemanyinsertprint) rArBr,rCr@rDkcols_valssqlrows rrIrIQs" T:T %+T:;xxd+dd+,VIe$456bjaP  NN3 %  :+ C2y%s4H c d sA/ A4A99B.B.c|jdt|}|jDcgc]}|d }}|t|j fScc}w)z;Shortcut to construct and execute simple SELECT statements.zSELECT * FROM r)executer> descriptionlistfetchall)rAr,cudrLs rselectrXrsR nVE]O4 5B.. ).QAaD.D ) bkkm$ $$ *s AceZdZUdZeed<dZeed<dZeed<dZe ed<dZ e ed <dZ e ed <d Zd ed eefdZd ed efdZy)ColSpecze A `ColSpec` captures sufficient information about a :class:`csvw.Column` for the DB schema. rr% csvw_typeN separatordb_typeFrequiredcsvwc|jxsd|_|jtvrt|j|_nHtdt|jj t|jj |_|jrK|jdk7r;td|jj|jj|_yyy)Nr%r!) r[TYPE_MAPr]rr to_string to_pythonr\rrr0s r __post_init__zColSpec.__post_init__s38 >>X %#DNN3DL! $..1;;Yt~~=V=`=`bDL >>dllf4!&$,,*>*> @Q@QRDL5>rrBr.c |jsy|j||j}}g}|j |jdddj |j }|jK|r(|j |d|d|d|jdn!|j d |d |j|j|r(|j |d|d |d|jdn|j d |d |jntd |j|j|jfDr|jr!|j d|d|j|jr!|j d|d|j|jr!|j d|d |jdj|S)a 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)rgrhz(`z`) >= z('z')r:z` >= z`) <= z` <= c3$K|]}|du ywrr)r;ccs rr<z ColSpec.check..sO*NB4*Nszlength(`z`) = z AND ) r_rminimummaximumgetr[appendanylength minLength maxLengthr=)r0rBccname constraintsfuncs rcheckz ColSpec.checksyy99i 25 II !qyy'<&c$..! yy$&&$r%tfBqyykQS'TU&&5'qyyk'BCyy$&&$r%tfBqyykQS'TU&&5'qyyk'BC O188Q[[!++*NO Oxx""XeWE!((#DE{{""XeWF1;;-#HI{{""XeWF1;;-#HI||K((rc|j|}|jrdnd}|rd|dnd}d||jd|jj||S)zNFormat the column metadata suitable for inclusion in a CREATE TABLE statement.z NOT NULLz CHECK (rGr:z` )rwr^rr])r0rB_checknull_constraintcheck_constraints rrOz ColSpec.sqlscI&)-+B39XfXQ/r9TYY'(4<<+<+<*=o=NO_N`aar)rrrrrr r[r\r]rr^boolr_r rer4rrwrOrrrrZrZysv IIsIsGVHdD(S!)/!)HSM!)Fb-b#brrZcReZdZUdZeed<ejeZ ee ed<ejeZ eed<eje jZe jed<dZeeeed<e dd ej&d eed dfd Zedd Zded efdZy) TableSpeca7 A `TableSpec` captures sufficient information about a :class:`csvw.Table` for the DB schema. .. note:: We support "light-weight" many-to-many relationships by allowing list-valued foreign key columns in CSVW. In the database these columns are turned into an associative table, adding the name of the column as value a `context` column. Thus, multiple columns in a table my be specified as targets of many-to-many relations with the same table. .. seealso:: ``_ rdefault_factorycolumns foreign_keys many_to_manyN primary_keyr,drop_self_referential_fksr.c T||j|jj}|jjDchc]}|js|j }}|jj D]}|jjrt|jdk(r#|jd|vrt|jjdk(s6Jd|jjd|jjd|jrt|jdk(sJd|jdtj|j|jd|jjj |jjd|j"|jd<W|r/|jjj |jk(r|j$j't)|j|jjj t)|jjf|jjD]}|j |j"vs|j+d }|jj't-|j |r |j.n||j+d |j+d |j+d  |Scc}w) 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. )rrr rzComposite key z in table z referencedzTable zJ referenced by list-valued foreign key must have non-composite primary keydatatyper\r^)rr[r\r^r_) local_name tableSchema primaryKeyrr\header foreignKeys referenceschemaReferencelencolumnReferenceresourcerrrassociation_tabler%rrrnsortedinheritrZbase)clsr,rspecrs list_valuedfkrs rfrom_table_metadatazTableSpec.from_table_metadatas((e6G6G6R6RS).):):)B)BR)BAakkqxx)B R##//B<<//r))*a/B4F4Fq4I[4Xr||;;<A@)",,*F*F)GzLL112+?@A ++D4D4D0E0J7!$))-567J@I?Z?Z ((+ --44 44Q7 @D%%b&8&8&;< 4 8M8M8T8TX\XaXa8a%%,,r112 --44r||;;<.%0.""**Axxt00099Z0 ##G/7hmmXii 4YYz2:. %+ ESs L%L%c@t|d|}t|d|}|j|jk(r*|xjdz c_|xjdz c_||d|||tdg|jg||gf|jg||gfgS)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. rM_1_2context)rrr)rZr)ratableapkbtablebpkafkbfks rrzTableSpec.association_tables#'(#'( 88sxx  HH H HH H81VH%#wy12((VcU+((VcU+  rrBc tj||j}|jDcgc]}|j |}}|j r;t |j Dcgc] }|| c}}|jd|d|jD]k\}}} t |Dcgc] }|| c}} t ||} t | Dcgc] }||| c}} |jd| d| d| dmdj|}djd ||jd |dgScc}wcc}wcc}wcc}w) z[ :param translate: :return: The SQL statement to create the table. z PRIMARY KEY(rGz FOREIGN KEY(z ) REFERENCES (z) ON DELETE CASCADEz,  zCREATE TABLE IF NOT EXISTS `z` () functoolspartialrrrOrr>rnrr=) r0rB col_translatecolclausesrsqcolsrrefrefcolsfkcolsrtablepkcolss rrOz TableSpec.sqls^ ")))TYY? 59\\B\c377=)\B   t7G7GH7G!]1-7GHIE NN\%2 3 $ 1 1 BW;1mA.;.Ls-`E_cCFg%9#a&E/9E_sz7there seem to be cyclic dependencies between the tables) tabledictvaluesrrrrrrrTrCallrpop ValueError)rrtablestatirr,s @@rschemar/sF$$&  ) ) -F * Hqvv..'')B F277O* '%%'G A QW Q&++-(E`VE]E_E_``!'E!2 ) QWRSS  !!rc eZdZdZ ddedeeeje fdee dee fdZ ddede fdZ ed ee effd Zedd e d ee d e fd Zd eej*ej.ffdZdde dee d e fdZd ee eee e fe fffdZde de d ee fdZde de d eee e dffdZd ee eej@ffdZ!dZ"ddZ#dZ$dddddZ%y) Databasea 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. NrfnamerBrc|xstj|_|rtj|nd|_|j ||d|_y)Nr)rname_translatorrBpathlibPathr init_schema _connection)r0rrrBrs r__init__zDatabase.__init__isF#>h&>&>,1W\\%(t  7PQrcr||_|jrt|j||_yg|_y)zLInititialize the db schema, possibly ignoring self-referential foreign keys.rN)rrr)r0rrs rrzDatabase.init_schemaus3MQWW GG/HJ Z\ rr.cV|jDcic]}|j|c}Scc}wr)rr)r0rs rtdictzDatabase.tdict{s&#';;/;a ;///s&r,r-c|xs|S)af 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 rrzDatabase.name_translatorsrc|jr;tjtjt |jS|j stjd|_|j S)z,DB connection to be used as context manager.z:memory:)r contextlibclosingsqlite3connectrrrds r connectionzDatabase.connectionsQ ::%%gooc$**o&FG G&z:D rtnamertcp|rt|j||St|j|S)z,Translate and then quote a db schema object.)r>rB)r0rrts r_qtz Database._qts/ $..67 7dnnU+,,rc Z|d|d}nd}tj|j|j}d||jdjd||jdjd|d |d ||jdj }|j |}|j D cic]N}|dt|dj|d jd D cgc] \} } || | fn| c} } Pc} } }Scc} } wcc} } }w) zDSelect data from an association table, grouped by first foreign key.zWHERE context = ''ryzSELECT rz, group_concat(r z>, ' '), group_concat(COALESCE(context, ''), '||') FROM  z GROUP BY z||) rrrrrrRrUzipsplit) r0rAr,r context_sqlqtrOrVrrKvs rselect_many_to_manyzDatabase.select_many_to_manysD  -gYa8KK   txx 4EMM!,11234&&(q)9)>)>&?%@AtfAk]*R a8H8M8M5N4OQZZ_NP[[]\MZ aD"1Q4::<1D1ABDBtq!%_Aq6!3BD DMZ\ \D\s;D&D D& D&c|jD]d}|j||k(s|j|jD]1}|j||j|k(s#|jccSfy)ze :return: separator for the column specified by db schema names `tname` and `cname`. N)rrBrrr\)r0rrtrrs rr\zDatabase.separatorsbJJD~~d#u,::d+33C~~dCHH5>"}},4 rcX|j||}|r|xsdj|S|S)z7Split a value if a separator is defined for the column.ry)r\r)r0rrtvalueseps r split_valuezDatabase.split_values.nnUE*+. ""3'9E9rc tjt}|j5}|jj D]}t |j|||j}|jjjD]P\}}|j|||jD]'\}}||j||j||<)Rt||j|\} } | D]>} ||j|j|j!t#| | @ ddd|S#1swY|SxYw)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. N)r defaultdictrTrrr TableReadSpecrrBr,ritemsr referencesrXrnread_rowr) r0resconnrrrrpkrrLrDrPs rrz Database.reads  %%d+ __ $** %TZZ%6t~~N#zz66<<>GC!%!9!9$C!H!N!N!PAJK+DNN5#,FG"Q ?$D$..*?@ dCu-.55dmmCcN6ST +  s DEEc ||fS)a 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)r0rMr-fkeys rassociation_table_contextz"Database.association_table_contextsV|rFc^|jd|||d|jjS)z5Write the data from the contained tablegroup to a db.force _exists_ok _skip_extrar)writerr)r0_forcerrs r write_from_tgzDatabase.write_from_tgs7tzz!#gglln  rc|gg}}|jDcic]}|j|}}t|D]\} } |jr*t |jdk(r| |jdnd} g} | j D]\} }| |j vr| sJt|j | jg|j | jDcgc]}|jc}z}||j|xsgDcgc]+}t| gt|j|| |z-c}| |vr|rtd| d|| t|trNjdk(r)jxsdjfd|D}n5t!j"|}n|j$j'|nd}| dk(r|j)j| j)||j)t| ||fScc}wcc}wcc}w)Nr rzunspecified column z found in datar%;c3\K|]#}jj|xsd%yw)ryN)r]r)r;vvrs rr<z%Database._get_rows..s->!#4#9#9":34>>!3D3L3L"M3La1663L"M#NOEK&&#$77(-#*BrdT$*H*HAr*R%SST#*(-.}&$(+>qc)PQQq'C!!T*==H4!$!5# ; ;?% JJ2 3 IIK**40D[[66&!^^AuQVV}dKP dr4>>1664?$? ! $zz| tr4>>58U12YFF , IIK#  s D%F  F)NNTrr)FFF)&rrrrr rrrrrr+r}rrpropertydictrr staticmethodrr Connectionrrrrr rr\rTrrrrrrrrrrrrrVs*9=488<    E',,"345   01  (0~  ]j]T] 0tCN+00 s HSM S   E'"4"4j6H6H"HI --Xc]-c- \c5sTWxZ]I]C^>^9_\ s 3 8C= ::S:E$s)SRVBV. #J#u%rrc$eZdZUdZeed<eed<eed<eje Z e ee ee ffed<eje Ze eefed<ejdZe ed <d Zd ee eefd ej(eeffd Zy)rz1Bundles data informing the reading of table rows.r,rrBr converters separatorsc4tjtSr)rrrrrrzTableReadSpec.5sAXAXY]A^rrcp|jjD]}|jtvrt|jj}nt |jj }|j|f|j|j|j|j<|js|jdk(r>|j|j|j|j|j<d|j|j|j|j<y)Nr%r) r,rr[rarr rcrrrBr\r)r0rconvs rrezTableReadSpec.__post_init__7s::%%C}}( .66 /99DGHHdCSDOODNN499chh? @}}==H,KN==DOODNN499chh$GHKQDOODNN499chh$GH&rrPr.cXtj}|D]\}}||jvr|d||<|sg||<$|j|dk(rtj|||<O|xsdj |j|Dcgc]}|j |d|c}||<||j |d|nd||<|jjrXt|jjdk(r6||j|j|jjdnd}|j|jjDcic]}|gc}|j|jj|i|Scc}wcc}w)zRead a table according to spec.Nrryr r)rrrrloadsrrr,rrrBrupdaterrrm)r0rPrWrKrv_rs rrzTableReadSpec.read_rowEs  # # %DAqDOO#9AaDAaD__Q'61::a=AaDABbPTP_P_`aPb@cd@c"1DOOA.q1"5@cdAaD34=,tq)!,Q/d!zz%%#djj.D.D*E*Jt~~dii)?)?)BC DPT  !8!89!8A!R%!89: $$R,-e :s F"$ F'N)rrrrrr rr+rrrrr rrrrer rrrrrrrrr-s;  I2C+2C2CTX2YJS%X ../Y!2!2!24!HJS#XH({((9^_J_ RHU38_5+:Q:QRUWZRZ:[rrr)0rrtypingrrrrrr(rrrrrcollections.abcrr rr_csvw.datatypesr csvw.metadatar r utilsrr dataclassrintr}floatDecimalrar+r4rr>rrTrIr rXrZrrrrrrrr/s6 ;;. $.  ***Vni iwt}=fgenggoo.FG   x  x 33333%* w!!&#  D> B%w!!%#%%S 88K2L% =b=b=b@ qZqZqZj8<$"t$"&.tn$"@DY$"NTTn ***r