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