3 # Debian-specific script to upgrade the database between releases
4 # Roland Mas <lolando@debian.org>
13 use vars qw/$dbh @reqlist $query/ ;
14 use vars qw/$sys_default_domain $sys_cvs_host
15 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
16 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
17 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
18 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
19 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
22 use vars qw/$pluginname/ ;
24 sub is_lesser ( $$ ) ;
25 sub is_greater ( $$ ) ;
27 sub parse_sql_file ( $ ) ;
29 require ("/usr/share/gforge/lib/include.pl") ; # Include a few predefined functions
30 require ("/usr/share/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
32 debug "You'll see some debugging info during this installation." ;
33 debug "Do not worry unless told otherwise." ;
37 # debug "Connected to the database OK." ;
39 $pluginname = "blocks" ;
41 $dbh->{AutoCommit} = 0;
42 $dbh->{RaiseError} = 1;
44 my ($sth, @array, $version, $path, $target) ;
46 &create_metadata_table ("0") ;
48 $version = &get_db_version ;
50 if (is_lesser $version, $target) {
51 my @filelist = ( "/usr/share/gforge/plugins/$pluginname/lib/$pluginname-init.sql" ) ;
53 foreach my $file (@filelist) {
54 debug "Processing $file" ;
55 @reqlist = @{ &parse_sql_file ($file) } ;
57 foreach my $s (@reqlist) {
60 $sth = $dbh->prepare ($query) ;
67 &update_db_version ($target) ;
72 $version = &get_db_version ;
74 if (is_lesser $version, $target) {
75 debug "Adding local data." ;
77 my $ip_address = qx/host $domain_name | awk '{print \}'/ ;
80 "INSERT INTO plugin_".$pluginname."_sample_data (domain, ip_address) VALUES ('$domain_name', '$ip_address')",
83 foreach my $s (@reqlist) {
86 $sth = $dbh->prepare ($query) ;
92 &update_db_version ($target) ;
97 debug "It seems your database install/upgrade went well and smoothly. That's cool." ;
98 debug "Please enjoy using Debian GForge." ;
100 # There should be a commit at the end of every block above.
101 # If there is not, then it might be symptomatic of a problem.
102 # For safety, we roll back.
107 warn "Transaction aborted because $@" ;
108 debug "Transaction aborted because $@" ;
109 debug "Last SQL query was:\n$query\n(end of query)" ;
111 debug "Please report this bug on the Debian bug-tracking system." ;
112 debug "Please include the previous messages as well to help debugging." ;
113 debug "You should not worry too much about this," ;
114 debug "your DB is still in a consistent state and should be usable." ;
121 sub is_lesser ( $$ ) {
122 my $v1 = shift || 0 ;
123 my $v2 = shift || 0 ;
125 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
130 sub is_greater ( $$ ) {
131 my $v1 = shift || 0 ;
132 my $v2 = shift || 0 ;
134 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
142 print STDERR "$v\n" ;
145 sub create_metadata_table ( $ ) {
146 my $v = shift || "0" ;
147 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
148 # Do we have the metadata table?
150 $query = "SELECT count(*) FROM pg_class WHERE relname = '$tablename' and relkind = 'r'";
152 my $sth = $dbh->prepare ($query) ;
154 my @array = $sth->fetchrow_array () ;
157 # Let's create this table if we have it not
159 if ($array [0] == 0) {
160 debug "Creating $tablename table." ;
161 $query = "CREATE TABLE $tablename (key varchar primary key, value text not null)" ;
163 $sth = $dbh->prepare ($query) ;
168 $query = "SELECT count(*) FROM $tablename WHERE key = 'db-version'";
170 $sth = $dbh->prepare ($query) ;
172 @array = $sth->fetchrow_array () ;
175 # Empty table? We'll have to fill it up a bit
177 if ($array [0] == 0) {
178 debug "Inserting first data into $tablename table." ;
179 $query = "INSERT INTO $tablename (key, value) VALUES ('db-version', '$v')" ;
181 $sth = $dbh->prepare ($query) ;
187 sub update_db_version ( $ ) {
188 my $v = shift or die "Not enough arguments" ;
189 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
191 debug "Updating $tablename table." ;
192 $query = "UPDATE $tablename SET value = '$v' WHERE key = 'db-version'" ;
194 my $sth = $dbh->prepare ($query) ;
199 sub get_db_version () {
200 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
202 $query = "SELECT value FROM $tablename WHERE key = 'db-version'" ;
204 my $sth = $dbh->prepare ($query) ;
206 my @array = $sth->fetchrow_array () ;
209 my $version = $array [0] ;
214 sub drop_table_if_exists ( $ ) {
215 my $tname = shift or die "Not enough arguments" ;
216 $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
217 my $sth = $dbh->prepare ($query) ;
219 my @array = $sth->fetchrow_array () ;
222 if ($array [0] != 0) {
223 # debug "Dropping table $tname" ;
224 $query = "DROP TABLE $tname" ;
226 $sth = $dbh->prepare ($query) ;
232 sub drop_sequence_if_exists ( $ ) {
233 my $sname = shift or die "Not enough arguments" ;
234 $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
235 my $sth = $dbh->prepare ($query) ;
237 my @array = $sth->fetchrow_array () ;
240 if ($array [0] != 0) {
241 # debug "Dropping sequence $sname" ;
242 $query = "DROP SEQUENCE $sname" ;
244 $sth = $dbh->prepare ($query) ;
250 sub drop_index_if_exists ( $ ) {
251 my $iname = shift or die "Not enough arguments" ;
252 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
253 my $sth = $dbh->prepare ($query) ;
255 my @array = $sth->fetchrow_array () ;
258 if ($array [0] != 0) {
259 # debug "Dropping index $iname" ;
260 $query = "DROP INDEX $iname" ;
262 $sth = $dbh->prepare ($query) ;
268 sub drop_view_if_exists ( $ ) {
269 my $iname = shift or die "Not enough arguments" ;
270 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='v'" ;
271 my $sth = $dbh->prepare ($query) ;
273 my @array = $sth->fetchrow_array () ;
276 if ($array [0] != 0) {
277 # debug "Dropping view $iname" ;
278 $query = "DROP VIEW $iname" ;
280 $sth = $dbh->prepare ($query) ;
286 sub bump_sequence_to ( $$ ) {
287 my ($sth, @array, $seqname, $targetvalue) ;
290 $targetvalue = shift ;
293 $query = "select nextval ('$seqname')" ;
294 $sth = $dbh->prepare ($query) ;
296 @array = $sth->fetchrow_array () ;
298 } until $array[0] >= $targetvalue ;