← Back to branch summary

~ubuntu-branches/ubuntu/natty/pytrainer/natty-proposed

« back to all changes in this revision

Viewing changes to pytrainer/lib/ddbb.py

  • Committer: Bazaar Package Importer
  • Author(s): Alessio Treglia
  • Date: 2010-02-04 06:07:11 UTC
  • mfrom: (4.1.3 sid)
  • Revision ID: james.westby@ubuntu.com-20100204060711-25n5aw66w5egeiph
Tags: 1.7.1-1ubuntu1
* Merge from debian testing, remaining changes:
  - debian/control:
    + Replace Depends on iceweasel with firefox | abrowser.
    + Bump python-dev,debhelper build-dependencies.
    - Drop dependency on python-glade2 (libglade -> gtkbuilder transition).
  - debian/rules:
    + Append --install-laoyut=deb to setup.py install to prevent a build
      failure with Python 2.6.

Show diffs side-by-side

added added

removed removed

Lines of Context:
pytrainer/lib/ddbb.py
21
21
 
22
22
import logging
23
23
import traceback
24
 
import commands
 
24
import commands, os
25
25
from system import checkConf
 
26
from pytrainer.lib.date import Date
26
27
 
27
28
class DDBB:
28
29
        def __init__(self, configuration):
29
30
                self.ddbb_type = configuration.getValue("pytraining","prf_ddbb")
30
 
                if self.ddbb_type == "mysql":
 
31
                if self.ddbb_type == "mysql": #TODO no longer supported?
31
32
                        from mysqlUtils import Sql
32
33
                else:
33
34
                        from sqliteUtils import Sql
43
44
                self.ddbbObject = Sql(ddbb_host,ddbb,ddbb_user,ddbb_pass)
44
45
                
45
46
        def connect(self):
46
 
                #si devolvemos 1 ha ido todo con exito
47
 
                #con 0 es que no estaba la bbdd creada
48
 
                #con -1 imposible conectar a la maquina.
 
47
                #si devolvemos 1 ha ido todo con exito          : return 1 if all successful
 
48
                #con 0 es que no estaba la bbdd creada          : 0 is DB not created
 
49
                #con -1 imposible conectar a la maquina.        : -1 is impossible to connect to the host
49
50
                var = self.ddbbObject.connect()
50
51
                if var == 0:
51
52
                        self.ddbbObject.createDDBB()
57
58
        def disconnect(self):
58
59
                self.ddbbObject.disconnect()
59
60
 
60
 
        def build_ddbb(self):
 
61
        def build_ddbb(self): #TODO Is this needed?
61
62
                self.ddbbObject.createDDBB()
62
63
                self.ddbbObject.connect()
63
64
                self.ddbbObject.createTables()
75
76
        def update(self,table,cells,value,condition):
76
77
                self.ddbbObject.update(table,cells,value,condition)
77
78
        
78
 
        def lastRecord(self,table):
 
79
        def lastRecord(self,table): #TODO Make generic i.e. something like id = "id_" + table[:-1] 
79
80
                if table=="records":
80
81
                        id = "id_record"
81
82
                if table=="sports":
86
87
                ret_val = self.ddbbObject.freeExec(sql)
87
88
                return ret_val[0][0]
88
89
 
89
 
        def addTitle2ddbb(self):
90
 
                #this function add a title column in
 
90
        #TODO Remove extra check functions below
 
91
 
 
92
        #def addTitle2ddbb(self):
 
93
                '''#this function add a title column in
91
94
                #the record ddbb. New in 0.9.9 version
92
95
                sql = "alter table records add title varchar(200)"
93
96
                try:
94
97
                        self.ddbbObject.freeExec(sql)
95
98
                except:
96
99
                        logging.error('Column title already exists in DB. Printing traceback and continuing')
97
 
                        traceback.print_exc()
 
100
                        traceback.print_exc()'''
98
101
                
99
 
        def addUnevenness2ddbb(self):
100
 
                #this function add accumulated unevennes columns in
 
102
        #def addUnevenness2ddbb(self):
 
103
                '''#this function add accumulated unevennes columns in
101
104
                #the record ddbb. New in 1.3.2 version
102
105
                sql = "alter table records add upositive float"
103
106
                try:
110
113
                        self.ddbbObject.freeExec(sql)
111
114
                except:
112
115
                        logging.error('Column unegative already exists in DB. Printing traceback and continuing')
113
 
                        traceback.print_exc()
 
116
                        traceback.print_exc()'''
