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 _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 sql_create(table, properties, primary_key=None, unique=None):
"""
:param table: NAME OF THE TABLE TO CREATE
:param properties: DICT WITH {name: type} PAIRS (type can be plain text)
:param primary_key: COLUMNS THAT MAKE UP THE PRIMARY KEY
:param unique: COLUMNS THAT SHOULD BE UNIQUE
:return:
"""
acc = [
SQL_CREATE,
quote_column(table),
SQL_OP,
sql_list([quote_column(k) + SQL(v) for k, v in properties.items()]),
]
if primary_key:
acc.append(SQL_COMMA),
acc.append(SQL(" PRIMARY KEY ")),
acc.append(sql_iso(sql_list([quote_column(c) for c in listwrap(primary_key)])))
if unique:
acc.append(SQL_COMMA),
acc.append(SQL(" UNIQUE ")),
acc.append(sql_iso(sql_list([quote_column(c) for c in listwrap(unique)])))
acc.append(SQL_CP)
return ConcatSQL(acc)
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 _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_new(self, table_name, candidate_key, new_record):
candidate_key = listwrap(candidate_key)
condition = SQL_AND.join([
quote_column(k) + "=" + quote_value(new_record[k])
if new_record[k] != None
else quote_column(k) + SQL_IS_NULL
for k in candidate_key
])
command = (
"INSERT INTO " + quote_column(table_name) + sql_iso(sql_list(
quote_column(k) for k in new_record.keys()
)) +
SQL_SELECT + "a.*" + SQL_FROM + sql_iso(
SQL_SELECT + sql_list([quote_value(v) + " " + quote_column(k) for k, v in new_record.items()]) +
SQL_FROM + "DUAL"
) + " a" +
SQL_LEFT_JOIN + sql_iso(
SQL_SELECT + "'dummy' exist " +
SQL_FROM + quote_column(table_name) +
SQL_WHERE + condition +
SQL_LIMIT + SQL_ONE
) + " b ON " + SQL_TRUE + SQL_WHERE + " exist " + SQL_IS_NULL
)
self.execute(command, {})
def _build_list_sql(self, db, first, batch_size):
# TODO: ENSURE THE LAST COLUMN IS THE id
if first:
dim = len(self._extract.field)
where = SQL_OR.join(
sql_iso(
sql_and(
quote_column(f) + ineq(i, e, dim) +
db.quote_value(Date(v) if t == "time" else v)
for e, (f, v, t) in enumerate(
zip(self._extract.field[0:i + 1:], first,
self._extract.type[0:i + 1:]))))
for i in range(dim))
else:
where = SQL_TRUE
selects = []
for t, f in zip(self._extract.type, self._extract.field):
if t == "time":
selects.append(
"CAST" +
sql_iso(sql_alias(quote_column(f), SQL("DATETIME(6)"))))
else:
selects.append(quote_column(f))
sql = (SQL_SELECT + sql_list(selects) + SQL_FROM +
self.settings.snowflake.fact_table + SQL_WHERE + where +
SQL_ORDERBY +
sql_list(quote_column(f) for f in self._extract.field) +
SQL_LIMIT + db.quote_value(batch_size))
return sql
def test_transactions2(service):
inserting = [('testing_transaction2_1', '1'),
('testing_transaction2_2', '2')]
with service.conn.transaction() as t:
# Make a change
t.execute(
"INSERT OR REPLACE INTO latestFileMod (file, revision) VALUES " +
sql_list(
sql_iso(sql_list(map(quote_value, i))) for i in inserting))
try:
# Query for one change
query_res1 = service.conn.get(
"SELECT revision FROM latestFileMod WHERE file=?",
('testing_transaction2_1', ))
assert False
except Exception as e:
assert DOUBLE_TRANSACTION_ERROR in e
# Query for the other change
query_res2 = t.get("SELECT revision FROM latestFileMod WHERE file=?",
('testing_transaction2_2', ))
assert query_res2[0][0] == '2'
def insert_list(self, table_name, records):
if not records:
return
columns = set()
for r in records:
columns |= set(r.keys())
columns = jx.sort(columns)
try:
self.execute(
"DELETE FROM " + self.quote_column(table_name) + SQL_WHERE +
"_id IN {{ids}}",
{"ids": self.quote_column([r["_id"] for r in records])})
command = (SQL_INSERT + self.quote_column(table_name) +
sql_iso(sql_list(self.quote_column(k)
for k in columns)) + SQL_VALUES +
sql_iso(
sql_list(
self.quote_value(r.get(k, None))
for k in columns for r in records)))
self.execute(command)
except Exception as e:
Log.error("problem with insert", e)
def _nest_column(self, column, new_path):
destination_table = concat_field(self.fact_name, new_path[0])
existing_table = concat_field(self.fact_name, 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
# TODO: IF THERE ARE CHILD TABLES, WE MUST UPDATE THEIR RELATIONS TOO?
# DEFINE A NEW TABLE?
# LOAD THE COLUMNS
details = self.namespace.db.about(destination_table)
if not details.data:
command = (SQL_CREATE + quote_column(destination_table) + sql_iso(
sql_list([
quoted_UID + "INTEGER", quoted_PARENT + "INTEGER",
quoted_ORDER + "INTEGER",
"PRIMARY KEY " + sql_iso(quoted_UID),
"FOREIGN KEY " + sql_iso(quoted_PARENT) + " REFERENCES " +
quote_column(existing_table) + sql_iso(quoted_UID)
])))
with self.namespace.db.transaction() as t:
t.execute(command)
self.add_table(new_path)
# TEST IF THERE IS ANY DATA IN THE NEW NESTED ARRAY
if not moving_columns:
return
column.es_index = destination_table
with self.namespace.db.transaction() as t:
t.execute("ALTER TABLE " + quote_column(destination_table) +
" ADD COLUMN " + quote_column(column.es_column) + " " +
column.es_type)
# Deleting parent columns
for col in moving_columns:
column = col.es_column
tmp_table = "tmp_" + existing_table
columns = list(
map(
text,
t.query(SQL_SELECT + SQL_STAR + SQL_FROM +
quote_column(existing_table) + SQL_LIMIT +
SQL_ZERO).header))
t.execute("ALTER TABLE " + quote_column(existing_table) +
" RENAME TO " + quote_column(tmp_table))
t.execute(
SQL_CREATE + quote_column(existing_table) + SQL_AS +
SQL_SELECT +
sql_list([quote_column(c)
for c in columns if c != column]) + SQL_FROM +
quote_column(tmp_table))
t.execute("DROP TABLE " + quote_column(tmp_table))
def to_sql(self, schema, not_null=False, boolean=False):
value = self.value.to_sql(schema, not_null=True)[0].sql.s
start = self.start.to_sql(schema, not_null=True)[0].sql.n
if self.length is NULL:
sql = "SUBSTR" + sql_iso(sql_list([value, start]))
else:
length = self.length.to_sql(schema, not_null=True)[0].sql.n
sql = "SUBSTR" + sql_iso(sql_list([value, start, length]))
return wrap([{"name": ".", "sql": sql}])
def sql_insert(table, records):
records = listwrap(records)
keys = list({k for r in records for k in r.keys()})
return ConcatSQL([
SQL_INSERT,
quote_column(table),
sql_iso(sql_list(map(quote_column, keys))),
SQL_VALUES,
sql_list(
sql_iso(sql_list([quote_value(r[k]) for k in keys]))
for r in records),
])
def insert(self, table_name, record):
keys = list(record.keys())
try:
command = (
"INSERT INTO " + quote_column(table_name) +
sql_iso(sql_list([quote_column(k) for k in keys])) +
" VALUES " +
sql_iso(sql_list([quote_value(record[k]) for k in keys]))
)
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}", record=record, cause=e)
def insert(self, table_name, record):
keys = list(record.keys())
try:
command = (SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list([quote_column(k)
for k in keys])) + SQL_VALUES +
sql_iso(sql_list([quote_value(record[k])
for k in keys])))
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}",
record=record,
cause=e)
def _db_insert_column(self, column):
try:
self.db.execute(
"INSERT INTO"
+ db_table_name
+ sql_iso(all_columns)
+ "VALUES"
+ sql_iso(
sql_list(
[
quote_value(column[c.name])
if c.name not in ("nested_path", "partitions")
else quote_value(value2json(column[c.name]))
for c in METADATA_COLUMNS
]
)
)
)
except Exception as e:
e = Except.wrap(e)
if "UNIQUE constraint failed" in e or " are not unique" in e:
# THIS CAN HAPPEN BECAUSE todo HAS OLD COLUMN DATA
self.todo.add((UPDATE, column), force=True)
else:
Log.error("do not know how to handle", cause=e)
def _sort2sql(self, sort):
"""
RETURN ORDER BY CLAUSE
"""
if not sort:
return ""
return SQL_ORDERBY + sql_list([quote_column(o.field) + (" DESC" if o.sort == -1 else "") for o in sort])
def insert_new(self, table_name, candidate_key, new_record):
candidate_key = listwrap(candidate_key)
condition = sql_eq(**{k: new_record[k] for k in candidate_key})
command = (
SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list(quote_column(k) for k in new_record.keys())) +
SQL_SELECT + "a.*" + SQL_FROM + sql_iso(SQL_SELECT + sql_list([
quote_value(v) + " " + quote_column(k)
for k, v in new_record.items()
]) + SQL_FROM + "DUAL") + " a" + SQL_LEFT_JOIN +
sql_iso(SQL_SELECT + "'dummy' exist " + SQL_FROM +
quote_column(table_name) + SQL_WHERE + condition +
SQL_LIMIT + SQL_ONE) + " b ON " + SQL_TRUE + SQL_WHERE +
" exist " + SQL_IS_NULL)
self.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 to_sql(self, schema, not_null=False, boolean=False):
value = self.value.to_sql(schema)[0].sql.s
find = self.find.to_sql(schema)[0].sql.s
return wrap([{"name": ".", "sql": {
"n": "INSTR" + sql_iso(sql_list([value, find]))
}}])
def _db_load(self):
self.last_load = Date.now()
result = self._query(
SQL_SELECT
+ "name"
+ SQL_FROM
+ "sqlite_master"
+ SQL_WHERE
+ SQL_AND.join(["name=" + db_table_name, "type=" + quote_value("table")])
)
if not result.data:
self._db_create()
return
result = self._query(
SQL_SELECT
+ all_columns
+ SQL_FROM
+ db_table_name
+ SQL_ORDERBY
+ sql_list(map(quote_column, ["es_index", "name", "es_column"]))
)
with self.locker:
for r in result.data:
c = row_to_column(result.header, r)
self._add(c)
def _sort2sql(self, sort):
"""
RETURN ORDER BY CLAUSE
"""
if not sort:
return ""
return SQL_ORDERBY + sql_list([self.db.quote_column(o.field) + (" DESC" if o.sort == -1 else "") for o in sort])
def add_cset_entries(self,
ordered_rev_list,
timestamp=False,
number_forward=True):
'''
Adds a list of revisions to the table. Assumes ordered_rev_list is an ordered
based on how changesets are found in the changelog. Going forwards or backwards is dealt
with by flipping the list
:param ordered_cset_list: Order given from changeset log searching.
:param timestamp: If false, records are kept indefinitely
but if holes exist: (delete, None, delete, None)
those delete's with None's around them
will not be deleted.
:param numbered: If True, this function will number the revision list
by going forward from max(revNum), else it'll go backwards
from revNum, then add X to all revnums and self.next_revnum
where X is the length of ordered_rev_list
:return:
'''
with self.conn.transaction() as t:
current_min = t.get_one("SELECT min(revnum) FROM csetlog")[0]
current_max = t.get_one("SELECT max(revnum) FROM csetlog")[0]
if not current_min or not current_max:
current_min = 0
current_max = 0
direction = -1
start = current_min - 1
if number_forward:
direction = 1
start = current_max + 1
ordered_rev_list = ordered_rev_list[::-1]
insert_list = [(start + direction * count, rev,
int(time.time()) if timestamp else -1)
for count, rev in enumerate(ordered_rev_list)]
# In case of overlapping requests
fmt_insert_list = []
for cset_entry in insert_list:
tmp = self._get_one_revision(t, cset_entry)
if not tmp:
fmt_insert_list.append(cset_entry)
for _, tmp_insert_list in jx.groupby(fmt_insert_list,
size=SQL_CSET_BATCH_SIZE):
t.execute(
"INSERT INTO csetLog (revnum, revision, timestamp)" +
" VALUES " + sql_list(
quote_set((revnum, revision, timestamp))
for revnum, revision, timestamp in tmp_insert_list))
# Move the revision numbers forward if needed
self.recompute_table_revnums()
# Start a maintenance run if needed
if self.check_for_maintenance():
Log.note("Scheduling maintenance run on clogger.")
self.maintenance_signal.go()
def to_sql(self, schema, not_null=False, boolean=False):
terms = [t.partial_eval().to_sql(schema)[0].sql.n for t in self.terms]
return wrap([{
"name": ".",
"sql": {
"n": "max" + sql_iso((sql_list(terms)))
}
}])
def insert_into_db_chunked(transaction, data, cmd, sql_chunk_size=500):
# For the `cmd` object, we expect something like (don't forget the whitespace at the end):
# "INSERT INTO temporal (tuid, file, revision, line) VALUES "
#
# `data` must be a list of tuples.
for _, inserts_list in jx.groupby(data, size=sql_chunk_size):
transaction.execute(cmd + sql_list(
quote_set(entry) for entry in inserts_list))
def quote_column(self, column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif isinstance(column_name, text_type):
if table:
column_name = join_column(table, column_name)
return SQL("`" + column_name.replace(".", "`.`") +
"`") # MY SQL QUOTE OF COLUMN NAMES
elif isinstance(column_name, list):
if table:
return sql_list(join_column(table, c) for c in column_name)
return sql_list(self.quote_column(c) for c in column_name)
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(
sql_alias(column_name.value,
self.quote_column(column_name.name)))
def quote_column(column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif is_text(column_name):
if table:
return join_column(table, column_name)
else:
return SQL("`" + '`.`'.join(split_field(column_name)) + "`") # MYSQL QUOTE OF COLUMN NAMES
elif is_binary(column_name):
return quote_column(column_name.decode('utf8'), table)
elif is_list(column_name):
if table:
return sql_list(join_column(table, c) for c in column_name)
return sql_list(quote_column(c) for c in column_name)
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(sql_alias(column_name.value, quote_column(column_name.name)))
def quote_column(column_name, table=None):
if column_name == None:
Log.error("missing column_name")
elif is_text(column_name):
if table:
return join_column(table, column_name)
else:
return SQL("`" + '`.`'.join(split_field(column_name)) +
"`") # MYSQL QUOTE OF COLUMN NAMES
elif is_binary(column_name):
return quote_column(column_name.decode('utf8'), table)
elif is_list(column_name):
if table:
return sql_list(join_column(table, c) for c in column_name)
return sql_list(quote_column(c) for c in column_name)
else:
# ASSUME {"name":name, "value":value} FORM
return SQL(sql_alias(column_name.value,
quote_column(column_name.name)))
def insert_into_db_chunked(transaction, data, cmd, sql_chunk_size=500):
# For the `cmd` object, we expect something like (don't forget the whitespace at the end):
# "INSERT INTO temporal (tuid, file, revision, line) VALUES "
#
# `data` must be a list of tuples.
for _, inserts_list in jx.groupby(data, size=sql_chunk_size):
transaction.execute(
cmd +
sql_list(quote_set(entry) for entry in inserts_list)
)
def insert_new(self, table_name, candidate_key, new_record):
candidate_key = listwrap(candidate_key)
condition = SQL_AND.join([
self.quote_column(k) + "=" + self.quote_value(new_record[k])
if new_record[k] != None else self.quote_column(k) + SQL_IS_NULL
for k in candidate_key
])
command = ("INSERT INTO " + self.quote_column(table_name) + sql_iso(
sql_list(self.quote_column(k)
for k in new_record.keys())) + SQL_SELECT + "a.*" +
SQL_FROM + sql_iso(SQL_SELECT + sql_list([
self.quote_value(v) + " " + self.quote_column(k)
for k, v in new_record.items()
]) + SQL_FROM + "DUAL") + " a" + SQL_LEFT_JOIN +
sql_iso(SQL_SELECT + "'dummy' exist " + SQL_FROM +
self.quote_column(table_name) + SQL_WHERE +
condition + SQL_LIMIT + SQL_ONE) + " b ON " +
SQL_TRUE + SQL_WHERE + " exist " + SQL_IS_NULL)
self.execute(command, {})
def insert_list(self, table_name, records):
if not records:
return
keys = set()
for r in records:
keys |= set(r.keys())
keys = jx.sort(keys)
try:
command = (SQL_INSERT + quote_column(table_name) +
sql_iso(sql_list([quote_column(k) for k in keys])) +
SQL_VALUES + sql_list(
sql_iso(sql_list([quote_value(r[k]) for k in keys]))
for r in records))
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}",
record=records,
cause=e)
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 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 insert_list(self, table_name, records):
if not records:
return
keys = set()
for r in records:
keys |= set(r.keys())
keys = jx.sort(keys)
try:
command = (
"INSERT INTO " + quote_column(table_name) +
sql_iso(sql_list([quote_column(k) for k in keys])) +
" VALUES " + sql_list([
sql_iso(sql_list([quote_value(r[k]) for k in keys]))
for r in records
])
)
self.execute(command)
except Exception as e:
Log.error("problem with record: {{record}}", record=records, cause=e)
def test_transactions(service):
# This should pass
old = service.get_tuids("/testing/geckodriver/CONTRIBUTING.md",
"6162f89a4838",
commit=False)
new = service.get_tuids("/testing/geckodriver/CONTRIBUTING.md",
"06b1a22c5e62",
commit=False)
assert len(old) == len(new)
# listed_inserts = [None] * 100
listed_inserts = [('test' + str(count), str(count))
for count, entry in enumerate(range(100))]
listed_inserts.append(
'hello world') # This should cause a transaction failure
try:
with service.conn.transaction() as t:
count = 0
while count < len(listed_inserts):
tmp_inserts = listed_inserts[count:count + 50]
count += 50
t.execute(
"INSERT OR REPLACE INTO latestFileMod (file, revision) VALUES "
+ sql_list(
sql_iso(sql_list(map(quote_value, i)))
for i in tmp_inserts))
assert False # SHOULD NOT GET HERE
except Exception as e:
e = Except.wrap(e)
assert "11 values for 2 columns" in e
# Check that the transaction was undone
latestTestMods = service.conn.get_one(
"SELECT revision FROM latestFileMod WHERE file=?", ('test1', ))
assert not latestTestMods
def update(self, table_name, where_slice, new_values):
"""
where_slice - A Data WHICH WILL BE USED TO MATCH ALL IN table
eg {"id": 42}
new_values - A dict WITH COLUMN NAME, COLUMN VALUE PAIRS TO SET
"""
new_values = quote_param(new_values)
where_clause = sql_eq(**where_slice)
command = (SQL_UPDATE + quote_column(table_name) + SQL_SET + sql_list(
[quote_column(k) + "=" + v
for k, v in new_values.items()]) + SQL_WHERE + where_clause)
self.execute(command, {})
def update(self, table_name, where_slice, new_values):
"""
where_slice - A Data WHICH WILL BE USED TO MATCH ALL IN table
eg {"id": 42}
new_values - A dict WITH COLUMN NAME, COLUMN VALUE PAIRS TO SET
"""
new_values = quote_param(new_values)
where_clause = SQL_AND.join([
quote_column(k) + "=" + quote_value(v) if v != None else quote_column(k) + SQL_IS_NULL
for k, v in where_slice.items()
])
command = (
"UPDATE " + quote_column(table_name) + "\n" +
"SET " +
sql_list([quote_column(k) + "=" + v for k, v in new_values.items()]) +
SQL_WHERE +
where_clause
)
self.execute(command, {})
def quote_list(values):
return sql_iso(sql_list(map(quote_value, values)))
def get_tuids(self, branch, revision, files):
"""
GET TUIDS FROM ENDPOINT, AND STORE IN DB
:param branch: BRANCH TO FIND THE REVISION/FILE
:param revision: THE REVISION NUNMBER
:param files: THE FULL PATHS TO THE FILES
:return: MAP FROM FILENAME TO TUID LIST
"""
# SCRUB INPUTS
revision = revision[:12]
files = [file.lstrip('/') for file in files]
with Timer(
"ask tuid service for {{num}} files at {{revision|left(12)}}",
{"num": len(files), "revision": revision},
silent=not self.enabled
):
response = self.db.query(
"SELECT file, tuids FROM tuid WHERE revision=" + quote_value(revision) +
" AND file IN " + quote_list(files)
)
found = {file: json2value(tuids) for file, tuids in response.data}
try:
remaining = set(files) - set(found.keys())
new_response = None
if remaining:
request = wrap({
"from": "files",
"where": {"and": [
{"eq": {"revision": revision}},
{"in": {"path": remaining}},
{"eq": {"branch": branch}}
]},
"branch": branch,
"meta": {
"format": "list",
"request_time": Date.now()
}
})
if self.push_queue is not None:
if DEBUG:
Log.note("record tuid request to SQS: {{timestamp}}", timestamp=request.meta.request_time)
self.push_queue.add(request)
else:
if DEBUG:
Log.note("no recorded tuid request")
if not self.enabled:
return found
new_response = http.post_json(
self.endpoint,
json=request,
timeout=self.timeout
)
with self.db.transaction() as transaction:
command = "INSERT INTO tuid (revision, file, tuids) VALUES " + sql_list(
quote_list((revision, r.path, value2json(r.tuids)))
for r in new_response.data
if r.tuids != None
)
if not command.endswith(" VALUES "):
transaction.execute(command)
self.num_bad_requests = 0
found.update({r.path: r.tuids for r in new_response.data} if new_response else {})
return found
except Exception as e:
self.num_bad_requests += 1
Till(seconds=SLEEP_ON_ERROR).wait()
if self.enabled and self.num_bad_requests >= 3:
self.enabled = False
Log.error("TUID service has problems.", cause=e)
return found
def add_cset_entries(self, ordered_rev_list, timestamp=False, number_forward=True):
'''
Adds a list of revisions to the table. Assumes ordered_rev_list is an ordered
based on how changesets are found in the changelog. Going forwards or backwards is dealt
with by flipping the list
:param ordered_cset_list: Order given from changeset log searching.
:param timestamp: If false, records are kept indefinitely
but if holes exist: (delete, None, delete, None)
those delete's with None's around them
will not be deleted.
:param numbered: If True, this function will number the revision list
by going forward from max(revNum), else it'll go backwards
from revNum, then add X to all revnums and self.next_revnum
where X is the length of ordered_rev_list
:return:
'''
with self.conn.transaction() as t:
current_min = t.get_one("SELECT min(revnum) FROM csetlog")[0]
current_max = t.get_one("SELECT max(revnum) FROM csetlog")[0]
if not current_min or not current_max:
current_min = 0
current_max = 0
direction = -1
start = current_min - 1
if number_forward:
direction = 1
start = current_max + 1
ordered_rev_list = ordered_rev_list[::-1]
insert_list = [
(
start + direction * count,
rev,
int(time.time()) if timestamp else -1
)
for count, rev in enumerate(ordered_rev_list)
]
# In case of overlapping requests
fmt_insert_list = []
for cset_entry in insert_list:
tmp = self._get_one_revision(t, cset_entry)
if not tmp:
fmt_insert_list.append(cset_entry)
for _, tmp_insert_list in jx.groupby(fmt_insert_list, size=SQL_CSET_BATCH_SIZE):
t.execute(
"INSERT INTO csetLog (revnum, revision, timestamp)" +
" VALUES " +
sql_list(
quote_set((revnum, revision, timestamp))
for revnum, revision, timestamp in tmp_insert_list
)
)
# Move the revision numbers forward if needed
self.recompute_table_revnums()
# Start a maintenance run if needed
if self.check_for_maintenance():
Log.note("Scheduling maintenance run on clogger.")
self.maintenance_signal.go()
def _db_worker(self, please_stop):
while not please_stop:
try:
with self._db_transaction():
result = self._query(
SQL_SELECT
+ all_columns
+ SQL_FROM
+ db_table_name
+ SQL_WHERE
+ "last_updated > "
+ quote_value(self.last_load)
+ SQL_ORDERBY
+ sql_list(map(quote_column, ["es_index", "name", "es_column"]))
)
with self.locker:
for r in result.data:
c = row_to_column(result.header, r)
self._add(c)
if c.last_updated > self.last_load:
self.last_load = c.last_updated
updates = self.todo.pop_all()
DEBUG and updates and Log.note(
"{{num}} columns to push to db", num=len(updates)
)
for action, column in updates:
while not please_stop:
try:
with self._db_transaction():
DEBUG and Log.note(
"{{action}} db for {{table}}.{{column}}",
action=action,
table=column.es_index,
column=column.es_column,
)
if action is EXECUTE:
self.db.execute(column)
elif action is UPDATE:
self.db.execute(
"UPDATE"
+ db_table_name
+ "SET"
+ sql_list(
[
"count=" + quote_value(column.count),
"cardinality="
+ quote_value(column.cardinality),
"multi=" + quote_value(column.multi),
"partitions="
+ quote_value(
value2json(column.partitions)
),
"last_updated="
+ quote_value(column.last_updated),
]
)
+ SQL_WHERE
+ SQL_AND.join(
[
"es_index = "
+ quote_value(column.es_index),
"es_column = "
+ quote_value(column.es_column),
"last_updated < "
+ quote_value(column.last_updated),
]
)
)
elif action is DELETE:
self.db.execute(
"DELETE FROM"
+ db_table_name
+ SQL_WHERE
+ SQL_AND.join(
[
"es_index = "
+ quote_value(column.es_index),
"es_column = "
+ quote_value(column.es_column),
]
)
)
else:
self._db_insert_column(column)
break
except Exception as e:
e = Except.wrap(e)
if "database is locked" in e:
Log.note("metadata database is locked")
Till(seconds=1).wait()
break
else:
Log.warning("problem updataing database", cause=e)
except Exception as e:
Log.warning("problem updating database", cause=e)
(Till(seconds=10) | please_stop).wait()
]
)
def row_to_column(header, row):
return Column(
**{
h: c
if c is None or h not in ("nested_path", "partitions")
else json2value(c)
for h, c in zip(header, row)
}
)
all_columns = sql_list([quote_column(c.name) for c in METADATA_COLUMNS])
SIMPLE_METADATA_COLUMNS = ( # FOR PURLY INTERNAL PYTHON LISTS, NOT MAPPING TO ANOTHER DATASTORE
[
Column(
name=c,
es_index="meta.columns",
es_column=c,
es_type="string",
jx_type=STRING,
last_updated=Date.now(),
nested_path=ROOT_PATH,
)
for c in ["table", "name", "type", "nested_path"]
]
def sort2sqlorderby(self, sort):
sort = jx.normalize_sort_parameters(sort)
return sql_list([quote_column(s.field) + (SQL_DESC if s.sort == -1 else SQL_ASC) for s in sort])
