· 9 years ago · Nov 25, 2016, 10:10 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 print "VIEWS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! @views\n";
217 foreach (@tables) {
218 print "CHECKING TABLES!!!!!!!!! $_\n";
219 $table = $_;
220 $exists = &checkifexists("${target}${rev}","$table");
221 if (!($exists)) {
222 &checkdb("${target}${rev}");
223 if ($table ~~ @views) {
224 print "THIS IS A VIEW!!!!!!!!!!!!!! $table\n";
225 } else {
226 print "THIS IS NOT A VIEW!!!!!!!!!!!!!! $table\n";
227 $dbh->do("CREATE VIEW `${target}${rev}`.`$table` AS SELECT * FROM `$db`.`$table`");
228 }
229 }
230 }
231}
232
233sub checkslavecol {
234 my ($target,$highrev) = @_;
235 print "TARGET: $target HIGH REV: $highrev\n";
236 $exists = &checkifexists("${target}$highrev","bookmark");
237 if (!($exists)) {
238 # 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
239 # and figuring out what was added after copying -- needs investigating, and need to see if this table ever changes and if so, how often.
240 $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)");
241 $dbh->do("USE ${target}$highrev");
242 $dbh->do("CREATE INDEX ix_bookmark ON bookmark (idFile, type);");
243 }
244}
245
246sub createviewhash {
247 my ($highdb,$target,$highrev) = @_;
248 my @views_order;
249 # get a list of the views in the video DB from the highest revision
250 $sth = $dbh->prepare("SHOW FULL TABLES IN $highdb WHERE TABLE_TYPE LIKE 'VIEW'");
251 $sth->execute;
252 $dbh->do("USE ${target}$highrev");
253 while (my $ref = $sth->fetchrow_hashref()) {
254 my $view = $ref->{"Tables_in_$highdb"};
255 push @views, $view;
256 }
257 foreach (@views) {
258 my $view = $_;
259 print "Fetching create info for $_\n";
260 $sth = $dbh->prepare("SHOW CREATE VIEW `$highdb`.`$view`");
261 $dbh->do("USE ${target}$highrev");
262 $sth->execute;
263 my $statement;
264 while (my $ref = $sth->fetchrow_hashref()) {
265 $statement = $ref->{'Create View'};
266 }
267 # change all the dbs to the target.
268 ($statement = $statement) =~ s/$highdb/${target}$highrev/g;
269 ($statement = $statement) =~ s/${MAINDB}/${target}/g;
270 print "HIGHDB: $highdb TARGET: $target MAINDB: $MAINDB\n";
271 print "$statement\n";
272 # remove the security statements from the CREATE.
273 ($statement = $statement) =~ s/^CREATE .* VIEW `${target}$highrev`.`$view`/CREATE VIEW `${target}$highrev`.`$view`/g;
274 # whenever we see files, make sure we use globalfiles from the master database instead
275 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`globalfiles`/`$highdb`.`globalfiles`/g;
276 foreach (@file_exceptions) {
277 my $exc = $_;
278 # make sure we change the exceptions (playCount, lastPlayed for now) changed to the target ones.
279 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`files`.`$exc`/`$highdb`.`files`.`${target}_$exc`/;
280 }
281 $view_hash{$view}{'statement'} = "$statement";
282 foreach (@views) {
283 my $testview = $_;
284 if ($statement =~ m/SELECT .*`$testview`/i) {
285 print "view $view requires $testview\n";
286 push @{$view_hash{$view}{'dep'}}, $testview;
287 }
288 }
289 }
290}
291
292sub recreatehighviews {
293 my ($target,$highrev) = @_;
294 foreach (keys %view_hash) {
295 &checkdeps($_,$target,$highrev);
296 }
297}
298
299sub checkdeps {
300 my $exists;
301 my ($view,$target,$highrev) = @_;
302 print "view check: $view -- $target -- $highrev\n";
303 my $depview;
304 if ($view_hash{$view}{'dep'}) {
305 foreach (@{$view_hash{$view}{'dep'}}) {
306 $depview = $_;
307 if (!($view_hash{$depview}{'pushed'})) {
308 print "Need to check $depview, missing dep\n";
309 &checkdeps($depview,$target,$highrev);
310 }
311 }
312 if (!($view_hash{$view}{'pushed'}) == 1) {
313 $exists = &checkifexists("${target}$highrev","$view");
314 if (!($exists)) {
315 $dbh->do("$view_hash{$view}{'statement'}");
316 }
317 $view_hash{$depview}{'pushed'} = 1;
318 } else {
319 print "$view already pushed\n";
320 }
321 } else {
322 if (!($view_hash{$view}{'pushed'} == 1)) {
323 $exists = &checkifexists("${target}$highrev","$view");
324 if (!($exists)) {
325 $dbh->do("$view_hash{$view}{'statement'}");
326 }
327 $view_hash{$view}{'pushed'} = 1;
328 } else {
329 print "$view already pushed\n";
330 }
331 }
332}
333
334&main;