3 # $Id: db-upgrade.pl,v 1.1.1.1 2004/03/29 01:20:13 vjrj Exp $
5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
16 use vars qw/$dbh @reqlist $query/ ;
17 use vars qw/$sys_default_domain $sys_cvs_host
18 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
19 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
20 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
21 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
22 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
25 use vars qw/$pluginname/ ;
27 sub is_lesser ( $$ ) ;
28 sub is_greater ( $$ ) ;
30 sub parse_sql_file ( $ ) ;
32 require ("/usr/share/gforge/lib/include.pl") ; # Include a few predefined functions
33 require ("/usr/share/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
34 chomp($domain_name=`/usr/share/gforge/bin/forge_get_config web_host`);
36 debug "You'll see some debugging info during this installation." ;
37 debug "Do not worry unless told otherwise." ;
41 # debug "Connected to the database OK." ;
43 $pluginname = "globalsearch" ;
45 $dbh->{AutoCommit} = 0;
46 $dbh->{RaiseError} = 1;
48 my ($sth, @array, $version, $path, $target) ;
50 &create_metadata_table ("0") ;
52 $version = &get_db_version ;
54 if (is_lesser $version, $target) {
55 my @filelist = ( "/usr/share/gforge/plugins/$pluginname/db/$pluginname-init.sql" ) ;
57 foreach my $file (@filelist) {
58 debug "Processing $file" ;
59 @reqlist = @{ &parse_sql_file ($file) } ;
61 foreach my $s (@reqlist) {
64 $sth = $dbh->prepare ($query) ;
71 &update_db_version ($target) ;
76 $version = &get_db_version ;
78 if (is_lesser $version, $target) {
79 debug "Adding local data." ;
81 my $packed_ip = gethostbyname("$domain_name");
83 if (defined $packed_ip) {
84 $ip_address = inet_ntoa($packed_ip);
88 # "INSERT INTO plugin_".$pluginname."_sample_data (domain, ip_address) VALUES ('$domain_name', '$ip_address')",
91 foreach my $s (@reqlist) {
94 $sth = $dbh->prepare ($query) ;
100 &update_db_version ($target) ;
101 debug "Committing." ;
105 debug "It seems your database install/upgrade went well and smoothly. That's cool." ;
106 debug "Please enjoy using Debian GForge." ;
108 # There should be a commit at the end of every block above.
109 # If there is not, then it might be symptomatic of a problem.
110 # For safety, we roll back.
115 warn "Transaction aborted because $@" ;
116 debug "Transaction aborted because $@" ;
117 debug "Last SQL query was:\n$query\n(end of query)" ;
119 debug "Please report this bug on the Debian bug-tracking system." ;
120 debug "Please include the previous messages as well to help debugging." ;
121 debug "You should not worry too much about this," ;
122 debug "your DB is still in a consistent state and should be usable." ;
129 sub is_lesser ( $$ ) {
130 my $v1 = shift || 0 ;
131 my $v2 = shift || 0 ;
133 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
138 sub is_greater ( $$ ) {
139 my $v1 = shift || 0 ;
140 my $v2 = shift || 0 ;
142 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
150 print STDERR "$v\n" ;
153 sub create_metadata_table ( $ ) {
154 my $v = shift || "0" ;
155 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
156 # Do we have the metadata table?
158 $query = "SELECT count(*) FROM pg_class WHERE relname = '$tablename' and relkind = 'r'";
160 my $sth = $dbh->prepare ($query) ;
162 my @array = $sth->fetchrow_array () ;
165 # Let's create this table if we have it not
167 if ($array [0] == 0) {
168 debug "Creating $tablename table." ;
169 $query = "CREATE TABLE $tablename (key varchar primary key, value text not null)" ;
171 $sth = $dbh->prepare ($query) ;
176 $query = "SELECT count(*) FROM $tablename WHERE key = 'db-version'";
178 $sth = $dbh->prepare ($query) ;
180 @array = $sth->fetchrow_array () ;
183 # Empty table? We'll have to fill it up a bit
185 if ($array [0] == 0) {
186 debug "Inserting first data into $tablename table." ;
187 $query = "INSERT INTO $tablename (key, value) VALUES ('db-version', '$v')" ;
189 $sth = $dbh->prepare ($query) ;
195 sub update_db_version ( $ ) {
196 my $v = shift or die "Not enough arguments" ;
197 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
199 debug "Updating $tablename table." ;
200 $query = "UPDATE $tablename SET value = '$v' WHERE key = 'db-version'" ;
202 my $sth = $dbh->prepare ($query) ;
207 sub get_db_version () {
208 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
210 $query = "SELECT value FROM $tablename WHERE key = 'db-version'" ;
212 my $sth = $dbh->prepare ($query) ;
214 my @array = $sth->fetchrow_array () ;
217 my $version = $array [0] ;
222 sub drop_table_if_exists ( $ ) {
223 my $tname = shift or die "Not enough arguments" ;
224 $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
225 my $sth = $dbh->prepare ($query) ;
227 my @array = $sth->fetchrow_array () ;
230 if ($array [0] != 0) {
231 # debug "Dropping table $tname" ;
232 $query = "DROP TABLE $tname" ;
234 $sth = $dbh->prepare ($query) ;
240 sub drop_sequence_if_exists ( $ ) {
241 my $sname = shift or die "Not enough arguments" ;
242 $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
243 my $sth = $dbh->prepare ($query) ;
245 my @array = $sth->fetchrow_array () ;
248 if ($array [0] != 0) {
249 # debug "Dropping sequence $sname" ;
250 $query = "DROP SEQUENCE $sname" ;
252 $sth = $dbh->prepare ($query) ;
258 sub drop_index_if_exists ( $ ) {
259 my $iname = shift or die "Not enough arguments" ;
260 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
261 my $sth = $dbh->prepare ($query) ;
263 my @array = $sth->fetchrow_array () ;
266 if ($array [0] != 0) {
267 # debug "Dropping index $iname" ;
268 $query = "DROP INDEX $iname" ;
270 $sth = $dbh->prepare ($query) ;
276 sub drop_view_if_exists ( $ ) {
277 my $iname = shift or die "Not enough arguments" ;
278 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='v'" ;
279 my $sth = $dbh->prepare ($query) ;
281 my @array = $sth->fetchrow_array () ;
284 if ($array [0] != 0) {
285 # debug "Dropping view $iname" ;
286 $query = "DROP VIEW $iname" ;
288 $sth = $dbh->prepare ($query) ;
294 sub bump_sequence_to ( $$ ) {
295 my ($sth, @array, $seqname, $targetvalue) ;
298 $targetvalue = shift ;
301 $query = "select nextval ('$seqname')" ;
302 $sth = $dbh->prepare ($query) ;
304 @array = $sth->fetchrow_array () ;
306 } until $array[0] >= $targetvalue ;