From e9192b3d42660a5781101df4357d276318151e8a Mon Sep 17 00:00:00 2001 From: Jules Laplace Date: Fri, 2 Aug 2013 17:14:41 -0500 Subject: cgi-bin & lib --- lib/db.pm | 2400 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 2400 insertions(+) create mode 100644 lib/db.pm (limited to 'lib/db.pm') diff --git a/lib/db.pm b/lib/db.pm new file mode 100644 index 0000000..90aaf08 --- /dev/null +++ b/lib/db.pm @@ -0,0 +1,2400 @@ + +our @FILE_KEYS = qw(id username thread filename title date size private ); +our @FILE_KEYS_KEYWORD = qw(id username thread filename title date size private keyword); +our $BUCKY_DB_TABLE_USERS = 'users'; +our $BUCKY_DB_TABLE_KEYWORDS = 'keywords'; +our $BUCKY_DB_TABLE_THREADS = 'threads'; +our $BUCKY_DB_TABLE_COMMENTS = 'comments'; +our $BUCKY_DB_TABLE_FILES = 'files'; +our $BUCKY_DB_TABLE_THREADSTAGS = 'threadstags'; +our $BUCKY_DB_TABLE_TAGS = 'tags'; +our $BUCKY_DB_TABLE_INVITES = 'invites'; + +our @BUCKY_DB_COLUMNS_USERS = + qw(id username password realname email aim grass ulevel firstseen lastseen lastsession timezone stickies sink display boxes); +our @BUCKY_DB_COLUMNS_THREADSTAGS = + qw(threadid tagid username createdate); +our @BUCKY_DB_COLUMNS_TAGS = + qw(id tag createdate owner ops public display); +our @BUCKY_DB_COLUMNS_THREADS = + qw(title username keyword createdate lastmodified size private allowed color display viewed revision); + +our @BUCKY_DB_COLUMNS_INVITES = + qw(id hash state attest created expired username password realname email grass keywords); +our @BUCKY_INVITE_STATES = + qw(approved active request expired rejected redeemed); +our $BUCKY_INVITE_REQUEST = 2; +our $BUCKY_INVITE_ACTIVE = 1; +our $BUCKY_INVITE_APPROVED = 0; +our $BUCKY_INVITE_REDEEMED = -1; +our $BUCKY_INVITE_REJECTED = -2; +our $BUCKY_INVITE_EXPIRED = -3; + +our $BUCKY_DB_ERROR_INSERT_USERS = 'failed to add user!'; +our $BUCKY_DB_ERROR_INSERT_INVITES = 'failed to add invite!'; +our $BUCKY_DB_ERROR_INSERT_TAGS = 'failed to add tag!'; +our $BUCKY_DB_ERROR_INSERT_THREADS = 'failed to add post!'; +our $BUCKY_DB_ERROR_INSERT_THREADSTAGS = 'failed to add tag to post!'; + +sub new_user + { + my ($new_user) = @_; + my $data; + $data->{row}->{username} = $new_user->{username}; + $data->{row}->{password} = $new_user->{password}; # should already be encrypted + $data->{row}->{email} = $new_user->{email}; + $data->{row}->{grass} = $new_user->{grass}; + $data->{row}->{realname} = $new_user->{realname}; +# $data->{row}->{boxes} = $dbh->quote($BUCKY_DEFAULT_BOXES); + $data->{row}->{boxes} = $BUCKY_DEFAULT_BOXES; + $data->{row}->{ulevel} = 1; + $data->{row}->{firstseen} = time; + $data->{row}->{lastseen} = time; + $data->{row}->{lastsession} = time; + $data->{row}->{timezone} = -8; + $data->{table} = $BUCKY_DB_TABLE_USERS; + $data->{columns} = \@BUCKY_DB_COLUMNS_USERS; + $data->{error} = $BUCKY_DB_ERROR_INSERT_USERS; + return add_row_by_hash( $data ); + } + +sub get_user + { + my ($uname) = @_; + my @fields = qw(id username password ulevel lastseen lastsession timezone stickies sink display boxes); + my @row; + my %temphash; + + $uname = $dbh->quote($uname); + + $query = "SELECT "; + foreach my $k (@fields) + { + $query .= $k; + if (++$i != @fields) + { $query .= ","; } + } + $query .= " FROM users WHERE username=$uname"; + + $sth = $dbh->prepare($query); + $sth->execute(); + @row = $sth->fetchrow_array(); + $sth->finish (); + + if (@row == 0) + { return -1; } + + for (my $i = 0; $i < @row; $i++) + { + $temphash{$fields[$i]} = $row[$i]; + } + + return \%temphash; + } + +sub get_uid + { + my ($name) = @_; + my $user_id; + my $rows = 0; + $name = $dbh->quote($name); + my $query = "SELECT id FROM users WHERE username = $name"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + $user_id = $row[0]; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No such user!\n" if ($DEBUG); + return -1; + } + return $user_id; + } + +sub get_all_users + { + my ($limit) = @_; + my @rows; + my $rows = 0; + my @keys = @BUCKY_DB_COLUMNS_USERS; + my $keylist = join ",", @keys; + my $query = "SELECT $keylist FROM users WHERE ulevel > -1"; + $query .= " AND lastseen > $limit" if (defined($limit)); + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + print $row[0]." " if ($DEBUG); + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No users!\n" if ($DEBUG); + return -1; + } + + my $ccount = count_comments_by_user(); + my $fcount = count_files_by_user(); + + foreach my $user (@rows) + { + my $this_user = $user->{username}; + $user->{ccount} = exists($ccount->{$this_user}) ? $ccount->{$this_user} : 0; + $user->{fcount} = exists($fcount->{$this_user}) ? $fcount->{$this_user} : 0; + } + return \@rows; + } + +sub count_files_by_user + { + my %temphash; + my $query = "SELECT username,COUNT(*) FROM files GROUP BY username"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + $temphash{"$row[0]"} = $row[1]; + } + $sth->finish(); + return \%temphash; + } + +sub count_comments_by_user + { + my %temphash; + my $query = "SELECT username,COUNT(*) FROM comments GROUP BY username"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + $temphash{"$row[0]"} = $row[1]; + } + $sth->finish(); + return \%temphash; + } + +sub get_username + { + my ($id) = @_; + my $username; + $id = $dbh->quote($id); + my $query = "SELECT username FROM users WHERE id = $id"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($username) = $sth->fetchrow_array(); + $sth->finish (); + return $username; + } + +sub get_user_profile + { + my ($uname) = @_; + my @fields = qw[username realname email aim phone location timezone lastseen display boxes stickies sink]; + my @row; + my %temphash; + $uname = $dbh->quote($uname); + my $query = "SELECT username,realname,email,aim,phone,location,timezone,lastseen,display,boxes,stickies,sink FROM users WHERE username=$uname"; + $sth = $dbh->prepare($query); + $sth->execute(); + @row = $sth->fetchrow_array(); + $sth->finish (); + for (my $i = 0; $i < @row; $i++) + { + $temphash{$fields[$i]} = $row[$i]; + } + return \%temphash; + } + +sub update_profile + { + my ($uname, $prof) = @_; + my %q; + my $i = 0; + my $query; + my @fields = qw[realname email aim phone location timezone display stickies sink]; + + $q{date} = $dbh->quote(time); + $uname = $dbh->quote($uname); + + foreach my $key (@fields) + { + $q{$key} = $dbh->quote($$prof{$key}); + } + + $query = "UPDATE users SET "; + foreach my $k (@fields) + { + $query .= $k."=".$q{$k}; + if (++$i != @fields) + { $query .= ","; } + } + $query .= " WHERE username=$uname"; + + my $rows = $dbh->do($query); + + if ($rows == 0) + { print "failed to update profile !"; return 0; } + + return 1; + } + +sub update_user_boxes + { + my ($uname, $boxes) = @_; + $uname = $dbh->quote($uname); + $boxes = $dbh->quote($boxes); + $query = "UPDATE users SET boxes=$boxes WHERE username=$uname"; + $dbh->do($query); + } + +sub update_lastsession + { + my ($uname) = @_; + $uname = $dbh->quote($uname); + $query = "UPDATE users SET lastsession=lastseen WHERE username=$uname"; + $dbh->do($query); + } + +sub update_password + { + my ($uname, $newpass) = @_; + $uname = $dbh->quote($uname); + $newpass = $dbh->quote($newpass); + $query = "UPDATE users SET password=$newpass WHERE username=$uname"; + $dbh->do($query); + } + +sub touch_user + { + my ($uname) = @_; + my $t = time; + + $t = $dbh->quote($t); + $uname = $dbh->quote($uname); + my $query = "UPDATE users SET lastseen=$t WHERE id=$uname"; + my $rows = $dbh->do($query); + + if ($rows == undef) + { error("failed to touch user $uname !"); } + + return 1; + } + +sub update_user_sticky + { + my ($uname, $stick) = @_; + + $uname = $dbh->quote($uname); + $stick = $dbh->quote($stick); + my $query = "UPDATE users SET stickies=$stick WHERE username=$uname"; + print $query if ($DEBUG); + my $rows = $dbh->do($query); + } + +sub update_user_sink + { + my ($uname, $sink) = @_; + + $uname = $dbh->quote($uname); + $sink = $dbh->quote($sink); + my $query = "UPDATE users SET sink=$sink WHERE username=$uname"; + print $query if ($DEBUG); + my $rows = $dbh->do($query); + } + +sub get_lastlog + { + my @rows; + my $rows = 0; + my @keys = qw[username lastseen]; + my $query = "SELECT username,lastseen FROM users WHERE ulevel > 0 ORDER BY lastseen DESC LIMIT 6"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No users!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } + +######################################### +# INVITES +######################################### + +sub new_invite + { + my ($invitehash) = @_; + my $data; + $data->{row} = $invitehash; + $data->{row}->{created} = time; + $data->{row}->{expired} = time + 86400*7; + return add_invite_by_hash( $data ); + } + +sub add_invite_by_hash + { + my ($data) = @_; + $data->{table} = $BUCKY_DB_TABLE_INVITES; + $data->{columns} = \@BUCKY_DB_COLUMNS_INVITES; + $data->{error} = $BUCKY_DB_ERROR_INSERT_INVITES; + return add_row_by_hash( $data ); + } + +sub set_invite_state + { + my ($invite, $state) = @_; + + if (! ref($invite)) + { return -1; } + + $invite->{state} = $state; + + $id = $dbh->quote($invite->{id}); + $state = $dbh->quote($state); + $query = "UPDATE $BUCKY_DB_TABLE_INVITES SET state=$state WHERE id=$id"; + print $query."
" if $DEBUG; + $dbh->do($query); + } + +sub set_invite_expired + { + my ($invite, $expired) = @_; + + if (! ref($invite)) + { return -1; } + + $invite->{expired} = $expired; + + $id = $dbh->quote($invite->{id}); + $expired = $dbh->quote($expired); + $query = "UPDATE $BUCKY_DB_TABLE_INVITES SET expired=$expired WHERE id=$id"; + print $query."
" if $DEBUG; + $dbh->do($query); + } + +sub set_invite_username + { + my ($invite, $username) = @_; + + if (! ref($invite)) + { return -1; } + + $invite->{username} = $username; + + $id = $dbh->quote($invite->{id}); + $username = $dbh->quote($username); + $query = "UPDATE $BUCKY_DB_TABLE_INVITES SET username=$username WHERE id=$id"; + print $query."
" if $DEBUG; + $dbh->do($query); + } + +sub get_invite_from_id + { + my ($id) = @_; + my @rows; + my $query; + my @columns = @BUCKY_DB_COLUMNS_INVITES; + my $joined_keys = join ",", @columns; + my $rows = 0; + my %finalhash; + + $id = $dbh->quote($id); + $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE id=$id"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + if ((@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $finalhash{$columns[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No such invite!\n" if ($DEBUG); + return -1; + } + + return \%finalhash; + } + +sub get_invite_from_hash + { + my ($hash) = @_; + my @rows; + my $query; + my @columns = @BUCKY_DB_COLUMNS_INVITES; + my $joined_keys = join ",", @columns; + my $rows = 0; + my %finalhash; + + $hash = $dbh->quote($hash); + $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE hash=$hash"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + if ((@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $finalhash{$columns[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No such invite!\n" if ($DEBUG); + return -1; + } + + return \%finalhash; + } + +sub get_active_invites + { + my $query; + my @columns = @BUCKY_DB_COLUMNS_INVITES; + my $joined_keys = join ",", @columns; + my $rows = 0; + my @finalarray; + my $now = $dbh->quote(time); + + $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE expired > $now"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$columns[$i]} = $row[$i]; + } + push @finalarray, \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No invites!\n" if ($DEBUG); + return -1; + } + + return \@finalarray; + } + +sub get_invites_by_user + { + my ($username) = @_; + + my $query; + my @columns = @BUCKY_DB_COLUMNS_INVITES; + my $joined_keys = join ",", @columns; + my $rows = 0; + my @finalarray; + my $username = $dbh->quote($username); + + $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE attest=$username"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$columns[$i]} = $row[$i]; + } + push @finalarray, \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No invites!\n" if ($DEBUG); + return -1; + } + + return \@finalarray; + } + +sub count_active_user_invites + { + my ($username) = @_; + my $count = 0; + $username = $dbh->quote($username); + my $query = "SELECT COUNT(*) FROM $BUCKY_DB_TABLE_INVITES WHERE attest=$username AND state > 0"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($count) = $sth->fetchrow_array(); + $sth->finish (); + return $count; + } + +######################################### +# BOXES +######################################### + +sub get_boxes + { + my ($uname) = @_; + my @rows; + my $query; + my @keys = qw(id mbox owner mcount editable); + my $rows = 0; + + $uname = $dbh->quote($uname); + $query = "SELECT id,mbox,owner,mcount,editable FROM boxes WHERE owner=$uname"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No messages!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } + +sub recount_mailbox + { + my ($box) = @_; + + my $count = count_messages($box); + + $box = $dbh->quote($box); + $count = $dbh->quote($count); + my $query = "UPDATE boxes SET mcount=$count WHERE mbox=$box"; + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to tweez mcount of $box!"); } + + return 1; + } + +sub count_messages + { + my ($box) = @_; + my $count = 0; + $box = $dbh->quote($box); + my $query = "SELECT COUNT(*) FROM messages WHERE mbox=$box"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($count) = $sth->fetchrow_array(); + $sth->finish (); + return $count; + } + +sub get_messages + { + my ($mbox, $limit, $startdate) = @_; + my @rows; + my $query; + my @keys = qw(id mbox unread sender recipient date subject size); + my $rows = 0; + + $mbox = $dbh->quote($mbox); + $query = "SELECT id,mbox,unread,sender,recipient,date,subject,length(body) FROM messages WHERE mbox=$mbox"; + + if ($startdate && $startdate ne "now") + { + $query .= " AND (date < $startdate)"; + } + $query .= " ORDER BY date DESC"; + if ($limit) + { + $query .= " LIMIT $limit"; + } + + print "$query
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No messages!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } + +sub add_mailbox + { + my ($box, $name, $editable) = @_; + $box = $dbh->quote($box); + $name = $dbh->quote($name); + $editable = $dbh->quote($editable); + my $query = "INSERT boxes (mbox,owner,editable) VALUES($box,$name,$editable)"; + my $rows = $dbh->do($query); + return $rows; + } + +######################################### +# MESSAGES +######################################### + +sub unflag_message + { + my ($id) = @_; + + $id = $dbh->quote($id); + my $query = "UPDATE messages SET unread=0 WHERE id=$id"; + my $rows = $dbh->do($query); + + if ($rows == undef) + { error("failed to unread $id !"); } + + return 1; + } + +sub get_message + { + my ($id) = @_; + my @row; + my @keys = qw(id mbox unread sender recipient date subject body); + my $rows = 0; + my %temphash; + $id = $dbh->quote($id); + my $query = "SELECT id,mbox,unread,sender,recipient,date,subject,body FROM messages WHERE id=$id"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish (); + if ($rows == 0) + { + print "No such message!\n" if ($DEBUG); + return -1; + } + return \%temphash; + } + +sub count_new_messages + { + my ($uname) = @_; + my $count = 0; + $uname = $dbh->quote($uname); + $ur = $dbh->quote("1"); + my $query = "SELECT COUNT(*) FROM messages WHERE recipient=$uname AND unread=$ur"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($count) = $sth->fetchrow_array(); + $sth->finish (); + return $count; + } + +sub new_message + { + my ($mbox, $m) = @_; + my %q; + + $q{date} = $dbh->quote(time); + + $qmbox = $dbh->quote($mbox); + foreach my $key (keys %$m) + { + $q{$key} = $dbh->quote($$m{$key}); + } + my $query = "INSERT messages (mbox,unread,sender,recipient,date,subject,body) VALUES($qmbox,$q{unread},$q{sender},$q{recipient},$q{date},$q{subject},$q{body})"; + my $rows = $dbh->do($query); + if ($rows == 0) + { print "failed to send message !"; return 0; } + + recount_mailbox($mbox); + return 1; + } + +sub update_message + { + my ($id, $m) = @_; + my %q; + my $i = 0; + my $query; + my @fields = qw(sender recipient date subject body); + $q{date} = $dbh->quote(time); + + $id = $dbh->quote($id); + + foreach my $key (keys %$m) + { + $q{$key} = $dbh->quote($m->{$key}); + } + + $query = "UPDATE messages SET "; + foreach my $k (@fields) + { + $query .= $k."=".$q{$k}; + if (++$i != 5) + { $query .= ","; } + } + $query .= " WHERE id=$id"; + + my $rows = $dbh->do($query); + + if ($rows == 0) + { print "failed to update message !"; return 0; } + + return 1; + } + + +sub delete_message + { + my ($id) = @_; + $id = $dbh->quote($id); + my $query = "DELETE FROM messages WHERE id=$id"; + my $rows = $dbh->do($query); + } +######################################### +# FILES +######################################### + +sub get_file + { + my ($id) = @_; + my @rows; + my $rows = 0; + my $query; + my %temphash; + + $id = $dbh->quote($id); + my $keys = join ",", @FILE_KEYS; + $query = "SELECT $keys FROM files WHERE id=$id"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No files!\n" if ($DEBUG); + return -1; + } + + return \%temphash; + } + +sub get_file_from_filename + { + my ($filename) = @_; + my @rows; + my $rows = 0; + my $query; + my %temphash; + + $filename = $dbh->quote($filename); + my $keys = join ",", @FILE_KEYS; + $query = "SELECT $keys FROM files WHERE filename=$filename"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { $temphash{$FILE_KEYS[$i]} = $row[$i]; } + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No files!\n" if ($DEBUG); + return -1; + } + + return \%temphash; + } + +sub get_files + { + my ($pid) = @_; + my @rows; + my $rows = 0; + my $query; + + $pid = $dbh->quote($pid); + my $keys = join ",", @FILE_KEYS; + $query = "SELECT $keys FROM files WHERE thread=$pid"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { return -1; } + + return \@rows; + } +sub get_recent_files + { + my @rows; + my $rowcount = 0; + my $query; + + my $keys = join ",", @FILE_KEYS; + $query = "SELECT $keys FROM files ORDER BY date DESC LIMIT 20"; + + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS[$i]} = $row[$i]; + } + push(@rows, \%temphash ); + $rowcount++; + } + $sth->finish(); + + if ($rowcount == 0) + { return -1; } + + return \@rows; + } + + + +sub get_tag_files + { + my ($tag) = @_; + my @rows; + my @FILE_KEYS_JOIN = qw(files.id files.username thread filename files.title files.date files.size files.private threads.keyword); + my $rows = 0; + $tag= $dbh->quote($tag); + my $keys = join ",", @FILE_KEYS_JOIN; + + my $query = "SELECT $keys FROM files,threads,threadstags,tags " . + "WHERE files.thread=threads.id AND threads.id=threadstags.threadid " . + "AND threadstags.tagid=tags.id AND tags.tag=$tag"; + $sth = $dbh->prepare( $query ); + $sth->execute(); + + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No files!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } +sub get_keyword_files + { + my ($keyword) = @_; + my @rows; + my @FILE_KEYS_JOIN = qw(files.id files.username thread filename files.title files.date files.size files.private threads.keyword); + my $rows = 0; + $keyword = $dbh->quote($keyword); + my $keys = join ",", @FILE_KEYS_JOIN; + + my $query = "SELECT $keys FROM files,threads WHERE files.thread=threads.id AND threads.keyword=$keyword"; + $sth = $dbh->prepare( $query ); + $sth->execute(); + + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No files!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } +sub get_user_files + { + my ($uname) = @_; + my @rows; + my $rows = 0; + my $query; + + $uname = $dbh->quote($uname); + my @FILE_KEYS_JOIN = qw(files.id files.username files.thread files.filename files.title files.date files.size files.private threads.keyword); + my $keys = join ",", @FILE_KEYS_JOIN; + $query = "SELECT $keys FROM files,threads WHERE files.username=$uname AND files.thread=threads.id"; + + $sth = $dbh->prepare($query); + $sth->execute(); + print $query."
" if ($DEBUG); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No files!\n" if ($DEBUG); + return -1; + } + + return \@rows; + } + +sub get_largest_files + { + my @rows; + my $rows = 0; + my $query; + my @keys = qw(id username thread filename date size private); + + $pid = $dbh->quote($pid); + my $keys = join ",", @FILE_KEYS; + $query = "SELECT $keys FROM files WHERE size > 100000000"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$FILE_KEYS[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { return -1; } + + return \@rows; + } + + +sub add_file + { + my ($pid, $uname, $filename, $size, $date) = @_; + my $f_id; + + $pid = $dbh->quote($pid); + $uname = $dbh->quote($uname); + $filename = $dbh->quote($filename); + $date = $dbh->quote($date); + $size = $dbh->quote($size); + my $query = "INSERT files (thread,username,date,filename,size) VALUES($pid,$uname,$date,$filename,$size)"; + my $rows = $dbh->do($query); + if ($rows == 0) + { error("failed to add file !"); } + + $query = "SELECT id FROM files WHERE username=$uname AND date=$date"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($f_id) = $sth->fetchrow_array(); + $sth->finish (); + + return $f_id; + } + +sub delete_file_record + { + my ($id) = @_; + $id = $dbh->quote($id); + my $query = "DELETE FROM files WHERE id=$id"; + my $rows = $dbh->do($query); + } + +sub count_user_files + { + my ($uname) = @_; + my @row; + $uname = $dbh->quote($uname); + my $query = "SELECT COUNT(*), SUM(size) FROM files WHERE username=$uname"; + $sth = $dbh->prepare($query); + $sth->execute(); + (@row) = $sth->fetchrow_array(); + $sth->finish (); + return @row; + } + +sub count_files + { + my ($thread) = @_; + my $count; + $thread = $dbh->quote($thread); + my $query = "SELECT COUNT(*) FROM files WHERE thread=$thread"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($count) = $sth->fetchrow_array(); + $sth->finish (); + return $count; + } + +######################################### +# COMMENTS +######################################### + +sub get_comment + { + my ($comment_id) = @_; + + #$comment_id = $dbh->quote($comment_id); + + $query = "SELECT * FROM comments WHERE id=$comment_id"; + + print "$query
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + my $comment = $sth->fetchrow_hashref(); + $sth->finish (); + + return $comment || -1; + } + +sub get_comments + { + my ($thread, $count) = @_; + my %rows_by_id; + my $query; + my $rows = 0; + + return -1 if (!defined($thread)); + $query = "SELECT * FROM comments WHERE thread=$thread"; + if ($count) + { $query .= " ORDER BY id DESC LIMIT $count"; } +print $query."
" if $DEBUG; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my $row = $sth->fetchrow_hashref) + { + my $id = $row->{'id'}; + $rows_by_id{$id} = $row; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { return -1; } + + return \%rows_by_id; + } + +sub count_comments + { + my ($thread) = @_; + my $count; + $thread = $dbh->quote($thread); + my $query = "SELECT COUNT(*) FROM comments WHERE thread = $thread"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($count) = $sth->fetchrow_array(); + $sth->finish (); + return $count; + } + +sub add_comment + { + my ($thread, $pid, $uname, $comment, $time) = @_; + my ($t) = $time || time; + my $qpid; + my $c_id; + + $thread = $dbh->quote($thread); + $pid = $dbh->quote($pid); + $uname = $dbh->quote($uname); + $comment = $dbh->quote($comment); + $t = $dbh->quote($t); + my $query = "INSERT comments (thread,parent_id,username,date,comment) VALUES($thread,$pid,$uname,$t,$comment)"; + my $rows = $dbh->do($query); + if ($rows == undef) + { error("failed to add comment !"); } + + $query = "SELECT id FROM comments WHERE username=$uname AND date=$t"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($c_id) = $sth->fetchrow_array(); + $sth->finish (); + + return $c_id; + } + +sub update_comment + { + my ($id, $comment) = @_; + my ($t) = time; + + $id = $dbh->quote($id); + $comment = $dbh->quote($comment); + $t = $dbh->quote($t); + my $query = "UPDATE comments SET date=$t,comment=$comment where id=$id"; + my $rows = $dbh->do($query); + if ($rows == undef) + { error("failed to edit comment !"); } + + return 1; + } + +sub delete_comment + { + my ($id) = @_; + $id = $dbh->quote($id); + my $query = "DELETE FROM comments WHERE id=$id"; + my $rows = $dbh->do($query); + } + +######################################### +# TAGS +######################################### + +sub get_tag + { + my ($tag_name) = @_; + $tag_name = $dbh->quote( $tag_name ); + my @columns = qw(id tag createdate owner ops public display); + my $query = "SELECT id,tag,createdate,owner,ops,public,display FROM tags WHERE tag =$tag_name"; + print "$query
" if ($DEBUG); + my $sth = $dbh->prepare( $query ); + $sth->execute(); + my $rows = 0; + my %temphash; + if ((@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $temphash{$columns[$i]} = $row[$i]; + } + $rows++; + } + if ($rows == 0) + { + print "No such tag!\n" if ($DEBUG); + return -1; + } + return \%temphash; + } +sub get_tag_count + { + my ($tag) = @_; + my $count = 0; + $tag = $dbh->quote( $tag ); + my $query = "SELECT COUNT(*) FROM tags WHERE tag=$tag"; + my $sth = $dbh->prepare( $query ); + $sth->execute; + ($count) = $sth->fetchrow_array(); + $sth->finish(); + return $count; + } +sub get_tag_names + { + my @tags; + my $query = "SELECT DISTINCT tag from tags,threadstags,threads " . + "WHERE threadstags.tagid=tags.id AND threadstags.threadid=threads.id " . + "AND ((threads.private = 0)"; + if ( $USER == -1 ) + { $query .= ")"; } + else + { $query .= " OR (threads.allowed LIKE \"% " . $USER->{id} . " %\"))"; } + print "$query
" if ($DEBUG); + $sth = $dbh->prepare( $query ); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + push ( @tags, $row[0] ); + } + $sth->finish(); + if (@tags == 0) + { +# print "No tags!\n" if ($DEBUG); + return -1; + } + return \@tags; + } +sub get_tags_for_thread + { + my ($id) = @_; + my @keys = qw(tag createdate owner ops public display); + my @rows; + my %temphash; + $id = $dbh->quote($id) unless ($id =~ /'/); + my $query = "SELECT tags.tag FROM threadstags,tags WHERE threadstags.threadid=$id AND threadstags.tagid=tags.id"; + print "$query
" if ($DEBUG); + $sth = $dbh->prepare( $query ); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + push( @rows, $row[0] ); + } + $sth->finish (); + if (@rows == 0) + { +# print "No tags for post!\n" if ($DEBUG); + return -1; + } + return \@rows; + } + +sub delete_tag_for_thread + { + my ($tag_id, $thread_id) = @_; + + if (ref($tag_id)) + { $tag_id = $tag_id->{id}; } + if (ref($thread_id)) + { $thread_id = $thread_id->{id}; } + + # Escape id numbers for SQL + $tag_id = $dbh->quote($tag_id); + $thread_id = $dbh->quote($thread_id); + + my $query = "DELETE FROM threadstags WHERE threadid=$thread_id AND tagid=$tag_id"; + my $rows = $dbh->do($query); + } + +sub increment_tag_for_thread_by_hash + { + } +sub new_tag + { + my ($tag) = @_; + my $data; + $data->{row}->{tag} = $tag; + $data->{row}->{owner} = $USER->{username}; + $data->{row}->{createdate} = time; + $data->{row}->{public} = 0; + return add_tag_by_hash( $data ); + } + +sub add_tag_by_hash + { + my ($data) = @_; + $data->{table} = $BUCKY_DB_TABLE_TAGS; + $data->{columns} = \@BUCKY_DB_COLUMNS_TAGS; + $data->{error} = $BUCKY_DB_ERROR_INSERT_TAGS; + return add_row_by_hash( $data ); + } + +sub add_row_by_hash + { + # Data hash + my ($data) = @_; + + # Return row id, retrieved identifier, or -1 if failure + my $id = -1; + + # Columns for this table/row, required + my $columns = $data->{columns} || return $returnVal; + + # SQL statement handle + my $sth; + + # Initialize query with SQL INSERT + my $query = "INSERT " . $data->{table} . " SET "; + + # Flag for prepending comma after first column + my $first_column = 0; + +# print "tag: " . $data->{row}->{tag} . "
\n"; +# print $data->{row}->{owner} . "
\n"; +# print "columns : " .$columns . "
\n"; + # Loop through row's columns to build SQL statement + my $first_set = 0; + foreach my $column (@$columns) + { +# print $data->{row}->{$column} . "
\n"; + # skip if this column is not provided for this row + next unless exists( $data->{row}->{$column} ); + + # Prepend comma for every column after the first + $query .= ", " unless ($first_set++ == 0); + + # SQL quote each column, append column name and value to insert statement + $query .= $column . "=" . $dbh->quote( $data->{row}->{$column} ); + } + + # Debug output the SQL query + print $query . "
" if ($DEBUG); + + # Execute SQL query + my $rows = $dbh->do( $query ); + + # Return any query error + if ($rows == undef) + { error( $data->{error} ); } + + # If there is a special identifying value to return for this inserted row + if ( ref($data->{retrieve}) ) + { + # Construct query for the requested column + $query = "SELECT " . $data->{retrieve}->{column} . " FROM " . $data->{table}; + + # Specify qualifiers with key and value + $query .= " WHERE " . $data->{retrieve}->{key} . "=" . $data->{retrieve}->{value}; + + # Prepare and execute SQL query + $sth = $dbh->prepare( $query ); + $sth->execute(); + + # Return identifying value (the first one, hopefully no multiple matches + ($id) = $sth->fetchrow_array(); + # Close SQL statement handle + $sth->finish(); + } + elsif ( $data->{retrieve} ) + { + $id = 0; + } + else + { + # Retrieve last auto-incremented value as a result of the last SQL INSERT- this is the ID + $id = $dbh->{'mysql_insertid'}; + } + + + # Return ID column or identifying value + return $id; + } + +sub add_tag_for_thread_by_hash + { + my ($newt) = @_; + my $thread_id = -1; + @keys = qw(title username keyword createdate lastmodified size private allowed color display viewed); + my @qkeys; + my %q; + my $i = 0; + my $sth; + + foreach my $key (@keys) + { + if (exists $newt->{$key}) + { + $q{$key} = $dbh->quote($newt->{$key}); + } + } + + @qkeys = keys %q; + + $query = "INSERT threads SET "; + foreach my $k (@qkeys) + { + $query .= $k."=".$q{$k}; + if (++$i != @qkeys) + { $query .= ","; } + } + + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + if ($rows == undef) + { error("failed to add post!"); } + + $query = "SELECT id FROM threads WHERE title=$q{title}"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($thread_id) = $sth->fetchrow_array(); + $sth->finish (); + + return $thread_id; + } +sub update_tag_for_thread + { + my ($tag_id, $thread_id) = @_; + + if (ref($tag_id)) + { $tag_id = $tag_id->{id}; } + if (ref($thread_id)) + { $thread_id = $thread_id->{id}; } + + $data->{table} = $BUCKY_DB_TABLE_THREADSTAGS; + $data->{columns} = \@BUCKY_DB_COLUMNS_THREADSTAGS; + $data->{error} = $BUCKY_DB_ERROR_INSERT_THREADSTAGS; + $data->{row}->{'threadid'} = $thread_id; + $data->{row}->{'tagid'} = $tag_id; + $data->{row}->{'username'} = $USER->{username}; + $data->{row}->{'createdate'} = time; + + add_row_by_hash( $data ); + return 1; + } +######################################### +# THREADS +######################################### +sub get_thread + { + my ($id) = @_; + my @row; + my @keys = qw(id title username keyword createdate lastmodified size private allowed flagged display color zipped viewed revision); + my $rows = 0; + my %temphash; + $id = $dbh->quote($id); + my $query = "SELECT id,title,username,keyword,createdate,lastmodified,size,private,allowed,flagged,display,color,zipped,viewed,revision FROM threads WHERE id=$id"; + print "$query
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + if ((@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish (); + if ($rows == 0) + { + print "No such post!\n" if ($DEBUG); + return -1; + } + $temphash{comments} = count_comments($temphash{id}); + $temphash{files} = count_files($temphash{id}); + $temphash{tags} = get_tags_for_thread( $id ); + return \%temphash; + } + +sub add_tag_by_hash_v2 + { + } +sub add_tag_for_thread_by_hash_v2 + { + } +sub add_thread_by_hash_v2 + { + my ($data) = @_; + $data->{table} = $BUCKY_DB_TABLE_THREADS; + $data->{columns} = \@BUCKY_DB_COLUMNS_THREADS; + $data->{error} = $BUCKY_DB_ERROR_INSERT_THREADS; + return add_row_by_hash( $data ); + } + +sub add_thread_by_hash + { + my ($newt) = @_; + my $thread_id = -1; + @keys = qw(title username keyword createdate lastmodified size private allowed color display viewed); + my @qkeys; + my %q; + my $i = 0; + my $sth; + + foreach my $key (@keys) + { + if (exists $newt->{$key}) + { + $q{$key} = $dbh->quote($newt->{$key}); + } + } + + @qkeys = keys %q; + + $query = "INSERT threads SET "; + foreach my $k (@qkeys) + { + $query .= $k."=".$q{$k}; + if (++$i != @qkeys) + { $query .= ","; } + } + + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + if ($rows == undef) + { error("failed to add post!"); } + + $query = "SELECT id FROM threads WHERE title=$q{title}"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($thread_id) = $sth->fetchrow_array(); + $sth->finish (); + + return $thread_id; + } + +sub add_thread + { + my ($title, $uname, $private) = @_; + my ($t) = time; + my $thread_id; + + if (!defined($private)) + { $private = 0; } + + $title = $dbh->quote($title); + $uname = $dbh->quote($uname); + $private = $dbh->quote($private); + my $viewed = 0; + $t = $dbh->quote($t); + my $query = "INSERT threads (title,username,createdate,lastmodified,private,viewed) VALUES($title,$uname,$t,$t,$private,$viewed)"; + my $rows = $dbh->do($query); + if ($rows == undef) + { error("failed to add post!"); } + + $query = "SELECT id FROM threads WHERE title=$title"; + $sth = $dbh->prepare($query); + $sth->execute(); + ($thread_id) = $sth->fetchrow_array(); + $sth->finish (); + + return $thread_id; + } + +sub touch_thread + { + my ($thread) = @_; + my $time = time; + + return 0 if ($thread == -1); + + my $id = $dbh->quote($thread->{id}); + my $revision = $dbh->quote($thread->{revision} + 1); + $time = $dbh->quote($time); + + my $query = "UPDATE threads SET lastmodified=$time,revision=$revision WHERE id=$id"; + my $rows = $dbh->do($query); + + if ($rows == undef) + { error("failed to touch post $id !"); } + + return 1; + } + +sub update_thread_size + { + my ($id) = @_; + my @row; + my $rows; + $id = $dbh->quote($id); + my $query = "SELECT SUM(size) FROM files WHERE thread=$id"; + $sth = $dbh->prepare($query); + $sth->execute(); + (@row) = $sth->fetchrow_array(); + $size = $row[0]; + $sth->finish (); + + $size = $dbh->quote($size); + $query = "UPDATE threads SET size=$size WHERE id=$id"; + $rows = $dbh->do($query); + } + +sub update_thread_display + { + my ($id, $disp) = @_; + $id = $dbh->quote($id); + $disp = $dbh->quote($disp); + $query = "UPDATE threads SET display=$disp WHERE id=$id"; + $dbh->do($query); + } + +sub update_thread_color + { + my ($id, $disp) = @_; + $id = $dbh->quote($id); + $disp = $dbh->quote($disp); + $query = "UPDATE threads SET color=$disp WHERE id=$id"; + $dbh->do($query); + } + +sub update_thread_zipped + { + my ($id, $val) = @_; + $id = $dbh->quote($id); + $val = $dbh->quote($val); + $query = "UPDATE threads SET zipped=$val WHERE id=$id"; + $dbh->do($query); + } + +sub update_thread_viewed + { + my ($id, $val) = @_; + $id = $dbh->quote($id); + $val = $dbh->quote($val); + $query = "UPDATE threads SET viewed=$val WHERE id=$id"; + $dbh->do($query); + } + +sub get_threads + { + my @rows; + my $query; + my @keys = qw(id title username keyword date size private allowed flagged color viewed revision); + my $rows = 0; + + $query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads"; + + print "$query
\n" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No posts!\n" if ($DEBUG); + return -1; + } + + for (my $i = 0; $i < @rows; $i++) + { + $rows[$i]{comments} = count_comments($rows[$i]{id}); + $rows[$i]{files} = count_files($rows[$i]{id}); + $rows[$i]{tags} = get_tags_for_thread($rows[$i]{id}); + } + + return \@rows; + } + +sub throttle_threads + { + my ($args) = @_; + my $keyword = $args->{keyword} || undef; + my $tag = $args->{tag} || undef; + my $limit = $args->{limit} || undef; + my $newest = $args->{newest} || undef; + my $oldest = $args->{oldest} || undef; +# my ($keyword, $limit, $newest, $oldest) = @_; + my @rows; + my $query; + my $where = "WHERE"; + my @keys = qw(id title username keyword date size private allowed flagged color viewed revision); + my $rows = 0; + + $query = "SELECT threads.id,title,threads.username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads"; + + if (defined($tag)) + { + $tag = $dbh->quote($tag); + $query .= ",threadstags,tags WHERE threads.id=threadstags.threadid AND threadstags.tagid=tags.id AND tags.tag=$tag"; + + } + elsif (defined($keyword) && $keyword ne "all" && $keyword != -1) + { + if ($keyword eq "unsorted") + { $query .= " WHERE ISNULL(keyword)"; } + else + { + $keyword = $dbh->quote($keyword); + $query .= " WHERE keyword=$keyword"; + } + $where = "AND"; + } + + if (defined($newest) && $newest ne "now") + { + $query .= " $where lastmodified < $newest"; + $where = "AND"; + } + if (defined($oldest)) + { + $query .= " $where lastmodified > $oldest"; + $where = "AND"; + } + $query .= " ORDER BY lastmodified DESC"; + if ($limit) + { $query .= " LIMIT $limit"; } + + print "$query
\n" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No posts!\n" if ($DEBUG); + return -1; + } + + for (my $i = 0; $i < @rows; $i++) + { + $rows[$i]{comments} = count_comments($rows[$i]{id}); + $rows[$i]{files} = count_files($rows[$i]{id}); + $rows[$i]{tags} = get_tags_for_thread($rows[$i]{id}); + } + + return \@rows; + } + +sub get_threads_by_user + { + my ($uname) = @_; + my @rows; + my $query; + my @keys = qw(id title username keyword date size private allowed flagged color viewed revision); + my $rows = 0; + + $uname = $dbh->quote($uname); + $query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads WHERE username=$uname"; + + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No posts!\n" if ($DEBUG); + return -1; + } + + for (my $i = 0; $i < @rows; $i++) + { + $rows[$i]{comments} = count_comments($rows[$i]{id}); + $rows[$i]{files} = count_files($rows[$i]{id}); + $rows[$i]{tags} = get_tags_for_thread($rows[$i]{id}); + } + + return \@rows; + } + +sub get_threads_by_tag + { + my ($tag) = @_; + my @rows; + my $query; + my @keys = qw(id title username keyword date size private allowed flagged color viewed revision); + my $rows = 0; + + $tag = $dbh->quote($tag); + $query = "SELECT threads.id,title,threads.username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads,threadstags,tags WHERE threads.id = threadstags.threadid AND threadstags.tagid = tags.id AND tags.tag = $tag"; + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No posts!\n" if ($DEBUG); + return -1; + } + + for (my $i = 0; $i < @rows; $i++) + { + $rows[$i]{comments} = count_comments($rows[$i]{id}); + $rows[$i]{files} = count_files($rows[$i]{id}); + $rows[$i]{tags} = get_tags_for_thread($rows[$i]{id}); + } + + return \@rows; + } + +sub get_threads_by_keyword + { + my ($keyword) = @_; + my @rows; + my $query; + my @keys = qw(id title username keyword date size private allowed flagged color viewed revision); + my $rows = 0; + + $keyword = $dbh->quote($keyword); + $query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads WHERE keyword=$keyword"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No posts!\n" if ($DEBUG); + return -1; + } + + for (my $i = 0; $i < @rows; $i++) + { + $rows[$i]{comments} = count_comments($rows[$i]{id}); + $rows[$i]{files} = count_files($rows[$i]{id}); + $rows[$i]{tags} = get_tags_for_thread($rows[$i]{id}); + } + + return \@rows; + } + +sub update_flagged + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + $state = $dbh->quote($state); + my $query = "UPDATE threads SET flagged=$state WHERE id=$id"; + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to switch post $thread!"); } + + return 1; + } + +sub update_thread_title + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + $state = $dbh->quote($state); + my $query = "UPDATE threads SET title=$state WHERE id=$id"; + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to switch post $thread!"); } + + return 1; + } + +sub update_thread_tag + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + } + +sub update_thread_keyword + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + if ($state ne "NULL") + { $state = $dbh->quote($state); } + my $query = "UPDATE threads SET keyword=$state WHERE id=$id"; + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to switch post $thread!"); } + + return 1; + } + + +######################################### +# KEYWORDS +######################################### + +sub get_tags + { + my @rows; + my @keys = qw(id tag createdate owner ops public display); + my %finalhash; + my $query = "SELECT id,tag,createdate,owner,ops,public,display FROM tags"; + print $query."
" if ($DEBUG); + $sth = $dbh->prepare( $query ); + $sth->execute(); + while ( my (@row) = $sth->fetchrow_array() ) + { + my %temphash; + for ( my $i = 0; $i < @row; $i++ ) + { $temphash{$keys[$i]} = $row[$i]; } + $finalhash{$temphash{"tag"}} = \%temphash; + $rows++; + } + $sth->finish(); + if ( $rows == 0 ) + { +# print "No tags!\n" if ($DEBUG); + return -1; + } + return \%finalhash; + } + +sub get_keywords + { + my @rows; + my $query; + my @keys = qw(id keyword threads owner ops public agglutinate color); + my $rows = 0; + my %finalhash; + + $query = "SELECT id,keyword,threads,owner,ops,public,agglutinate,color FROM keywords"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) + { + $temphash{$keys[$i]} = $row[$i]; + } + $finalhash{$temphash{"keyword"}} = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No keywords!\n" if ($DEBUG); + return -1; + } + + return \%finalhash; + } + +sub get_keyword + { + my ($keyword) = @_; + my @rows; + my $query; + my @keys = qw(id keyword threads owner ops public agglutinate color); + my $rows = 0; + my %finalhash; + + $keyword = $dbh->quote($keyword); + $query = "SELECT id,keyword,threads,owner,ops,public,agglutinate,color FROM keywords WHERE keyword=$keyword"; + + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + if ((@row) = $sth->fetchrow_array()) + { + for (my $i = 0; $i < @row; $i++) + { + $finalhash{$keys[$i]} = $row[$i]; + } + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No keywords!\n" if ($DEBUG); + return -1; + } + + return \%finalhash; + } + +sub add_keyword + { + my ($newk) = @_; + my @keys = qw(keyword threads owner ops public agglutinate color); + my @qkeys; + my %q; + my $i = 0; + + $q{createdate} = $dbh->quote(time); + + foreach my $key (@keys) + { + if (exists $newk->{$key}) + { + $q{$key} = $dbh->quote($newk->{$key}); + } + } + + @qkeys = keys %q; + + $query = "INSERT keywords SET "; + foreach my $k (@qkeys) + { + $query .= $k."=".$q{$k}; + if (++$i != @qkeys) + { $query .= ","; } + } + + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to add keyword $newk->{keyword}!"); } + + return 1; + } + +sub update_keyword + { + my ($keyword, $newk) = @_; + my @keys = qw(threads ops public agglutinate color); + my @qkeys; + my %q; + my $i = 0; + + my $keyword = $dbh->quote($keyword); + + foreach my $key (@keys) + { + if (defined($newk->{$key})) + { + $q{$key} = $dbh->quote($newk->{$key}); + } + } + + @qkeys = keys %q; + + $query = "UPDATE keywords SET "; + foreach my $k (@qkeys) + { + $query .= $k."=".$q{$k}; + if (++$i != @qkeys) + { $query .= ","; } + } + $query .= " WHERE keyword=$keyword"; + + print $query."
" if ($DEBUG); + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to update keyword $keyword!"); } + + return 1; + } + +sub update_keyword_color + { + my ($key, $disp) = @_; + $key = $dbh->quote($key); + $disp = $dbh->quote($disp); + $query = "UPDATE keywords SET color=$disp WHERE keyword=$key"; + $dbh->do($query); + } + +######################################### +# PRIVACY WHITELISTING +######################################### + +sub switch_thread_privacy + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + $state = $dbh->quote($state); + my $query = "UPDATE threads SET private=$state WHERE id=$id"; + my $rows = $dbh->do($query); + + if ($rows == 0) + { error("failed to switch post $thread!"); } + + switch_file_privacy($id, $state, 1); + + return 1; + } + +sub switch_file_privacy + { + my ($id, $state, $q) = @_; + + unless (defined($q)) + { + $id = $dbh->quote($id); + $state = $dbh->quote($state); + } + + my $query = "UPDATE files SET private=$state WHERE thread=$id"; + my $rows = $dbh->do($query); + + return 1; + } + +sub switch_whitelist + { + my ($id, $state) = @_; + + $id = $dbh->quote($id); + $state = $dbh->quote($state); + my $query = "UPDATE threads SET allowed=$state WHERE id=$id"; + my $rows = $dbh->do($query); + + if ($rows == undef) + { error("failed to switch post $thread!"); } + + return 1; + } + + +######################################### +# ET CETERA + +sub commit_murder + { + my ($uname) = @_; + $uname = $dbh->quote($uname); + $dbh->do("UPDATE users SET ulevel=-2 WHERE username=$uname"); + } + +sub commit_blessing + { + my ($uname) = @_; + my $lev; + $uname = $dbh->quote($uname); + $sth = $dbh->prepare("SELECT ulevel FROM users WHERE username=$uname"); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { $lev = $row[0]; } + $sth->finish(); + + $lev++; + $lev = $dbh->quote($lev); + $dbh->do("UPDATE users SET ulevel=$lev WHERE username=$uname"); + } + +sub delete_thread + { + my ($id) = @_; + my $rows; + $id = $dbh->quote($id); + $dbh->do("DELETE FROM files WHERE thread=$id"); + $dbh->do("DELETE FROM comments WHERE thread=$id"); + $dbh->do("DELETE FROM threads WHERE id=$id"); + system("$RM_PATH", "-rf", "$data_path/$id"); + } + +sub get_participation + { + my ($id) = @_; + $id = $dbh->quote($id); + my %temphash; + my $i = 0; + + $sth = $dbh->prepare("SELECT username FROM files WHERE thread=$id"); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { $temphash{$row[0]} = 1; } + $sth->finish(); + + $sth = $dbh->prepare("SELECT username FROM comments WHERE thread=$id"); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { $temphash{$row[0]} = 1; } + $sth->finish(); + + foreach (keys %temphash) + { $i++; } + + return $i; + } + +{ +my $usermax = 0; # cache +my %rands; +sub get_random_user + { + my $selected = -1; + my $randy = 1; + + unless ($usermax) + { + my @keys = qw[$usermax]; + my $query = "SELECT MAX(id) FROM users"; + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + ($usermax) = $sth->fetchrow_array(); + $sth->finish (); + } + + do + { $randy = (int rand $usermax) + 1; } + while ( exists($rands{$randy}) ); + $rands{$randy} += 1; + $selected = get_username($randy); + + print "Random user: $selected
" if ($DEBUG); + + return $selected; + } +} + +sub get_random_flagged_image + { + my @rows; + my $rows = 0; + my $selected = -1; + my @keys = qw[thread filename]; + my $query = "SELECT threads.id,files.filename FROM threads,files WHERE threads.flagged=files.id AND threads.private=0"; + print $query."
" if ($DEBUG); + $sth = $dbh->prepare($query); + $sth->execute(); + while (my (@row) = $sth->fetchrow_array()) + { + my %temphash; + for (my $i = 0; $i < @row; $i++) +{ $temphash{$keys[$i]} = $row[$i]; } + $rows[$rows] = \%temphash; + $rows++; + } + $sth->finish(); + + if ($rows == 0) + { + print "No flagged images!
\n" if ($DEBUG); + return -1; + } + + $selected = $rows[(int rand @rows)]; + print "Random flagged image: $selected->{thread}/$selected->{filename}
" if ($DEBUG); + + return $selected; + } +# + +### given an id: +### my (@fields) = get_row_from_id("threads", $id); +### ("author, startdate, lastmodified, flagged_file_id") + +### my ($ = get_rows_from_pid("files", $pid); +### select %(id, path, filename, author, date, size) +### from files where pid = $pid +### foreach $aref (@$arrayref) { my $id = $aref[0]; %q{$id}=($aref[1..$#$aref]); } + +### get_rows_from_pid("comments", $pid); +### select %(id, author, date, comment) +### from comments where pid = $pid + +1; + -- cgit v1.2.3-70-g09d2