'newhistory' => 'INSERT INTO history (location, owner, discussion, nodename, nodenote) VALUES(?, ?, ?, ?, ?)',
'history' => 'SELECT history.id, history.discussion, history.time, history.nodename, history.nodenote, history.seen, users.login, users.email FROM history LEFT OUTER JOIN users ON history.owner = users.id WHERE history.location = ? ORDER BY history.time',
'admindump' => 'SELECT
- locations.id, locations.name, locations.note, locations.mainhistory, musers.login, main.discussion,
- history.id, history.discussion, history.nodename, history.nodenote, users.login
+ locations.id, locations.name, locations.note, locations.mainhistory, musers.email, main.discussion,
+ history.id, history.discussion, history.nodename, history.nodenote, users.email
FROM
locations INNER JOIN history ON history.location = locations.id
LEFT OUTER JOIN users ON history.owner = users.id
LEFT OUTER JOIN history AS main ON locations.mainhistory = main.id
- LEFT OUTER JOIN users AS musers ON main.owner = musers.id WHERE history.seen = "0"
+ LEFT OUTER JOIN users AS musers ON main.owner = musers.id
+ WHERE history.seen = "0" AND locations.id LIKE ?
ORDER BY locations.id, history.id
- LIMIT 15',#Dumps new discussion submits with their senders and corresponding main history and names
+ LIMIT 100',#Dumps new discussion submits with their senders and corresponding main history and names
'delete-hist' => 'DELETE FROM history WHERE id = ?',
'mark-checked' => 'UPDATE history SET seen = 1 WHERE id = ?',
'delete-item' => 'DELETE FROM locations WHERE id = ?',
pending.user, pending.reason, history.time, history.location',
'dropnotifsxmpp' => 'DELETE FROM pending WHERE notification = 1 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextxmpp <= ? )',
'dropnotifsmail' => 'DELETE FROM pending WHERE notification = 0 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextmail <= ? )',
- 'time' => 'SELECT NOW()'
-
+ 'time' => 'SELECT NOW()',
+ 'searchname' => 'SELECT l.id, l.name, p.name FROM locations AS l JOIN locations AS p ON l.parent = p.id WHERE l.name LIKE ? ORDER BY l.id',
+ 'searchlocalname' => 'SELECT l.id, l.name, p.name FROM locations AS l JOIN locations AS p ON l.parent = p.id WHERE l.name LIKE ? AND l.id LIKE ? ORDER BY l.id',
+ 'hasChildren' => 'SELECT DISTINCT 1 FROM locations WHERE parent = ?',
+ 'hasMain' => 'SELECT DISTINCT 1 FROM locations WHERE id = ? AND mainhistory IS NOT NULL'
} );
}
+sub hasChildren( $$ ) {
+ my( $self, $parent ) = @_;
+ return scalar @{$self->query( 'hasChildren', [ $parent ] )};
+}
+
+sub hasMain( $$ ) {
+ my( $self, $id ) = @_;
+ return scalar @{$self->query( 'hasMain', [ $id ] )};
+}
+
my %sorts = ( 'id' => 1, 'rid' => 1, 'name' => 1, 'rname' => 1 );
sub nodes( $$$$ ) {
return $self->last();
}
-sub adminDump( $ ) {
- return shift->query( 'admindump', [] );
+sub adminDump( $$ ) {
+ my( $self, $prefix ) = @_;
+ return $self->query( 'admindump', [ "$prefix%" ] );
}
sub deleteHistory( $$ ) {
$self->command( 'dropnotifsxmpp', [ $time ] );
}
+sub searchName( $$$ ) {
+ my( $self, $search, $prefix ) = @_;
+ return $self->query( 'searchlocalname', [ "%$search%", "$prefix/%" ] ) if defined $prefix;
+ return $self->query( 'searchname', [ "%$search%" ] );
+}
+
1;