Menu
▾
▴
[Pytrainer-svn] SF.net SVN: pytrainer:[396] pytrainer/trunk/pytrainer/lib
|
From: <dg...@us...> - 2009-11-22 20:50:35
|
Revision: 396
http://pytrainer.svn.sourceforge.net/pytrainer/?rev=396&view=rev
Author: dgranda
Date: 2009-11-22 20:50:22 +0000 (Sun, 22 Nov 2009)
Log Message:
-----------
Addig functions to check database
Modified Paths:
--------------
pytrainer/trunk/pytrainer/lib/ddbb.py
pytrainer/trunk/pytrainer/lib/sqliteUtils.py
Modified: pytrainer/trunk/pytrainer/lib/ddbb.py
===================================================================
--- pytrainer/trunk/pytrainer/lib/ddbb.py 2009-11-22 09:18:26 UTC (rev 395)
+++ pytrainer/trunk/pytrainer/lib/ddbb.py 2009-11-22 20:50:22 UTC (rev 396)
@@ -21,6 +21,8 @@
import logging
import traceback
+import commands
+from system import checkConf
class DDBB:
def __init__(self, configuration):
@@ -30,6 +32,10 @@
else:
from sqliteUtils import Sql
+ self.conf = checkConf()
+ self.confdir = self.conf.getValue("confdir")
+ self.ddbb_path = "%s/pytrainer.ddbb" %self.confdir
+
ddbb_host = configuration.getValue("pytraining","prf_ddbbhost")
ddbb = configuration.getValue("pytraining","prf_ddbbname")
ddbb_user = configuration.getValue("pytraining","prf_ddbbuser")
@@ -202,10 +208,11 @@
def checkDBIntegrity(self):
"""17.11.2009 - dgranda
- Retrieves tables and columns from database and adds something if missed. New in version 1.7.0
+ Retrieves tables and columns from database, checks current ones and adds something if missed. New in version 1.7.0
args: none
returns: none"""
logging.debug('>>')
+ logging.info('Checking PyTrainer database')
if self.ddbb_type != "sqlite":
logging.error('Support for MySQL database is decommissioned, please migrate to SQLite. Exiting check')
exit(-2)
@@ -239,28 +246,42 @@
"time":"date",
"name":"varchar(200)",
"sym":"varchar(200)"}
- columns = [columnsRecords,columnsSports,columnsWaypoints]
tablesList = {"records":columnsRecords,"sports":columnsSports,"waypoints":columnsWaypoints}
try:
- tablesDB = self.ddbbObject.select("sqlite_master","name", "type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name")
- logging.debug('Found '+ str(len(tablesDB))+' tables in db: '+ str(tablesDB))
+ tablesDBT = self.ddbbObject.select("sqlite_master","name", "type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name")
except:
logging.error('Not able to retrieve which tables are in DB. Printing traceback')
traceback.print_exc()
exit(-1)
- if len(tablesDB) > len(tablesList):
- logging.info('Database has more tables than expected, please check duplicity!')
- for entry in tablesDB:
- if entry[0] in tablesList:
- logging.debug('Inspecting '+str(entry[0])+' table')
- self.ddbbObject.checkTable(entry[0],tablesList[entry[0]]) # ToDo
- del tablesList[entry[0]]
- if len(tablesList) > 0:
- logging.info('Missing '+str(len(tablesList))+' tables in database, adding them')
- for table in tablesList:
- logging.info('Adding table '+str(table))
- #self.ddbbObject.createTableDefault(table,columns[tablesList.pos(table)]) # ToDo -> review sqliteUtils.createTables
+
+ tablesDB = [] # Database retrieves a list with tuples ¿?
+ for entry in tablesDBT:
+ tablesDB.append(entry[0])
+ logging.debug('Found '+ str(len(tablesDB))+' tables in DB: '+ str(tablesDB))
+
+ # Create a compressed copy of current DB
+ try:
+ self.createDatabaseBackup()
+ except:
+ logging.error('Not able to make a copy of current DB. Printing traceback and exiting')
+ traceback.print_exc()
+ exit(-1)
+
+ for entry in tablesList:
+ if entry not in tablesDB:
+ logging.warn('Table '+str(entry)+' does not exist in DB')
+ self.ddbbObject.createTableDefault(entry,tablesList[entry])
+ else:
+ self.ddbbObject.checkTable(entry,tablesList[entry])
+ logging.debug('<<')
+
+ def createDatabaseBackup(self):
+ logging.debug('>>')
+ logging.debug('Database path: '+str(self.ddbb_path))
+ result = commands.getstatusoutput('gzip -c '+self.ddbb_path+' > '+self.ddbb_path+'_`date +%Y%m%d_%H%M`.gz')
+ if result[0] != 0:
+ raise Exception, "Copying current database does not work, error #"+str(result[0])
else:
- logging.info('Database has all needed tables')
+ logging.info('Database backup successfully created')
logging.debug('<<')
Modified: pytrainer/trunk/pytrainer/lib/sqliteUtils.py
===================================================================
--- pytrainer/trunk/pytrainer/lib/sqliteUtils.py 2009-11-22 09:18:26 UTC (rev 395)
+++ pytrainer/trunk/pytrainer/lib/sqliteUtils.py 2009-11-22 20:50:22 UTC (rev 396)
@@ -102,6 +102,27 @@
self.insert("sports","name",["Mountain Bike"]);
self.insert("sports","name",["Bike"]);
self.insert("sports","name",["Run"]);
+
+ def createTableDefault(self,tableName,columns):
+ """22.11.2009 - dgranda
+ Creates a new table in database given name and column name and data types. New in version 1.7.0
+ args:
+ tableName - string with name of the table
+ columns - dictionary containing column names and data types coming from definition
+ returns: none"""
+ logging.debug('>>')
+ logging.info('Creating '+str(tableName)+' table with default values')
+ logging.debug('Columns definition: '+str(columns))
+ cur = self.db.cursor()
+ sql = 'CREATE TABLE %s (' %(tableName)
+ for entry in columns:
+ sql += '%s %s,' %(entry,columns[entry])
+ # Removing trailing comma
+ sql = sql.rstrip(',')
+ sql = sql+");"
+ logging.debug('SQL sentence: '+str(sql))
+ cur.execute(sql)
+ logging.debug('<<')
def addWaipoints2ddbb(self):
cur = self.db.cursor()
@@ -187,21 +208,48 @@
columns - dictionary containing column names and data types coming from definition
returns: none"""
logging.debug('>>')
+ logging.info('Inspecting '+str(tableName)+' table')
+ logging.debug('Columns definition: '+str(columns))
+
+ # Retrieving data from DB
+ tableInfo = self.retrieveTableInfo(tableName)
+ #logging.debug('Raw data retrieved from DB '+str(tableName)+': '+str(tableInfo))
+
+ # Comparing data retrieved from DB with what comes from definition
+ columnsDB = {}
+ for field in tableInfo:
+ newField = {field[1]:field[2]}
+ columnsDB.update(newField)
+ logging.debug('Useful data retrieved from '+str(tableName)+' in DB: '+str(columnsDB))
+
+ # http://mail.python.org/pipermail/python-list/2002-May/142854.html
+ tempDict = dict(zip(columns,columns))
+ result = [x for x in columnsDB if x not in tempDict]
+ logging.debug("Comparison result: "+str(result))
+
+ if len(result) > 0: # may have also different data type
+ logging.debug('Found columns missed in DB: '+ str(result))
+ for entry in result:
+ logging.debug('Column '+ str(entry) +' not found in DB')
+ self.addColumn(tableName,str(entry),columnsDB[entry])
+ else:
+ logging.info('Table '+ str(tableName) +' is OK')
+ logging.debug('<<')
+
+ def retrieveTableInfo(self,tableName):
cur = self.db.cursor()
sql = "PRAGMA table_info(%s);" %tableName
cur.execute(sql)
tableInfo = []
for row in cur:
tableInfo.append(row)
- logging.debug('Raw data retrieved from table '+str(tableName)+': '+str(tableInfo))
- logging.debug('Table '+str(tableName)+' definition: '+str(columns))
- # Comparing data retrieved from DB and what comes from definition
- # Extracting data needed (column names and types)
- tableInfoComp = {}
- for field in tableInfo:
- newField = {field[1]:field[2]}
- tableInfoComp.update(newField)
- logging.debug('Useful data retrieved from table '+str(tableName)+': '+str(tableInfoComp))
- # Finding out if they differ and what exactly if yes - ToDo
- logging.debug('<<')
+ return tableInfo
+ def addColumn(self,tableName,columnName,dataType):
+ sql = "alter table %s add %s %s" %(tableName,columnName,dataType)
+ try:
+ self.ddbbObject.freeExec(sql)
+ except:
+ logging.error('Not able to add/change column '+columnName+' to table '+tableName)
+ traceback.print_exc()
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
