def main(table):
import db_access3
DB = "ECM_Acts_CBGM"
TABLE = table
dba = db_access3.DBA("remote")
cursor = dba.cursor()
cmd = "select count(distinct anfadr, endadr) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
PASSCOUNT = res[0]
print "%s hat %d variierte Stellen." % (TABLE, PASSCOUNT)
cmd = "select count(distinct hsnr) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
HSNRCOUNT = res[0]
print "%s hat %d Handschriften." % (TABLE, HSNRCOUNT)
cmd = "select distinct hsnr from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
hsnrs = cursor.fetchall()
cmd = "select distinct anfadr, endadr from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
passages = cursor.fetchall()
for hsnr in hsnrs:
for passage in passages:
cmd = "select count(id) from %s.%s " % (DB, TABLE)
cmd += "where hsnr = %d " % (hsnr[0])
cmd += "and anfadr = %d and endadr = %d " % (passage[0],
passage[1])
cursor.execute(cmd)
res = cursor.fetchone()
if res[0] != 1:
print "Fehler bei Handschrift %d an Stelle %d/%d. " % (
hsnr[0], passage[0], passage[1])
cursor.close()
dba.close()
def main_3(db, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen erschließen
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
cmd = "show tables from `%s` like 'Acts%s%%'; " % (db, sChapter)
cursor.execute(cmd)
tables = cursor.fetchall()
for table in tables:
# Tabellenfelder loeschen mit 'alter table ..., drop ...'
cmd = "alter table `%s`.`%s` " % (db, table[0])
cmd += "drop SUFFIX1, drop ADR, drop ZUSATZ, drop LesartenKey "
printer(cmd)
try:
cursor.execute(cmd)
except:
pass
dba.commit()
cursor.close()
dba.close()
def main_9(db1, db2, tab1, tab2, mode="remote"):
import db_access3
dba = db_access3.DBA(mode)
cursor = dba.cursor()
sourcetable = tab1
lactable = sourcetable + "Lac"
# 0. Truncate table
cmd = "truncate %s.%s " % (db1, lactable)
cursor.execute(cmd)
dba.commit()
# 1.1.
passages, passcount = dba.getPassages(db1, sourcetable)
# 1.2.
cmd = "select distinct hs from %s.%s " % (db2, tab2)
cmd += "order by hsnr "
cursor.execute(cmd)
mss = cursor.fetchall()
# 1.3.
for ms in mss:
hs = ms[0]
for passage in passages:
anfadr = passage[0]
endadr = passage[1]
cmd = "select count(id) from %s.%s " % (db2, tab2)
cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr)
cmd += "and hs = '%s' " % (hs)
cursor.execute(cmd)
result = cursor.fetchone()
rescount = result[0]
if rescount > 0:
enter2LocalLacList(cursor, hs, db1, lactable, anfadr, endadr)
dba.commit() # it's an InnoDB table
cursor.close()
dba.close()
def main_5(database, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen aus chapter generieren
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
table = "Acts" + sChapter + "att"
if database.endswith("2"):
table += "_2"
if database.endswith("3"):
table += "_3"
# Alle variierten Stellen auflisten
passages, void = dba.getPassages(database, table)
for passage in passages:
anfadr = passage[0]
endadr = passage[1]
# new code
# get readings without labezsuf and overlapping variants
cmd = "select distinct anfadr, endadr, labez "
cmd += "from `%s`.`%s` " % (database, table)
cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr)
cmd += "and labez not like 'z%%'; "
count = cursor.execute(cmd)
if count == 1:
cmd = "delete from `%s`.`%s` " % (database, table)
cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr)
cursor.execute(cmd)
# old code
## Pro variierter Stelle alle Lesarten auflisten
#readings, void = dba.getReadings(database, table, anfadr, endadr)
#HasVariant = False
#for reading in readings:
# labez = reading[2]
# # Gibt es eine Lesart anders als 'a' oder 'z...'?
# if labez in ("b", "c", "d", "e", "f", "g", "h", "i", "j",
# "k", "l", "m", "n", "o", "p", "q", "r", "s",
# "t", "u", "v", "w", "x", "y"):
# HasVariant = True
# break
#if not HasVariant:
# # Wenn nicht, dann diesen Eintrag loeschen, da es sich nicht
# # um eine variierte Stelle handelt: Es gibt keine Varianten
# cmd = "delete from `%s`.`%s` " % (database, table)
# cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr)
# printer(cmd)
# cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main_1(sourceDB, targetDB, chapter=0, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3, datetime
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# sourceDB = "Apparat_Annette"
# targetDB = "ECM_Acts_UpdatePh2"
# 1.0. Loeschen der bereits vorhandenen Tabellen in der Zieldatenbank
cmd = "drop database `%s` " % (targetDB)
cursor.execute(cmd)
cmd = "create database `%s` " % (targetDB)
cursor.execute(cmd)
if chapter == 0:
# 1.1. Anzeige aller Tabellen in der Quelldatenbank
cmd = "show tables from `%s` like 'Acts%%'; " % (sourceDB)
else:
# 1.1. Anzeige der (beiden) Tabellen eines Kapitels
if chapter < 10:
sChapter = "0" + str(chapter)
else:
sChapter = str(chapter)
cmd = "show tables from `%s` like 'Acts%s%%'; " % (sourceDB, sChapter)
printer(cmd)
cursor.execute(cmd)
tables = cursor.fetchall()
for table in tables:
# 1.2. Generieren eines Datumsstempels
d = "%s" % datetime.date.today()
datum = d[:4] + d[5:7] + d[8:]
# 1.3. Ausschneiden der Buch-, Kapitel- und Versionsbezeichnung
name = table[0][:9]
# Neuen Namen zusammensetzen
name += datum
# Bei Bedarf das Suffix 'lac' anfuegen
if table[0].endswith("lac"):
name += "lac"
# 1.4. Tabellen anlegen mit 'create table like ...'
cmd = "create table `%s`.`%s` " % (targetDB, name)
cmd += "like `%s`.`%s` " % (sourceDB, table[0])
printer(cmd)
cursor.execute(cmd)
# 1.5. Befuellen mit 'insert into ... select * from ...'
cmd = "insert into `%s`.`%s` " % (targetDB, name)
cmd += "select * from `%s`.`%s` " % (sourceDB, table[0])
printer(cmd)
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main(table):
import access
import db_access3
DB = "VarGenAtt_ActPh3"
TABLE = table
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
cmd = "select count(distinct begadr, endadr) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
PASSCOUNT = res[0]
print "%s hat %d variierte Stellen." % (TABLE, PASSCOUNT)
cmd = "select count(distinct ms) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
HSNRCOUNT = res[0]
print "%s hat %d Handschriften." % (TABLE, HSNRCOUNT)
cmd = "select count(*) from %s.%s" % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
SUM = res[0]
if SUM == PASSCOUNT * HSNRCOUNT:
print "magisches Produkt stimmt"
else:
cmd = "select distinct ms from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
hsnrs = cursor.fetchall()
for hsnr in hsnrs:
cmd = "select count(ms) from %s.%s where ms = %d " % (DB, TABLE,
hsnr[0])
cursor.execute(cmd)
c = cursor.fetchone()
count = int(c[0])
if count != PASSCOUNT:
print hsnr
if count != PASSCOUNT:
# Jede Handschrift darf es pro variierter Stelle nur einmal geben
cmd = "select distinct begadr, endadr from %s.%s " % (
DB, TABLE)
cursor.execute(cmd)
passages = cursor.fetchall()
for passage in passages:
cmd = "select varid, witn from %s.%s " % (DB, TABLE)
cmd += "where begadr = %d and endadr = %d and ms = %d " % (
passage[0], passage[1], hsnr[0])
cursor.execute(cmd)
res = cursor.fetchall()
if len(res) > 1:
print "\tBei %d/%d ist der Zeuge mehrfach verzeichnet. Vgl. ID:" % (
passage[0], passage[1])
print "\t", res
else:
print "\t%s" % (count)
break
cursor.close()
dba.close()
def main_2(db, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen erschließen
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
cmd = "show tables from `%s` like 'Acts%s%%'; " % (db, sChapter)
cursor.execute(cmd)
tables = cursor.fetchall()
for table in tables:
cmd = "select korr, lekt, id from %s.%s " % (db, table[0])
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
korr = row[0]
lekt = row[1]
id = row[2]
if korr is not None:
if korr.find("L") >= 0:
cmd = "update %s.%s " % (db, table[0])
cmd += "set lekt = '%s' where id = %d " % (korr, id)
printer(cmd)
cursor.execute(cmd)
cmd = "update %s.%s " % (db, table[0])
cmd += "set korr = '' where id = %d " % (id)
printer(cmd)
cursor.execute(cmd)
if lekt is not None:
pos = lekt.find("C")
if pos >= 0:
pass
else:
pos = lekt.find("*")
if pos >= 0:
cmd = "update %s.%s " % (db, table[0])
cmd += "set korr = '%s' where id = %d " % (lekt, id)
printer(cmd)
cursor.execute(cmd)
cmd = "update %s.%s " % (db, table[0])
cmd += "set lekt = '' where id = %d " % (id)
printer(cmd)
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main_4(source, target, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Datenbank auswählen
cmd = "use %s" % (source)
cursor.execute(cmd)
dba.commit()
# Anfang des Tabellennamens generieren
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
tablename = "Acts" + sChapter
# Tabellen erfragen
cmd = "show tables like '%s%%'" % (tablename)
cursor.execute(cmd)
tables = cursor.fetchall()
for t in tables:
table = t[0]
# Ausschneiden der Buch- und Kapitelbezeichnung
name = tablename[0:6]
# Neuen Namen zusammensetzen vgl. unten
name += "att"
if table.endswith("lac"):
name += "Lac"
if target.endswith("2"):
name += "_2"
if target.endswith("3"):
name += "_3"
# Evtl. vorhandene Tabelle loeschen
cmd = "drop table if exists `%s`.`%s` " % (target, name)
printer(cmd)
cursor.execute(cmd)
# Tabellen anlegen mit 'create table like ...'
cmd = "create table `%s`.`%s` " % (target, name)
cmd += "like `%s`.`%s` " % (source, table)
printer(cmd)
cursor.execute(cmd)
# Befuellen mit 'insert into ... select * from ...'
cmd = "insert into `%s`.`%s` " % (target, name)
cmd += "select * from `%s`.`%s` " % (source, table)
printer(cmd)
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main_7(database, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen aus chapter generieren
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
table = "Acts" + sChapter + "att"
if database.endswith("2"):
table += "_2"
if database.endswith("3"):
table += "_3"
# Zu loeschende Zeichen definieren
pattern = "fo_1234567890/"
# Alle 'zw'-Lesarten auflisten
cmd = "select labezsuf, anfadr, endadr from `%s`.`%s` " % (database, table)
cmd += "where labez like 'zw' "
# Fälle wie "zw e/f" müssen aber stehen bleiben, z.B. Acta 4,2/20-34
# alternativ könnte man die beiden folgenden Zeilen streichen,
# und in der Schleife ein continue setzen, wenn ein RegEx zutrifft.
cmd += "and labezsuf not like 'f%%' "
cmd += "and labezsuf not like '%%/f%%' "
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
labezsuf = row[0]
anfadr = row[1]
endadr = row[2]
s = mystrip(labezsuf, pattern)
# Labez einheitlich immer gleich?
b = matches(s[0], s)
# Wenn wahr, dann diesen Datensatz updaten
if b:
cmd = "update `%s`.`%s` " % (database, table)
cmd += "set labez = '%s' " % (s[0])
cmd += "where labez like 'zw' "
cmd += "and labezsuf like '%s' " % (labezsuf)
cmd += "and anfadr = %s and endadr = %s " % (anfadr, endadr)
printer(cmd)
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main(table):
import db_access3
DB = "ECM_Acts_CBGMPh2"
TABLE = table
dba = db_access3.DBA("remote")
cursor = dba.cursor()
cmd = "select count(distinct anfadr, endadr) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
PASSCOUNT = res[0]
print "%s hat %d variierte Stellen." % (TABLE, PASSCOUNT)
cmd = "select count(distinct hsnr) from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
HSNRCOUNT = res[0]
print "%s hat %d Handschriften." % (TABLE, HSNRCOUNT)
cmd = "select count(*) from %s.%s" % (DB, TABLE)
cursor.execute(cmd)
res = cursor.fetchone()
SUM = res[0]
if SUM == PASSCOUNT * HSNRCOUNT:
print "magisches Produkt stimmt"
else:
cmd = "select distinct hsnr from %s.%s " % (DB, TABLE)
cursor.execute(cmd)
hsnrs = cursor.fetchall()
for hsnr in hsnrs:
cmd = "select count(hsnr) from %s.%s where hsnr = %d " % (
DB, TABLE, hsnr[0])
cursor.execute(cmd)
c = cursor.fetchone()
if int(c[0]) != PASSCOUNT:
print hsnr
if int(c[0]) > PASSCOUNT:
# Jede Handschrift darf es pro variierter Stelle nur einmal geben
passages, void = dba.getPassages(DB, TABLE)
for passage in passages:
cmd = "select id from %s.%s " % (DB, TABLE)
cmd += "where anfadr = %d and endadr = %d and hsnr = %d " % (
passage[0], passage[1], hsnr[0])
cursor.execute(cmd)
res = cursor.fetchall()
if len(res) > 1:
print "\tBei %d/%d ist der Zeuge mehrfach verzeichnet. Vgl. ID:" % (
passage[0], passage[1])
print "\t", res
else:
print "\t%s" % (c[0])
cursor.close()
dba.close()
def __init__(self, _edition=28):
'''
Konstruktor
Die Datenbank enthält den Nestletext der Auflagen 28 und
der gerade entstehenden 29. Auflage (= ECM Acta). Andere
Auflagen können nicht abgefragt werden.
'''
#import MySQLdb
self.fehlverse = Fehlverse.Fehlvers()
dba = db_access3.DBA("remote")
self.cursor = dba.cursor()
self.edition = _edition
if self.edition < 28 or self.edition > 29:
print "Selected edition is not available!"
print "Using edition 28 instead."
self.edition = 28
def main_6b(database, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen aus chapter generieren
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
table = "Acts" + sChapter + "att"
if database.endswith("2"):
table += "_2"
if database.endswith("3"):
table += "_3"
# Alle in Frage kommenden Stellen auflisten
cmd = "select anfadr, endadr, HS from `%s`.`%s` " % (database, table)
cmd += "where HS like '%%V%%' or HS like '%%*%%' or HS like '%%C%%' "
cursor.execute(cmd)
rows = cursor.fetchall()
for r in rows:
anf = r[0]
end = r[1]
wit = r[2]
# 'V', 'C' oder '*' aus der Handschriftenbezeichnung entfernen
wit_new = killVStarC(wit)
# Update der Datenbanktabelle
cmd = "update `%s`.`%s` " % (database, table)
cmd += "set HS = '%s' " % (wit_new)
cmd += "where anfadr = %d and endadr = %d " % (anf, end)
cmd += "and HS = '%s' " % (wit)
printer(cmd) # Kontrollausgabe
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main(database, table, mode="remote"):
import db_access3
dba = db_access3.DBA(mode)
cursor = dba.cursor()
# get the passages
cmd = "select distinct begadr, endadr from `%s`.`%s` " % (database, table)
cursor.execute(cmd)
rows = cursor.fetchall()
for r in rows:
anfadr = r[0]
endadr = r[1]
# get the readings and their sources
cmd = "select varid, varnew from `%s`.`%s` " % (database, table)
cmd += "where begadr = %d and endadr = %d " % (anfadr, endadr)
cmd += "and (varnew like '%%1' or "
cmd += "varnew like '%%2' or "
cmd += "varnew like '%%3' or "
cmd += "varnew like '%%4' or "
cmd += "varnew like '%%5' or "
cmd += "varnew like '%%6' or "
cmd += "varnew like '%%7' or "
cmd += "varnew like '%%8' or "
cmd += "varnew like '%%9') "
cursor.execute(cmd)
result = cursor.fetchall()
for n in result:
varid = n[0]
varnew = n[1]
# are there any readings having varid instead of varnew as S1 or S2?
cmd = "select distinct varid, varnew, s1, s2 from `%s`.`%s` " % (
database, table)
cmd += "where begadr = %d and endadr = %d " % (anfadr, endadr)
cmd += "and (s1 = '%s' or s2 = '%s') " % (varid, varid)
cmd += "and varid not like 'z%%' "
cursor.execute(cmd)
mistakes = cursor.fetchall()
for m in mistakes:
print "%d/%d:%s has the wrong source(s): %s" % (anfadr, endadr,
varnew, m[2])
cursor.close()
dba.close()
def main_1b(database, chapter=0, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
if chapter == 0:
# 1.1. Anzeige aller Tabellen in der Quelldatenbank
cmd = "show tables from `%s` like 'Acts%%'; " % (database)
else:
# 1.1. Anzeige der (beiden) Tabellen eines Kapitels
if int(chapter) < 10:
sChapter = "0" + str(chapter)
else:
sChapter = str(chapter)
cmd = "show tables from `%s` like 'Acts%s%%'; " % (database, sChapter)
printer(cmd)
cursor.execute(cmd)
tables = cursor.fetchall()
for table in tables:
# Lückenliste überspringen
if table[0].endswith("lac"):
continue
# 1.2. Datensätze mit Handschriftennummer > 500000 löschen
cmd = "delete from `%s`.`%s` " % (database, table[0])
cmd += "where `hsnr` >= 500000 "
printer(cmd)
cursor.execute(cmd)
dba.commit()
cursor.close()
dba.close()
def main_6(database, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Tabellennamen aus chapter generieren
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
table = "Acts" + sChapter + "att"
if database.endswith("2"):
table += "_2"
if database.endswith("3"):
table += "_3"
# Zuerst die einfachen Faelle:
cmd = "delete from `%s`.`%s` " % (database, table)
cmd += "where (lekt = 'L2' or korr in ('C', 'C1', 'C2', 'C3', 'A', 'K')) "
cmd += "and suffix2 <> '%C*%' "
printer(cmd)
cursor.execute(cmd)
# Nachbesserung notwendig:
cmd = "delete from `%s`.`%s` " % (database, table)
cmd += "where suffix2 like '%L2%' or suffix2 like '%A%' "
cmd += "or suffix2 like '%K%' "
printer(cmd)
cursor.execute(cmd)
# Sonderfall Selbstkorrektur: C*
cmd = "select anfadr, endadr, hsnr from `%s`.`%s` " % (database, table)
cmd += "where suffix2 like '%%C*' "
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
anfadr = row[0]
endadr = row[1]
hsnr = row[2]
cmd = "delete from `%s`.`%s` " % (database, table)
cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr)
cmd += "and hsnr = %d " % (hsnr)
cmd += "and suffix2 like '%%*%%' and suffix2 not like '%%C*' "
printer(cmd)
cursor.execute(cmd)
dba.commit()
# Eintraege loeschen, die nun keine Varianten mehr haben
passages, void = dba.getPassages(database, table)
for p in passages:
anfadr = p[0]
endadr = p[1]
cmd = "select count(distinct labez) "
cmd += "from `%s`.`%s` " % (database, table)
cmd += "where anfadr = %d and endadr = %d " % (anfadr, endadr)
cmd += "and labez not like 'a' and labez not like 'z%%'"
cursor.execute(cmd)
res = cursor.fetchone()
if res[0] == 0:
cmd = "delete from `%s`.`%s` " % (database, table)
cmd += "where anfadr = %d and endadr = %d " % (anfadr, endadr)
printer(cmd)
cursor.execute(cmd)
dba.commit()
# Eintraege - Ende
cursor.close()
dba.close()
def main(mode="remote"):
# static variables
PHASE1 = "`ECM_ActsPh2`.`Acts15GVZ`"
PHASE2 = "`ECM_ActsPh4`.`Acts15GVZ`"
# configure database connection
import db_access3, Address
from NestleAland import getBookName
conn = db_access3.DBA(mode)
cursor = conn.cursor()
# print temporary table
def printTable():
cmd = "select anfadr, endadr, labez, labezsuf, reading1 "
cmd += "from `ECM_Acts`.`TempTable` order by anfadr asc, endadr desc, labez asc; "
cursor.execute(cmd)
rows = cursor.fetchall()
for r in rows:
anf = r[0]
end = r[1]
lab = r[2]
las = r[3]
rd1 = r[4]
b, bc, bv, bw, ec, ev, ew = Address.decodeAdr(anf, end)
sb = getBookName(b)
s = db_access3.formatAdr(sb, bc, bv, bw, ec, ev, ew)
s1 = s2 = ""
if rd1 != None:
s1 = "\n\t>" + rd1.decode('utf8') + "< "
print "%s%s%s%s " % (s, lab, las, s1)
# file to generate sql statements for patristic citation database
fd = open("deleatur.sql", "w")
# create temporary table
cmd = "drop table if exists `ECM_Acts`.`TempTable`; "
cursor.execute(cmd)
cmd = "CREATE TABLE `ECM_Acts`.`TempTable` ("
cmd += "`anfadr` int(11) NOT NULL, "
cmd += "`endadr` int(11) NOT NULL, "
cmd += "`labez` varchar(8) NOT NULL, "
cmd += "`labezsuf` varchar(16) default '', "
cmd += "`reading1` varchar(1024) character set utf8 collate utf8_bin default NULL"
cmd += ") ENGINE=MyISAM DEFAULT CHARSET=latin1"
cursor.execute(cmd)
print "old:"
cmd = "select distinct anfadr, endadr, labez, labezsuf from %s order by anfadr, endadr desc; " % (
PHASE1)
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
anf = row[0]
end = row[1]
lab = row[2]
las = row[3]
cmd = "select count(*) from %s " % (PHASE2)
cmd += "where anfadr = %s and endadr = %s " % (anf, end)
cmd += "and labez = '%s' and labezsuf = '%s' " % (lab, las)
cursor.execute(cmd)
res = cursor.fetchone()
if res[0] == 0:
cmd = "insert into `ECM_Acts`.`TempTable` "
cmd += "(anfadr, endadr, labez, labezsuf) values "
cmd += "(%d, %d, '%s', '%s'); " % (anf, end, lab, las)
cursor.execute(cmd)
# generate sql file
s = "delete from `Apparat_Zitate`.`Cit2Reading` "
s += "where anfadr = %d and endadr = %d " % (anf, end)
s += "and labez = '%s' and labezsuf = '%s'; \n" % (lab, las)
fd.write(s)
cmd = "select distinct a.anfadr, a.endadr, a.labez, a.labezsuf, a.lesart from %s a, %s b " % (
PHASE1, PHASE2)
cmd += "where a.anfadr = b.anfadr and a.endadr = b.endadr "
cmd += "and a.labez = b.labez and a.labezsuf = b.labezsuf "
cmd += "and a.lesart <> b.lesart "
cmd += "order by b.anfadr, b.endadr desc, b.labez; "
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
anf = row[0]
end = row[1]
lab = row[2]
las = row[3]
try:
rdg = row[4].decode('utf8')
except:
rdg = ""
# check if already entered in the table
# cmd = "select count(*) from `ECM_Acts`.`TempTable` "
# cmd += "where anfadr = %s and endadr = %s " % (anf, end)
# cmd += "and labez = '%s' and labezsuf = '%s'; " % (lab, las)
# count = cursor.execute(cmd)
# if count == 0:
cmd = "insert into `ECM_Acts`.`TempTable` "
cmd += "(anfadr, endadr, labez, labezsuf, reading1) values "
cmd += "(%d, %d, '%s', '%s', '%s'); " % (anf, end, lab, las, rdg)
cursor.execute(cmd)
printTable()
# truncate temp table
cmd = "truncate `ECM_Acts`.`TempTable`; "
cursor.execute(cmd)
print ""
print "new:"
cmd = "select distinct anfadr, endadr, labez, labezsuf from %s order by anfadr, endadr desc; " % (
PHASE2)
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
anf = row[0]
end = row[1]
lab = row[2]
las = row[3]
cmd = "select count(*) from %s " % (PHASE1)
cmd += "where anfadr = %s and endadr = %s " % (anf, end)
cmd += "and labez = '%s' and labezsuf = '%s' " % (lab, las)
cursor.execute(cmd)
res = cursor.fetchone()
if res[0] == 0:
cmd = "insert into `ECM_Acts`.`TempTable` "
cmd += "(anfadr, endadr, labez, labezsuf) values "
cmd += "(%d, %d, '%s', '%s'); " % (anf, end, lab, las)
cursor.execute(cmd)
cmd = "select distinct b.anfadr, b.endadr, b.labez, b.labezsuf, b.lesart from %s a, %s b " % (
PHASE1, PHASE2)
cmd += "where a.anfadr = b.anfadr and a.endadr = b.endadr "
cmd += "and a.labez = b.labez and a.labezsuf = b.labezsuf "
cmd += "and a.lesart <> b.lesart "
cmd += "order by b.anfadr, b.endadr desc, b.labez; "
cursor.execute(cmd)
rows = cursor.fetchall()
for row in rows:
anf = row[0]
end = row[1]
lab = row[2]
las = row[3]
try:
rdg = row[4].decode('utf8')
except:
rdg = ""
# check if already entered in the table
# cmd = "select count(*) from `ECM_Acts`.`TempTable` "
# cmd += "where anfadr = %s and endadr = %s " % (anf, end)
# cmd += "and labez = '%s' and labezsuf = '%s'; " % (lab, las)
# count = cursor.execute(cmd)
# if count == 0:
cmd = "insert into `ECM_Acts`.`TempTable` "
cmd += "(anfadr, endadr, labez, labezsuf, reading1) values "
cmd += "(%d, %d, '%s', '%s', '%s'); " % (anf, end, lab, las, rdg)
cursor.execute(cmd)
# read temporary table
printTable()
# drop temporary table
cmd = "drop table `ECM_Acts`.`TempTable`; "
cursor.execute(cmd)
# closing database
cursor.close()
conn.close()
# close file handle
fd.close()
def main_9(db1, db2, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Generate name of newtable and lactable
sChapter = str(chapter)
if int(chapter) < 10:
sChapter = "0" + sChapter
newtable = "Acts" + sChapter + "att"
lactable = newtable + "Lac"
if db1.endswith("2"):
newtable += "_2"
lactable += "_2"
if db1.endswith("3"):
newtable += "_3"
lactable += "_3"
# Query systematic lacuna table name
sys_lac_table = getSysLacTable(cursor, db2, sChapter)
# 9.0. Truncate lacuna table
cmd = "truncate %s.%s " % (db1, lactable)
printer(cmd)
cursor.execute(cmd)
dba.commit()
# 9.1.
passages, passcount = dba.getPassages(db1, newtable)
# 9.2.
cmd = "select distinct hs from %s.%s " % (db2, sys_lac_table)
cmd += "order by hsnr "
printer(cmd)
cursor.execute(cmd)
mss = cursor.fetchall()
# get max endadr
cmd = "select max(endadr) from `%s`.`%s` " % (db2, sys_lac_table)
cursor.execute(cmd)
result = cursor.fetchone()
max_endadr = result[0]
# 9.3.
for ms in mss:
hs = ms[0]
# 9.4.
for passage in passages:
anfadr = passage[0]
endadr = passage[1]
cmd = "select count(id) from %s.%s " % (db2, sys_lac_table)
if endadr < max_endadr:
cmd += "where anfadr <= %d and endadr >= %d " % (anfadr,
endadr)
else:
cmd += "where anfadr <= %d and endadr >= %d " % (anfadr,
endadr - 1)
cmd += "and hs = '%s' " % (hs)
printer(cmd)
cursor.execute(cmd)
result = cursor.fetchone()
rescount = result[0]
# 9.5.
if rescount > 0:
enter2LocalLacList(cursor, hs, db1, lactable, anfadr, endadr)
dba.commit() # it's an InnoDB table
cursor.close()
dba.close()
def main_5b(database, chapter, verbose=False):
def printer(s):
"""
Print information if needed.
"""
if verbose:
print s
import access
import db_access3
# Open handles
dba = db_access3.DBA(access.get())
cursor = dba.cursor()
# Choose database
cmd = "USE %s;" % (database)
cursor.execute(cmd)
dba.commit()
table = getTableName(cursor, database, chapter)
# Select T+number witnesses ordered by addresses
cmd = "SELECT DISTINCT `ANFADR`, `ENDADR` FROM `%s`.`%s` " % (database,
table)
cmd += "WHERE `HS` LIKE '%%T%%' "
cmd += "ORDER BY `ANFADR`, `ENDADR` DESC;"
cursor.execute(cmd)
addresses = cursor.fetchall()
for adr in addresses:
anf = adr[0]
end = adr[1]
cmd = "SELECT DISTINCT `HSNR` FROM `%s`.`%s` " % (database, table)
cmd += "WHERE `ANFADR` = %s AND `ENDADR` = %s " % (anf, end)
cmd += "AND `HS` LIKE '%%T%%';"
cursor.execute(cmd)
hss = cursor.fetchall()
for hsnr in hss:
# three lists to store information:
del_ids = []
labez_s = []
labezsuf_s = []
cmd = "SELECT `ID`, `HS`, `LABEZ`, `ANFADR`, `ENDADR`, `BUCH`, "
cmd += "`KAPANF`, `VERSANF`, `WORTANF`, `KAPEND`, `VERSEND`, `WORTEND`, "
cmd += "`HSNR`, `LABEZSUF` "
cmd += "FROM `%s`.`%s` " % (database, table)
cmd += "WHERE `HS` LIKE '%%T%%' "
cmd += "AND `ANFADR` = %s AND `ENDADR` = %s " % (anf, end)
cmd += "AND `HSNR` = %s " % (hsnr)
cmd += "ORDER BY `LABEZ`, `LABEZSUF`;"
count = cursor.execute(cmd)
if count == 1:
# the counterpart seems to be a lacuna: treat this entry as
# normal witness and delete the T notation
row = cursor.fetchone()
ident = row[0]
hs = row[1]
pos = hs.find("T")
hs = hs[:len(hs) - 2] # chop off T1 etc.
cmd = "UPDATE `%s`.`%s` " % (database, table)
cmd += "SET HS = '%s' " % (hs)
cmd += "WHERE id = %s;" % (ident)
print cmd
# cursor.execute(cmd)
else: # count > 1
rows = cursor.fetchall()
for row in rows:
del_ids.append(row[0])
labez_s.append(row[2])
labezsuf_s.append(row[13])
book = row[5]
bc = row[6]
bv = row[7]
bw = row[8]
ec = row[9]
ev = row[10]
ew = row[11]
anf = row[3]
end = row[4]
labez = row[2]
hs = row[1]
hsnr = row[12]
pos = hs.find("T")
hs = hs[:pos]
# Delete these witnesses
for i in del_ids:
cmd = "DELETE FROM `%s`.`%s` " % (database, table)
cmd += "WHERE `ID` = %d;" % (i)
print cmd
# cursor.execute(cmd)
# Insert a new zw reading
suffix2 = ""
for n in range(len(del_ids)):
l1 = labez_s[n]
l2 = labezsuf_s[n]
suffix2 = suffix2 + l1
if len(l2) > 0:
suffix2 = suffix2 + "_" + l2
suffix2 = suffix2 + "/"
suffix2 = suffix2[:len(suffix2) - 1] # chop off last slash
cmd = "INSERT INTO `%s`.`%s` " % (database, table)
cmd += "(BUCH, KAPANF, VERSANF, WORTANF, KAPEND, VERSEND, WORTEND, "
cmd += "ANFADR, ENDADR, LABEZ, LABEZSUF, HS, HSNR) VALUES ("
cmd += "%d, %d, %d, %d, %d, %d, %d, " % (book, bc, bv, bw, ec,
ev, ew)
cmd += "%d, %d, 'zw', '%s', '%s', %d" % (anf, end, suffix2, hs,
hsnr)
cmd += ");"
print cmd
# cursor.execute(cmd)
# Inno-DB tables need an explicit commit statement
dba.commit()
# Close handles
cursor.close()
dba.close()
