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)