I think I have a better handle on how and why to code this up in "my application" (an overstatement for my Python script), thanks for bearing with me. If you care, below is my first attempt for this using the Python library.
Sorry about the messy renaming, I received messy partitions from data sources.
Maybe there is one question I already have on the Python script: Can I rename the table not to use the "sys." beginning, or that's part of the architecture?
And by the way, if you can recommend your favorite SQL:2003 tutorial that is compatible with MonetDB, I would be happy to guide my coworkers to it!
# script to merge tables in MonetDB
import re
from monetdb import mapi
server = mapi.Server()
server.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="dbfarm", language="sql")
def tablemerge(stub,yearlist):
for year in yearlist:
server.cmd('ALTER TABLE %s_%d ADD COLUMN "year" INTEGER DEFAULT %d;' % (stub,year,year))
newstub = re.sub(r'sys.ds_chocker_lev_', r'', stub)
if year == yearlist[0]:
unioncall = 'CREATE TABLE %s AS SELECT * FROM %s_%d ' % (newstub,stub,year)
else:
unioncall += 'UNION ALL SELECT * FROM %s_%d ' % (stub,year)
unioncall += ';'
server.cmd(unioncall)
for year in yearlist:
server.cmd('DROP TABLE %s_%d;' % (stub,year))
print '%s done.' % stub
def treatmentmerge(stub,stringlist):
for file in stringlist:
if file == stringlist[0]:
unioncall = 'CREATE TABLE %s AS SELECT * FROM %s%s ' % (stub,stub,file)
else:
unioncall += 'UNION ALL SELECT * FROM %s%s ' % (stub,file)
unioncall += ';'
server.cmd(unioncall)
for file in stringlist:
server.cmd('DROP TABLE %s%s;' % (stub,file))
print '%s done.' % stub
def kurumerge(st,yearlist):
stub = 'ku'+str(st)
for year in yearlist:
server.cmd('ALTER TABLE sys.ds_chocker_lev_kuru%d_%s ADD COLUMN "year" INTEGER DEFAULT %d;' % (year,stub,year))
if year == yearlist[0]:
unioncall = 'CREATE TABLE %s AS SELECT * FROM sys.ds_chocker_lev_kuru%d_%s ' % (stub,year,stub)
else:
unioncall += 'UNION ALL SELECT * FROM %s_%d ' % (stub,year)
unioncall += ';'
server.cmd(unioncall)
for year in yearlist:
server.cmd('DROP TABLE sys.ds_chocker_lev_kuru%d_%s;' % (year,stub))
print '%s done.' % stub
def drugsmerge():
for year in xrange(2005,2013):
for month in xrange(1,13):
if year == 2005 & month < 7:
continue
server.cmd('ALTER TABLE lmed_%d_mon%d ADD COLUMN "year" INTEGER DEFAULT %d;' % (year,month,year))
server.cmd('ALTER TABLE lmed_%d_mon%d ADD COLUMN "month" INTEGER DEFAULT %d;' % (year,month,month))
if year == 2005 & month == 7:
unioncall = 'CREATE TABLE lmed AS SELECT * FROM lmed_%d_mon%d ' % (year,month)
else:
unioncall += 'UNION ALL SELECT * FROM lmed_%d_mon%d ' % (year,month)
unioncall += ';'
server.cmd(unioncall)
for year in yearlist:
server.cmd('DROP TABLE lmed_%d_mon%d;' % (year,month))
print 'lmed done.'
for stub in ['civandr']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1998,2013))
# use with xrange(start,end+1)
for stub in ['civil']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1968,2013))
for stub in ['iot']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1990,2013))
for stub in ['fas']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(2000,2013))
for stub in ['form']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1999,2008))
for stub in ['jobb']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1985,2012))
for stub in ['lisaftg','lisaindivid']:
tablemerge('sys.ds_chocker_lev_%s' % stub,xrange(1990,2012))
for st in [21,26,31,33]:
kurumerge(st,xrange(1999,2008))
for st in [20,22,25,32,40,50,55]:
kurumerge(st,xrange(1999,2000).extend(xrange(2003,2008)))
for st in [34,35,52,56]:
kurumerge(st,xrange(2003,2008))
drugsmerge()
treatmentmerge('sys.oppenvard',['2011','2012','20012005','20062010'])
treatmentmerge('sys.slutenvard',['2011','2012','19871996','1997','19982004','20052010'])