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