· 10 years ago · May 09, 2016, 06:09 PM
1#!/usr/bin/perl
2use DBI;
3use experimental 'smartmatch';
4use Getopt::Long;
5
6
7$result = GetOptions ("user=s" => \$USER,
8 "pass=s" => \$PASS,
9 "target=s" => \$TARGET,
10 "host=s" => \$HOST,
11 "h=s" => \$HOST,
12 "u=s" => \$USER,
13 "p=s" => \$PASS,
14 "port=i" => \$PORT,
15 "config=s" => \$CONFIG,
16 "main=s" => \$MAINDB,
17 "help" => \$HELP);
18
19
20if ((!($USER)) || (!($PASS)) || (!($TARGET)) || (!($MAINDB))) {
21 &help;
22 exit;
23}
24
25if (!($PORT)) {
26 $PORT = "3306";
27}
28
29if (!($CONFIG)) {
30 $CONFIG="kodisql_config";
31}
32
33$mysqlhost="$HOST";
34$mysqluser="$USER";
35$mysqlpass="$PASS";
36$mysqlport="$PORT";
37
38
39# Temporary settings.. will be command line options later
40$target = "$TARGET";
41# The master kodi database to replicate from (whatever is declared in your advancedsettings.xml).
42$maindb = "$MAINDB";
43
44
45$globaltable = "globalfiles";
46@file_exceptions = ("playCount","lastPlayed");
47
48# The database where we keep the list of clients and other info we may need on upgrades.
49$configdb = "$CONFIG";
50
51
52################################################
53# Connect to the database.
54$dbh = DBI->connect("DBI:mysql:host=$mysqlhost", "$mysqluser", "$mysqlpass", {'RaiseError' => 1});
55@dbs = $dbh->func('_ListDBs');
56foreach (@dbs) {
57 if ($_ =~ /${maindb}\d+/) {
58 push @kodidbs, $_;
59 }
60}
61my $highdb;
62my $highrev;
63foreach (@kodidbs) {
64 $db = $_;
65 $rev = $_;;
66 $highrev = $highdb;
67 ($rev) = $rev =~ m/${maindb}(\d+)/;
68 ($highrev) = $highdb =~ m/${maindb}(\d+)/;
69 if (($rev > $highrev) || (!$highrev)) {
70 $highrev = $rev;
71 $highdb = "${maindb}$highrev";
72 }
73 if ((($oldrev < $ref) && ($rev < $highref)) || (!($oldrev))) {
74 my $exists = &checkifexists("$target$oldrev");
75 if ($exists) {
76 $oldrev = $rev;
77 }
78 }
79}
80
81sub help {
82 print "MySQL-Kodi Dynamic database replicator\n";
83 print "wickedsun 2016\n";
84 print " --user,-u\t\tSet MySQL USERNAME\t(required)\n";
85 print " --pass,-p\t\tSet MySQL PASSWORD\t(required)\n";
86 print " --port\t\tSet MySQL PORT\t\t(Default: 3306)\n";
87 print " --target\t\tSet TARGET Database\t(required)\n";
88 print " --main\t\tSet MAIN Database\t(required)\n";
89 print " --config\t\tSet CONFIG Database\t(Default: kodisql_config)\n";
90 print " --update\t\tUpdate oldest slave DB to current schema (not yet implemented!)\n";
91 print " --help\t\tThis help message\n";
92}
93
94# check if the target db exists and create it if it doesn't
95sub checkdb {
96 my $db = @_[0];
97 my $exists = &checkifexists($db);
98 if (!($exists)) {
99 $dbh->do("CREATE DATABASE $db");
100 }
101}
102
103# check if globalfiles exists
104sub checkglobalfiles {
105 my ($db,$target) = @_;
106 my @statement;
107 my $exists = &checkifexists($db,$globaltable);
108 my $mainindex
109 if (!($exists)) {
110 $dbh->do("USE $db");
111 # $dbh->do("RENAME TABLE `files` TO `$globaltable`");
112 # No longer rename files, that was a bad idea (breaks kodi on new DB version)
113 # however, we can probably get away with creating a globalfile view and adding columns to that as long as we link the index of files to it.
114
115
116 # figure out the index of files
117 $dbh->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA = `$db` and TABLE_NAME = `files`")
118 $dbh->execute;
119 while (my $ref = $sth->fetchrow_hashref()) {
120 $mainindex = $ref->{'COLUMN_NAME'};
121 }
122 $dbh->do("CREATE VIEW `$db`.`$globaltable` AS SELECT $mainindex FROM `$db`.`files`");
123 }
124}
125
126# check if columns for target exist, and create if they do not.
127sub checkslavecol {
128 my ($db,$target) = @_;
129 $exists = &checkifexists("${target}$highrev","bookmark");
130 if (!($exists)) {
131 # could probably make this table dynamic instead of this.. basically just recreate the table; updates might be tricky like comparing the new and old tables of the main db
132 # and figuring out what was added after copying -- needs investigating, and need to see if this table ever changes and if so, how often.
133 $dbh->do("CREATE TABLE `${target}$highrev`.`bookmark` ( idBookmark integer primary key AUTO_INCREMENT, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer)");
134 $dbh->do("USE ${target}$highrev");
135 $dbh->do("CREATE INDEX ix_bookmark ON bookmark (idFile, type);");
136 }
137}
138
139sub createviews {
140 ($db,$target,$rev) = @_;
141 my $exists = &checkifexists($db,$globaltable,"${target}_playCount");
142 if (!($exists)) {
143 $dbh->do("ALTER TABLE `$db`.`files` ADD `${target}_playCount` INT( 11 ) NULL DEFAULT NULL , ADD `${target}_lastPlayed` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
144 $dbh->do("USE ${target}$highrev");
145 }
146 $sth = $dbh->prepare("SHOW COLUMNS FROM $db.$globaltable");
147 $sth->execute;
148 my $statement;
149 my $header;
150 my $tail;
151 my $exists;
152 while (my $ref = $sth->fetchrow_hashref()) {
153 my $field = $ref->{'Field'};
154 foreach (@file_exceptions) {
155 $exc = $_;
156 if ($field eq $exc) {
157 # CREATE VIEW `User1Videos93`.`files` AS idFile, idPath, strFilename, playCount1 AS playCount, lastPlayed1 AS lastPlayed, dateAdded FROM `MyVideos93`.`globalfiles`;
158 push @statement, "${target}_$exc AS $exc";
159 push @statement_main, "${maindb}_$exc AS $exc";
160 print "Pushed ${maindb}_$exc AS $exc\n";
161 }
162 }
163 if ((!($field ~~ @statement)) && (!($field ~~ @file_exceptions))) {
164 my $skip = 0;
165 foreach (@file_exceptions) {
166 my $except = $_;
167 if ($field =~ m/.*_$except/) { $skip = 1;}
168 }
169 if ($skip == 0) {
170 push @statement, "$field";
171 push @statement_main, "$field";
172 }
173 }
174 }
175 foreach (@statement) {
176 my $part = $_;
177 if ($statement) {
178 $statement = "$statement, $part";
179 $statement_main = "$statement_main, $part";
180 } else {
181 $statement = "$part";
182 $statement_main = "$part";
183 }
184 }
185 $header = "CREATE VIEW `${target}${rev}`.`files` AS SELECT";
186 $header_main = "CREATE VIEW `$highdb`.`files` AS SELECT";
187 $footer = "FROM `$highdb`.`$globaltable`";
188 $footer_main = "FROM `$highdb`.`$globaltable`";
189 $exists = &checkifexists("${target}${rev}","files");
190 if (!($exists)) {
191 print "creating VIEW: $header $statement $footer\n";
192 $dbh->do("$header $statement $footer");
193 }
194 $exists = &checkifexists("$highdb","files");
195 if (!($exists)) {
196 print "creating VIEW_MAIN: $header_main $statement_main $footer_main\n";
197 $dbh->do("$header_main $statement_main $footer_main");
198 }
199 $sth = $dbh->prepare("SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = '$db' GROUP BY TABLE_NAME");
200 $sth->execute;
201 while (my $ref = $sth->fetchrow_hashref()) {
202 # CREATE VIEW `User1Videos93`.`actor_link` AS SELECT * FROM `MyVideos93`.`actor_link`;
203 if (!(($ref->{'TABLE_NAME'} eq "files") || ($ref->{'TABLE_NAME'} eq "bookmark") || ($ref->{'TABLE_NAME'} eq "$globaltable"))) {
204 push @tables, $ref->{'TABLE_NAME'};
205 }
206 }
207 foreach (@tables) {
208 $table = $_;
209 $exists = &checkifexists("${target}${rev}","$table");
210 if (!($exists)) {
211 $dbh->do("CREATE VIEW `${target}${rev}`.`$table` AS SELECT * FROM `$db`.`$table`");
212 }
213 }
214}
215
216sub createviewhash {
217 my ($highdb,$target,$highrev) = @_;
218 my @views_order;
219 # get a list of the views in the video DB from the highest revision
220 $sth = $dbh->prepare("SHOW FULL TABLES IN $highdb WHERE TABLE_TYPE LIKE 'VIEW'");
221 $sth->execute;
222 $dbh->do("USE ${target}$highrev");
223 while (my $ref = $sth->fetchrow_hashref()) {
224 my $view = $ref->{"Tables_in_$highdb"};
225 push @views, $view;
226 }
227 foreach (@views) {
228 my $view = $_;
229 print "Fetching create info for $_\n";
230 $sth = $dbh->prepare("SHOW CREATE VIEW `$highdb`.`$view`");
231 $sth->execute;
232 my $statement;
233 while (my $ref = $sth->fetchrow_hashref()) {
234 $statement = $ref->{'Create View'};
235 }
236 # change all the dbs to the target.
237 ($statement = $statement) =~ s/$highdb/${target}$highrev/g;
238 # remove the security statements from the CREATE.
239 ($statement = $statement) =~ s/^CREATE .* VIEW `${target}$highrev`.`$view`/CREATE VIEW `${target}$highrev`.`$view`/g;
240 # whenever we see files, make sure we use globalfiles from the master database instead
241 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`globalfiles`/`$highdb`.`globalfiles`/g;
242 foreach (@file_exceptions) {
243 my $exc = $_;
244 # make sure we change the exceptions (playCount, lastPlayed for now) changed to the target ones.
245 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`files`.`$exc`/`$highdb`.`globalfiles`.`${target}_$exc`/;
246 }
247 $view_hash{$view}{'statement'} = "$statement";
248 foreach (@views) {
249 my $testview = $_;
250 if ($statement =~ m/SELECT .*`$testview`/i) {
251 print "view $view requires $testview\n";
252 push @{$view_hash{$view}{'dep'}}, $testview;
253 }
254 }
255 }
256}
257
258sub recreatehighviews {
259 foreach (keys %view_hash) {
260 &checkdeps($view);
261 }
262}
263
264
265sub checkdeps {
266 my $exists;
267 print "view check: $_\n";
268 my ($view) = $_;
269 my $depview;
270 if ($view_hash{$view}{'dep'}) {
271 foreach (@{$view_hash{$view}{'dep'}}) {
272 $depview = $_;
273 if (!($view_hash{$depview}{'pushed'})) {
274 print "Need to check $depview, missing dep\n";
275 &checkdeps($depview);
276 }
277 }
278 if (!($view_hash{$view}{'pushed'}) == 1) {
279 $exists = &checkifexists("${target}$highrev","$view");
280 if (!($exists)) {
281 $dbh->do("$view_hash{$view}{'statement'}");
282 }
283 $view_hash{$depview}{'pushed'} = 1;
284 } else {
285 print "$view already pushed\n";
286 }
287 } else {
288 if (!($view_hash{$view}{'pushed'} == 1)) {
289 $exists = &checkifexists("${target}$highrev","$view");
290 if (!($exists)) {
291 $dbh->do("$view_hash{$view}{'statement'}");
292 }
293 $view_hash{$view}{'pushed'} = 1;
294 } else {
295 print "$view already pushed\n";
296 }
297 }
298}
299
300sub checkifexists {
301 my ($db,$table,$column) = @_;
302 if ($column) {
303 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = '$table' AND COLUMN_NAME = '$column'");
304 } elsif ($table) {
305 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = '$table'");
306 } else {
307 $sth = $dbh->prepare("SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$db'");
308 }
309 $sth->execute;
310 my $rows = $sth->rows;
311 $sth->finish;
312 if ($rows > 0) {
313 print "table/column exists -- $db $table $column \n";
314 return 1;
315 } else {
316 print "table/column does not exists -- $db $table $column\n";
317 return;
318 }
319}
320
321sub main {
322 &checkdb("$configdb");
323 $dbh->do("USE $configdb");
324 &checkdb("${target}${highrev}");
325 # make sure the target has the required columns
326 &checkslavecol("$highdb","$target");
327 &checkglobalfiles("$highdb","$target");
328 &createviews("$highdb","$target","$highrev");
329 &createviewhash("$highdb","$target","$highrev");
330 &recreatehighviews;
331}
332
333&main;