Switch to sqlite

More suitable for low-ram virtual machines, and easier to maintain.
Postgres is great, but somewhat overkill for a simple pastebin.

1 files changed, 28 insertions, 31 deletions

diff --git a/index.cgi b/index.cgi
index 3404e0c..a50f28d 100755
--- a/index.cgi
+++ b/index.cgi
@@ -1,31 +1,10 @@
 #!/usr/bin/perl
 
-
-# SQL schema:
-#
-# CREATE TABLE pastes (
-#   code char(5) PRIMARY KEY,
-#   syntax varchar NOT NULL DEFAULT 'nosyntax',
-#   wrap boolean NOT NULL DEFAULT false,
-#   raw text NOT NULL,
-#   html text,
-#   date timestamp with time zone NOT NULL DEFAULT now(),
-#   lastvisit timestamp with time zone NOT NULL DEFAULT now(),
-#   parse_urls boolean NOT NULL DEFAULT false,
-#   passcode varchar
-# );
-
-
-# Converting from older schemas:
-# 2014-01-10:
-#  ALTER TABLE pastes ALTER COLUMN code TYPE varchar;
-# 2010-12-23:
-#  ALTER TABLE pastes DROP COLUMN ip;
-
 # This isn't the cleanest code I've ever written...
 
 use strict;
 use warnings;
+use POSIX 'strftime';
 use TUWF ':html', 'html_escape';
 
 
@@ -38,6 +17,7 @@ TUWF::set(
   max_post_body => 1024*1024, # 1MiB
   # let DBI figure out the login details from the DBI_ environment variables
   db_login      => [undef, undef, undef],
+  pre_request_handler => \&init,
 );
 
 my $codematch = qr/([a-z0-9]{5}|[a-zA-Z0-9\.-]{32})/;
@@ -50,6 +30,25 @@ TUWF::register(
 TUWF::run();
 
 
+sub init {
+  my $self = shift;
+  $self->dbh()->sqlite_busy_timeout(10000);
+  $self->dbExec(q(
+    CREATE TABLE IF NOT EXISTS pastes (
+      code       TEXT PRIMARY KEY,
+      syntax     TEXT NOT NULL DEFAULT 'nosyntax',
+      wrap       INTEGER NOT NULL DEFAULT 0,
+      parse_urls INTEGER NOT NULL DEFAULT 0,
+      raw        TEXT NOT NULL,
+      html       TEXT,
+      date       INTEGER NOT NULL,
+      lastvisit  INTEGER NOT NULL,
+      passcode   TEXT
+    )));
+  return 1;
+}
+
+
 sub upload {
   my $self = shift;
 
@@ -81,8 +80,8 @@ sub upload {
 
   my $html = $f->{s} eq 'nosyntax' ? undef : _get_html({ raw => $dat, syntax => $f->{s}, parse_urls => $f->{c}});
   $self->dbExec(
-    'INSERT INTO pastes (code, syntax, wrap, raw, html, parse_urls, passcode) VALUES(!l)',
-    [ $code, $f->{s}, $f->{w}, $dat, $html, $f->{c}, $p || undef]
+    'INSERT INTO pastes (code, syntax, wrap, parse_urls, passcode, raw, html, date, lastvisit) VALUES(!l)',
+    [ $code, $f->{s}, $f->{w}?1:0, $f->{c}?1:0, $p||undef, $dat, $html, time, time]
   );
 }
 
@@ -108,8 +107,7 @@ sub mypastes {
   my $p = $self->passcode('pc','psp');
 
   my $pl = $self->dbAll(q|
-    SELECT code, to_char(date, 'YYYY-MM-DD HH24:MI:SS') AS date, syntax,
-           substring(raw from 1 for 150) AS preview, length(raw) AS size
+    SELECT code, date, syntax, substr(raw, 1, 150) AS preview, length(raw) AS size
       FROM pastes
      WHERE passcode = ?
      ORDER BY date DESC|,
@@ -126,7 +124,7 @@ sub mypastes {
      for(@$pl) {
        use utf8;
        Tr;
-        td class => 'mpldate', $_->{date};
+        td class => 'mpldate', strftime '%F %T', gmtime $_->{date};
         td class => 'mplcode'; a href => "/$_->{code}", substr($_->{code}, 0, 5).(length $_->{code} > 5 ? '…':''); end;
         td class => 'mplsyn',  $_->{syntax};
         td class => 'mplsize', sprintf '%.1fk', $_->{size}/1024;
@@ -369,12 +367,11 @@ sub htmlUploadForm {
 sub getpaste {
   my($self, $code, $col) = @_;
   my $r = $self->dbRow(q|
-    SELECT !s, (lastvisit < (NOW()-'1 day'::interval)) AS needsupdate
-      FROM pastes WHERE code = ?
+    SELECT !s, lastvisit FROM pastes WHERE code = ?
     |, $col, $code
   );
   return $self->msg('No paste with that code.') if !keys %$r;
-  $self->dbExec('UPDATE pastes SET lastvisit = NOW() WHERE code = ?', $code) if $r->{needsupdate};
+  $self->dbExec('UPDATE pastes SET lastvisit = ? WHERE code = ?', time(), $code) if $r->{lastvisit} < time()-24*3600;
   return $r;
 }
 
@@ -438,7 +435,7 @@ sub getcode {
     # Weird characters at the start or end are annoying, skip these
     next if $code =~ /^[-.]/ || $code =~ /[-.]$/;
 
-    last if !$self->dbRow('SELECT 1 AS exists FROM pastes WHERE code = ?', $code)->{exists};
+    last if !$self->dbRow('SELECT 1 AS exist FROM pastes WHERE code = ?', $code)->{exist};
     warn "Generated duplicate code: $code. Trying again...\n";
     $i++;
   }