SQL for sqlite3

[James Petry]

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()

[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 :)

[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])))

[aexl]

flambeau, could you post your sqlite3 table definition?

[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 ;-)

[Robert Rossmair]

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

[Robert Rossmair]

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

[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')

[Chris Nelson]

See also #7115.

[Chris Nelson]

(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.

[Chris Nelson]

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