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