114
117
                        
115
 
        def addWaypoints2ddbb(self):
116
 
                #adds waipoints table to database
 
118
        #def addWaypoints2ddbb(self):
 
119
                '''#adds waipoints table to database
117
120
                try:
118
121
                        self.ddbbObject.addWaipoints2ddbb()
119
122
                except:
120
123
                        logging.error('Waypoints table already exists in DB. Printing traceback and continuing')
121
 
                        traceback.print_exc()
 
124
                        traceback.print_exc()'''
122
125
        
123
 
        def updatemonth(self):
124
 
                #this is a function to repair a bug from
 
126
        #def updatemonth(self):
 
127
                '''#this is a function to repair a bug from
125
128
                #pytrainer 0.9.5 and previus
126
129
                listOfRecords = self.ddbbObject.select("records","id_record,date", None)
127
130
                for record in listOfRecords:
128
131
                        rec = record[1].split("-")
129
132
                        newmonth = int(rec[1])+1
130
133
                        newdate = "%s-%d-%s" %(rec[0],newmonth,rec[2])
131
 
                        self.ddbbObject.update("records","date",[newdate], "id_record = %d" %record[0])
 
134
                        self.ddbbObject.update("records","date",[newdate], "id_record = %d" %record[0])'''
132
135
 
133
 
        def updateDateFormat(self):
134
 
                #this is a function to repair a bug from 
 
136
        #def updateDateFormat(self): #TODO Might still need this function??
 
137
                '''#this is a function to repair a bug from 
135
138
                #pytrainer 0.9.8 and previus
136
139
                listOfRecords = self.ddbbObject.select("records","id_record,date", None)
137
140
                for record in listOfRecords:
140
143
                                newdate = "%0.4d-%0.2d-%0.2d" %(int(rec[0]),int(rec[1]),int(rec[2]))
141
144
                                self.ddbbObject.update("records","date",[newdate], "id_record = %d" %record[0])
142
145
                        except:
143
 
                                print record
 
146
                                print record'''
144
147
                        
145
 
        def addweightandmet2ddbb(self):
146
 
                #this function add weight extra and met fields to sports table
 
148
        #def addweightandmet2ddbb(self):
 
149
                '''#this function add weight extra and met fields to sports table
147
150
                sql = "alter table sports add weight float"
148
151
                try:
149
152
                        self.ddbbObject.freeExec(sql)
155
158
                        self.ddbbObject.freeExec(sql)
156
159
                except:
157
160
                        logging.error('Column met already exists in DB. Printing traceback and continuing')
158
 
                        traceback.print_exc()
 
161
                        traceback.print_exc()'''
159
162
                        
160
 
        def checkmettable(self):
161
 
                sql = "alter table sports add met float"
 
163
        #def checkmettable(self):
 
164
                '''sql = "alter table sports add met float"
162
165
                try:
163
166
                        self.ddbbObject.freeExec(sql)
164
167
                except:
165
168
                        logging.error('Column met already exists in DB. Printing traceback and continuing')
166
 
                        traceback.print_exc()
 
169
                        traceback.print_exc()'''
167
170
 
168
 
        def addpaceandmax2ddbb(self):
169
 
                sql = "alter table records add maxspeed float"
 
171
        #def addpaceandmax2ddbb(self):
 
172
                '''sql = "alter table records add maxspeed float"
170
173
                try:
171
174
                        self.ddbbObject.freeExec(sql)
172
175
                except:
189
192
                        self.ddbbObject.freeExec(sql)
190
193
                except:
191
194
                        logging.error('Column maxbeats already exists in DB. Printing traceback and continuing')
192
 
                        traceback.print_exc()
 
195
                        traceback.print_exc()'''
193
196
                
194
 
        def addDateTimeUTC2ddbb(self):
195
 
                sql = "alter table records add date_time_utc varchar2(20)"
 
197
        #def addDateTimeUTC2ddbb(self):
 
198
                '''sql = "alter table records add date_time_utc varchar2(20)"
196
199
                try:
197
200
                        self.ddbbObject.freeExec(sql)
198
201
                except:
199
202
                        logging.error('Column date_time_utc already exists in DB. Printing traceback and continuing')
200
 
                        traceback.print_exc()
 
203
                        traceback.print_exc()'''
