annotate commons/core/sql/DbSQLite.py @ 6:769e306b7933

Change the repository level.
author yufei-luo
date Fri, 18 Jan 2013 04:54:14 -0500
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
1 import sqlite3
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
2 import os
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
3 import sys
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
4
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
5 #TODO: update...compare with DbMySql.py
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
6 class DbSQLite(object):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
7
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
8 ## Constructor
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
9 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
10 # @param host string db file path
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
11 # @param cfgFileName string configuration file name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
12 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
13 # @note when a parameter is left blank, the constructor is able
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
14 # to set attribute values from environment variable: REPET_HOST,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
15 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
16 def __init__(self, host = ""):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
17 if host != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
18 self.host = host
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
19 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
20 msg = "ERROR: no host specified"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
21 sys.stderr.write( "%s\n" % msg )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
22 sys.exit(1)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
23 # remove open() and cursor from init() use directly outside this class ...
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
24 self.open()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
25 self.cursor = self.db.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
26
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
27 ## Connect to the DbSQLite database
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
28 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
29 # @param verbose integer (default = 0)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
30 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
31 def open( self, verbose = 0, nb = 0 ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
32 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
33 #sqlite.connect(":memory:", check_same_thread = False)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
34 self.db = sqlite3.connect(self.host, check_same_thread= False, isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
35 except sqlite3.Error, e:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
36 if verbose > 0:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
37 print "ERROR %s" % e
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
38 sys.stdout.flush()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
39 return False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
40 return True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
41
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
42 ## Execute a SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
43 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
44 # @param qry string SQL query to execute
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
45 # @param params parameters of SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
46 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
47 def execute( self, qry, params=None ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
48 try :
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
49 if params == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
50 self.cursor.execute( qry )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
51 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
52 self.cursor.execute( qry, params )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
53 except Exception, e:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
54 #TODO Must be test
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
55 try :
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
56 if params == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
57 self.cursor.execute( qry )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
58 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
59 self.cursor.execute( qry, params )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
60 except Exception, e:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
61 print "Erreur : %s" % e
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
62
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
63 ## Retrieve the results of a SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
64 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
65 def fetchall(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
66 return self.cursor.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
67
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
68 ## Record a new table in the 'info_table' table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
69 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
70 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
71 # @param info string information on the table origin
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
72 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
73 def updateInfoTable( self, tableName, info ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
74 if not self.doesTableExist( "info_tables" ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
75 sqlCmd = "CREATE TABLE info_tables ( name varchar(255), file varchar(255) )"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
76 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
77 sqlCmd = 'INSERT INTO info_tables VALUES ("%s","%s")' % (tableName, info)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
78 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
79
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
80 def createTable(self, tableName, dataType, overwrite=False, verbose=0):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
81 if verbose > 0:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
82 print "creating table '%s' from file '%s' of type '%s'..." % (tableName, dataType)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
83 sys.stdout.flush()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
84 if overwrite:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
85 self.dropTable(tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
86 if dataType.lower() in ["job", "jobs"]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
87 self.createJobTable(tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
88 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
89 print "ERROR: unknown type %s" % (dataType)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
90 self.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
91 sys.exit(1)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
92 if verbose > 0:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
93 print "done!"; sys.stdout.flush()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
94
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
95 ## Create a job table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
96 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
97 # @param tablename new table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
98 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
99 def createJobTable( self, tablename ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
100 sqlCmd = "CREATE TABLE %s" % ( tablename )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
101 sqlCmd += " ( jobid INT UNSIGNED"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
102 sqlCmd += ", jobname VARCHAR(255)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
103 sqlCmd += ", groupid VARCHAR(255)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
104 sqlCmd += ", command TEXT"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
105 sqlCmd += ", launcher VARCHAR(1024)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
106 sqlCmd += ", queue VARCHAR(255)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
107 sqlCmd += ", status VARCHAR(255)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
108 sqlCmd += ", time timestamp"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
109 sqlCmd += ", node VARCHAR(255) )"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
110 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
111
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
112 self.updateInfoTable( tablename, "job table" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
113 sqlCmd = "CREATE INDEX igroupid ON " + tablename + " ( groupid )"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
114 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
115
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
116 ## Test if a table exists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
117 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
118 # @param table string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
119 # @return boolean True if the table exists, False otherwise
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
120 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
121 def doesTableExist( self, table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
122 qry = "PRAGMA table_info(%s)" % (table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
123 self.execute( qry )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
124 results = self.cursor.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
125 if results:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
126 return True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
127 return False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
128
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
129 def isEmpty( self, tableName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
130 return self.getSize( tableName ) == 0
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
131
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
132 ## Give the rows number of the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
133 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
134 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
135 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
136 def getSize( self, tableName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
137 qry = "SELECT count(*) FROM %s;" % ( tableName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
138 self.execute( qry )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
139 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
140 return int( res[0][0] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
141
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
142 ## Remove a table if it exists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
143 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
144 # @param table string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
145 # @param verbose integer (default = 0)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
146 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
147 def dropTable( self, table, verbose = 0 ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
148 if self.doesTableExist( table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
149 sqlCmd = "DROP TABLE %s" % ( table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
150 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
151 sqlCmd = 'DELETE FROM info_tables WHERE name = "%s"' % ( table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
152 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
153
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
154 ## Get a list with the fields
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
155 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
156 def getFieldList( self, table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
157 lFields = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
158 sqlCmd = "PRAGMA table_info(%s)" % ( table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
159 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
160 lResults = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
161 for res in lResults:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
162 lFields.append( res[1] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
163 return lFields
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
164
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
165 ## delete this SQLite database session
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
166 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
167 def delete(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
168 os.remove(self.host)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
169
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
170 ## Close the connection
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
171 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
172 def close( self ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
173 self.db.close()