def _upgradeSchema(self):
"Alter tables prior to ORM initialization."
db = self.db
# speed up the upgrade
db.execute("pragma temp_store = memory")
db.execute("pragma cache_size = 10000")
db.execute("pragma synchronous = off")
# these weren't always correctly set
db.execute("pragma page_size = 4096")
db.execute("pragma legacy_file_format = 0")
for mid in db.list("select id from models"):
# ensure the ordinals are correct for each cardModel
for c, cmid in enumerate(
db.list(
"""select id from cardModels
where modelId = ? order by ordinal""", mid)):
db.execute("update cardModels set ordinal = ? where id = ?", c,
cmid)
# and fieldModel
for c, fmid in enumerate(
db.list(
"""select id from fieldModels
where modelId = ? order by ordinal""", mid)):
db.execute("update fieldModels set ordinal = ? where id = ?",
c, fmid)
# then fix ordinals numbers on cards & fields
db.execute("""update cards set ordinal = (select ordinal from
cardModels where cardModels.id = cardModelId)""")
db.execute("""update fields set ordinal = (select ordinal from
fieldModels where id = fieldModelId)""")
# notes
###########
# tags should have a leading and trailing space if not empty, and not
# use commas
db.execute("""
update facts set tags = (case
when trim(tags) == "" then ""
else " " || replace(replace(trim(tags), ",", " "), " ", " ") || " "
end)
""")
# pull facts into memory, so we can merge them with fields efficiently
facts = db.all("""
select id, id, modelId, cast(created*1000 as int), cast(modified as int),
0, tags from facts order by created""")
# build field hash
fields = {}
for (fid, ord, val) in db.execute("""select factId, ordinal, value
from fields order by factId, ordinal"""):
if fid not in fields:
fields[fid] = []
val = self._mungeField(val)
fields[fid].append((ord, val))
# build insert data and transform ids, and minimize qt's
# bold/italics/underline cruft.
map = {}
data = []
factidmap = {}
from anki.utils import minimizeHTML
highest = 0
for c, row in enumerate(facts):
oldid = row[0]
row = list(row)
if row[3] <= highest:
highest = max(highest, row[3]) + 1
row[3] = highest
else:
highest = row[3]
factidmap[row[0]] = row[3]
row[0] = row[3]
del row[3]
map[oldid] = row[0]
# convert old 64bit id into a string, discarding sign bit
row[1] = base91(abs(row[1]))
row.append(
minimizeHTML("\x1f".join([x[1]
for x in sorted(fields[oldid])])))
data.append(row)
# and put the facts into the new table
db.execute("drop table facts")
_addSchema(db, False)
db.executemany("insert into notes values (?,?,?,?,?,?,?,'','',0,'')",
data)
db.execute("drop table fields")
# cards
###########
# we need to pull this into memory, to rewrite the creation time if
# it's not unique and update the fact id
rows = []
cardidmap = {}
highest = 0
for row in db.execute("""
select id, cast(created*1000 as int), factId, ordinal,
cast(modified as int), 0,
(case relativeDelay
when 0 then 1
when 1 then 2
when 2 then 0 end),
(case type
when 0 then 1
when 1 then 2
when 2 then 0
else type end),
cast(due as int), cast(interval as int),
cast(factor*1000 as int), reps, noCount from cards
order by created"""):
# find an unused time
row = list(row)
if row[1] <= highest:
highest = max(highest, row[1]) + 1
row[1] = highest
else:
highest = row[1]
# rewrite fact id
row[2] = factidmap[row[2]]
# note id change and save all but old id
cardidmap[row[0]] = row[1]
rows.append(row[1:])
# drop old table and rewrite
db.execute("drop table cards")
_addSchema(db, False)
db.executemany(
"""
insert into cards values (?,?,1,?,?,?,?,?,?,?,?,?,?,0,0,0,0,"")""", rows)
# reviewHistory -> revlog
###########
# fetch the data so we can rewrite ids quickly
r = []
for row in db.execute("""
select
cast(time*1000 as int), cardId, 0, ease,
cast(nextInterval as int), cast(lastInterval as int),
cast(nextFactor*1000 as int), cast(min(thinkingTime, 60)*1000 as int),
yesCount from reviewHistory"""):
row = list(row)
# new card ids
try:
row[1] = cardidmap[row[1]]
except:
# id doesn't exist
continue
# no ease 0 anymore
row[3] = row[3] or 1
# determine type, overwriting yesCount
# newInt = row[4]
oldInt = row[5]
yesCnt = row[8]
# yesCnt included the current answer
if row[3] > 1:
yesCnt -= 1
if oldInt < 1:
# new or failed
if yesCnt:
# type=relrn
row[8] = 2
else:
# type=lrn
row[8] = 0
else:
# type=rev
row[8] = 1
r.append(row)
db.executemany(
"insert or ignore into revlog values (?,?,?,?,?,?,?,?,?)", r)
db.execute("drop table reviewHistory")
# deck
###########
self._migrateDeckTbl()
# tags
###########
tags = {}
for t in db.list("select tag from tags"):
tags[t] = intTime()
db.execute("update col set tags = ?", json.dumps(tags))
db.execute("drop table tags")
db.execute("drop table cardTags")
# the rest
###########
db.execute("drop table media")
db.execute("drop table sources")
self._migrateModels()
_updateIndices(db)
def _upgradeSchema(self):
"Alter tables prior to ORM initialization."
db = self.db
# speed up the upgrade
db.execute("pragma temp_store = memory")
db.execute("pragma cache_size = 10000")
db.execute("pragma synchronous = off")
# these weren't always correctly set
db.execute("pragma page_size = 4096")
db.execute("pragma legacy_file_format = 0")
# previous versions sometimes had cards with incorrect ordinals and
# the db check didn't fix that. so we fix it here:
db.execute("""
update cards set ordinal = (select ordinal from cardModels
where id = cards.cardModelId)""")
# notes
###########
# tags should have a leading and trailing space if not empty, and not
# use commas
db.execute("""
update facts set tags = (case
when trim(tags) == "" then ""
else " " || replace(replace(trim(tags), ",", " "), " ", " ") || " "
end)
""")
# pull facts into memory, so we can merge them with fields efficiently
facts = db.all("""
select id, id, modelId, 1, cast(created*1000 as int), cast(modified as int),
0, tags from facts order by created""")
# build field hash
fields = {}
for (fid, ord, val) in db.execute(
"select factId, ordinal, value from fields order by factId, ordinal"):
if fid not in fields:
fields[fid] = []
val = self._mungeField(val)
fields[fid].append((ord, val))
# build insert data and transform ids, and minimize qt's
# bold/italics/underline cruft.
map = {}
data = []
factidmap = {}
from anki.utils import minimizeHTML
highest = 0
for c, row in enumerate(facts):
oldid = row[0]
row = list(row)
if row[4] <= highest:
highest = max(highest, row[4]) + 1
row[4] = highest
else:
highest = row[4]
factidmap[row[0]] = row[4]
row[0] = row[4]
del row[4]
map[oldid] = row[0]
# convert old 64bit id into a string, discarding sign bit
row[1] = base91(abs(row[1]))
row.append(minimizeHTML("\x1f".join([x[1] for x in sorted(fields[oldid])])))
data.append(row)
# and put the facts into the new table
db.execute("drop table facts")
_addSchema(db, False)
db.executemany("insert into notes values (?,?,?,?,?,?,?,?,'','',0,'')", data)
db.execute("drop table fields")
# cards
###########
# we need to pull this into memory, to rewrite the creation time if
# it's not unique and update the fact id
rows = []
cardidmap = {}
highest = 0
for row in db.execute("""
select id, cast(created*1000 as int), factId, ordinal,
cast(modified as int), 0,
(case relativeDelay
when 0 then 1
when 1 then 2
when 2 then 0 end),
(case type
when 0 then 1
when 1 then 2
when 2 then 0
else type end),
cast(due as int), cast(interval as int),
cast(factor*1000 as int), reps, noCount from cards
order by created"""):
# find an unused time
row = list(row)
if row[1] <= highest:
highest = max(highest, row[1]) + 1
row[1] = highest
else:
highest = row[1]
# rewrite fact id
row[2] = factidmap[row[2]]
# note id change and save all but old id
cardidmap[row[0]] = row[1]
rows.append(row[1:])
# drop old table and rewrite
db.execute("drop table cards")
_addSchema(db, False)
db.executemany("""
insert into cards values (?,?,1,?,?,?,?,?,?,?,?,?,?,0,0,0,"")""",
rows)
# reviewHistory -> revlog
###########
# fetch the data so we can rewrite ids quickly
r = []
for row in db.execute("""
select
cast(time*1000 as int), cardId, 0, ease,
cast(nextInterval as int), cast(lastInterval as int),
cast(nextFactor*1000 as int), cast(min(thinkingTime, 60)*1000 as int),
yesCount from reviewHistory"""):
row = list(row)
# new card ids
try:
row[1] = cardidmap[row[1]]
except:
# id doesn't exist
continue
# no ease 0 anymore
row[3] = row[3] or 1
# determine type, overwriting yesCount
newInt = row[4]
oldInt = row[5]
yesCnt = row[8]
# yesCnt included the current answer
if row[3] > 1:
yesCnt -= 1
if oldInt < 1:
# new or failed
if yesCnt:
# type=relrn
row[8] = 2
else:
# type=lrn
row[8] = 0
else:
# type=rev
row[8] = 1
r.append(row)
db.executemany(
"insert or ignore into revlog values (?,?,?,?,?,?,?,?,?)", r)
db.execute("drop table reviewHistory")
# deck
###########
self._migrateDeckTbl()
# tags
###########
tags = {}
for t in db.list("select tag from tags"):
tags[t] = intTime()
db.execute("update col set tags = ?", simplejson.dumps(tags))
db.execute("drop table tags")
db.execute("drop table cardTags")
# the rest
###########
db.execute("drop table media")
db.execute("drop table sources")
self._migrateModels()
_updateIndices(db)