Esempio n. 1
0
File: db.py Progetto: intari/KSP
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, )) ]
Esempio n. 2
0
File: db.py Progetto: nomada2/KSP
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
Esempio n. 3
0
File: db.py Progetto: akleiw/KSP
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
Esempio n. 4
0
File: db.py Progetto: akleiw/KSP
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))
Esempio n. 5
0
File: db.py Progetto: MSylvia/KSP
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)
Esempio n. 6
0
File: db.py Progetto: intari/KSP
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()
Esempio n. 7
0
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)
Esempio n. 8
0
File: db.py Progetto: nomada2/KSP
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, ))
        ]
Esempio n. 9
0
File: db.py Progetto: akleiw/KSP
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
Esempio n. 10
0
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')
Esempio n. 11
0
File: db.py Progetto: intari/KSP
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()
Esempio n. 12
0
File: db.py Progetto: nomada2/KSP
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
Esempio n. 13
0
File: db.py Progetto: MSylvia/KSP
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))
Esempio n. 14
0
File: db.py Progetto: akleiw/KSP
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)
Esempio n. 15
0
File: db.py Progetto: nomada2/KSP
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)
Esempio n. 16
0
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')
Esempio n. 17
0
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')
Esempio n. 18
0
File: db.py Progetto: nomada2/KSP
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
Esempio n. 19
0
File: db.py Progetto: nomada2/KSP
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))
Esempio n. 20
0
File: db.py Progetto: akleiw/KSP
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
Esempio n. 21
0
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)
Esempio n. 22
0
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')
Esempio n. 23
0
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')
Esempio n. 24
0
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()
Esempio n. 25
0
File: db.py Progetto: akleiw/KSP
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, )) ]
Esempio n. 26
0
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()
Esempio n. 27
0
File: db.py Progetto: akleiw/KSP
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)