def update_from_20_to_21(db: sqlite3.Connection) -> None: # pragma: no cover
# for https://github.com/lemon24/reader/issues/178
from ._search import Search
search = Search(db)
if search.is_enabled():
search._drop_triggers()
db.execute("""
ALTER TABLE entries_search_sync_state
ADD COLUMN es_rowids TEXT NOT NULL DEFAULT '[]';
""")
input = db.execute("""
SELECT json_group_array(rowid), _id, _feed
FROM entries_search
GROUP BY _id, _feed;
""")
db.executemany(
"""
UPDATE entries_search_sync_state
SET es_rowids = ?
WHERE (id, feed) = (?, ?);
""",
input,
)
search._create_triggers()
def _write(db: sqlite3.Connection) -> None:
if len(entries) < 20:
self._logger.debug("add %d transactions", len(entries))
else:
self._logger.debug("add %d transactions (too many to show)",
len(entries))
db.executemany(self.CREATE_SQL, datas)
def k_means(con: sqlite3.Connection):
df = pd.read_sql_query(
'''SELECT "index", CD34, CD10, CD19, CD27, IgM, IgD, CD138, CD20, CD22, IgG, IgA, CD38, CD45 FROM loaded_data''',
con,
index_col="index")
print(df.head(10))
kmean = KMeans(n_clusters=500)
print(np.shape(df.to_numpy()[::5, :]))
kmean.fit(df.to_numpy()[::5, :])
clusters = pd.DataFrame(kmean.cluster_centers_,
columns=[
"CD34", "CD10", "CD19", "CD27", "IgM", "IgD",
"CD138", "CD20", "CD22", "IgG", "IgA", "CD38",
"CD45"
])
clusters["tmp_class"] = ''
clusters["tmp_color"] = ''
clusters["have_class"] = 125
print(clusters)
clusters.to_sql('clusters', con, if_exists='replace', index=True)
df["prediction"] = kmean.predict(df.to_numpy())
con.executemany("UPDATE loaded_data SET k_mean = ? WHERE \"index\" == ? ",
[(data["prediction"], int(i))
for i, data in df.iterrows()])
con.commit()
return None
def _store_stream(transaction: sqlite3.Connection):
# add the head blob and set it to be announced
transaction.execute(
"insert or ignore into blob values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)",
(sd_blob.blob_hash, sd_blob.length, 0, 1, "pending", 0, 0,
descriptor.blobs[0].blob_hash, descriptor.blobs[0].length, 0,
1, "pending", 0, 0))
# add the rest of the blobs with announcement off
if len(descriptor.blobs) > 2:
transaction.executemany(
"insert or ignore into blob values (?, ?, ?, ?, ?, ?, ?)",
[(blob.blob_hash, blob.length, 0, 0, "pending", 0, 0)
for blob in descriptor.blobs[1:-1]])
# associate the blobs to the stream
transaction.execute(
"insert or ignore into stream values (?, ?, ?, ?, ?)",
(descriptor.stream_hash, sd_blob.blob_hash, descriptor.key,
binascii.hexlify(descriptor.stream_name.encode()).decode(),
binascii.hexlify(
descriptor.suggested_file_name.encode()).decode()))
# add the stream
transaction.executemany(
"insert or ignore into stream_blob values (?, ?, ?, ?)",
[(descriptor.stream_hash, blob.blob_hash, blob.blob_num,
blob.iv) for blob in descriptor.blobs])
def _save_kademlia_peers(transaction: sqlite3.Connection):
transaction.execute('delete from peer').fetchall()
transaction.executemany(
'insert into peer(node_id, address, udp_port, tcp_port) values (?, ?, ?, ?)',
((binascii.hexlify(
p.node_id), p.address, p.udp_port, p.tcp_port)
for p in peers)).fetchall()
def store_stream(transaction: sqlite3.Connection, sd_blob: 'BlobFile',
descriptor: 'StreamDescriptor'):
# add all blobs, except the last one, which is empty
transaction.executemany(
"insert or ignore into blob values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
((blob.blob_hash, blob.length, 0, 0, "pending", 0, 0, blob.added_on,
blob.is_mine)
for blob in (descriptor.blobs[:-1] if len(descriptor.blobs) > 1 else
descriptor.blobs) + [sd_blob])).fetchall()
# associate the blobs to the stream
transaction.execute(
"insert or ignore into stream values (?, ?, ?, ?, ?)",
(descriptor.stream_hash, sd_blob.blob_hash, descriptor.key,
binascii.hexlify(descriptor.stream_name.encode()).decode(),
binascii.hexlify(
descriptor.suggested_file_name.encode()).decode())).fetchall()
# add the stream
transaction.executemany(
"insert or ignore into stream_blob values (?, ?, ?, ?)",
((descriptor.stream_hash, blob.blob_hash, blob.blob_num, blob.iv)
for blob in descriptor.blobs)).fetchall()
# ensure should_announce is set regardless if insert was ignored
transaction.execute(
"update blob set should_announce=1 where blob_hash in (?, ?)", (
sd_blob.blob_hash,
descriptor.blobs[0].blob_hash,
)).fetchall()
def _recover(transaction: sqlite3.Connection):
stream_hashes = [d.stream_hash for d, s in descriptors_and_sds]
for descriptor, sd_blob in descriptors_and_sds:
content_claim = transaction.execute(
"select * from content_claim where stream_hash=?",
(descriptor.stream_hash, )).fetchone()
delete_stream(
transaction,
descriptor) # this will also delete the content claim
store_stream(transaction, sd_blob, descriptor)
store_file(transaction, descriptor.stream_hash,
os.path.basename(descriptor.suggested_file_name),
download_directory, 0.0, 'stopped')
if content_claim:
transaction.execute(
"insert or ignore into content_claim values (?, ?)",
content_claim)
transaction.executemany(
"update file set status='stopped' where stream_hash=?",
[(stream_hash, ) for stream_hash in stream_hashes])
download_dir = binascii.hexlify(
self.conf.download_dir.encode()).decode()
transaction.executemany(
f"update file set download_directory=? where stream_hash=?",
[(download_dir, stream_hash) for stream_hash in stream_hashes])
def _sync_blobs(transaction: sqlite3.Connection):
transaction.executemany(
"update file set status='stopped' where stream_hash=?",
transaction.execute(
"select distinct sb.stream_hash from stream_blob sb "
"inner join blob b on b.blob_hash=sb.blob_hash and b.status=='pending'"
).fetchall())
def _write_processed_pdfs_as_fulltexts(connection: sqlite3.Connection,
full_texts: List[Tuple[int, str]]):
query = """
INSERT INTO fulltexts (postings_id, text)
VALUES (?, ?)
"""
connection.executemany(query, full_texts)
def delete_stream(transaction: sqlite3.Connection, descriptor: 'StreamDescriptor'):
blob_hashes = [(blob.blob_hash, ) for blob in descriptor.blobs[:-1]]
blob_hashes.append((descriptor.sd_hash, ))
transaction.execute("delete from content_claim where stream_hash=? ", (descriptor.stream_hash,))
transaction.execute("delete from file where stream_hash=? ", (descriptor.stream_hash,))
transaction.execute("delete from stream_blob where stream_hash=?", (descriptor.stream_hash,))
transaction.execute("delete from stream where stream_hash=? ", (descriptor.stream_hash,))
transaction.executemany("delete from blob where blob_hash=?", blob_hashes)
def empty_queue(queue: list, connection: sqlite3.Connection) -> list:
set_bar_desc('executing')
query = f'UPDATE {table} SET datetime = ?, date = ?, formatted = 1 WHERE ROWID = ?;'
params = [(datetime_obj, datetime_obj.date(), rowid) for datetime_obj, rowid in queue]
connection.executemany(query, params)
set_bar_desc('committing')
connection.commit()
set_bar_desc('reading')
return []
def writemany(db: sqlite3.Connection, objs: twitter.models.Status):
parameters = [
(obj.id_str, obj.created_at_in_seconds, obj.full_text,
obj.in_reply_to_status_id, obj.in_reply_to_user_id, str(obj))
for obj in objs
]
db.executemany(
f'''insert into tweets (id, time, text, reply_status_id, reply_user_id, raw_json) values (?, ?, ?, ?, ?, ?);''',
parameters)
db.commit()
def _write(db: sqlite3.Connection) -> None:
if len(entries) < 20:
self._logger.debug(
"update %d transactions: %s", len(entries),
[(hash_to_hex_str(a), b, byte_repr(c), TxFlags.to_repr(d))
for (a, b, c, d) in entries])
else:
self._logger.debug("update %d transactions (too many to show)",
len(entries))
db.executemany(self.UPDATE_MANY_SQL, datas)
def _update_table(database: sqlite3.Connection,
values: t.Iterable[t.Tuple[int, t.Any]]) -> None:
query = '''
UPDATE locations
SET (normalized, latitude, longitude) = (?,?,?) WHERE _rowid_ = ?;
'''
value_tuples = ((res.address, res.lat, res.lng, rowid)
for rowid, res in values)
database.executemany(query, value_tuples)
database.commit()
def _add_blobs(transaction: sqlite3.Connection):
transaction.executemany(
"insert or ignore into blob values (?, ?, ?, ?, ?, ?, ?)",
[(blob_hash, length, 0, 0,
"pending" if not finished else "finished", 0, 0)
for blob_hash, length in blob_hashes_and_lengths])
if finished:
transaction.executemany(
"update blob set status='finished' where blob.blob_hash=?",
[(blob_hash, )
for blob_hash, _ in blob_hashes_and_lengths])
def _sync_blobs(transaction: sqlite3.Connection) -> typing.Set[str]:
finished_blob_hashes = tuple(
blob_hash for (blob_hash, ) in transaction.execute(
"select blob_hash from blob where status='finished'").
fetchall())
finished_blobs_set = set(finished_blob_hashes)
to_update_set = finished_blobs_set.difference(blob_files)
transaction.executemany(
"update blob set status='pending' where blob_hash=?",
((blob_hash, ) for blob_hash in to_update_set)).fetchall()
return blob_files.intersection(finished_blobs_set)
def sync_bikes(strava, db: sqlite3.Connection):
with db: # transaction
db.execute("BEGIN")
old_bikes = set(b['id'] for b in db.execute("SELECT id FROM bike"))
for bike in strava.get_bikes():
old_bikes.discard(bike['id'])
sync_bike(bike, db)
delete = ((bike, ) for bike in old_bikes)
db.executemany("DELETE FROM bike WHERE id = ?", delete)
def InsertDataMany(con: sqlite3.Connection, sqlstr, params):
# sqlstr = "insert into ProviceData_AllConfirm (BeiJing) values (?)"
#
# params = [("2"),("3")]
if con:
try:
con.executemany(sqlstr, params)
con.commit()
return True
except Exception as e:
print(e)
return False
def query_table(conn: sqlite3.Connection, hashes: Iterable[str]):
"""
Context manager that creates a table called `query` that can be joined
against to speed up queries.
"""
with conn:
conn.execute('CREATE TEMPORARY TABLE query(filehash PRIMARY KEY)')
conn.executemany(
'''
INSERT INTO query(filehash) VALUES (?)
''', ((fh, ) for fh in hashes))
yield
with conn:
conn.execute('DROP TABLE IF EXISTS query')
def insert_result(connection: sqlite3.Connection, record: dict,
binary_record: dict):
with connection:
sql = 'UPDATE charset SET count=? WHERE oid=?'
connection.executemany(sql, ((count, index)
for index, count in record.items()))
print('Finished word record insertion')
regularize_relation(binary_record)
with connection:
sql = 'INSERT INTO relation values (?, ?, ?)'
connection.executemany(sql, ((l, r, c)
for l, d in binary_record.items()
for r, c in d.items()))
print('Finished relation insertion')
def insert_metric_values(
db: sqlite3.Connection,
metric_values: Dict[int, int],
has_data: Dict[int, bool],
commit: Commit,
) -> None:
values = [(commit.sha, metric_id, commit.date, value)
for metric_id, value in metric_values.items()
if has_data[metric_id]]
db.executemany(
'INSERT INTO metric_data (sha, metric_id, timestamp, running_value)\n'
'VALUES (?, ?, ?, ?)\n',
values,
)
def _load_file(xmpf: Path, db: sqlite3.Connection):
meta: CuteMeta = CuteMeta.from_file(xmpf)
timestamp = meta.last_updated
if not meta.uid: return
if not meta.hash: return
log.info("Loading %r", str(xmpf))
# Sync data
if meta.generate_keywords():
log.info("Updated autogenerated keywords")
timestamp = datetime.utcnow() # make sure we set the correct timestamp
with __lock:
try:
__hashes.add(meta.hash)
except KeyError:
log.warn("Possible duplicate %r", str(xmpf))
db.execute(
f"""
INSERT INTO Metadata (
last_updated, uid, hash, caption, author, source, group_id, rating, source_other, source_via
) VALUES (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)""", (timestamp, meta.uid, meta.hash, meta.caption, meta.author,
meta.source, meta.group_id, meta.rating, meta.source_other,
meta.source_via))
if meta.date:
db.execute(
"""
UPDATE Metadata SET date = ? WHERE uid is ?
""", (meta.date, meta.uid))
if meta.keywords:
db.executemany(
f"""
INSERT INTO Metadata_Keywords VALUES (
?, ?
)
""", [(meta.uid, keyword) for keyword in meta.keywords])
if meta.collections:
db.executemany(
f"""
INSERT INTO Metadata_Collections VALUES (
?, ?
)
""", [(meta.uid, collection) for collection in meta.collections])
def _delete_stream(transaction: sqlite3.Connection):
transaction.execute(
"delete from content_claim where stream_hash=? ",
(descriptor.stream_hash, ))
transaction.execute("delete from file where stream_hash=? ",
(descriptor.stream_hash, ))
transaction.execute("delete from stream_blob where stream_hash=?",
(descriptor.stream_hash, ))
transaction.execute("delete from stream where stream_hash=? ",
(descriptor.stream_hash, ))
transaction.execute("delete from blob where blob_hash=?",
(descriptor.sd_hash, ))
transaction.executemany("delete from blob where blob_hash=?",
[(blob.blob_hash, )
for blob in descriptor.blobs[:-1]])
def set_tags(journal_id: int, connection: Connection, tags: Tuple[str] = None):
if not tags:
connection.execute('INSERT INTO tags(entry_id) VALUES(?)',
(journal_id, ))
else:
old = get_tags(journal_id, connection)
added = set(tags).difference(old)
added = [(journal_id, tag) for tag in added]
connection.executemany('INSERT INTO tags(entry_id,tag) VALUES(?,?)',
added)
removed = set(old).difference(tags)
removed = [(journal_id, tag) for tag in removed]
connection.executemany('DELETE FROM tags WHERE entry_id=? AND tag=?',
removed)
connection.commit()
def _insert_events(
self,
c: Connection,
stored_events: List[StoredEvent],
**kwargs: Any,
) -> None:
params = []
for stored_event in stored_events:
params.append((
stored_event.originator_id.hex,
stored_event.originator_version,
stored_event.topic,
stored_event.state,
))
c.executemany(self.insert_events_statement, params)
def create_query_table(conn: sqlite3.Connection, hashes: Iterator[str]=None) -> None:
"""
Create a temporary table called `query_hash` that constists of a list of
file hashes. One can then use a NATURAL JOIN to fetch rows matching the
file hashes provided.
"""
conn.execute('''
CREATE TEMPORARY TABLE query_hash(hash PRIMARY KEY)
''')
if hashes is None:
hashes = filehashes()
conn.executemany('''
INSERT INTO query_hash(hash) VALUES (?)
''', ((fh,) for fh in hashes))
def _save_meta(meta: CuteMeta, timestamp: datetime, db: sqlite3.Connection):
# Sync data
if meta.generate_keywords():
log.info("Updated autogenerated keywords")
timestamp = datetime.utcnow() # make sure we set the correct timestamp
db.execute(
"""
DELETE FROM Metadata_Keywords WHERE uid is ?
""", (meta.uid, ))
db.execute(
"""
DELETE FROM Metadata_Collections WHERE uid is ?
""", (meta.uid, ))
if meta.keywords:
db.executemany(
f"""
INSERT INTO Metadata_Keywords VALUES (
?, ?
)
""", [(meta.uid, keyword) for keyword in meta.keywords])
if meta.collections:
db.executemany(
f"""
INSERT INTO Metadata_Collections VALUES (
?, ?
)
""", [(meta.uid, collection) for collection in meta.collections])
db.execute(
"""
UPDATE Metadata SET
last_updated = ?,
hash = ?,
caption = ?,
author = ?,
source = ?,
group_id = ?,
rating = ?,
source_other = ?,
source_via = ?
WHERE
uid is ?
""", (timestamp, meta.hash, meta.caption, meta.author, meta.source,
meta.group_id, meta.rating, meta.source_other, meta.source_via,
meta.uid))
def _initialize(conn: sqlite3.Connection) -> None:
schema = resources.read_text('wn', 'schema.sql')
with conn:
conn.executescript(schema)
# prepare lookup tables
conn.executemany('INSERT INTO parts_of_speech (pos) VALUES (?)',
((pos, ) for pos in constants.PARTS_OF_SPEECH))
conn.executemany('INSERT INTO adjpositions (position) VALUES (?)',
((adj, ) for adj in constants.ADJPOSITIONS))
conn.executemany('INSERT INTO synset_relation_types (type) VALUES (?)',
((typ, ) for typ in constants.SYNSET_RELATIONS))
conn.executemany('INSERT INTO sense_relation_types (type) VALUES (?)',
((typ, ) for typ in constants.SENSE_RELATIONS))
conn.executemany(
'INSERT INTO lexicographer_files (id, name) VALUES (?,?)',
((id, name) for name, id in constants.LEXICOGRAPHER_FILES.items()))
def _sync_blobs(transaction: sqlite3.Connection) -> typing.Set[str]:
to_update = [
(blob_hash, ) for (blob_hash, ) in transaction.execute(
"select blob_hash from blob where status='finished'")
if blob_hash not in blob_files
]
transaction.executemany(
"update blob set status='pending' where blob_hash=?",
to_update)
return {
blob_hash
for blob_hash, in _batched_select(
transaction,
"select blob_hash from blob where status='finished' and blob_hash in {}",
list(blob_files))
}
def update_manually_removed_files(transaction: sqlite3.Connection):
removed = []
for (stream_hash, download_directory, file_name) in transaction.execute(
"select stream_hash, download_directory, file_name from file where saved_file=1"
).fetchall():
if download_directory and file_name and os.path.isfile(
os.path.join(binascii.unhexlify(download_directory).decode(),
binascii.unhexlify(file_name).decode())):
continue
else:
removed.append((stream_hash,))
if removed:
transaction.executemany(
"update file set file_name=null, download_directory=null, saved_file=0 where stream_hash=?",
removed
)
