1 # A few SQL helper functions
4 # This file is copyright 2004 Roland Mas <99.roland.mas@aist.enst.fr>.
6 # This is Free Software; you can redistribute it and/or modify it under the
7 # terms of the GNU General Public License version 2, as published by the
8 # Free Software Foundation.
11 # drop_view_if_exists ("view_name") ;
12 # drop_table_if_exists ("table_name") ;
13 # drop_index_if_exists ("index_name") ;
14 # drop_sequence_if_exists ("sequence_name") ;
15 # remove_plugin_from_groups ("plugin_name") ;
16 # remove_plugin_from_users ("plugin_name") ;
19 # * No real bugs known -- yet
26 use subs qw/ &get_plugin_id &remove_plugin_from_groups
27 &remove_plugin_from_users &drop_table_if_exists
28 &drop_index_if_exists &drop_sequence_if_exists
29 &drop_view_if_exists &bump_sequence_to &update_plugin_db_version
30 &get_plugin_db_version &debug &create_plugin_metadata_table
31 &is_lesser &is_greater &db_connect &db_disconnect / ;
33 sub get_plugin_id ( $$ ) ;
34 sub remove_plugin_from_groups ( $$ ) ;
35 sub remove_plugin_from_users ( $$ ) ;
36 sub table_exists ( $$ ) ;
37 sub view_exists ( $$ ) ;
38 sub drop_table_if_exists ( $$ ) ;
39 sub drop_index_if_exists ( $$ ) ;
40 sub drop_sequence_if_exists ( $$ ) ;
41 sub drop_view_if_exists ( $$ ) ;
42 sub bump_sequence_to ( $$$ ) ;
43 sub update_plugin_db_version ( $$$ ) ;
44 sub get_plugin_db_version ( $$ ) ;
45 sub create_plugin_metadata_table ( $$$ ) ;
46 sub is_lesser ( $$ ) ;
47 sub is_greater ( $$ ) ;
50 sub db_disconnect ( ) ;
52 sub table_exists ( $$ ) {
53 my $dbh = shift or die "Not enough arguments" ;
54 my $tname = shift or die "Not enough arguments" ;
55 my $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
56 my $sth = $dbh->prepare ($query) ;
58 my @array = $sth->fetchrow_array () ;
61 if ($array [0] != 0) {
68 sub drop_table_if_exists ( $$ ) {
69 my $dbh = shift or die "Not enough arguments" ;
70 my $tname = shift or die "Not enough arguments" ;
72 if (&table_exists ($dbh, $tname)) {
73 # debug "Dropping table $tname" ;
74 my $query = "DROP TABLE $tname" ;
76 my $sth = $dbh->prepare ($query) ;
82 sub drop_sequence_if_exists ( $$ ) {
83 my $dbh = shift or die "Not enough arguments" ;
84 my $sname = shift or die "Not enough arguments" ;
85 my $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
86 my $sth = $dbh->prepare ($query) ;
88 my @array = $sth->fetchrow_array () ;
91 if ($array [0] != 0) {
92 # debug "Dropping sequence $sname" ;
93 $query = "DROP SEQUENCE $sname" ;
95 $sth = $dbh->prepare ($query) ;
101 sub drop_index_if_exists ( $$ ) {
102 my $dbh = shift or die "Not enough arguments" ;
103 my $iname = shift or die "Not enough arguments" ;
104 my $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
105 my $sth = $dbh->prepare ($query) ;
107 my @array = $sth->fetchrow_array () ;
110 if ($array [0] != 0) {
111 # debug "Dropping index $iname" ;
112 $query = "DROP INDEX $iname" ;
114 $sth = $dbh->prepare ($query) ;
120 sub view_exists ( $$ ) {
121 my $dbh = shift or die "Not enough arguments" ;
122 my $vname = shift or die "Not enough arguments" ;
123 my $query = "SELECT count(*) FROM pg_class WHERE relname='$vname' AND relkind='v'" ;
124 my $sth = $dbh->prepare ($query) ;
126 my @array = $sth->fetchrow_array () ;
129 if ($array [0] != 0) {
136 sub drop_view_if_exists ( $$ ) {
137 my $dbh = shift or die "Not enough arguments" ;
138 my $vname = shift or die "Not enough arguments" ;
140 if (&view_exists ($dbh, $vname)) {
141 # debug "Dropping view $vname" ;
142 my $query = "DROP VIEW $vname" ;
144 my $sth = $dbh->prepare ($query) ;
150 sub bump_sequence_to ( $$$ ) {
151 my $dbh = shift or die "Not enough arguments" ;
152 my $seqname = shift or die "Not enough arguments" ;
153 my $targetvalue = shift or die "Not enough arguments" ;
158 my $query = "select nextval ('$seqname')" ;
159 $sth = $dbh->prepare ($query) ;
161 @array = $sth->fetchrow_array () ;
163 } until $array[0] >= $targetvalue ;
166 sub get_plugin_id ( $$ ) {
167 my $dbh = shift or die "Not enough arguments" ;
168 my $pluginname = shift or die "Not enough arguments" ;
172 my $query = "SELECT plugin_id FROM plugins WHERE plugin_name = '$pluginname'" ;
173 my $sth = $dbh->prepare ($query) ;
175 if (my @array = $sth->fetchrow_array ()) {
176 $pluginid = $array [0] ;
183 sub remove_plugin_from_groups ( $$ ) {
184 my $dbh = shift or die "Not enough arguments" ;
185 my $pluginid = shift or die "Not enough arguments" ;
187 my $query = "DELETE FROM group_plugin WHERE plugin_id = $pluginid" ;
188 my $sth = $dbh->prepare ($query) ;
193 sub remove_plugin_from_users ( $$ ) {
194 my $dbh = shift or die "Not enough arguments" ;
195 my $pluginid = shift or die "Not enough arguments" ;
197 my $query = "DELETE FROM user_plugin WHERE plugin_id = $pluginid" ;
198 my $sth = $dbh->prepare ($query) ;
203 sub update_plugin_db_version ( $$$ ) {
204 my $dbh = shift or die "Not enough arguments" ;
205 my $pluginname = shift or die "Not enough arguments" ;
206 my $v = shift or die "Not enough arguments" ;
208 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
210 debug "Updating $tablename table." ;
211 my $query = "UPDATE $tablename SET value = '$v' WHERE key = 'db-version'" ;
213 my $sth = $dbh->prepare ($query) ;
218 sub get_plugin_db_version ( $$ ) {
219 my $dbh = shift or die "Not enough arguments" ;
220 my $pluginname = shift or die "Not enough arguments" ;
222 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
224 my $query = "SELECT value FROM $tablename WHERE key = 'db-version'" ;
226 my $sth = $dbh->prepare ($query) ;
228 my @array = $sth->fetchrow_array () ;
231 my $version = $array [0] ;
239 print STDERR "$v\n" ;
242 sub create_plugin_metadata_table ( $$$ ) {
243 my $dbh = shift or die "Not enough arguments" ;
244 my $pluginname = shift or die "Not enough arguments" ;
245 my $v = shift || "0" ;
247 my $tablename = "plugin_" .$pluginname . "_meta_data" ;
248 # Do we have the metadata table?
250 my $query = "SELECT count(*) FROM pg_class WHERE relname = '$tablename' and relkind = 'r'";
252 my $sth = $dbh->prepare ($query) ;
254 my @array = $sth->fetchrow_array () ;
257 # Let's create this table if we have it not
259 if ($array [0] == 0) {
260 debug "Creating $tablename table." ;
261 $query = "CREATE TABLE $tablename (key varchar primary key, value text not null)" ;
263 $sth = $dbh->prepare ($query) ;
268 $query = "SELECT count(*) FROM $tablename WHERE key = 'db-version'";
270 $sth = $dbh->prepare ($query) ;
272 @array = $sth->fetchrow_array () ;
275 # Empty table? We'll have to fill it up a bit
277 if ($array [0] == 0) {
278 debug "Inserting first data into $tablename table." ;
279 $query = "INSERT INTO $tablename (key, value) VALUES ('db-version', '$v')" ;
281 $sth = $dbh->prepare ($query) ;
287 sub is_lesser ( $$ ) {
288 my $v1 = shift || 0 ;
289 my $v2 = shift || 0 ;
291 return (versioncmp($v1, $v2) < 0) ;
294 sub is_greater ( $$ ) {
295 my $v1 = shift || 0 ;
296 my $v2 = shift || 0 ;
298 return (versioncmp($v1, $v2) > 0) ;