def _nest_column(self, column, new_path): destination_table = join_field([self.name] + split_field(new_path)) existing_table = join_field([self.name] + split_field(column.nested_path[0])) # FIND THE INNER COLUMNS WE WILL BE MOVING new_columns = {} for cname, cols in self.columns.items(): if startswith_field(cname, column.names[self.name]): new_columns[cname] = set() for col in cols: new_columns[cname].add(col) col.nested_path = [new_path] + col.nested_path # TODO: IF THERE ARE CHILD TABLES, WE MUST UPDATE THEIR RELATIONS TOO? # DEFINE A NEW TABLE? # LOAD THE COLUMNS command = "PRAGMA table_info(" + quote_table(destination_table) + ")" details = self.db.query(command) if details.data: raise Log.error("not expected, new nesting!") from jx_sqlite.query_table import QueryTable self.nested_tables[new_path] = sub_table = QueryTable( destination_table, self.db, exists=False) self.db.execute("ALTER TABLE " + quote_table(sub_table.name) + " ADD COLUMN " + quoted_PARENT + " INTEGER") self.db.execute("ALTER TABLE " + quote_table(sub_table.name) + " ADD COLUMN " + quote_table(ORDER) + " INTEGER") for cname, cols in new_columns.items(): for c in cols: sub_table.add_column(c) # TEST IF THERE IS ANY DATA IN THE NEW NESTED ARRAY all_cols = [c for _, cols in sub_table.columns.items() for c in cols] if not all_cols: has_nested_data = "0" elif len(all_cols) == 1: has_nested_data = _quote_column(all_cols[0]) + " is NOT NULL" else: has_nested_data = "COALESCE(" + \ ",".join(_quote_column(c) for c in all_cols) + \ ") IS NOT NULL" # FILL TABLE WITH EXISTING COLUMN DATA command = "INSERT INTO " + quote_table(destination_table) + "(\n" + \ ",\n".join( [quoted_UID, quoted_PARENT, quote_table(ORDER)] + [_quote_column(c) for _, cols in sub_table.columns.items() for c in cols] ) + \ "\n)\n" + \ "\nSELECT\n" + ",".join( [quoted_UID, quoted_UID, "0"] + [_quote_column(c) for _, cols in sub_table.columns.items() for c in cols] ) + \ "\nFROM\n" + quote_table(existing_table) + \ "\nWHERE\n" + has_nested_data self.db.execute(command)
def __getitem__(self, item): cs = self.columns.get(item, None) if not cs: return [Null] command = " UNION ALL ".join("SELECT " + _quote_column(c) + " FROM " + quote_table(c.es_index) for c in cs) output = self.db.query(command) return [o[0] for o in output]
def __iter__(self): columns = [ c for c, cs in self.columns.items() for c in cs if c.type not in STRUCT ] command = "SELECT " + \ ",\n".join(_quote_column(c) for c in columns) + \ " FROM " + quote_table(self.name) rows = self.db.query(command) for r in rows: output = Data() for (k, t), v in zip(columns, r): output[k] = v yield output
def add_column(self, column): """ ADD COLUMN, IF IT DOES NOT EXIST ALREADY """ if column.name not in self.columns: self.columns[column.name] = {column} elif column.type not in [c.type for c in self.columns[column.name]]: self.columns[column.name].add(column) if column.type == "nested": nested_table_name = concat_field(self.name, column.name) # MAKE THE TABLE from jx_sqlite.query_table import QueryTable table = QueryTable(nested_table_name, self.db, exists=False) self.nested_tables[column.name] = table else: self.db.execute("ALTER TABLE " + quote_table(self.name) + " ADD COLUMN " + _quote_column(column) + " " + column.type)
def change_schema(self, required_changes): required_changes = wrap(required_changes) for required_change in required_changes: if required_change.add: column = required_change.add if column.type == "nested": # WE ARE ALSO NESTING self._nest_column(column, column.names[self.name]) table = join_field([self.name] + split_field(column.nested_path[0])) self.db.execute("ALTER TABLE " + quote_table(table) + " ADD COLUMN " + _quote_column(column) + " " + sql_types[column.type]) self.columns.add(column) elif required_change.nest: column = required_change.nest new_path = required_change.new_path self._nest_column(column, new_path)
def update(self, command): """ :param command: EXPECTING dict WITH {"set": s, "clear": c, "where": w} FORMAT """ command = wrap(command) # REJECT DEEP UPDATES touched_columns = command.set.keys() | set(listwrap(command['clear'])) for c in self.get_leaves(): if c.name in touched_columns and c.nested_path and len( c.name) > len(c.nested_path[0]): Log.error("Deep update not supported") # ADD NEW COLUMNS where = jx_expression(command.where) _vars = where.vars() _map = { v: c.es_column for v in _vars for c in self.columns.get(v, Null) if c.type not in STRUCT } where_sql = where.map(_map).to_sql() new_columns = set(command.set.keys()) - set(self.columns.keys()) for new_column_name in new_columns: nested_value = command.set[new_column_name] ctype = get_type(nested_value) column = Column(names={self.name: new_column_name}, type=ctype, es_index=self.name, es_column=typed_column(new_column_name, ctype)) self.add_column(column) # UPDATE THE NESTED VALUES for nested_column_name, nested_value in command.set.items(): if get_type(nested_value) == "nested": nested_table_name = concat_field(self.name, nested_column_name) nested_table = nested_tables[nested_column_name] self_primary_key = ",".join( quote_table(c.es_column) for u in self.uid for c in self.columns[u]) extra_key_name = UID_PREFIX + "id" + unicode(len(self.uid)) extra_key = [e for e in nested_table.columns[extra_key_name]][0] sql_command = "DELETE FROM " + quote_table(nested_table.name) + \ "\nWHERE EXISTS (" + \ "\nSELECT 1 " + \ "\nFROM " + quote_table(nested_table.name) + " n" + \ "\nJOIN (" + \ "\nSELECT " + self_primary_key + \ "\nFROM " + quote_table(self.name) + \ "\nWHERE " + where_sql + \ "\n) t ON " + \ " AND ".join( "t." + quote_table(c.es_column) + " = n." + quote_table(c.es_column) for u in self.uid for c in self.columns[u] ) + \ ")" self.db.execute(sql_command) # INSERT NEW RECORDS if not nested_value: continue doc_collection = {} for d in listwrap(nested_value): nested_table.flatten(d, Data(), doc_collection, path=nested_column_name) prefix = "INSERT INTO " + quote_table(nested_table.name) + \ "(" + \ self_primary_key + "," + \ _quote_column(extra_key) + "," + \ ",".join( quote_table(c.es_column) for c in doc_collection.get(".", Null).active_columns ) + ")" # BUILD THE PARENT TABLES parent = "\nSELECT " + \ self_primary_key + \ "\nFROM " + quote_table(self.name) + \ "\nWHERE " + jx_expression(command.where).to_sql() # BUILD THE RECORDS children = " UNION ALL ".join( "\nSELECT " + quote_value(i) + " " + quote_table(extra_key.es_column) + "," + ",".join( quote_value(row[c.name]) + " " + quote_table(c.es_column) for c in doc_collection.get(".", Null).active_columns) for i, row in enumerate( doc_collection.get(".", Null).rows)) sql_command = prefix + \ "\nSELECT " + \ ",".join( "p." + quote_table(c.es_column) for u in self.uid for c in self.columns[u] ) + "," + \ "c." + _quote_column(extra_key) + "," + \ ",".join( "c." + quote_table(c.es_column) for c in doc_collection.get(".", Null).active_columns ) + \ "\nFROM (" + parent + ") p " + \ "\nJOIN (" + children + \ "\n) c on 1=1" self.db.execute(sql_command) # THE CHILD COLUMNS COULD HAVE EXPANDED # ADD COLUMNS TO SELF for n, cs in nested_table.columns.items(): for c in cs: column = Column(names={self.name: c.name}, type=c.type, es_index=c.es_index, es_column=c.es_column, nested_path=[nested_column_name] + c.nested_path) if c.name not in self.columns: self.columns[column.name] = {column} elif c.type not in [ c.type for c in self.columns[c.name] ]: self.columns[column.name].add(column) command = "UPDATE " + quote_table(self.name) + " SET " + \ ",\n".join( [ _quote_column(c) + "=" + quote_value(get_if_type(v, c.type)) for k, v in command.set.items() if get_type(v) != "nested" for c in self.columns[k] if c.type != "nested" and len(c.nested_path) == 1 ] + [ _quote_column(c) + "=NULL" for k in listwrap(command['clear']) if k in self.columns for c in self.columns[k] if c.type != "nested" and len(c.nested_path) == 1 ] ) + \ " WHERE " + where_sql self.db.execute(command)
def __init__(self, name, db=None, uid=GUID, exists=False, kwargs=None): """ :param name: NAME FOR THIS TABLE :param db: THE DB TO USE :param uid: THE UNIQUE INDEX FOR THIS TABLE :return: HANDLE FOR TABLE IN db """ global _config Container.__init__(self, frum=None) if db: self.db = db else: self.db = db = Sqlite() if not _config: from pyLibrary.queries.containers import config as _config if not _config.default: _config.default = {"type": "sqlite", "settings": {"db": db}} self.name = name self.uid = listwrap(uid) self._next_uid = 1 self._make_digits_table() self.uid_accessor = jx.get(self.uid) self.nested_tables = OrderedDict( ) # MAP FROM NESTED PATH TO Table OBJECT, PARENTS PROCEED CHILDREN self.nested_tables["."] = self self.columns = Index( keys=[join_field(["names", self.name])] ) # MAP FROM DOCUMENT ABS PROPERTY NAME TO THE SET OF SQL COLUMNS IT REPRESENTS (ONE FOR EACH REALIZED DATATYPE) if not exists: for u in self.uid: if u == GUID: pass else: c = Column(names={name: u}, type="string", es_column=typed_column(u, "string"), es_index=name) self.add_column_to_schema(self.nested_tables, c) command = ("CREATE TABLE " + quote_table(name) + "(" + (",".join([quoted_UID + " INTEGER"] + [ _quote_column(c) + " " + sql_types[c.type] for u, cs in self.columns.items() for c in cs ])) + ", PRIMARY KEY (" + (", ".join([quoted_UID] + [ _quote_column(c) for u in self.uid for c in self.columns[u] ])) + "))") self.db.execute(command) else: # LOAD THE COLUMNS command = "PRAGMA table_info(" + quote_table(name) + ")" details = self.db.query(command) for r in details: cname = untyped_column(r[1]) ctype = r[2].lower() column = Column(names={name: cname}, type=ctype, nested_path=['.'], es_column=typed_column(cname, ctype), es_index=name) self.add_column_to_schema(self.columns, column)