1 package BSE::DB::Mysql;
9 use Constants 0.1 qw/$DSN $UN $PW/;
19 Articles => 'select * from article',
21 'replace article values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
23 'insert article values (null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
24 deleteArticle => 'delete from article where id = ?',
25 getArticleByPkey => 'select * from article where id = ?',
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
34 'Articles.stepKids' => <<EOS,
35 select ar.* from article ar, other_parents op
36 where op.childId = ar.id and op.parentId = ?
38 'Articles.visibleStepKids' => <<EOS,
39 select ar.* from article ar, other_parents op
40 where op.childId = ar.id
41 and op.parentId = ? and ? between op.release and op.expire
43 'Articles.ids'=>'select id from article',
45 Images => 'select * from image',
47 'replace image values (?,?,?,?,?,?,?,?,?)',
48 addImage => 'insert image values(null, ?, ?, ?, ?, ?, ?, ?, ?)',
49 deleteImage => 'delete from image where id = ?',
50 getImageByArticleId => 'select * from image where articleId = ? order by displayOrder',
52 dropIndex => 'delete from searchindex',
53 insertIndex => 'insert searchindex values(?, ?, ?, ?)',
54 searchIndex => 'select * from searchindex where id = ?',
55 searchIndexWC => 'select * from searchindex where id like ?',
57 Products=> 'select article.*, product.* from article, product where id = articleId',
58 addProduct => 'insert product values(?,?,?,?,?,?,?)',
59 getProductByPkey => 'select article.*, product.* from article, product where id=? and articleId = id',
60 replaceProduct => 'replace product values(?,?,?,?,?,?,?)',
61 'Products.stepProducts' => <<EOS,
62 select ar.*, pr.* from article ar, product pr, other_parents op
63 where ar.id = pr.articleId and op.childId = ar.id and op.parentId = ?
65 'Products.visibleStep' => <<EOS,
66 select ar.*, pr.* from article ar, product pr, other_parents op
67 where ar.id = pr.articleId and op.childId = ar.id
68 and op.parentId = ? and ? between op.release and op.expire
70 deleteProduct => 'delete from product where articleId = ?',
71 Orders => 'select * from orders',
72 getOrderByPkey => 'select * from orders where id = ?',
73 getOrderItemByOrderId => 'select * from order_item where orderId = ?',
74 addOrder => 'insert orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
75 replaceOrder => 'replace orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
76 addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?)',
77 getOrderByUserId => 'select * from orders where userId = ?',
79 getOrderItemByProductId => 'select * from order_item where productId = ?',
81 OtherParents => 'select * from other_parents',
82 getOtherParentByChildId => <<EOS,
83 select * from other_parents where childId = ? order by childDisplayOrder desc
85 getOtherParentByParentId => <<EOS,
86 select * from other_parents where parentId = ? order by parentDisplayOrder desc
88 getOtherParentByParentIdAndChildId =>
89 'select * from other_parents where parentId = ? and childId = ?',
90 addOtherParent=>'insert other_parents values(null,?,?,?,?,?,?)',
91 deleteOtherParent => 'delete from other_parents where id = ?',
92 replaceOtherParent=>'replace other_parents values(?,?,?,?,?,?,?)',
93 'OtherParents.anylinks' =>
94 'select * from other_parents where childId = ? or parentId = ?',
97 'insert into article_files values (null,?,?,?,?,?,?,?,?,?,?,?)',
99 'replace article_files values (?,?,?,?,?,?,?,?,?,?,?,?)',
100 deleteArticleFile => 'delete from article_files where id = ?',
101 getArticleFileByArticleId =>
102 'select * from article_files where articleId = ? order by displayOrder desc',
103 getArticleFileByPkey => 'select * from article_files where id = ?',
106 select distinct af.*, oi.id as item_id
107 from article_files af, order_item oi
108 where af.articleId = oi.productId and oi.orderId = ?
109 order by af.description
112 getSiteUserByUserId =>
113 'select * from site_users where userId = ?',
115 'select * from site_users where id = ?',
116 addSiteUser => 'insert site_users values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
117 replaceSiteUser => 'replace site_users values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
118 'SiteUsers.removeSubscriptions'=>
119 'delete from subscribed_users where userId = ?',
120 'SiteUsers.removeSub'=>
121 'delete from subscribed_users where userId = ? and subId = ?',
122 'SiteUsers.subRecipients' => <<EOS,
123 select si.* from site_users si, subscribed_users su
124 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
126 SiteUsers => 'select * from site_users',
129 'select * from subscription_types',
130 addSubscriptionType=>
131 'insert subscription_types values(null,?,?,?,?,?,?,?,?,?,?,?,?)',
132 replaceSubscriptionType=>
133 'replace subscription_types values(?,?,?,?,?,?,?,?,?,?,?,?,?)',
134 getSubscriptionTypeByPkey =>
135 'select * from subscription_types where id = ? order by name',
136 deleteSubscriptionType =>
137 'delete from subscription_types where id = ?',
138 subRecipientCount => <<EOS,
139 select count(*) as "count" from site_users si, subscribed_users su
140 where confirmed <> 0 and disabled = 0 and si.id = su.userId and su.subId = ?
142 'SubscriptionTypes.userSubscribedTo' => <<'EOS',
143 select su.* from subscription_types su, subscribed_users us
144 where us.userId = ? and us.subId = su.id
148 'insert subscribed_users values(null,?,?)',
149 getSubscribedUserByUserId =>
150 'select * from subscribed_users where userId = ?',
152 # the following don't work with the row/table classes
154 'select id, title from article order by level, displayOrder desc',
156 getEmailBlackEntryByEmail =>
157 'select * from email_blacklist where email = ?',
158 addEmailBlackEntry =>
159 'insert email_blacklist values(null,?,?)',
162 'insert email_requests values(null,?,?,?,?)',
163 replaceEmailRequest =>
164 'replace email_requests values(?,?,?,?,?)',
165 deleteEmailRequest =>
166 'delete from email_requests where id = ?',
167 getEmailRequestByGenEmail =>
168 'select * from email_requests where genEmail = ?',
170 addAdminBase => 'insert into admin_base values(null, ?)',
171 replaceAdminBase => 'replace into admin_base values(?, ?)',
172 deleteAdminBase => 'delete from admin_base where id = ?',
173 getAdminBaseByPkey => 'select * from admin_base where id=?',
176 select bs.*, us.* from admin_base bs, admin_users us
177 where bs.id = us.base_id
180 getAdminUserByLogon => <<SQL,
181 select bs.*, us.* from admin_base bs, admin_users us
182 where bs.id = us.base_id and us.logon = ?
184 getAdminUserByPkey => <<SQL,
185 select bs.*, us.* from admin_base bs, admin_users us
186 where bs.id = us.base_id and bs.id = ?
188 addAdminUser => 'insert into admin_users values(?,?,?,?,?)',
189 replaceAdminUser => 'replace into admin_users values(?,?,?,?,?)',
190 deleteAdminUser => 'delete from admin_users where base_id = ?',
191 adminUsersGroups => <<SQL,
193 from admin_base bs, admin_groups gr, admin_membership am
194 where bs.id = gr.base_id && am.user_id = ? and am.group_id = bs.id
197 userGroups => 'select * from admin_membership where user_id = ?',
198 deleteUserGroups => 'delete from admin_membership where user_id = ?',
200 AdminGroups => <<SQL,
202 from admin_base bs, admin_groups gr
203 where bs.id = gr.base_id
206 adminGroupsUsers => <<SQL,
208 from admin_base bs, admin_users us, admin_membership am
209 where bs.id = us.base_id && am.group_id = ? and am.user_id = bs.id
212 getAdminGroupByName => <<SQL,
213 select bs.*, gr.* from admin_base bs, admin_groups gr
214 where bs.id = gr.base_id and gr.name = ?
216 getAdminGroupByPkey => <<SQL,
217 select bs.*, gr.* from admin_base bs, admin_groups gr
218 where bs.id = gr.base_id and bs.id = ?
220 addAdminGroup => 'insert into admin_groups values(?,?,?,?)',
221 replaceAdminGroup => 'replace into admin_groups values(?,?,?,?)',
222 deleteAdminGroup => 'delete from admin_groups where base_id = ?',
223 groupUsers => 'select * from admin_membership where group_id = ?',
224 'AdminGroups.userPermissionGroups' => <<SQL,
225 select bs.*, ag.* from admin_base bs, admin_groups ag, admin_membership am
226 where bs.id = ag.base_id
227 and ( (ag.base_id = am.group_id and am.user_id = ?)
228 or ag.name = 'everyone' )
231 addUserToGroup => 'insert into admin_membership values(?,?)',
232 delUserFromGroup => <<SQL,
233 delete from admin_membership where user_id = ? and group_id = ?
235 deleteGroupUsers => 'delete from admin_membership where group_id = ?',
237 articleObjectPerm => <<SQL,
238 select * from admin_perms where object_id = ? and admin_id = ?
240 addArticleObjectPerm => 'insert into admin_perms values(?,?,?)',
241 replaceArticleObjectPerm => 'replace into admin_perms values(?,?,?)',
245 where ap.admin_id = ?
249 from admin_perms ap, admin_membership am
250 where ap.admin_id = am.group_id and am.user_id = ?
252 commonPerms => <<SQL,
254 from admin_perms ap, admin_groups ag
255 where ap.admin_id = ag.base_id and ag.name = 'everyone'
262 warn "Incorrect number of parameters passed to DatabaseHandle::single\n" unless @_ == 0;
264 unless ( defined $self ) {
265 my $dbh = DBI->connect_cached( $DSN, $UN, $PW)
266 or die "Cannot connect to database: $DBI::errstr";
268 $self = bless { dbh => $dbh }, $class;
274 my ($self, $name) = @_;
276 $name =~ s/BSE.*:://;
278 $statements{$name} or confess "Statement named '$name' not found";
279 my $sth = $self->{dbh}->prepare($statements{$name})
280 or croak "Cannot prepare $name statment: ",$self->{dbh}->errstr;
286 my ($self, $sth) = @_;
288 my $id = $sth->{"mysql_insertid"};
297 # this is wierd - we only need to reset this on 5.6.x (for x == 0 so
299 # Works fine without the reset for 5.005_03
301 $self->{dbh}->disconnect;