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])