3 # $Id: db-upgrade.pl,v 1.1.1.1 2005/09/22 17:32:56 rhertzog Exp $
5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
15 use vars qw/$dbh @reqlist $query/ ;
16 use vars qw/$sys_default_domain $sys_cvs_host
17 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
18 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
19 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
20 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
21 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
24 use vars qw/$pluginname/ ;
26 sub is_lesser ( $$ ) ;
27 sub is_greater ( $$ ) ;
29 sub parse_sql_file ( $ ) ;
31 require ("/usr/share/gforge/lib/include.pl") ; # Include a few predefined functions
32 require ("/usr/share/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
34 debug "You'll see some debugging info during this installation." ;
35 debug "Do not worry unless told otherwise." ;
39 # debug "Connected to the database OK." ;
41 $pluginname = "scmgit" ;
43 $dbh->{AutoCommit} = 0;
44 $dbh->{RaiseError} = 1;
46 my ($sth, @array, $version, $path, $target) ;
48 &create_metadata_table ("0") ;
50 $version = &get_db_version ;
52 if (is_lesser $version, $target) {
53 my @filelist = ( "/usr/share/gforge/plugins/$pluginname/lib/$pluginname-init.sql" ) ;
55 foreach my $file (@filelist) {
56 debug "Processing $file" ;
57 @reqlist = @{ &parse_sql_file ($file) } ;
59 foreach my $s (@reqlist) {
62 $sth = $dbh->prepare ($query) ;
69 &update_db_version ($target) ;
74 &update_with_sql("/usr/share/gforge/plugins/$pluginname/lib/20121019-multiple-repos.sql","0.2");
75 &update_with_sql("/usr/share/gforge/plugins/$pluginname/lib/20121128-drop-old-tables.sql","0.3");
77 debug "It seems your database install/upgrade went well and smoothly. That's cool." ;
78 debug "Please enjoy using Debian GForge." ;
80 # There should be a commit at the end of every block above.
81 # If there is not, then it might be symptomatic of a problem.
82 # For safety, we roll back.
87 warn "Transaction aborted because $@" ;
88 debug "Transaction aborted because $@" ;
89 debug "Last SQL query was:\n$query\n(end of query)" ;
91 debug "Please report this bug on the Debian bug-tracking system." ;
92 debug "Please include the previous messages as well to help debugging." ;
93 debug "You should not worry too much about this," ;
94 debug "your DB is still in a consistent state and should be usable." ;
101 sub is_lesser ( $$ ) {
102 my $v1 = shift || 0 ;
103 my $v2 = shift || 0 ;
105 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
110 sub is_greater ( $$ ) {
111 my $v1 = shift || 0 ;
112 my $v2 = shift || 0 ;
114 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
122 print STDERR "$v\n" ;
125 sub create_metadata_table ( $ ) {
126 my $v = shift || "0" ;
127 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
128 # Do we have the metadata table?
130 $query = "SELECT count(*) FROM pg_class WHERE relname = '$tablename' and relkind = 'r'";
132 my $sth = $dbh->prepare ($query) ;
134 my @array = $sth->fetchrow_array () ;
137 # Let's create this table if we have it not
139 if ($array [0] == 0) {
140 debug "Creating $tablename table." ;
141 $query = "CREATE TABLE $tablename (key varchar primary key, value text not null)" ;
143 $sth = $dbh->prepare ($query) ;
148 $query = "SELECT count(*) FROM $tablename WHERE key = 'db-version'";
150 $sth = $dbh->prepare ($query) ;
152 @array = $sth->fetchrow_array () ;
155 # Empty table? We'll have to fill it up a bit
157 if ($array [0] == 0) {
158 debug "Inserting first data into $tablename table." ;
159 $query = "INSERT INTO $tablename (key, value) VALUES ('db-version', '$v')" ;
161 $sth = $dbh->prepare ($query) ;
167 sub update_db_version ( $ ) {
168 my $v = shift or die "Not enough arguments" ;
169 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
171 debug "Updating $tablename table." ;
172 $query = "UPDATE $tablename SET value = '$v' WHERE key = 'db-version'" ;
174 my $sth = $dbh->prepare ($query) ;
179 sub get_db_version () {
180 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
182 $query = "SELECT value FROM $tablename WHERE key = 'db-version'" ;
184 my $sth = $dbh->prepare ($query) ;
186 my @array = $sth->fetchrow_array () ;
189 my $version = $array [0] ;
194 sub drop_table_if_exists ( $ ) {
195 my $tname = shift or die "Not enough arguments" ;
196 $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
197 my $sth = $dbh->prepare ($query) ;
199 my @array = $sth->fetchrow_array () ;
202 if ($array [0] != 0) {
203 # debug "Dropping table $tname" ;
204 $query = "DROP TABLE $tname" ;
206 $sth = $dbh->prepare ($query) ;
212 sub drop_sequence_if_exists ( $ ) {
213 my $sname = shift or die "Not enough arguments" ;
214 $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
215 my $sth = $dbh->prepare ($query) ;
217 my @array = $sth->fetchrow_array () ;
220 if ($array [0] != 0) {
221 # debug "Dropping sequence $sname" ;
222 $query = "DROP SEQUENCE $sname" ;
224 $sth = $dbh->prepare ($query) ;
230 sub drop_index_if_exists ( $ ) {
231 my $iname = shift or die "Not enough arguments" ;
232 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
233 my $sth = $dbh->prepare ($query) ;
235 my @array = $sth->fetchrow_array () ;
238 if ($array [0] != 0) {
239 # debug "Dropping index $iname" ;
240 $query = "DROP INDEX $iname" ;
242 $sth = $dbh->prepare ($query) ;
248 sub drop_view_if_exists ( $ ) {
249 my $iname = shift or die "Not enough arguments" ;
250 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='v'" ;
251 my $sth = $dbh->prepare ($query) ;
253 my @array = $sth->fetchrow_array () ;
256 if ($array [0] != 0) {
257 # debug "Dropping view $iname" ;
258 $query = "DROP VIEW $iname" ;
260 $sth = $dbh->prepare ($query) ;
266 sub bump_sequence_to ( $$ ) {
267 my ($sth, @array, $seqname, $targetvalue) ;
270 $targetvalue = shift ;
273 $query = "select nextval ('$seqname')" ;
274 $sth = $dbh->prepare ($query) ;
276 @array = $sth->fetchrow_array () ;
278 } until $array[0] >= $targetvalue ;
281 sub update_with_sql ( $$ ) {
282 my $sqlfile = shift or die "Not enough arguments" ;
283 my $target = shift or die "Not enough arguments" ;
284 $sqlfile =~ s/\.sql$//;
285 my $version = &get_db_version ;
286 if (&is_lesser ($version, $target)) {
287 &debug ("Upgrading database with $sqlfile.sql") ;
289 @reqlist = @{ &parse_sql_file ("$sqlfile.sql") } ;
290 foreach my $s (@reqlist) {
293 my $sth = $dbh->prepare ($query) ;
299 &update_db_version ($target) ;