1#! /usr/bin/env python 2 3""" 4usage: %(progname)s [args] 5""" 6 7 8import os, sys, string, time, getopt 9from log import * 10 11import odb 12import sqlite 13 14import re 15 16# --- these are using for removing nulls from strings 17# --- because sqlite can't handle them 18 19def escape_string(str): 20 def subfn(m): 21 c = m.group(0) 22 return "%%%02X" % ord(c) 23 24 return re.sub("('|\0|%)",subfn,str) 25 26def unescape_string(str): 27 def subfn(m): 28 hexnum = int(m.group(1),16) 29 return "%c" % hexnum 30 return re.sub("%(..)",subfn,str) 31 32class Database(odb.Database): 33 def __init__(self,db, debug=0): 34 odb.Database.__init__(self, db, debug=debug) 35 self.SQLError = sqlite.Error 36 37 def escape(self,str): 38 if str is None: 39 return None 40 elif type(str) == type(""): 41 return string.replace(str,"'","''") 42 elif type(str) == type(1): 43 return str 44 else: 45 raise "unknown column data type: %s" % type(str) 46 47 48 def listTables(self, cursor=None): 49 if cursor is None: cursor = self.defaultCursor() 50 cursor.execute("select name from sqlite_master where type='table'") 51 rows = cursor.fetchall() 52 tables = [] 53 for row in rows: tables.append(row[0]) 54 return tables 55 56 def listIndices(self, cursor=None): 57 if cursor is None: cursor = self.defaultCursor() 58 cursor.execute("select name from sqlite_master where type='index'") 59 rows = cursor.fetchall() 60 tables = [] 61 for row in rows: tables.append(row[0]) 62 return tables 63 64 def listFieldsDict(self, table_name, cursor=None): 65 if cursor is None: cursor = self.defaultCursor() 66 sql = "pragma table_info(%s)" % table_name 67 cursor.execute(sql) 68 rows = cursor.fetchall() 69 70 columns = {} 71 for row in rows: 72 colname = row[1] 73 columns[colname] = row 74 return columns 75 76 def _tableCreateStatement(self, table_name, cursor=None): 77 if cursor is None: cursor = self.defaultCursor() 78 sql = "select sql from sqlite_master where type='table' and name='%s'" % table_name 79 print sql 80 cursor.execute(sql) 81 row = cursor.fetchone() 82 sqlstatement = row[0] 83 return sqlstatement 84 85 86 def alterTableToMatch(self, table): 87 tableName = table.getTableName() 88 tmpTableName = tableName + "_" + str(os.getpid()) 89 90 91 invalidAppCols, invalidDBCols = table.checkTable(warnflag=0) 92 93## if invalidAppCols or invalidDBCols: 94## return 95 96 if not invalidAppCols and not invalidDBCols: 97 return 98 99 100 oldcols = self.listFieldsDict(tableName) 101# tmpcols = oldcols.keys() 102 103 tmpcols = [] 104 newcols = table.getAppColumnList() 105 for colname, coltype, options in newcols: 106 if oldcols.has_key(colname): tmpcols.append(colname) 107 108 tmpcolnames = string.join(tmpcols, ",") 109 110 statements = [] 111 112 sql = "begin transaction" 113 statements.append(sql) 114 115 sql = "create temporary table %s (%s)" % (tmpTableName, tmpcolnames) 116 statements.append(sql) 117 118 sql = "insert into %s select %s from %s" % (tmpTableName, tmpcolnames, tableName) 119 statements.append(sql) 120 121 sql = "drop table %s" % tableName 122 statements.append(sql) 123 124 sql = table._createTableSQL() 125 statements.append(sql) 126 127 sql = "insert into %s(%s) select %s from %s" % (tableName, tmpcolnames, tmpcolnames, tmpTableName) 128 statements.append(sql) 129 130 sql = "drop table %s" % tmpTableName 131 statements.append(sql) 132 133 sql = "commit" 134 statements.append(sql) 135 136 cur = self.defaultCursor() 137 for statement in statements: 138# print statement 139 cur.execute(statement) 140 141 142def test(): 143 pass 144 145def usage(progname): 146 print __doc__ % vars() 147 148def main(argv, stdout, environ): 149 progname = argv[0] 150 optlist, args = getopt.getopt(argv[1:], "", ["help", "test", "debug"]) 151 152 testflag = 0 153 if len(args) == 0: 154 usage(progname) 155 return 156 for (field, val) in optlist: 157 if field == "--help": 158 usage(progname) 159 return 160 elif field == "--debug": 161 debugfull() 162 elif field == "--test": 163 testflag = 1 164 165 if testflag: 166 test() 167 return 168 169 170if __name__ == "__main__": 171 main(sys.argv, sys.stdout, os.environ) 172