Modify

Opened 6 years ago

Closed 2 years ago

Last modified 21 months ago

#4243 closed enhancement (fixed)

SQL for sqlite3

Reported by: flambeau Owned by: ChrisNelson
Priority: normal Component: TeamCalendarPlugin
Severity: normal Keywords: sql calendar
Cc: r.rossmair@… Trac Release: 0.11

Description (last modified by rjollos)

I've got the TeamCalendarPlugin to work with sqlite3 by changing just one function. I don't use MySQL so I'm not sure if this will work with that too.

def update_timetable(self, tuples):
    db = self.env.get_db_cnx()
    cursor = db.cursor()
    for t in tuples:
      sql = "SELECT * FROM %s WHERE username='%s' AND ondate='%s'" % (self.table_name, t[1], t[0])
      cursor.execute(sql)
      if cursor.fetchone():
        sql = "UPDATE %s SET availability=%d WHERE username='%s' AND ondate='%s'" % (self.table_name, t[2], t[1], t[0])
        cursor.execute(sql)
      else:
        sql = "INSERT INTO %s (ondate, username, availability) VALUES ('%s', '%s', %d)" % (self.table_name, t[0], t[1], t[2])
        cursor.execute(sql)
    db.commit()

Attachments (0)

Change History (13)

comment:2 Changed 6 years ago by anonymous

I can't really see what you changed, so it's hard to judge, but I'd guess the above would work in MySQL as well. I'm willing to accept a patch, but it has to have been tested with MySQL :)

comment:3 Changed 5 years ago by aexl

this is the code before the patch:

   def update_timetable_mysql(self, tuples):
        db = self.env.get_db_cnx()
        insert_cursor = db.cursor()
        # XXX: This is MySQL specific
        insert_cursor.execute("INSERT INTO %s (ondate, username, availability) VALUES %s "
                              "ON DUPLICATE KEY UPDATE availability = VALUES(availability)" %
                                (self.table_name,
                                 ", ".join(["('%s', '%s', %d)" % (t[0], t[1], t[2] and 1 or 0,) for t in tuples])))

comment:4 Changed 5 years ago by aexl

flambeau, could you post your sqlite3 table definition?

comment:5 follow-up: Changed 5 years ago by aexl

what worked in my case for table creation:

CREATE TABLE `team_availability` (
	  `username` TEXT NOT NULL DEFAULT '',
	  `ondate` TEXT NOT NULL DEFAULT '0000-00-00',
	  `availability` REAL DEFAULT NULL,
	  PRIMARY KEY (`username`,`ondate`) ON CONFLICT replace
	);

i dont know sqlite in depth, but as far as i understand it, the last clause ON CONFLICT replace should make it possible to make the sql significantly easier.

going to bed now ;-)

comment:6 in reply to: ↑ 5 ; follow-up: Changed 4 years ago by r.rossmair@…

  • Cc r.rossmair@… added

Replying to aexl:

CREATE TABLE `team_availability` (
	  `username` TEXT NOT NULL DEFAULT '',
	  `ondate` TEXT NOT NULL DEFAULT '0000-00-00',
	  `availability` REAL DEFAULT NULL,
	  PRIMARY KEY (`username`,`ondate`) ON CONFLICT replace
	);

That gives me an internal error when I try to apply changes, e.g.

eKeyError: u'2010-02-01'

where '2010-02-01' is the start of the displayed range, not the date for which I tried to change availability.

However, this seems to work (it's the same as suggested for MySQL):

	CREATE TABLE `team_availability` (
	  `username` varchar(255) NOT NULL DEFAULT '',
	  `ondate` date NOT NULL DEFAULT '0000-00-00',
	  `availability` float unsigned DEFAULT NULL,
	  PRIMARY KEY (`username`,`ondate`)
	);

Tested with Trac 0.12multirepos-r9124

comment:7 in reply to: ↑ 6 Changed 4 years ago by rrossmair

I wasn't aware my email-address would be publicly visible. Someone please remove it.

comment:8 Changed 3 years ago by anonymous

For everybody looking at this ticket here the initial report in copyable form. It works great for me so far:

Index: teamcalendar/calendar.py
===================================================================
--- teamcalendar/calendar.py	(Revision 10172)
+++ teamcalendar/calendar.py	(Arbeitskopie)
@@ -75,13 +75,18 @@
         
     def update_timetable(self, tuples):
         db = self.env.get_db_cnx()
-        insert_cursor = db.cursor()
-        # XXX: This is MySQL specific
-        insert_cursor.execute("INSERT INTO %s (ondate, username, availability) VALUES %s "
-                              "ON DUPLICATE KEY UPDATE availability = VALUES(availability)" %
-                                (self.table_name,
-                                 ", ".join(["('%s', '%s', %d)" % (t[0], t[1], t[2] and 1 or 0,) for t in tuples])))
-    
+        cursor = db.cursor()
+        for t in tuples:
+            sql = "SELECT * FROM %s WHERE username='%s' AND ondate='%s'" % (self.table_name, t[1], t[0])
+            cursor.execute(sql)
+            if cursor.fetchone():
+                sql = "UPDATE %s SET availability=%d WHERE username='%s' AND ondate='%s'" % (self.table_name, t[2], t[1], t[0]) 
+                cursor.execute(sql)
+            else:
+                sql = "INSERT INTO %s (ondate, username, availability) VALUES ('%s', '%s', %d)" % (self.table_name, t[0], t[1], t[2]) 
+                cursor.execute(sql)
+        db.commit()
+        
     def string_to_date(self, date_str, fallback=None):
         try:
             date_tuple = time.strptime(date_str, '%Y-%m-%d')

comment:9 Changed 3 years ago by rjollos

  • Description modified (diff)

comment:10 Changed 3 years ago by ChrisNelson

  • Owner changed from optilude to ChrisNelson

comment:11 Changed 3 years ago by ChrisNelson

  • Status changed from new to assigned

See also #7115.

comment:11 Changed 3 years ago by ChrisNelson

(In [11189]) An alternative, db-agnostic fix for updating availability. Refs #4243, 7115.

This approach assumes it is faster to manipulate Python lists in memory
and minimize DB access than to do at least one DB access per record.

The patch in 4243 did one SELECT to get the state of the DB, then one
UPDATE or INSERT for each and every record.

The patch in 7115 did something similar less elegantly.

This version does one SELECT, some list manipulation, one UPDATE for
each *changed* record, one INSERT for *all* *new* records, and nothing
for records that didn't change.

comment:12 Changed 3 years ago by ChrisNelson

(In [11206]) Finish conversion to Trac DP API. Refs #4243, #7115.

comment:13 Changed 2 years ago by ChrisNelson

  • Resolution set to fixed
  • Status changed from assigned to closed

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from ChrisNelson. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.