| 1 | package CGI::Session::Driver::flexmysql; | ||
| 2 | |||
| 3 | use strict; | ||
| 4 | use DBI; | ||
| 5 | use Carp; | ||
| 6 | use CGI::Session::Driver; | ||
| 7 | @CGI::Session::Driver::flexmysql::ISA = qw( CGI::Session::Driver ); | ||
| 8 | $CGI::Session::Driver::flexmysql::VERSION = "0.2.04"; | ||
| 9 | |||
| 10 | ######################## | ||
| 11 | # Driver methods follow | ||
| 12 | ######################## | ||
| 13 | |||
| 14 | sub init { | ||
| 15 | my $self = shift; | ||
| 16 | |||
| 17 | $self->{MySQL_dbh} = $self->{Handle} || DBI->connect( | ||
| 18 | $self->{DataSource}, | ||
| 19 | $self->{User} || undef, | ||
| 20 | $self->{Password} || undef, | ||
| 21 | { RaiseError=>1, PrintError=>1, AutoCommit=>1 } ); | ||
| 22 | |||
| 23 | # If we established the connection, we should close it | ||
| 24 | $self->{MySQL_disconnect} = 1 unless $self->{Handle}; | ||
| 25 | $self->{MySQL_disconnect} = $self->{AutoDisconnect} if defined $self->{AutoDisconnect}; | ||
| 26 | |||
| 27 | # Table setup options | ||
| 28 | $self->{MySQL_table} = $self->{Table} || 'sessions'; | ||
| 29 | $self->{MySQL_keyfield} = $self->{KeyField} || 'id'; | ||
| 30 | $self->{MySQL_datafield} = $self->{DataField} || 'a_session'; | ||
| 31 | |||
| 32 | return 1; | ||
| 33 | } | ||
| 34 | |||
| 35 | |||
| 36 | # stores the serialized data. Returns 1 for sucess, undef otherwisefield); | ||
| 37 | sub store { | ||
| 38 | my ($self, $sid, $data) = @_; | ||
| 39 | |||
| 40 | my $dbh = $self->{MySQL_dbh}; | ||
| 41 | my $table = $self->{MySQL_table}; | ||
| 42 | my $keyfield = $self->{MySQL_keyfield}; | ||
| 43 | my $datafield = $self->{MySQL_datafield}; | ||
| 44 | |||
| 45 | my ($ok,$exhausted) = (0,0); | ||
| 46 | local $dbh->{RaiseError} = 0; | ||
| 47 | local $dbh->{PrintError} = 0; | ||
| 48 | do { | ||
| 49 | eval { | ||
| 50 | if( $self->{NoInsert} ) { | ||
| 51 | $dbh->do(qq|UPDATE $table SET $datafield=? WHERE $keyfield=?|, | ||
| 52 | undef, $data, $sid) || die $dbh->errstr,"\n"; # croak ref($dbh)," do failed: ",$dbh->errstr; | ||
| 53 | } | ||
| 54 | elsif( $self->{NoReplace} ) { | ||
| 55 | my $rows = $dbh->do(qq|UPDATE $table SET $datafield=? WHERE $keyfield=?|, | ||
| 56 | undef, $data, $sid) || die $dbh->errstr,"\n"; # croak ref($dbh)," do failed: ",$dbh->errstr; | ||
| 57 | if( $rows == 0 ) { | ||
| 58 | $dbh->do(qq|INSERT INTO $table ($keyfield,$datafield) VALUES(?,?)|, | ||
| 59 | undef, $sid, $data) || die $dbh->errstr,"\n"; # croak ref($dbh)," do failed: ",$dbh->errstr; | ||
| 60 | } | ||
| 61 | } | ||
| 62 | else { | ||
| 63 | $dbh->do(qq|REPLACE INTO $table ($keyfield,$datafield) VALUES(?,?)|, | ||
| 64 | undef, $sid, $data) || die $dbh->errstr,"\n"; # croak ref($dbh)," do failed: ",$dbh->errstr; | ||
| 65 | } | ||
| 66 | $ok = 1; | ||
| 67 | }; | ||
| 68 | if( $@ && $@ =~ m/Table '(.+)' doesn't exist/ && $self->{AutoCreate} ) { | ||
| 69 | $exhausted = 1; | ||
| 70 | $dbh->do("CREATE TABLE $table ($keyfield VARCHAR(32) NOT NULL PRIMARY KEY, $datafield TEXT NULL)") && | ||
| 71 | carp __PACKAGE__," issued CREATE TABLE $table ($keyfield VARCHAR(32) NOT NULL PRIMARY KEY, $datafield TEXT NULL)"; | ||
| 72 | $dbh->do(qq|INSERT INTO $table ($keyfield,$datafield) VALUES(?,?)|, | ||
| 73 | undef, $sid, $data) || die $dbh->errstr,"\n"; # croak ref($dbh)," do failed: ",$dbh->errstr; | ||
| 74 | } | ||
| 75 | elsif( $@ ) { | ||
| 76 | $exhausted = 1; | ||
| 77 | croak __PACKAGE__," store failed: ",$@; | ||
| 78 | } | ||
| 79 | } until $ok || $exhausted; | ||
| 80 | |||
| 81 | return 1; | ||
| 82 | } | ||
| 83 | |||
| 84 | |||
| 85 | |||
| 86 | # retrieves the serialized data and deserializes it | ||
| 87 | sub retrieve { | ||
| 88 | my ($self, $sid) = @_; | ||
| 89 | |||
| 90 | my $dbh = $self->{MySQL_dbh}; | ||
| 91 | my $table = $self->{MySQL_table}; | ||
| 92 | my $keyfield = $self->{MySQL_keyfield}; | ||
| 93 | my $datafield = $self->{MySQL_datafield}; | ||
| 94 | |||
| 95 | my $data = $dbh->selectrow_array(qq|SELECT $datafield FROM $table WHERE $keyfield=?|, undef, $sid); | ||
| 96 | $data ||= ""; # in case it was NULL | ||
| 97 | return $data; | ||
| 98 | } | ||
| 99 | |||
| 100 | |||
| 101 | # removes the given data and all the disk space associated with it | ||
| 102 | sub remove { | ||
| 103 | my ($self, $sid) = @_; | ||
| 104 | |||
| 105 | my $dbh = $self->{MySQL_dbh}; | ||
| 106 | my $table = $self->{MySQL_table}; | ||
| 107 | my $keyfield = $self->{MySQL_keyfield}; | ||
| 108 | my $datafield = $self->{MySQL_datafield}; | ||
| 109 | |||
| 110 | if( $self->{NoDelete} ) { | ||
| 111 | $dbh->do(qq|UPDATE $table SET $datafield=NULL WHERE $keyfield=?|, | ||
| 112 | undef, $sid); | ||
| 113 | } | ||
| 114 | else { | ||
| 115 | $dbh->do(qq|DELETE FROM $table WHERE $keyfield=?|, undef, $sid); | ||
| 116 | } | ||
| 117 | |||
| 118 | return 1; | ||
| 119 | } | ||
| 120 | |||
| 121 | |||
| 122 | sub traverse { | ||
| 123 | my ($self,$coderef) = @_; | ||
| 124 | # not implemented yet | ||
| 125 | return 1; | ||
| 126 | } | ||
| 127 | |||
| 128 | |||
| 129 | # called right before the object is destroyed to do cleanup | ||
| 130 | sub DESTROY { | ||
| 131 | my ($self) = @_; | ||
| 132 | my $dbh = $self->{MySQL_dbh}; | ||
| 133 | |||
| 134 | # Call commit if it isn't meant to be autocommited! | ||
| 135 | unless ( $dbh->{AutoCommit} ) { | ||
| 136 | $dbh->commit(); | ||
| 137 | } | ||
| 138 | |||
| 139 | if ( $self->{MySQL_disconnect} ) { | ||
| 140 | $dbh->disconnect(); | ||
| 141 | } | ||
| 142 | |||
| 143 | return 1; | ||
| 144 | } | ||
| 145 | |||
| 146 | |||
| 147 | |||
| 148 | |||
| 149 | |||
| 150 | 1; | ||
| 151 | |||
| 152 | |||
| 153 | =pod | ||
| 154 | |||
| 155 | =head1 NAME | ||
| 156 | |||
| 157 | CGI::Session::Driver::flexmysql - flexible MySQL driver for CGI::Session | ||
| 158 | |||
| 159 | =head1 SYNOPSIS | ||
| 160 | |||
| 161 | use CGI::Session; | ||
| 162 | |||
| 163 | # use the default setup, compatible with Apache::Session | ||
| 164 | # (table 'sessions', primary key 'id', data field 'a_session') | ||
| 165 | |||
| 166 | $session = new CGI::Session("driver:flexmysql", undef, {Handle=>$dbh}); | ||
| 167 | |||
| 168 | # or specify a custom table setup for your sessions: | ||
| 169 | |||
| 170 | $session = new CGI::Session("driver:flexmysql", undef, { | ||
| 171 | Handle => $dbh, # Or use DataSource / User / Password | ||
| 172 | Table => 'custom_table', # You can put your sessions in any table | ||
| 173 | KeyField => 'id', # and any field for your session ids | ||
| 174 | DataField => 'a_session', # and any field for your session data | ||
| 175 | AutoCreate => 1, # even if it doesn't exist yet! | ||
| 176 | }); | ||
| 177 | |||
| 178 | For more options and examples, read the rest of this document and | ||
| 179 | consult L<CGI::Session>. | ||
| 180 | |||
| 181 | =head1 DESCRIPTION | ||
| 182 | |||
| 183 | FlexMySQL is a CGI::Session driver to store session data in MySQL table. | ||
| 184 | |||
| 185 | It differs from the original mysql driver in several ways: | ||
| 186 | |||
| 187 | =over | ||
| 188 | |||
| 189 | =item 1. | ||
| 190 | |||
| 191 | FlexMySQL lets you completely customize your table setup each time you create | ||
| 192 | a new session object. No more setting a class variable for the table name, | ||
| 193 | and also no more agonizing about not liking the default field names. | ||
| 194 | |||
| 195 | =item 2. | ||
| 196 | |||
| 197 | FlexMySQL gives you control over inserting and deleting rows -- so you can | ||
| 198 | ensure that only existing table rows are used for storing sessions, | ||
| 199 | if you're into that sort of thing. | ||
| 200 | |||
| 201 | =item 3. | ||
| 202 | |||
| 203 | FlexMySQL does not misuse the MySQL locking facility. Read "LOCKING" below. | ||
| 204 | |||
| 205 | =item 4. | ||
| 206 | |||
| 207 | FlexMySQL lets you supply an open database handle and then forget about it | ||
| 208 | using the AutoDisconnect feature. | ||
| 209 | |||
| 210 | =back | ||
| 211 | |||
| 212 | To write your own drivers for B<CGI::Session>, refer to L<CGI::Session>. | ||
| 213 | |||
| 214 | =head1 STORAGE | ||
| 215 | |||
| 216 | To store session data in MySQL database, you first need to create or select | ||
| 217 | a table for it. If you don't already have a table for sessions, it's easiest | ||
| 218 | to use the default setup and create a table with the following command: | ||
| 219 | |||
| 220 | CREATE TABLE sessions ( | ||
| 221 | id CHAR(32) NOT NULL PRIMARY KEY, | ||
| 222 | a_session TEXT NOT NULL | ||
| 223 | ); | ||
| 224 | |||
| 225 | You can also add any number of additional columns to the table, but the above "id" | ||
| 226 | and "a_session" are required (unless you specify other field names with options to C<new>). | ||
| 227 | |||
| 228 | =head1 OPTIONS | ||
| 229 | |||
| 230 | You can specify several options to C<new> to change the way FlexMySQL interacts | ||
| 231 | with your database. | ||
| 232 | |||
| 233 | Here is the basic session creation statement: | ||
| 234 | |||
| 235 | $session = new CGI::Session("driver:flexmysql", undef, $options); | ||
| 236 | |||
| 237 | Where C<$options> is a hash reference. C<$options> can include: | ||
| 238 | |||
| 239 | =head2 Handle => $dbh | ||
| 240 | |||
| 241 | An open database handle. Useful if you already have one handy. FlexMySQL will | ||
| 242 | not disconnect your handle unless you ask it to do so, so you can safely use it | ||
| 243 | elsewhere at the same time. | ||
| 244 | |||
| 245 | =head2 DataSource => $dbi_data_source | ||
| 246 | |||
| 247 | If you don't have an open database handle and don't feel like making one, you can | ||
| 248 | use this parameter to have FlexMySQL automatically connect for you. The | ||
| 249 | C<$dbi_data_source> is what goes in this statement: | ||
| 250 | |||
| 251 | DBI->connect( $dbi_data_source ); | ||
| 252 | |||
| 253 | See L<DBI> for details. C<$dbi_data_source> could look like "dbi:MySQL:your_db_name". | ||
| 254 | |||
| 255 | =head2 User => $dbi_username | ||
| 256 | |||
| 257 | Unless you're in configuration paradise, you'll probably need a username to connect | ||
| 258 | to your database. Specify C<$dbi_username> as if you're making this statement: | ||
| 259 | |||
| 260 | DBI->connect( $dbi_data_source, $dbi_username ); | ||
| 261 | |||
| 262 | =head2 Password => $dbi_password | ||
| 263 | |||
| 264 | Again, this is optional. Use it as if you're making this statement: | ||
| 265 | |||
| 266 | DBI->connect( $dbi_data_source, $dbi_username, $dbi_password ); | ||
| 267 | |||
| 268 | =head2 Table => $session_table | ||
| 269 | |||
| 270 | If you don't specify a table, FlexMySQL will try to use a table named 'sessions' | ||
| 271 | by default. This makes it compatible with L<Apache::Session>. | ||
| 272 | |||
| 273 | This option is useful when other apps are using the session table and it's | ||
| 274 | not named 'sessions'. | ||
| 275 | |||
| 276 | =head2 KeyField => $unique_key | ||
| 277 | |||
| 278 | If you don't specify a field name to use as the unique key for session ids, | ||
| 279 | FlexMySQL will try to use a field named 'id'. This makes it compatible with | ||
| 280 | L<Apache::Session>. | ||
| 281 | |||
| 282 | A situation where I use this option is when I use the same table for user info | ||
| 283 | and sessions. This makes it easy to find problems that my users are having | ||
| 284 | because the session is right there on the same row as their other info. I often | ||
| 285 | use their email address as login names, so my setting looks like: | ||
| 286 | |||
| 287 | Table => 'Member', | ||
| 288 | KeyField => 'Email', | ||
| 289 | |||
| 290 | =head2 DataField => $field_name | ||
| 291 | |||
| 292 | If you don't specify a field name to use for data storage, FlexMySQL will try | ||
| 293 | to use a field name 'a_session'. This makes it compatible with L<Apache::Session>. | ||
| 294 | |||
| 295 | Few apps use a field named 'a_session', so I usually don't have to mess with this, | ||
| 296 | but it's here for your configuration pleasure. | ||
| 297 | |||
| 298 | =head2 AutoDisconnect => 1 | ||
| 299 | |||
| 300 | If you don't specify AutoDisconnect at all, or specify undef, then the default | ||
| 301 | behavior is polite: if you gave an open database handle with Handle => $dbh, | ||
| 302 | we leave it open, and if you let FlexMySQL open its own handle by specifying | ||
| 303 | connection parameters, then we also close it for you. | ||
| 304 | |||
| 305 | You can override this behavior and have the handle disconnected no matter what | ||
| 306 | by using AutoDisconnect => 1 | ||
| 307 | |||
| 308 | If you specify a 0 (as opposed to undef or not at all), then FlexMySQL will | ||
| 309 | leave the database handle OPEN no matter what (even if it created it). | ||
| 310 | There aren't many cases where you'll want to use this, but it's here if you want it. | ||
| 311 | |||
| 312 | =head2 NoInsert => 1 | ||
| 313 | |||
| 314 | This option prevents FlexMySQL from inserting new rows into your session table. | ||
| 315 | At first glance, this may seem a bit stupid because it would prevent you from | ||
| 316 | storing any sessions at all -- but I have a use for it, so it's here for you too. | ||
| 317 | |||
| 318 | I often store sessions directly on my user table to make sure that when a user | ||
| 319 | logs in, s/he can always pick up where s/he left off even if logging in from | ||
| 320 | a different browser, after erasing cookies, etc. I do this by specifying the | ||
| 321 | Table and KeyField options. Since the user table already has one row for each | ||
| 322 | user, and since I use the email address of the user as the KeyField, I do NOT | ||
| 323 | want FlexMySQL to create new rows in my user table and put some random MD5 string | ||
| 324 | as the email address. So I specify: | ||
| 325 | |||
| 326 | NoInsert => 1 | ||
| 327 | |||
| 328 | And this makes FlexMySQL use an UPDATE statement instead of a REPLACE INTO statement, | ||
| 329 | which guarantees that if the claimed session id doesn't exist in the table, it | ||
| 330 | won't be created. | ||
| 331 | |||
| 332 | =head2 NoDelete => 1 | ||
| 333 | |||
| 334 | Same scenario as above -- for most applications where you're generating random | ||
| 335 | session ids and inserting rows into a table, you don't want this option because | ||
| 336 | it means that you can't delete sessions except with an external script. | ||
| 337 | |||
| 338 | However, I have a use for it: if I call $session->delete() I want it to delete | ||
| 339 | the session, but since my sessions are often stored in my user table, I do NOT | ||
| 340 | want it to delete the entire row! Using this option effectively makes C<delete> | ||
| 341 | the same as C<clear> (well, almost) because when you specify NoDelete => 1, FlexMySQL | ||
| 342 | updates the database field and sets it to NULL, whereas with C<clear> all the | ||
| 343 | session fields are cleared but the session object itself is still stored in your | ||
| 344 | table's data field). | ||
| 345 | |||
| 346 | =head2 NoReplace => 1 | ||
| 347 | |||
| 348 | Use this option when you want to use INSERT and UPDATE statements instead of a | ||
| 349 | REPLACE statement to store sessions. | ||
| 350 | |||
| 351 | Thanks to Simon Rees for suggesting this option. | ||
| 352 | |||
| 353 | =head2 AutoCreate => 1 | ||
| 354 | |||
| 355 | If you specify this option, FlexMySQL will automatically create a table for | ||
| 356 | session storage if one does not already exist. If you specified any customization | ||
| 357 | such as table name, key field, etc. it is used to create the table. | ||
| 358 | |||
| 359 | =head1 LOCKING | ||
| 360 | |||
| 361 | GET_LOCK and RELEASE_LOCK work on a server-wide basis. If we acquire | ||
| 362 | a lock on some 32-character id for the purpose of sessions, no other | ||
| 363 | application can use locking with 32-character id's generated in the | ||
| 364 | same way (MD5) because there might be a very accidental overlap, which | ||
| 365 | could really screw things up depending on what that other application | ||
| 366 | is doing. In general, session data is not important and we can assume | ||
| 367 | all other applications should have priority with regard to everything. | ||
| 368 | Also, even though CGI::Session::MySQL obtains the locks, it would still | ||
| 369 | overwrite the data as soon as the "other thread" releases its lock, | ||
| 370 | which means that if two clients are using the same session id, the | ||
| 371 | locking gives absolutely no protection. MySQL already guranatees that | ||
| 372 | data is not corrupted when we update the same row from different | ||
| 373 | places at nearly the same time because it uses a queue for the requests. | ||
| 374 | It would be different if, once it detected that there is a lock, it | ||
| 375 | read the data again and SYNCHRONIZED it before updating. Then locking | ||
| 376 | would be helpful because no other client could synchronize at the same | ||
| 377 | time. But CGI::Session::MySQL doesn't actually do anything that requires | ||
| 378 | locking. | ||
| 379 | |||
| 380 | A real use for locking would be to actually lock the session data while | ||
| 381 | the it's being used. GET_LOCK and RELEASE_LOCK are usually not the | ||
| 382 | appropriate way to do this, especially for non-cgi applications where | ||
| 383 | the session may be in use for a considerable amount of time. I propose | ||
| 384 | that this locking be done using an extra field. GET_LOCK and RELEASE_LOCK | ||
| 385 | would be appropriate for locking the row while this extra locking field | ||
| 386 | is being checked and updated, and then this extra locking field would | ||
| 387 | tell other processes using FlexMySQL whether they can read or write the | ||
| 388 | session data. | ||
| 389 | |||
| 390 | Right now I don't have a need for this kind of locking, so it's not | ||
| 391 | implemented. If you want it, you can either send me a patch or roll your | ||
| 392 | own CGI::Session subclass. Asking me very nicely will also work. | ||
| 393 | |||
| 394 | =head1 COPYRIGHT | ||
| 395 | |||
| 396 | CGI::Session::Driver::flexmysql is Copyright (C) 2004-2005 Jonathan Buhacoff. All rights reserved. | ||
| 397 | |||
| 398 | This library is free software and can be modified and distributed under the same | ||
| 399 | terms as Perl itself. | ||
| 400 | |||
| 401 | |||
| 402 | =head1 AUTHOR | ||
| 403 | |||
| 404 | Jonathan Buhacoff <jonathan@buhacoff.net> wrote CGI::Session::Driver::flexmysql | ||
| 405 | |||
| 406 | =head1 SEE ALSO | ||
| 407 | |||
| 408 | =over 4 | ||
| 409 | |||
| 410 | =item * | ||
| 411 | |||
| 412 | L<CGI::Session::Driver::mysql|CGI::Session::Driver::mysql> - The original MySQL driver for CGI::Session | ||
| 413 | written by Sherzod Ruzmetov <sherzodr@cpan.org>. Kudos | ||
| 414 | to Sherzod for making such a great session management framework. | ||
| 415 | |||
| 416 | =item * | ||
| 417 | |||
| 418 | L<CGI::Session|CGI::Session> - CGI::Session manual | ||
| 419 | |||
| 420 | =item * | ||
| 421 | |||
| 422 | L<CGI::Session::Tutorial|CGI::Session::Tutorial> - extended CGI::Session manual | ||
| 423 | |||
| 424 | =item * | ||
| 425 | |||
| 426 | L<CGI::Session::CookBook|CGI::Session::CookBook> - practical solutions for real life problems | ||
| 427 | |||
| 428 | =item * | ||
| 429 | |||
| 430 | B<RFC 2965> - "HTTP State Management Mechanism" found at ftp://ftp.isi.edu/in-notes/rfc2965.txt | ||
| 431 | |||
| 432 | =item * | ||
| 433 | |||
| 434 | L<CGI|CGI> - standard CGI library | ||
| 435 | |||
| 436 | =item * | ||
| 437 | |||
| 438 | L<Apache::Session|Apache::Session> - an alternative to CGI::Session | ||
| 439 | |||
| 440 | =back | ||
| 441 | |||
| 442 | =cut |