| 1 | # | ||
| 2 | # Class::Persistent::Plugin::MySQL - Plugin to enable persistence through the MySQL-database. | ||
| 3 | # $Id$ | ||
| 4 | # | ||
| 5 | # Copyright (C) 2000 by Heiko Wundram. | ||
| 6 | # All rights reserved. | ||
| 7 | # | ||
| 8 | # This program is free software; you can redistribute and/or modify it under the same terms as Perl itself. | ||
| 9 | # | ||
| 10 | # $Log$ | ||
| 11 | # | ||
| 12 | |||
| 13 | package Class::Persistent::Plugin::MySQL; | ||
| 14 | $Class::Persistent::Plugin::MySQL::VERSION = '0.01'; | ||
| 15 | |||
| 16 | use DBI qw(:sql_types); | ||
| 17 | |||
| 18 | use Carp; | ||
| 19 | |||
| 20 | sub new | ||
| 21 | { | ||
| 22 | my ($class,$dsn,$user,$passwd) = @_; | ||
| 23 | $class = ref $class ? ref $class : $class; | ||
| 24 | my $dbh = DBI->connect( $dsn, $user, $passwd, | ||
| 25 | { PrintError => 0, RaiseError => 0 } ) | ||
| 26 | or confess("Cannot connect to database: $DBI::errstr!"); | ||
| 27 | my $self = {}; | ||
| 28 | |||
| 29 | $self->{"_dbh"} = $dbh; | ||
| 30 | |||
| 31 | bless( $self, $class ); | ||
| 32 | |||
| 33 | return $self; | ||
| 34 | } | ||
| 35 | |||
| 36 | sub normalize_pkg | ||
| 37 | { | ||
| 38 | my ($pkg) = @_; | ||
| 39 | |||
| 40 | $pkg =~ s/:/_/g; | ||
| 41 | return $pkg; | ||
| 42 | } | ||
| 43 | |||
| 44 | sub get_max_id | ||
| 45 | { | ||
| 46 | if( @_ != 2 ) | ||
| 47 | { | ||
| 48 | confess "get_max_id can only be called with one arguments!"; | ||
| 49 | } | ||
| 50 | |||
| 51 | my ($class,$pkg) = @_; | ||
| 52 | ref $class or confess "get_max_id can only be called on an instance of the storage class!"; | ||
| 53 | my $dbh = $class->{"_dbh"}; | ||
| 54 | my $db_pkg = normalize_pkg($pkg); | ||
| 55 | my $sth; | ||
| 56 | my $ret_val; | ||
| 57 | |||
| 58 | ( $sth = $dbh->prepare("SELECT max(_id) FROM $db_pkg") ) | ||
| 59 | or confess("Something is really wrong with the database!"); | ||
| 60 | |||
| 61 | if( !$sth->execute() ) | ||
| 62 | { | ||
| 63 | $ret_val = 0; | ||
| 64 | } | ||
| 65 | else | ||
| 66 | { | ||
| 67 | ($ret_val) = $sth->fetchrow_array; | ||
| 68 | } | ||
| 69 | $sth->finish(); | ||
| 70 | |||
| 71 | return $ret_val+1; | ||
| 72 | } | ||
| 73 | |||
| 74 | sub load | ||
| 75 | { | ||
| 76 | if( @_ < 3 || @_ > 4 ) | ||
| 77 | { | ||
| 78 | confess "load can only be called with two or three arguments!"; | ||
| 79 | } | ||
| 80 | |||
| 81 | my ($class,$out,$pkg,$type) = @_; | ||
| 82 | ref $class or confess "load can only be called on an instance of the storage class!"; | ||
| 83 | ref $out or confess "load can only be called on an instance of the container class!"; | ||
| 84 | my $dbh = $class->{"_dbh"}; | ||
| 85 | my $db_pkg = normalize_pkg($pkg); | ||
| 86 | my $sth = $class->{"_sth"}; | ||
| 87 | my $ret_val; | ||
| 88 | my $set_val; | ||
| 89 | my ($attribs,$types); | ||
| 90 | |||
| 91 | if( $sth ) | ||
| 92 | { | ||
| 93 | $set_val = $sth->fetchrow_hashref(); | ||
| 94 | if( !$set_val ) | ||
| 95 | { | ||
| 96 | $ret_val = 1; | ||
| 97 | $sth->finish(); | ||
| 98 | delete $class->{"_sth"}; | ||
| 99 | } | ||
| 100 | else | ||
| 101 | { | ||
| 102 | $ret_val = 0; | ||
| 103 | } | ||
| 104 | } | ||
| 105 | else | ||
| 106 | { | ||
| 107 | ( $sth = $dbh->prepare("SELECT * FROM $db_pkg".($type?" WHERE $type":"")) ) | ||
| 108 | or confess("Something is really wrong with the database!"); | ||
| 109 | |||
| 110 | if( !$sth->execute() ) | ||
| 111 | { | ||
| 112 | $set_val = undef; | ||
| 113 | $ret_val = -1; | ||
| 114 | } | ||
| 115 | else | ||
| 116 | { | ||
| 117 | $set_val = $sth->fetchrow_hashref(); | ||
| 118 | $ret_val = 0; | ||
| 119 | $class->{"_sth"} = $sth; | ||
| 120 | } | ||
| 121 | } | ||
| 122 | |||
| 123 | if( $ret_val == 0 ) | ||
| 124 | { | ||
| 125 | ($attribs,$types) = split_hash($set_val); | ||
| 126 | $out->set_attributes_type($attribs,$types); | ||
| 127 | } | ||
| 128 | |||
| 129 | return $ret_val; | ||
| 130 | } | ||
| 131 | |||
| 132 | sub split_hash | ||
| 133 | { | ||
| 134 | if( @_ != 1 ) | ||
| 135 | { | ||
| 136 | confess "split_hash can only be called with one argument!"; | ||
| 137 | } | ||
| 138 | |||
| 139 | my ($set_val) = @_; | ||
| 140 | my $key; | ||
| 141 | my ($attribs,$types) = ({},{}); | ||
| 142 | |||
| 143 | foreach $key (keys %$set_val) | ||
| 144 | { | ||
| 145 | if( $key =~ /^(.*)_type$/ ) | ||
| 146 | { | ||
| 147 | $types->{$1} = $set_val->{$key}; | ||
| 148 | } | ||
| 149 | else | ||
| 150 | { | ||
| 151 | $attribs->{$key} = $set_val->{$key}; | ||
| 152 | } | ||
| 153 | } | ||
| 154 | |||
| 155 | return ($attribs,$types); | ||
| 156 | } | ||
| 157 | |||
| 158 | sub store | ||
| 159 | { | ||
| 160 | if( @_ != 3 ) | ||
| 161 | { | ||
| 162 | confess "store can only be called with two arguments!"; | ||
| 163 | } | ||
| 164 | |||
| 165 | my ($class,$out,$pkg) = @_; | ||
| 166 | ref $class or confess "store can only be called on an instance of the storage class!"; | ||
| 167 | ref $out or confess "store can only be called on an instance of the container class!"; | ||
| 168 | my $dbh = $class->{"_dbh"}; | ||
| 169 | my $db_pkg = normalize_pkg($pkg); | ||
| 170 | my $sth; | ||
| 171 | my ($attribs,$types); | ||
| 172 | my ($statement_pre,$statement_post,$statement); | ||
| 173 | my %binds; | ||
| 174 | my @binds; | ||
| 175 | my ($attrib,$i); | ||
| 176 | |||
| 177 | ($attribs,$types) = $out->get_attributes_type(); | ||
| 178 | |||
| 179 | construct_table($dbh,$db_pkg,$types); | ||
| 180 | |||
| 181 | $statement_pre = "INSERT INTO $db_pkg ("; | ||
| 182 | $statement_post = "VALUES ("; | ||
| 183 | $i = 1; | ||
| 184 | |||
| 185 | foreach $attrib (keys %$attribs) | ||
| 186 | { | ||
| 187 | $binds{$i++} = $attribs->{$attrib}; | ||
| 188 | $binds{$i++} = $types->{$attrib}; | ||
| 189 | |||
| 190 | $statement_pre .= $attrib.",".$attrib."_type,"; | ||
| 191 | $statement_post .= "?,?,"; | ||
| 192 | } | ||
| 193 | |||
| 194 | $statement_pre =~ s/,$//; | ||
| 195 | $statement_post =~ s/,$//; | ||
| 196 | |||
| 197 | $statement = $statement_pre.") ".$statement_post.")"; | ||
| 198 | |||
| 199 | ( $sth = $dbh->prepare($statement) ) | ||
| 200 | or confess("Something amiss with the database!"); | ||
| 201 | |||
| 202 | foreach $attrib (keys %binds) | ||
| 203 | { | ||
| 204 | $sth->bind_param($attrib,$binds{$attrib},SQL_VARCHAR); | ||
| 205 | } | ||
| 206 | |||
| 207 | return $sth->execute(@binds); | ||
| 208 | } | ||
| 209 | |||
| 210 | sub save | ||
| 211 | { | ||
| 212 | if( @_ != 3 ) | ||
| 213 | { | ||
| 214 | confess "save can only be called with two arguments!"; | ||
| 215 | } | ||
| 216 | |||
| 217 | my ($class,$out,$pkg) = @_; | ||
| 218 | ref $class or confess "save can only be called on an instance of the storage class!"; | ||
| 219 | ref $out or confess "save can only be called on an instance of the container class!"; | ||
| 220 | my $dbh = $class->{"_dbh"}; | ||
| 221 | my $db_pkg = normalize_pkg($pkg); | ||
| 222 | my $sth; | ||
| 223 | my ($attribs,$types); | ||
| 224 | my $statement; | ||
| 225 | my %binds; | ||
| 226 | my ($attrib,$i); | ||
| 227 | |||
| 228 | ($attribs,$types) = $out->get_attributes_type(); | ||
| 229 | |||
| 230 | construct_table($dbh,$db_pkg,$types); | ||
| 231 | |||
| 232 | $statement = "UPDATE $db_pkg SET "; | ||
| 233 | $i = 1; | ||
| 234 | |||
| 235 | foreach $attrib (keys %$attribs) | ||
| 236 | { | ||
| 237 | $binds{$i++} = $attribs->{$attrib}; | ||
| 238 | $binds{$i++} = $types->{$attrib}; | ||
| 239 | |||
| 240 | $statement .= $attrib."=?,".$attrib."_type=?,"; | ||
| 241 | } | ||
| 242 | |||
| 243 | $statement =~ s/,$//; | ||
| 244 | |||
| 245 | $statement .= " WHERE _id = ".$attribs->{"_id"}; | ||
| 246 | |||
| 247 | ( $sth = $dbh->prepare($statement) ) | ||
| 248 | or confess("Something amiss with the database!"); | ||
| 249 | |||
| 250 | foreach $attrib (keys %binds) | ||
| 251 | { | ||
| 252 | $sth->bind_param($attrib,$binds{$attrib},SQL_VARCHAR); | ||
| 253 | } | ||
| 254 | |||
| 255 | return $sth->execute(); | ||
| 256 | } | ||
| 257 | |||
| 258 | sub delete | ||
| 259 | { | ||
| 260 | if( @_ != 3 ) | ||
| 261 | { | ||
| 262 | confess "delete requires two arguments!"; | ||
| 263 | } | ||
| 264 | |||
| 265 | my ($class,$out,$pkg) = @_; | ||
| 266 | ref $class or confess "delete can only be called on an instance of the storage class!"; | ||
| 267 | ref $out or confess "delete can only be called on an instance of the container class!"; | ||
| 268 | my $dbh = $class->{"_dbh"}; | ||
| 269 | my $db_pkg = normalize_pkg($pkg); | ||
| 270 | my $sth; | ||
| 271 | my ($attribs,$types); | ||
| 272 | my $statement; | ||
| 273 | |||
| 274 | ($attribs,$types) = $out->get_attributes_type(); | ||
| 275 | |||
| 276 | $statement = "DELETE FROM $db_pkg WHERE _id = ".$attribs->{"_id"}; | ||
| 277 | |||
| 278 | ( $sth = $dbh->prepare($statement) ) | ||
| 279 | or confess("Something is amiss with the database!"); | ||
| 280 | |||
| 281 | return $sth->execute(); | ||
| 282 | } | ||
| 283 | |||
| 284 | sub calc_refs | ||
| 285 | { | ||
| 286 | if( @_ != 3 ) | ||
| 287 | { | ||
| 288 | confess "calc_refs requires two arguments!"; | ||
| 289 | } | ||
| 290 | |||
| 291 | my ($class,$out,$pkg) = @_; | ||
| 292 | ref $class or confess "calc_refs can only be called on an instance of the storage class!"; | ||
| 293 | ref $out or confess "calc_refs can only be called on an instance of the container class!"; | ||
| 294 | my $dbh = $class->{"_dbh"}; | ||
| 295 | my $sth; | ||
| 296 | my ($attribs,$types); | ||
| 297 | my $id; | ||
| 298 | my (@tables,$table); | ||
| 299 | my $refs; | ||
| 300 | my $vals; | ||
| 301 | my $key; | ||
| 302 | |||
| 303 | ($attribs,$types) = $out->get_attributes_type(); | ||
| 304 | |||
| 305 | $id = $attribs->{"_id"}."|$pkg"; | ||
| 306 | |||
| 307 | $statement = "SELECT * FROM pkg_list"; | ||
| 308 | |||
| 309 | ( $sth = $dbh->prepare($statement) ) | ||
| 310 | or confess("Something is amiss with the database!"); | ||
| 311 | |||
| 312 | $sth->execute(); | ||
| 313 | |||
| 314 | while( ($table) = $sth->fetchrow_array() ) | ||
| 315 | { | ||
| 316 | push @tables, $table; | ||
| 317 | } | ||
| 318 | |||
| 319 | foreach $table (@tables) | ||
| 320 | { | ||
| 321 | $statement = "SELECT * FROM $table"; | ||
| 322 | |||
| 323 | ( $sth = $dbh->prepare($statement) ) | ||
| 324 | or confess("Something is amiss with the database!"); | ||
| 325 | |||
| 326 | $sth->execute(); | ||
| 327 | |||
| 328 | while( $vals = $sth->fetchrow_hashref() ) | ||
| 329 | { | ||
| 330 | foreach $key (keys %$vals) | ||
| 331 | { | ||
| 332 | if( $key =~ /_type$/ ) | ||
| 333 | { | ||
| 334 | next; | ||
| 335 | } | ||
| 336 | |||
| 337 | if( $vals->{$key."_type"} eq 'c' ) | ||
| 338 | { | ||
| 339 | if( $vals->{$key} eq $id ) | ||
| 340 | { | ||
| 341 | $refs++; | ||
| 342 | } | ||
| 343 | } | ||
| 344 | } | ||
| 345 | } | ||
| 346 | } | ||
| 347 | |||
| 348 | return $refs; | ||
| 349 | } | ||
| 350 | |||
| 351 | sub check_tables | ||
| 352 | { | ||
| 353 | if( @_ != 1 ) | ||
| 354 | { | ||
| 355 | confess "check_tables takes no arguments!"; | ||
| 356 | } | ||
| 357 | |||
| 358 | my ($class) = @_; | ||
| 359 | ref $class or confess "delete can only be called on an instance of the storage class!"; | ||
| 360 | my $dbh = $class->{"_dbh"}; | ||
| 361 | my $sth; | ||
| 362 | my $statement; | ||
| 363 | my (@tables,$table); | ||
| 364 | my $count; | ||
| 365 | my @delete; | ||
| 366 | |||
| 367 | $statement = "SELECT * FROM pkg_list"; | ||
| 368 | |||
| 369 | ( $sth = $dbh->prepare($statement) ) | ||
| 370 | or confess("Something is amiss with the database!"); | ||
| 371 | |||
| 372 | $sth->execute(); | ||
| 373 | |||
| 374 | while( ($table) = $sth->fetchrow_array() ) | ||
| 375 | { | ||
| 376 | push @tables, $table; | ||
| 377 | } | ||
| 378 | |||
| 379 | foreach $table (@tables) | ||
| 380 | { | ||
| 381 | $statement = "SELECT COUNT(*) FROM $table"; | ||
| 382 | |||
| 383 | ( $sth = $dbh->prepare($statement) ) | ||
| 384 | or confess("Something is amiss with the database!"); | ||
| 385 | |||
| 386 | $sth->execute(); | ||
| 387 | |||
| 388 | ($count) = $sth->fetchrow_array(); | ||
| 389 | |||
| 390 | if( $count == 0 ) | ||
| 391 | { | ||
| 392 | push @delete, $table; | ||
| 393 | } | ||
| 394 | } | ||
| 395 | |||
| 396 | foreach $table (@delete) | ||
| 397 | { | ||
| 398 | $statement = "DROP TABLE $table"; | ||
| 399 | |||
| 400 | ( $sth = $dbh->prepare($statement) ) | ||
| 401 | or confess("Something is amiss with the database!"); | ||
| 402 | |||
| 403 | $sth->execute(); | ||
| 404 | |||
| 405 | $statement = "DELETE FROM pkg_list WHERE pkg = '$table'"; | ||
| 406 | |||
| 407 | ( $sth = $dbh->prepare($statement) ) | ||
| 408 | or confess("Something is amiss with the database!"); | ||
| 409 | |||
| 410 | $sth->execute(); | ||
| 411 | } | ||
| 412 | } | ||
| 413 | |||
| 414 | sub construct_table | ||
| 415 | { | ||
| 416 | if( @_ != 3 ) | ||
| 417 | { | ||
| 418 | confess "construct_table can only be called with two arguments!"; | ||
| 419 | } | ||
| 420 | |||
| 421 | my ($dbh,$db_pkg,$types) = @_; | ||
| 422 | my $statement; | ||
| 423 | my $field; | ||
| 424 | |||
| 425 | $statement = "CREATE TABLE pkg_list (pkg VARCHAR(255) NOT NULL,UNIQUE (pkg))"; | ||
| 426 | |||
| 427 | ( $sth = $dbh->prepare($statement) ) | ||
| 428 | or confess("Something is amiss with the database!"); | ||
| 429 | |||
| 430 | $sth->execute(); | ||
| 431 | |||
| 432 | $statement = "INSERT INTO pkg_list VALUES (?)"; | ||
| 433 | |||
| 434 | ( $sth = $dbh->prepare($statement) ) | ||
| 435 | or confess("Something is amiss with the database!"); | ||
| 436 | |||
| 437 | $sth->bind_param(1,$db_pkg,SQL_VARCHAR); | ||
| 438 | |||
| 439 | $sth->execute(); | ||
| 440 | |||
| 441 | $statement = "CREATE TABLE $db_pkg ("; | ||
| 442 | |||
| 443 | foreach $field (keys %$types) | ||
| 444 | { | ||
| 445 | $statement .= $field." "; | ||
| 446 | |||
| 447 | if( $types->{$field} eq 'n' ) | ||
| 448 | { | ||
| 449 | $statement .= "BIGINT,"; | ||
| 450 | } | ||
| 451 | elsif( $types->{$field} eq 's' || $types->{$field} eq 'c' ) | ||
| 452 | { | ||
| 453 | $statement .= "LONGTEXT,"; | ||
| 454 | } | ||
| 455 | else | ||
| 456 | { | ||
| 457 | $statement .= "LONGBLOB,"; | ||
| 458 | } | ||
| 459 | |||
| 460 | $statement .= $field."_type CHAR(1),"; | ||
| 461 | } | ||
| 462 | |||
| 463 | $statement =~ s/,$/\)/; | ||
| 464 | |||
| 465 | ( $sth = $dbh->prepare($statement) ) | ||
| 466 | or confess("Something is amiss with the database!"); | ||
| 467 | |||
| 468 | return $sth->execute(); | ||
| 469 | } | ||
| 470 | |||
| 471 | sub DESTROY | ||
| 472 | { | ||
| 473 | my ($class) = @_; | ||
| 474 | |||
| 475 | $class->{"_dbh"}->disconnect() or croak("Could not disconnect from Datasource!"); | ||
| 476 | } | ||
| 477 | |||
| 478 | 1; |