make metadata more general
[bse.git] / site / data / db / sql_statements.data
CommitLineData
2076966c 1--
a3e0dbc8 2# VERSION=1.006
2076966c
TC
3name: bse_siteuserSeminarBookingsDetail
4sql_statement: <<SQL
5select ar.*, pr.*, se.*, ss.*, sb.*,
6 lo.description as loc_description,
7 lo.room as loc_room,
8 lo.street1 as loc_street1,
9 lo.street2 as loc_street2,
10 lo.suburb as loc_suburb,
11 lo.state as loc_state,
12 lo.country as loc_country,
13 lo.postcode as loc_postcode,
14 lo.public_notes as loc_public_notes,
15 lo.bookings_name as loc_bookings_name,
16 lo.bookings_phone as loc_bookings_phone,
17 lo.bookings_fax as loc_bookings_fax,
11c35ec9
TC
18 lo.bookings_url as loc_bookings_url,
19 sb.id as booking_id
2076966c
TC
20 from article ar, product pr, bse_seminars se, bse_seminar_sessions ss,
21 bse_locations lo, bse_seminar_bookings sb
22 where sb.siteuser_id = ?
23 and sb.session_id = ss.id
24 and ss.seminar_id = ar.id
25 and ss.seminar_id = pr.articleId
26 and ss.seminar_id = se.seminar_id
27 and ss.location_id = lo.id
28SQL
29
30name: bse_locationSessionDetail
31sql_statement: <<SQL
32select ar.*, pr.*, se.*, ss.*,
33 ss.id as session_id
34 from article ar, product pr, bse_seminars se, bse_seminar_sessions ss
35 where ss.location_id = ?
36 and ss.seminar_id = ar.id
37 and ss.seminar_id = pr.articleId
38 and ss.seminar_id = se.seminar_id
39 order by ss.when_at desc
40SQL
20d6805f 41
11c35ec9 42name: getSeminarBookingBySession_idAndSiteuser_id
20d6805f
TC
43sql_statement: <<SQL
44select * from bse_seminar_bookings
45 where session_id = ? and siteuser_id = ?
46SQL
47
48name: bse_cancelSessionBookingForUser
49sql_statement: <<SQL
50delete from bse_seminar_bookings
51 where session_id = ? and siteuser_id = ?
52SQL
53
54name: bse_updateSessionBookingForUser
55sql_statement: <<SQL
56update bse_seminar_bookings
57 set roll_present = ?, options = ?, customer_instructions = ?,
58 support_notes = ?
59 where session_id = ? and siteuser_id = ?
11c35ec9
TC
60SQL
61
62name: getSeminarBookingByPkey
63sql_statement: <<SQL
64select * from bse_seminar_bookings where id = ?
65SQL
66
67name: replaceSeminarBooking
68sql_statement: <<SQL
69replace bse_seminar_bookings values(?,?,?,?,?,?,?)
70SQL
71
72name: SeminarSessions.sessionsUnbookedByUser
73sql_statement: <<SQL
74select ss.* from bse_seminar_sessions ss left join bse_seminar_bookings sb
75 on ss.id = sb.session_id and sb.siteuser_id = ?
76where ss.seminar_id = ? and sb.siteuser_id is null
77SQL
0eb78304
TC
78
79name: seminarSessionInfoUnbooked
80sql_statement: <<SQL
81select ss.*, lo.*, ss.id as session_id
82 from bse_locations lo
83 right join bse_seminar_sessions ss on lo.id = ss.location_id
84 left join bse_seminar_bookings sb
85 on ss.id = sb.session_id and sb.siteuser_id = ?
86where ss.seminar_id = ? and sb.siteuser_id is null and lo.id = ss.location_id
87SQL
88
4d764c34
TC
89name: AdminGroups.forUser
90sql_statement: <<SQL
91select ab.*, ag.*
92 from admin_base ab, admin_groups ag, admin_membership am
93where am.user_id = ? and am.group_id = ab.id and ab.id = ag.base_id
94SQL
95
32696f84
TC
96name: siteuserAddFileCategory
97sql_statement: <<SQL
98insert into bse_file_subscriptions(siteuser_id, category)
99 values(?, ?)
100SQL
101
102name: siteuserRemoveFileCategory
103sql_statement: <<SQL
104delete from bse_file_subscriptions where siteuser_id = ? and category = ?
105SQL
106
107name: siteuserSubscribedFileCategories
108sql_statement: <<SQL
109select category
110from bse_file_subscriptions
111where siteuser_id = ?
112SQL
113
114name: OwnedFiles.userVisibleGroupFiles
115sql_statement: <<SQL
116select of.*
117from bse_owned_files of, bse_siteuser_membership sm
118where sm.siteuser_id = ?
119 and sm.group_id = of.owner_id
120 and of.owner_type = 'G'
121SQL
122
123name: bseDownloadLogAge
124sql_statement: <<SQL
125delete from bse_file_access_log
126 where when_at < date_sub(now(), interval ? day)
3f187817
TC
127SQL
128
129name: bseClearOldFileNotifications
130sql_statement: <<SQL
131delete from bse_file_notifies
132 where when_at < date_sub(now(), interval ? day)
133SQL
134
135name: bseAddOwnedFileNotification
136sql_statement: <<SQL
137insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
138 values(?, ?, ?, now())
139SQL
140
141name: bseAddOwnedFileNotificationTime
142sql_statement: <<SQL
143insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
144 values(?, ?, ?, ?)
145SQL
146
147name: baseClearOldFileNotifications
148sql_statement: <<SQL
149delete from bse_file_notifies
150 where when_at < date_sub(now(), interval ? day)
151SQL
152
153name: bseDeleteFileNotification
154sql_statement: <<SQL
155delete from bse_file_notifies where id = ?
156SQL
157
158name: bseExpandGroupFileNotification
159sql_statement: <<SQL
160insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
161select fn.file_id, 'U', sm.siteuser_id, fn.when_at
162from bse_file_notifies fn,
163 bse_siteuser_membership sm,
164 bse_owned_files fi,
165 bse_file_subscriptions fs
166where fn.id = ?
167 and fi.id = fn.file_id
168 and fn.owner_id = sm.group_id
169 and fs.siteuser_id = fs.siteuser_id
170 and fs.category = fi.category;
171SQL
172
173name: bseNotifyFileGroupEntries
174sql_statement: <<SQL
175select * from bse_file_notifies
176where owner_type = 'G'
177order by when_at
178limit 100
179SQL
180
181name: bseFileNotifyUsers
182sql_statement: <<SQL
183select distinct owner_id as id
184from bse_file_notifies
185where owner_type = 'U'
186SQL
187
188name: bseFileNotifyUserEntries
189sql_statement: <<SQL
190select *
191from bse_file_notifies
192where owner_type = 'U'
193 and owner_id = ?
194SQL
026d5cec
TC
195
196name: BackgroundTasks
197sql_statement: select * from bse_background_tasks
6430ee52
TC
198
199name: bseClearArticleFileMetadata
200sql_statement: <<SQL
201delete from bse_article_file_meta
202where file_id = ?
f5b7b326 203 and owner_type = ?
6430ee52 204SQL
c840f7f9
TC
205
206name: bseClearArticleFileAppMetadata
207sql_statement: <<SQL
208delete from bse_article_file_meta
f5b7b326
TC
209where file_id = ?
210 and appdata <> 0
211 and owner_type = ?
c840f7f9
TC
212SQL
213
214name: bseClearArticleFileSysMetadata
215sql_statement: <<SQL
216delete from bse_article_file_meta
f5b7b326
TC
217where file_id = ?
218 and appdata = 0
219 and owner_type = ?
c840f7f9
TC
220SQL
221
36e373a9
TC
222name: bseDeleteArticleFileMetaByName
223sql_statement: <<SQL
224delete from bse_article_file_meta
f5b7b326
TC
225where file_id = ?
226 and name = ?
227 and owner_type = ?
36e373a9 228SQL
60bc6601
TC
229
230name: bseArticleKidSummary
231sql_statement: <<SQL
66acec63
TC
232select id, title, level, listed, lastModified
233from article where parentid = ?
60bc6601 234order by displayOrder desc
91fad97a
TC
235SQL
236
237name: bseChildOrder
238sql_statement: <<SQL
239select id, displayOrder
240from article
241where parentid = ?
242SQL
243
244name: bseStepchildOrder
245sql_statement: <<SQL
246select childId as id, parentDisplayOrder as displayOrder, id as step_id
247from other_parents
248where parentId = ?
249SQL
250
251name: bseSetStepOrder
252sql_statement: <<SQL
253update other_parents
254set parentDisplayOrder = ?
255where id = ?
256SQL
257
258name: bseSetArticleOrder
259sql_statement: <<SQL
260update article
261set displayOrder = ?
262where id = ?
263SQL
a0edb02e
TC
264
265name: bseMarkOwnedOrdersAnon
266sql_statement: <<SQL
267update orders
268set siteuser_id = -1 where siteuser_id = ?
269SQL
270
271name: bseRemoveUserSubs
272sql_statement: <<SQL
273delete from subscribed_users
274where userId = ?
275SQL
276
277name: bseRemoveUserWishlist
278sql_statement: <<SQL
279delete from bse_wishlist
280where user_id = ?
281SQL
282
283name: bseRemoveUserMemberships
284sql_statement: <<SQL
285delete from bse_siteuser_membership
286where siteuser_id = ?
287SQL
288
289name: bseRemoveUserFileSubs
290sql_statement: <<SQL
291delete from bse_file_subscriptions
292where siteuser_id = ?
293SQL
294
295name: bseRemoveUserFileNotifies
296sql_statement: <<SQL
297delete from bse_file_notifies
298where owner_id = ? and owner_type = 'U'
299SQL
300
301name: bseMarkUserFileAccessesAnon
302sql_statement: <<SQL
303update bse_file_access_log
304set siteuser_id = -1 where siteuser_id = ?
305SQL
306
307name: bseRemoveUserProdSubs
308sql_statement: <<SQL
309delete from bse_user_subscribed
310where siteuser_id = ?
311SQL
312
313name: bseRemoveUserBookings
314sql_statement: <<SQL
315delete from bse_seminar_bookings
316where siteuser_id = ?
317SQL
318
726ffaed
TC
319name: bseMarkProductOrderItemsAnon
320sql_statement: <<SQL
321update order_item
322set productId = -1
323where productId = ?
324SQL
325
326name: bseRemoveProductFromWishlists
327sql_statement: <<SQL
328delete from bse_wishlist
329where product_id = ?
330SQL
ebc63b18
TC
331
332name: bseGetMsgManaged
333sql_statement: <<SQL
334select m.message, m.language_code, b.formatting, b.params
335from bse_msg_managed m, bse_msg_base b
336where m.id = ?
337 and m.language_code = ?
338 and m.id = b.id
339SQL
340
341name: bseGetMsgDefault
342sql_statement: <<SQL
343select d.message, d.language_code, b.formatting, b.params
344from bse_msg_defaults d, bse_msg_base b
345where d.id = ?
346 and d.language_code = ?
347 and d.id = b.id
348order by d.priority desc
349SQL
350
351name: bseAllMsgs
352sql_statement: <<SQL
353select * from bse_msg_base order by id
354SQL
355
356name: bseMessageDetail
357sql_statement: select * from bse_msg_base where id = ?
358
359name: bseMessageDefaults
360sql_statement: <<SQL
361select * from bse_msg_defaults
362where id = ?
363SQL
364
365name: bseMessageDefinitions
366sql_statement: <<SQL
367select * from bse_msg_managed
368where id = ?
369SQL
370
371name: bseMessageFetch
372sql_statement: <<SQL
373select * from bse_msg_managed
374where id = ? and language_code = ?
375SQL
376
377name: bseMessageCreate
378sql_statement: <<SQL
379insert into bse_msg_managed(id, language_code, message) values(?,?,?)
380SQL
381
382name: bseMessageUpdate
383sql_statement: <<SQL
384update bse_msg_managed
385 set message = ?
386 where id = ? and language_code = ?
387SQL
388
389name: bseMessageDelete
390sql_statement: <<SQL
391delete from bse_msg_managed where id = ? and language_code = ?
392SQL
393
394name: AdminUIStates.matchingState
395sql_statement: <<SQL
396select *
397from bse_admin_ui_state
398where user_id = ?
399 and name like ?
400SQL
1d383001
TC
401
402name: bseProductHasSaleFiles
403sql_statement: <<SQL
404select exists(
405 select id
406 from article_files
407 where articleId = ?
408 and forSale <> 0
409 ) as "have_sale_files"
410SQL
c925a6af
TC
411
412name: bseAuditLogClean
413sql_statement: <<SQL
414delete from bse_audit_log
415where when_at < date_sub(now(), interval ? day)
416SQL
736c2142
TC
417
418name: ArticleFiles.userPaidFor
419sql_statement: <<SQL
853430eb 420select distinct af.*
736c2142
TC
421from article_files af, orders od, order_item oi
422where af.forSale <> 0
423 and af.articleId = oi.productId
424 and oi.orderId = od.id
425 and od.paidFor <> 0
426 and od.siteuser_id = ?
427order by af.displayName
428SQL
590bd52e 429
eb9d306d
TC
430name: ArticleFiles.orderPaidFor
431sql_statement: <<SQL
432select distinct af.*
433from article_files af, order_item oi
434where af.forSale <> 0
435 and af.articleId = oi.productId
436 and oi.orderId = ?
437order by af.displayName
438SQL
439
7c6f563b 440name: Orders.fileOrdersByUser
590bd52e 441sql_statement: <<SQL
7c6f563b 442select distinct od.*
590bd52e
TC
443from article_files af, order_item oi, orders od
444where af.id = ?
445 and af.articleId = oi.productId
446 and oi.orderId = od.id
590bd52e
TC
447 and od.siteuser_id = ?
448SQL
1d7c96a9
TC
449
450name: Files.selected_files
451sql_statement: <<SQL
452select bf.*
453from bse_files bf, bse_selected_files sf
454where sf.owner_type = ?
455 and sf.owner_id = ?
456 and sf.file_id = bf.id
457SQL
458
459name: SelectedFiles.remove_owner
460sql_statement: <<SQL
461delete from bse_selected_files
462 where owner_type = ?
463 and owner_id = ?
464SQL
465
dfd483db
TC
466name: bseRemoveProductPrices
467sql_statement: <<SQL
468delete from bse_price_tier_prices
469where product_id = ?
470SQL
76c6b28e
TC
471
472name: TagMembers.removeByTag
473sql_statement: <<SQL
474delete from bse_tag_members
475where owner_type = ?
476 and owner_id = ?
477 and tag_id = ?
478SQL
479
480name: TagMembers.remove_owned_by
481sql_statement: <<SQL
482delete from bse_tag_members
483where owner_type = ?
484 and owner_id = ?
485SQL
486
487name: Tags.object_tags
488sql_statement: <<SQL
489select t.*
490from bse_tags t, bse_tag_members tm
491where t.id = tm.tag_id
492 and tm.owner_type = ?
493 and tm.owner_id = ?
494order by t.cat, t.val
495SQL
496
497name: Tag_ids.by_owner
498sql_statement: <<SQL
499select tag_id as "id"
500from bse_tag_members
501where owner_type = ?
502 and owner_id = ?
503SQL
504
505name: TagMembers.deleteTag
506sql_statement: <<SQL
507delete from bse_tag_members
508where tag_id = ?
509SQL
510
511name: Articles.byTag
512sql_statement: <<SQL
513select a.*
514from article a, bse_tag_members m
515where a.id = m.owner_id
516 and m.tag_id = ?
517SQL
518
519name: bseTagsCleanup
520sql_statement: <<SQL
521delete from bse_tags
522where id not in (select tag_id from bse_tag_members);
523SQL
81aa5f57
TC
524
525name: TagMembers.allprods
526sql_statement: <<SQL
527select *
528from bse_tag_members
529where owner_type = 'BA'
530 and owner_id in
531 (select id
532 from article
533 where parentid = ?
534 and listed != 0
f09e8b4f 535 and generator = 'BSE::Generate::Product'
81aa5f57
TC
536 union
537 select op.childId
538 from other_parents op, article a
539 where op.parentId = ?
540 and op.childId = a.id
f09e8b4f 541 and a.generator = 'BSE::Generate::Product'
81aa5f57
TC
542 )
543SQL
544
545name: Tags.allprods
546sql_statement: <<SQL
547select distinct t.*
548from bse_tags t, bse_tag_members m
549where t.id = m.tag_id
550 and m.owner_type = 'BA'
551 and m.owner_id in
552 (select id
553 from article
554 where parentid = ?
555 and listed <> 0
f09e8b4f 556 and generator = 'BSE::Generate::Product'
81aa5f57
TC
557 union
558 select op.childId
559 from other_parents op, article a
560 where op.parentId = ?
561 and op.childId = a.id
562 and a.listed <> 0
f09e8b4f 563 and a.generator = 'BSE::Generate::Product')
81aa5f57
TC
564SQL
565
566name: TagMembers.allkids
567sql_statement: <<SQL
568select *
569from bse_tag_members
570where owner_type = 'BA'
571 and owner_id in
572 (select id
573 from article
574 where parentid = ?
575 and listed <> 0
576 union
577 select op.childId
578 from other_parents op, article a
579 where op.parentId = ?
580 and op.childId = a.id
581 and a.listed <> 0
582 )
583SQL
584
585name: Tags.allkids
586sql_statement: <<SQL
587select distinct t.*
588from bse_tags t, bse_tag_members m
589where t.id = m.tag_id
590 and m.owner_type = 'BA'
591 and m.owner_id in
592 (select id
593 from article
594 where parentid = ?
595 and listed <> 0
596
597 union
598 select op.childId
599 from other_parents op, article a
600 where op.parentId = ?
601 and op.childId = a.id
602 and a.listed <> 0
603 )
604SQL
605
a3e0dbc8
TC
606name: Tags.images
607sql_statement: <<SQL
608select distinct t.*
609from bse_tags t, bse_tag_members m, image i
610where t.id = m.tag_id
611 and m.owner_type = 'BI'
612 and m.owner_id = i.id
613 and i.articleId = ?
614SQL
615
616name: TagMembers.images
617sql_statement: <<SQL
618select distinct m.*
619from bse_tag_members m, image i
620where m.owner_type = 'BI'
621 and m.owner_id = i.id
622 and i.articleId = ?
623SQL
624
34c37938
TC
625name: TagCategoryDeps.deleteCat
626sql_statement: <<SQL
627delete from bse_tag_category_deps
628where cat_id = ?
629SQL
630
631name: TagOwners.allCats
632sql_statement: <<SQL
633select distinct concat(t.cat, ':') as cat
634from bse_tags t
635where owner_type = ?
636 and t.cat <> ''
637order by cat
638SQL
7bf4efe4
TC
639
640name: bseRemoveArticleSiteUserGroups
641sql_statement: <<SQL
642delete from bse_article_groups
643where article_id = ?
644SQL
645
646name: bseRemoveArticleAdminAccess
647sql_statement: <<SQL
648delete from admin_perms
649where object_id = ?
650SQL
74b3689a
TC
651
652name: AuditLog.logonRecords
653sql_statement: <<SQL
654select * from bse_audit_log
655where object_id = ?
656 and object_type = ?
657 and facility = 'bse'
658 and component = ?
659 and module = ?
660 and
661 (
662 `function` = 'success'
663 or
664 `function` = 'invalid'
665 or
666 `function` = 'unlock'
667 or
668 `function` = 'recover'
669 )
670 and when_at > date_sub(now(), interval ? minute)
671order by when_at asc
672SQL
673
674name: AuditLog.ipLogonRecords
675sql_statement: <<SQL
676select * from bse_audit_log
677where ip_address = ?
678 and facility = 'bse'
679 and component = ?
680 and module = ?
681 and
682 (
683 `function` = 'success'
684 or
685 `function` = 'invalid'
686 or
687 `function` = 'unlock'
688 )
689 and when_at > date_sub(now(), interval ? minute)
690order by when_at asc
691SQL
692
693name: bse_lockout_ip
694sql_statement: call bse_ip_lockout(?,?,?)
695
1bd8e312
TC
696name: getAdminUserByPassword_type
697sql_statement: <<SQL
698select bs.*, us.* from admin_base bs, admin_users us
699 where bs.id = us.base_id and us.password_type = ?
700SQL
023761bd
TC
701
702name: PriceTiers.forCoupon
703sql_statement: <<SQL
704select t.*
705from bse_price_tiers t, bse_coupon_tiers c
706where c.coupon_id = ?
707 and c.tier_id = t.id
708SQL
a3e0dbc8 709