| 1 | # -*- perl -*- | ||
| 2 | |||
| 3 | # Copyright (c) 2010 AdCopy | ||
| 4 | # Author: Jeff Weisberg | ||
| 5 | # Created: 2010-Jun-15 17:21 (EDT) | ||
| 6 | # Function: sqlite example storage backend | ||
| 7 | # | ||
| 8 | # $Id: SQLite.pm,v 1.2 2010/09/07 15:31:59 jaw Exp $ | ||
| 9 | |||
| 10 | package AC::Yenta::Store::SQLite; | ||
| 11 | use AC::Yenta::Debug 'sqlite'; | ||
| 12 | use MIME::Base64; | ||
| 13 | use DBI; | ||
| 14 | use strict; | ||
| 15 | |||
| 16 | my $initsql; | ||
| 17 | |||
| 18 | AC::Yenta::Store::Map->add_backend( sqlite => 'AC::Yenta::Store::SQLite' ); | ||
| 19 | |||
| 20 | sub new { | ||
| 21 | my $class = shift; | ||
| 22 | my $name = shift; | ||
| 23 | my $conf = shift; | ||
| 24 | |||
| 25 | my $file = $conf->{dbfile}; | ||
| 26 | unless( $file ){ | ||
| 27 | problem("no dbfile specified for '$name'"); | ||
| 28 | return; | ||
| 29 | } | ||
| 30 | |||
| 31 | debug("opening sqlite file=$file"); | ||
| 32 | |||
| 33 | my $dsn = "dbi:SQLite:dbname=$file"; | ||
| 34 | |||
| 35 | my $db = DBI->connect( $dsn, '', '', { | ||
| 36 | AutoCommit => 1, | ||
| 37 | RaiseError => 1, | ||
| 38 | } ); | ||
| 39 | |||
| 40 | problem("cannot open sqlite file $file") unless $db; | ||
| 41 | |||
| 42 | _init( $db ); | ||
| 43 | |||
| 44 | return bless { | ||
| 45 | file => $file, | ||
| 46 | db => $db, | ||
| 47 | }, $class; | ||
| 48 | } | ||
| 49 | |||
| 50 | sub get { | ||
| 51 | my $me = shift; | ||
| 52 | my $map = shift; | ||
| 53 | my $sub = shift; | ||
| 54 | my $key = shift; | ||
| 55 | |||
| 56 | debug("get $map/$sub/$key"); | ||
| 57 | |||
| 58 | my $st = _do($me->{db}, 'select value, 1 from ykv where map = ? and sub = ? and key = ?', $map, $sub, $key); | ||
| 59 | |||
| 60 | my($v, $found) = $st->fetchrow_array(); | ||
| 61 | return unless $found; | ||
| 62 | |||
| 63 | $v = decode_base64($v); | ||
| 64 | |||
| 65 | if( wantarray ){ | ||
| 66 | return ($v, 1); | ||
| 67 | } | ||
| 68 | return $v; | ||
| 69 | } | ||
| 70 | |||
| 71 | sub put { | ||
| 72 | my $me = shift; | ||
| 73 | my $map = shift; | ||
| 74 | my $sub = shift; | ||
| 75 | my $key = shift; | ||
| 76 | my $val = shift; | ||
| 77 | |||
| 78 | debug("put $map/$sub/$key"); | ||
| 79 | |||
| 80 | my $st = _do($me->{db}, 'select 1 from ykv where map = ? and sub = ? and key = ?', $map, $sub, $key); | ||
| 81 | my($found) = $st->fetchrow_array(); | ||
| 82 | |||
| 83 | if( $found ){ | ||
| 84 | _do($me->{db}, 'update ykv set value = ? where map = ? and sub = ? and key = ?', encode_base64($val), $map, $sub, $key); | ||
| 85 | }else{ | ||
| 86 | _do($me->{db}, 'insert into ykv (map,sub,key,value) values (?,?,?,?)', $map, $sub, $key, encode_base64($val)); | ||
| 87 | } | ||
| 88 | |||
| 89 | return 1; | ||
| 90 | } | ||
| 91 | |||
| 92 | sub del { | ||
| 93 | my $me = shift; | ||
| 94 | my $map = shift; | ||
| 95 | my $sub = shift; | ||
| 96 | my $key = shift; | ||
| 97 | |||
| 98 | debug("del $map/$sub/$key"); | ||
| 99 | |||
| 100 | _do($me->{db}, 'delete from ykv where map = ? and sub = ? and key = ?', $map, $sub, $key); | ||
| 101 | |||
| 102 | return 1; | ||
| 103 | } | ||
| 104 | |||
| 105 | sub sync {} | ||
| 106 | |||
| 107 | sub range { | ||
| 108 | my $me = shift; | ||
| 109 | my $map = shift; | ||
| 110 | my $sub = shift; | ||
| 111 | my $key = shift; | ||
| 112 | my $end = shift; # undef => to end of map | ||
| 113 | |||
| 114 | my $st; | ||
| 115 | |||
| 116 | if( defined $end ){ | ||
| 117 | $st = _do($me->{db}, 'select key as k, value as v from ykv where map = ? and sub = ? and key >= ? and key < ?', | ||
| 118 | $map, $sub, $key, $end); | ||
| 119 | }else{ | ||
| 120 | $st = _do($me->{db}, 'select key as k, value as v from ykv where map = ? and sub = ? and key >= ?', | ||
| 121 | $map, $sub, $key); | ||
| 122 | } | ||
| 123 | |||
| 124 | my $r = $st->fetchall_arrayref({}); | ||
| 125 | |||
| 126 | return @$r; | ||
| 127 | } | ||
| 128 | |||
| 129 | ################################################################ | ||
| 130 | |||
| 131 | sub _init { | ||
| 132 | my $db = shift; | ||
| 133 | |||
| 134 | eval { | ||
| 135 | for my $sql (split /;/, $initsql){ | ||
| 136 | $sql =~ s/--\s.*$//gm; # remove comments | ||
| 137 | next unless $sql !~ /^\s*$/; | ||
| 138 | _do($db, $sql); | ||
| 139 | } | ||
| 140 | }; | ||
| 141 | if(my $e=$@){ | ||
| 142 | # QQQ? | ||
| 143 | problem("error initializing sqlite db: $e"); | ||
| 144 | } | ||
| 145 | } | ||
| 146 | |||
| 147 | sub _do { | ||
| 148 | my $db = shift; | ||
| 149 | my $sql = shift; | ||
| 150 | |||
| 151 | my( $st, $nrow ); | ||
| 152 | eval { | ||
| 153 | debug("sql: $sql"); | ||
| 154 | $st = $db->prepare( $sql ); | ||
| 155 | $nrow = $st->execute( @_ ); | ||
| 156 | }; | ||
| 157 | my $e = $@; | ||
| 158 | die $e if $e; | ||
| 159 | |||
| 160 | return $st; | ||
| 161 | } | ||
| 162 | |||
| 163 | ################################################################ | ||
| 164 | |||
| 165 | $initsql = <<END; | ||
| 166 | |||
| 167 | create table if not exists ykv ( | ||
| 168 | map text not null, | ||
| 169 | sub text not null, | ||
| 170 | key text not null, | ||
| 171 | value text, | ||
| 172 | |||
| 173 | unique(map,sub,key) | ||
| 174 | ); | ||
| 175 | |||
| 176 | create index if not exists ykvidx on ykv(map, sub, key); | ||
| 177 | |||
| 178 | pragma synchronous = 1; -- default is full(2) | ||
| 179 | |||
| 180 | pragma cache_size = 100000; -- default is 2000 | ||
| 181 | |||
| 182 | vacuum; | ||
| 183 | |||
| 184 | analyze; | ||
| 185 | |||
| 186 | END | ||
| 187 | ; | ||
| 188 | |||
| 189 | 1; |