import sqlite3
conn = sqlite3.connect('KoboReader.sqlite')
c = conn.cursor()
c.execute('''SELECT DISTINCT BookID FROM content''')
import re
re_path = re.compile(r'^file:///mnt/(?P<dev>[^/]*)/(?P<path>.*)/(?P<filename>[^/]*)$')
def splitpath(p):
m = re_path.match(p)
return (m.group('dev'), m.group('path'), m.group('filename'))
database = {}
for v in c.fetchall():
p = v[0]
if p and p.startswith('file:///mnt/'):
dev, dirpath, filename = splitpath(p)
if (dev,dirpath) not in database:
database[(dev,dirpath)] = []
database[(dev,dirpath)].append(filename)
for dev,dirpath in database:
shelf = dirpath.replace('/','_')
c.execute("SELECT * FROM Shelf WHERE InternalName='%s'" % shelf)
if c.fetchone() is None :
c.execute("INSERT INTO Shelf VALUES ('2012-07-25T19:59:29Z', NULL, '%s', '2012-07-25T19:59:29Z', '%s', NULL, 'false', 'true', 'false')" % (shelf,shelf))
c.execute("SELECT * FROM Shelf WHERE InternalName='%s'" % shelf)
for f in database[(dev,dirpath)]:
fullname = u'file:///mnt/%s/%s/%s' % (dev,dirpath,f)
fullname = fullname.replace("'","''")
c.execute("SELECT * FROM ShelfContent WHERE ShelfName='%s' AND ContentId='%s'" % (shelf, fullname))
r = c.fetchone()
if r is None:
c.execute("INSERT INTO ShelfContent VALUES ('%s','%s','2012-07-25T19:59:29Z','false','false')" % (shelf,fullname))
conn.commit()
c.close()