· 10 years ago · Jan 20, 2016, 03:39 AM
1#/bin/perl
2use DBI;
3
4$mysqlhost="ip or hostname here";
5$mysqluser="user here";
6$mysqlpass="password here";
7
8# Connect to the database.
9my $dbh = DBI->connect("DBI:mysql:host=$mysqlhost", "$mysqluser", "$mysqlpass", {'RaiseError' => 1});
10$target = "Guests";
11$dbh->do("USE Main_Videos91;");
12@dbs = $dbh->func('_ListDBs');
13@file_exceptions = ("playCount","lastPlayed");
14foreach (@dbs) {
15 if ($_ =~ /Main_Videos\d+/) {
16 push @kodidbs, $_;
17
18 }
19}
20my $highdb;
21my $highrev;
22foreach (@kodidbs) {
23 my $db = $_;
24 my $rev = $_;
25 $highrev = $highdb;
26 ($rev) = $db =~ m/Main_Videos(\d+)/;
27 #($highrev) = $highdb =~ m/Main_Videos(\d+)/;
28 $highrev = "91";
29 if (($rev > $highrev) || (!$highdb)) {
30 $highdb = "Main_Videos91";
31 }
32}
33
34# check if the target db exists and create it if it doesn't
35sub checkdb {
36 my $db = @_[0];
37 $sth = $dbh->prepare("SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$db';");
38 $sth->execute;
39 $rows = $sth->rows;
40 $sth->finish;
41 if ($rows == 0) {
42 print "$db does not exist\n";
43 $dbh->do("CREATE DATABASE $db;");
44 } else {
45 print "$db already exists\n";
46 }
47}
48
49# check if globalfiles exists
50sub checkglobalfiles {
51 my ($db,$target) = @_;
52 my @statement;
53 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = 'globalfiles';");
54 $sth->execute;
55 $rows = $sth->rows;
56 $sth->finish;
57 if ($rows == 0) {
58 $dbh->do("USE $db;");
59 $dbh->do("RENAME TABLE `files` TO `globalfiles`;");
60 # unhappy with this.. not very dynamic :(
61 } else {
62 print "Table $highdb.globalfiles already exists\n";
63 }
64}
65
66# check if columns for target exist, and create if they do not.
67sub checkslavecol {
68 my ($db,$target) = @_;
69 $sth = $dbh->prepare("SELECT * FROM information_schema.columns WHERE table_schema = '$db' AND table_name = 'globalfiles' AND column_name = '${target}_playCount';");
70 $sth->execute;
71 $rows = $sth->rows;
72 $sth->finish;
73 if ($rows == 0) {
74 print "$db.$target does not exist\n";
75 $dbh->do("USE ${target}_Videos$highrev;");
76 $dbh->do("CREATE TABLE bookmark ( idBookmark integer primary key AUTO_INCREMENT, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);");
77 $dbh->do("CREATE INDEX ix_bookmark ON bookmark (idFile, type);");
78 } else {
79 print "$db.files ${target}_playCount and ${target}_lastPlayed already exists\n";
80 }
81
82}
83
84#sub createviews {
85#}
86
87sub createviews {
88 ($db,$target,$rev) = @_;
89 $dbh->do("ALTER TABLE `$db`.`globalfiles` ADD `${target}_playCount` INT( 11 ) NULL DEFAULT NULL , ADD `${target}_lastPlayed` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;");
90 $sth = $dbh->prepare("SHOW COLUMNS FROM $db.globalfiles");
91 $sth->execute;
92 my $statement;
93 my $header;
94 my $tail;
95 while (my $ref = $sth->fetchrow_hashref()) {
96 my $field = $ref->{'Field'};
97 foreach (@file_exceptions) {
98 $exc = $_;
99 if ($field eq $exc) {
100 # CREATE VIEW `User1Videos93`.`files` AS idFile, idPath, strFilename, playCount1 AS playCount, lastPlayed1 AS lastPlayed, dateAdded FROM `MyVideos93`.`globalfiles`;
101 push @statement, "${target}_$exc AS $exc";
102 }
103 }
104 if ((!($field ~~ @statement)) && (!($field ~~ @file_exceptions))) {
105 my $skip = 0;
106 foreach (@file_exceptions) {
107 my $except = $_;
108 if ($field =~ m/.*_$except/) { $skip = 1;}
109 }
110 if ($skip == 0) { push @statement, "$field"; }
111 }
112 }
113 foreach (@statement) {
114 my $part = $_;
115 print "PART: $part\n";
116 if ($statement) {
117 $statement = "$statement, $part";
118 } else {
119 $statement = "$part"
120 }
121 }
122 $header = "CREATE VIEW `${target}_Videos${rev}`.`files` AS SELECT";
123 $footer = "FROM `$highdb`.`globalfiles`";
124 print "creating VIEW: $header $statement $footer\n";
125 $dbh->do("$header $statement $footer");
126 $sth = $dbh->prepare("SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = '$db' GROUP BY TABLE_NAME;");
127 $sth->execute;
128 while (my $ref = $sth->fetchrow_hashref()) {
129 # CREATE VIEW `User1Videos93`.`actor_link` AS SELECT * FROM `MyVideos93`.`actor_link`;
130 if (!(($ref->{'TABLE_NAME'} eq "files") || ($ref->{'TABLE_NAME'} eq "bookmark") || ($ref->{'TABLE_NAME'} eq "globalfiles"))) {
131 $dbh->do("CREATE VIEW `${target}_Videos${rev}`.`$ref->{'TABLE_NAME'}` AS SELECT * FROM `$db`.`$ref->{'TABLE_NAME'}`");
132 }
133 }
134}
135
136sub createviewhash {
137 my ($highdb,$target,$highrev) = @_;
138 my @views_order;
139 # get a list of the views in the video DB from the highest revision
140 $sth = $dbh->prepare("SHOW FULL TABLES IN $highdb WHERE TABLE_TYPE LIKE 'VIEW'");
141 $sth->execute;
142 $dbh->do("USE ${target}_Videos$highrev;");
143 while (my $ref = $sth->fetchrow_hashref()) {
144 my $view = $ref->{"Tables_in_$highdb"};
145 push @views, $view;
146 }
147 foreach (@views) {
148 my $view = $_;
149 print "Fetching create info for $_\n";
150 $sth = $dbh->prepare("SHOW CREATE VIEW `$highdb`.`$view`");
151 $sth->execute;
152 my $statement;
153 while (my $ref = $sth->fetchrow_hashref()) {
154 $statement = $ref->{'Create View'};
155 }
156 # change all the dbs to the target.
157 ($statement = $statement) =~ s/$highdb/${target}_Videos$highrev/g;
158 # remove the security statements from the CREATE.
159 ($statement = $statement) =~ s/^CREATE .* VIEW `${target}_Videos$highrev`.`$view`/CREATE VIEW `${target}_Videos$highrev`.`$view`/g;
160 # whenever we see files, make sure we use globalfiles from the master database instead
161 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`globalfiles`/`$highdb`.`globalfiles`/g;
162 foreach (@file_exceptions) {
163 my $exc = $_;
164 # make sure we change the exceptions (playCount, lastPlayed for now) changed to the target ones.
165 #($statement = $statement) =~ s/`${target}_Videos$highrev`.`files`.`$exc`/`$highdb`.`globalfiles`.`${target}_$exc`/;
166 }
167 print "$statement\n";
168 $view_hash{$view}{'statement'} = "$statement";
169 foreach (@views) {
170 my $testview = $_;
171 if ($statement =~ m/SELECT .*`$testview`/i) {
172 print "view $view requires $testview\n";
173 push @{$view_hash{$view}{'dep'}}, $testview;
174 }
175 }
176 }
177}
178
179sub recreatehighviews {
180 foreach (keys %view_hash) {
181 &checkdeps($view);
182 }
183}
184
185
186sub checkdeps {
187 print "view check: $_\n";
188 my ($view) = $_;
189 print "$view_hash{$view}{'statement'}\n";
190 my $depview;
191 if ($view_hash{$view}{'dep'}) {
192 foreach (@{$view_hash{$view}{'dep'}}) {
193 $depview = $_;
194 if (!($view_hash{$depview}{'pushed'})) {
195 print "Need to check $depview, missing dep\n";
196 &checkdeps($depview);
197 }
198 }
199 if (!($view_hash{$view}{'pushed'}) == 1) {
200 $dbh->do("$view_hash{$view}{'statement'}");
201 $view_hash{$depview}{'pushed'} = 1;
202
203 } else {
204 print "$view already pushed\n";
205 }
206 } else {
207 if (!($view_hash{$view}{'pushed'} == 1)) {
208 print "$view_hash{$view}{'statement'}\n";
209 $dbh->do("$view_hash{$view}{'statement'}");
210 $view_hash{$view}{'pushed'} = 1;
211 } else {
212 print "$view already pushed\n";
213 }
214 }
215}
216
217
218sub main {
219 &checkdb("${target}_Videos${highrev}");
220 # make sure the target has the required columns
221 &checkslavecol("$highdb","$target");
222 &checkglobalfiles("$highdb","$target");
223 &createviews("$highdb","$target","$highrev");
224 &createviewhash("$highdb","$target","$highrev");
225 &recreatehighviews;
226}
227
228&main;