Database.pm (5980B)
1 package SL::Database; 2 3 use DBI; 4 use File::Temp; 5 6 sub new { 7 my $class = shift; 8 my $db_file = shift; 9 10 my $self = {}; 11 bless ($self, $class); 12 13 $self->{dbh} = DBI->connect( 14 "dbi:SQLite:dbname=$db_file", 15 "", "", 16 { 17 RaiseError => 1, 18 sqlite_unicode => 1, 19 } 20 ) or die $DBI::errstr; 21 22 $self->{dbh}->do("PRAGMA foreign_keys = ON"); 23 $self->{dbh}->{AutoCommit} = 1; 24 25 return $self; 26 } 27 28 sub create_tables { 29 my ($self) = @_; 30 31 my $db_handle = $self->{dbh}; 32 $db_handle->begin_work; 33 34 $db_handle->do(qq{ 35 create table if not exists lists ( 36 num integer primary key, 37 name text not null, 38 date int, 39 created int not null, 40 last_updated int not null) 41 }); 42 43 $db_handle->do(qq{ 44 create table if not exists devices ( 45 num integer primary key, 46 id text not null, 47 phone_num text not null, 48 os text, 49 push_token text, 50 seen_first int not null, 51 seen_last int not null) 52 }); 53 54 $db_handle->do(qq{ 55 create table if not exists friends ( 56 device integer not null, 57 friend text not null, 58 primary key(device, friend), 59 foreign key(device) references devices(num)) 60 }); 61 62 $db_handle->do(qq{ 63 create table if not exists mutual_friends ( 64 device integer not null, 65 mutual_friend integer not null, 66 primary key(device, mutual_friend), 67 foreign key(device) references devices(num), 68 foreign key(mutual_friend) references devices(num)) 69 }); 70 71 $db_handle->do(qq{ 72 create table if not exists list_members ( 73 list integer, 74 device integer, 75 joined int not null, 76 primary key(list, device), 77 foreign key(list) references lists(num), 78 foreign key(device) references devices(num)) 79 }); 80 81 $db_handle->do(qq{ 82 create table if not exists list_data ( 83 num integer primary key, 84 list integer, 85 name text, 86 owner integer, 87 status int not null default 0, 88 quantity int, 89 created int not null, 90 last_updated int not null, 91 foreign key(list) references lists(num), 92 foreign key(owner) references devices(num)) 93 }); 94 95 $db_handle->commit; 96 $self->{dbh}->disconnect(); 97 $self->{dbh} = undef; 98 } 99 100 sub prepare_stmt_handles { 101 my ($self) = @_; 102 103 my $dbh = $self->{dbh}; 104 my $sql; 105 106 # list table queries 107 $sql = 'select * from lists where num = ?'; 108 $self->{list_select} = $dbh->prepare($sql); 109 110 $sql = 'insert into lists (name, date, created, last_updated) values (?, ?, ?, ?)'; 111 $self->{new_list} = $dbh->prepare($sql); 112 113 $sql = qq{update lists set name = coalesce(?, name), 114 date = coalesce(?, date), last_updated = ? where num = ?}; 115 $self->{update_list} = $dbh->prepare($sql); 116 117 $sql = 'delete from lists where num = ?'; 118 $self->{delete_list} = $dbh->prepare($sql); 119 120 # devices table queries 121 $sql = 'insert into devices (id, phone_num, os, push_token, seen_first, seen_last) values (?, ?, ?, ?, ?, ?)'; 122 $self->{new_device} = $dbh->prepare($sql); 123 124 $sql = 'select * from devices where phone_num = ?'; 125 $self->{ph_num_exists} = $dbh->prepare($sql); 126 127 $sql = 'select * from devices where id = ?'; 128 $self->{select_device_id} = $dbh->prepare($sql); 129 130 $sql = 'update devices set push_token = coalesce(?, push_token) where num = ?'; 131 $self->{update_device} = $dbh->prepare($sql); 132 133 # friends table queries 134 $sql = 'insert or replace into friends (device, friend) values (?, ?)'; 135 $self->{friends_insert} = $dbh->prepare($sql); 136 137 $sql = 'select * from friends where device = ? and friend = ?'; 138 $self->{friends_select} = $dbh->prepare($sql); 139 140 $sql = 'delete from friends where device = ? and friend = ?'; 141 $self->{friends_delete} = $dbh->prepare($sql); 142 143 # mutual_friends table queries 144 $sql = 'insert or replace into mutual_friends (device, mutual_friend) values (?, ?)'; 145 $self->{mutual_friend_insert} = $dbh->prepare($sql); 146 147 $sql = qq{select devices.num, devices.phone_num, devices.os, devices.push_token 148 from devices, mutual_friends 149 where devices.num = mutual_friends.mutual_friend 150 and mutual_friends.device = ?}; 151 $self->{mutual_friend_select} = $dbh->prepare($sql); 152 153 $sql = qq{select devices.os, devices.push_token from devices, mutual_friends 154 where devices.num = mutual_friends.mutual_friend 155 and mutual_friends.device = ? and devices.push_token is not null}; 156 $self->{mutual_friend_notify_select} = $dbh->prepare($sql); 157 158 $sql = 'delete from mutual_friends where device = ? and mutual_friend = ?'; 159 $self->{mutual_friends_delete} = $dbh->prepare($sql); 160 161 # lists/list_members compound queries 162 $sql = qq{select lists.num, lists.name, lists.date from lists, list_members where 163 lists.num = list_members.list and list_members.device = ?}; 164 $self->{get_lists} = $dbh->prepare($sql); 165 166 $sql = qq{select devices.phone_num from devices, list_members 167 where devices.num = list_members.device and list_members.list = ?}; 168 $self->{list_members_phnums} = $dbh->prepare($sql); 169 170 $sql = qq{select devices.os, devices.push_token from devices, list_members 171 where devices.num = list_members.device and list_members.list = ? 172 and list_members.device != ?}; 173 $self->{select_list_members} = $dbh->prepare($sql); 174 175 # list_members table queries 176 $sql = 'select device from list_members where list = ?'; 177 $self->{get_list_members} = $dbh->prepare($sql); 178 179 $sql = 'insert into list_members (list, device, joined) values (?, ?, ?)'; 180 $self->{new_list_member} = $dbh->prepare($sql); 181 182 $sql = 'delete from list_members where list = ? and device = ?'; 183 $self->{remove_list_member} = $dbh->prepare($sql); 184 185 $sql = 'select device from list_members where list = ? and device = ?'; 186 $self->{check_list_member} = $dbh->prepare($sql); 187 188 $sql = qq{select list from list_members where device = ? except 189 select list from list_members where device = ?}; 190 $self->{get_other_lists} = $dbh->prepare($sql); 191 192 # list_data table queries 193 $sql = 'delete from list_data where list = ?'; 194 $self->{delete_list_data} = $dbh->prepare($sql); 195 196 $sql = 'select * from list_data where list = ?'; 197 $self->{get_list_items} = $dbh->prepare($sql); 198 199 $sql = 'insert into list_data (list, name, quantity, created, last_updated) values (?, ?, ?, ?, ?)'; 200 $self->{new_list_item} = $dbh->prepare($sql); 201 } 202 203 1;