def create_fact(self, uid=UID):
"""
MAKE NEW TABLE WITH GIVEN guid
:param uid: name, or list of names, for the GUID
:return: None
"""
self.add_table_to_schema(["."])
uid = listwrap(uid)
new_columns = []
for u in uid:
if u == UID:
pass
else:
c = Column(names={".": u},
type="string",
es_column=typed_column(u, "string"),
es_index=self.fact)
self.add_column_to_schema(c)
new_columns.append(c)
command = ("CREATE TABLE " + quote_column(self.fact) + sql_iso(
sql_list([quoted_GUID + " TEXT "] + [quoted_UID + " INTEGER"] + [
quote_column(c.es_column) + " " + sql_types[c.type]
for c in self.tables["."].schema.columns
] + [
"PRIMARY KEY " + sql_iso(
sql_list([quoted_GUID] + [quoted_UID] + [
quote_column(c.es_column)
for c in self.tables["."].schema.columns
]))
])))
self.db.execute(command)
def where(self, filter):
"""
WILL NOT PULL WHOLE OBJECT, JUST TOP-LEVEL PROPERTIES
:param filter: jx_expression filter
:return: list of objects that match
"""
select = []
column_names = []
for cname, cs in self.columns.items():
cs = [c for c in cs if c.type not in STRUCT and len(c.nested_path) == 1]
if len(cs) == 0:
continue
column_names.append(cname)
if len(cs) == 1:
select.append(quote_column(c.es_column) + " " + quote_column(c.name))
else:
select.append(
"coalesce(" +
sql_list(quote_column(c.es_column) for c in cs) +
") " + quote_column(c.name)
)
result = self.db.query(
SQL_SELECT + SQL("\n,").join(select) +
SQL_FROM + quote_column(self.sf.fact) +
SQL_WHERE + jx_expression(filter).to_sql()
)
return wrap([{c: v for c, v in zip(column_names, r)} for r in result.data])
def _add_column(self, column):
cname = column.name
if column.jx_type == "nested":
# WE ARE ALSO NESTING
self._nest_column(column, [cname] + column.nested_path)
table = concat_field(self.fact_name, column.nested_path[0])
try:
with self.namespace.db.transaction() as t:
t.execute("ALTER TABLE" + quote_column(table) + "ADD COLUMN" +
quote_column(column.es_column) + column.es_type)
self.namespace.columns.add(column)
except Exception as e:
if "duplicate column name" in e:
# THIS HAPPENS WHEN MULTIPLE THREADS ARE ASKING FOR MORE COLUMNS TO STORE DATA
# THIS SHOULD NOT BE A PROBLEM SINCE THE THREADS BOTH AGREE THE COLUMNS SHOULD EXIST
# BUT, IT WOULD BE NICE TO MAKE LARGER TRANSACTIONS SO THIS NEVER HAPPENS
# CONFIRM THE COLUMN EXISTS IN LOCAL DATA STRUCTURES
for c in self.namespace.columns:
if c.es_column == column.es_column:
break
else:
Log.error("Did not add column {{column}]",
column=column.es_column,
cause=e)
else:
Log.error("Did not add column {{column}]",
column=column.es_column,
cause=e)
def _edges_op(self, query, frum):
query = query.copy() # WE WILL BE MARKING UP THE QUERY
index_to_column = {} # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE)
outer_selects = [] # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE)
frum_path = split_field(frum)
base_table = join_field(frum_path[0:1])
path = join_field(frum_path[1:])
nest_to_alias = {
nested_path: quote_column("__" + unichr(ord('a') + i) + "__")
for i, (nested_path, sub_table) in enumerate(self.sf.tables.items())
}
schema = self.sf.tables[relative_field(frum, self.sf.fact)].schema
tables = []
for n, a in nest_to_alias.items():
if startswith_field(path, n):
tables.append({"nest": n, "alias": a})
tables = jx.sort(tables, {"value": {"length": "nest"}})
from_sql = quote_column(join_field([base_table] + split_field(tables[0].nest))) + tables[0].alias
for previous, t in zip(tables, tables[1::]):
from_sql += (
SQL_LEFT_JOIN + quote_column(concat_field(base_table, t.nest)) + t.alias +
SQL_ON + join_column(t.alias, quoted_PARENT) + " = " + join_column(previous.alias, quoted_UID)
)
main_filter = query.where.to_sql(schema, boolean=True)[0].sql.b
# SHIFT THE COLUMN DEFINITIONS BASED ON THE NESTED QUERY DEPTH
ons = []
join_types = []
wheres = []
null_ons = [EXISTS_COLUMN + SQL_IS_NULL]
groupby = []
null_groupby = []
orderby = []
domains = []
select_clause = [SQL_ONE + EXISTS_COLUMN] + [quote_column(c.es_column) for c in self.sf.tables['.'].columns]
for edge_index, query_edge in enumerate(query.edges):
edge_alias = quote_column("e" + text_type(edge_index))
if query_edge.value:
edge_values = [p for c in query_edge.value.to_sql(schema).sql for p in c.items()]
elif not query_edge.value and any(query_edge.domain.partitions.where):
case = SQL_CASE
for pp, p in enumerate(query_edge.domain.partitions):
w = p.where.to_sql(schema)[0].sql.b
t = quote_value(pp)
case += SQL_WHEN + w + SQL_THEN + t
case += SQL_ELSE + SQL_NULL + SQL_END # quote value with length of partitions
edge_values = [("n", case)]
elif query_edge.range:
edge_values = query_edge.range.min.to_sql(schema)[0].sql.items() + query_edge.range.max.to_sql(schema)[
0].sql.items()
def to_sql(self, schema, not_null=False, boolean=False):
cols = [
Data({cname: c}) for cname, cs in schema.map_to_sql(self.var).items()
for c in cs
]
if not cols:
# DOES NOT EXIST
return wrap([{
"name": ".",
"sql": {
"0": "NULL"
},
"nested_path": ROOT_PATH
}])
acc = {}
if boolean:
for col in cols:
cname, col = col.items()[0]
nested_path = col.nested_path[0]
if col.type == OBJECT:
value = "1"
elif col.type == "boolean":
value = quote_column(col.es_column).sql
else:
value = "(" + quote_column(col.es_column).sql + ") IS NOT NULL"
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb['b'] = value
else:
for col in cols:
cname, col = col.items()[0]
if col.type == OBJECT:
prefix = self.var + "."
for cn, cs in schema.items():
if cn.startswith(prefix):
for child_col in cs:
tempa = acc.setdefault(child_col.nested_path[0],
{})
tempb = tempa.setdefault(get_property_name(cname),
{})
tempb[json_type_to_sql_type[
col.type]] = quote_column(
child_col.es_column).sql
else:
nested_path = col.nested_path[0]
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(
col.es_column).sql
return wrap([{
"name": relative_field(cname, self.var),
"sql": types,
"nested_path": nested_path
} for nested_path, pairs in acc.items() for cname, types in pairs.items()])
def _make_digits_table(self):
existence = self.db.query("PRAGMA table_info(__digits__)")
if not existence.data:
with self.db.transaction() as t:
t.execute(
"CREATE TABLE" + quote_column(DIGITS_TABLE) + "(value INTEGER)"
)
t.execute(
"INSERT INTO"
+ quote_column(DIGITS_TABLE)
+ SQL_UNION_ALL.join(SQL_SELECT + quote_value(i) for i in range(10))
)
def test_bad_exists_properties(self):
test = {
"data": [{
"~e~": 1
}, {
"~e~": 1
}],
"query": {
"from": TEST_TABLE,
"select": [{
"name": "count",
"aggregate": "count"
}],
},
"expecting_list": {
"meta": {
"format": "value"
},
"data": {
"count": 2
}
}
}
subtest = wrap(test)
cont = self.utils.fill_container(subtest, typed=False)
db = Sqlite(filename="metadata.localhost.sqlite")
try:
with db.transaction() as t:
t.execute(
"insert into " + quote_column("meta.columns") +
"(name, es_type, jx_type, nested_path, es_column, es_index, last_updated) VALUES "
+ quote_set([
".", "object", "exists", '["."]', ".", cont.alias,
Date.now()
]))
except Exception as e:
pass
try:
with db.transaction() as t:
t.execute(
"insert into " + quote_column("meta.columns") +
"(name, es_type, jx_type, nested_path, es_column, es_index, last_updated) VALUES "
+ quote_set([
"~e~", "long", "exists", '["."]', "~e~", cont.alias,
Date.now()
]))
except Exception as e:
pass
self.utils.send_queries(subtest)
def _add_column(self, column):
cname = column.names["."]
if column.type == "nested":
# WE ARE ALSO NESTING
self._nest_column(column, [cname] + column.nested_path)
table = concat_field(self.fact, column.nested_path[0])
self.db.execute("ALTER TABLE " + quote_column(table) + " ADD COLUMN " +
quote_column(column.es_column) + " " +
sql_types[column.type])
self.add_column_to_schema(column)
def _add_column(self, column):
cname = column.name
if column.jx_type == "nested":
# WE ARE ALSO NESTING
self._nest_column(column, [cname] + column.nested_path)
table = concat_field(self.fact_name, column.nested_path[0])
with self.namespace.db.transaction() as t:
t.execute("ALTER TABLE" + quote_column(table) + "ADD COLUMN" +
quote_column(column.es_column) + " " + column.es_type)
self.namespace.columns.add(column)
def _drop_column(self, column):
# DROP COLUMN BY RENAMING IT, WITH __ PREFIX TO HIDE IT
cname = column.name
if column.jx_type == "nested":
# WE ARE ALSO NESTING
self._nest_column(column, [cname] + column.nested_path)
table = concat_field(self.fact_name, column.nested_path[0])
with self.namespace.db.transaction() as t:
t.execute("ALTER TABLE" + quote_column(table) + "RENAME COLUMN" +
quote_column(column.es_column) + " TO " +
quote_column("__" + column.es_column))
self.namespace.columns.remove(column)
def _insert(self, collection):
for nested_path, details in collection.items():
active_columns = wrap(list(details.active_columns))
rows = details.rows
num_rows = len(rows)
table_name = concat_field(self.name, nested_path)
if table_name == self.name:
# DO NOT REQUIRE PARENT OR ORDER COLUMNS
meta_columns = [GUID, UID]
else:
meta_columns = [UID, PARENT, ORDER]
all_columns = meta_columns + active_columns.es_column # ONLY THE PRIMITIVE VALUE COLUMNS
command = ConcatSQL([
SQL_INSERT,
quote_column(table_name),
sql_iso(sql_list(map(quote_column, all_columns))), SQL_VALUES,
sql_list(
sql_iso(
sql_list(quote_value(row.get(c)) for c in all_columns))
for row in unwrap(rows))
])
with self.db.transaction() as t:
t.execute(command)
def _db_create(self):
with self._db_transaction():
self.db.execute(
"CREATE TABLE "
+ db_table_name
+ sql_iso(
sql_list(
[
quote_column(c.name)
+ " "
+ json_type_to_sqlite_type[c.jx_type]
for c in METADATA_COLUMNS
]
+ [
"PRIMARY KEY"
+ sql_iso(
sql_list(map(quote_column, ["es_index", "es_column"]))
)
]
)
)
)
for c in METADATA_COLUMNS:
self._add(c)
self._db_insert_column(c)
def _insert(self, collection):
for nested_path, details in collection.items():
active_columns = wrap(list(details.active_columns))
rows = details.rows
table_name = concat_field(self.facts.snowflake.fact_name,
nested_path)
if table_name == self.facts.snowflake.fact_name:
# DO NOT REQUIRE PARENT OR ORDER COLUMNS
meta_columns = [GUID, UID]
else:
meta_columns = [UID, PARENT, ORDER]
all_columns = meta_columns + active_columns.es_column
prefix = ("INSERT INTO " + quote_column(table_name) +
sql_iso(sql_list(map(quote_column, all_columns))))
# BUILD THE RECORDS
records = SQL_UNION_ALL.join(
SQL_SELECT +
sql_list(quote_value(row.get(c)) for c in all_columns)
for row in unwrap(rows))
with self.db.transaction() as t:
t.execute(prefix + records)
def to_sql(self, schema, not_null=False, boolean=False):
if self.var == GUID:
return wrap([{"name": ".", "sql": {"s": quoted_GUID}, "nested_path": ROOT_PATH}])
vars = schema[self.var]
if not vars:
# DOES NOT EXIST
return wrap([{"name": ".", "sql": {"0": SQL_NULL}, "nested_path": ROOT_PATH}])
var_name = list(set(listwrap(vars).name))
if len(var_name) > 1:
Log.error("do not know how to handle")
var_name = var_name[0]
cols = schema.leaves(self.var)
acc = {}
if boolean:
for col in cols:
cname = relative_field(col.name, var_name)
nested_path = col.nested_path[0]
if col.type == OBJECT:
value = SQL_TRUE
elif col.type == BOOLEAN:
value = quote_column(col.es_column)
else:
value = quote_column(col.es_column) + SQL_IS_NOT_NULL
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb['b'] = value
else:
for col in cols:
cname = relative_field(col.name, var_name)
if col.type == OBJECT:
prefix = self.var + "."
for cn, cs in schema.items():
if cn.startswith(prefix):
for child_col in cs:
tempa = acc.setdefault(child_col.nested_path[0], {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(child_col.es_column)
else:
nested_path = col.nested_path[0]
tempa = acc.setdefault(nested_path, {})
tempb = tempa.setdefault(get_property_name(cname), {})
tempb[json_type_to_sql_type[col.type]] = quote_column(col.es_column)
return wrap([
{"name": cname, "sql": types, "nested_path": nested_path}
for nested_path, pairs in acc.items() for cname, types in pairs.items()
])
def remove_facts(self, fact_name):
paths = self.ns.columns._snowflakes[fact_name]
if paths:
with self.db.transaction() as t:
for p in paths:
full_name = concat_field(fact_name, p[0])
t.execute("DROP TABLE "+quote_column(full_name))
self.ns.columns.remove_table(fact_name)
def id_generator(db):
"""
INSTALL AN ID GENERATOR
"""
about = db.about(VERSION_TABLE)
if not about:
with db.transaction() as t:
t.execute(
sql_create(VERSION_TABLE, {
"version": "TEXT",
"next_id": "LONG"
}))
t.execute(
sql_insert(VERSION_TABLE, {
"version": "1.0",
"next_id": 1000
}))
else:
for cid, name, dtype, notnull, dfft_value, pk in about:
if name == "next_id":
break
else:
with db.transaction() as t:
t.execute("ALTER TABLE " + quote_column(VERSION_TABLE) +
" ADD COLUMN next_id LONG")
t.execute(SQL_UPDATE + quote_column(VERSION_TABLE) + SQL_SET +
sql_eq(next_id=1000))
def _gen_ids():
while True:
with db.transaction() as t:
top_id = first(
first(
t.query(
sql_query({
"select": "next_id",
"from": VERSION_TABLE
})).data))
max_id = top_id + 1000
t.execute(SQL_UPDATE + quote_column(VERSION_TABLE) + SQL_SET +
sql_eq(next_id=max_id))
while top_id < max_id:
yield top_id
top_id += 1
return _gen_ids().__next__
def monitor(self, please_stop):
while not please_stop:
# Delete expired session
try:
with self.db.transaction() as t:
t.execute("DELETE FROM " + quote_column(self.table) +
SQL_WHERE + sql_lt(expires=Date.now().unix))
except Exception as e:
Log.warning("problem with session expires", cause=e)
(please_stop | Till(seconds=60)).wait()
def _window_op(self, query, window):
# http://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSqlAnalyticalFunctions
if window.value == "rownum":
return (
"ROW_NUMBER()-1 OVER (" +
" PARTITION BY " + sql_iso(sql_list(window.edges.values)) +
SQL_ORDERBY + sql_iso(sql_list(window.edges.sort)) +
") AS " + quote_column(window.name)
)
range_min = text_type(coalesce(window.range.min, "UNBOUNDED"))
range_max = text_type(coalesce(window.range.max, "UNBOUNDED"))
return (
sql_aggs[window.aggregate] + sql_iso(window.value.to_sql()) + " OVER (" +
" PARTITION BY " + sql_iso(sql_list(window.edges.values)) +
SQL_ORDERBY + sql_iso(sql_list(window.edges.sort)) +
" ROWS BETWEEN " + range_min + " PRECEDING AND " + range_max + " FOLLOWING " +
") AS " + quote_column(window.name)
)
def __init__(self, db):
self.db = db
self._snowflakes = {} # MAP FROM BASE TABLE TO LIST OF NESTED TABLES
self._columns = ColumnList()
# FIND ALL TABLES
result = self.db.query(
"SELECT * FROM sqlite_master WHERE type='table' ORDER BY name")
tables = wrap([{k: d[i]
for i, k in enumerate(result.header)}
for d in result.data])
last_nested_path = []
for table in tables:
if table.name.startswith("__"):
continue
base_table, nested_path = tail_field(table.name)
# FIND COMMON NESTED PATH SUFFIX
for i, p in enumerate(last_nested_path):
if startswith_field(nested_path, p):
last_nested_path = last_nested_path[i:]
break
else:
last_nested_path = []
full_nested_path = [nested_path] + last_nested_path
nested_tables = self._snowflakes.setdefault(
base_table, [nested_path] + last_nested_path)
nested_tables.append(
jx_base.TableDesc(name=table.name,
nested_path=full_nested_path))
# LOAD THE COLUMNS
command = "PRAGMA table_info" + sql_iso(quote_column(table.name))
details = self.db.query(command)
for cid, name, dtype, notnull, dfft_value, pk in details.data:
if name.startswith("__"):
continue
cname, ctype = untyped_column(name)
self._columns.add(
Column(
name=cname, # I THINK COLUMNS HAVE THIER FULL PATH
jx_type=coalesce(
ctype, {
"TEXT": "string",
"REAL": "number",
"INTEGER": "integer"
}.get(dtype)),
nested_path=full_nested_path,
es_type=dtype,
es_column=name,
es_index=table.name))
last_nested_path = full_nested_path
def create_snowflake(self, fact_name, uid=UID):
"""
MAKE NEW TABLE WITH GIVEN guid
:param fact_name: NAME FOR THE CENTRAL FACTS
:param uid: name, or list of names, for the GUID
:return: Facts
"""
self.add_table_to_schema(["."])
uid = listwrap(uid)
new_columns = []
for u in uid:
if u == UID:
pass
else:
c = Column(
name=u,
jx_type=STRING,
es_column=typed_column(u, "string"),
es_index=fact_name
)
self.add_column_to_schema(c)
new_columns.append(c)
command = (
"CREATE TABLE " + quote_column(fact_name) + sql_iso(sql_list(
[quoted_GUID + " TEXT "] +
[quoted_UID + " INTEGER"] +
[quote_column(c.es_column) + " " + json_type_to_sqlite_type[c.jx_type] for c in self.tables["."].schema.columns] +
["PRIMARY KEY " + sql_iso(sql_list(
[quoted_GUID] +
[quoted_UID] +
[quote_column(c.es_column) for c in self.tables["."].schema.columns]
))]
))
)
self.db.execute(command)
snowflake = Snowflake(fact_name, self)
return Facts(self, snowflake)
def create_or_replace_facts(self, fact_name, uid=UID):
"""
MAKE NEW TABLE WITH GIVEN guid
:param fact_name: NAME FOR THE CENTRAL FACTS
:param uid: name, or list of names, for the GUID
:return: Facts
"""
self.remove_snowflake(fact_name)
self._snowflakes[fact_name] = ["."]
uid = listwrap(uid)
new_columns = []
for u in uid:
if u == UID:
pass
else:
c = Column(name=u,
jx_type=mo_json.STRING,
es_column=typed_column(
u, json_type_to_sql_type[mo_json.STRING]),
es_type=json_type_to_sqlite_type[mo_json.STRING],
es_index=fact_name,
last_updated=Date.now())
self.add_column_to_schema(c)
new_columns.append(c)
command = ("CREATE TABLE " + quote_column(fact_name) + sql_iso(
sql_list([quoted_GUID + " TEXT "] + [quoted_UID + " INTEGER"] + [
quote_column(c.es_column) + " " + c.es_type
for c in new_columns
] + [
"PRIMARY KEY " + sql_iso(
sql_list([quoted_GUID] + [quoted_UID] +
[quote_column(c.es_column) for c in new_columns]))
])))
with self.db.transaction() as t:
t.execute(command)
snowflake = Snowflake(fact_name, self)
return Facts(self, snowflake)
def _nest_column(self, column, new_path):
destination_table = concat_field(self.fact, new_path)
existing_table = concat_field(self.fact, column.nested_path[0])
# FIND THE INNER COLUMNS WE WILL BE MOVING
moving_columns = []
for c in self.columns:
if destination_table != column.es_index and column.es_column == c.es_column:
moving_columns.append(c)
c.nested_path = [new_path] + c.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 not details.data:
command = ("CREATE TABLE " + quote_table(destination_table) + "(" +
(",".join([
quoted_UID + " INTEGER", quoted_PARENT + " INTEGER",
quoted_ORDER + " INTEGER"
])) + ", PRIMARY KEY (" + quoted_UID + ")" +
", FOREIGN KEY (" + quoted_PARENT + ") REFERENCES " +
quote_table(existing_table) + "(" + quoted_UID + ")"
")")
self.db.execute(command)
self.add_table_to_schema([new_path])
# TEST IF THERE IS ANY DATA IN THE NEW NESTED ARRAY
if not moving_columns:
return
column.es_index = destination_table
self.db.execute("ALTER TABLE " + quote_table(destination_table) +
" ADD COLUMN " + quote_column(column.es_column) + " " +
sql_types[column.type])
# Deleting parent columns
for col in moving_columns:
column = col.es_column
tmp_table = "tmp_" + existing_table
columns = self.db.query("select * from " +
quote_table(existing_table) +
" LIMIT 0").header
self.db.execute("ALTER TABLE " + quote_table(existing_table) +
" RENAME TO " + quote_table(tmp_table))
self.db.execute(
"CREATE TABLE " + quote_table(existing_table) + " AS SELECT " +
(", ".join([quote_table(c) for c in columns if c != column])) +
" FROM " + quote_table(tmp_table))
self.db.execute("DROP TABLE " + quote_table(tmp_table))
def save_session(self, app, session, response):
if not session or not session.keys():
return
if not session.session_id:
session.session_id = generate_sid()
session.permanent = True
DEBUG and Log.note("save session {{session}}", session=session)
now = Date.now().unix
session_id = session.session_id
result = self.db.query(
sql_query({
"from": self.table,
"where": {
"eq": {
"session_id": session_id
}
}
}))
saved_record = first(Data(zip(result.header, r)) for r in result.data)
expires = min(session.expires,
now + self.cookie.inactive_lifetime.seconds)
if saved_record:
DEBUG and Log.note("found session {{session}}",
session=saved_record)
saved_record.data = value2json(session)
saved_record.expires = expires
saved_record.last_used = now
with self.db.transaction() as t:
t.execute("UPDATE " + quote_column(self.table) + SQL_SET +
sql_list(
sql_eq(**{k: v})
for k, v in saved_record.items()) + SQL_WHERE +
sql_eq(session_id=session_id))
else:
new_record = {
"session_id": session_id,
"data": value2json(session),
"expires": expires,
"last_used": now,
}
DEBUG and Log.note("new record for db {{session}}",
session=new_record)
with self.db.transaction() as t:
t.execute(sql_insert(self.table, new_record))
response.set_cookie(app.session_cookie_name,
session_id,
expires=expires)
def _gen_ids(self):
while True:
with self.db.transaction() as t:
top_id = first(
first(
t.query(
SQL_SELECT
+ quote_column("next_id")
+ SQL_FROM
+ quote_column(ABOUT_TABLE)
).data
)
)
max_id = top_id + 1000
t.execute(
SQL_UPDATE
+ quote_column(ABOUT_TABLE)
+ SQL_SET
+ sql_eq(next_id=max_id)
)
while top_id < max_id:
yield top_id
top_id += 1
def read_db(self):
"""
PULL SCHEMA FROM DATABASE, BUILD THE MODEL
:return: None
"""
# FIND ALL TABLES
result = self.db.query(
"SELECT * FROM sqlite_master WHERE type='table' ORDER BY name")
tables = wrap([{k: d[i]
for i, k in enumerate(result.header)}
for d in result.data])
tables_found = False
for table in tables:
if table.name.startswith("__"):
continue
tables_found = True
nested_path = [
join_field(split_field(tab.name)[1:])
for tab in jx.reverse(tables)
if startswith_field(table.name, tab.name)
]
self.add_table_to_schema(nested_path)
# LOAD THE COLUMNS
command = "PRAGMA table_info" + sql_iso(quote_column(table.name))
details = self.db.query(command)
for cid, name, dtype, notnull, dfft_value, pk in details.data:
if name.startswith("__"):
continue
cname, ctype = untyped_column(name)
column = Column(names={
np: relative_field(cname, np)
for np in nested_path
},
type=coalesce(
ctype, {
"TEXT": "string",
"REAL": "number",
"INTEGER": "integer"
}.get(dtype)),
nested_path=nested_path,
es_column=name,
es_index=table.name)
self.add_column_to_schema(column)
return tables_found
def where(self, filter):
"""
WILL NOT PULL WHOLE OBJECT, JUST TOP-LEVEL PROPERTIES
:param filter: jx_expression filter
:return: list of objects that match
"""
select = []
column_names = []
for c in self.schema.columns:
if c.jx_type in STRUCT:
continue
if len(c.nested_path) != 1:
continue
column_names.append(c.name)
select.append(sql_alias(quote_column(c.es_column), c.name))
where_sql = SQLang[jx_expression(filter)].to_sql(self.schema)[0].sql.b
result = self.db.query(ConcatSQL((
SQL_SELECT, JoinSQL(SQL_COMMA, select),
SQL_FROM, quote_column(self.snowflake.fact_name),
SQL_WHERE, where_sql
)))
return wrap([{c: v for c, v in zip(column_names, r)} for r in result.data])
def _gen_ids():
while True:
with db.transaction() as t:
top_id = first(
first(
t.query(
sql_query({
"select": "next_id",
"from": VERSION_TABLE
})).data))
max_id = top_id + 1000
t.execute(SQL_UPDATE + quote_column(VERSION_TABLE) + SQL_SET +
sql_eq(next_id=max_id))
while top_id < max_id:
yield top_id
top_id += 1
def _load_from_database(self):
# FIND ALL TABLES
result = self.db.query(
"SELECT * FROM sqlite_master WHERE type='table' ORDER BY name")
tables = wrap([{k: d
for k, d in zip(result.header, row)}
for row in result.data])
last_nested_path = []
for table in tables:
if table.name.startswith("__"):
continue
base_table, nested_path = tail_field(table.name)
# FIND COMMON NESTED PATH SUFFIX
for i, p in enumerate(last_nested_path):
if startswith_field(nested_path, p):
last_nested_path = last_nested_path[i:]
break
else:
last_nested_path = []
full_nested_path = [nested_path] + last_nested_path
self._snowflakes[literal_field(base_table)] += [full_nested_path]
# LOAD THE COLUMNS
command = "PRAGMA table_info" + sql_iso(quote_column(table.name))
details = self.db.query(command)
for cid, name, dtype, notnull, dfft_value, pk in details.data:
if name.startswith("__"):
continue
cname, ctype = untyped_column(name)
self.add(
Column(name=cname,
jx_type=coalesce(sql_type_to_json_type.get(ctype),
IS_NULL),
nested_path=full_nested_path,
es_type=dtype,
es_column=name,
es_index=table.name,
last_updated=Date.now()))
last_nested_path = full_nested_path
def update_session(self, session_id, props):
"""
UPDATE GIVEN SESSION WITH PROPERTIES
:param session_id:
:param props:
:return:
"""
now = Date.now().unix
session = self.get_session(session_id)
for k, v in props.items():
session[k] = v
session.last_used = now
record = {
"session_id": session_id,
"data": value2json(session),
"expires": session.expires,
"last_used": session.last_used,
}
with self.db.transaction() as t:
t.execute(SQL_UPDATE + quote_column(self.table) + SQL_SET +
sql_list(sql_eq(**{k: v}) for k, v in record.items()) +
SQL_WHERE + sql_eq(session_id=session_id))
"nested": "TEXT"
}
STATS = {
"count": "COUNT({{value}})",
"std": "SQRT((1-1.0/COUNT({{value}}))*VARIANCE({{value}}))",
"min": "MIN({{value}})",
"max": "MAX({{value}})",
"sum": "SUM({{value}})",
"median": "MEDIAN({{value}})",
"sos": "SUM({{value}}*{{value}})",
"var": "(1-1.0/COUNT({{value}}))*VARIANCE({{value}})",
"avg": "AVG({{value}})"
}
quoted_GUID = quote_column(GUID)
quoted_UID = quote_column(UID)
quoted_ORDER = quote_column(ORDER)
quoted_PARENT = quote_column(PARENT)
def sql_text_array_to_set(column):
def _convert(row):
text = row[column]
if text == None:
return set()
else:
value = json2value(row[column])
return set(value) - {None}
return _convert
from jx_python import jx
from mo_collections import UniqueIndex
from mo_dots import Data, FlatList, Null, NullType, ROOT_PATH, concat_field, is_container, is_data, is_list, join_field, listwrap, split_field, unwraplist, wrap
from mo_files import File
from mo_future import items, none_type, reduce, text_type, binary_type
from mo_json import (INTEGER, NUMBER, STRING, STRUCT, json2value, python_type_to_json_type, value2json)
from mo_json.typed_encoder import unnest_path, untype_path
from mo_logs import Except, Log
from mo_threads import Lock, Queue, Thread, Till
from mo_times.dates import Date
from pyLibrary.sql import (SQL_AND, SQL_FROM, SQL_ORDERBY, SQL_SELECT, SQL_WHERE, sql_iso, sql_list)
from pyLibrary.sql.sqlite import json_type_to_sqlite_type, quote_column, quote_value
DEBUG = False
singlton = None
db_table_name = quote_column("meta.columns")
INSERT, UPDATE, DELETE, EXECUTE = "insert", "update", "delete", "execute"
class ColumnList(Table, jx_base.Container):
"""
OPTIMIZED FOR THE PARTICULAR ACCESS PATTERNS USED
"""
def __init__(self, name):
Table.__init__(self, "meta.columns")
self.db_file = File("metadata." + name + ".sqlite")
self.data = {} # MAP FROM ES_INDEX TO (abs_column_name to COLUMNS)
self.locker = Lock()
self._schema = None