loncom/interface/lonmysql.pm - view

File: [LON-CAPA] / loncom / interface / lonmysql.pm
Revision 1.8: download - view: text, annotated - select for diffs
Mon Mar 10 21:22:36 2003 UTC (21 years, 3 months ago) by matthew
Branches: MAIN
CVS tags: HEAD

Modified data structure expected by lonmysql::create_table.
$table->{'columns'} is now an array ref instead of a hash ref.

Cleaned up POD in lonmysql.pm

lonsearchcat.pm has been tested and does work.

1: # The LearningOnline Network with CAPA 2: # MySQL utility functions 3: # 4: # $Id: lonmysql.pm,v 1.8 2003/03/10 21:22:36 matthew Exp $ 5: # 6: # Copyright Michigan State University Board of Trustees 7: # 8: # This file is part of the LearningOnline Network with CAPA (LON-CAPA). 9: # 10: # LON-CAPA is free software; you can redistribute it and/or modify 11: # it under the terms of the GNU General Public License as published by 12: # the Free Software Foundation; either version 2 of the License, or 13: # (at your option) any later version. 14: # 15: # LON-CAPA is distributed in the hope that it will be useful, 16: # but WITHOUT ANY WARRANTY; without even the implied warranty of 17: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18: # GNU General Public License for more details. 19: # 20: # You should have received a copy of the GNU General Public License 21: # along with LON-CAPA; if not, write to the Free Software 22: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 23: # 24: # /home/httpd/html/adm/gpl.txt 25: # 26: # http://www.lon-capa.org/ 27: # 28: ###################################################################### 29: 30: package Apache::lonmysql; 31: 32: use strict; 33: use DBI; 34: use Apache::lonnet(); 35: 36: ###################################################################### 37: ###################################################################### 38: 39: =pod 40: 41: =head1 Name 42: 43: lonmysql - LONCAPA MySQL utility functions 44: 45: =head1 Synopsis 46: 47: lonmysql contains utility functions to make accessing the mysql loncapa 48: database easier. 49: 50: =head1 Description 51: 52: lonmysql does its best to encapsulate all the database/table functions 53: and provide a common interface. The goal, however, is not to provide 54: a complete reimplementation of the DBI interface. Instead we try to 55: make using mysql as painless as possible. 56: 57: Each table has a numeric ID that is a parameter to most lonmysql functions. 58: The table id is returned by &create_table. 59: If you lose the table id, it is lost forever. 60: The table names in MySQL correspond to 61: $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.$table_id. If the table id 62: is non-numeric, it is assumed to be the full name of a table. If you pass 63: the table id in a form, you MUST ensure that what you send to lonmysql is 64: numeric, otherwise you are opening up all the tables in the MySQL database. 65: 66: =over 4 67: 68: =item Creating a table 69: 70: To create a table, you need a description of its structure. See the entry 71: for &create_table for a description of what is needed. 72: 73: $table_id = &create_table({ 74: columns => { 75: id => { 76: type => 'INT', 77: restrictions => 'NOT NULL', 78: primary_key => 'yes', 79: auto_inc => 'yes' 80: } 81: verbage => { type => 'TEXT' }, 82: }, 83: column_order => [qw/id verbage idx_verbage/], 84: fulltext => [qw/verbage/], 85: }); 86: 87: The above command will create a table with two columns, 'id' and 'verbage'. 88: 89: 'id' will be an integer which is autoincremented and non-null. 90: 91: 'verbage' will be of type 'TEXT', which (conceivably) allows any length 92: text string to be stored. Depending on your intentions for this database, 93: setting restrictions => 'NOT NULL' may help you avoid storing empty data. 94: 95: the fulltext element sets up the 'verbage' column for 'FULLTEXT' searching. 96: 97: 98: 99: =item Storing rows 100: 101: Storing a row in a table requires calling &store_row($table_id,$data) 102: 103: $data is either a hash reference or an array reference. If it is an array 104: reference, the data is passed as is (after being escaped) to the 105: "INSERT INTO <table> VALUES( ... )" SQL command. If $data is a hash reference, 106: the data will be placed into an array in the proper column order for the table 107: and then passed to the database. 108: 109: An example of inserting into the table created above is: 110: 111: &store_row($table_id,[undef,'I am not a crackpot!']); 112: 113: or equivalently, 114: 115: &store_row($table_id,{ verbage => 'I am not a crackpot!'}); 116: 117: Since the above table was created with the first column ('id') as 118: autoincrement, providing a value is unnecessary even though the column was 119: marked as 'NOT NULL'. 120: 121: 122: 123: =item Retrieving rows 124: 125: Retrieving rows requires calling get_rows: 126: 127: @row = &Apache::lonmysql::get_rows($table_id,$condition) 128: 129: This results in the query "SELECT * FROM <table> HAVING $condition". 130: 131: @row = &Apache::lonmysql::get_rows($table_id,'id>20'); 132: 133: returns all rows with column 'id' greater than 20. 134: 135: =back 136: 137: =cut 138: 139: ###################################################################### 140: ###################################################################### 141: =pod 142: 143: =head1 Package Variables 144: 145: =over 4 146: 147: =cut 148: 149: ################################################## 150: ################################################## 151: 152: =pod 153: 154: =item %Tables 155: 156: Holds information regarding the currently open connections. Each key 157: in the %Tables hash will be a unique table key. The value associated 158: with a key is a hash reference. Most values are initialized when the 159: table is created. 160: 161: The following entries are allowed in the hash reference: 162: 163: =over 4 164: 165: =item Name 166: 167: Table name. 168: 169: =item Type 170: 171: The type of table, typically MyISAM. 172: 173: =item Row_format 174: 175: Describes how rows should be stored in the table. DYNAMIC or STATIC. 176: 177: =item Create_time 178: 179: The date of the tables creation. 180: 181: =item Update_time 182: 183: The date of the last modification of the table. 184: 185: =item Check_time 186: 187: Usually NULL. 188: 189: =item Avg_row_length 190: 191: The average length of the rows. 192: 193: =item Data_length 194: 195: The length of the data stored in the table (bytes) 196: 197: =item Max_data_length 198: 199: The maximum possible size of the table (bytes). 200: 201: =item Index_length 202: 203: The length of the index for the table (bytes) 204: 205: =item Data_free 206: 207: I have no idea what this is. 208: 209: =item Comment 210: 211: The comment associated with the table. 212: 213: =item Rows 214: 215: The number of rows in the table. 216: 217: =item Auto_increment 218: 219: The value of the next auto_increment field. 220: 221: =item Create_options 222: 223: I have no idea. 224: 225: =item Col_order 226: 227: an array reference which holds the order of columns in the table. 228: 229: =item row_insert_sth 230: 231: The statement handler for row inserts. 232: 233: =back 234: 235: Col_order and row_insert_sth are kept internally by lonmysql and are not 236: part of the usual MySQL table information. 237: 238: =cut 239: 240: ################################################## 241: ################################################## 242: my %Tables; 243: 244: ################################################## 245: ################################################## 246: =pod 247: 248: =item $errorstring 249: 250: Holds the last error. 251: 252: =cut 253: ################################################## 254: ################################################## 255: my $errorstring; 256: 257: ################################################## 258: ################################################## 259: =pod 260: 261: =item $debugstring 262: 263: Describes current events within the package. 264: 265: =cut 266: ################################################## 267: ################################################## 268: my $debugstring; 269: 270: ################################################## 271: ################################################## 272: 273: =pod 274: 275: =item $dbh 276: 277: The database handler; The actual connection to MySQL via the perl DBI. 278: 279: =cut 280: 281: ################################################## 282: ################################################## 283: my $dbh; 284: 285: ################################################## 286: ################################################## 287: 288: # End of global variable declarations 289: 290: =pod 291: 292: =back 293: 294: =cut 295: 296: ###################################################################### 297: ###################################################################### 298: 299: =pod 300: 301: =head1 Internals 302: 303: =over 4 304: 305: =cut 306: 307: ###################################################################### 308: ###################################################################### 309: 310: =pod 311: 312: =item &connect_to_db() 313: 314: Inputs: none. 315: 316: Returns: undef on error, 1 on success. 317: 318: Checks to make sure the database has been connected to. If not, the 319: connection is established. 320: 321: =cut 322: 323: ############################### 324: sub connect_to_db { 325: return 1 if ($dbh); 326: if (! ($dbh = DBI->connect("DBI:mysql:loncapa","www", 327: $Apache::lonnet::perlvar{'lonSqlAccess'}, 328: { RaiseError=>0,PrintError=>0}))) { 329: $debugstring = "Unable to connect to loncapa database."; 330: if (! defined($dbh)) { 331: $debugstring = "Unable to connect to loncapa database."; 332: $errorstring = "dbh was undefined."; 333: } elsif ($dbh->err) { 334: $errorstring = "Connection error: ".$dbh->errstr; 335: } 336: return undef; 337: } 338: $debugstring = "Successfully connected to loncapa database."; 339: return 1; 340: } 341: 342: ############################### 343: 344: =pod 345: 346: =item &disconnect_from_db() 347: 348: Inputs: none. 349: 350: Returns: Always returns 1. 351: 352: Severs the connection to the mysql database. 353: 354: =cut 355: 356: ############################### 357: sub disconnect_from_db { 358: foreach (keys(%Tables)) { 359: # Supposedly, having statement handlers running around after the 360: # database connection has been lost will cause trouble. So we 361: # kill them off just to be sure. 362: if (exists($Tables{$_}->{'row_insert_sth'})) { 363: delete($Tables{$_}->{'row_insert_sth'}); 364: } 365: } 366: $dbh->disconnect if ($dbh); 367: $debugstring = "Disconnected from database."; 368: $dbh = undef; 369: return 1; 370: } 371: 372: ############################### 373: 374: =pod 375: 376: =item &number_of_rows() 377: 378: Input: table identifier 379: 380: Returns: the number of rows in the given table, undef on error. 381: 382: =cut 383: 384: ############################### 385: sub number_of_rows { 386: my ($table_id) = @_; 387: return undef if (! defined(&connect_to_db())); 388: return undef if (! defined(&update_table_info($table_id))); 389: return $Tables{&translate_id($table_id)}->{'Rows'}; 390: } 391: 392: ############################### 393: 394: =pod 395: 396: =item &get_error() 397: 398: Inputs: none. 399: 400: Returns: The last error reported. 401: 402: =cut 403: 404: ############################### 405: sub get_error { 406: return $errorstring; 407: } 408: 409: ############################### 410: 411: =pod 412: 413: =item &get_debug() 414: 415: Inputs: none. 416: 417: Returns: A string describing the internal state of the lonmysql package. 418: 419: =cut 420: 421: ############################### 422: sub get_debug { 423: return $debugstring; 424: } 425: 426: ############################### 427: 428: =pod 429: 430: =item &update_table_info() 431: 432: Inputs: table id 433: 434: Returns: undef on error, 1 on success. 435: 436: &update_table_info updates the %Tables hash with current information about 437: the given table. 438: 439: The default MySQL table status fields are: 440: 441: Name Type Row_format 442: Max_data_length Index_length Data_free 443: Create_time Update_time Check_time 444: Avg_row_length Data_length Comment 445: Rows Auto_increment Create_options 446: 447: Additionally, "Col_order" is updated as well. 448: 449: =cut 450: 451: ############################### 452: sub update_table_info { 453: my ($table_id) = @_; 454: return undef if (! defined(&connect_to_db())); 455: my $table_status = &check_table($table_id); 456: return undef if (! defined($table_status)); 457: if (! $table_status) { 458: $errorstring = "table $table_id does not exist."; 459: return undef; 460: } 461: my $tablename = &translate_id($table_id); 462: # 463: # Get MySQLs table status information. 464: # 465: my @tabledesc = qw/ 466: Name Type Row_format Rows Avg_row_length Data_length 467: Max_data_length Index_length Data_free Auto_increment 468: Create_time Update_time Check_time Create_options Comment /; 469: my $db_command = "SHOW TABLE STATUS FROM loncapa LIKE '$tablename'"; 470: my $sth = $dbh->prepare($db_command); 471: $sth->execute(); 472: if ($sth->err) { 473: $errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n". 474: $sth->errstr; 475: &disconnect_from_db(); 476: return undef; 477: } 478: # 479: my @info=$sth->fetchrow_array; 480: for (my $i=0;$i<= $#info ; $i++) { 481: $Tables{$tablename}->{$tabledesc[$i]}= $info[$i]; 482: } 483: # 484: # Determine the column order 485: # 486: $db_command = "DESCRIBE $tablename"; 487: $sth = $dbh->prepare($db_command); 488: $sth->execute(); 489: if ($sth->err) { 490: $errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n". 491: $sth->errstr; 492: &disconnect_from_db(); 493: return undef; 494: } 495: my $aref=$sth->fetchall_arrayref; 496: $Tables{$tablename}->{'Col_order'}=[]; # Clear values. 497: # The values we want are the 'Field' entries, the first column. 498: for (my $i=0;$i< @$aref ; $i++) { 499: push @{$Tables{$tablename}->{'Col_order'}},$aref->[$i]->[0]; 500: } 501: # 502: $debugstring = "Retrieved table info for $tablename"; 503: return 1; 504: } 505: 506: ############################### 507: 508: =pod 509: 510: =item &create_table() 511: 512: Inputs: 513: table description 514: 515: Input formats: 516: 517: table description = { 518: permanent => 'yes' or 'no', 519: columns => [ 520: { name => 'colA', 521: type => mysql type, 522: restrictions => 'NOT NULL' or empty, 523: primary_key => 'yes' or empty, 524: auto_inc => 'yes' or empty, 525: }, 526: { name => 'colB', 527: ... 528: }, 529: { name => 'colC', 530: ... 531: }, 532: ], 533: } 534: 535: Returns: 536: undef on error, table id on success. 537: 538: =cut 539: 540: ############################### 541: sub create_table { 542: return undef if (!defined(&connect_to_db($dbh))); 543: my ($table_des)=@_; 544: # 545: # Build request to create table 546: ################################## 547: my @Columns; 548: my $col_des; 549: my $table_id = &get_new_table_id(); 550: my $tablename = &translate_id($table_id); 551: my $request = "CREATE TABLE IF NOT EXISTS ".$tablename." "; 552: foreach my $coldata (@{$table_des->{'columns'}}) { 553: my $column = $coldata->{'name'}; 554: next if (! defined($column)); 555: $col_des = ''; 556: if (lc($coldata->{'type'}) =~ /(enum|set)/) { # 'enum' or 'set' 557: $col_des.=$column." ".$coldata->{'type'}."('". 558: join("', '",@{$coldata->{'values'}})."')"; 559: } else { 560: $col_des.=$column." ".$coldata->{'type'}; 561: if (exists($coldata->{'size'})) { 562: $col_des.="(".$coldata->{'size'}.")"; 563: } 564: } 565: # Modifiers 566: if (exists($coldata->{'restrictions'})){ 567: $col_des.=" ".$coldata->{'restrictions'}; 568: } 569: if (exists($coldata->{'default'})) { 570: $col_des.=" DEFAULT '".$coldata->{'default'}."'"; 571: } 572: $col_des.=' AUTO_INCREMENT' if (exists($coldata->{'auto_inc'}) && 573: ($coldata->{'auto_inc'} eq 'yes')); 574: $col_des.=' PRIMARY KEY' if (exists($coldata->{'primary_key'}) && 575: ($coldata->{'primary_key'} eq 'yes')); 576: } continue { 577: # skip blank items. 578: push (@Columns,$col_des) if ($col_des ne ''); 579: } 580: if (exists($table_des->{'fulltext'}) && (@{$table_des->{'fulltext'}})) { 581: push (@Columns,'FULLTEXT ('.join(',',@{$table_des->{'fulltext'}}).')'); 582: } 583: $request .= "(".join(", ",@Columns).") "; 584: unless($table_des->{'permanent'} eq 'yes') { 585: $request.="COMMENT = 'temporary' "; 586: } 587: $request .= "TYPE=MYISAM"; 588: # 589: # Execute the request to create the table 590: ############################################# 591: my $count = $dbh->do($request); 592: if (! defined($count)) { 593: $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n"; 594: return undef; 595: } 596: # 597: # Set up the internal bookkeeping 598: ############################################# 599: delete($Tables{$tablename}) if (exists($Tables{$tablename})); 600: return undef if (! defined(&update_table_info($table_id))); 601: $debugstring = "Created table $tablename at time ".time. 602: " with request\n$request"; 603: return $table_id; 604: } 605: 606: ############################### 607: 608: =pod 609: 610: =item &get_new_table_id() 611: 612: Used internally to prevent table name collisions. 613: 614: =cut 615: 616: ############################### 617: sub get_new_table_id { 618: my $newid = 0; 619: my @tables = &tables_in_db(); 620: foreach (@tables) { 621: if (/^$ENV{'user.name'}_$ENV{'user.domain'}_(\d+)$/) { 622: $newid = $1 if ($1 > $newid); 623: } 624: } 625: return ++$newid; 626: } 627: 628: ############################### 629: 630: =pod 631: 632: =item &get_rows() 633: 634: Inputs: $table_id,$condition 635: 636: Returns: undef on error, an array ref to (array of) results on success. 637: 638: Internally, this function does a 'SELECT * FROM table WHERE $condition'. 639: $condition = 'id>0' will result in all rows where column 'id' has a value 640: greater than 0 being returned. 641: 642: =cut 643: 644: ############################### 645: sub get_rows { 646: my ($table_id,$condition) = @_; 647: return undef if (! defined(&connect_to_db())); 648: my $table_status = &check_table($table_id); 649: return undef if (! defined($table_status)); 650: if (! $table_status) { 651: $errorstring = "table $table_id does not exist."; 652: return undef; 653: } 654: my $tablename = &translate_id($table_id); 655: my $request = 'SELECT * FROM '.$tablename.' WHERE '.$condition; 656: my $sth=$dbh->prepare($request); 657: $sth->execute(); 658: if ($sth->err) { 659: $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n". 660: $sth->errstr; 661: $debugstring = "Failed to get rows matching $condition"; 662: return undef; 663: } 664: $debugstring = "Got rows matching $condition"; 665: my @Results = @{$sth->fetchall_arrayref}; 666: return @Results; 667: } 668: 669: ############################### 670: 671: =pod 672: 673: =item &store_row() 674: 675: Inputs: table id, row data 676: 677: returns undef on error, 1 on success. 678: 679: =cut 680: 681: ############################### 682: sub store_row { 683: my ($table_id,$rowdata) = @_; 684: # 685: return undef if (! defined(&connect_to_db())); 686: my $table_status = &check_table($table_id); 687: return undef if (! defined($table_status)); 688: if (! $table_status) { 689: $errorstring = "table $table_id does not exist."; 690: return undef; 691: } 692: # 693: my $tablename = &translate_id($table_id); 694: # 695: my $sth; 696: if (exists($Tables{$tablename}->{'row_insert_sth'})) { 697: $sth = $Tables{$tablename}->{'row_insert_sth'}; 698: } else { 699: # Build the insert statement handler 700: return undef if (! defined(&update_table_info($table_id))); 701: my $insert_request = 'INSERT INTO '.$tablename.' VALUES('; 702: foreach (@{$Tables{$tablename}->{'Col_order'}}) { 703: $insert_request.="?,"; 704: } 705: chop $insert_request; 706: $insert_request.=")"; 707: $sth=$dbh->prepare($insert_request); 708: $Tables{$tablename}->{'row_insert_sth'}=$sth; 709: } 710: my @Parameters; 711: if (ref($rowdata) eq 'ARRAY') { 712: @Parameters = @$rowdata; 713: } elsif (ref($rowdata) eq 'HASH') { 714: foreach (@{$Tables{$tablename}->{'Col_order'}}) { 715: push(@Parameters,$rowdata->{$_}); 716: } 717: } 718: $sth->execute(@Parameters); 719: if ($sth->err) { 720: $errorstring = "$dbh ATTEMPTED insert @Parameters RESULTING ERROR:\n". 721: $sth->errstr; 722: return undef; 723: } 724: $debugstring = "Stored row."; 725: return 1; 726: } 727: 728: ########################################### 729: 730: =pod 731: 732: =item &tables_in_db() 733: 734: Returns a list containing the names of all the tables in the database. 735: Returns undef on error. 736: 737: =cut 738: 739: ########################################### 740: sub tables_in_db { 741: return undef if (!defined(&connect_to_db())); 742: my $sth=$dbh->prepare('SHOW TABLES'); 743: $sth->execute(); 744: if ($sth->err) { 745: $errorstring = "$dbh ATTEMPTED:\n".'SHOW TABLES'. 746: "\nRESULTING ERROR:\n".$sth->errstr; 747: return undef; 748: } 749: my $aref = $sth->fetchall_arrayref; 750: my @table_list=(); 751: foreach (@$aref) { 752: push @table_list,$_->[0]; 753: } 754: $debugstring = "Got list of tables in DB: @table_list"; 755: return @table_list; 756: } 757: 758: ########################################### 759: 760: =pod 761: 762: =item &translate_id() 763: 764: Used internally to translate a numeric table id into a MySQL table name. 765: If the input $id contains non-numeric characters it is assumed to have 766: already been translated. 767: 768: Checks are NOT performed to see if the table actually exists. 769: 770: =cut 771: 772: ########################################### 773: sub translate_id { 774: my $id = shift; 775: # id should be a digit. If it is not a digit we assume the given id 776: # is complete and does not need to be translated. 777: return $id if ($id =~ /\D/); 778: return $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.$id; 779: } 780: 781: ########################################### 782: 783: =pod 784: 785: =item &check_table() 786: 787: Input: table id 788: 789: Checks to see if the requested table exists. Returns 0 (no), 1 (yes), or 790: undef (error). 791: 792: =cut 793: 794: ########################################### 795: sub check_table { 796: my $table_id = shift; 797: return undef if (!defined(&connect_to_db())); 798: # 799: $table_id = &translate_id($table_id); 800: my @Table_list = &tables_in_db(); 801: my $result = 0; 802: foreach (@Table_list) { 803: if (/^$table_id$/) { 804: $result = 1; 805: last; 806: } 807: } 808: # If it does not exist, make sure we do not have it listed in %Tables 809: delete($Tables{$table_id}) if ((! $result) && exists($Tables{$table_id})); 810: $debugstring = "check_table returned $result for $table_id"; 811: return $result; 812: } 813: 814: ########################################### 815: 816: =pod 817: 818: =item &remove_from_table() 819: 820: Input: $table_id, $column, $value 821: 822: Returns: the number of rows deleted. undef on error. 823: 824: Executes a "delete from $tableid where $column like binary '$value'". 825: 826: =cut 827: 828: ########################################### 829: sub remove_from_table { 830: my ($table_id,$column,$value) = @_; 831: return undef if (!defined(&connect_to_db())); 832: # 833: $table_id = &translate_id($table_id); 834: my $command = 'DELETE FROM '.$table_id.' WHERE '.$dbh->quote($column). 835: " LIKE BINARY ".$dbh->quote($value); 836: my $sth = $dbh->prepare($command); 837: $sth->execute(); 838: if ($sth->err) { 839: $errorstring = "ERROR on execution of ".$command."\n".$sth->errstr; 840: return undef; 841: } 842: my $rows = $sth->rows; 843: return $rows; 844: } 845: 846: 847: 1; 848: 849: __END__; 850: 851: =pod 852: 853: =back 854: 855: =cut