#!/usr/bin/env python3 """ birchdocdbkit.py - add, delete or change objects in a birchdocdb database Description - client designed to be run by BioLegato to make changes in the database. After (or maybe before?) changes are done, pops up an HTML page in a minimal browser, showing the revised documentation page. (Is there a straightforward way to highlight the changes?). In most cases adding deleting or changing may require changes in more than one SQL table, especially those that link two tables. birchdockit.py uses the Python sqlite3 module for database transactions. Synopsis: birchdocdbkit.py --add [program|package|file] filename databasefile --commit birchdocdbkit.py --delete [program|package|file] name databasefile --commit birchdocdbkit.py --change [program|package|file] filename databasefile --commit @modified: August 19, 2025 @author: Brian Fristensky @contact: brian.fristensky@umanitoba.ca """ import argparse import os import re import shutil import sqlite3 import sys blib = os.environ.get("BIRCHLIB") sys.path.append(blib) from birchlib import Birchmod from birchlib import Htmlutils from birchlib import HTMLWriter PROGRAM = "birchdocdbkit.py: " USAGE = "\n\t USAGE: birchdocdbkit.py --add|--delete|--change [program|package|file] [filename] databasefile [--commit]" BM = Birchmod(PROGRAM, USAGE) PROGS = [] CATDICT = {} PROGDICT = {} PKGDICT = {} DOCDICT = {} HT = Htmlutils(CATDICT, PROGDICT) PLATFORMS = {"L":"linux-x86_64","l":"linux-arm64","M":"osx-x86_64","m":"macos-arm64"} COMTYPE = ["command","interactive","gui","birch","birchadmin","bldna", "blnalign","blprotein","blpalign","bldata","blmarker","blnfetch", "blpfetch","blreads","blncbi","bltable","bltree"] NL = "\n" DEBUG = True # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class Parameters: "Wrapper class for global parameters" def __init__(self): """ Get the location of the BIRCH home directory, BIRCHPATH Preferrably, we get it from the BIRCH.properties file, but if that isn't found, we get it from the BIRCH environment variable. The one catch is that since we don't already know the location of BIRCHPATH, the former will only work if the current working directory is install-scripts. Normally, this would only be used when installing or updating BIRCH Otherwise, the environment variable will be read. """ FN = '../local/admin/BIRCH.properties' if os.path.exists(FN): print("birchdocdbkit.py: Reading $BIRCH from BIRCH.properties") self.BIRCHPATH = "" self.BIRCH_PLATFORM = "" FILE = open(FN, 'r') for LINE in FILE: TOKENS = LINE.split("=") if TOKENS[0] == 'BirchProps.homedir': self.BIRCHPATH = TOKENS[1].strip() if TOKENS[0] == 'BirchProps.platform': self.BIRCH_PLATFORM = TOKENS[1].strip() FILE.close() else: print("birchdocdbkit.py: Reading $BIRCH and $BIRCH_PLATFORM environment variables.") self.BIRCHPATH = os.environ['BIRCH'] # If BIRCH_PLATFORM is set in the environment, we want that # to supersede the value in BIRCH.properties plat = "" try: plat = os.environ['BIRCH_PLATFORM'] except: pass if plat != "": self.BIRCH_PLATFORM = plat # Everything else depends on BIRCHPATH print("birchdocdbkit.py: BIRCHPATH set to: " + self.BIRCHPATH) print("birchdocdbkit.py: BIRCH_PLATFORM set to: " + self.BIRCH_PLATFORM) self.BIRCHWEBPATH = self.BIRCHPATH + '/public_html' self.BIRCHACEDBPATH = self.BIRCHPATH + '/public_html/birchdb' self.BIRCHLOCALACEDBPATH = self.BIRCHPATH + '/local/public_html/birchdb' self.BIRCHDOCDBPATH = self.BIRCHPATH + '/public_html/birchdocdb' self.BIRCHLOCALDOCDBPATH = self.BIRCHPATH + '/local/public_html/birchdocdb' # After all that, now we get the command line arguments self.ACTION = "" self.CLASS = "" self.COMMIT = False self.NAME = "" self.IFN = "" self.DBFN = "" self.read_args() def read_args(self): """ Read command line arguments into a Parameter object """ parser = argparse.ArgumentParser() parser.add_argument("--add", dest="add", action="store", default="", help="add an object to the database") parser.add_argument("--delete", dest="delete", action="store", default="", help="delete an object from the database") parser.add_argument("--change", dest="change", action="store", default="", help="change an object in the database") parser.add_argument("--commit", dest="commit", action="store_true", default=False, help="commit the change in the database") parser.add_argument("remainder", nargs="*", action="store", default=[], help="remaining command line arguments") args = parser.parse_args() if args.add != "" : self.ACTION = "add" self.CLASS = args.add elif args.delete != "" : self.ACTION = "delete" self.CLASS = args.delete elif args.change != "" : self.ACTION = "change" self.CLASS = args.change else : print(USAGE) exit() self.COMMIT = args.commit print(args.remainder) if self.ACTION == "add" : self.IFN = args.remainder[0] self.DBFN = args.remainder[1] elif self.ACTION == "delete" : self.NAME = args.remainder[0] self.DBFN = args.remainder[1] else : self.IFN = args.remainder[0] self.DBFN = args.remainder[1] if DEBUG : print("ACTION: " + self.ACTION) print("CLASS: " + self.CLASS) print("COMMIT: " + str(self.COMMIT)) print("IFN: " + self.IFN) print("DBFN: " + self.DBFN) print("NAME: " + self.NAME) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def Quote(S,C): """ Enclose string S in quotes using C as the quote character. """ return C + S + C # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def GetStr(LINE): """ Read the first string delimited by double quotes @param LINE: The line to be cleaned up @type LINE: str Each quoted line is modified as follows: escape characters \\ added by ACEDB are deleted '>' and '<' characters are changed to '>' and '<' so they are not accidentally recognized by HTML as tag delimiters. """ # Change > and < characters to HTML > and < LINE = LINE.replace('>', '>') LINE = LINE.replace('<', '<') # replace single quote characters with HTML apostrophe code LINE = LINE.replace("\'", ''') return LINE # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def AddToList(L, NAME): """ If a list contains NAME, do nothing If it does not exist, append it to the list @param L: The list to check for name @type L: list @param NAME: The name to be checked for @type NAME: str """ if NAME in L: pass else: L.append(NAME) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class Category: def __init__(self): """ Initializes arguments: name="" program=[] pkg=[] """ self.name = "" self.program = [] self.pkg = [] # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def ReadCategory(FN, CATS): """ Read category.ace into a list of category objects @param FN: THe name of the file to be read @type FN: str @param CATS: The categories object to be read into @type CATS: list """ # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def AssignCategory(NAME): """ "If a category called NAME already exists, return a pointer" "If it does not exist, create a new category object and return a pointer" @param NAME: The name of the category to check for @type NAME: str """ if NAME in CATDICT: C = CATDICT[NAME] else: C = Category() CATS.append(C) C.name = NAME return C if os.path.exists(FN): FILE = open(FN, 'r') for LINE in FILE: if re.match('Category\\s:\\s".+"', LINE): C = AssignCategory(GetStr(LINE)) elif re.match('Program\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) AddToList(C.program, TOKENS[1]) elif re.match('Package\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) AddToList(C.pkg, TOKENS[1]) FILE.close() # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class Package: def __init__(self): """ Initializes arguments: name="" description="" category=[] doc=[] program=[] data=[] platform=[] installation=[] """ self.name = "" self.description = "" self.category = [] self.doc = [] self.program = [] self.data = [] self.platform = [] self.installation = [] # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def ReadPackage(FN, PKGS): """ Read package.ace into a list of Package objects @param FN: The name of the file to be read in @type FN: str @param PKGS: The list of package objects to be read into @type PKGS:list """ # - - - - - - - - - - - - - - - - - - - def AssignPackage(NAME): """ If a package called NAME already exists, return a pointer If it does not exist, create a new package object and return a pointer @param NAME:The name to attempt to assign @type NAME:str """ if NAME in PKGDICT: PK = PKGDICT[NAME] else: PK = Package() PKGS.append(PK) PK.name = NAME return PK if os.path.exists(FN): FILE = open(FN, 'r') for LINE in FILE: if re.match('Package\\s:\\s".+"', LINE): PK = AssignPackage(GetStr(LINE)) elif re.match('Description\\s+".+"', LINE): PK.description = GetStr(LINE) elif re.match('Category\\s+".+"', LINE): AddToList(PK.category, GetStr(LINE)) elif re.match('Documentation\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) AddToList(PK.doc, TOKENS[1]) elif re.match('Program\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) AddToList(PK.program, TOKENS[1]) elif re.match('Data\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) AddToList(PK.data, TOKENS[1]) elif re.match('Platform\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) if TOKENS[1] in PLATFORMS : AddToList(PK.platform, TOKENS[1]) elif re.match('BIRCH', LINE): AddToList(PK.installation, "BIRCH") elif re.match('local', LINE): AddToList(PK.installation, "local") #print "Packages read " #for PK in PKGS : #print PK.name FILE.close() #------------------ General SQL methods ----------------------- class Indenter: "Methods indenting output file" def __init__(self): """ Initializes arguments: indentwidth=4 col=0 lpad="" """ self.indentwidth = 4 self.col = 0 # current indentation column self.lpad = "" def indent(self): """ increase indent using identwidth blank spaces """ self.col = self.col + self.indentwidth self.lpad = ' '.rjust(self.col) def undent(self): """ decrease indent using identwidth blank spaces """ self.col = self.col - self.indentwidth if self.col < 0: self.col = 0 self.lpad = "" else: self.lpad = ' '.rjust(self.col) def InsertRow(Cursor,TableName,Cols,Values) : """ Write one or more rows of values to SQLFILE. """ if len(Values) > 0 : COMMAND="INSERT INTO " + TableName + " ( " + Cols[0] for s in Cols[1:] : COMMAND += "," + s COMMAND += ") VALUES (" COMMAND += Values[0] for v in Values[1:] : COMMAND += "," + v COMMAND += ");" print("COMMAND: " + COMMAND) Cursor.execute(COMMAND) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class ChoiceTable: def __init__(self): """ Class for single-column tables used to limit possible choices in an SQL field (column) """ self.TableName = "" # name of table self.ColHeading = "" # column heading self.Values = [] # list of values def WriteChoiceTable (self,SQLFILE,TableName, ColName, keylist) : """ Write Category table to SQLFILE FIXME @param catfn: @type catfn: """ SQLFILE.write(NL) Ind = Indenter() # for SQLFILE, the current state of indentation. SQLFILE.write("-- " + TableName + " -------------------------" + NL) SQLFILE.write("CREATE TABLE IF NOT EXISTS " + TableName + " (" + NL) SQLFILE.write(" " + ColName + " TEXT PRIMARY KEY" + NL) SQLFILE.write(");" + NL) SQLFILE.write(NL) if len(keylist) > 0 : SQLFILE.write("INSERT INTO " + TableName + " ( ") SQLFILE.write(ColName) SQLFILE.write(" )" + NL) Ind.indent() SQLFILE.write(Ind.lpad + "VALUES" + NL) Ind.indent() lastline = len(keylist) -1 linecount = 0 for key in keylist: Values = [key] InsertRow(SQLFILE,Ind,Values) if linecount != lastline : SQLFILE.write("," + NL) linecount += 1 SQLFILE.write(";" + NL) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class LinkTable: def __init__(self): """ Class for creating 2-way links in SQL. SQL has no direct way to specify one-to-many or many-to-many fields. The hack is that you have to create a table that references two tables. Initializes arguments: LCol="" RCol="" """ self.tablename = "" # name of table in SQL self.LCol = "" # left column heading self.LFK = "" # left reference key self.LFKCol = "" # left reference column name self.RCol = "" # right column heading self.RFK = "" # right reference key self.RFKCol = "" # right reference column name self.linktuples = [] # list of values for left and right columns def addtuple(self,tuple) : self.linktuples.append(tuple) def WriteLinkTable (self,SQLFILE) : """ Write link table to SQLFILE """ SQLFILE.write(NL) Ind = Indenter() # for SQLFILE, the current state of indentation. SQLFILE.write("-- " + self.tablename + " -------------------------" + NL) SQLFILE.write("CREATE TABLE IF NOT EXISTS " + self.tablename + " (" + NL) #SQLFILE.write(" " + self.LCol + " TEXT REFERENCES " + self.LFK + " NOT NULL, " + NL) #SQLFILE.write(" " + self.RCol + " TEXT REFERENCES " + self.RFK + " NOT NULL" + NL) SQLFILE.write(" " + self.LCol + " TEXT NOT NULL," + NL) SQLFILE.write(" " + self.RCol + " TEXT NOT NULL ," + NL) SQLFILE.write(" FOREIGN KEY (" + self.LCol + ") REFERENCES " + self.LFK + " (" + self.LFKCol + ") ON UPDATE CASCADE ON DELETE CASCADE ," + NL) SQLFILE.write(" FOREIGN KEY (" + self.RCol + ") REFERENCES " + self.RFK + " (" + self.RFKCol + ") ON UPDATE CASCADE ON DELETE CASCADE " + NL) SQLFILE.write(");" + NL) SQLFILE.write(NL) if len(self.linktuples) > 0 : SQLFILE.write("INSERT INTO " + self.tablename + " ( ") SQLFILE.write(self.LCol + ", " + self.RCol) SQLFILE.write(" )" + NL) Ind.indent() SQLFILE.write(Ind.lpad + "VALUES" + NL) Ind.indent() lastline = len(self.linktuples) - 1 linecount = 0 for t in self.linktuples: Values = [t[0],t[1]] InsertRow(SQLFILE,Ind,Values) if linecount != lastline : SQLFILE.write("," + NL) linecount += 1 SQLFILE.write(";" + NL) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class Program: def __init__(self): """ Initializes arguments: name="" description="" category=[] interface=[] package="" doc=[] data=[] sampleinput=[] sampleoutput=[] platform=[] """ self.name = "" self.description = "" self.category = [] self.interface = [] self.package = "" self.doc = [] self.data = [] self.sampleinput = [] self.sampleoutput = [] self.platform = [] self.installation = "" # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def ReadProgramData(self,IFN): """ @param IFN: The name of the file to be read in @type IFN: str """ if os.path.exists(IFN): INFILE = open(IFN, 'r') for LINE in INFILE: TOKENS = LINE.strip().split(':', maxsplit=1) FIELD = TOKENS[0] VALUE = TOKENS[1] if FIELD == "Program" : self.name = VALUE elif FIELD == "Description" : self.description = GetStr(VALUE) elif FIELD == "Category" : AddToList(self.category, GetStr(VALUE)) elif FIELD == "Launch" : TOKENS = VALUE.split(':') if TOKENS[0] in COMTYPE : AddToList(self.interface, [TOKENS[0],TOKENS[1]]) elif FIELD == "Package" : self.package = GetStr(VALUE) elif FIELD == "Documentation" : AddToList(self.doc,GetStr(VALUE)) elif FIELD == "Data" : AddToList(self.data,GetStr(VALUE)) elif FIELD == "SampleInput" : AddToList(self.sampleinput,GetStr(VALUE)) elif FIELD == "SampleOutput" : AddToList(self.sampleinput,GetStr(VALUE)) elif FIELD == "Platforms" : TOKENS = VALUE.split(':') for t in TOKENS : if t in PLATFORMS : AddToList(self.platform,PLATFORMS[t]) elif FIELD == "Installation" : self.installation = GetStr(VALUE) INFILE.close() # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def WriteDBProgram(self,Prog,Cursor): """ @param IFN: The name of the file to be read in @type IFN: str """ result = Cursor.execute("SELECT Name from Program WHERE Name='" + Prog.name + "';") if len(result.fetchall()) > 0 : print("birchdocdbkit.py: Program " + Prog.name + " exists. Aborting.") exit() else : ColNames = ["Name","Description","Installation"] InsertRow(Cursor,"Program",ColNames,[Quote(Prog.name,"'"),Quote(Prog.description,"'"), Quote(Prog.installation,"'")]) ColNames = ["Program","Category"] for C in Prog.category : InsertRow(Cursor,"ProgCat",ColNames,[Quote(Prog.name,"'"),Quote(C,"'")]) ColNames = ["Program","ComType","Command"] for I in Prog.interface : InsertRow(Cursor,"ProgLaunch",ColNames,[Quote(Prog.name,"'"),Quote(I[0],"'"), Quote(I[1],"'")]) ColNames = ["Package","Program"] InsertRow(Cursor,"PkgProg",ColNames,[Quote(Prog.package,"'"),Quote(Prog.name,"'")]) ColNames = ["Program","Documentation"] for D in Prog.doc : InsertRow(Cursor,"ProgDoc",ColNames,[Quote(Prog.name,"'"),Quote(D,"'")]) ColNames = ["Program","Data"] for D in Prog.data : InsertRow(Cursor,"ProgDat",ColNames,[Quote(Prog.name,"'"),Quote(D,"'")]) ColNames = ["Program","SampleInput"] for S in Prog.sampleinput : InsertRow(Cursor,"ProgSampleInp",ColNames,[Quote(Prog.name,"'"),Quote(D,"'")]) ColNames = ["Program","SampleOutput"] for S in Prog.sampleoutput : InsertRow(Cursor,"ProgSampleOut",ColNames,[Quote(Prog.name,"'"),Quote(D,"'")]) ColNames = ["Program","Platform"] for P in Prog.platform : InsertRow(Cursor,"ProgPlat",ColNames,[Quote(Prog.name,"'"),Quote(P,"'")]) # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - class File: def __init__(self): """ Initializes arguments: name="" description="" command='\"$ACE_FILE_LAUNCHER\" ' path="" """ self.name = "" self.description = "" #self.command = '\"$ACE_FILE_LAUNCHER\" ' self.command = '$ACE_FILE_LAUNCHER' self.path = "" # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def ReadDocFiles(FN, DOCFILES): """ Read file.ace into a list of file objects @param FN: The name of the file to be read in @type FN: str @param DOCFILES: the list of file objects to be read into @type DOCFILES: list """ # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def AssignFile(NAME): """ If a file called NAME already exists, return a pointer If it does not exist, create a new file object and return a pointer @param NAME: The name to attempt to assign @type NAME: str """ if NAME in DOCDICT: FL = DOCDICT[NAME] else: FL = File() DOCFILES.append(FL) FL.name = NAME return FL if os.path.exists(FN): FILE = open(FN, 'r') for LINE in FILE: if re.match('File\\s:\\s".+"', LINE): FL = AssignFile(GetStr(LINE)) elif re.match('Description\\s+".+"', LINE): FL.description = GetStr(LINE) elif re.match('Pick_me_to_call\\s+".+"', LINE): TOKENS = HT.tokenize(LINE) FL.path = (TOKENS[2]) FILE.close() def write_file_table(SQLFILE): """ FIXME @param catfn: @type catfn: """ Ind = Indenter() # for SQLFILE, the current state of indentation. SQLFILE.write(NL) SQLFILE.write("-- File -------------------------" + NL) SQLFILE.write("CREATE TABLE IF NOT EXISTS File (" + NL) SQLFILE.write(" Name TEXT PRIMARY KEY," + NL) SQLFILE.write(" Description TEXT," + NL) SQLFILE.write(" Action TEXT," + NL) SQLFILE.write(" PathOrURL TEXT" + NL) SQLFILE.write(");" + NL) SQLFILE.write(NL) filelist = list(DOCDICT.keys()) filelist.sort(key=lambda PathToURL: PathToURL.lower()) if len(filelist) > 0 : SQLFILE.write("INSERT INTO File ( ") ColNames = ["Name","Description","Action","PathOrURL"] SQLFILE.write(ColNames[0]) for name in ColNames[1:] : SQLFILE.write(", " + name) SQLFILE.write(" )" + NL) Ind.indent() SQLFILE.write(Ind.lpad + "VALUES" + NL) Ind.indent() lastline = len(filelist) -1 linecount = 0 for filekey in filelist: Values = [DOCDICT[filekey].name,DOCDICT[filekey].description,DOCDICT[filekey].command,DOCDICT[filekey].path] print(Values) InsertRow(SQLFILE,Ind,Values) if linecount != lastline : SQLFILE.write("," + NL) linecount +=1 SQLFILE.write(";" + NL) Ind.undent() Ind.undent() # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - def WritePackageTable(SQLFILE): """ Write Package data to the Package table """ Ind = Indenter() # for SQLFILE, the current state of indentation. SQLFILE.write(NL) SQLFILE.write("-- Package -------------------------" + NL) SQLFILE.write("CREATE TABLE IF NOT EXISTS Package (" + NL) SQLFILE.write(" Name TEXT PRIMARY KEY," + NL) SQLFILE.write(" Description TEXT," + NL) SQLFILE.write(" Installation TEXT" + NL) SQLFILE.write(");" + NL) SQLFILE.write(NL) pkglist = list(PKGDICT.keys()) pkglist.sort(key=lambda name : name.lower()) if len(pkglist) > 0 : SQLFILE.write("INSERT INTO Package ( ") ColNames = ["Name","Description","Installation"] SQLFILE.write(ColNames[0]) for name in ColNames[1:] : SQLFILE.write(", " + name) SQLFILE.write(" )" + NL) Ind.indent() SQLFILE.write(Ind.lpad + "VALUES" + NL) Ind.indent() lastline = len(pkglist) -1 linecount = 0 for pkgkey in pkglist: if len(PKGDICT[pkgkey].installation) == 0 : Values = [PKGDICT[pkgkey].name,PKGDICT[pkgkey].description,""] else : Values = [PKGDICT[pkgkey].name,PKGDICT[pkgkey].description,PKGDICT[pkgkey].installation[0]] print(Values) InsertRow(SQLFILE,Ind,Values) if linecount != lastline : SQLFILE.write("," + NL) linecount +=1 SQLFILE.write(";" + NL) Ind.undent() Ind.undent() #======================== MAIN PROCEDURE ========================== def main(): """ Called when not in documentation mode. """ print ("***** obtaining parameters") P = Parameters() # Open connections to database Connection = sqlite3.connect(P.DBFN) Cursor = Connection.cursor() if P.ACTION == "add" : if P.CLASS == "program" : Prog = Program() Prog.ReadProgramData(P.IFN) print("Program name: " + Prog.name) print("Description: " + Prog.description) print("Interface: " + str(Prog.interface)) print("Category: " + str(Prog.category)) print("Documentation: " + str(Prog.doc)) print("Package: " + Prog.package) print("Platform: " + str(Prog.platform)) print("Installation: " + Prog.installation) Prog.WriteDBProgram(Prog,Cursor) elif P.CLASS == "package" : pass elif P.CLASS == "file" : pass else : pass elif P.ACTION == "delete" : pass elif P.ACTION == "change" : pass else : exit() Connection.commit() Connection.close() if __name__=="__main__": if (BM.documentor() or "-test" in sys.argv): pass else: main() BM.exit_success()