Store Unix timestamps in the database, rather than SQL ones.

This will make doing calculations with them easier, and removes some
really annoying database-portability problems.

diff --git a/bin/pastebin.userv b/bin/pastebin.userv
index 3c87cb5..ed7ba7a 100755 (executable)
--- a/bin/pastebin.userv
+++ b/bin/pastebin.userv
@@ -42,11 +42,10 @@ EOF
   @ARGV == 0 or Odin::fail "usage: list";
   my $db = Odin::open_db;
   for my $r (@{$db->selectall_arrayref
   @ARGV == 0 or Odin::fail "usage: list";
   my $db = Odin::open_db;
   for my $r (@{$db->selectall_arrayref

-               ("SELECT " . Odin::sql_timestamp($db, "stamp") .
-                     ", tag, lang, title
+               ("SELECT tag, stamp, lang, title

                  FROM odin_pastebin WHERE owner = ?
                  ORDER BY stamp", undef, $Odin::WHO)}) {
                  FROM odin_pastebin WHERE owner = ?
                  ORDER BY stamp", undef, $Odin::WHO)}) {

-    my ($stamp, $tag, $lang, $title) = @$r;
+    my ($tag, $stamp, $lang, $title) = @$r;

     my $t = strftime "%Y-%m-%d %H:%M:%S %z", localtime $stamp;
     printf "%-25s  %-12s  %-16s  %s\n",
       $t, $tag, $lang, encode locale => $title;
     my $t = strftime "%Y-%m-%d %H:%M:%S %z", localtime $stamp;
     printf "%-25s  %-12s  %-16s  %s\n",
       $t, $tag, $lang, encode locale => $title;

diff --git a/bin/shorturl.userv b/bin/shorturl.userv
index 489168d..edad9f6 100755 (executable)
--- a/bin/shorturl.userv
+++ b/bin/shorturl.userv
@@ -23,10 +23,10 @@ EOF
   @ARGV == 0 or Odin::fail "usage: list";
   my $db = Odin::open_db;
   for my $r (@{$db->selectall_arrayref
   @ARGV == 0 or Odin::fail "usage: list";
   my $db = Odin::open_db;
   for my $r (@{$db->selectall_arrayref

-      ("SELECT " . Odin::sql_timestamp($db, "stamp") . ", tag, url
+      ("SELECT tag, stamp, url

        FROM odin_shorturl WHERE owner = ?
        ORDER BY stamp", undef, $Odin::WHO)}) {
        FROM odin_shorturl WHERE owner = ?
        ORDER BY stamp", undef, $Odin::WHO)}) {

-    my ($stamp, $tag, $url) = @$r;
+    my ($tag, $stamp, $url) = @$r;

     my $t = strftime "%Y-%m-%d %H:%M:%S %z", localtime $stamp;
     printf "%-25s  %-12s  %s\n", $t, $tag, $url;
   }
     my $t = strftime "%Y-%m-%d %H:%M:%S %z", localtime $stamp;
     printf "%-25s  %-12s  %s\n", $t, $tag, $url;
   }

diff --git a/lib/Odin.pm b/lib/Odin.pm
index a9bff5a..551c51f 100644 (file)
--- a/lib/Odin.pm
+++ b/lib/Odin.pm
@@ -99,12 +99,10 @@ sub open_db (@) {
   my $drv = $db->{Driver}{Name};
   if ($drv eq "Pg") {
     $db->{private_odin_retry_p} = sub { $db->state =~ /^40[0P]01$/ };
   my $drv = $db->{Driver}{Name};
   if ($drv eq "Pg") {
     $db->{private_odin_retry_p} = sub { $db->state =~ /^40[0P]01$/ };

-    $db->{private_odin_unixstamp} = sub { "extract(epoch from $_[0])" };

   } elsif ($drv eq "SQLite") {
     $db->{private_odin_retry_p} = sub { $db->err == 5 };
   } elsif ($drv eq "SQLite") {
     $db->{private_odin_retry_p} = sub { $db->err == 5 };

-    $db->{private_odin_unixstamp} = sub { "strftime('%s', $_[0])" };

   } else {
   } else {

-    fail "unsupported database driver `$drv' (patches welcome)", undef;
+    $db->{private_odin_retry_p} = sub { 0 };

   }
 
   return $db;
   }
 
   return $db;


@@ -131,10 +129,6 @@ sub xact (&$) {
   die $exc;
 }
 
   die $exc;
 }
 

-sub sql_timestamp ($$) {
-  my ($db, $col) = @_;
-  return $db->{private_odin_unixstamp}->($col);
-}

 
 ###--------------------------------------------------------------------------
 ### Sequence numbers and tagging.
 
 ###--------------------------------------------------------------------------
 ### Sequence numbers and tagging.


@@ -267,8 +261,9 @@ sub new_shorturl ($) {
        undef, $WHOCMP, $url);
     unless (defined $tag) {
       $tag = encode_tag(next_seq($db, "odin_shorturl_seq"));
        undef, $WHOCMP, $url);
     unless (defined $tag) {
       $tag = encode_tag(next_seq($db, "odin_shorturl_seq"));

-      $db->do("INSERT INTO odin_shorturl (tag, owner, url) VALUES (?, ?, ?)",
-             undef, $tag, $WHO, $url);
+      $db->do("INSERT INTO odin_shorturl (tag, stamp, owner, url)
+              VALUES (?, ?, ?, ?)", undef,
+             $tag, $NOW, $WHO, $url);

     }
   } $db;
   return $tag;
     }
   } $db;
   return $tag;


@@ -331,9 +326,9 @@ sub new_pastebin (\%) {
   xact {
     $tag = encode_tag next_seq $db, "odin_pastebin_seq";
     $db->do("INSERT INTO odin_pastebin
   xact {
     $tag = encode_tag next_seq $db, "odin_pastebin_seq";
     $db->do("INSERT INTO odin_pastebin

-              (tag, edithash, owner, $PASTEBIN_PROPCOLS)
-            VALUES (?, ?, ?, $PASTEBIN_PROPPLACES)", undef,
-           $tag, $hash, $WHO, @{$new}{@PASTEBIN_PROPS});
+              (tag, stamp, edithash, owner, $PASTEBIN_PROPCOLS)
+            VALUES (?, ?, ?, ?, $PASTEBIN_PROPPLACES)", undef,
+           $tag, $NOW, $hash, $WHO, @{$new}{@PASTEBIN_PROPS});

   } $db;
   return $tag, $editkey;
 }
   } $db;
   return $tag, $editkey;
 }

diff --git a/sql/setup-pastebin.sql b/sql/setup-pastebin.sql
index b4e99a1..391f8be 100644 (file)
--- a/sql/setup-pastebin.sql
+++ b/sql/setup-pastebin.sql
@@ -23,7 +23,7 @@ insert into odin_pastebin_seq (seq) values (10000);
 
 create table odin_pastebin
        (tag varchar(16) primary key,
 
 create table odin_pastebin
        (tag varchar(16) primary key,

-        stamp timestamp not null default current_timestamp,
+        stamp bigint not null,

         edithash varchar(128) not null,
         owner varchar(64) not null,
         title varchar(128) not null,
         edithash varchar(128) not null,
         owner varchar(64) not null,
         title varchar(128) not null,

diff --git a/sql/setup-shorturl.sql b/sql/setup-shorturl.sql
index eaddf90..dee90a3 100644 (file)
--- a/sql/setup-shorturl.sql
+++ b/sql/setup-shorturl.sql
@@ -17,7 +17,7 @@ insert into odin_shorturl_seq (seq) values (10000);
 
 create table odin_shorturl
        (tag varchar(16) primary key,
 
 create table odin_shorturl
        (tag varchar(16) primary key,

-        stamp timestamp not null default current_timestamp,
+        stamp bigint not null,

         owner varchar(64) not null,
         url text not null);
 create index odin_shorturl_by_owner on odin_shorturl (owner);
         owner varchar(64) not null,
         url text not null);
 create index odin_shorturl_by_owner on odin_shorturl (owner);