1 package App::IdiotBox::Store::SQLite;
5 use DBIx::Data::Collection::Set::Wrapper::Inflate;
6 use DBIx::Data::Store::CRUD;
7 use DBIx::Data::Store::Raw;
10 my ($class, $ib) = @_;
11 my $raw = DBIx::Data::Store::Raw->connect(
12 'dbi:SQLite:'.$ib->config->{db_file}
14 $ib->{recent_announcements} = _bind_announcements($raw);
15 $ib->{buckets} = _bind_buckets($raw);
19 my ($raw, $inflator, $sql, $extra) = @_;
20 DBIx::Data::Collection::Set::Wrapper::Inflate->new({
21 inflator => $inflator,
22 inner => DBIx::Data::Store::CRUD->new({
31 package App::IdiotBox::Inflator::Announcement;
34 my ($self, $raw) = @_;
35 my %new = (bucket => bless({}, 'App::IdiotBox::Bucket'));
36 (@new{qw(id made_at video_count)},
37 @{$new{bucket}}{qw(slug name video_count)})
39 bless(\%new, 'App::IdiotBox::Announcement');
43 my ($self, $body) = @_;
44 [ $body->{bucket}{slug}, $body->{made_at} ]
48 sub _bind_announcements {
51 'App::IdiotBox::Inflator::Announcement',
55 announcement.id, announcement.made_at, COUNT(DISTINCT my_videos.slug),
56 bucket.slug, bucket.name, COUNT(DISTINCT all_videos.slug)
58 announcements announcement
60 ON bucket.slug = announcement.bucket_slug
62 ON my_videos.announcement_id = announcement.id
63 JOIN videos all_videos
64 ON all_videos.bucket_slug = announcement.bucket_slug
65 JOIN announcements all_announcements
66 ON all_announcements.bucket_slug = announcement.bucket_slug
68 announcement.made_at, bucket.slug, bucket.name
70 announcement.made_at = MAX(all_announcements.made_at)
72 announcement.made_at DESC
75 my ($store, undef, $dbh, $args) = @_;
76 $store->_sth_for($dbh, q{
77 INSERT INTO announcements
78 (bucket_slug, made_at)
82 [ $dbh->last_insert_id(undef,undef,undef,undef),
83 $args->[1], 0, $args->[0], undef, undef ];
90 package App::IdiotBox::Inflator::Bucket;
92 use base qw(DBIx::Data::Store::Inflator::Simple);
94 sub _raw { shift->{raw} }
98 my $inflated = $self->SUPER::inflate(@_);
99 $inflated->{videos} = App::IdiotBox::Store::SQLite::_bind_bucket_videos(
100 $self->_raw, $inflated
109 App::IdiotBox::Inflator::Bucket->new({
110 all_columns => [ qw(slug name) ],
111 body_columns => [ qw(slug name) ],
112 spec_columns => [ qw(slug) ],
113 class => 'App::IdiotBox::Bucket',
133 DELETE FROM buckets WHERE slug = ?
136 UPDATE buckets SET slug = ?, name = ? WHERE slug = ?
143 package App::IdiotBox::Inflator::BucketVideo;
145 use base qw(DBIx::Data::Store::Inflator::Simple);
149 my $new = shift->SUPER::new(@_);
150 Scalar::Util::weaken($new->{bucket});
154 sub _bucket { shift->{bucket} }
158 my $inflated = $self->SUPER::inflate(@_);
159 Scalar::Util::weaken($inflated->{bucket} = $self->_bucket);
164 sub _bind_bucket_videos {
165 my ($raw, $bucket) = @_;
168 App::IdiotBox::Inflator::BucketVideo->new({
170 spec_columns => [ qw(slug) ],
171 all_columns => [ qw(slug name author details) ],
172 body_columns => [ qw(announcement_id slug name author) ],
173 class => 'App::IdiotBox::Video',
177 SELECT slug, name, author, details
179 WHERE bucket_slug = ?
183 SELECT slug, name, author, details
185 WHERE slug = ? AND bucket_slug = ?
189 (announcement_id, slug, name, author, details, bucket_slug)
195 { append_args => [ $bucket->slug ] }