commit 53b59e98e0fca52a7bc550043d3cbfc229969a43
parent d67aabff3e4f1d033d689249316ebeb3b0314e8d
Author: kyle <kyle@getaddrinfo.net>
Date: Sat, 13 Feb 2016 19:16:11 -0700
sl: move logger and database classes into their own files
- sl is getting close to 1000 lines so start splitting out functionality
Diffstat:
| A | server/database.pm | | | 203 | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| A | server/logger.pm | | | 48 | ++++++++++++++++++++++++++++++++++++++++++++++++ |
| M | server/sl | | | 255 | +++---------------------------------------------------------------------------- |
3 files changed, 258 insertions(+), 248 deletions(-)
diff --git a/server/database.pm b/server/database.pm
@@ -0,0 +1,203 @@
+package database;
+
+use DBI;
+use File::Temp;
+
+sub new {
+ my $class = shift;
+ my $db_file = shift;
+
+ my $self = {};
+ bless ($self, $class);
+
+ $self->{dbh} = DBI->connect(
+ "dbi:SQLite:dbname=$db_file",
+ "", "",
+ {
+ RaiseError => 1,
+ sqlite_unicode => 1,
+ }
+ ) or die $DBI::errstr;
+
+ $self->{dbh}->do("PRAGMA foreign_keys = ON");
+ $self->{dbh}->{AutoCommit} = 1;
+
+ return $self;
+}
+
+sub create_tables {
+ my ($self) = @_;
+
+ my $db_handle = $self->{dbh};
+ $db_handle->begin_work;
+
+ $db_handle->do(qq{
+ create table if not exists lists (
+ num integer primary key,
+ name text not null,
+ date int,
+ created int not null,
+ last_updated int not null)
+ });
+
+ $db_handle->do(qq{
+ create table if not exists devices (
+ num integer primary key,
+ id text not null,
+ phone_num text not null,
+ os text,
+ push_token text,
+ seen_first int not null,
+ seen_last int not null)
+ });
+
+ $db_handle->do(qq{
+ create table if not exists friends (
+ device integer not null,
+ friend text not null,
+ primary key(device, friend),
+ foreign key(device) references devices(num))
+ });
+
+ $db_handle->do(qq{
+ create table if not exists mutual_friends (
+ device integer not null,
+ mutual_friend integer not null,
+ primary key(device, mutual_friend),
+ foreign key(device) references devices(num),
+ foreign key(mutual_friend) references devices(num))
+ });
+
+ $db_handle->do(qq{
+ create table if not exists list_members (
+ list integer,
+ device integer,
+ joined int not null,
+ primary key(list, device),
+ foreign key(list) references lists(num),
+ foreign key(device) references devices(num))
+ });
+
+ $db_handle->do(qq{
+ create table if not exists list_data (
+ num integer primary key,
+ list integer,
+ name text,
+ owner integer,
+ status int not null default 0,
+ quantity,
+ created int not null,
+ last_updated int not null,
+ foreign key(list) references lists(num),
+ foreign key(owner) references devices(num))
+ });
+
+ $db_handle->commit;
+ $self->{dbh}->disconnect();
+ $self->{dbh} = undef;
+}
+
+sub prepare_stmt_handles {
+ my ($self) = @_;
+
+ my $dbh = $self->{dbh};
+ my $sql;
+
+ # list table queries
+ $sql = 'select * from lists where num = ?';
+ $self->{list_select} = $dbh->prepare($sql);
+
+ $sql = 'insert into lists (name, date, created, last_updated) values (?, ?, ?, ?)';
+ $self->{new_list} = $dbh->prepare($sql);
+
+ $sql = qq{update lists set name = coalesce(?, name),
+ date = coalesce(?, date), last_updated = ? where num = ?};
+ $self->{update_list} = $dbh->prepare($sql);
+
+ $sql = 'delete from lists where num = ?';
+ $self->{delete_list} = $dbh->prepare($sql);
+
+ # devices table queries
+ $sql = 'insert into devices (id, phone_num, os, push_token, seen_first, seen_last) values (?, ?, ?, ?, ?, ?)';
+ $self->{new_device} = $dbh->prepare($sql);
+
+ $sql = 'select * from devices where phone_num = ?';
+ $self->{ph_num_exists} = $dbh->prepare($sql);
+
+ $sql = 'select * from devices where id = ?';
+ $self->{select_device_id} = $dbh->prepare($sql);
+
+ $sql = 'update devices set push_token = coalesce(?, push_token) where num = ?';
+ $self->{update_device} = $dbh->prepare($sql);
+
+ # friends table queries
+ $sql = 'insert or replace into friends (device, friend) values (?, ?)';
+ $self->{friends_insert} = $dbh->prepare($sql);
+
+ $sql = 'select * from friends where device = ? and friend = ?';
+ $self->{friends_select} = $dbh->prepare($sql);
+
+ $sql = 'delete from friends where device = ? and friend = ?';
+ $self->{friends_delete} = $dbh->prepare($sql);
+
+ # mutual_friends table queries
+ $sql = 'insert or replace into mutual_friends (device, mutual_friend) values (?, ?)';
+ $self->{mutual_friend_insert} = $dbh->prepare($sql);
+
+ $sql = qq{select devices.num, devices.phone_num, devices.os, devices.push_token
+ from devices, mutual_friends
+ where devices.num = mutual_friends.mutual_friend
+ and mutual_friends.device = ?};
+ $self->{mutual_friend_select} = $dbh->prepare($sql);
+
+ $sql = qq{select devices.os, devices.push_token from devices, mutual_friends
+ where devices.num = mutual_friends.mutual_friend
+ and mutual_friends.device = ? and devices.push_token is not null};
+ $self->{mutual_friend_notify_select} = $dbh->prepare($sql);
+
+ $sql = 'delete from mutual_friends where device = ? and mutual_friend = ?';
+ $self->{mutual_friends_delete} = $dbh->prepare($sql);
+
+ # lists/list_members compound queries
+ $sql = qq{select lists.num, lists.name, lists.date from lists, list_members where
+ lists.num = list_members.list and list_members.device = ?};
+ $self->{get_lists} = $dbh->prepare($sql);
+
+ $sql = qq{select devices.phone_num from devices, list_members
+ where devices.num = list_members.device and list_members.list = ?};
+ $self->{list_members_phnums} = $dbh->prepare($sql);
+
+ $sql = qq{select devices.os, devices.push_token from devices, list_members
+ where devices.num = list_members.device and list_members.list = ?
+ and list_members.device != ?};
+ $self->{select_list_members} = $dbh->prepare($sql);
+
+ # list_members table queries
+ $sql = 'select device from list_members where list = ?';
+ $self->{get_list_members} = $dbh->prepare($sql);
+
+ $sql = 'insert into list_members (list, device, joined) values (?, ?, ?)';
+ $self->{new_list_member} = $dbh->prepare($sql);
+
+ $sql = 'delete from list_members where list = ? and device = ?';
+ $self->{remove_list_member} = $dbh->prepare($sql);
+
+ $sql = 'select device from list_members where list = ? and device = ?';
+ $self->{check_list_member} = $dbh->prepare($sql);
+
+ $sql = qq{select list from list_members where device = ? except
+ select list from list_members where device = ?};
+ $self->{get_other_lists} = $dbh->prepare($sql);
+
+ # list_data table queries
+ $sql = 'delete from list_data where list = ?';
+ $self->{delete_list_data} = $dbh->prepare($sql);
+
+ $sql = 'select * from list_data where list = ?';
+ $self->{get_list_items} = $dbh->prepare($sql);
+
+ $sql = 'insert into list_data (list, name, quantity, status, owner, last_updated) values (?, ?, ?, ?, ?, ?)';
+ $self->{new_list_item} = $dbh->prepare($sql);
+}
+
+1;
diff --git a/server/logger.pm b/server/logger.pm
@@ -0,0 +1,48 @@
+package logger;
+use POSIX;
+
+sub new {
+ my $class = shift;
+
+ my $self = {};
+ bless ($self, $class);
+
+ $self->{addr} = '';
+ $self->{port} = '';
+ $self->{msg_type} = '';
+ return $self;
+}
+
+sub set_peer_host_port {
+ my ($self, $sock) = @_;
+ ($self->{addr}, $self->{port}) = ($sock->peerhost(), $sock->peerport());
+}
+
+sub set_msg {
+ my ($self, $msg_type) = @_;
+
+ if ($msg_type ne '') {
+ $self->{msg_type} = "$msg_type: ";
+ } else {
+ $self->{msg_type} = '';
+ }
+}
+
+sub print {
+ my ($self, @args) = @_;
+
+ my $ftime = strftime("%F %T", localtime);
+ printf "%s %-15s %-5s> %s", $ftime, $self->{addr}, $self->{port}, $self->{msg_type};
+ # we print potentially unsafe strings here, don't use printf
+ print @args;
+}
+
+sub print_bare {
+ my ($self, @args) = @_;
+
+ my $ftime = strftime("%F %T", localtime);
+ printf "%s> ", $ftime;
+ printf @args;
+}
+
+1;
diff --git a/server/sl b/server/sl
@@ -4,7 +4,6 @@ use strict;
use BSD::arc4random qw(arc4random_bytes arc4random_stir);
use Digest::SHA qw(sha256_base64);
-use DBI;
use File::Temp;
use Getopt::Std;
use IO::Socket::SSL;
@@ -13,19 +12,24 @@ use JSON::XS;
use Scalar::Util qw(looks_like_number);
use Try::Tiny;
+use logger;
+use database;
+
require "msgs.pl";
our (%msg_num, @msg_str, @msg_func, $protocol_ver);
my %args;
getopts("p:t", \%args);
+# This is used by the parent to create the database if it doesn't already
+# exist, and then each child connects to this db to do queries
my $db_file = "db";
$db_file = File::Temp->new(SUFFIX => '.db', EXLOCK => 0) if ($args{t});
my $log = logger->new();
$log->print_bare("using database '$db_file'\n");
-my $db = database->new();
+my $db = database->new($db_file);
$db->create_tables();
my $server_socket = new IO::Socket::INET (
@@ -74,7 +78,7 @@ while (my $client_socket = $server_socket->accept()) {
my $ssl_cipher = $client_socket->get_cipher();
$log->print("ssl ok, ver = '$ssl_ver' cipher = '$ssl_cipher'\n");
- my $db = database->new();
+ my $db = database->new($db_file);
$db->prepare_stmt_handles();
while (1) {
@@ -746,248 +750,3 @@ sub device_id_valid {
$log->print("unknown device '$device_id'\n");
return ('the client sent an unknown device id');
}
-
-package database;
-
-sub new {
- my $class = shift;
-
- my $self = {};
- bless ($self, $class);
-
- $self->{dbh} = DBI->connect(
- "dbi:SQLite:dbname=$db_file",
- "", "",
- {
- RaiseError => 1,
- sqlite_unicode => 1,
- }
- ) or die $DBI::errstr;
-
- $self->{dbh}->do("PRAGMA foreign_keys = ON");
- $self->{dbh}->{AutoCommit} = 1;
-
- return $self;
-}
-
-sub create_tables {
- my ($self) = @_;
-
- my $db_handle = $self->{dbh};
- $db_handle->begin_work;
-
- $db_handle->do(qq{
- create table if not exists lists (
- num integer primary key,
- name text not null,
- date int,
- created int not null,
- last_updated int not null)
- });
-
- $db_handle->do(qq{
- create table if not exists devices (
- num integer primary key,
- id text not null,
- phone_num text not null,
- os text,
- push_token text,
- seen_first int not null,
- seen_last int not null)
- });
-
- $db_handle->do(qq{
- create table if not exists friends (
- device integer not null,
- friend text not null,
- primary key(device, friend),
- foreign key(device) references devices(num))
- });
-
- $db_handle->do(qq{
- create table if not exists mutual_friends (
- device integer not null,
- mutual_friend integer not null,
- primary key(device, mutual_friend),
- foreign key(device) references devices(num),
- foreign key(mutual_friend) references devices(num))
- });
-
- $db_handle->do(qq{
- create table if not exists list_members (
- list integer,
- device integer,
- joined int not null,
- primary key(list, device),
- foreign key(list) references lists(num),
- foreign key(device) references devices(num))
- });
-
- $db_handle->do(qq{
- create table if not exists list_data (
- num integer primary key,
- list integer,
- name text,
- owner integer,
- status int not null default 0,
- quantity,
- created int not null,
- last_updated int not null,
- foreign key(list) references lists(num),
- foreign key(owner) references devices(num))
- });
-
- $db_handle->commit;
- $self->{dbh}->disconnect();
- $self->{dbh} = undef;
-}
-
-sub prepare_stmt_handles {
- my ($self) = @_;
-
- my $dbh = $self->{dbh};
- my $sql;
-
- # list table queries
- $sql = 'select * from lists where num = ?';
- $self->{list_select} = $dbh->prepare($sql);
-
- $sql = 'insert into lists (name, date, created, last_updated) values (?, ?, ?, ?)';
- $self->{new_list} = $dbh->prepare($sql);
-
- $sql = qq{update lists set name = coalesce(?, name),
- date = coalesce(?, date), last_updated = ? where num = ?};
- $self->{update_list} = $dbh->prepare($sql);
-
- $sql = 'delete from lists where num = ?';
- $self->{delete_list} = $dbh->prepare($sql);
-
- # devices table queries
- $sql = 'insert into devices (id, phone_num, os, push_token, seen_first, seen_last) values (?, ?, ?, ?, ?, ?)';
- $self->{new_device} = $dbh->prepare($sql);
-
- $sql = 'select * from devices where phone_num = ?';
- $self->{ph_num_exists} = $dbh->prepare($sql);
-
- $sql = 'select * from devices where id = ?';
- $self->{select_device_id} = $dbh->prepare($sql);
-
- $sql = 'update devices set push_token = coalesce(?, push_token) where num = ?';
- $self->{update_device} = $dbh->prepare($sql);
-
- # friends table queries
- $sql = 'insert or replace into friends (device, friend) values (?, ?)';
- $self->{friends_insert} = $dbh->prepare($sql);
-
- $sql = 'select * from friends where device = ? and friend = ?';
- $self->{friends_select} = $dbh->prepare($sql);
-
- $sql = 'delete from friends where device = ? and friend = ?';
- $self->{friends_delete} = $dbh->prepare($sql);
-
- # mutual_friends table queries
- $sql = 'insert or replace into mutual_friends (device, mutual_friend) values (?, ?)';
- $self->{mutual_friend_insert} = $dbh->prepare($sql);
-
- $sql = qq{select devices.num, devices.phone_num, devices.os, devices.push_token
- from devices, mutual_friends
- where devices.num = mutual_friends.mutual_friend
- and mutual_friends.device = ?};
- $self->{mutual_friend_select} = $dbh->prepare($sql);
-
- $sql = qq{select devices.os, devices.push_token from devices, mutual_friends
- where devices.num = mutual_friends.mutual_friend
- and mutual_friends.device = ? and devices.push_token is not null};
- $self->{mutual_friend_notify_select} = $dbh->prepare($sql);
-
- $sql = 'delete from mutual_friends where device = ? and mutual_friend = ?';
- $self->{mutual_friends_delete} = $dbh->prepare($sql);
-
- # lists/list_members compound queries
- $sql = qq{select lists.num, lists.name, lists.date from lists, list_members where
- lists.num = list_members.list and list_members.device = ?};
- $self->{get_lists} = $dbh->prepare($sql);
-
- $sql = qq{select devices.phone_num from devices, list_members
- where devices.num = list_members.device and list_members.list = ?};
- $self->{list_members_phnums} = $dbh->prepare($sql);
-
- $sql = qq{select devices.os, devices.push_token from devices, list_members
- where devices.num = list_members.device and list_members.list = ?
- and list_members.device != ?};
- $self->{select_list_members} = $dbh->prepare($sql);
-
- # list_members table queries
- $sql = 'select device from list_members where list = ?';
- $self->{get_list_members} = $dbh->prepare($sql);
-
- $sql = 'insert into list_members (list, device, joined) values (?, ?, ?)';
- $self->{new_list_member} = $dbh->prepare($sql);
-
- $sql = 'delete from list_members where list = ? and device = ?';
- $self->{remove_list_member} = $dbh->prepare($sql);
-
- $sql = 'select device from list_members where list = ? and device = ?';
- $self->{check_list_member} = $dbh->prepare($sql);
-
- $sql = qq{select list from list_members where device = ? except
- select list from list_members where device = ?};
- $self->{get_other_lists} = $dbh->prepare($sql);
-
- # list_data table queries
- $sql = 'delete from list_data where list = ?';
- $self->{delete_list_data} = $dbh->prepare($sql);
-
- $sql = 'select * from list_data where list = ?';
- $self->{get_list_items} = $dbh->prepare($sql);
-
- $sql = 'insert into list_data (list, name, quantity, status, owner, last_updated) values (?, ?, ?, ?, ?, ?)';
- $self->{new_list_item} = $dbh->prepare($sql);
-}
-
-package logger;
-use POSIX;
-
-sub new {
- my $class = shift;
-
- my $self = {};
- bless ($self, $class);
-
- $self->{addr} = '';
- $self->{port} = '';
- $self->{msg_type} = '';
- return $self;
-}
-
-sub set_peer_host_port {
- my ($self, $sock) = @_;
- ($self->{addr}, $self->{port}) = ($sock->peerhost(), $sock->peerport());
-}
-
-sub set_msg {
- my ($self, $msg_type) = @_;
-
- if ($msg_type ne '') {
- $self->{msg_type} = "$msg_type: ";
- } else {
- $self->{msg_type} = '';
- }
-}
-
-sub print {
- my ($self, @args) = @_;
-
- my $ftime = strftime("%F %T", localtime);
- printf "%s %-15s %-5s> %s", $ftime, $self->{addr}, $self->{port}, $self->{msg_type};
- # we print potentially unsafe strings here, don't use printf
- print @args;
-}
-
-sub print_bare {
- my ($self, @args) = @_;
-
- my $ftime = strftime("%F %T", localtime);
- printf "%s> ", $ftime;
- printf @args;
-}
