· 8 years ago · Jul 16, 2018, 06:50 AM
1use strictures;
2
3=pod
4
5WHAT ON EARTH IS THIS?
6
7Well if you calm down, I'll explain.
8
9This is vision for a new edit schema, and is very very roughly mocked up in a
10working implementation here. First off all, lets take a look at the schema.
11
12Currently, we have a single table for edits, and a table for votes and
13edit_notes. This does not scale well, and is a massive head ache to work with -
14we all know this. The new schema will look a little bit like this:
15
16 edit_group
17 edit
18 edit_revision
19 data_edit_*
20 edit_note
21 vote
22
23Edits are grouped together. For a lot of edits, there might only be 1 edit in
24the group, but for more complex edits, we might make a lot of changes. Next, an
25edit can have multiple revisions. Again, a lot of edits will hopefully only have
261 revision, but being able to support multiple revisions is helpful. Multiple
27revisions are a solution to the "this is great, but you got {some field} wrong."
28In this case the user can click "new revision" (new version, whatever) and move
29back into an editor (edit artist/edit release, etc) and submit a new version of
30the edit.
31
32For the edit themselves, each edit is represented by 2 rows - a revision, and a
33set of data assossciated with the row. The revision row contains the date, the
34editor who made the revision, etc - metadata, effectively. The data row is a
35little bit more interesting. In here we store the data that is being edited, but
36also a reference to the data at the point of the revision. This is interesting,
37so lets look into this in a bit more detail.
38
39Say I'm editing an artist. I need to store all the information about my edit
40(the new name, the new sort name, etc). But to view the edit in history, I need
41to know how the artist looked at that point in time. We use KiokuDB to flatten
42the object graph into a set of JSON entries, and store these in a larger table.
43For edit artist, this handles the case of artists being deleted, but is
44significantly more useful for tasks such as relationships.
45
46When a relationship is added, we want to see how it looked at the time of
47addition. There is a lot of data required to accurately reconstruct a
48relationship though - the current edits don't even manage to do so. However,
49with KiokuDB we throw it the relationship object, and it does the work for us.
50We never miss data.
51
52Building on the object graph, each entity now consumes a content ID role - this
53means that the ID we use to store it for edits is generated based on a hash of
54interesting data. This way, we never store data twice (for example, dates) we
55keeps the size of the table down.
56
57=cut
58
59{
60 package ContentID;
61 use Moose::Role;
62 use Digest::SHA1 qw( sha1_hex );
63 with 'KiokuDB::Role::ID::Content';
64 requires 'content';
65 sub kiokudb_object_id {
66 my $self = shift;
67 return sha1_hex(join(', ', $self->content));
68 }
69};
70
71{
72 package Artist;
73 use Moose;
74 with 'ContentID';
75
76 has $_ => ( is => 'ro' )
77 for qw( name sort_name );
78
79 sub content {
80 my $self = shift;
81 return (
82 $self->name,
83 $self->sort_name
84 );
85 }
86}
87
88{
89 package Edit;
90 use Moose::Role;
91 has 'id' => ( is => 'ro' );
92 has 'revision' => ( is => 'ro', default => 1 );
93
94 sub new_revision {
95 my $self = shift;
96 }
97}
98
99{
100 package Edit::Artist;
101 use Moose;
102 with 'Edit';
103
104 has $_ => ( is => 'ro' )
105 for qw( name sort_name );
106
107 has 'artist' => (
108 is => 'ro',
109 required => 1
110 );
111
112 sub to_row {
113 my ($self, $c) = @_;
114 return 'data_edit_artist' => {
115 name => $self->name,
116 sort_name => $self->sort_name,
117 artist => $c->kiokudb->store($self->artist)
118 }
119 }
120}
121
122{
123 package Data::Edit;
124 use Moose;
125 use DBI;
126 use KiokuDB;
127
128 has 'dbh' => (
129 is => 'ro',
130 lazy => 1,
131 default => sub {
132 my $dbh = DBI->connect(
133 'dbi:Pg:dbname=musicbrainz_db_2', 'musicbrainz_user', ''
134 );
135
136 $dbh->do('
137 DROP TABLE IF EXISTS edit;
138 DROP TABLE IF EXISTS edit_revision;
139 DROP TABLE IF EXISTS edit_group;
140 DROP TABLE IF EXISTS data_edit_artist;
141
142 CREATE TABLE edit_group ( id SERIAL );
143 CREATE TABLE edit (
144 id SERIAL,
145 edit_group INTEGER NOT NULL
146 );
147 CREATE TABLE edit_revision (
148 edit INTEGER NOT NULL,
149 revision INTEGER
150 );
151 CREATE TABLE data_edit_artist (
152 edit INTEGER NOT NULL,
153 revision INTEGER NOT NULL,
154 name TEXT, sort_name TEXT, artist TEXT
155 );
156 ');
157
158 return $dbh;
159 }
160 );
161
162 has 'kiokudb' => (
163 is => 'ro',
164 lazy => 1,
165 default => sub {
166 KiokuDB->connect(
167 'dbi:Pg:dbname=musicbrainz_db_2',
168 user => 'musicbrainz_user',
169 password => '',
170 create => 1
171 );
172 },
173 );
174
175 sub store {
176 my ($self, $edit) = @_;
177 my $sth;
178
179 my $group = do {
180 my $sth = $self->dbh
181 ->prepare('INSERT INTO edit_group DEFAULT VALUES RETURNING id');
182 $sth->execute;
183 (@{ $sth->fetchrow_arrayref })[0]
184 };
185
186 my $edit_id = do {
187 my $sth = $self->dbh
188 ->prepare('INSERT INTO edit (edit_group) VALUES (?) RETURNING id');
189 $sth->execute($group);
190 (@{ $sth->fetchrow_arrayref })[0]
191 };
192
193 {
194 my $sth = $self->dbh
195 ->prepare('INSERT INTO edit_revision (edit, revision) VALUES (?, ?)');
196 $sth->execute($edit_id, $edit->revision);
197 };
198
199 my $scope = $self->kiokudb->new_scope;
200
201 my ($table, $row) = $edit->to_row($self);
202 $row->{revision} = $edit->revision;
203 $row->{edit} = $edit_id;
204 my $query = "INSERT INTO $table ";
205 my @cols = keys %$row;
206 $query .= '(' . join(', ', @cols) . ') VALUES (' .
207 join(', ', ('?') x @cols) . ')';
208 $sth = $self->dbh->prepare($query);
209 $sth->execute(map { $row->{$_} } @cols);
210 }
211}
212
213# ----
214
215my $artist = Artist->new(
216 name => 'June MilLer',
217 sort_name => 'Miller, June',
218);
219
220my $edit = Edit::Artist->new(
221 artist => $artist,
222 name => 'June Miller',
223 sort_name => 'june miller'
224);
225
226my $final_edit = $edit->new_revision(
227 sort_name => 'June Miller'
228);
229
230my $data = Data::Edit->new;
231$data->store($edit);