site/cgi-bin/modules/BSE/DB/Mysql.pm

   1 package BSE::DB::Mysql;
   2 use strict;
   3 use DBI;
   4 use vars qw/@ISA/;
   5 @ISA = qw(BSE::DB);
   6
   7 use vars qw($VERSION);
   8
   9 use Constants 0.1 qw/$DSN $UN $PW/;
  10
  11 use Carp;
  12
  13 my $self;
  14
  15 $VERSION = 1.01;
  16
  17 my %statements =
  18   (
  19    Articles => 'select * from article',
  20    replaceArticle =>
  21      'replace article values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  22    addArticle =>
  23      'insert article values (null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  24    deleteArticle => 'delete from article where id = ?',
  25    getArticleByPkey => 'select * from article where id = ?',
  26
  27    getArticleByLevel => 'select * from article where level = ?',
  28    getArticleByParentid => 'select * from article where parentid = ?',
  29    'Articles.stepParents' => <<EOS,
  30 select ar.* from article ar, other_parents op
  31   where ar.id = op.parentId and op.childId = ?
  32 order by op.childDisplayOrder desc
  33 EOS
  34    'Articles.stepKids' => <<EOS,
  35 select ar.* from article ar, other_parents op
  36    where op.childId = ar.id and op.parentId = ?
  37 EOS
  38 # originally "... and ? between op.release and op.expire"
  39 # but since the first argument was a string, mysql treated the comparisons
  40 # as string comparisons
  41    'Articles.visibleStepKids' => <<EOS,
  42 select ar.* from article ar, other_parents op
  43    where op.childId = ar.id
  44      and op.parentId = ?
  45      and date_format(?, '%Y%m%d') between date_format(op.release, '%Y%m%d') and date_format(op.expire, '%Y%m%d') and listed <> 0
  46 EOS
  47    'Articles.ids'=>'select id from article',
  48
  49    Images => 'select * from image',
  50    replaceImage =>
  51      'replace image values (?,?,?,?,?,?,?,?,?)',
  52    addImage => 'insert image values(null, ?, ?, ?, ?, ?, ?, ?, ?)',
  53    deleteImage => 'delete from image where id = ?',
  54    getImageByArticleId => 'select * from image where articleId = ? order by displayOrder',
  55
  56    dropIndex => 'delete from searchindex',
  57    insertIndex => 'insert searchindex values(?, ?, ?, ?)',
  58    searchIndex => 'select * from searchindex where id = ?',
  59    searchIndexWC => 'select * from searchindex where id like ?',
  60
  61    Products=> 'select article.*, product.* from article, product where id = articleId',
  62    addProduct => 'insert product values(?,?,?,?,?,?,?,?,?,?,?)',
  63    getProductByPkey => 'select article.*, product.* from article, product where id=? and articleId = id',
  64    replaceProduct => 'replace product values(?,?,?,?,?,?,?,?,?,?,?)',
  65    'Products.stepProducts' => <<EOS,
  66 select ar.*, pr.* from article ar, product pr, other_parents op
  67    where ar.id = pr.articleId and op.childId = ar.id and op.parentId = ?
  68 EOS
  69    'Products.visibleStep' => <<EOS,
  70 select ar.*, pr.* from article ar, product pr, other_parents op
  71    where ar.id = pr.articleId and op.childId = ar.id
  72      and op.parentId = ? and ? between op.release and op.expire
  73 EOS
  74    'Products.subscriptionDependent' => <<SQL,
  75 select ar.*, pr.* from article ar, product pr
  76    where ar.id = pr.articleId
  77      and (pr.subscription_id = ? or subscription_required = ?)
  78 SQL
  79    'Products.orderProducts' => <<SQL,
  80 select ar.*, pr.* from article ar, product pr, order_item oi
  81   where oi.orderId = ? and oi.productId = ar.id and ar.id = pr.articleId
  82 SQL
  83    deleteProduct => 'delete from product where articleId = ?',
  84    Orders => 'select * from orders',
  85    getOrderByPkey => 'select * from orders where id = ?',
  86    getOrderItemByOrderId => 'select * from order_item where orderId = ?',
  87    addOrder => 'insert orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  88    replaceOrder => 'replace orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  89    addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  90    getOrderByUserId => 'select * from orders where userId = ?',
  91    deleteOrdersItems => 'delete from order_item where orderId = ?',
  92
  93    getOrderItemByProductId => 'select * from order_item where productId = ?',
  94
  95    OtherParents => 'select * from other_parents',
  96    getOtherParentByChildId => <<EOS,
  97 select * from other_parents where childId = ? order by childDisplayOrder desc
  98 EOS
  99    getOtherParentByParentId => <<EOS,
 100 select * from other_parents where parentId = ? order by parentDisplayOrder desc
 101 EOS
 102    getOtherParentByParentIdAndChildId =>
 103    'select * from other_parents where parentId = ? and childId = ?',
 104    addOtherParent=>'insert other_parents values(null,?,?,?,?,?,?)',
 105    deleteOtherParent => 'delete from other_parents where id = ?',
 106    replaceOtherParent=>'replace other_parents values(?,?,?,?,?,?,?)',
 107    'OtherParents.anylinks' =>
 108    'select * from other_parents where childId = ? or parentId = ?',
 109
 110    addArticleFile =>
 111    'insert into article_files values (null,?,?,?,?,?,?,?,?,?,?,?)',
 112    replaceArticleFile =>
 113    'replace article_files values (?,?,?,?,?,?,?,?,?,?,?,?)',
 114    deleteArticleFile => 'delete from article_files where id = ?',
 115    getArticleFileByArticleId =>
 116    'select * from article_files where articleId = ? order by displayOrder desc',
 117    getArticleFileByPkey => 'select * from article_files where id = ?',
 118
 119    orderFiles =><<SQL,
 120 select distinct af.*, oi.id as item_id
 121 from article_files af, order_item oi
 122 where af.articleId = oi.productId and oi.orderId = ?
 123 order by oi.id, af.displayOrder desc
 124 SQL
 125
 126    getSiteUserByUserId =>
 127    'select * from site_users where userId = ?',
 128    getSiteUserByPkey =>
 129    'select * from site_users where id = ?',
 130    getSiteUserByAffiliate_name =>
 131    'select * from site_users where affiliate_name = ?',
 132    addSiteUser => 'insert site_users values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
 133    replaceSiteUser => 'replace site_users values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
 134    'SiteUsers.removeSubscriptions'=>
 135    'delete from subscribed_users where userId = ?',
 136    'SiteUsers.removeSub'=>
 137    'delete from subscribed_users where userId = ? and subId = ?',
 138    'SiteUsers.subRecipients' => <<EOS,
 139 select si.* from site_users si, subscribed_users su
 140   where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
 141 EOS
 142    SiteUsers => 'select * from site_users',
 143    'SiteUsers.allSubscribers' => <<SQL,
 144 select distinct su.*
 145   from site_users su, orders od, order_item oi
 146   where su.id = od.siteuser_id and od.id = oi.orderId
 147         and oi.subscription_id <> -1
 148 SQL
 149    getBSESiteuserImage => <<SQL,
 150 select * from bse_siteuser_images
 151   where siteuser_id = ? and image_id = ?
 152 SQL
 153    getBSESiteuserImages => <<SQL,
 154 select * from bse_siteuser_images where siteuser_id = ?
 155 SQL
 156    addBSESiteuserImage => <<SQL,
 157 insert bse_siteuser_images values(?,?,?,?,?,?,?,?)
 158 SQL
 159    replaceBSESiteuserImage => <<SQL,
 160 replace bse_siteuser_images values(?,?,?,?,?,?,?,?)
 161 SQL
 162    deleteBSESiteuserImage=> <<SQL,
 163 delete from bse_siteuser_images where siteuser_id = ? and image_id = ?
 164 SQL
 165
 166    SubscriptionTypes =>
 167    'select * from subscription_types',
 168    addSubscriptionType=>
 169    'insert subscription_types values(null,?,?,?,?,?,?,?,?,?,?,?,?)',
 170    replaceSubscriptionType=>
 171    'replace subscription_types values(?,?,?,?,?,?,?,?,?,?,?,?,?)',
 172    getSubscriptionTypeByPkey =>
 173    'select * from subscription_types where id = ? order by name',
 174    deleteSubscriptionType =>
 175    'delete from subscription_types where id = ?',
 176    subRecipientCount => <<EOS,
 177 select count(*) as "count" from site_users si, subscribed_users su
 178   where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
 179 EOS
 180    'SubscriptionTypes.userSubscribedTo' => <<'EOS',
 181 select su.* from subscription_types su, subscribed_users us
 182   where us.userId = ? and us.subId = su.id
 183 EOS
 184
 185    addSubscribedUser=>
 186    'insert subscribed_users values(null,?,?)',
 187    getSubscribedUserByUserId =>
 188    'select * from subscribed_users where userId = ?',
 189
 190    # the following don't work with the row/table classes
 191    articlesList =>
 192    'select id, title from article order by level, displayOrder desc',
 193
 194    getEmailBlackEntryByEmail =>
 195    'select * from email_blacklist where email = ?',
 196    addEmailBlackEntry =>
 197    'insert email_blacklist values(null,?,?)',
 198
 199    addEmailRequest =>
 200    'insert email_requests values(null,?,?,?,?)',
 201    replaceEmailRequest =>
 202    'replace email_requests values(?,?,?,?,?)',
 203    deleteEmailRequest =>
 204    'delete from email_requests where id = ?',
 205    getEmailRequestByGenEmail =>
 206    'select * from email_requests where genEmail = ?',
 207
 208    addAdminBase => 'insert into admin_base values(null, ?)',
 209    replaceAdminBase => 'replace into admin_base values(?, ?)',
 210    deleteAdminBase => 'delete from admin_base where id = ?',
 211    getAdminBaseByPkey => 'select * from admin_base where id=?',
 212
 213    AdminUsers => <<SQL,
 214 select bs.*, us.* from admin_base bs, admin_users us
 215   where bs.id = us.base_id
 216    order by logon
 217 SQL
 218    getAdminUserByLogon => <<SQL,
 219 select bs.*, us.* from admin_base bs, admin_users us
 220   where bs.id = us.base_id and us.logon = ?
 221 SQL
 222    getAdminUserByPkey => <<SQL,
 223 select bs.*, us.* from admin_base bs, admin_users us
 224   where bs.id = us.base_id and bs.id = ?
 225 SQL
 226    addAdminUser => 'insert into admin_users values(?,?,?,?,?)',
 227    replaceAdminUser => 'replace into admin_users values(?,?,?,?,?)',
 228    deleteAdminUser => 'delete from admin_users where base_id = ?',
 229    adminUsersGroups => <<SQL,
 230 select bs.*, gr.*
 231   from admin_base bs, admin_groups gr, admin_membership am
 232   where bs.id = gr.base_id && am.user_id = ? and am.group_id = bs.id
 233   order by gr.name
 234 SQL
 235    userGroups => 'select * from admin_membership where user_id = ?',
 236    deleteUserGroups => 'delete from admin_membership where user_id = ?',
 237
 238    AdminGroups => <<SQL,
 239 select bs.*, gr.*
 240   from admin_base bs, admin_groups gr
 241   where bs.id = gr.base_id
 242   order by name
 243 SQL
 244    adminGroupsUsers => <<SQL,
 245 select bs.*, us.*
 246   from admin_base bs, admin_users us, admin_membership am
 247   where bs.id = us.base_id && am.group_id = ? and am.user_id = bs.id
 248   order by logon
 249 SQL
 250    getAdminGroupByName => <<SQL,
 251 select bs.*, gr.* from admin_base bs, admin_groups gr
 252   where bs.id = gr.base_id and gr.name = ?
 253 SQL
 254    getAdminGroupByPkey => <<SQL,
 255 select bs.*, gr.* from admin_base bs, admin_groups gr
 256   where bs.id = gr.base_id and bs.id = ?
 257 SQL
 258    addAdminGroup => 'insert into admin_groups values(?,?,?,?)',
 259    replaceAdminGroup => 'replace into admin_groups values(?,?,?,?)',
 260    deleteAdminGroup => 'delete from admin_groups where base_id = ?',
 261    groupUsers => 'select * from admin_membership where group_id = ?',
 262    'AdminGroups.userPermissionGroups' => <<SQL,
 263 select bs.*, ag.* from admin_base bs, admin_groups ag, admin_membership am
 264 where bs.id = ag.base_id
 265   and ( (ag.base_id = am.group_id and am.user_id = ?)
 266         or ag.name = 'everyone' )
 267 SQL
 268
 269    addUserToGroup => 'insert into admin_membership values(?,?)',
 270    delUserFromGroup => <<SQL,
 271 delete from admin_membership where user_id = ? and group_id = ?
 272 SQL
 273    deleteGroupUsers => 'delete from admin_membership where group_id = ?',
 274
 275    articleObjectPerm => <<SQL,
 276 select * from admin_perms where object_id = ? and admin_id = ?
 277 SQL
 278    addArticleObjectPerm => 'insert into admin_perms values(?,?,?)',
 279    replaceArticleObjectPerm => 'replace into admin_perms values(?,?,?)',
 280    userPerms => <<SQL,
 281 select distinct ap.*
 282 from admin_perms ap
 283 where ap.admin_id = ?
 284 SQL
 285    groupPerms => <<SQL,
 286 select distinct ap.*
 287 from admin_perms ap, admin_membership am
 288 where ap.admin_id = am.group_id and am.user_id = ?
 289 SQL
 290    commonPerms => <<SQL,
 291 select distinct ap.*
 292 from admin_perms ap, admin_groups ag
 293 where ap.admin_id = ag.base_id and ag.name = 'everyone'
 294 SQL
 295    Subscriptions => 'select * from bse_subscriptions',
 296    addSubscription => 'insert bse_subscriptions values(null,?,?,?,?)',
 297    replaceSubscription => 'replace bse_subscriptions values(?,?,?,?,?)',
 298    deleteSubscription => <<SQL,
 299 delete from bse_subscriptions where subscription_id = ?
 300 SQL
 301    getSubscriptionByPkey => <<SQL,
 302 select * from bse_subscriptions where subscription_id = ?
 303 SQL
 304    getSubscriptionByText_id => <<SQL,
 305 select * from bse_subscriptions where text_id = ?
 306 SQL
 307    subscriptionOrderItemCount => <<SQL,
 308 select count(*) as "count" from order_item where subscription_id = ?
 309 SQL
 310    subscriptionOrderSummary => <<SQL,
 311 select od.id, od.userId, od.orderDate, od.siteuser_id,
 312     sum(oi.subscription_period * oi.units) as "subscription_period"
 313   from orders od, order_item oi
 314   where oi.subscription_id = ? and od.id = oi.orderId and od.complete <> 0
 315   group by od.id, od.userId, od.orderDate, od.siteuser_id
 316   order by od.orderDate desc
 317 SQL
 318    subscriptionUserSummary => <<SQL,
 319 select su.*, us.*
 320   from site_users su, bse_user_subscribed us
 321 where su.id = us.siteuser_id and us.subscription_id = ?
 322 SQL
 323    subscriptionProductCount => <<SQL,
 324 select count(*) as "count" from product
 325   where subscription_id = ? or subscription_required = ?
 326 SQL
 327    removeUserSubscribed => <<SQL,
 328 delete from bse_user_subscribed where subscription_id = ? and siteuser_id = ?
 329 SQL
 330    addUserSubscribed => <<SQL,
 331 insert bse_user_subscribed values (?,?,?,?,?)
 332 SQL
 333    subscriptionUserBought => <<SQL,
 334 select od.orderDate,
 335   oi.subscription_period * oi.units as "subscription_period",
 336   oi.max_lapsed,
 337   od.id as "order_id", oi.id as "item_id", oi.productId as "product_id"
 338   from orders od, order_item oi
 339   where oi.subscription_id = ? and od.id = oi.orderId and od.siteuser_id = ?
 340         and od.complete <> 0
 341 SQL
 342    userSubscribedEntry => <<SQL,
 343 select * from bse_user_subscribed
 344   where siteuser_id = ? and subscription_id = ?
 345 SQL
 346    siteuserSubscriptions => <<SQL,
 347 select su.*, us.started_at, us.ends_at, us.max_lapsed
 348   from bse_subscriptions su, bse_user_subscribed us
 349 where us.siteuser_id = ? and us.subscription_id = su.subscription_id
 350    and us.ends_at >= curdate()
 351 SQL
 352
 353    addLocation => <<SQL,
 354 insert bse_locations values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 355 SQL
 356    replaceLocation => <<SQL,
 357 replace bse_locations values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 358 SQL
 359    getLocationByPkey => 'select * from bse_locations where id = ?',
 360    deleteLocation => 'delete from bse_locations where id = ?',
 361    Locations => 'select * from bse_locations order by description',
 362
 363    Seminars => <<SQL,
 364 select ar.*, pr.*, se.*
 365   from article ar, product pr, bse_seminars se
 366   where ar.id = pr.articleId and ar.id = se.articleId
 367 SQL
 368    addSeminar => 'insert bse_seminars values(?,?)',
 369    replaceSeminar => 'replace bse_seminars values(?,?)',
 370    getSeminarByPkey => <<SQL,
 371 select ar.*, pr.*, se.*
 372   from article ar, product pr, bse_seminars se
 373   where id = ? and ar.id = pr.articleId and ar.id = se.seminar_id
 374 SQL
 375   );
 376
 377 sub _single
 378 {
 379   my $class = shift;
 380   warn "Incorrect number of parameters passed to DatabaseHandle::single\n" unless @_ == 0;
 381
 382   unless ( defined $self ) {
 383     my $dbh = DBI->connect_cached( $DSN, $UN, $PW)
 384       or die "Cannot connect to database: $DBI::errstr";
 385
 386     $self = bless { dbh => $dbh }, $class;
 387   }
 388   $self;
 389 }
 390
 391 my $get_sql_by_name = 'select sql_statement from sql_statements where name=?';
 392
 393 sub stmt {
 394   my ($self, $name) = @_;
 395
 396   $name =~ s/BSE.*:://;
 397
 398   my $sql = $statements{$name};
 399   unless ($sql) {
 400     my @row = $self->{dbh}->selectrow_array($get_sql_by_name, {}, $name);
 401     if (@row) {
 402       $sql = $row[0];
 403       #print STDERR "Found SQL '$sql'\n";
 404     }
 405     else {
 406       print STDERR "SQL statment $name not found in sql_statements table\n";
 407     }
 408   }
 409   $sql or confess "Statement named '$name' not found";
 410   my $sth = $self->{dbh}->prepare($sql)
 411     or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
 412
 413   $sth;
 414 }
 415
 416 sub insert_id {
 417   my ($self, $sth) = @_;
 418
 419   my $id = $sth->{"mysql_insertid"};
 420
 421   return $id;
 422 }
 423
 424 # gotta love this
 425 sub DESTROY
 426 {
 427   my ($self) = @_;
 428   # this is wierd - we only need to reset this on 5.6.x (for x == 0 so
 429   # far)
 430   # Works fine without the reset for 5.005_03
 431   if ($self->{dbh}) {
 432     $self->{dbh}->disconnect;
 433     delete $self->{dbh};
 434   }
 435 }
 436
 437 1;
 438