save the product tier on ordering
[bse.git] / site / cgi-bin / modules / BSE / DB / Mysql.pm
CommitLineData
a855ba81 1package BSE::DB::Mysql;
dec5de5e 2use strict;
a855ba81 3use DBI;
b19047a6 4use vars qw/@ISA/;
444957b9 5use Carp 'confess';
b19047a6 6@ISA = qw(BSE::DB);
a855ba81 7
4ad0e50a 8our $VERSION = "1.014";
cb7fd78d 9
4ad0e50a 10use vars qw($MAX_CONNECTION_AGE);
a855ba81
TC
11
12use Carp;
13
33bccea7
TC
14my $self;
15
5ac2ad24
TC
16$MAX_CONNECTION_AGE = 1200;
17
a855ba81
TC
18my %statements =
19 (
dbdff741
TC
20 # don't ever load the entire articles table
21 #Articles => 'select * from article',
a855ba81 22 replaceArticle =>
dbfbfb12 23 'replace article values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
a855ba81 24 addArticle =>
dbfbfb12 25 'insert article values (null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
a855ba81 26 deleteArticle => 'delete from article where id = ?',
a855ba81
TC
27 getArticleByPkey => 'select * from article where id = ?',
28
29 getArticleByLevel => 'select * from article where level = ?',
30 getArticleByParentid => 'select * from article where parentid = ?',
c76e86ea 31 getArticleByLinkAlias => 'select * from article where linkAlias = ?',
99ef7979
TC
32 'Articles.stepParents' => <<EOS,
33select ar.* from article ar, other_parents op
34 where ar.id = op.parentId and op.childId = ?
35order by op.childDisplayOrder desc
3ffa8a72
TC
36EOS
37 'Articles.visibleStepParents' => <<EOS,
38select ar.* from article ar, other_parents op
39 where ar.id = op.parentId and op.childId = ?
40 and date_format(?, '%Y%m%d') between date_format(op.release, '%Y%m%d') and date_format(op.expire, '%Y%m%d')
41 and listed <> 0
42order by op.childDisplayOrder desc
721cd24c
TC
43EOS
44 'Articles.stepKids' => <<EOS,
45select ar.* from article ar, other_parents op
46 where op.childId = ar.id and op.parentId = ?
47EOS
4175638b
TC
48# originally "... and ? between op.release and op.expire"
49# but since the first argument was a string, mysql treated the comparisons
50# as string comparisons
721cd24c
TC
51 'Articles.visibleStepKids' => <<EOS,
52select ar.* from article ar, other_parents op
53 where op.childId = ar.id
4175638b 54 and op.parentId = ?
deae2a52 55 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
99ef7979 56EOS
531fb3bc 57 'Articles.ids'=>'select id from article',
147e99e8
TC
58 articlePossibleStepparents => <<EOS,
59select a.id, a.title from article a
60where a.id not in (select parentId from other_parents where childId = ?)
61 and a.id <> ?;
62EOS
63 articlePossibleStepchildren => <<EOS,
64select a.id, a.title from article a
65where a.id not in (select childId from other_parents where parentId = ?)
66 and a.id <> ?;
12cbbf27
TC
67EOS
68 bse_MaxArticleDisplayOrder => <<EOS,
69select max(displayOrder) as "displayOrder" from article
147e99e8 70EOS
99ef7979 71
721cd24c
TC
72 Images => 'select * from image',
73 replaceImage =>
f40af7e2
TC
74 'replace image values (?,?,?,?,?,?,?,?,?,?,?,?)',
75 addImage => 'insert image values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
721cd24c 76 deleteImage => 'delete from image where id = ?',
ca9aa2bf 77 getImageByArticleId => 'select * from image where articleId = ? order by displayOrder',
c2096d67
TC
78 getImageByPkey => 'select * from image where id = ?',
79 getImageByArticleIdAndName => <<SQL,
80select * from image where articleId = ? and name = ?
81SQL
721cd24c 82
a855ba81
TC
83 dropIndex => 'delete from searchindex',
84 insertIndex => 'insert searchindex values(?, ?, ?, ?)',
85 searchIndex => 'select * from searchindex where id = ?',
86 searchIndexWC => 'select * from searchindex where id like ?',
87
88 Products=> 'select article.*, product.* from article, product where id = articleId',
306eb97a 89 addProduct => 'insert product values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
a855ba81 90 getProductByPkey => 'select article.*, product.* from article, product where id=? and articleId = id',
c0007bcb 91 getProductByProduct_code => 'select article.*, product.* from article, product where product_code=? and articleId = id',
3220063e 92 getProductByLinkAlias => 'select article.*, product.* from article, product where linkAlias=? and articleId = id',
306eb97a 93 replaceProduct => 'replace product values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
99ef7979
TC
94 'Products.stepProducts' => <<EOS,
95select ar.*, pr.* from article ar, product pr, other_parents op
96 where ar.id = pr.articleId and op.childId = ar.id and op.parentId = ?
97EOS
98 'Products.visibleStep' => <<EOS,
99select ar.*, pr.* from article ar, product pr, other_parents op
100 where ar.id = pr.articleId and op.childId = ar.id
101 and op.parentId = ? and ? between op.release and op.expire
137c1c40 102 and listed <> 0
99ef7979 103EOS
d7538448
TC
104 'Products.subscriptionDependent' => <<SQL,
105select ar.*, pr.* from article ar, product pr
106 where ar.id = pr.articleId
107 and (pr.subscription_id = ? or subscription_required = ?)
ab2cd916
TC
108SQL
109 'Products.orderProducts' => <<SQL,
110select ar.*, pr.* from article ar, product pr, order_item oi
111 where oi.orderId = ? and oi.productId = ar.id and ar.id = pr.articleId
d7538448 112SQL
6473c56f 113 deleteProduct => 'delete from product where articleId = ?',
d49667a2
TC
114 'Products.userWishlist' => <<SQL,
115select 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
117order by wi.display_order desc
12cbbf27
TC
118SQL
119 'Products.visible_children_of' => <<SQL,
120select ar.*, pr.* from article ar, product pr
121 where ar.id = pr.articleId
122 and listed <> 0
123 and ar.parentid = ?
124 and ? between ar.release and ar.expire
d49667a2
TC
125SQL
126 bse_userWishlistOrder => <<SQL,
127select product_id, display_order
128from bse_wishlist
129where user_id = ?
130order by display_order desc
131SQL
132 bse_userWishlistReorder => <<SQL,
133update bse_wishlist
134 set display_order = ?
135where user_id = ? and product_id = ?
136SQL
137 bse_addToWishlist => <<SQL,
138insert into bse_wishlist(user_id, product_id, display_order)
139 values(?, ?, ?)
140SQL
141 bse_removeFromWishlist => <<SQL,
142delete from bse_wishlist where user_id = ? and product_id = ?
143SQL
144
a855ba81 145 Orders => 'select * from orders',
f0722dd2 146 #getOrderByPkey => 'select * from orders where id = ?',
a855ba81 147 getOrderItemByOrderId => 'select * from order_item where orderId = ?',
f0722dd2
TC
148 #addOrder => 'insert orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
149 #replaceOrder => 'replace orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
4ad0e50a
TC
150 #addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
151 #replaceOrderItem => 'replace order_item values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
f0722dd2 152 #getOrderByUserId => 'select * from orders where userId = ?',
5d88571c 153 deleteOrdersItems => 'delete from order_item where orderId = ?',
99ef7979 154
6473c56f
TC
155 getOrderItemByProductId => 'select * from order_item where productId = ?',
156
99ef7979
TC
157 OtherParents => 'select * from other_parents',
158 getOtherParentByChildId => <<EOS,
159select * from other_parents where childId = ? order by childDisplayOrder desc
160EOS
161 getOtherParentByParentId => <<EOS,
162select * from other_parents where parentId = ? order by parentDisplayOrder desc
163EOS
164 getOtherParentByParentIdAndChildId =>
165 'select * from other_parents where parentId = ? and childId = ?',
166 addOtherParent=>'insert other_parents values(null,?,?,?,?,?,?)',
167 deleteOtherParent => 'delete from other_parents where id = ?',
168 replaceOtherParent=>'replace other_parents values(?,?,?,?,?,?,?)',
169 'OtherParents.anylinks' =>
170 'select * from other_parents where childId = ? or parentId = ?',
edc5d096 171
e63c3728 172 ArticleFiles => 'select * from article_files',
edc5d096 173 addArticleFile =>
6430ee52 174 'insert into article_files values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
edc5d096 175 replaceArticleFile =>
6430ee52 176 'replace article_files values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
edc5d096
TC
177 deleteArticleFile => 'delete from article_files where id = ?',
178 getArticleFileByArticleId =>
179 'select * from article_files where articleId = ? order by displayOrder desc',
4afdbb1b 180 getArticleFileByPkey => 'select * from article_files where id = ?',
abf5bbc6 181
7c6f563b
TC
182 "ArticleFiles.orderFiles" =><<SQL,
183select distinct af.*
abf5bbc6
TC
184from article_files af, order_item oi
185where af.articleId = oi.productId and oi.orderId = ?
3d3e00ed 186order by oi.id, af.displayOrder desc
abf5bbc6 187SQL
edc5d096 188
5899bc52
TC
189 # getSiteUserByUserId =>
190 # 'select * from site_users where userId = ?',
191 # getSiteUserByPkey =>
192 # 'select * from site_users where id = ?',
193 # getSiteUserByAffiliate_name =>
194 # 'select * from site_users where affiliate_name = ?',
195 # addSiteUser => 'insert site_users values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
196 # replaceSiteUser => 'replace site_users values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
b19047a6
TC
197 'SiteUsers.removeSubscriptions'=>
198 'delete from subscribed_users where userId = ?',
531fb3bc
TC
199 'SiteUsers.removeSub'=>
200 'delete from subscribed_users where userId = ? and subId = ?',
201 'SiteUsers.subRecipients' => <<EOS,
b27af108 202select si.* from bse_siteusers si, subscribed_users su
dc040d12 203 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
531fb3bc 204EOS
b27af108 205 SiteUsers => 'select * from bse_siteusers',
af74f0b4
TC
206 'SiteUsers.allSubscribers' => <<SQL,
207select distinct su.*
b27af108 208 from bse_siteusers su, orders od, order_item oi
af74f0b4
TC
209 where su.id = od.siteuser_id and od.id = oi.orderId
210 and oi.subscription_id <> -1
211SQL
b27af108 212 siteuserAllIds => 'select id from bse_siteusers',
dfdeb4fe
TC
213 getBSESiteuserImage => <<SQL,
214select * from bse_siteuser_images
215 where siteuser_id = ? and image_id = ?
216SQL
217 getBSESiteuserImages => <<SQL,
218select * from bse_siteuser_images where siteuser_id = ?
219SQL
220 addBSESiteuserImage => <<SQL,
221insert bse_siteuser_images values(?,?,?,?,?,?,?,?)
222SQL
223 replaceBSESiteuserImage => <<SQL,
224replace bse_siteuser_images values(?,?,?,?,?,?,?,?)
225SQL
226 deleteBSESiteuserImage=> <<SQL,
df37553e 227delete from bse_siteuser_images where siteuser_id = ? and image_id = ?
dfdeb4fe 228SQL
b19047a6
TC
229
230 SubscriptionTypes =>
231 'select * from subscription_types',
232 addSubscriptionType=>
4ef01459 233 'insert subscription_types values(null,?,?,?,?,?,?,?,?,?,?,?,?)',
b19047a6 234 replaceSubscriptionType=>
4ef01459 235 'replace subscription_types values(?,?,?,?,?,?,?,?,?,?,?,?,?)',
b19047a6
TC
236 getSubscriptionTypeByPkey =>
237 'select * from subscription_types where id = ? order by name',
b553afa2
TC
238 deleteSubscriptionType =>
239 'delete from subscription_types where id = ?',
4772671f 240 subRecipientCount => <<EOS,
b27af108 241select count(*) as "count" from bse_siteusers si, subscribed_users su
dc040d12 242 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
4772671f 243EOS
9063386f
TC
244 'SubscriptionTypes.userSubscribedTo' => <<'EOS',
245select su.* from subscription_types su, subscribed_users us
246 where us.userId = ? and us.subId = su.id
247EOS
b19047a6
TC
248
249 addSubscribedUser=>
250 'insert subscribed_users values(null,?,?)',
251 getSubscribedUserByUserId =>
252 'select * from subscribed_users where userId = ?',
253
254 # the following don't work with the row/table classes
255 articlesList =>
256 'select id, title from article order by level, displayOrder desc',
257
258 getEmailBlackEntryByEmail =>
259 'select * from email_blacklist where email = ?',
2a295ea9
TC
260 addEmailBlackEntry =>
261 'insert email_blacklist values(null,?,?)',
b19047a6
TC
262
263 addEmailRequest =>
264 'insert email_requests values(null,?,?,?,?)',
265 replaceEmailRequest =>
266 'replace email_requests values(?,?,?,?,?)',
531fb3bc
TC
267 deleteEmailRequest =>
268 'delete from email_requests where id = ?',
b19047a6
TC
269 getEmailRequestByGenEmail =>
270 'select * from email_requests where genEmail = ?',
6473c56f
TC
271
272 addAdminBase => 'insert into admin_base values(null, ?)',
08123550
TC
273 replaceAdminBase => 'replace into admin_base values(?, ?)',
274 deleteAdminBase => 'delete from admin_base where id = ?',
275 getAdminBaseByPkey => 'select * from admin_base where id=?',
6473c56f
TC
276
277 AdminUsers => <<SQL,
278select bs.*, us.* from admin_base bs, admin_users us
279 where bs.id = us.base_id
280 order by logon
281SQL
282 getAdminUserByLogon => <<SQL,
283select bs.*, us.* from admin_base bs, admin_users us
284 where bs.id = us.base_id and us.logon = ?
285SQL
286 getAdminUserByPkey => <<SQL,
287select bs.*, us.* from admin_base bs, admin_users us
288 where bs.id = us.base_id and bs.id = ?
289SQL
74b3689a
TC
290 addAdminUser => 'insert into admin_users values(?,?,?,?,?,?,?)',
291 replaceAdminUser => 'replace into admin_users values(?,?,?,?,?,?,?)',
08123550 292 deleteAdminUser => 'delete from admin_users where base_id = ?',
156a4907
TC
293 "AdminUsers.group_members" => <<SQL,
294select bs.*, us.*
295 from admin_base bs, admin_users us, admin_membership am
296 where bs.id = us.base_id && am.group_id = ? and am.user_id = bs.id
297 order by logon
298SQL
6473c56f
TC
299 adminUsersGroups => <<SQL,
300select bs.*, gr.*
301 from admin_base bs, admin_groups gr, admin_membership am
302 where bs.id = gr.base_id && am.user_id = ? and am.group_id = bs.id
303 order by gr.name
304SQL
08123550
TC
305 userGroups => 'select * from admin_membership where user_id = ?',
306 deleteUserGroups => 'delete from admin_membership where user_id = ?',
6473c56f
TC
307
308 AdminGroups => <<SQL,
309select bs.*, gr.*
310 from admin_base bs, admin_groups gr
311 where bs.id = gr.base_id
312 order by name
6473c56f
TC
313SQL
314 getAdminGroupByName => <<SQL,
315select bs.*, gr.* from admin_base bs, admin_groups gr
316 where bs.id = gr.base_id and gr.name = ?
317SQL
318 getAdminGroupByPkey => <<SQL,
319select bs.*, gr.* from admin_base bs, admin_groups gr
320 where bs.id = gr.base_id and bs.id = ?
321SQL
4d764c34
TC
322 addAdminGroup => 'insert into admin_groups values(?,?,?,?,?)',
323 replaceAdminGroup => 'replace into admin_groups values(?,?,?,?,?)',
08123550
TC
324 deleteAdminGroup => 'delete from admin_groups where base_id = ?',
325 groupUsers => 'select * from admin_membership where group_id = ?',
156a4907
TC
326 bseAdminGroupMember => <<SQL,
327select 1
328from admin_membership
329where group_id = ?
330 and user_id = ?
331SQL
9168c88c
TC
332 'AdminGroups.userPermissionGroups' => <<SQL,
333select bs.*, ag.* from admin_base bs, admin_groups ag, admin_membership am
334where bs.id = ag.base_id
335 and ( (ag.base_id = am.group_id and am.user_id = ?)
336 or ag.name = 'everyone' )
337SQL
08123550
TC
338
339 addUserToGroup => 'insert into admin_membership values(?,?)',
340 delUserFromGroup => <<SQL,
341delete from admin_membership where user_id = ? and group_id = ?
342SQL
343 deleteGroupUsers => 'delete from admin_membership where group_id = ?',
344
345 articleObjectPerm => <<SQL,
346select * from admin_perms where object_id = ? and admin_id = ?
347SQL
348 addArticleObjectPerm => 'insert into admin_perms values(?,?,?)',
349 replaceArticleObjectPerm => 'replace into admin_perms values(?,?,?)',
4010d92e 350 userPerms => <<SQL,
9168c88c 351select distinct ap.*
4010d92e 352from admin_perms ap
9168c88c 353where ap.admin_id = ?
4010d92e
TC
354SQL
355 groupPerms => <<SQL,
356select distinct ap.*
357from admin_perms ap, admin_membership am
358where ap.admin_id = am.group_id and am.user_id = ?
359SQL
360 commonPerms => <<SQL,
361select distinct ap.*
362from admin_perms ap, admin_groups ag
363where ap.admin_id = ag.base_id and ag.name = 'everyone'
0ec4ac8a
TC
364SQL
365 Subscriptions => 'select * from bse_subscriptions',
366 addSubscription => 'insert bse_subscriptions values(null,?,?,?,?)',
367 replaceSubscription => 'replace bse_subscriptions values(?,?,?,?,?)',
368 deleteSubscription => <<SQL,
369delete from bse_subscriptions where subscription_id = ?
370SQL
371 getSubscriptionByPkey => <<SQL,
372select * from bse_subscriptions where subscription_id = ?
9d576c12
TC
373SQL
374 getSubscriptionByText_id => <<SQL,
375select * from bse_subscriptions where text_id = ?
d7538448
TC
376SQL
377 subscriptionOrderItemCount => <<SQL,
378select count(*) as "count" from order_item where subscription_id = ?
379SQL
380 subscriptionOrderSummary => <<SQL,
e3506e41 381select od.id, od.userId, od.orderDate, od.siteuser_id, od.billFirstName, od.billLastName, od.filled,
9d576c12 382 sum(oi.subscription_period * oi.units) as "subscription_period"
d7538448 383 from orders od, order_item oi
d44b5da9 384 where oi.subscription_id = ? and od.id = oi.orderId and od.complete <> 0
d7538448
TC
385 group by od.id, od.userId, od.orderDate, od.siteuser_id
386 order by od.orderDate desc
af74f0b4
TC
387SQL
388 subscriptionUserSummary => <<SQL,
389select su.*, us.*
b27af108 390 from bse_siteusers su, bse_user_subscribed us
af74f0b4 391where su.id = us.siteuser_id and us.subscription_id = ?
d7538448
TC
392SQL
393 subscriptionProductCount => <<SQL,
394select count(*) as "count" from product
395 where subscription_id = ? or subscription_required = ?
af74f0b4
TC
396SQL
397 removeUserSubscribed => <<SQL,
398delete from bse_user_subscribed where subscription_id = ? and siteuser_id = ?
399SQL
400 addUserSubscribed => <<SQL,
401insert bse_user_subscribed values (?,?,?,?,?)
402SQL
403 subscriptionUserBought => <<SQL,
829c9ed9
TC
404select od.orderDate,
405 oi.subscription_period * oi.units as "subscription_period",
406 oi.max_lapsed,
af74f0b4
TC
407 od.id as "order_id", oi.id as "item_id", oi.productId as "product_id"
408 from orders od, order_item oi
409 where oi.subscription_id = ? and od.id = oi.orderId and od.siteuser_id = ?
d44b5da9 410 and od.complete <> 0
af74f0b4
TC
411SQL
412 userSubscribedEntry => <<SQL,
413select * from bse_user_subscribed
414 where siteuser_id = ? and subscription_id = ?
9d576c12
TC
415SQL
416 siteuserSubscriptions => <<SQL,
417select su.*, us.started_at, us.ends_at, us.max_lapsed
418 from bse_subscriptions su, bse_user_subscribed us
419where us.siteuser_id = ? and us.subscription_id = su.subscription_id
420 and us.ends_at >= curdate()
9168c88c 421SQL
37dd20ad
TC
422
423 addLocation => <<SQL,
424insert bse_locations values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
425SQL
426 replaceLocation => <<SQL,
427replace bse_locations values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
428SQL
429 getLocationByPkey => 'select * from bse_locations where id = ?',
430 deleteLocation => 'delete from bse_locations where id = ?',
431 Locations => 'select * from bse_locations order by description',
432
433 Seminars => <<SQL,
434select ar.*, pr.*, se.*
435 from article ar, product pr, bse_seminars se
718a070d 436 where ar.id = pr.articleId and ar.id = se.seminar_id
37dd20ad
TC
437SQL
438 addSeminar => 'insert bse_seminars values(?,?)',
439 replaceSeminar => 'replace bse_seminars values(?,?)',
726ffaed 440 deleteSeminar => 'delete from bse_seminars where seminar_id = ?',
37dd20ad
TC
441 getSeminarByPkey => <<SQL,
442select ar.*, pr.*, se.*
443 from article ar, product pr, bse_seminars se
444 where id = ? and ar.id = pr.articleId and ar.id = se.seminar_id
7b5ef271
TC
445SQL
446 getSeminarByProduct_code => <<SQL,
447select ar.*, pr.*, se.*
448 from article ar, product pr, bse_seminars se
449 where product_code = ? and ar.id = pr.articleId and ar.id = se.seminar_id
718a070d
TC
450SQL
451 'Locations.seminarFuture' => <<SQL,
452select distinct lo.*
453 from bse_locations lo, bse_seminar_sessions ss
454where ss.seminar_id = ? and ss.when_at > ?
455 and ss.location_id = lo.id
456order by lo.description
457SQL
458 'Locations.session_id' => <<SQL,
459select lo.*
460 from bse_locations lo, bse_seminar_sessions ss
461where lo.id = ss.location_id and ss.id = ?
37dd20ad 462SQL
16ac5598
TC
463
464 seminarSessionInfo => <<SQL,
465select se.*, lo.description
466 from bse_seminar_sessions se, bse_locations lo
467 where se.seminar_id = ? and se.location_id = lo.id
468order by when_at desc
718a070d
TC
469SQL
470 seminarFutureSessionInfo => <<SQL,
471select se.*, lo.description, lo.room, lo.street1, lo.street2, lo.suburb,
472 lo.state, lo.country, lo.postcode, lo.public_notes
473 from bse_seminar_sessions se, bse_locations lo
474 where se.seminar_id = ? and se.when_at > ? and se.location_id = lo.id
475order by when_at desc
16ac5598
TC
476SQL
477 addSeminarSession => 'insert bse_seminar_sessions values(null,?,?,?,?)',
478 replaceSeminarSession => 'replace bse_seminar_sessions values(?,?,?,?,?)',
479 deleteSeminarSession => 'delete from bse_seminar_sessions where id = ?',
480 getSeminarSessionByPkey => 'select * from bse_seminar_sessions where id = ?',
481 getSeminarSessionByLocation_idAndWhen_at => <<SQL,
482select * from bse_seminar_sessions
483 where location_id = ? and when_at = ?
718a070d
TC
484SQL
485 getSeminarSessionBySeminar_id => <<SQL,
486select * from bse_seminar_sessions
487 where seminar_id = ?
16ac5598
TC
488SQL
489 'SeminarSessions.futureSessions' => <<SQL,
490select * from bse_seminar_sessions
491 where seminar_id = ? and when_at >= ?
718a070d
TC
492SQL
493 'SeminarSessions.futureSeminarLocation' => <<SQL,
494select *
495 from bse_seminar_sessions
496 where seminar_id = ? and location_id = ? and when_at > ?
16ac5598
TC
497SQL
498 'SiteUsers.sessionBookings' => <<SQL,
b27af108 499select su.* from bse_siteusers su, bse_seminar_bookings sb
16ac5598
TC
500 where sb.session_id = ? and su.id = sb.siteuser_id
501SQL
502 cancelSeminarSessionBookings => <<SQL,
503delete from bse_seminar_bookings where session_id = ?
504SQL
505 conflictSeminarSessions => <<SQL,
506select bo1.siteuser_id
507 from bse_seminar_bookings bo1, bse_seminar_bookings bo2
508where bo1.session_id = ? and bo2.session_id = ?
509 and bo1.siteuser_id = bo2.siteuser_id
510SQL
511 seminarSessionBookedIds => <<SQL,
512select * from bse_seminar_bookings where session_id = ?
513SQL
11c35ec9
TC
514 addSeminarBooking => <<SQL,
515insert bse_seminar_bookings values(null,?,?,?,?,?,?)
16ac5598
TC
516SQL
517 seminarSessionRollCallEntries => <<SQL,
11c35ec9
TC
518select bo.roll_present, su.id, su.userId, su.name1, su.name2, su.email,
519 bo.id as booking_id
b27af108 520 from bse_seminar_bookings bo, bse_siteusers su
16ac5598
TC
521where bo.session_id = ? and bo.siteuser_id = su.id
522SQL
718a070d 523 updateSessionRollPresent => <<SQL,
16ac5598
TC
524update bse_seminar_bookings
525 set roll_present = ?
526 where session_id = ? and siteuser_id = ?
718a070d 527SQL
efcc5a30 528 userSeminarSessionBookings => <<SQL,
718a070d
TC
529select session_id
530 from bse_seminar_bookings sb, bse_seminar_sessions ss
531where ss.seminar_id = ? and ss.id = sb.session_id and siteuser_id = ?
efcc5a30
TC
532SQL
533 SiteUserGroups => 'select * from bse_siteuser_groups',
534 addSiteUserGroup => 'insert bse_siteuser_groups values(null,?)',
535 replaceSiteUserGroup => 'replace bse_siteuser_groups values(?,?)',
536 deleteSiteUserGroup => 'delete from bse_siteuser_groups where id = ?',
537 getSiteUserGroupByPkey => 'select * from bse_siteuser_groups where id = ?',
c5286ebe 538 getSiteUserGroupByName => 'select * from bse_siteuser_groups where name = ?',
efcc5a30
TC
539 siteuserGroupMemberIds => <<SQL,
540select siteuser_id as "id"
541from bse_siteuser_membership
542where group_id = ?
543SQL
544 siteuserGroupAddMember => <<SQL,
545insert bse_siteuser_membership values(?,?)
546SQL
547 siteuserGroupDeleteMember => <<SQL,
548delete from bse_siteuser_membership where group_id = ? and siteuser_id = ?
549SQL
550 siteuserGroupDeleteAllMembers => <<SQL,
551delete from bse_siteuser_membership where group_id = ?
552SQL
553 siteuserMemberOfGroup => <<SQL,
554select * from bse_siteuser_membership
555where siteuser_id = ? and group_id = ?
556SQL
557 siteuserGroupsForUser => <<SQL,
558select group_id as "id" from bse_siteuser_membership where siteuser_id = ?
c2096d67
TC
559SQL
560
561 articleAccessibleToGroup => <<SQL,
562select * from bse_article_groups
563where article_id = ? and group_id = ?
564SQL
565 siteuserGroupsForArticle => <<SQL,
566select group_id as "id" from bse_article_groups
567where article_id = ?
568SQL
569 articleAddSiteUserGroup => <<SQL,
570insert bse_article_groups values(?,?)
571SQL
572 articleDeleteSiteUserGroup => <<SQL,
573delete from bse_article_groups
574where article_id = ? and group_id = ?
575SQL
576 siteuserGroupDeleteAllPermissions => <<SQL,
577delete from bse_article_groups where group_id = ?
16ac5598 578SQL
a855ba81
TC
579 );
580
5ac2ad24
TC
581# called when we start working on a new request, mysql seems to have
582# problems with old connections sometimes, or so it seems, so
583# disconnect occasionally (only matters for fastcgi, mod_perl)
584sub _startup {
585 my $class = shift;
586
587 if ($self) {
60321291
TC
588 unless ($self->{dbh}->ping) {
589 print STDERR "Database connection lost - reconnecting\n";
f811b4b8 590 $self->{dbh} = $class->connect;
5ac2ad24
TC
591 $self->{birth} = time();
592 }
593 }
594}
595
f811b4b8
TC
596sub connect {
597 my ($class, $dbname) = @_;
d2473dc2 598
f811b4b8
TC
599 my $dsn = $class->dsn($dbname);
600 my $un = $self->dbuser($dbname);
601 my $pass = $self->dbpassword($dbname);
602 my $dbopts = $self->dbopts($dbname);
d2473dc2 603 my $dbh = DBI->connect( $dsn, $un, $pass, $dbopts)
5ac2ad24 604 or die "Cannot connect to database: $DBI::errstr";
d2473dc2 605
5ac2ad24
TC
606 # this might fail, but I don't care
607 $dbh->do("set session sql_mode='ansi_quotes'");
608
609 return $dbh;
610}
611
a855ba81
TC
612sub _single
613{
d2473dc2 614 my ($class, $cfg) = @_;
5ac2ad24 615
d2473dc2 616 warn "Incorrect number of parameters passed to BSE::DB::Mysql::single\n" unless @_ == 2;
a855ba81
TC
617
618 unless ( defined $self ) {
d2473dc2
TC
619 $self = bless
620 {
621 dbh => undef,
622 birth => time(),
623 cfg => $cfg,
624 }, $class;
625
f811b4b8 626 $self->{dbh} = $self->connect;
a855ba81
TC
627 }
628 $self;
629}
630
026d5cec
TC
631sub _forked {
632 my $self = shift;
633
634 $self->{dbh}{InactiveDestroy} = 1;
635 delete $self->{dbh};
f811b4b8 636 $self->{dbh} = $self->connect;
026d5cec
TC
637}
638
639
dc872a32
TC
640my $get_sql_by_name = 'select sql_statement from sql_statements where name=?';
641
cb0f8be6
TC
642my %sql_cache;
643
58baa27b 644sub stmt_sql {
a855ba81
TC
645 my ($self, $name) = @_;
646
6473c56f 647 $name =~ s/BSE.*:://;
b19047a6 648
dc872a32 649 my $sql = $statements{$name};
cb0f8be6
TC
650 unless ($sql) {
651 if (exists $sql_cache{$name}) {
b6965984 652 return $sql_cache{$name};
cb0f8be6
TC
653 }
654 }
dc872a32
TC
655 unless ($sql) {
656 my @row = $self->{dbh}->selectrow_array($get_sql_by_name, {}, $name);
657 if (@row) {
658 $sql = $row[0];
62533efa 659 #print STDERR "Found SQL '$sql'\n";
dc872a32
TC
660 }
661 else {
58baa27b 662 #print STDERR "SQL statment $name not found in sql_statements table\n";
dc872a32 663 }
cb0f8be6
TC
664
665 $sql_cache{$name} = $sql;
dc872a32 666 }
58baa27b
TC
667
668 return $sql;
669}
670
671sub stmt {
672 my ($self, $name) = @_;
673
674 my $sql = $self->stmt_sql($name)
675 or confess "Statement named '$name' not found";
676 my $sth = $self->{dbh}->prepare($sql)
677 or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
678
679 $sth;
680}
681
682sub stmt_noerror {
683 my ($self, $name) = @_;
684
685 my $sql = $self->stmt_sql($name)
686 or return;
dc872a32 687 my $sth = $self->{dbh}->prepare($sql)
a855ba81
TC
688 or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
689
690 $sth;
691}
692
693sub insert_id {
694 my ($self, $sth) = @_;
695
99ef7979
TC
696 my $id = $sth->{"mysql_insertid"};
697
698 return $id;
a855ba81
TC
699}
700
3f9c8a96
TC
701sub dbopts {
702 my ($class) = @_;
703
704 my $opts = $class->SUPER::dbopts();
705
706 if (BSE::Cfg->utf8
707 && lc(BSE::Cfg->charset) eq "utf-8") {
708 $opts->{mysql_enable_utf8} = 1;
709 }
710
711 return $opts;
712}
713
a855ba81
TC
714# gotta love this
715sub DESTROY
716{
33bccea7 717 my ($self) = @_;
a855ba81
TC
718 # this is wierd - we only need to reset this on 5.6.x (for x == 0 so
719 # far)
720 # Works fine without the reset for 5.005_03
33bccea7
TC
721 if ($self->{dbh}) {
722 $self->{dbh}->disconnect;
723 delete $self->{dbh};
a855ba81
TC
724 }
725}
726
7271;
728