o mia@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|SNsrrA/home/kim/smarthome/.venv/lib/python3.10/site-packages/csvw/db.pyidentity,r TEXTZINTEGERcC|dur|St|Sr)intrrrr;rcCr r)boolrrrrr<rZREALcCr r)floatrrrrr?rcCs|dur|St|Sr)decimalDecimalrrrrr@ZBLOB)stringintegerbooleanrZ hexBinaryc@s*eZdZddedejedefddZdS)SchemaTranslatorNtablecolumnreturncCdSrr)selfrrrrr__call__Ir zSchemaTranslator.__call__r)__name__ __module__ __qualname__strtypingOptionalrrrrrrHs"rc@seZdZdedefddZdS)ColumnTranslatorrrcCrrr)rrrrrrNr zColumnTranslator.__call__N)rr r!r"rrrrrr%Msr%cGsddd|DS)N,css|]}d|VqdS)z`{0}`N)format).0namerrr Sszquoted..)join)namesrrrquotedRsr-Fsingledb translaterkeysrowsr/c 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(krr1rr jrzinsert..r&cSsg|]}dqS)?r)r(_rrrr8ksTr.N)r'r-r+Z executemanyinsertprint)r0r1rr2r/r3sqlrowrr7rr;Vs" r;rcCs4|dt|}dd|jD}|t|fS)NzSELECT * FROM {0}cSsg|]}|dqS)rr)r(drrrr8zrzselect..)executer'r- descriptionlistfetchall)r0rcucolsrrrselectxsrFc@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 converterNrHFcCsf|jtvrt|j\|_|_|_nd|_t|jj|_t|jj|_|jr/|jdkr1d|_dSdSdS)Nr ) csvw_typeTYPE_MAPdb_typeconvertreadrZ to_string to_python separatorrrrr__attrs_post_init__s  zColSpec.__attrs_post_init__r1rcCs2|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)rTrUz{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)minimummaximumgetrKappendr'anylengthZ minLengthZ maxLengthr+)rr1ccname constraintsfuncrrrchecks6   z ColSpec.checkcCs<||}d||j|j|jrdnd|rd|SdS)Nz`{0}` {1}{2}{3}z NOT NULLz CHECK ({0}))rbr'r)rMrequired)rr1_checkrrrr=s   z ColSpec.sql)rr r!__doc__attribr)rKrQrMrNrOrdrWrSr%r#r$r"rbr=rrrrrG~s       #rGc@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:: ``_ rJNTrdrop_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)rQheaderr(r^rrr sz0TableSpec.from_table_metadata..rz)Composite key {0} in table {1} referencedzSTable {0} referenced by list-valued foreign key must have non-composite primary keydatatyperQrd)r)rKrQrdrW)Z local_nameZ tableSchemaZ primaryKeycolumnsZ foreignKeys referenceZschemaReferencelenZcolumnReferencer'resourcerkr)riassociation_tabler many_to_many foreign_keysr[sortedrlinheritrGbase)clsrrjspecZ list_valuedfkr^rprrrfrom_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}Z_1Z_2context)r)rqrw)rGr'r))r{ZatableZapkZbtableZbpkZafkZbfkrrrru s    zTableSpec.association_tabler1c 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)r=)r(col col_translaterrr8&rz!TableSpec.sql..zPRIMARY KEY({0})cg|]}|qSrrrmrrrr8)rz6FOREIGN KEY({0}) REFERENCES {1}({2}) ON DELETE CASCADEcrrrrmrrrr8,rcr4rrrm)refr1rrr8.rz,CREATE TABLE IF NOT EXISTS `{0}` ( {1} )z, ) functoolspartialr)rqrkr[r'r-rwr+)rr1Zclausesr}Zrefcolsr)rrr1rr= s  z TableSpec.sqlT)rri)rr r!rfrgrhr)FactoryrBrqrw collections OrderedDictrvrk classmethodrWTabler#r$rr~rurr"r=rrrrris&   2 riTtgrjcsi}|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). rjrdroc3s(|]}|dvp|dkVqdS)roNr)r(rorderedrrrr*Ps&zschema..z7there seem to be cyclic dependencies between the tables) tabledictitemsrir~r)rvvaluesrrrBr2allrwpop ValueError)rrjtablestnametatirrrschema3s,      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. NTrfnamer1rjcCs8|ptj|_|r t|nd|_|j||dd|_dSNr)rname_translatorr1pathlibPathr init_schema _connection)rrrr1rjrrr__init__ms  zDatabase.__init__cCs*||_|jrt|j|d|_dSg|_dSr)rrr)rrrjrrrrys zDatabase.init_schemarcCsdd|jDS)NcSi|]}|j|qSrr))r(rrrr rz"Database.tdict..)rrRrrrtdict~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"rrRrrr connections  zDatabase.connectionc sdur d}nd}dt||j|jdjt||j|jdjt||j|}||}fdd|DS)NzWHERE context = '{0}'rczgSELECT {0}, group_concat({1}, ' '), group_concat(COALESCE(context, ''), '||') FROM {2} {3} GROUP BY {0}rroc s<i|]}|dfddt|d|ddDqS)rcs$g|]\}}dur||fn|qSrr)r(r6vrrrr8sz;Database.select_many_to_many...roz||)zipsplit)r(rrrrrs z0Database.select_many_to_many..)r'r-r1r)rqr@rC)rr0rrZ context_sqlr=rDrrrselect_many_to_manys   zDatabase.select_many_to_manyrr_cCsP|jD]"}|||kr%|j|jD]}|||j|kr$|jSqqdS)ze :return: separator for the column specified by db schema names `tname` and `cname`. N)rr1rqr)rQ)rrr_r)rrrrrQs zDatabase.separatorcCs"|||}|r|p d|S|S)Nrc)rQr)rrr_valueseprrr 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. rrorjsonNcsg|] }d|qS)ror)r(Zv_rNr6rrr8sz!Database.read..rcrcSsi|]}|gqSrrr5rrrrz!Database.read..)r defaultdictrBrrrdictrrqr)r r1rKrLrrPrQrvrrrFrrrloadsrrkrsupdaterZr[)rresconnrsepsrefsrrrpkrrEr3r>r?rrrrOs`     "     (" 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)rrrfkeyrrrassociation_table_contextsz"Database.association_table_contextFcCs|jd|||d|jS)Nforce _exists_ok _skip_extrar)writerrO)rZ_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)r1zPRAGMA foreign_keys = ON;cSrrrrmrrrrrz"Database.write..rorNcSsg|]}|jqSrrrmrrrr8%rz"Database.write..z$unspecified column {0} found in datar;c3s|] }|p dVqdS)rcN)rN)r(vvrrrr*6s z!Database.write..) rexistsrunlinkrrr@r=r1commitrrrBr)rq enumeraterkrsrrvtuplerr[r' isinstancerKrQr+rdumpsrNr;)rrrrrr0rrrr3r2rErr>rrr6rrZatkeyrrrrrrrsn                ( $zDatabase.write)NNTrr)FFF)$rr r!rfrr#r$Unionrrr"rrrrpropertyDictrir staticmethodrr ConnectionrrrrrrQListrrrOrrrrrrrrZs6    8 rr)%rfrr#rrrrrrrgrWZcsvw.datatypesrZ csvw.metadatarr rLProtocolrr%r-rr"SequencerBr$rr;TuplerrFrrGobjectrirrrrrrs|    *"Cq  '