def create_indexes(self):
helper.drop_idxs(self.c)
print("Recreating indexes...")
t = time.time()
queries = """
create index `idxclid8` on `var_data_picktime` ( `var`, `sumConflicts_at_picktime`, `latest_vardist_feature_calc`);
create index `idxclid81` on `var_data_picktime` ( `var`, `sumConflicts_at_picktime`);
create index `idxclid82` on `var_dist` ( `var`, `latest_vardist_feature_calc`);
create index `idxclid9` on `var_data_fintime` ( `var`, `sumConflicts_at_picktime`);
create index `idxclid10` on `dec_var_clid` ( `var`, `sumConflicts_at_picktime`, `clauseID`);
create index `idxclid-s2` on `restart_dat_for_var` (`conflicts`, `latest_satzilla_feature_calc`, `branch_strategy`);
create index `idxclid-s4` on `satzilla_features` (`latest_satzilla_feature_calc`);
create index `idxclid-s1` on `sum_cl_use` ( `clauseID`, `num_used`);
"""
for l in queries.split('\n'):
t2 = time.time()
if options.verbose:
print("Creating/dropping index: ", l)
self.c.execute(l)
if options.verbose:
print("Index dropping&creation T: %-3.2f s" %
(time.time() - t2))
print("indexes dropped&created T: %-3.2f s" % (time.time() - t))
def one_database(dbfname):
with QueryAddIdxes(dbfname) as q:
q.measure_size()
helper.drop_idxs(q.c)
q.create_indexes()
with helper.QueryFill(dbfname) as q:
q.delete_and_create_all()
q.fill_used_later()
q.fill_used_later_X("long", offset=0, duration=options.long)
q.fill_used_later_X("short", offset=0, duration=options.short)
q.fill_used_later_X("forever",
offset=0,
duration=(1000 * 1000 * 1000),
forever=True)
q.fill_used_later_X("long", offset=options.long, duration=options.long)
q.fill_used_later_X("short",
offset=options.short,
duration=options.short)
print("Using sqlite3 DB file %s" % dbfname)
for tier in ["short", "long", "forever"]:
print("------> Doing tier {tier}".format(tier=tier))
with QueryCls(dbfname) as q:
ok, df = q.get_one_data_all_dumpnos(tier)
if not ok:
print("-> Skipping file {file} {tier}".format(file=dbfname,
tier=tier))
continue
if options.verbose:
print("Describing----")
dat = df.describe()
print(dat)
print("Describe done.---")
print("Features: ", df.columns.values.flatten().tolist())
if options.verbose:
print("Describing post-transform ----")
print(df.describe())
print("Describe done.---")
print("Features: ", df.columns.values.flatten().tolist())
cleanname = re.sub(r'\.cnf.gz.sqlite$', '', dbfname)
cleanname = re.sub(r'\.db$', '', dbfname)
cleanname = re.sub(r'\.sqlitedb$', '', dbfname)
cleanname = "{cleanname}-cldata-{tier}".format(cleanname=cleanname,
tier=tier)
dump_dataframe(df, cleanname)
def drop_idxs_tables(self):
helper.drop_idxs(self.c)
print("Dropping tables...")
t = time.time()
q = """
drop table if exists `goodClauses`;
drop table if exists `idxused_clauses`;
"""
for l in q.split('\n'):
t2 = time.time()
if options.verbose:
print("Dropping table: ", l)
self.c.execute(l)
if options.verbose:
print("Dopped table T: %-3.2f s" % (time.time() - t2))
print("Tables dropped T: %-3.2f s" % (time.time() - t))
def create_indexes(self):
helper.drop_idxs(self.c)
print("Recreating indexes...")
queries = """
create index `idx1` on `tags` (`runid`, `name`);
create index `idx2` on `timepassed` (`runid`, `elapsed`);
create index `idx3` on `timepassed` (`runid`, `elapsed`, `name`);
create index `idx4` on `memused` (`runid`, `MB`, `name`);
"""
for l in queries.split('\n'):
t2 = time.time()
if options.verbose:
print("Creating index: ", l)
self.c.execute(l)
if options.verbose:
print("Index creation T: %-3.2f s" % (time.time() - t2))
print("indexes created T: %-3.2f s" % (time.time() - t))
def create_indexes(self):
helper.drop_idxs(self.c)
print("Creating needed indexes...")
t = time.time()
q = """
create index `idxclid-del` on `cl_last_in_solver` (`clauseID`, `conflicts`);
create index `idxclid-del2` on `used_clauses` (`clauseID`);
create index `idxclid-del3` on `used_clauses` (`clauseID`, `used_at`);
create index `idxclid1-2` on `clause_stats` (`clauseID`);
"""
for l in q.split('\n'):
t2 = time.time()
if options.verbose:
print("Creating index: ", l)
self.c.execute(l)
if options.verbose:
print("Index creation T: %-3.2f s" % (time.time() - t2))
print("indexes created T: %-3.2f s" % (time.time() - t))
def del_table_and_vacuum(self):
helper.drop_idxs(self.c)
t = time.time()
queries = """
DROP TABLE IF EXISTS `used_later`;
DROP TABLE IF EXISTS `only_keep_rdb`;
DROP TABLE IF EXISTS `used_cl_ids`;
"""
for q in queries.split("\n"):
self.c.execute(q)
print("Deleted tables T: %-3.2f s" % (time.time() - t))
q = """
vacuum;
"""
t = time.time()
lev = self.conn.isolation_level
self.conn.isolation_level = None
self.c.execute(q)
self.conn.isolation_level = lev
print("Vacuumed database T: %-3.2f s" % (time.time() - t))
(options, args) = parser.parse_args()
if len(args) < 1:
print("ERROR: You must give the sqlite file!")
exit(-1)
if not options.fair:
print("NOTE: Sampling will NOT be fair.")
print(" This is because otherwise, DB will be huge")
print(" and we need lots of positive datapoints")
print(" most of which will be from clauses that are more used")
with QueryDatRem(args[0]) as q:
q.check_db_sanity()
helper.dangerous(q.c)
helper.drop_idxs(q.c)
q.create_indexes1()
q.remove_too_many_vardata()
if False:
print("This is good for verifying that the fast ones are close")
# slower percentiles
t = time.time()
with helper.QueryFill(args[0]) as q:
helper.dangerous(q.c)
q.delete_and_create_all()
q.create_indexes(verbose=options.verbose)
q.fill_used_later()
q.fill_used_later_X("short", duration=10000)
q.fill_used_later_X("long", duration=50000)
q.fill_used_later_X("forever",
def drop_idxs(self):
helper.drop_idxs(self.c)
