o ia@sdZddlZddlZddlZddlZddlZddlZddlZddlZddl Z ddl Z ddl m Z ddl mZddZdeefdeefdd d d d fd d d dd fdeefdZGdddejZGdddejZddZdddejdededejededejef dd Zdejded!ejejeejffd"d#Ze jGd$d%d%Z e jGd&d'd'e!Z" (d/d)e jd*ejed!eje"fd+d,Z#Gd-d.d.e!Z$dS)0a( 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) TableGroupcCs|SNsrr;/home/ubuntu/.local/lib/python3.10/site-packages/csvw/db.pyidentity,r TEXTINTEGERcC|dur|St|Sr)intrrrr;rcCr r)boolrrrrr<rREALcCr r)floatrrrrr?rcCs|dur|St|Sr)decimalDecimalrrrrr@BLOB)stringintegerbooleanr hexBinaryc@s*eZdZddedejedefddZdS)SchemaTranslatorNtablecolumnreturncCdSrr)selfrrrrr__call__Ir zSchemaTranslator.__call__r)__name__ __module__ __qualname__strtypingOptionalr"rrrrrHs"rc@seZdZdedefddZdS)ColumnTranslatorrrcCr rr)r!rrrrr"Nr zColumnTranslator.__call__N)r#r$r%r&r"rrrrr)Msr)cGsddd|DS)N,css|]}d|VqdS)z`{0}`N)format).0namerrr Sszquoted..)join)namesrrrquotedRsr1Fsingledb translaterkeysrowsr3c s|rIdttfdd|Dddd|D}z |||WdS|s@|D] }t|||ddq0YdSt|t|dS) 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})cg|]}|qSrrr,krr5rr jrzinsert..r*cSsg|]}dqS)?r)r,_rrrr<ksTr2N)r+r1r/ executemanyinsertprint)r4r5rr6r3r7sqlrowrr;rr@Vs" r@rcCs4|dt|}dd|jD}|t|fS)NzSELECT * FROM {0}cSsg|]}|dqS)rr)r,drrrr<zrzselect..)executer+r1 descriptionlistfetchall)r4rcucolsrrrselectxsrKc@seZdZdZeZejddddZejddZejddZ ejddZ ejddZ ejddZ ejddZ d d Zd ed ejefd dZd ed efddZdS)ColSpecze A `ColSpec` captures sufficient information about a :class:`csvw.Column` for the DB schema. rcCs |r|SdS)Nrrrrrrrs zColSpec.)default converterNrMFcCsf|jtvrt|j\|_|_|_nd|_t|jj|_t|jj|_|jr/|jdkr1d|_dSdSdS)Nr ) csvw_typeTYPE_MAPdb_typeconvertreadr to_string to_python separatorr!rrr__attrs_post_init__s  zColSpec.__attrs_post_init__r5rcCs2|jsdS|j||j}}g}|jdus|jdur^ddd|j}|jdur@|r6|d||j|n |d||j|jdur]|rS|d||j|nA|d||jn6td d |j |j |j fDr|j rz|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)rZr[z{2}(`{0}`) >= {2}('{1}')z `{0}` >= {1}z{2}(`{0}`) <= {2}('{1}')z `{0}` <= {1}css|]}|duVqdSrr)r,ccrrrr.sz ColSpec.check..zlength(`{0}`) = {1}zlength(`{0}`) >= {1}zlength(`{0}`) <= {1}z AND ) csvwr-minimummaximumgetrPappendr+anylength minLength maxLengthr/)r!r5ccname constraintsfuncrrrchecks6   z ColSpec.checkcCs<||}d||j|j|jrdnd|rd|SdS)Nz`{0}` {1}{2}{3}z NOT NULLz CHECK ({0}))rjr+r-rRrequired)r!r5_checkrrrrBs   z ColSpec.sql)r#r$r%__doc__attribr-rPrWrRrSrTrlr]rYr)r'r(r&rjrBrrrrrL~s       #rLc@seZdZdZeZejeedZ ejeedZ ejee j dZ ejddZe ddejdejeddfdd Zedd d Zd edefd dZdS) 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:: ``_ rONTrdrop_self_referential_fksrc Csp||j|jjd}dd|jjD}|jjD]q}|jjst|jdkrj|jd|vrjt|jjdks>Jd |jj|jj |j rHt|j dksPJd |j t |j |j d|jj j|jjd|j|jd<q|rt|jj j|j ks|jt|j|jj jt|jjfq|jjD])}|j|jvr|d}|jt|j|r|jn||d |d |dd q|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. )r- primary_keycSsh|]}|jr|jqSr)rWheaderr,rfrrr sz0TableSpec.from_table_metadata..rz)Composite key {0} in table {1} referencedzSTable {0} referenced by list-valued foreign key must have non-composite primary keydatatyperWrl)r-rPrWrlr]) local_name tableSchema primaryKeycolumns foreignKeys referenceschemaReferencelencolumnReferencer+resourcersr-rqassociation_tabler many_to_many foreign_keysrasortedrtinheritrLbase)clsrrrspec list_valuedfkrfrxrrrfrom_table_metadatasP        zTableSpec.from_table_metadatacCstd||}td||}|j|jkr$|jd7_|jd7_|d||||tdg|jg||gf|jg||gfgdS)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-r|r)rLr+r-)ratableapkbtablebpkafkbfkrrrr s    zTableSpec.association_tabler5c st|jfdd|jD}|jr&|dtfdd|jD|jD]&\}}|dtfdd|Dttfdd|Dq)d|jd |S) z[ :param translate: :return: The SQL statement to create the table. csg|]}|qSr)rB)r,col col_translaterrr<&rz!TableSpec.sql..zPRIMARY KEY({0})cg|]}|qSrrrurrrr<)rz6FOREIGN KEY({0}) REFERENCES {1}({2}) ON DELETE CASCADEcrrrrurrrr<,rcr8rrru)refr5rrr<.rz,CREATE TABLE IF NOT EXISTS `{0}` ( {1} )z, ) functoolspartialr-r|rsrar+r1rr/)r!r5clausesrrefcolsr)rrr5rrB s  z TableSpec.sqlT)rrq)r#r$r%rnrorpr-FactoryrGr|r collections OrderedDictrrs classmethodr]Tabler'r(rrrrr&rBrrrrrqs&   2 rqTtgrrcsi}|jD]\}tj|d}|||j<|jD]}|||j<qqtd}|r\|dkr\|d7}t | D]t fdd|j DrU| <nq;|r\|dks1|rbtdt S)a Convert the table and column descriptions of a `TableGroup` into specifications for the DB schema. :param tg: CSVW TableGroup. :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: A pair (tables, reference_tables). rrrdrwc3s(|]}|dvp|dkVqdS)rwNr)r,rorderedrrrr.Ps&zschema..z7there seem to be cyclic dependencies between the tables) tabledictitemsrqrr-rvaluesrrrGr6allrpop ValueError)rrrtablestnametatirrrschema3s,      rc @sBeZdZdZ   d'dedejejej e fdeje deje fdd Z d(d d Zed eje effd dZed)de deje d e fddZd ejejejffddZd efddZde de d eje fddZd ejeje e dffddZd eje ejejffddZ dd Z!d*d"d#Z"d!d!d!d$d%d&Z#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. NTrfnamer5rrcCs8|ptj|_|r t|nd|_|j||dd|_dSNr)rname_translatorr5pathlibPathr init_schema _connection)r!rrr5rrrrr__init__ms  zDatabase.__init__cCs*||_|jrt|j|d|_dSg|_dSr)rrr)r!rrrrrrrys zDatabase.init_schemarcCsdd|jDS)NcSi|]}|j|qSrr-)r,rrrr rz"Database.tdict..)rrXrrrtdict~szDatabase.tdictrrcCs|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)rrrrrrs zDatabase.name_translatorcCs4|jrttt|jS|jstd|_|jS)Nz:memory:)r contextlibclosingsqlite3connectr&rrXrrr connections  zDatabase.connectionc sdur d}nd}dt||j|jdjt||j|jdjt||j|}||}fdd|DS)NzWHERE context = '{0}'rkzgSELECT {0}, group_concat({1}, ' '), group_concat(COALESCE(context, ''), '||') FROM {2} {3} GROUP BY {0}rrwc s<i|]}|dfddt|d|ddDqS)rcs$g|]\}}dur||fn|qSrr)r,r:vrrrr<sz;Database.select_many_to_many...rwz||)zipsplit)r,rrrrrs z0Database.select_many_to_many..)r+r1r5r-r|rErH)r!r4rr context_sqlrBrIrrrselect_many_to_manys   zDatabase.select_many_to_manyrrgcCsP|jD]"}|||kr%|j|jD]}|||j|kr$|jSqqdS)ze :return: separator for the column specified by db schema names `tname` and `cname`. N)rr5r|r-rW)r!rrgr-rrrrrWs zDatabase.separatorcCs"|||}|r|p d|S|S)Nrk)rWr)r!rrgvalueseprrr split_values zDatabase.split_valuec stt}|0}|jjD]!}iitt}}|j|}|jD]O}|j t g| ||j <|j t vrIt |j d| ||j d<nt|j j| ||j d<|jrt|j dkrk|j|| ||j <q%d|| ||j <q%|jD]\}}||||D]\} } | || | ||<qqzt|| |\} } | D]} t}t| | D]J\} |vr| durd|<q| sg|<q|dkrt| |<qfdd| pd|D|<q| durd| nd|<q|jrt|jdkr|| ||jd nd} |d d |jD||| i|| ||qqWd|S1s>wY|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. rrwrjsonNcsg|] }d|qS)rwr)r,v_rSr:rrr<sz!Database.read..rkrcSsi|]}|gqSrrr9rrrrz!Database.read..)r defaultdictrGrrrdictrr|r-r r5rPrQrrVrWrrrrKrrrloadsrrsrupdater`ra)r!resconnrsepsrefsrrrpkrrJr7rCrDrrrrTs`     "     (" 00z Database.readcCs||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!rrfkeyrrrassociation_table_contextsz"Database.association_table_contextFcCs|jd|||d|jS)Nforce _exists_ok _skip_extrar)writerrT)r!_forcerrrrr write_from_tgszDatabase.write_from_tgrc s|jr|jr|std|j|!}|jD] }||j|jdq|d| t t }|jD]}|j |vrBq:gg} } dd|jD} t||j D]\} } |jrkt|jdkrk| |jdnd}g}| D]\}}||jvr|sJ|j|}t|j gd d |jD}|pgD]}||||\}}|||||fqqs|| vr|rqstd || |t|t rjd krڈjpd fdd|D}nt|}n |dur|nd}| dkr| j ||qs| t|qVt||j|j | g| Rq:|D]\}} t||j|d|ddg| Rq| WddS1s=wYdS)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)r5zPRAGMA foreign_keys = ON;cSrrrrurrrrrz"Database.write..rwrNcSsg|]}|jqSrrrurrrr<%rz"Database.write..z$unspecified column {0} found in datar;c3s|] }|p dVqdS)rkN)rS)r,vvrrrr.6s z!Database.write..) rexistsrunlinkrrrErBr5commitrrrGr-r| enumeratersrrrtuplerrar+ isinstancerPrWr/rdumpsrSr@)r!rrrrr4rrrr7r6rJrrCrrr:rratkeyrrrrrrrsn                ( $zDatabase.write)NNTrr)FFF)$r#r$r%rnrr'r(Unionrrr&rrrrpropertyDictrqr staticmethodrr ConnectionrrrrrrWListrrrTrrrrrrrrZs6    8 rr)%rnrr'rrrrrrror]csvw.datatypesr csvw.metadatarr rQProtocolrr)r1rr&SequencerGr(rr@TuplerrKrrLobjectrqrrrrrrs|    *"Cq  '