Yoshihisa Fukuhara
higef****@users*****
2006年 3月 23日 (木) 12:46:15 JST
Index: affelio/apps/bb/upgrade/index.cgi
diff -u /dev/null affelio/apps/bb/upgrade/index.cgi:1.1
--- /dev/null Thu Mar 23 12:46:15 2006
+++ affelio/apps/bb/upgrade/index.cgi Thu Mar 23 12:46:14 2006
@@ -0,0 +1,431 @@
+#!/usr/bin/perl
+
+use strict;
+
+#use CGI qw(-unique_headers);
+use strict;
+use CGI;
+use Cwd;
+use DBI;
+use lib("../../../extlib");
+use lib("../../../lib");
+#use HTML::Template;
+use AffelioApp;
+use Affelio::Managing::ProfileManager;
+use Affelio::Managing::ApplicationManager;
+#use Affelio;
+use Affelio::misc::Debug qw(debug_print);
+use Error qw(:try);
+
+our $cgi = new CGI();
+
+#########################################################################
+#1st screen
+#########################################################################
+if($cgi->url_param("mode") ne "go"){
+ print "Content-type: text/html; charset=UTF-8\n";
+ print "Pragma: no-cache", "\n\n";
+ print <<EOM;
+<HTML>
+<h1>Upgrade Script from bb ver0.x - ver1.x to 2.0</h1>
+<P>
+<H2>1. 作æ¥å‰ã«ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ãƒãƒƒã‚¯ã‚¢ãƒƒãƒ—ã‚’ãŠå–ã‚Šãã ã•ã„。</H2>
+<P>
+<H2>2. Affelio BBã®ã‚¢ãƒƒãƒ—グレードをãŠã“ãªã„ã¾ã™ã€‚</H2>
+下ã®ãƒœã‚¿ãƒ³ã‚’クリックã™ã‚‹ã¨ã‚¢ãƒƒãƒ—グレードを実行ã—ã¾ã™ã€‚
+<P>
+<FORM ACTION="./index.cgi?mode=go" method=POST>
+<INPUT TYPE="submit" VALUE="Upgrade">
+</FORM>
+</HTML>
+EOM
+exit(1);
+
+
+#########################################################################
+#Do upgrade
+#########################################################################
+}else{
+#Initialize AFAP
+ print "Content-type: text/html; charset=UTF-8\n";
+ print "Pragma: no-cache", "\n\n";
+
+ ################################
+ #Start Affelio
+ ################################
+ our $afap;
+ try{
+ chdir '..';
+ my $dir = getcwd();
+ chdir 'upgrade';
+ $afap = new AffelioApp(ConfigDir => $dir, cgi => $cgi);
+ }catch Error with{
+ my $e = shift;
+ print("AffelioApp load error.\n" . $e->stacktrace);
+ exit(1);
+ };
+ my $dbh = $afap->get_userdata_dbh();
+
+ ################################
+ #Create permission setting table
+ ################################
+ my $permission_table = "AFuser_bb_$afap->{install_name}_permission";
+
+ {
+ my $query = "SELECT * from $permission_table";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the app table does not exist.<BR>";
+ print "So, We will make it<BR>";
+ my $query = "CREATE TABLE $permission_table (key TEXT, super BOOLEAN, user BOOLEAN)";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in creating the table<BR><BR>' . $@;
+ exit(1);
+ }
+ #Insert initial data.
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('category_edit', 0,0)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('category_delete', 0,0)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('category_status', 0,0)");
+
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('forum_edit', 1,0)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('forum_delete', 0,0)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('forum_status', 0,0)");
+
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('topic_edit', 1,1)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('topic_delete', 1,0)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('topic_status', 1,0)");
+
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('comment_edit', 1,1)");
+ $dbh->do("INSERT INTO $permission_table (key, super, user) VALUES ('comment_delete', 1,1)");
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ ################################
+ #Rename tables
+ ################################
+ my $category_tb = "AFuser_bb_$afap->{install_name}_category";
+ my $forum_tb = "AFuser_bb_$afap->{install_name}_forum";
+ my $topic_tb = "AFuser_bb_$afap->{install_name}_topic";
+ my $pref_tb = "AFuser_bb_$afap->{install_name}_pref";
+ my $spam_tb = "AFuser_bb_$afap->{install_name}_spam";
+ {
+ my $query = "SELECT * from $category_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the new category table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE $afap->{install_name}_category RENAME TO $category_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ {
+ my $query = "SELECT * from $forum_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the new forum table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE $afap->{install_name}_forum RENAME TO $forum_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ {
+ my $query = "SELECT * from $topic_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the new topic table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE $afap->{install_name}_topic RENAME TO $topic_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ {
+ my $query = "SELECT * from $pref_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the new pref table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE $afap->{install_name}_pref RENAME TO $pref_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ {
+ my $query = "SELECT * from $spam_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that the new spam table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE $afap->{install_name}_spam RENAME TO $spam_tb";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+
+ ################################
+ #Rename reply tables
+ ################################
+ my $reply_tb = "AFuser_bb_$afap->{install_name}_reply_";
+ my @topics = getall($dbh, "SELECT topic_id FROM $topic_tb");
+ foreach(@topics){
+ {
+ my $query = "SELECT * from ".$reply_tb.$_->{topic_id};
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that AFuser_bb_".$afap->{install_name}."_reply_".$_->{topic_id}." table does not exist.<BR>";
+ print "So, We will rename the old table.<BR>";
+ my $query = "ALTER TABLE reply_".$_->{topic_id}." RENAME TO ".$reply_tb.$_->{topic_id};
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print 'Error occured in renaming the table<BR><BR>' . $@;
+ exit(1);
+ }
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print 'We confirmed that the app table already exists.<BR>';
+ print 'So, We will do nothing.<BR>';
+ }
+ }#block
+ }
+
+
+ ################################
+ #Update tables
+ ################################
+ print "We will update some CORE_apps infomation...<BR>";
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "owner_index", "admin.cgi", "owner.cgi", "TEXT","app_name","bb");
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "app_version", "2.0", "0.8", "TEXT");
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "app_version", "2.0", "0.9", "TEXT");
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "app_version", "2.0", "1.0", "TEXT");
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "app_version", "2.0", "1.0.1", "TEXT");
+ update_table($afap->{af}->getDB, "AFuser_CORE_apps", "app_version", "2.0", "1.1", "TEXT");
+
+ print "Done.<BR>";
+
+ print '<h2>OK. Successfuly done.</H2><P><B>Delete this CGI immediately!!</B>';
+ exit(1);
+}
+
+
+
+##############################################
+# get all columns
+##############################################
+sub getall {
+ my ($dbh, $query) = @_;
+ my $sth = $dbh->prepare($query);
+ $sth->execute;
+
+ my @ret;
+ while(my $row = $sth->fetchrow_hashref) {
+ push @ret, $row;
+ }
+ $sth->finish;
+
+ return @ret;
+}
+
+sub add_column{
+ my $dbh= shift;
+ my $table = shift;
+ my $col = shift;
+ my $type = shift;
+ my $def = shift;
+
+ my $query = "SELECT $col from $table";
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ if($@){
+ print "We confirmed that table [$table] needs column [$col]<BR>";
+ my $query2 = "ALTER TABLE $table ADD COLUMN $col $type";
+ my $sth;
+ eval{
+ $dbh->do($query2);
+ };
+ if($@){
+ print 'Error occured in modifying the table<BR><BR>' . $@;
+ exit(1);
+ }
+ print "Successfully added [$col].<BR>";
+
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print "We confirmed that table [$table] already has [$col]<BR>";
+ print 'So, We will do nothing.<BR>';
+ }
+}
+
+
+sub update_table{
+ my $dbh= shift;
+ my $table = shift;
+ my $col = shift;
+ my $value = shift;
+ my $current = shift;
+ my $type = shift;
+ my $col2 = shift;
+ my $current2 = shift;
+
+ my $query;
+ if ($type eq "INTEGER"){
+ $query = "SELECT * from $table WHERE $col = $current";
+ }else{
+ $query = "SELECT * from $table WHERE $col = '$current'";
+ }
+ if ($col2 ne "" && $current2 ne""){
+ $query.= " AND $col2 = '$current2'";
+ }
+# print $query;
+ my $sth;
+ eval{
+ $sth = $dbh->prepare($query);
+ $sth->execute;
+ };
+ my $row=0;
+ while(my @tmp= $sth->fetchrow_array){
+ $row++;
+ }
+ $sth->finish;
+
+ if($row>0){
+ my $query2;
+ print "We confirmed that table [$table] needs update [$col]<BR>";
+ if ($type eq "INTEGER"){
+ $query2 = "UPDATE $table SET $col = $value WHERE $col = $current";
+ }else{
+ $query2 = "UPDATE $table SET $col = '$value' WHERE $col = '$current'";
+ }
+ if ($col2 ne "" && $current2 ne""){
+ $query2.= " AND $col2 = '$current2'";
+ }
+# print $query2;
+ my $sth;
+ eval{
+ $dbh->do($query2);
+ };
+ if($@){
+ print 'Error occured in modifying the table<BR><BR>' . $@;
+ exit(1);
+ }
+ print "Successfully update [$col].<BR>";
+
+ }else{
+ #Application table already exists.
+ #So, do nothing.
+ print "We confirmed that table [$table] was updated<BR>";
+ print 'So, We will do nothing.<BR>';
+ }
+}