def test_02():
"""
Initialise PUPILS table from "old" raw data (creation from scratch,
no pre-existing table).
"""
db = DB(_testyear, 'RECREATE')
fpath = os.path.join(Paths.getYearPath(_testyear, 'DIR_SCHOOLDATA'),
'_test', 'pupil_data_0_raw')
REPORT.Test(
"Initialise with raw pupil data for school-year %d from:\n %s" %
(_testyear, fpath))
rpd = readRawPupils(_testyear, fpath)
for klass in sorted(rpd):
REPORT.Test("\n +++ Class %s" % klass)
for row in rpd[klass]:
REPORT.Test(" --- %s" % repr(row))
updateFromRaw(_testyear, rpd)
db.renameTable('PUPILS', 'PUPILS0')
db.deleteIndexes('PUPILS0')
REPORT.Test("Saved in table PUPILS0")
def updateFromRaw(schoolyear, rawdata):
"""Update the PUPILS table from the supplied raw pupil data.
Only the fields supplied in the raw data will be affected.
If there is no PUPILS table, create it, leaving fields for which no
data is supplied empty.
<rawdata>: {klass -> [<_IndexedData> instance, ...]}
"""
updated = False
allfields = list(CONF.TABLES.PUPILS_FIELDNAMES)
db = DB(schoolyear, flag='CANCREATE')
# Build a pid-indexed mapping of the existing (old) pupil data.
# Note that this is read in as <sqlite3.Row> instances!
oldclasses = {}
classes = set(rawdata) # collect all classes, old and new
if db.tableExists('PUPILS'):
for pmap in db.getTable('PUPILS'):
pid = pmap['PID']
klass = pmap['CLASS']
classes.add(klass)
try:
oldclasses[klass][pid] = pmap
except:
oldclasses[klass] = {pid: pmap}
# Collect rows for the new PUPILS table:
newpupils = []
# Run through the new data, class-by-class
for klass in sorted(classes):
changed = OrderedDict() # pids with changed fields
try:
plist = rawdata[klass]
except:
# Class not in new data
updated = True
#TODO: do I want to record this (with pupil names??)?
REPORT.Warn(_CLASSGONE, klass=klass)
continue
try:
oldpids = oldclasses[klass]
except:
# A new class
updated = True
oldpids = {}
#?
REPORT.Warn(_NEWCLASS, klass=klass)
#TODO: only the PIDs are stored, I would need at least their names, for reporting
added = []
for pdata in plist:
pid = pdata['PID']
prow = []
try:
pmap0 = oldpids[pid]
except:
# A new pupil
for f in allfields:
try:
val = pdata[f]
except:
val = None
prow.append(val)
updated = True
added.append(pid)
else:
del (oldpids[pid]) # remove entry for this pid
diff = {}
# Compare fields
for f in allfields:
oldval = pmap0[f]
try: # Allow for this field not being present in the
# new data.
val = pdata[f]
# Record changed fields
if val != oldval:
diff[f] = val
except:
val = oldval
prow.append(val)
if diff:
updated = True
changed[pid] = diff
newpupils.append(prow)
if added:
REPORT.Info(_NEWPUPILS, klass=klass, pids=repr(added))
if changed:
REPORT.Info(_PUPILCHANGES, klass=klass, data=repr(changed))
if oldpids:
REPORT.Info(_OLDPUPILS, klass=klass, pids=repr(list(oldpids)))
if updated:
REPORT.Info(_REBUILDPUPILS, year=schoolyear)
else:
REPORT.Warn(_NOUPDATES, year=schoolyear)
return
# Build database table NEWPUPILS.
# Use (CLASS, PSORT) as primary key, with additional index on PID.
# This makes quite a small db (without rowid).
indexes = db.makeTable2('NEWPUPILS',
allfields,
data=newpupils,
force=True,
pk=('CLASS', 'PSORT'),
index=('PID', ))
db.deleteTable('OLDPUPILS')
if db.tableExists('PUPILS'):
db.renameTable('PUPILS', 'OLDPUPILS')
db.renameTable('NEWPUPILS', 'PUPILS')
db.deleteIndexes('PUPILS')
db.makeIndexes('PUPILS', indexes)