201
204
                
202
 
        def shortFromLocal(self, getSport=True): # Check LEFT and RIGHT JOINS for people with multiple sports
203
 
                if getSport is True:
 
205
        #def shortFromLocal(self, getSport=True): # Check LEFT and RIGHT JOINS for people with multiple sports
 
206
                '''if getSport is True:
204
207
                        sql = "select sports.name,records.date_time_utc from sports INNER JOIN records ON sports.id_sports = records.sport"
205
208
                else:
206
209
                        sql = "select records.date_time_utc from sports INNER JOIN records ON sports.id_sports = records.sport"
207
 
                return self.ddbbObject.freeExec(sql)
 
210
                return self.ddbbObject.freeExec(sql)'''
208
211
 
209
212
        def checkDBIntegrity(self):
210
213
                """17.11.2009 - dgranda
237
240
                        "maxpace":"float", 
238
241
                        "pace":"float", 
239
242
                        "maxbeats":"float", 
 
243
                        "date_time_local":"varchar2(20)",
240
244
                        "date_time_utc":"varchar2(20)"}
241
245
                columnsWaypoints = {"id_waypoint":"integer primary key autoincrement",
242
246
                        "lat":"float",
267
271
                        traceback.print_exc()
268
272
                        exit(-1)
269
273
 
 
274
                #Check Tables
270
275
                for entry in tablesList:
271
276
                        if entry not in tablesDB:
272
277
                                logging.warn('Table '+str(entry)+' does not exist in DB')
273
278
                                self.ddbbObject.createTableDefault(entry,tablesList[entry])
274
279
                        else:
275
280
                                self.ddbbObject.checkTable(entry,tablesList[entry])
 
281
 
 
282
                #Run any functions to update or correct data
 
283
                #These functions _must_ be safe to run at any time (i.e. not be version specfic or only safe to run once)
 
284
                self.populate_date_time_local()
276
285
                logging.debug('<<')
277
286
 
278
287
        def createDatabaseBackup(self):
285
294
                        logging.info('Database backup successfully created')
286
295
                logging.debug('<<')
287
296
 
 
297
        def populate_date_time_local(self):
 
298
                '''     Populate date_time_local and date from date_time_utc
 
299
                                only access records that date_time_local is NULL
 
300
                                using OS timezone to create local_time
 
301
 
 
302
                                also updates date if date != local_time
 
303
 
 
304
                                TODO - leaves date_time_local blank for records that have been manually created (as date_time_utc will actually be local time)
 
305
                '''
 
306
                logging.debug('--')
 
307
                listOfRecords = self.ddbbObject.select("records","id_record,date,date_time_utc,date_time_local", "date_time_local is NULL")
 
308
                logging.debug("Found %d records in DB without date_time_local field populated" % (len(listOfRecords) ) )
 
309
                for record in listOfRecords:
 
310
                        try:
 
311
                                gpxfile = self.conf.getValue("gpxdir")+"/%s.gpx"%(record[0])
 
312
                                dateFromUTC = Date().getDateTime(record[2])
 
313
                                if os.path.isfile(gpxfile) : #GPX file exists for this record - probably not a manual record
 
314
                                        date_time_local = str(dateFromUTC[1])
 
315
                                        dateFromLocal = dateFromUTC[1].strftime("%Y-%m-%d")
 
316
                                        if record[1] != dateFromLocal:
 
317
                                                #date field incorrect - update it
 
318
                                                logging.debug("Updating record id: %s with date: %s and date_time_local: %s" % (record[0], dateFromLocal, date_time_local) )
 
319
                                                self.ddbbObject.update("records","date, date_time_local",[dateFromLocal, date_time_local], "id_record = %d" %record[0])
 
320
                                        else:
 
321
                                                #date field OK, just update date_time_local
 
322
                                                logging.debug("Updating record id: %s with date_time_local: %s" % (record[0], date_time_local) )
 
323
                                                self.ddbbObject.update("records","date_time_local",[date_time_local], "id_record = %d" %record[0])
 
324
                                else: #Manual entry?    
 
325
                                        #For manual entries, the UTC time is the local time
 
326
                                        #TODO figure out a way to correct this...
 
327
                                        pass
 
328
                        except:
 
329
                                print "Error updating record: " + str(record)
 
330
                                logging.debug("Error updating record: " + str(record))