Commit | Line | Data |
265c2b91 |
1 | package App::IdiotBox::Store::SQLite; |
2 | |
6df05090 |
3 | use strictures 1; |
4 | |
5 | use DBIx::Data::Collection::Set::Wrapper::Inflate; |
265c2b91 |
6 | use DBIx::Data::Store::CRUD; |
6df05090 |
7 | use DBIx::Data::Store::Raw; |
8 | |
9 | sub bind { |
10 | my ($class, $ib) = @_; |
11 | my $raw = DBIx::Data::Store::Raw->connect( |
12 | 'dbi:SQLite:'.$ib->config->{db_file} |
13 | ); |
14 | $ib->{recent_announcements} = _bind_announcements($raw); |
15 | $ib->{buckets} = _bind_buckets($raw); |
16 | } |
17 | |
18 | sub _bind_set { |
19 | my ($raw, $inflator, $sql, $extra) = @_; |
20 | DBIx::Data::Collection::Set::Wrapper::Inflate->new({ |
21 | inflator => $inflator, |
22 | inner => DBIx::Data::Store::CRUD->new({ |
23 | raw => $raw, |
24 | sql => $sql, |
25 | %{$extra||{}}, |
26 | }) |
27 | }); |
28 | } |
29 | |
30 | { |
31 | package App::IdiotBox::Inflator::Announcement; |
32 | |
33 | sub inflate { |
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)}) |
38 | = @$raw; |
39 | bless(\%new, 'App::IdiotBox::Announcement'); |
40 | } |
41 | |
42 | sub deflate_body { |
43 | my ($self, $body) = @_; |
44 | [ $body->{bucket}{slug}, $body->{made_at} ] |
45 | } |
46 | } |
47 | |
48 | sub _bind_announcements { |
49 | _bind_set( |
50 | $_[0], |
51 | 'App::IdiotBox::Inflator::Announcement', |
52 | { |
53 | select_all => q{ |
54 | SELECT |
55 | announcement.id, announcement.made_at, COUNT(DISTINCT my_videos.slug), |
56 | bucket.slug, bucket.name, COUNT(DISTINCT all_videos.slug) |
57 | FROM |
58 | announcements announcement |
59 | JOIN buckets bucket |
60 | ON bucket.slug = announcement.bucket_slug |
61 | JOIN videos my_videos |
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 |
67 | GROUP BY |
68 | announcement.made_at, bucket.slug, bucket.name |
69 | HAVING |
70 | announcement.made_at = MAX(all_announcements.made_at) |
71 | ORDER BY |
72 | announcement.made_at DESC |
265c2b91 |
73 | }, |
6df05090 |
74 | insert_sql => sub { |
75 | my ($store, undef, $dbh, $args) = @_; |
76 | $store->_sth_for($dbh, q{ |
77 | INSERT INTO announcements |
78 | (bucket_slug, made_at) |
79 | VALUES |
80 | (?, ?) |
81 | }, $args); |
82 | [ $dbh->last_insert_id(undef,undef,undef,undef), |
83 | $args->[1], 0, $args->[0], undef, undef ]; |
265c2b91 |
84 | }, |
6df05090 |
85 | } |
86 | ) |
87 | } |
265c2b91 |
88 | |
6df05090 |
89 | { |
90 | package App::IdiotBox::Inflator::Bucket; |
91 | |
92 | use base qw(DBIx::Data::Store::Inflator::Simple); |
93 | |
94 | sub _raw { shift->{raw} } |
95 | |
96 | sub inflate { |
97 | my $self = shift; |
98 | my $inflated = $self->SUPER::inflate(@_); |
99 | $inflated->{videos} = App::IdiotBox::Store::SQLite::_bind_bucket_videos( |
100 | $self->_raw, $inflated |
101 | ); |
102 | $inflated; |
103 | } |
265c2b91 |
104 | } |
105 | |
6df05090 |
106 | sub _bind_buckets { |
107 | _bind_set( |
108 | $_[0], |
109 | App::IdiotBox::Inflator::Bucket->new({ |
110 | all_columns => [ qw(slug name) ], |
111 | spec_columns => [ qw(slug) ], |
112 | class => 'App::IdiotBox::Bucket', |
113 | raw => $_[0], |
114 | }), |
115 | { |
116 | select_all => q{ |
117 | SELECT slug, name |
118 | FROM buckets |
119 | }, |
120 | select_one => q{ |
121 | SELECT slug, name |
122 | FROM buckets |
123 | WHERE slug = ? |
124 | }, |
125 | } |
126 | ) |
265c2b91 |
127 | } |
128 | |
6df05090 |
129 | { |
130 | package App::IdiotBox::Inflator::BucketVideo; |
265c2b91 |
131 | |
6df05090 |
132 | use base qw(DBIx::Data::Store::Inflator::Simple); |
133 | use Scalar::Util (); |
265c2b91 |
134 | |
6df05090 |
135 | sub new { |
136 | my $new = shift->SUPER::new(@_); |
137 | Scalar::Util::weaken($new->{bucket}); |
138 | $new; |
139 | } |
140 | |
141 | sub _bucket { shift->{bucket} } |
142 | |
143 | sub inflate { |
144 | my $self = shift; |
145 | my $inflated = $self->SUPER::inflate(@_); |
146 | Scalar::Util::weaken($inflated->{bucket} = $self->_bucket); |
147 | $inflated; |
265c2b91 |
148 | } |
265c2b91 |
149 | } |
150 | |
6df05090 |
151 | sub _bind_bucket_videos { |
152 | my ($raw, $bucket) = @_; |
153 | _bind_set( |
154 | $raw, |
155 | App::IdiotBox::Inflator::BucketVideo->new({ |
156 | bucket => $bucket, |
157 | spec_columns => [ qw(slug) ], |
158 | all_columns => [ qw(slug name author details) ], |
159 | body_columns => [ qw(announcement_id slug name author) ], |
160 | class => 'App::IdiotBox::Video', |
161 | }), |
162 | { |
163 | select_all => q{ |
164 | SELECT slug, name, author, details |
165 | FROM videos |
166 | WHERE bucket_slug = ? |
167 | ORDER BY name |
168 | }, |
169 | select_one => q{ |
170 | SELECT slug, name, author, details |
171 | FROM videos |
172 | WHERE slug = ? AND bucket_slug = ? |
173 | }, |
174 | insert_one => q{ |
175 | INSERT INTO videos |
176 | (announcement_id, slug, name, author, details, bucket_slug) |
177 | VALUES |
178 | (?, ?, ?, ?, '', ?) |
179 | } |
180 | |
181 | }, |
182 | { append_args => [ $bucket->slug ] } |
183 | ); |
265c2b91 |
184 | } |
185 | |
186 | 1; |