class SQLiteUtils(object): @override def __init__(self, kwargs=None): self._index = None def setUp(self): self._index = QueryTable("testing") def tearDown(self): pass def setUpClass(self): pass def tearDownClass(self): pass def not_real_service(self): return True def execute_es_tests(self, subtest, tjson=False): subtest = wrap(subtest) subtest.name = extract_stack()[1]['method'] if subtest.disable: return if "sqlite" in subtest["not"]: return self.fill_container(subtest, tjson=tjson) self.send_queries(subtest) def fill_container(self, subtest, tjson=False): """ RETURN SETTINGS THAT CAN BE USED TO POINT TO THE INDEX THAT'S FILLED """ subtest = wrap(subtest) try: # INSERT DATA self._index.insert(subtest.data) except Exception, e: Log.error("can not load {{data}} into container", {"data": subtest.data}, e) frum = subtest.query['from'] if isinstance(frum, basestring): subtest.query["from"] = frum.replace(test_jx.TEST_TABLE, self._index.name) else: Log.error("Do not know how to handle") return Data()
def get_or_create_facts(self, fact_name, uid=UID): """ FIND TABLE BY NAME, OR CREATE IT IF IT DOES NOT EXIST :param fact_name: NAME FOR THE CENTRAL INDEX :param uid: name, or list of names, for the GUID :return: Facts """ about = self.db.about(fact_name) if not about: if uid != UID: Log.error("do not know how to handle yet") self.ns.columns._snowflakes[fact_name] = ["."] self.ns.columns.add(Column( name="_id", es_column="_id", es_index=fact_name, es_type=json_type_to_sqlite_type[STRING], jx_type=STRING, nested_path=['.'], multi=1, last_updated=Date.now() )) command = sql_create(fact_name, {UID: "INTEGER PRIMARY KEY", GUID: "TEXT"}, unique=UID) with self.db.transaction() as t: t.execute(command) return QueryTable(fact_name, self)
def __init__( self, db=None, # EXISTING Sqlite3 DATBASE, OR CONFIGURATION FOR Sqlite DB filename=None, # FILE FOR THE DATABASE (None FOR MEMORY DATABASE) kwargs=None # See Sqlite parameters ): global _config if isinstance(db, Sqlite): self.db = db else: # PASS CALL PARAMETERS TO Sqlite self.db = db = Sqlite(filename=filename, kwargs=set_default({}, db, kwargs)) self.db.create_new_functions() # creating new functions: regexp if not _config: # REGISTER sqlite AS THE DEFAULT CONTAINER TYPE from jx_base.container import config as _config if not _config.default: _config.default = {"type": "sqlite", "settings": {"db": db}} self.setup() self.ns = Namespace(db=db) self.about = QueryTable("meta.about", self) self.next_uid = self._gen_ids( ) # A DELIGHTFUL SOURCE OF UNIQUE INTEGERS
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 get_or_create_facts(self, fact_name, uid=UID): """ FIND TABLE BY NAME, OR CREATE IT IF IT DOES NOT EXIST :param fact_name: NAME FOR THE CENTRAL FACTS :param uid: name, or list of names, for the GUID :return: Facts """ about = self.db.about(fact_name) if not about: if uid != UID: Log.error("do not know how to handle yet") self.ns.columns._snowflakes[fact_name] = ["."] command = sql_create(fact_name, {UID: "INTEGER PRIMARY KEY", GUID: "TEXT"}, unique=UID) with self.db.transaction() as t: t.execute(command) return QueryTable(fact_name, self)
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 __init__(self, db=None): global _config if isinstance(db, Sqlite): self.db = db else: self.db = db = Sqlite(db) self.db.create_new_functions() # creating new functions: regexp if not _config: # REGISTER sqlite AS THE DEFAULT CONTAINER TYPE from jx_base.container import config as _config if not _config.default: _config.default = {"type": "sqlite", "settings": {"db": db}} self.setup() self.ns = Namespace(db=db) self.about = QueryTable("meta.about", self) self.next_uid = self._gen_ids() # A DELIGHTFUL SOURCE OF UNIQUE INTEGERS
def get_table(self, table_name): return QueryTable(table_name, self)
def setUp(self): self._index = QueryTable(name="testing", kwargs=test_jx.global_settings)
class SQLiteUtils(object): @override def __init__(self, kwargs=None): self._index = None def setUp(self): self._index = QueryTable(name="testing", kwargs=test_jx.global_settings) def tearDown(self): pass def setUpClass(self): pass def tearDownClass(self): pass def not_real_service(self): return True def execute_tests(self, subtest, tjson=False, places=6): subtest = wrap(subtest) subtest.name = get_stacktrace()[1]['method'] if subtest.disable: return self.fill_container(subtest, tjson=tjson) self.send_queries(subtest) def fill_container(self, subtest, tjson=False): """ RETURN SETTINGS THAT CAN BE USED TO POINT TO THE INDEX THAT'S FILLED """ subtest = wrap(subtest) try: # INSERT DATA self._index.insert(subtest.data) except Exception as e: Log.error("can not load {{data}} into container", {"data": subtest.data}, e) frum = subtest.query['from'] if isinstance(frum, text): subtest.query["from"] = frum.replace( test_jx.TEST_TABLE, self._index.facts.snowflake.fact_name) else: Log.error("Do not know how to handle") return Data({"index": subtest.query["from"]}) def send_queries(self, subtest): subtest = wrap(subtest) try: # EXECUTE QUERY num_expectations = 0 for k, v in subtest.items(): if k.startswith("expecting_"): # WHAT FORMAT ARE WE REQUESTING format = k[len("expecting_"):] elif k == "expecting": # NO FORMAT REQUESTED (TO TEST DEFAULT FORMATS) format = None else: continue num_expectations += 1 expected = v subtest.query.format = format subtest.query.meta.testing = True # MARK ALL QUERIES FOR TESTING SO FULL METADATA IS AVAILABLE BEFORE QUERY EXECUTION result = self.execute_query(subtest.query) compare_to_expected(subtest.query, result, expected) if num_expectations == 0: Log.error( "Expecting test {{name|quote}} to have property named 'expecting_*' for testing the various format clauses", {"name": subtest.name}) except Exception as e: Log.error("Failed test {{name|quote}}", name=subtest.name, cause=e) def execute_query(self, query): try: if startswith_field(query["from"], self._index.facts.snowflake.fact_name): return self._index.query(deepcopy(query)) elif query["from"] == "meta.columns": return self._index.query_metadata(deepcopy(query)) else: Log.error("Do not know how to handle") except Exception as e: Log.error("Failed query", e) def try_till_response(self, *args, **kwargs): self.execute_query( convert.json2value(convert.utf82unicode(kwargs["data"])))
def setUp(self): self._index = QueryTable("testing")
def setUp(self): container = Container(db = test_jx.global_settings.db) self._index = QueryTable(name="testing", container=container)