def list_last_read(asin, count = -1):
# lists all last_reads, in reverse timestamp order
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
# it's enough to check the last_read table
# if there are no entries there, quite unlikely to have bookmarks/notes/etc
return [ lr for lr in db.execute('SELECT * FROM last_read2 WHERE asin = ? ORDER BY timestamp DESC LIMIT ' + str(count), (asin, )) ]
def get_last_read_updates(device_serial, furthest=True):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
# get all book ids for which this device has last_read entries
device_lr = {
r.asin: r
for r in db.execute('SELECT * FROM last_read2 WHERE device = ?', (
device_serial, ))
}
# logging.debug("%s has last_read for %s", device_serial, device_books)
# get all entries where the latest read was done by some other device
if furthest:
last_read_query = 'SELECT * FROM last_read2 GROUP BY asin HAVING pos = MAX(pos) AND device != ?'
else:
last_read_query = 'SELECT * FROM last_read2 GROUP BY asin HAVING timestamp = MAX(timestamp) AND device != ?'
latest_lr = [
lr for lr in db.execute(last_read_query, (device_serial, ))
]
# only pick the latest entries done by other devices, when this device also has an entry
latest_lr = [
lr for lr in latest_lr
if lr.asin in device_lr and device_lr[lr.asin].pos < lr.pos
]
if latest_lr:
logging.debug("%s needs to update last_read from %s",
device_serial,
[(lr.asin, lr.device, lr.pos) for lr in latest_lr])
return latest_lr
def get_all(asin):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
# pick the latest last_read
result = [ lr for lr in db.execute('SELECT * FROM last_read2 WHERE asin = ? ORDER BY timestamp DESC LIMIT 1', (asin, )) ]
result.extend(db.execute('SELECT * FROM annotations2 WHERE asin = ? AND pos > 0 ORDER BY timestamp', (asin, )))
return result
def annotations_updated(device_serial, asin):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
for a in db.execute('SELECT * from annotations2 WHERE asin = ? AND device != ?', (asin, device_serial)):
synced_devices = device_serial if a.synced_devices is None else a.synced_devices + "," + device_serial
db.execute('UPDATE annotations2 SET synced_devices = ? WHERE id = ?', (synced_devices, a.id))
def load_all():
with sqlite3(_db_path) as db:
db.row_factory = sqlite3_Row
for row in db.execute('SELECT * FROM devices'):
row = dict(row)
yield _Device(**row)
global _last_changed
_last_changed = os.path.getmtime(_db_path)
def _execute(query, parameters = ()):
with sqlite3(_db_path) as db:
if query.startswith('INSERT INTO '):
qmarks = ('?', ) * len(parameters)
query = query.replace('*', ','.join(qmarks))
logging.debug("execute %s %s", query, parameters)
db.execute(query, parameters)
db.commit()
def load_all():
with sqlite3(_db_path) as db:
db.row_factory = sqlite3_Row
for row in db.execute('SELECT * FROM devices'):
row = dict(row)
yield _Device(**row)
global _last_changed
_last_changed = os.path.getmtime(_db_path)
def list_last_read(asin, count=-1):
# lists all last_reads, in reverse timestamp order
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
return [
lr for lr in db.execute(
'SELECT * FROM last_read2 WHERE asin = ? ORDER BY timestamp DESC LIMIT '
+ str(count), (asin, ))
]
def get_annotation_updates(device_serial):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
result = []
for a in db.execute('SELECT * from annotations2 WHERE device != ?', (device_serial, )):
if a.synced_devices is None or device_serial not in a.synced_devices.split(','):
result.append(a)
return result
def migrate_3(db_path):
with sqlite3(db_path) as db:
try: db.execute('ALTER TABLE devices ADD COLUMN alias TEXT')
except: pass
try: db.execute('ALTER TABLE devices ADD COLUMN kind TEXT')
except: pass
try: db.execute('ALTER TABLE devices ADD COLUMN lto INTEGER DEFAULT -1')
except: pass
db.execute('VACUUM')
def _execute(query, parameters = ()):
# logging.debug("execute %s %s", query, parameters)
with sqlite3(_db_path) as db:
if type(parameters) == tuple: # one query, simple parameters
db.execute(query, parameters)
elif type(parameters) == list: # multiple queries, assume the parameters contains a list of tuples
db.executemany(query, parameters)
else: # err...
raise Exception("don't know how to use parameters", str(parameters))
db.commit()
def get_annotation_updates(device_serial):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
result = []
for a in db.execute('SELECT * from annotations2 WHERE device != ?',
(device_serial, )):
if a.synced_devices is None or device_serial not in a.synced_devices.split(
','):
result.append(a)
return result
def annotations_updated(device_serial, asin):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
for a in db.execute('SELECT * from annotations2 WHERE asin = ? AND device != ?', (asin, device_serial)):
if a.synced_devices is None:
synced_devices = device_serial
else:
synced_devices = set(a.synced_devices.split(','))
synced_devices.add(device_serial)
synced_devices = ','.join(synced_devices)
db.execute('UPDATE annotations2 SET synced_devices = ? WHERE id = ?', (synced_devices, a.id))
def _execute(query, parameters = ()):
try:
with sqlite3(_db_path) as db:
if query.startswith('INSERT INTO '):
qmarks = ('?', ) * len(parameters)
query = query.replace('*', ','.join(qmarks))
db.execute(query, parameters)
if not query.startswith('SELECT '):
db.commit()
logging.debug("%s %s", query, parameters)
except:
logging.exception("%s %s", query, parameters)
def _execute(query, parameters=()):
try:
with sqlite3(_db_path) as db:
if query.startswith('INSERT INTO '):
qmarks = ('?', ) * len(parameters)
query = query.replace('*', ','.join(qmarks))
db.execute(query, parameters)
if not query.startswith('SELECT '):
db.commit()
# logging.debug("%s %s", query, parameters)
except:
logging.exception("%s %s", query, parameters)
def _migrate_2_annotations(db_path, row_factory):
try:
with sqlite3(db_path) as db:
db.row_factory = row_factory
for anot in db.execute('SELECT * FROM annotations'):
# all the timestamp the device sent are local time
timestamp = parse_timestamp(anot.timestamp)
db.execute('INSERT INTO annotations2 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(None, anot.asin, 'UNKNOWN', anot.kind, timestamp, anot.begin, anot.end, anot.pos, anot.state, anot.text, None))
except:
logging.exception("migrating to annotations2")
finally:
db.execute('DROP TABLE IF EXISTS annotations')
def _migrate_2_last_read(db_path, row_factory):
try:
with sqlite3(db_path) as db:
db.row_factory = row_factory
for lr in db.execute('SELECT * FROM last_read'):
# all the timestamp the device sent are local time
timestamp = parse_timestamp(lr.timestamp)
db.execute('INSERT INTO last_read2 VALUES (?, ?, ?, ?, ?, ?, ?)',
(None, lr.asin, 'UNKNOWN', timestamp, lr.begin, lr.pos, lr.state))
except:
logging.exception("migrating to last_read2")
finally:
db.execute('DROP TABLE IF EXISTS last_read')
def get_all(asin):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
# pick the latest last_read
result = [
lr for lr in db.execute(
'SELECT * FROM last_read2 WHERE asin = ? ORDER BY timestamp DESC LIMIT 1',
(asin, ))
]
result.extend(
db.execute(
'SELECT * FROM annotations2 WHERE asin = ? AND pos > 0 ORDER BY timestamp',
(asin, )))
return result
def annotations_updated(device_serial, asin):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
for a in db.execute(
'SELECT * from annotations2 WHERE asin = ? AND device != ?',
(asin, device_serial)):
if a.synced_devices is None:
synced_devices = device_serial
else:
synced_devices = set(a.synced_devices.split(','))
synced_devices.add(device_serial)
synced_devices = ','.join(synced_devices)
db.execute(
'UPDATE annotations2 SET synced_devices = ? WHERE id = ?',
(synced_devices, a.id))
def get_last_read_updates(device_serial, furthest = True):
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
# get all book ids for which this device has last_read entries
device_lr = { r.asin : r for r in db.execute('SELECT * FROM last_read2 WHERE device = ?', (device_serial, )) }
# logging.debug("%s has last_read for %s", device_serial, device_books)
# get all entries where the latest read was done by some other device
if furthest:
last_read_query = 'SELECT * FROM last_read2 GROUP BY asin HAVING pos = MAX(pos) AND device != ?'
else:
last_read_query = 'SELECT * FROM last_read2 GROUP BY asin HAVING timestamp = MAX(timestamp) AND device != ?'
latest_lr = [ lr for lr in db.execute(last_read_query, (device_serial, )) ]
# only pick the latest entries done by other devices, when this device also has an entry
latest_lr = [ lr for lr in latest_lr if lr.asin in device_lr and device_lr[lr.asin].pos < lr.pos ]
if latest_lr:
logging.debug("%s needs to update last_read from %s", device_serial, [ (lr.asin, lr.device, lr.pos) for lr in latest_lr ])
return latest_lr
def _execute(query, parameters=()):
# logging.debug("execute %s %s", query, parameters)
try:
with sqlite3(_db_path) as db:
if type(parameters) == tuple: # one query, simple parameters
db.execute(query, parameters)
elif type(
parameters
) == list: # multiple queries, assume the parameters contains a list of tuples
db.executemany(query, parameters)
else: # err...
raise Exception("don't know how to use parameters",
str(parameters))
db.commit()
except:
logging.exception("%s %s", query, parameters)
global _last_changed
_last_changed = os.path.getmtime(_db_path)
def _migrate_2_last_read(db_path, row_factory):
try:
with sqlite3(db_path) as db:
try:
db.execute('SELECT asin FROM last_read LIMIT 1')
except:
return
db.row_factory = row_factory
for lr in db.execute('SELECT * FROM last_read'):
# all the timestamp the device sent are local time
timestamp = parse_timestamp(lr.timestamp)
db.execute(
'INSERT INTO last_read2 (id, asin, device, timestamp, begin, pos, state) VALUES (?, ?, ?, ?, ?, ?, ?)',
(None, lr.asin, 'UNKNOWN', timestamp, lr.begin, lr.pos,
lr.state))
db.commit()
except:
logging.exception("migrating to last_read2")
finally:
db.execute('DROP TABLE IF EXISTS last_read')
def _migrate_2_annotations(db_path, row_factory):
try:
with sqlite3(db_path) as db:
try:
db.execute('SELECT asin FROM annotations LIMIT 1')
except:
return
db.row_factory = row_factory
for anot in db.execute('SELECT * FROM annotations'):
# all the timestamp the device sent are local time
timestamp = parse_timestamp(anot.timestamp)
db.execute(
'INSERT INTO annotations2 (id, asin, device, kind, timestamp, begin, end, pos, state, text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(None, anot.asin, 'UNKNOWN', anot.kind, timestamp,
anot.begin, anot.end, anot.pos, anot.state, anot.text))
db.commit()
except:
logging.exception("migrating to annotations2")
finally:
db.execute('DROP TABLE IF EXISTS annotations')
def migrate_2(db_path, row_factory):
_migrate_2_last_read(db_path, row_factory)
_migrate_2_annotations(db_path, row_factory)
with sqlite3(db_path) as db:
db.execute('VACUUM')
db.commit()
def list_last_read(asin, count = -1):
# lists all last_reads, in reverse timestamp order
with sqlite3(_db_path) as db:
db.row_factory = _namedtuple_row_factory
return [ lr for lr in db.execute('SELECT * FROM last_read2 WHERE asin = ? ORDER BY timestamp DESC LIMIT ' + str(count), (asin, )) ]
def migrate_2(db_path, row_factory):
_migrate_2_last_read(db_path, row_factory)
_migrate_2_annotations(db_path, row_factory)
with sqlite3(db_path) as db:
db.execute('VACUUM')
db.commit()
def load_all():
with sqlite3(_db_path) as db:
db.row_factory = sqlite3_Row
for row in db.execute('SELECT * FROM devices'):
row = dict(row)
yield _Device(**row)