· 9 years ago · Dec 15, 2016, 08:14 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 "data=s" => \$DATA,
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 (!($DATA)) {
30 $DATA="kodisql";
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 = "files";
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$datadb = "$DATA";
50
51################################################
52# Connect to the database.
53sub connectdb {
54 $dbh = DBI->connect("DBI:mysql:host=$mysqlhost", "$mysqluser", "$mysqlpass", {'RaiseError' => 1});
55 @dbs = $dbh->func('_ListDBs');
56 foreach (@dbs) {
57 if ($_ =~ /${maindb}\d+/) {
58 push @kodidbs, $_;
59 }
60 }
61 my $highdb;
62 my $highrev;
63 foreach (@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 return("$highrev","$highdb");
81}
82
83sub help {
84 print "MySQL-Kodi Dynamic database replicator\n";
85 print "wickedsun 2016\n";
86 print " --user,-u\t\tSet MySQL USERNAME\t(required)\n";
87 print " --pass,-p\t\tSet MySQL PASSWORD\t(required)\n";
88 print " --port\t\tSet MySQL PORT\t\t(Default: 3306)\n";
89 print " --target\t\tSet TARGET Database\t(required)\n";
90 print " --main\t\tSet MAIN Database\t(required)\n";
91 print " --config\t\tSet CONFIG Database\t(Default: kodisql_config)\n";
92 print " --updateall\t\tUpdate all slave DBs to current schema (not yet implemented!)\n";
93 print " --help\t\tThis help message\n";
94}
95
96# check if the target db exists and create it if it doesn't
97sub checkdb {
98 my $db = @_[0];
99 my $exists = &checkifexists($db);
100 if (!($exists)) {
101 $dbh->do("CREATE DATABASE $db");
102 }
103}
104
105
106sub main {
107 my ($highrev,$highdb) = &connectdb;
108 print "HIGH: $highdb\n";
109 &checkdb("$DATA");
110 &checkdb("$target$highrev");
111 &checkslavecol("$target","$highrev");
112 &createviews("$highdb","$target","$highrev");
113 my %view;
114 &createviewhash("$highdb","$target","$highrev");
115 &recreatehighviews("$target","$highrev");
116}
117
118sub checkifexists {
119 my ($db,$table,$column) = @_;
120 if ($column) {
121 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = '$table' AND COLUMN_NAME = '$column'");
122 } elsif ($table) {
123 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = '$table'");
124 } else {
125 $sth = $dbh->prepare("SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$db'");
126 }
127 $sth->execute;
128 my $rows = $sth->rows;
129 $sth->finish;
130 if ($rows > 0) {
131 print "table/column exists -- $db $table $column \n";
132 return 1;
133 } else {
134 print "table/column does not exists -- $db $table $column\n";
135 return;
136 }
137}
138
139sub createviews {
140 ($db,$target,$rev) = @_;
141 $sth = $dbh->prepare("SHOW FULL TABLES IN $db WHERE TABLE_TYPE LIKE 'VIEW'");
142 $sth->execute;
143 my @views;
144 while (my $ref = $sth->fetchrow_hashref()) {
145 my $view = $ref->{"Tables_in_$db"};
146 push @views, $view;
147 }
148 $dbh->do("USE $db");
149 my $exists = &checkifexists($db,"files","${target}_playCount");
150 if (!($exists)) {
151 $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");
152 }
153 print "$db\n";
154 $dbh->do("USE $db");
155 $sth = $dbh->prepare("SHOW COLUMNS FROM $globaltable");
156 $sth->execute;
157 my $statement;
158 my $header;
159 my $tail;
160 my $exists;
161 while (my $ref = $sth->fetchrow_hashref()) {
162 my $field = $ref->{'Field'};
163 foreach (@file_exceptions) {
164 $exc = $_;
165 if ($field eq $exc) {
166 # CREATE VIEW `User1Videos93`.`files` AS idFile, idPath, strFilename, playCount1 AS playCount, lastPlayed1 AS lastPlayed, dateAdded FROM `MyVideos93`.`globalfiles`;
167 push @statement, "files.${target}_$exc AS $exc";
168 #push @statement_main, "${maindb}_$exc AS $exc";
169 print "Pushed ${maindb}_$exc AS $exc\n";
170 }
171 }
172 if ((!($field ~~ @statement)) && (!($field ~~ @file_exceptions))) {
173 my $skip = 0;
174 foreach (@file_exceptions) {
175 my $except = $_;
176 if ($field =~ m/.*_$except/) { $skip = 1;}
177 }
178 if ($skip == 0) {
179 push @statement, "files.${field}";
180 push @statement_main, "$field";
181 }
182 }
183 }
184 foreach (@statement) {
185 my $part = $_;
186 if ($statement) {
187 $statement = "$statement, $part";
188 $statement_main = "$statement_main, $part";
189 } else {
190 $statement = "$part";
191 $statement_main = "$part";
192 }
193 }
194 $header = "CREATE VIEW `${target}${rev}`.`files` AS SELECT";
195 $header_main = "CREATE VIEW `$db`.`files` AS SELECT";
196 $footer = "FROM `$db`.`$globaltable`";
197 $footer_main = "FROM `$db`.`$globaltable`";
198 $exists = &checkifexists("${target}${rev}","files");
199 if (!($exists)) {
200 print "creating VIEW: $header $statement $footer\n";
201 $dbh->do("$header $statement $footer");
202 }
203 $exists = &checkifexists("$db","files");
204 if (!($exists)) {
205 print "creating VIEW_MAIN: $header_main $statement_main $footer_main\n";
206 $dbh->do("$header_main $statement_main $footer_main");
207 }
208 $sth = $dbh->prepare("SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = '$db' GROUP BY TABLE_NAME");
209 $sth->execute;
210 while (my $ref = $sth->fetchrow_hashref()) {
211 # CREATE VIEW `User1Videos93`.`actor_link` AS SELECT * FROM `MyVideos93`.`actor_link`;
212 if (!(($ref->{'TABLE_NAME'} eq "files") || ($ref->{'TABLE_NAME'} eq "bookmark"))) {
213 push @tables, $ref->{'TABLE_NAME'};
214 }
215 }
216 foreach (@tables) {
217 $table = $_;
218 $exists = &checkifexists("${target}${rev}","$table");
219 if (!($exists)) {
220 &checkdb("${target}${rev}");
221 if (!($table ~~ @views)) {
222 $dbh->do("CREATE VIEW `${target}${rev}`.`$table` AS SELECT * FROM `$db`.`$table`");
223 }
224 }
225 }
226}
227
228sub checkslavecol {
229 my ($target,$highrev) = @_;
230 print "TARGET: $target HIGH REV: $highrev\n";
231 $exists = &checkifexists("${target}$highrev","bookmark");
232 if (!($exists)) {
233 # 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
234 # and figuring out what was added after copying -- needs investigating, and need to see if this table ever changes and if so, how often.
235 $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)");
236 $dbh->do("USE ${target}$highrev");
237 $dbh->do("CREATE INDEX ix_bookmark ON bookmark (idFile, type);");
238 }
239}
240
241sub createviewhash {
242 my ($highdb,$target,$highrev) = @_;
243 my @views_order;
244 # get a list of the views in the video DB from the highest revision
245 $sth = $dbh->prepare("SHOW FULL TABLES IN $highdb WHERE TABLE_TYPE LIKE 'VIEW'");
246 $sth->execute;
247 $dbh->do("USE ${target}$highrev");
248 while (my $ref = $sth->fetchrow_hashref()) {
249 my $view = $ref->{"Tables_in_$highdb"};
250 push @views, $view;
251 }
252 foreach (@views) {
253 my $view = $_;
254 print "Fetching create info for $_\n";
255 $sth = $dbh->prepare("SHOW CREATE VIEW `$highdb`.`$view`");
256 $dbh->do("USE ${target}$highrev");
257 $sth->execute;
258 my $statement;
259 while (my $ref = $sth->fetchrow_hashref()) {
260 $statement = $ref->{'Create View'};
261 }
262 # change all the dbs to the target.
263 ($statement = $statement) =~ s/$highdb/${target}$highrev/g;
264 ($statement = $statement) =~ s/${MAINDB}/${target}/g;
265 print "HIGHDB: $highdb TARGET: $target MAINDB: $MAINDB\n";
266 print "$statement\n";
267 # remove the security statements from the CREATE.
268 ($statement = $statement) =~ s/^CREATE .* VIEW `${target}$highrev`.`$view`/CREATE VIEW `${target}$highrev`.`$view`/g;
269 # whenever we see files, make sure we use globalfiles from the master database instead
270 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`globalfiles`/`$highdb`.`globalfiles`/g;
271 foreach (@file_exceptions) {
272 my $exc = $_;
273 # make sure we change the exceptions (playCount, lastPlayed for now) changed to the target ones.
274 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`files`.`$exc`/`$highdb`.`files`.`${target}_$exc`/;
275 }
276 $view_hash{$view}{'statement'} = "$statement";
277 foreach (@views) {
278 my $testview = $_;
279 if ($statement =~ m/SELECT .*`$testview`/i) {
280 print "view $view requires $testview\n";
281 push @{$view_hash{$view}{'dep'}}, $testview;
282 }
283 }
284 }
285}
286
287sub recreatehighviews {
288 my ($target,$highrev) = @_;
289 foreach (keys %view_hash) {
290 &checkdeps($_,$target,$highrev);
291 }
292}
293
294sub checkdeps {
295 my $exists;
296 my ($view,$target,$highrev) = @_;
297 print "view check: $view -- $target -- $highrev\n";
298 my $depview;
299 if ($view_hash{$view}{'dep'}) {
300 foreach (@{$view_hash{$view}{'dep'}}) {
301 $depview = $_;
302 if (!($view_hash{$depview}{'pushed'})) {
303 print "Need to check $depview, missing dep\n";
304 &checkdeps($depview,$target,$highrev);
305 }
306 }
307 if (!($view_hash{$view}{'pushed'}) == 1) {
308 $exists = &checkifexists("${target}$highrev","$view");
309 if (!($exists)) {
310 $dbh->do("$view_hash{$view}{'statement'}");
311 }
312 $view_hash{$depview}{'pushed'} = 1;
313 } else {
314 print "$view already pushed\n";
315 }
316 } else {
317 if (!($view_hash{$view}{'pushed'} == 1)) {
318 $exists = &checkifexists("${target}$highrev","$view");
319 if (!($exists)) {
320 $dbh->do("$view_hash{$view}{'statement'}");
321 }
322 $view_hash{$view}{'pushed'} = 1;
323 } else {
324 print "$view already pushed\n";
325 }
326 }
327}
328
329&main;