cgi-bin & lib

1 files changed, 2400 insertions, 0 deletions

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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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 "<tt>$query</tt><br>" 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."<br>" 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<br>" 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."<br>" 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<br>" 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<br>" 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<br>" 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} . "<br>\n";
+#	print $data->{row}->{owner} . "<br>\n";
+#	print "columns : " .$columns  . "<br>\n";
+	# Loop through row's columns to build SQL statement
+	my $first_set = 0;
+	foreach my $column (@$columns)
+		{	
+#		print $data->{row}->{$column} . "<BR>\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 . "<br>" 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."<br>" 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<br>" 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."<br>" 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<br>\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<br>\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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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."<br>" 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<br>" 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."<br>" 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!<br>\n" if ($DEBUG);
+		return -1;
+		}
+
+	$selected = $rows[(int rand @rows)];
+	print "Random flagged image: $selected->{thread}/$selected->{filename}<br>" 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;
+