changeset 631:9596cd122639

tj: Adjust database schema a bit, add new field 'uupdated'.
author Matti Hamalainen <ccr@tnsp.org>
date Mon, 15 Feb 2021 13:42:10 +0200
parents b7e3a47ed072
children 68b8cf87e86e
files create_tj_db.tcl tj.tcl
diffstat 2 files changed, 10 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/create_tj_db.tcl	Thu Feb 11 16:23:45 2021 +0200
+++ b/create_tj_db.tcl	Mon Feb 15 13:42:10 2021 +0200
@@ -19,20 +19,23 @@
   lappend sch "uuser VARCHAR(32)"
 
   # created timestamp
-  lappend sch "ucreated INT"
+  lappend sch "ucreated DATETIME"
+
+  # updated timestamp
+  lappend sch "uupdated DATETIME"
 
   # description
   lappend sch "udesc VARCHAR(1024)"
 
-  # 0 = TJ, 1 = one-time reminder, 2 = repeat reminder
-  lappend sch "utype INT"
-
   # unique identifier for type 0
   lappend sch "uid VARCHAR(32)"
 
   # target date/time
   lappend sch "utarget DATETIME"
 
+  # 0 = TJ, 1 = one-time reminder, 2 = repeat reminder
+  lappend sch "utype INT"
+
   utl_create_table dbh "$utable" [join $sch ","]
 }
 
--- a/tj.tcl	Thu Feb 11 16:23:45 2021 +0200
+++ b/tj.tcl	Mon Feb 15 13:42:10 2021 +0200
@@ -349,9 +349,10 @@
       return 1
     }
 
+    set ucreated [utl_sql_stamp_to_datetime [clock seconds]]
     if {$umode} {
       # Insert new entry
-      set usql "INSERT INTO tj (uid,uuser,ucreated,udesc,utype,utarget) VALUES ('[utl_escape $uid]', '[utl_escape $uhand]', [unixtime], '[utl_escape $rdesc]', 0, '$rdate')"
+      set usql "INSERT INTO tj (uid,uuser,ucreated,uupdated,udesc,utype,utarget) VALUES ('[utl_escape $uid]', '[utl_escape $uhand]', '$ucreated', '$ucreated', '[utl_escape $rdesc]', 0, '$rdate')"
       if {[catch {tj_dbh eval $usql} uerrmsg]} {
         tj_msg $upublic $unick $uchan "err_sql" [list $uerrmsg]
         tj_log "$uerrmsg on SQL:\n$usql"
@@ -364,6 +365,7 @@
     } else {
       # Update existing entry
       set usqlargs {}
+      lappend usqlargs "uupdated='$ucreated'"
       if {[string length $rdesc] > 0} {
         lappend usqlargs "udesc='[utl_escape $rdesc]'"
       }