PciIds/DBQ.pm

   1 #       PciIds web database
   2 #       Copyright (C) 2008 Michal Vaner (vorner@ucw.cz)
   3 #
   4 #       This program is free software; you can redistribute it and/or modify
   5 #       it under the terms of the GNU General Public License as published by
   6 #       he Free Software Foundation; either version 2 of the License, or
   7 #       (at your option) any later version.
   8 #
   9 #       This program is distributed in the hope that it will be useful,
  10 #       but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 #       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 #
  13 #       GNU General Public License for more details.
  14 #
  15 #       You should have received a copy of the GNU General Public License
  16 #       along with this program; if not, write to the Free Software
  17 #       Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  18
  19 package PciIds::DBQ;
  20 use strict;
  21 use warnings;
  22 use base 'PciIds::DBQAny';
  23
  24 my $adminDumpSql = 'SELECT
  25                         locations.id, locations.name, locations.note, locations.mainhistory, musers.email, musers.login, main.discussion, main.time,
  26                         history.id, history.discussion, history.nodename, history.nodenote, users.email, users.login, history.time
  27                 FROM
  28                         locations INNER JOIN history ON history.location = locations.id
  29                         LEFT OUTER JOIN users ON history.owner = users.id
  30                         LEFT OUTER JOIN history AS main ON locations.mainhistory = main.id
  31                         LEFT OUTER JOIN users AS musers ON main.owner = musers.id
  32                 WHERE history.seen = "0" AND locations.id LIKE ?
  33                 ORDER BY locations.id, history.id
  34                 LIMIT ';
  35
  36 sub new( $ ) {
  37         my( $dbh ) = @_;
  38         my $node = 'SELECT id, name, note, mainhistory FROM locations WHERE parent = ? ORDER BY ';
  39         my $noder = 'SELECT id, name, note, mainhistory FROM locations WHERE parent = ? AND id LIKE ? ORDER BY ';
  40         return bless PciIds::DBQAny::new( $dbh, {
  41                 'nodes-id' => $node.'id',
  42                 'nodes-name' => $node.'name',
  43                 'nodes-rid' => $node.'id DESC',
  44                 'nodes-rname' => $node.'name DESC',
  45                 'nodes-id-r' => $noder.'id',
  46                 'nodes-name-r' => $noder.'name',
  47                 'nodes-rid-r' => $noder.'id DESC',
  48                 'nodes-rname-r' => $noder.'name DESC',
  49                 'item' => 'SELECT parent, name, note, mainhistory FROM locations WHERE id = ?',
  50                 'login' => 'SELECT id FROM users WHERE login = ?',
  51                 'email' => 'SELECT id FROM users WHERE email = ?',
  52                 'adduser' => 'INSERT INTO users (login, email, passwd) VALUES(?, ?, ?)',
  53                 'adduser-null' => 'INSERT users (email, passwd) VALUES(?, ?)',
  54                 'loginfomail' => 'SELECT id, passwd, logtime, lastlog FROM users WHERE email = ?',
  55                 'loginfoname' => 'SELECT id, passwd, logtime, lastlog, email FROM users WHERE login = ?',
  56                 'resetinfo' => 'SELECT id, login, passwd FROM users WHERE email = ?',
  57                 'changepasswd' => 'UPDATE users SET passwd = ? WHERE id = ?',
  58                 'setlastlog' => 'UPDATE users SET logtime = now(), lastlog = ? WHERE id = ?',
  59                 'rights' => 'SELECT rightId FROM rights WHERE userId = ?',
  60                 'newitem' => 'INSERT INTO locations (id, parent) VALUES(?, ?)',
  61                 'newhistory' => 'INSERT INTO history (location, owner, discussion, nodename, nodenote) VALUES(?, ?, ?, ?, ?)',
  62                 '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',
  63                 'admindump' => "$adminDumpSql 100",#Dumps new discussion submits with their senders and corresponding main history and names
  64                 'delete-hist' => 'DELETE FROM history WHERE id = ?',
  65                 'mark-checked' => 'UPDATE history SET seen = 1 WHERE id = ?',
  66                 'delete-item' => 'DELETE FROM locations WHERE id = ?',
  67                 'set-mainhist' => 'UPDATE locations SET
  68                                 mainhistory = ?,
  69                                 name = ( SELECT nodename FROM history WHERE id = ? ),
  70                                 note = ( SELECT nodenote FROM history WHERE id = ? )
  71                         WHERE
  72                                 id = ?',
  73                 'profiledata' => 'SELECT email, xmpp, login, mailgather, xmppgather FROM users WHERE id = ?',
  74                 'pushprofile' => 'UPDATE users SET xmpp = ?, login = ?, mailgather = ?, xmppgather = ? WHERE id = ?',
  75                 'setemail' => 'UPDATE users SET email = ?, passwd = ? WHERE id = ?',
  76                 'notifuser' => 'SELECT location, recursive FROM notifications WHERE user = ? ORDER BY location',
  77                 'notifdata' => 'SELECT recursive, type, notification FROM notifications WHERE user = ? AND location = ?',
  78                 'drop-notif' => 'DELETE FROM notifications WHERE user = ? AND location = ?',
  79                 'new-notif' => 'INSERT INTO notifications (user, location, recursive, type, notification) VALUES (?, ?, ?, ?, ?)',
  80                 'notify' => 'INSERT INTO pending (user, history, notification, reason) SELECT DISTINCT user, ?, ?, ? FROM notifications WHERE ( notification = 2 OR notification = ? ) AND type <= ? AND ( location = ? OR ( recursive = 1 AND SUBSTR( ?, 1, LENGTH( location ) ) = location ) )',
  81                 'newtime-mail' => 'UPDATE users SET nextmail = FROM_UNIXTIME( UNIX_TIMESTAMP( NOW() ) + 60 * mailgather ) WHERE nextmail < NOW() AND EXISTS ( SELECT 1 FROM notifications WHERE ( notification = 0 OR notification = 2 ) AND type <= ? AND ( location = ? OR ( recursive = 1 AND SUBSTR( ?, 1, LENGTH( location ) ) = location ) ) )',
  82                 'newtime-xmpp' => 'UPDATE users SET nextxmpp = FROM_UNIXTIME( UNIX_TIMESTAMP( NOW() ) + 60 * xmppgather ) WHERE nextxmpp < NOW() AND EXISTS ( SELECT 1 FROM notifications WHERE ( notification = 1 OR notification = 2 ) AND type <= ? AND ( location = ? OR ( recursive = 1 AND SUBSTR( ?, 1, LENGTH( location ) ) = location ) ) )',
  83                 'mailout' => 'SELECT
  84                                 pending.user, users.email,
  85                                 pending.reason, history.discussion, history.nodename, history.nodenote, history.time,
  86                                 auth.login, history.location, locations.name, locations.note
  87                         FROM
  88                                 pending
  89                                 INNER JOIN users ON users.id = pending.user
  90                                 INNER JOIN history ON history.id = pending.history
  91                                 INNER JOIN locations ON history.location = locations.id
  92                                 INNER JOIN users AS auth ON auth.id = history.owner
  93                         WHERE
  94                                 pending.notification = 0
  95                                 AND users.nextmail <= ?
  96                         ORDER BY
  97                                 pending.user, pending.reason, history.time, history.location',
  98                 'xmppout' => 'SELECT
  99                                 pending.user, users.xmpp,
 100                                 pending.reason, history.discussion, history.nodename, history.nodenote, history.time,
 101                                 auth.login, history.location, locations.name, locations.note
 102                         FROM
 103                                 pending
 104                                 INNER JOIN users ON users.id = pending.user
 105                                 INNER JOIN history ON history.id = pending.history
 106                                 INNER JOIN locations ON history.location = locations.id
 107                                 INNER JOIN users AS auth ON auth.id = history.owner
 108                         WHERE
 109                                 pending.notification = 1
 110                                 AND users.nextxmpp <= ?
 111                                 AND users.xmpp IS NOT NULL
 112                         ORDER BY
 113                                 pending.user, pending.reason, history.time, history.location',
 114                 'dropnotifsxmpp' => 'DELETE FROM pending WHERE notification = 1 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextxmpp <= ? )',
 115                 'dropnotifsmail' => 'DELETE FROM pending WHERE notification = 0 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextmail <= ? )',
 116                 'time' => 'SELECT NOW()',
 117                 '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 LENGTH(l.id), l.id',
 118                 '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 LENGTH(l.id), l.id',
 119                 'hasChildren' => 'SELECT DISTINCT 1 FROM locations WHERE parent = ?',
 120                 'hasMain' => 'SELECT DISTINCT 1 FROM locations WHERE id = ? AND mainhistory IS NOT NULL',
 121                 'notif-exists' => 'SELECT DISTINCT 1 FROM notifications WHERE user = ? AND ( location = ? OR ( recursive = 1 AND type <= 1 AND SUBSTR( ?, 1, LENGTH( location ) ) = location ) )',
 122                 'itemname' => 'SELECT name FROM locations WHERE id = ?',
 123                 'admincount' => 'SELECT COUNT( DISTINCT location ) FROM history WHERE seen = 0 AND location LIKE ?'
 124         } );
 125 }
 126
 127 sub hasChildren( $$ ) {
 128         my( $self, $parent ) = @_;
 129         return scalar @{$self->query( 'hasChildren', [ $parent ] )};
 130 }
 131
 132 sub hasMain( $$ ) {
 133         my( $self, $id ) = @_;
 134         return scalar @{$self->query( 'hasMain', [ $id ] )};
 135 }
 136
 137 my %sorts = ( 'id' => 1, 'rid' => 1, 'name' => 1, 'rname' => 1 );
 138
 139 sub nodes( $$$$ ) {
 140         my( $self, $parent, $args, $restrict ) = @_;
 141         my $q = 'id';
 142         $q = $args->{'sort'} if( defined( $args->{'sort'} ) && defined( $sorts{$args->{'sort'}} ) );
 143         if( defined( $restrict ) && ( $restrict ne "" ) ) {
 144                 return $self->query( 'nodes-'.$q.'-r', [ $parent, $parent.'/'.$restrict.'%' ] );
 145         } else {
 146                 return $self->query( 'nodes-'.$q, [ $parent ] );
 147         }
 148 }
 149
 150 sub item( $$ ) {
 151         my( $self, $id ) = @_;
 152         my $result = $self->query( "item", [ $id ] );
 153         if( scalar @{$result} ) {
 154                 return $result->[ 0 ];
 155         } else {
 156                 return undef;
 157         }
 158 }
 159
 160 sub hasLogin( $$ ) {
 161         my( $self, $login ) = @_;
 162         my $result = $self->query( 'login', [ $login ] );
 163         return scalar @{$result};
 164 }
 165
 166 sub hasEmail( $$ ) {
 167         my( $self, $email ) = @_;
 168         my $result = $self->query( 'email', [ $email ] );
 169         return scalar @{$result};
 170 }
 171
 172 sub addUser( $$$$ ) {
 173         my( $self, $login, $email, $passwd ) = @_;
 174         eval {
 175                 if( ( defined $login ) && ( $login ne '' ) ) {
 176                         $self->command( 'adduser', [ $login, $email, $passwd ] );
 177                 } else {
 178                         $self->command( 'adduser-null', [ $email, $passwd ] );
 179                 }
 180         };
 181         if( $@ ) {
 182                 return 0;
 183         } else {
 184                 return $self->last();
 185         }
 186 }
 187
 188 sub getLogInfo( $$ ) {
 189         my( $self, $info ) = @_;
 190         my $data;
 191         if( $info =~ /@/ ) {#An email address
 192                 $data = $self->query( 'loginfomail', [ $info ] );
 193         } else {
 194                 $data = $self->query( 'loginfoname', [ $info ] );
 195         }
 196         if( scalar @{$data} ) {
 197                 my( $id, $passwd, $logtime, $lastlog, $email ) = @{$data->[ 0 ]};
 198                 my $logstring;
 199                 $logstring = "Last logged from $lastlog at $logtime" if( defined $logtime && defined $lastlog );
 200                 $email = $info if( $info =~ /@/ );
 201                 return( $id, $passwd, $email, $logstring );
 202         } else {
 203                 return undef;
 204         }
 205 }
 206
 207 sub rights( $$ ) {
 208         my( $self, $id ) = @_;
 209         return $self->query( 'rights', [ $id ] );
 210 }
 211
 212 sub setLastLog( $$$ ) {
 213         my( $self, $id, $from ) = @_;
 214         $self->command( 'setlastlog', [ $from, $id ] );
 215 }
 216
 217 sub history( $$ ) {
 218         my( $self, $addr ) = @_;
 219         return $self->query( 'history', [ $addr ] );
 220 }
 221
 222 sub spaceNorm( $ ) {
 223         $_ = shift;
 224         return undef unless defined $_;
 225         s/[ \t]+/ /g;
 226         s/^\s//;
 227         s/\s$//;
 228         s/\r//;
 229         return $_;
 230 }
 231
 232 sub submitItem( $$$ ) {
 233         my( $self, $data, $auth ) = @_;
 234         my( $addr ) = ( $data->{'address'} );
 235         foreach( @{$addr->addressDeps()} ) {
 236                 my( $dep, $error ) = @{$_};
 237                 return ( $error, undef ) unless defined $self->item( $dep->get(), 0 );
 238         }
 239         return( 'exists', undef ) if( defined( $self->item( $addr->get(), 0 ) ) );
 240         eval {
 241                 $self->command( 'newitem', [ $addr->get(), $addr->parent()->get() ] );
 242                 $self->command( 'newhistory', [ $addr->get(), $auth->{'authid'}, spaceNorm( $data->{'discussion'} ), spaceNorm( $data->{'name'} ), spaceNorm( $data->{'note'} ) ] );
 243         };
 244         if( $@ ) {
 245                 $self->rollback();
 246                 return( 'internal: '.$@, undef );
 247         }
 248         return( '', $self->last() );
 249 }
 250
 251 sub submitHistory( $$$$ ) {
 252         my( $self, $data, $auth, $address ) = @_;
 253         if( $data->{'delete'} ) {
 254                 $self->command( 'newhistory', [ $address->get(), $auth->{'authid'}, spaceNorm( $data->{'text'} ), '', '' ], 1 );
 255         } else {
 256                 $data->{'name'} = undef if defined $data->{'name'} && $data->{'name'} eq '';
 257                 $self->command( 'newhistory', [ $address->get(), $auth->{'authid'}, spaceNorm( $data->{'text'} ), spaceNorm( $data->{'name'} ), spaceNorm( $data->{'note'} ) ], 1 );
 258         }
 259         return $self->last();
 260 }
 261
 262 sub adminDump( $$$ ) {
 263         my( $self, $prefix, $limit ) = @_;
 264         if( $limit ) {
 265                 $limit = int( $limit * 1.2 );
 266                 my $q = $self->{'dbh'}->prepare( "$adminDumpSql $limit" );
 267                 $q->execute( "$prefix%" );
 268                 my @result = @{$q->fetchall_arrayref()};#Copy the array, finish() deletes the content
 269                 $q->finish();
 270                 return \@result;
 271         } else {
 272                 return $self->query( 'admindump', [ "$prefix%" ] );
 273         }
 274 }
 275
 276 sub deleteHistory( $$ ) {
 277         my( $self, $id ) = @_;
 278         $self->command( 'delete-hist', [ $id ] );
 279 }
 280
 281 sub markChecked( $$ ) {
 282         my( $self, $id ) = @_;
 283         $self->command( 'mark-checked', [ $id ] );
 284 }
 285
 286 sub deleteItem( $$ ) {
 287         my( $self, $id ) = @_;
 288         $self->command( 'delete-item', [ $id ] );
 289 }
 290
 291 sub setMainHistory( $$$ ) {
 292         my( $self, $location, $history ) = @_;
 293         $self->command( 'set-mainhist', [ $history, $history, $history, $location ] );
 294 }
 295
 296 sub resetInfo( $$ ) {
 297         my( $self, $mail ) = @_;
 298         my $result = $self->query( 'resetinfo', [ $mail ] );
 299         if( scalar @{$result} ) {
 300                 return ( @{$result->[0]} );
 301         } else {
 302                 return undef;
 303         }
 304 }
 305
 306 sub changePasswd( $$$ ) {
 307         my( $self, $id, $passwd ) = @_;
 308         $self->command( 'changepasswd', [ $passwd, $id ] );
 309 }
 310
 311 sub profileData( $$ ) {
 312         my( $self, $id ) = @_;
 313         my %result;
 314         ( $result{'email'}, $result{'xmpp'}, $result{'login'}, $result{'email_time'}, $result{'xmpp_time'} ) = @{$self->query( 'profiledata', [ $id ] )->[0]};
 315         return \%result;
 316 }
 317
 318 sub setEmail( $$$$ ) {
 319         my( $self, $id, $email, $passwd ) = @_;
 320         $self->command( 'setemail', [ $email, $passwd, $id ] );
 321 }
 322
 323 sub pushProfile( $$$$$$ ) {
 324         my( $self, $id, $login, $xmpp, $mailgather, $xmppgather ) = @_;
 325         $self->command( 'pushprofile', [ $xmpp, $login, $mailgather, $xmppgather, $id ] );
 326 }
 327
 328 sub notificationsUser( $$ ) {
 329         my( $self, $uid ) = @_;
 330         return $self->query( 'notifuser', [ $uid ] );
 331 }
 332
 333 sub getNotifData( $$$ ) {
 334         my( $self, $uid, $location ) = @_;
 335         my $result = $self->query( 'notifdata', [ $uid, $location ] );
 336         if( @{$result} ) {
 337                 my( $recursive, $notification, $way ) = @{$result->[0]};
 338                 return {
 339                         'recursive' => $recursive,
 340                         'notification' => $notification,
 341                         'way' => $way };
 342         } else {
 343                 return { 'recursive' => 1 };
 344         }
 345 }
 346
 347 sub submitNotification( $$$$ ) {
 348         my( $self, $uid, $location, $data ) = @_;
 349         $self->command( 'drop-notif', [ $uid, $location ] );
 350         $self->command( 'new-notif', [ $uid, $location, $data->{'recursive'}, $data->{'notification'}, $data->{'way'} ] ) unless( $data->{'notification'} == 3 );
 351 }
 352
 353 sub pushNotifications( $$$$$ ) {
 354         my( $self, $location, $history, $priority, $reason ) = @_;
 355         $self->command( 'notify', [ $history, 0, $reason, 0, $priority, $location, $location ] );
 356         $self->command( 'notify', [ $history, 1, $reason, 1, $priority, $location, $location ] );
 357         $self->command( 'newtime-mail', [ $priority, $location, $location ] );
 358         $self->command( 'newtime-xmpp', [ $priority, $location, $location ] );
 359 }
 360
 361 sub notifExists( $$$ ) {
 362         my( $self, $user, $location ) = @_;
 363         return scalar @{$self->query( 'notif-exists', [ $user, $location, $location ] )};
 364 }
 365
 366 sub mailNotifs( $$ ) {
 367         my( $self, $time ) = @_;
 368         return $self->query( 'mailout', [ $time ] );
 369 }
 370
 371 sub xmppNotifs( $$ ) {
 372         my( $self, $time ) = @_;
 373         return $self->query( 'xmppout', [ $time ] );
 374 }
 375
 376 sub time( $ ) {
 377         my( $self ) = @_;
 378         return $self->query( 'time', [] )->[0]->[0];
 379 }
 380
 381 sub dropNotifs( $$ ) {
 382         my( $self, $time ) = @_;
 383         $self->command( 'dropnotifsmail', [ $time ] );
 384         $self->command( 'dropnotifsxmpp', [ $time ] );
 385 }
 386
 387 sub searchName( $$$ ) {
 388         my( $self, $search, $prefix ) = @_;
 389         return $self->query( 'searchlocalname', [ "%$search%", "$prefix/%" ] ) if defined $prefix;
 390         return $self->query( 'searchname', [ "%$search%" ] );
 391 }
 392
 393 sub itemName( $$ ) {
 394         my( $self, $id ) = @_;
 395         my $result = $self->query( 'itemname', [ $id ] )->[0]->[0];
 396         return defined $result ? $result : '';
 397 }
 398
 399 sub adminCount( $$ ) {
 400         my( $tables, $prefix ) = @_;
 401         return $tables->query( 'admincount', [ "$prefix%" ] )->[0]->[0];
 402 }
 403
 404 1;