PiadZddlZddlZddlZddlZddlZddlZddlZddlZddl Z ddl Z ddl m Z ddl mZdZdeefdeefddd fd d d fd eefdZGddejZGddejZdZdddejdededejededejef dZdejdedejejeejffdZe jGdd Z e jGd!d"e!Z" d)d$e jd%ejedeje"fd&Z#Gd'd(e!Z$dS)*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) TableGroupc|SNss [/home/jaya/work/projects/VOICE-AGENT/VIET/agent-env/lib/python3.11/site-packages/csvw/db.pyidentityr ,s HTEXTINTEGERc(||nt|Sr)intrs r r;sqy!!c!ffr c(||nt|Sr)boolrs r rr<sqy!!d1ggr REALc(||nt|Sr)floatrs r rr?sqy!!eAhhr c2||ntj|Sr)decimalDecimalrs r rr@sqy!!goa&8&8r BLOB)stringintegerbooleanr hexBinaryc:eZdZddedejedefdZdS)SchemaTranslatorNtablecolumnreturncdSrr)selfr!r"s r __call__zSchemaTranslator.__call__I r r)__name__ __module__ __qualname__strtypingOptionalr&rr r r r HsI  c 6?3+? 3      r r ceZdZdedefdZdS)ColumnTranslatorr"r#cdSrr)r%r"s r r&zColumnTranslator.__call__Nr'r N)r(r)r*r+r&rr r r/r/Ms6 s s      r r/c@dd|DS)N,c3@K|]}d|VdS)z`{0}`N)format).0names r zquoted..Ss.;;TGNN4((;;;;;;r )join)namess r quotedr:Rs# 88;;U;;; ; ;;r Fsingledb translater!keysrowsr<c |rdttfd|Ddd|D} |||dS#|s|D]}t |||dYdSt |t |xYwdS)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})c(g|]}|Srr)r5kr!r>s r zinsert..js%777QYYua((777r r2cg|]}dS)?r)r5_s r rDzinsert..ks(((ac(((r Tr;N)r4r:r8 executemanyinsertprint)r=r>r!r?r<r@sqlrows `` r rIrIVs " 299 99U## $ $ 77777$777 8 HH((4((( ) )++  NN3 % % % % %  IIC2y%s4HHHHHIIIc d s$A<<B<B<r#c|dt|}d|jD}|t |fS)NzSELECT * FROM {0}cg|] }|d S)rr)r5ds r rDzselect..zs ) ) )QAaD ) ) )r )executer4r: descriptionlistfetchall)r=r!cucolss r selectrVxsX '..ve}}== > >B ) )". ) ) )D bkkmm$$ $$r c^eZdZdZejZejddZejdZejdZ ejdZ ejdZ ejdZ ejdZ dZd ed ejefd Zd ed efd ZdS) ColSpecze A `ColSpec` captures sufficient information about a :class:`csvw.Column` for the DB schema. rc|r|ndS)Nrrrs r rzColSpec.s1>Raa(r )default converterNrZFc&|jtvr&t|j\|_|_|_n?d|_t |jj|_t |jj|_|jr|jdkr d|_dSdSdS)Nr ) csvw_typeTYPE_MAPdb_typeconvertreadr to_string to_python separatorr%s r __attrs_post_init__zColSpec.__attrs_post_init__s >X % %4DL!$.1;DI > "dlf44!DLLL " "44r r>r#c|jsdS|j||j}}g}|j|jddd|j}|j`|r0|d||j|n.|d||j|j`|r0|d||j|n|d||jntd |j |j |j fDr|j r.|d ||j |j r.|d ||j |j r.|d ||j d |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)rirjz{2}(`{0}`) >= {2}('{1}')z `{0}` >= {1}z{2}(`{0}`) <= {2}('{1}')z `{0}` <= {1}c3K|]}|duV dSrr)r5ccs r r7z ColSpec.check..s&OOB4OOOOOOr zlength(`{0}`) = {1}zlength(`{0}`) >= {1}zlength(`{0}`) <= {1}z AND ) csvwr6minimummaximumgetr^appendr4anylength minLength maxLengthr8)r%r>ccname constraintsfuncs r checkz ColSpec.checksy  F9ii 225 I !qy'<&c$.!! y$P&&'A'H'HPQPY[_'`'`aaaa&&~'<'_checks r rKz ColSpec.sqlshI&& '' Idi L= 0KKb-3 ;N ! !& ) ) ) == =r )r(r)r*__doc__attribr6r^rer`rarbr}rmrgr/r,r-r+rzrKrr r rXrX~s 4799D4R4RSSSI%%%Idgd###Gdgd###G 474 Dtwu%%%H 474 D"""!)/!)FOC4H!)!)!)!)F=-=#======r rXceZdZdZejZejejeZ ejejeZ ejeje j Z ejdZe d dejdejeddfdZed d Zd edefd ZdS) 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:: ``_ r\NTr!drop_self_referential_fksr#c ||j|jj}d|jjD}|jjD]}|jjst|jdkr|jd|vrt|jjdks2Jd |jj|jj |j rt|j dks"Jd |j t|j |j d|jj j|jjd|j|jd<)|r|jj j|j ksU|jt'|j|jj jt'|jjf|jjD]}|j|jvr|d}|jt-|j|r|jn||d|d |d |S) 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. )r6 primary_keyc*h|]}|j |jSr)reheaderr5rvs r z0TableSpec.from_table_metadata..s!RRRAakRqxRRRr rz)Composite key {0} in table {1} referencedzSTable {0} referenced by list-valued foreign key must have non-composite primary keydatatyperer})r6r^rer}rm) local_name tableSchema primaryKeycolumns foreignKeys referenceschemaReferencelencolumnReferencer4resourcerr6rassociation_tabler many_to_many foreign_keysrqsortedrinheritrXbase)clsr!rspec list_valuedfkrvrs r from_table_metadatazTableSpec.from_table_metadatas~s(e6G6RSSSRR):)BRRR #/  B</ r)**a//B4Fq4I[4X4Xr|;<<AAACJJL8L133BAA +8D4D0E0E0J0J0J&&,fTY&7&71K0JJ@I?Z?Z (+ -4 4Q7 @@D%b&8&;<< 4 8M8TX\Xa8a8a%,,r122 -4r|;<<. "*  Axt00099Z00 ##G/7EhmmXii 44YYz22:.. %%% r c~td||}td||}|j|jkr |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. z{0}_{1}_1_2context)r6rr)rXr4r6)ratableapkbtablebpkafkbfks r rzTableSpec.association_table si&&vs3344i&&vs3344 8sx   HH HH HH HHs!!&&11#wy112(VcU+(VcU+    r r>c Htj|jfd|jD}|jr@|dtfd|jD|jD]m\}}|dtfd|Dttfd|Dnd|jd |S) z[ :param translate: :return: The SQL statement to create the table. c:g|]}|Sr)rK)r5col col_translates r rDz!TableSpec.sql..&s%BBBc377=))BBBr zPRIMARY KEY({0})c&g|] }|Srrr5rvrs r rDz!TableSpec.sql..)s#===q--""===r z6FOREIGN KEY({0}) REFERENCES {1}({2}) ON DELETE CASCADEc&g|] }|Srrrs r rDz!TableSpec.sql..,s#666aq))666r c(g|]}|Srr)r5rvrefr>s r rDz!TableSpec.sql...s%<<clausesrrefcolsrrs ` @@r rKz TableSpec.sql sf "))TY?? BBBBT\BBB   A NN-44V====D,<===6?@@ A A A $ 1 @ @ BW NNSZZ666626667yy~~&&<<<<.Ps9``cCFg%9#a&E/``````r z7there seem to be cyclic dependencies between the tables) tabledictitemsrrr6rvaluesrrrRr?allrpop ValueError) rrtablestnametatirr!s @@r schemar3siF **,,!! u  ) ) -F * H Hqv.'')) ! !B F27OO !%''G A QWW Q&++--((  E`````VE]E_````` !'E!2!2  QWWTRSSS   ! !!r c $eZdZdZ ddedejejej e fdeje deje fdZ dd Zed eje effd Zedd e d eje d e fdZd ejejejffdZd efdZde de d eje fdZd ejeje e dffdZd eje ejejffdZ dZ!ddZ"dddddZ#dS)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. NTrfnamer>rc|p tj|_|rtj|nd|_|||d|_dSNr)rname_translatorr>pathlibPathr init_schema _connection)r%rrr>rs r __init__zDatabase.__init__msV#>h&>,1;W\%(((t  7PQQQr c\||_|jrt|j|ng|_dSr)rrr)r%rrs r rzDatabase.init_schemaysDMQW]f G/HJJJJZ\ r r#c$d|jDS)Nci|] }|j| Srr6)r5rs r z"Database.tdict..s///a///r )rrfs r tdictzDatabase.tdict~s//4;////r r!r"c |p|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 r rzDatabase.name_translatorsr c|jr8tjtjt |jS|jstjd|_|jS)Nz:memory:)r contextlibclosingsqlite3connectr+rrfs r connectionzDatabase.connectionsV : H%goc$*oo&F&FGG G ;&z::D r c d}nd}dt||j|jdjt||j|jdjt||j|}||}fd|DS)NzWHERE context = '{0}'r|zgSELECT {0}, group_concat({1}, ' '), group_concat(COALESCE(context, ''), '||') FROM {2} {3} GROUP BY {0}rrc i|]V}|dfdt|d|ddDWS)rc&g|] \}}||fn|Srr)r5rCvrs r rDz;Database.select_many_to_many...s?DDDq!%_Aq66!DDDr rz||)zipsplit)r5rrs r rz0Database.select_many_to_many..s\\\HI aDDDDD"1Q4::<<1D1A1ABBDDD\\\r )r4r:r>r6rrPrS)r%r=r!r context_sqlrKrTs ` r select_many_to_manyzDatabase.select_many_to_manys  188AAKKK#Vt~~ej%-2B2GHHIIt~~ej%-2B2GHHIIt~~ej1122  ZZ__\\\\MO[[]]\\\ \r rrwc|jD]Z}|||kr?|j|jD],}|||j|kr |jccS-[dS)ze :return: separator for the column specified by db schema names `tname` and `cname`. N)rr>rr6re)r%rrwr6rs r rezDatabase.separatorsJ - -D~~d##u,,:d+3--C~~dCH55>>"},,,,,? - -r cd|||}|r|pd|n|S)Nr|)rer)r%rrwvalueseps r split_valuezDatabase.split_values7nnUE**+.9 ""3'''E9r c :tjt}|5}|jjD]D}iitjt c}}|j|}|jD]}|j tg| ||j <|j tvr;t|j d| ||j d<n9t|j j| ||j d<|jrM|j dkr$|j|| ||j <d|| ||j <|jD]S\}}||||D]$\} } | || | ||<%Tt)|| |\} } | D]s} tj}t-| | D]\} |vrb| d|<| sg|<|dkrt/j| |<=fd| pd|D|<k| d| nd|<|jr?t7|jdkr'|| ||jdnd} |d |jD||| i|| ||uF dddn #1swxYwY|S) 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. rrrjsonNc>g|]}d|S)rr)r5v_rarCs r rDz!Database.read..s+']']']b  1 b(9(9']']']r r|rci|]}|gSrr)r5rCs r rz!Database.read..s@@@a@@@r )r defaultdictrRrrrdictrrr6r r>r^r_rrdrerrrrVrrrloadsrrrupdaterprq)r%resconnrsepsrefsr!rrpkrrUr@rLrOrarCs @@r rbz Database.reads %d++ __  / 9$*. 9. 9 ')"k.Ed.K.K#t 5)!= K KC@C(?SGDNN5#(;;<}00FNs}F]^_F`uch ? ?@CC&cm4> uch ? ?@C}K=H44DGMDsx!@!@AADJDsx!@!@A %17799AAGC!%!9!9$C!H!H!N!N!P!PAAA?@Rs!;!;<<A$D$..*?*?@@ d99C#/11A #D# O O199 y'+!%&^')!!%aF!2!2'+z!}}!']']']']']QW"OOTXYZT[D\D\']']']!78}=71:a=#3#3#3$AaDD ,W14U5F1G1G11L1L4>>%1B11EFFGGRVHH@@U-?@@@AAAHHTXXb"--...u--.55a8888%99. 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9/ 9` sMNNNc ||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)r%r!r"fkeys r association_table_contextz"Database.association_table_contextsV|r FcR|jd|||d|jS)Nforce _exists_ok _skip_extrar)writerrb)r%_forcer r s r write_from_tgzDatabase.write_from_tgs?tz!#gllnn  r r c |jrC|jr*|std|j|5}|jD]0}|||j1|d| tj t}|jD]0}|j |vr gg} } d|jD} t||j D]\} } |jr+t#|jdkr| |jdnd}g}| D]d\}}||jvrx|sJ|j|}t)|j gd|jDz}|pgD]:}||||\}}|||||f;|| vr%|rtd || |t1|trIjd kr)jpd fd |D}n.t9j|}n||nd}| dkr| j ||f| t)|t?||j|j | g| R2|D](\}} t?||j|d|ddg| R)| ddddS#1swxYwYdS) z 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)r>zPRAGMA foreign_keys = ON;ci|] }|j| Srrrs r rz"Database.write..s555a555r rrNcg|] }|j Srrrs r rDz"Database.write..%s6R6R6R!qv6R6R6Rr z$unspecified column {0} found in datar;c3FK|]}|pdVdS)r|N)ra)r5vvrs r r7z!Database.write..6sSDKDKBD B(=2DKDKDKDKDKDKr ) rexistsrunlinkrrrPrKr>commitrrrRr6r enumeraterrrrtuplerrqr4 isinstancer^rer8rdumpsrarI)r%r r r rr=r!rrr@r?rUrrLrrrCrratkeyrrrrs @r rzDatabase.writes6 : $$*++-- $ $ !VWWW !!### __  5 " @ @ 599t~9>>???? JJ2 3 3 3 IIKKK*400D[( @( @6&&d5519555'af 66"/"/FAs=O-0-?-?1-D-DQ]1-..JNF # --1..#%II2!"!2B$)27)6R6Rrz6R6R6R*R$S$SE()wBHH040N0NqRSUW0X0X g $U 2 2Bg3F G G G GH !}}#.!Z$,*4(N(U(UVW(X(X+Z+Z%Z"&q'C)!T22N#&=H#<#<),)=#(C(CDKDKDKDKHIDKDKDK)K)KAA)- 1 AA67mCKKNNN Avv $ CH 5 5 5"MM!,,,,KKf ....r4>164?$?????#zz|| G G tr4>58U122YFFFFFF IIKKKk5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 s LM22M69M6)NNTrr)FFF)$r(r)r*rrr,r-Unionrrr+r rrrpropertyDictrr staticmethodrr ConnectionrrrrrreListrrrbrrrrr r rrZsF*GK;??C     ?6< c0A#BC  '78  (.t'<     ]]]] 0v{3 >2000X0  s FOC,@ C   \  FL);Z=O)OP    \\\\\"-s-3-6?33G----:&,v{3?OQTVZ?Z2[::::6fk#v{63E'F"FG6666p #u%AAAAAAAr rr)%rrr,rrrrrrrrmcsvw.datatypesr csvw.metadatarr r_Protocolr r/r:r#r+SequencerRr-rrITupler$rVr rXobjectrrrrr r r+s)6  $$$$$$$$$$$$     ,,--/ ..88: #  0     v        v   <<<,1 w!&%  ?4( D%w!%#%&,v{3?OQWQ`?`2a%%%% @=@=@=@=@=@=@=@=Fm;m;m;m;m;m;m;m;b?C$"$"t$"&,od&;$"GM{S\G]$"$"$"$"Nkkkkkvkkkkkr