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