1 package BSE::DB::Mysql;
8 use vars qw($VERSION $MAX_CONNECTION_AGE);
10 use Constants 0.1 qw/$DSN $UN $PW $DBOPTS/;
18 $MAX_CONNECTION_AGE = 1200;
22 # don't ever load the entire articles table
23 #Articles => 'select * from article',
25 'replace article values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
27 'insert article values (null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
28 deleteArticle => 'delete from article where id = ?',
29 getArticleByPkey => 'select * from article where id = ?',
31 getArticleByLevel => 'select * from article where level = ?',
32 getArticleByParentid => 'select * from article where parentid = ?',
33 getArticleByLinkAlias => 'select * from article where linkAlias = ?',
34 'Articles.stepParents' => <<EOS,
35 select ar.* from article ar, other_parents op
36 where ar.id = op.parentId and op.childId = ?
37 order by op.childDisplayOrder desc
39 'Articles.visibleStepParents' => <<EOS,
40 select ar.* from article ar, other_parents op
41 where ar.id = op.parentId and op.childId = ?
42 and date_format(?, '%Y%m%d') between date_format(op.release, '%Y%m%d') and date_format(op.expire, '%Y%m%d')
44 order by op.childDisplayOrder desc
46 'Articles.stepKids' => <<EOS,
47 select ar.* from article ar, other_parents op
48 where op.childId = ar.id and op.parentId = ?
50 # originally "... and ? between op.release and op.expire"
51 # but since the first argument was a string, mysql treated the comparisons
52 # as string comparisons
53 'Articles.visibleStepKids' => <<EOS,
54 select ar.* from article ar, other_parents op
55 where op.childId = ar.id
57 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
59 'Articles.ids'=>'select id from article',
60 articlePossibleStepparents => <<EOS,
61 select a.id, a.title from article a
62 where a.id not in (select parentId from other_parents where childId = ?)
65 articlePossibleStepchildren => <<EOS,
66 select a.id, a.title from article a
67 where a.id not in (select childId from other_parents where parentId = ?)
70 bse_MaxArticleDisplayOrder => <<EOS,
71 select max(displayOrder) as "displayOrder" from article
74 Images => 'select * from image',
76 'replace image values (?,?,?,?,?,?,?,?,?,?,?)',
77 addImage => 'insert image values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
78 deleteImage => 'delete from image where id = ?',
79 getImageByArticleId => 'select * from image where articleId = ? order by displayOrder',
80 getImageByPkey => 'select * from image where id = ?',
81 getImageByArticleIdAndName => <<SQL,
82 select * from image where articleId = ? and name = ?
85 dropIndex => 'delete from searchindex',
86 insertIndex => 'insert searchindex values(?, ?, ?, ?)',
87 searchIndex => 'select * from searchindex where id = ?',
88 searchIndexWC => 'select * from searchindex where id like ?',
90 Products=> 'select article.*, product.* from article, product where id = articleId',
91 addProduct => 'insert product values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
92 getProductByPkey => 'select article.*, product.* from article, product where id=? and articleId = id',
93 getProductByProduct_code => 'select article.*, product.* from article, product where product_code=? and articleId = id',
94 replaceProduct => 'replace product values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
95 'Products.stepProducts' => <<EOS,
96 select ar.*, pr.* from article ar, product pr, other_parents op
97 where ar.id = pr.articleId and op.childId = ar.id and op.parentId = ?
99 'Products.visibleStep' => <<EOS,
100 select ar.*, pr.* from article ar, product pr, other_parents op
101 where ar.id = pr.articleId and op.childId = ar.id
102 and op.parentId = ? and ? between op.release and op.expire
104 'Products.subscriptionDependent' => <<SQL,
105 select ar.*, pr.* from article ar, product pr
106 where ar.id = pr.articleId
107 and (pr.subscription_id = ? or subscription_required = ?)
109 'Products.orderProducts' => <<SQL,
110 select ar.*, pr.* from article ar, product pr, order_item oi
111 where oi.orderId = ? and oi.productId = ar.id and ar.id = pr.articleId
113 deleteProduct => 'delete from product where articleId = ?',
114 'Products.userWishlist' => <<SQL,
115 select ar.*, pr.* from article ar, product pr, bse_wishlist wi
116 where wi.user_id = ? and wi.product_id = ar.id and ar.id = pr.articleId
117 order by wi.display_order desc
119 getProductOptionByProduct_id => <<SQL,
121 from bse_product_options
124 'Products.visible_children_of' => <<SQL,
125 select ar.*, pr.* from article ar, product pr
126 where ar.id = pr.articleId
129 and ? between ar.release and ar.expire
131 bse_userWishlistOrder => <<SQL,
132 select product_id, display_order
135 order by display_order desc
137 bse_userWishlistReorder => <<SQL,
139 set display_order = ?
140 where user_id = ? and product_id = ?
142 bse_addToWishlist => <<SQL,
143 insert into bse_wishlist(user_id, product_id, display_order)
146 bse_removeFromWishlist => <<SQL,
147 delete from bse_wishlist where user_id = ? and product_id = ?
150 Orders => 'select * from orders',
151 getOrderByPkey => 'select * from orders where id = ?',
152 getOrderItemByOrderId => 'select * from order_item where orderId = ?',
153 addOrder => 'insert orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
154 replaceOrder => 'replace orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
155 addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
156 replaceOrderItem => 'replace order_item values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
157 getOrderByUserId => 'select * from orders where userId = ?',
158 deleteOrdersItems => 'delete from order_item where orderId = ?',
160 getOrderItemByProductId => 'select * from order_item where productId = ?',
162 OtherParents => 'select * from other_parents',
163 getOtherParentByChildId => <<EOS,
164 select * from other_parents where childId = ? order by childDisplayOrder desc
166 getOtherParentByParentId => <<EOS,
167 select * from other_parents where parentId = ? order by parentDisplayOrder desc
169 getOtherParentByParentIdAndChildId =>
170 'select * from other_parents where parentId = ? and childId = ?',
171 addOtherParent=>'insert other_parents values(null,?,?,?,?,?,?)',
172 deleteOtherParent => 'delete from other_parents where id = ?',
173 replaceOtherParent=>'replace other_parents values(?,?,?,?,?,?,?)',
174 'OtherParents.anylinks' =>
175 'select * from other_parents where childId = ? or parentId = ?',
177 ArticleFiles => 'select * from article_files',
179 'insert into article_files values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
180 replaceArticleFile =>
181 'replace article_files values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
182 deleteArticleFile => 'delete from article_files where id = ?',
183 getArticleFileByArticleId =>
184 'select * from article_files where articleId = ? order by displayOrder desc',
185 getArticleFileByPkey => 'select * from article_files where id = ?',
188 select distinct af.*, oi.id as item_id
189 from article_files af, order_item oi
190 where af.articleId = oi.productId and oi.orderId = ?
191 order by oi.id, af.displayOrder desc
194 getSiteUserByUserId =>
195 'select * from site_users where userId = ?',
197 'select * from site_users where id = ?',
198 getSiteUserByAffiliate_name =>
199 'select * from site_users where affiliate_name = ?',
200 addSiteUser => 'insert site_users values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
201 replaceSiteUser => 'replace site_users values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
202 'SiteUsers.removeSubscriptions'=>
203 'delete from subscribed_users where userId = ?',
204 'SiteUsers.removeSub'=>
205 'delete from subscribed_users where userId = ? and subId = ?',
206 'SiteUsers.subRecipients' => <<EOS,
207 select si.* from site_users si, subscribed_users su
208 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
210 SiteUsers => 'select * from site_users',
211 'SiteUsers.allSubscribers' => <<SQL,
213 from site_users su, orders od, order_item oi
214 where su.id = od.siteuser_id and od.id = oi.orderId
215 and oi.subscription_id <> -1
217 siteuserAllIds => 'select id from site_users',
218 getBSESiteuserImage => <<SQL,
219 select * from bse_siteuser_images
220 where siteuser_id = ? and image_id = ?
222 getBSESiteuserImages => <<SQL,
223 select * from bse_siteuser_images where siteuser_id = ?
225 addBSESiteuserImage => <<SQL,
226 insert bse_siteuser_images values(?,?,?,?,?,?,?,?)
228 replaceBSESiteuserImage => <<SQL,
229 replace bse_siteuser_images values(?,?,?,?,?,?,?,?)
231 deleteBSESiteuserImage=> <<SQL,
232 delete from bse_siteuser_images where siteuser_id = ? and image_id = ?
236 'select * from subscription_types',
237 addSubscriptionType=>
238 'insert subscription_types values(null,?,?,?,?,?,?,?,?,?,?,?,?)',
239 replaceSubscriptionType=>
240 'replace subscription_types values(?,?,?,?,?,?,?,?,?,?,?,?,?)',
241 getSubscriptionTypeByPkey =>
242 'select * from subscription_types where id = ? order by name',
243 deleteSubscriptionType =>
244 'delete from subscription_types where id = ?',
245 subRecipientCount => <<EOS,
246 select count(*) as "count" from site_users si, subscribed_users su
247 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
249 'SubscriptionTypes.userSubscribedTo' => <<'EOS',
250 select su.* from subscription_types su, subscribed_users us
251 where us.userId = ? and us.subId = su.id
255 'insert subscribed_users values(null,?,?)',
256 getSubscribedUserByUserId =>
257 'select * from subscribed_users where userId = ?',
259 # the following don't work with the row/table classes
261 'select id, title from article order by level, displayOrder desc',
263 getEmailBlackEntryByEmail =>
264 'select * from email_blacklist where email = ?',
265 addEmailBlackEntry =>
266 'insert email_blacklist values(null,?,?)',
269 'insert email_requests values(null,?,?,?,?)',
270 replaceEmailRequest =>
271 'replace email_requests values(?,?,?,?,?)',
272 deleteEmailRequest =>
273 'delete from email_requests where id = ?',
274 getEmailRequestByGenEmail =>
275 'select * from email_requests where genEmail = ?',
277 addAdminBase => 'insert into admin_base values(null, ?)',
278 replaceAdminBase => 'replace into admin_base values(?, ?)',
279 deleteAdminBase => 'delete from admin_base where id = ?',
280 getAdminBaseByPkey => 'select * from admin_base where id=?',
283 select bs.*, us.* from admin_base bs, admin_users us
284 where bs.id = us.base_id
287 getAdminUserByLogon => <<SQL,
288 select bs.*, us.* from admin_base bs, admin_users us
289 where bs.id = us.base_id and us.logon = ?
291 getAdminUserByPkey => <<SQL,
292 select bs.*, us.* from admin_base bs, admin_users us
293 where bs.id = us.base_id and bs.id = ?
295 addAdminUser => 'insert into admin_users values(?,?,?,?,?)',
296 replaceAdminUser => 'replace into admin_users values(?,?,?,?,?)',
297 deleteAdminUser => 'delete from admin_users where base_id = ?',
298 adminUsersGroups => <<SQL,
300 from admin_base bs, admin_groups gr, admin_membership am
301 where bs.id = gr.base_id && am.user_id = ? and am.group_id = bs.id
304 userGroups => 'select * from admin_membership where user_id = ?',
305 deleteUserGroups => 'delete from admin_membership where user_id = ?',
307 AdminGroups => <<SQL,
309 from admin_base bs, admin_groups gr
310 where bs.id = gr.base_id
313 adminGroupsUsers => <<SQL,
315 from admin_base bs, admin_users us, admin_membership am
316 where bs.id = us.base_id && am.group_id = ? and am.user_id = bs.id
319 getAdminGroupByName => <<SQL,
320 select bs.*, gr.* from admin_base bs, admin_groups gr
321 where bs.id = gr.base_id and gr.name = ?
323 getAdminGroupByPkey => <<SQL,
324 select bs.*, gr.* from admin_base bs, admin_groups gr
325 where bs.id = gr.base_id and bs.id = ?
327 addAdminGroup => 'insert into admin_groups values(?,?,?,?,?)',
328 replaceAdminGroup => 'replace into admin_groups values(?,?,?,?,?)',
329 deleteAdminGroup => 'delete from admin_groups where base_id = ?',
330 groupUsers => 'select * from admin_membership where group_id = ?',
331 'AdminGroups.userPermissionGroups' => <<SQL,
332 select bs.*, ag.* from admin_base bs, admin_groups ag, admin_membership am
333 where bs.id = ag.base_id
334 and ( (ag.base_id = am.group_id and am.user_id = ?)
335 or ag.name = 'everyone' )
338 addUserToGroup => 'insert into admin_membership values(?,?)',
339 delUserFromGroup => <<SQL,
340 delete from admin_membership where user_id = ? and group_id = ?
342 deleteGroupUsers => 'delete from admin_membership where group_id = ?',
344 articleObjectPerm => <<SQL,
345 select * from admin_perms where object_id = ? and admin_id = ?
347 addArticleObjectPerm => 'insert into admin_perms values(?,?,?)',
348 replaceArticleObjectPerm => 'replace into admin_perms values(?,?,?)',
352 where ap.admin_id = ?
356 from admin_perms ap, admin_membership am
357 where ap.admin_id = am.group_id and am.user_id = ?
359 commonPerms => <<SQL,
361 from admin_perms ap, admin_groups ag
362 where ap.admin_id = ag.base_id and ag.name = 'everyone'
364 Subscriptions => 'select * from bse_subscriptions',
365 addSubscription => 'insert bse_subscriptions values(null,?,?,?,?)',
366 replaceSubscription => 'replace bse_subscriptions values(?,?,?,?,?)',
367 deleteSubscription => <<SQL,
368 delete from bse_subscriptions where subscription_id = ?
370 getSubscriptionByPkey => <<SQL,
371 select * from bse_subscriptions where subscription_id = ?
373 getSubscriptionByText_id => <<SQL,
374 select * from bse_subscriptions where text_id = ?
376 subscriptionOrderItemCount => <<SQL,
377 select count(*) as "count" from order_item where subscription_id = ?
379 subscriptionOrderSummary => <<SQL,
380 select od.id, od.userId, od.orderDate, od.siteuser_id,
381 sum(oi.subscription_period * oi.units) as "subscription_period"
382 from orders od, order_item oi
383 where oi.subscription_id = ? and od.id = oi.orderId and od.complete <> 0
384 group by od.id, od.userId, od.orderDate, od.siteuser_id
385 order by od.orderDate desc
387 subscriptionUserSummary => <<SQL,
389 from site_users su, bse_user_subscribed us
390 where su.id = us.siteuser_id and us.subscription_id = ?
392 subscriptionProductCount => <<SQL,
393 select count(*) as "count" from product
394 where subscription_id = ? or subscription_required = ?
396 removeUserSubscribed => <<SQL,
397 delete from bse_user_subscribed where subscription_id = ? and siteuser_id = ?
399 addUserSubscribed => <<SQL,
400 insert bse_user_subscribed values (?,?,?,?,?)
402 subscriptionUserBought => <<SQL,
404 oi.subscription_period * oi.units as "subscription_period",
406 od.id as "order_id", oi.id as "item_id", oi.productId as "product_id"
407 from orders od, order_item oi
408 where oi.subscription_id = ? and od.id = oi.orderId and od.siteuser_id = ?
411 userSubscribedEntry => <<SQL,
412 select * from bse_user_subscribed
413 where siteuser_id = ? and subscription_id = ?
415 siteuserSubscriptions => <<SQL,
416 select su.*, us.started_at, us.ends_at, us.max_lapsed
417 from bse_subscriptions su, bse_user_subscribed us
418 where us.siteuser_id = ? and us.subscription_id = su.subscription_id
419 and us.ends_at >= curdate()
422 addLocation => <<SQL,
423 insert bse_locations values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
425 replaceLocation => <<SQL,
426 replace bse_locations values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
428 getLocationByPkey => 'select * from bse_locations where id = ?',
429 deleteLocation => 'delete from bse_locations where id = ?',
430 Locations => 'select * from bse_locations order by description',
433 select ar.*, pr.*, se.*
434 from article ar, product pr, bse_seminars se
435 where ar.id = pr.articleId and ar.id = se.seminar_id
437 addSeminar => 'insert bse_seminars values(?,?)',
438 replaceSeminar => 'replace bse_seminars values(?,?)',
439 getSeminarByPkey => <<SQL,
440 select ar.*, pr.*, se.*
441 from article ar, product pr, bse_seminars se
442 where id = ? and ar.id = pr.articleId and ar.id = se.seminar_id
444 getSeminarByProduct_code => <<SQL,
445 select ar.*, pr.*, se.*
446 from article ar, product pr, bse_seminars se
447 where product_code = ? and ar.id = pr.articleId and ar.id = se.seminar_id
449 'Locations.seminarFuture' => <<SQL,
451 from bse_locations lo, bse_seminar_sessions ss
452 where ss.seminar_id = ? and ss.when_at > ?
453 and ss.location_id = lo.id
454 order by lo.description
456 'Locations.session_id' => <<SQL,
458 from bse_locations lo, bse_seminar_sessions ss
459 where lo.id = ss.location_id and ss.id = ?
462 seminarSessionInfo => <<SQL,
463 select se.*, lo.description
464 from bse_seminar_sessions se, bse_locations lo
465 where se.seminar_id = ? and se.location_id = lo.id
466 order by when_at desc
468 seminarFutureSessionInfo => <<SQL,
469 select se.*, lo.description, lo.room, lo.street1, lo.street2, lo.suburb,
470 lo.state, lo.country, lo.postcode, lo.public_notes
471 from bse_seminar_sessions se, bse_locations lo
472 where se.seminar_id = ? and se.when_at > ? and se.location_id = lo.id
473 order by when_at desc
475 addSeminarSession => 'insert bse_seminar_sessions values(null,?,?,?,?)',
476 replaceSeminarSession => 'replace bse_seminar_sessions values(?,?,?,?,?)',
477 deleteSeminarSession => 'delete from bse_seminar_sessions where id = ?',
478 getSeminarSessionByPkey => 'select * from bse_seminar_sessions where id = ?',
479 getSeminarSessionByLocation_idAndWhen_at => <<SQL,
480 select * from bse_seminar_sessions
481 where location_id = ? and when_at = ?
483 getSeminarSessionBySeminar_id => <<SQL,
484 select * from bse_seminar_sessions
487 'SeminarSessions.futureSessions' => <<SQL,
488 select * from bse_seminar_sessions
489 where seminar_id = ? and when_at >= ?
491 'SeminarSessions.futureSeminarLocation' => <<SQL,
493 from bse_seminar_sessions
494 where seminar_id = ? and location_id = ? and when_at > ?
496 'SiteUsers.sessionBookings' => <<SQL,
497 select su.* from site_users su, bse_seminar_bookings sb
498 where sb.session_id = ? and su.id = sb.siteuser_id
500 cancelSeminarSessionBookings => <<SQL,
501 delete from bse_seminar_bookings where session_id = ?
503 conflictSeminarSessions => <<SQL,
504 select bo1.siteuser_id
505 from bse_seminar_bookings bo1, bse_seminar_bookings bo2
506 where bo1.session_id = ? and bo2.session_id = ?
507 and bo1.siteuser_id = bo2.siteuser_id
509 seminarSessionBookedIds => <<SQL,
510 select * from bse_seminar_bookings where session_id = ?
512 addSeminarBooking => <<SQL,
513 insert bse_seminar_bookings values(null,?,?,?,?,?,?)
515 seminarSessionRollCallEntries => <<SQL,
516 select bo.roll_present, su.id, su.userId, su.name1, su.name2, su.email,
518 from bse_seminar_bookings bo, site_users su
519 where bo.session_id = ? and bo.siteuser_id = su.id
521 updateSessionRollPresent => <<SQL,
522 update bse_seminar_bookings
524 where session_id = ? and siteuser_id = ?
526 userSeminarSessionBookings => <<SQL,
528 from bse_seminar_bookings sb, bse_seminar_sessions ss
529 where ss.seminar_id = ? and ss.id = sb.session_id and siteuser_id = ?
531 SiteUserGroups => 'select * from bse_siteuser_groups',
532 addSiteUserGroup => 'insert bse_siteuser_groups values(null,?)',
533 replaceSiteUserGroup => 'replace bse_siteuser_groups values(?,?)',
534 deleteSiteUserGroup => 'delete from bse_siteuser_groups where id = ?',
535 getSiteUserGroupByPkey => 'select * from bse_siteuser_groups where id = ?',
536 getSiteUserGroupByName => 'select * from bse_siteuser_groups where name = ?',
537 siteuserGroupMemberIds => <<SQL,
538 select siteuser_id as "id"
539 from bse_siteuser_membership
542 siteuserGroupAddMember => <<SQL,
543 insert bse_siteuser_membership values(?,?)
545 siteuserGroupDeleteMember => <<SQL,
546 delete from bse_siteuser_membership where group_id = ? and siteuser_id = ?
548 siteuserGroupDeleteAllMembers => <<SQL,
549 delete from bse_siteuser_membership where group_id = ?
551 siteuserMemberOfGroup => <<SQL,
552 select * from bse_siteuser_membership
553 where siteuser_id = ? and group_id = ?
555 siteuserGroupsForUser => <<SQL,
556 select group_id as "id" from bse_siteuser_membership where siteuser_id = ?
559 articleAccessibleToGroup => <<SQL,
560 select * from bse_article_groups
561 where article_id = ? and group_id = ?
563 siteuserGroupsForArticle => <<SQL,
564 select group_id as "id" from bse_article_groups
567 articleAddSiteUserGroup => <<SQL,
568 insert bse_article_groups values(?,?)
570 articleDeleteSiteUserGroup => <<SQL,
571 delete from bse_article_groups
572 where article_id = ? and group_id = ?
574 siteuserGroupDeleteAllPermissions => <<SQL,
575 delete from bse_article_groups where group_id = ?
579 # called when we start working on a new request, mysql seems to have
580 # problems with old connections sometimes, or so it seems, so
581 # disconnect occasionally (only matters for fastcgi, mod_perl)
586 unless ($self->{dbh}->ping) {
587 print STDERR "Database connection lost - reconnecting\n";
588 $self->{dbh} = $class->_connect;
589 $self->{birth} = time();
595 my $dbh = DBI->connect( $DSN, $UN, $PW, $DBOPTS)
596 or die "Cannot connect to database: $DBI::errstr";
598 # this might fail, but I don't care
599 $dbh->do("set session sql_mode='ansi_quotes'");
608 warn "Incorrect number of parameters passed to DatabaseHandle::single\n" unless @_ == 0;
610 unless ( defined $self ) {
611 my $dbh = $class->_connect;
613 $self = bless { dbh => $dbh, birth => time() }, $class;
618 my $get_sql_by_name = 'select sql_statement from sql_statements where name=?';
621 my ($self, $name) = @_;
623 $name =~ s/BSE.*:://;
625 my $sql = $statements{$name};
627 my @row = $self->{dbh}->selectrow_array($get_sql_by_name, {}, $name);
630 #print STDERR "Found SQL '$sql'\n";
633 #print STDERR "SQL statment $name not found in sql_statements table\n";
641 my ($self, $name) = @_;
643 my $sql = $self->stmt_sql($name)
644 or confess "Statement named '$name' not found";
645 my $sth = $self->{dbh}->prepare($sql)
646 or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
652 my ($self, $name) = @_;
654 my $sql = $self->stmt_sql($name)
656 my $sth = $self->{dbh}->prepare($sql)
657 or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
663 my ($self, $sth) = @_;
665 my $id = $sth->{"mysql_insertid"};
674 # this is wierd - we only need to reset this on 5.6.x (for x == 0 so
676 # Works fine without the reset for 5.005_03
678 $self->{dbh}->disconnect;