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