Annotation of loncom/interface/lonmysql.pm, revision 1.27
1.1 matthew 1: # The LearningOnline Network with CAPA
2: # MySQL utility functions
3: #
1.27 ! matthew 4: # $Id: lonmysql.pm,v 1.26 2005/02/09 21:21:08 matthew Exp $
1.1 matthew 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;
1.16 www 34: use POSIX qw(strftime mktime);
35:
1.21 matthew 36: my $mysqluser;
37: my $mysqlpassword;
1.27 ! matthew 38: my $mysqldatabase;
1.21 matthew 39:
40: sub set_mysql_user_and_password {
41: # If we are running under Apache and LONCAPA, use the LON-CAPA
42: # user and password. Otherwise...? ? ? ?
1.27 ! matthew 43: my ($input_mysqluser,$input_mysqlpassword,$input_mysqldatabase) = @_;
! 44: if (! defined($mysqldatabase)) {
! 45: $mysqldatabase = 'loncapa';
! 46: }
! 47: if (defined($input_mysqldatabase)) {
! 48: $mysqldatabase = $input_mysqldatabase;
! 49: }
1.21 matthew 50: if (! defined($mysqluser) || ! defined($mysqlpassword)) {
51: if (! eval 'require Apache::lonnet();') {
52: $mysqluser = 'www';
53: $mysqlpassword = $Apache::lonnet::perlvar{'lonSqlAccess'};
54: } else {
1.22 matthew 55: $mysqluser = '';
1.21 matthew 56: $mysqlpassword = '';
57: }
58: }
1.27 ! matthew 59: if (defined($input_mysqluser)) {
! 60: $mysqluser = $input_mysqluser;
! 61: }
! 62: if (defined($input_mysqlpassword)) {
! 63: $mysqlpassword = $input_mysqlpassword;
! 64: }
1.21 matthew 65: }
1.1 matthew 66:
67: ######################################################################
68: ######################################################################
69:
70: =pod
71:
72: =head1 Name
73:
74: lonmysql - LONCAPA MySQL utility functions
75:
76: =head1 Synopsis
77:
78: lonmysql contains utility functions to make accessing the mysql loncapa
79: database easier.
80:
81: =head1 Description
82:
83: lonmysql does its best to encapsulate all the database/table functions
84: and provide a common interface. The goal, however, is not to provide
85: a complete reimplementation of the DBI interface. Instead we try to
86: make using mysql as painless as possible.
87:
88: Each table has a numeric ID that is a parameter to most lonmysql functions.
89: The table id is returned by &create_table.
90: If you lose the table id, it is lost forever.
91: The table names in MySQL correspond to
92: $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.$table_id. If the table id
93: is non-numeric, it is assumed to be the full name of a table. If you pass
94: the table id in a form, you MUST ensure that what you send to lonmysql is
95: numeric, otherwise you are opening up all the tables in the MySQL database.
96:
97: =over 4
98:
99: =item Creating a table
100:
101: To create a table, you need a description of its structure. See the entry
102: for &create_table for a description of what is needed.
103:
104: $table_id = &create_table({
1.9 matthew 105: id => 'tableid', # usually you will use the returned id
106: columns => (
107: { name => 'id',
108: type => 'INT',
109: restrictions => 'NOT NULL',
110: primary_key => 'yes',
111: auto_inc => 'yes'
112: },
113: { name => 'verbage',
114: type => 'TEXT' },
115: ),
116: fulltext => [qw/verbage/],
1.3 matthew 117: });
1.1 matthew 118:
119: The above command will create a table with two columns, 'id' and 'verbage'.
120:
121: 'id' will be an integer which is autoincremented and non-null.
122:
123: 'verbage' will be of type 'TEXT', which (conceivably) allows any length
124: text string to be stored. Depending on your intentions for this database,
125: setting restrictions => 'NOT NULL' may help you avoid storing empty data.
126:
1.3 matthew 127: the fulltext element sets up the 'verbage' column for 'FULLTEXT' searching.
1.1 matthew 128:
129:
130:
131: =item Storing rows
132:
133: Storing a row in a table requires calling &store_row($table_id,$data)
134:
135: $data is either a hash reference or an array reference. If it is an array
136: reference, the data is passed as is (after being escaped) to the
137: "INSERT INTO <table> VALUES( ... )" SQL command. If $data is a hash reference,
138: the data will be placed into an array in the proper column order for the table
139: and then passed to the database.
140:
141: An example of inserting into the table created above is:
142:
143: &store_row($table_id,[undef,'I am not a crackpot!']);
144:
145: or equivalently,
146:
147: &store_row($table_id,{ verbage => 'I am not a crackpot!'});
148:
149: Since the above table was created with the first column ('id') as
150: autoincrement, providing a value is unnecessary even though the column was
151: marked as 'NOT NULL'.
152:
153:
154:
155: =item Retrieving rows
156:
157: Retrieving rows requires calling get_rows:
158:
159: @row = &Apache::lonmysql::get_rows($table_id,$condition)
160:
161: This results in the query "SELECT * FROM <table> HAVING $condition".
162:
163: @row = &Apache::lonmysql::get_rows($table_id,'id>20');
164:
165: returns all rows with column 'id' greater than 20.
166:
167: =back
168:
169: =cut
170:
171: ######################################################################
172: ######################################################################
173: =pod
174:
175: =head1 Package Variables
176:
177: =over 4
178:
179: =cut
180:
181: ##################################################
182: ##################################################
183:
184: =pod
185:
186: =item %Tables
187:
188: Holds information regarding the currently open connections. Each key
189: in the %Tables hash will be a unique table key. The value associated
190: with a key is a hash reference. Most values are initialized when the
191: table is created.
192:
193: The following entries are allowed in the hash reference:
194:
195: =over 4
196:
1.3 matthew 197: =item Name
198:
199: Table name.
200:
201: =item Type
202:
203: The type of table, typically MyISAM.
204:
205: =item Row_format
206:
207: Describes how rows should be stored in the table. DYNAMIC or STATIC.
208:
209: =item Create_time
210:
211: The date of the tables creation.
212:
213: =item Update_time
214:
215: The date of the last modification of the table.
216:
217: =item Check_time
218:
219: Usually NULL.
220:
221: =item Avg_row_length
222:
223: The average length of the rows.
224:
225: =item Data_length
226:
227: The length of the data stored in the table (bytes)
228:
229: =item Max_data_length
230:
231: The maximum possible size of the table (bytes).
1.1 matthew 232:
1.3 matthew 233: =item Index_length
1.1 matthew 234:
1.3 matthew 235: The length of the index for the table (bytes)
1.1 matthew 236:
1.3 matthew 237: =item Data_free
1.1 matthew 238:
1.3 matthew 239: I have no idea what this is.
1.1 matthew 240:
1.3 matthew 241: =item Comment
1.1 matthew 242:
1.3 matthew 243: The comment associated with the table.
244:
245: =item Rows
246:
247: The number of rows in the table.
248:
249: =item Auto_increment
250:
251: The value of the next auto_increment field.
252:
253: =item Create_options
254:
255: I have no idea.
256:
257: =item Col_order
258:
259: an array reference which holds the order of columns in the table.
260:
261: =item row_insert_sth
1.1 matthew 262:
263: The statement handler for row inserts.
264:
1.9 matthew 265: =item row_replace_sth
266:
267: The statement handler for row inserts.
268:
1.1 matthew 269: =back
270:
1.3 matthew 271: Col_order and row_insert_sth are kept internally by lonmysql and are not
272: part of the usual MySQL table information.
273:
1.1 matthew 274: =cut
275:
276: ##################################################
277: ##################################################
278: my %Tables;
279:
280: ##################################################
281: ##################################################
282: =pod
283:
284: =item $errorstring
285:
286: Holds the last error.
287:
288: =cut
289: ##################################################
290: ##################################################
291: my $errorstring;
292:
293: ##################################################
294: ##################################################
295: =pod
296:
297: =item $debugstring
298:
299: Describes current events within the package.
300:
301: =cut
302: ##################################################
303: ##################################################
304: my $debugstring;
305:
306: ##################################################
307: ##################################################
308:
309: =pod
310:
311: =item $dbh
312:
313: The database handler; The actual connection to MySQL via the perl DBI.
314:
315: =cut
316:
317: ##################################################
318: ##################################################
319: my $dbh;
320:
321: ##################################################
322: ##################################################
323:
324: # End of global variable declarations
325:
326: =pod
327:
328: =back
329:
330: =cut
331:
332: ######################################################################
333: ######################################################################
334:
335: =pod
336:
337: =head1 Internals
338:
339: =over 4
340:
341: =cut
342:
343: ######################################################################
344: ######################################################################
345:
346: =pod
347:
348: =item &connect_to_db()
349:
350: Inputs: none.
351:
352: Returns: undef on error, 1 on success.
353:
354: Checks to make sure the database has been connected to. If not, the
355: connection is established.
356:
357: =cut
358:
359: ###############################
360: sub connect_to_db {
361: return 1 if ($dbh);
1.21 matthew 362: if (! defined($mysqluser) || ! defined($mysqlpassword)) {
363: &set_mysql_user_and_password();
364: }
1.27 ! matthew 365: if (! ($dbh = DBI->connect("DBI:mysql:$mysqldatabase",$mysqluser,$mysqlpassword,
1.1 matthew 366: { RaiseError=>0,PrintError=>0}))) {
367: $debugstring = "Unable to connect to loncapa database.";
1.7 matthew 368: if (! defined($dbh)) {
369: $debugstring = "Unable to connect to loncapa database.";
370: $errorstring = "dbh was undefined.";
371: } elsif ($dbh->err) {
1.1 matthew 372: $errorstring = "Connection error: ".$dbh->errstr;
373: }
374: return undef;
375: }
376: $debugstring = "Successfully connected to loncapa database.";
1.13 matthew 377: return 1;
378: }
379:
380: ###############################
381:
382: =pod
383:
384: =item &verify_sql_connection()
385:
386: Inputs: none.
387:
388: Returns: 0 (failure) or 1 (success)
389:
390: Checks to make sure the database can be connected to. It does not
391: initialize anything in the lonmysql package.
392:
393: =cut
394:
395: ###############################
396: sub verify_sql_connection {
1.21 matthew 397: if (! defined($mysqluser) || ! defined($mysqlpassword)) {
398: &set_mysql_user_and_password();
399: }
1.13 matthew 400: my $connection;
1.21 matthew 401: if (! ($connection = DBI->connect("DBI:mysql:loncapa",
402: $mysqluser,$mysqlpassword,
1.13 matthew 403: { RaiseError=>0,PrintError=>0}))) {
404: return 0;
405: }
406: undef($connection);
1.1 matthew 407: return 1;
408: }
409:
410: ###############################
411:
412: =pod
413:
414: =item &disconnect_from_db()
415:
416: Inputs: none.
417:
418: Returns: Always returns 1.
419:
420: Severs the connection to the mysql database.
421:
422: =cut
423:
424: ###############################
425: sub disconnect_from_db {
426: foreach (keys(%Tables)) {
427: # Supposedly, having statement handlers running around after the
428: # database connection has been lost will cause trouble. So we
429: # kill them off just to be sure.
430: if (exists($Tables{$_}->{'row_insert_sth'})) {
431: delete($Tables{$_}->{'row_insert_sth'});
432: }
1.9 matthew 433: if (exists($Tables{$_}->{'row_replace_sth'})) {
434: delete($Tables{$_}->{'row_replace_sth'});
435: }
1.1 matthew 436: }
437: $dbh->disconnect if ($dbh);
438: $debugstring = "Disconnected from database.";
439: $dbh = undef;
440: return 1;
441: }
442:
443: ###############################
444:
445: =pod
446:
1.2 matthew 447: =item &number_of_rows()
1.1 matthew 448:
1.2 matthew 449: Input: table identifier
450:
1.3 matthew 451: Returns: the number of rows in the given table, undef on error.
1.1 matthew 452:
453: =cut
454:
455: ###############################
1.2 matthew 456: sub number_of_rows {
457: my ($table_id) = @_;
1.3 matthew 458: return undef if (! defined(&connect_to_db()));
459: return undef if (! defined(&update_table_info($table_id)));
460: return $Tables{&translate_id($table_id)}->{'Rows'};
1.10 matthew 461: }
462: ###############################
463:
464: =pod
465:
466: =item &get_dbh()
467:
468: Input: nothing
469:
470: Returns: the database handler, or undef on error.
471:
472: This routine allows the programmer to gain access to the database handler.
473: Be careful.
474:
475: =cut
476:
477: ###############################
478: sub get_dbh {
479: return undef if (! defined(&connect_to_db()));
480: return $dbh;
1.1 matthew 481: }
482:
483: ###############################
484:
485: =pod
486:
487: =item &get_error()
488:
489: Inputs: none.
490:
491: Returns: The last error reported.
492:
493: =cut
494:
495: ###############################
496: sub get_error {
497: return $errorstring;
498: }
499:
500: ###############################
501:
502: =pod
503:
504: =item &get_debug()
505:
506: Inputs: none.
507:
508: Returns: A string describing the internal state of the lonmysql package.
509:
510: =cut
511:
512: ###############################
513: sub get_debug {
514: return $debugstring;
515: }
516:
517: ###############################
518:
519: =pod
520:
1.8 matthew 521: =item &update_table_info()
1.1 matthew 522:
523: Inputs: table id
524:
1.3 matthew 525: Returns: undef on error, 1 on success.
1.1 matthew 526:
1.3 matthew 527: &update_table_info updates the %Tables hash with current information about
528: the given table.
529:
530: The default MySQL table status fields are:
1.1 matthew 531:
532: Name Type Row_format
533: Max_data_length Index_length Data_free
534: Create_time Update_time Check_time
535: Avg_row_length Data_length Comment
536: Rows Auto_increment Create_options
537:
1.3 matthew 538: Additionally, "Col_order" is updated as well.
539:
1.1 matthew 540: =cut
541:
542: ###############################
1.3 matthew 543: sub update_table_info {
1.1 matthew 544: my ($table_id) = @_;
1.3 matthew 545: return undef if (! defined(&connect_to_db()));
546: my $table_status = &check_table($table_id);
547: return undef if (! defined($table_status));
548: if (! $table_status) {
549: $errorstring = "table $table_id does not exist.";
550: return undef;
551: }
1.1 matthew 552: my $tablename = &translate_id($table_id);
1.3 matthew 553: #
554: # Get MySQLs table status information.
555: #
1.1 matthew 556: my @tabledesc = qw/
557: Name Type Row_format Rows Avg_row_length Data_length
558: Max_data_length Index_length Data_free Auto_increment
559: Create_time Update_time Check_time Create_options Comment /;
560: my $db_command = "SHOW TABLE STATUS FROM loncapa LIKE '$tablename'";
561: my $sth = $dbh->prepare($db_command);
562: $sth->execute();
563: if ($sth->err) {
564: $errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n".
565: $sth->errstr;
1.3 matthew 566: &disconnect_from_db();
1.1 matthew 567: return undef;
568: }
569: #
570: my @info=$sth->fetchrow_array;
571: for (my $i=0;$i<= $#info ; $i++) {
1.25 matthew 572: if ($tabledesc[$i] !~ /^(Create_|Update_|Check_)time$/) {
573: $Tables{$tablename}->{$tabledesc[$i]}=
574: &unsqltime($info[$i]);
575: } else {
576: $Tables{$tablename}->{$tabledesc[$i]}= $info[$i];
577: }
1.3 matthew 578: }
579: #
580: # Determine the column order
581: #
582: $db_command = "DESCRIBE $tablename";
1.5 matthew 583: $sth = $dbh->prepare($db_command);
1.3 matthew 584: $sth->execute();
585: if ($sth->err) {
586: $errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n".
587: $sth->errstr;
588: &disconnect_from_db();
589: return undef;
590: }
591: my $aref=$sth->fetchall_arrayref;
592: $Tables{$tablename}->{'Col_order'}=[]; # Clear values.
593: # The values we want are the 'Field' entries, the first column.
594: for (my $i=0;$i< @$aref ; $i++) {
595: push @{$Tables{$tablename}->{'Col_order'}},$aref->[$i]->[0];
1.1 matthew 596: }
597: #
598: $debugstring = "Retrieved table info for $tablename";
1.3 matthew 599: return 1;
1.1 matthew 600: }
1.25 matthew 601:
602: ###############################
603:
604: =pod
605:
606: =item &table_information()
607:
608: Inputs: table id
609:
610: Returns: hash with the table status
611:
612: =cut
613:
614: ###############################
615: sub table_information {
616: my $table_id=shift;
617: if (&update_table_info($table_id)) {
618: return %{$Tables{$table_id}};
619: } else {
620: return ();
621: }
622: }
623:
1.18 www 624: ###############################
625:
626: =pod
627:
628: =item &col_order()
629:
630: Inputs: table id
1.1 matthew 631:
1.18 www 632: Returns: array with column order
633:
634: =cut
635:
1.25 matthew 636: ###############################
1.18 www 637: sub col_order {
638: my $table_id=shift;
639: if (&update_table_info($table_id)) {
640: return @{$Tables{$table_id}->{'Col_order'}};
641: } else {
642: return ();
643: }
644: }
1.21 matthew 645:
1.1 matthew 646: ###############################
647:
648: =pod
649:
1.8 matthew 650: =item &create_table()
1.1 matthew 651:
652: Inputs:
1.21 matthew 653: table description, see &build_table_creation_request
654: Returns:
655: undef on error, table id on success.
656:
657: =cut
658:
659: ###############################
660: sub create_table {
661: return undef if (!defined(&connect_to_db($dbh)));
662: my ($table_des)=@_;
1.24 matthew 663: my ($request,$table_id) = &build_table_creation_request($table_des);
1.21 matthew 664: #
665: # Execute the request to create the table
666: #############################################
667: my $count = $dbh->do($request);
668: if (! defined($count)) {
669: $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n".
670: $dbh->errstr();
671: return undef;
672: }
673: my $tablename = &translate_id($table_id);
674: delete($Tables{$tablename}) if (exists($Tables{$tablename}));
675: return undef if (! defined(&update_table_info($table_id)));
676: $debugstring = "Created table $tablename at time ".time.
677: " with request\n$request";
678: return $table_id;
679: }
1.1 matthew 680:
1.21 matthew 681: ###############################
682:
683: =pod
684:
685: =item build_table_creation_request
686:
687: Input: table description
1.1 matthew 688:
689: table description = {
690: permanent => 'yes' or 'no',
1.8 matthew 691: columns => [
692: { name => 'colA',
693: type => mysql type,
694: restrictions => 'NOT NULL' or empty,
695: primary_key => 'yes' or empty,
696: auto_inc => 'yes' or empty,
697: },
698: { name => 'colB',
699: ...
700: },
701: { name => 'colC',
702: ...
703: },
704: ],
1.9 matthew 705: 'PRIMARY KEY' => (index_col_name,...),
1.11 matthew 706: KEY => [{ name => 'idx_name',
707: columns => (col1,col2,..),},],
708: INDEX => [{ name => 'idx_name',
709: columns => (col1,col2,..),},],
710: UNIQUE => [{ index => 'yes',
1.9 matthew 711: name => 'idx_name',
1.11 matthew 712: columns => (col1,col2,..),},],
713: FULLTEXT => [{ index => 'yes',
1.9 matthew 714: name => 'idx_name',
1.11 matthew 715: columns => (col1,col2,..),},],
1.9 matthew 716:
1.1 matthew 717: }
718:
1.21 matthew 719: Returns: scalar string containing mysql commands to create the table
1.1 matthew 720:
721: =cut
722:
723: ###############################
1.21 matthew 724: sub build_table_creation_request {
1.1 matthew 725: my ($table_des)=@_;
726: #
727: # Build request to create table
728: ##################################
729: my @Columns;
730: my $col_des;
1.9 matthew 731: my $table_id;
732: if (exists($table_des->{'id'})) {
733: $table_id = $table_des->{'id'};
734: } else {
735: $table_id = &get_new_table_id();
736: }
1.3 matthew 737: my $tablename = &translate_id($table_id);
1.1 matthew 738: my $request = "CREATE TABLE IF NOT EXISTS ".$tablename." ";
1.8 matthew 739: foreach my $coldata (@{$table_des->{'columns'}}) {
740: my $column = $coldata->{'name'};
741: next if (! defined($column));
1.1 matthew 742: $col_des = '';
1.3 matthew 743: if (lc($coldata->{'type'}) =~ /(enum|set)/) { # 'enum' or 'set'
1.1 matthew 744: $col_des.=$column." ".$coldata->{'type'}."('".
745: join("', '",@{$coldata->{'values'}})."')";
746: } else {
747: $col_des.=$column." ".$coldata->{'type'};
748: if (exists($coldata->{'size'})) {
749: $col_des.="(".$coldata->{'size'}.")";
750: }
751: }
752: # Modifiers
753: if (exists($coldata->{'restrictions'})){
754: $col_des.=" ".$coldata->{'restrictions'};
755: }
756: if (exists($coldata->{'default'})) {
757: $col_des.=" DEFAULT '".$coldata->{'default'}."'";
758: }
1.3 matthew 759: $col_des.=' AUTO_INCREMENT' if (exists($coldata->{'auto_inc'}) &&
760: ($coldata->{'auto_inc'} eq 'yes'));
761: $col_des.=' PRIMARY KEY' if (exists($coldata->{'primary_key'}) &&
762: ($coldata->{'primary_key'} eq 'yes'));
1.1 matthew 763: } continue {
764: # skip blank items.
765: push (@Columns,$col_des) if ($col_des ne '');
766: }
1.9 matthew 767: if (exists($table_des->{'PRIMARY KEY'})) {
768: push (@Columns,'PRIMARY KEY ('.join(',',@{$table_des->{'PRIMARY KEY'}})
769: .')');
770: }
1.11 matthew 771: #
772: foreach my $indextype ('KEY','INDEX') {
773: next if (!exists($table_des->{$indextype}));
774: foreach my $indexdescription (@{$table_des->{$indextype}}) {
775: my $text = $indextype.' ';
776: if (exists($indexdescription->{'name'})) {
777: $text .=$indexdescription->{'name'};
1.9 matthew 778: }
1.11 matthew 779: $text .= ' ('.join(',',@{$indexdescription->{'columns'}}).')';
1.9 matthew 780: push (@Columns,$text);
781: }
782: }
1.11 matthew 783: #
784: foreach my $indextype ('UNIQUE','FULLTEXT') {
785: next if (! exists($table_des->{$indextype}));
786: foreach my $indexdescription (@{$table_des->{$indextype}}) {
787: my $text = $indextype.' ';
788: if (exists($indexdescription->{'index'}) &&
789: $indexdescription->{'index'} eq 'yes') {
1.9 matthew 790: $text .= 'INDEX ';
791: }
1.11 matthew 792: if (exists($indexdescription->{'name'})) {
793: $text .=$indexdescription->{'name'};
1.9 matthew 794: }
1.11 matthew 795: $text .= ' ('.join(',',@{$indexdescription->{'columns'}}).')';
1.9 matthew 796: push (@Columns,$text);
797: }
1.3 matthew 798: }
1.11 matthew 799: #
1.1 matthew 800: $request .= "(".join(", ",@Columns).") ";
801: unless($table_des->{'permanent'} eq 'yes') {
802: $request.="COMMENT = 'temporary' ";
803: }
804: $request .= "TYPE=MYISAM";
1.24 matthew 805: return $request,$table_id;
1.1 matthew 806: }
807:
808: ###############################
809:
810: =pod
811:
1.8 matthew 812: =item &get_new_table_id()
1.1 matthew 813:
814: Used internally to prevent table name collisions.
815:
816: =cut
817:
818: ###############################
819: sub get_new_table_id {
820: my $newid = 0;
821: my @tables = &tables_in_db();
822: foreach (@tables) {
823: if (/^$ENV{'user.name'}_$ENV{'user.domain'}_(\d+)$/) {
824: $newid = $1 if ($1 > $newid);
825: }
826: }
827: return ++$newid;
828: }
829:
830: ###############################
831:
832: =pod
833:
1.8 matthew 834: =item &get_rows()
1.1 matthew 835:
836: Inputs: $table_id,$condition
837:
838: Returns: undef on error, an array ref to (array of) results on success.
839:
1.2 matthew 840: Internally, this function does a 'SELECT * FROM table WHERE $condition'.
1.1 matthew 841: $condition = 'id>0' will result in all rows where column 'id' has a value
842: greater than 0 being returned.
843:
844: =cut
845:
846: ###############################
847: sub get_rows {
848: my ($table_id,$condition) = @_;
1.3 matthew 849: return undef if (! defined(&connect_to_db()));
850: my $table_status = &check_table($table_id);
851: return undef if (! defined($table_status));
852: if (! $table_status) {
853: $errorstring = "table $table_id does not exist.";
854: return undef;
855: }
1.1 matthew 856: my $tablename = &translate_id($table_id);
1.9 matthew 857: my $request;
858: if (defined($condition) && $condition ne '') {
859: $request = 'SELECT * FROM '.$tablename.' WHERE '.$condition;
860: } else {
861: $request = 'SELECT * FROM '.$tablename;
862: $condition = 'no condition';
863: }
1.1 matthew 864: my $sth=$dbh->prepare($request);
865: $sth->execute();
866: if ($sth->err) {
867: $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n".
868: $sth->errstr;
869: $debugstring = "Failed to get rows matching $condition";
870: return undef;
871: }
872: $debugstring = "Got rows matching $condition";
873: my @Results = @{$sth->fetchall_arrayref};
874: return @Results;
875: }
876:
877: ###############################
878:
879: =pod
880:
1.8 matthew 881: =item &store_row()
1.1 matthew 882:
883: Inputs: table id, row data
884:
885: returns undef on error, 1 on success.
886:
887: =cut
888:
889: ###############################
890: sub store_row {
891: my ($table_id,$rowdata) = @_;
1.3 matthew 892: #
893: return undef if (! defined(&connect_to_db()));
894: my $table_status = &check_table($table_id);
895: return undef if (! defined($table_status));
896: if (! $table_status) {
897: $errorstring = "table $table_id does not exist.";
898: return undef;
899: }
900: #
1.1 matthew 901: my $tablename = &translate_id($table_id);
1.3 matthew 902: #
1.1 matthew 903: my $sth;
1.3 matthew 904: if (exists($Tables{$tablename}->{'row_insert_sth'})) {
905: $sth = $Tables{$tablename}->{'row_insert_sth'};
1.1 matthew 906: } else {
1.3 matthew 907: # Build the insert statement handler
908: return undef if (! defined(&update_table_info($table_id)));
1.1 matthew 909: my $insert_request = 'INSERT INTO '.$tablename.' VALUES(';
1.3 matthew 910: foreach (@{$Tables{$tablename}->{'Col_order'}}) {
1.1 matthew 911: $insert_request.="?,";
912: }
913: chop $insert_request;
914: $insert_request.=")";
915: $sth=$dbh->prepare($insert_request);
1.3 matthew 916: $Tables{$tablename}->{'row_insert_sth'}=$sth;
1.1 matthew 917: }
918: my @Parameters;
919: if (ref($rowdata) eq 'ARRAY') {
920: @Parameters = @$rowdata;
921: } elsif (ref($rowdata) eq 'HASH') {
1.3 matthew 922: foreach (@{$Tables{$tablename}->{'Col_order'}}) {
1.6 matthew 923: push(@Parameters,$rowdata->{$_});
1.1 matthew 924: }
925: }
926: $sth->execute(@Parameters);
927: if ($sth->err) {
928: $errorstring = "$dbh ATTEMPTED insert @Parameters RESULTING ERROR:\n".
929: $sth->errstr;
930: return undef;
931: }
932: $debugstring = "Stored row.";
933: return 1;
934: }
935:
1.23 matthew 936:
937: ###############################
938:
939: =pod
940:
941: =item &bulk_store_rows()
942:
943: Inputs: table id, [columns],[[row data1].[row data2],...]
944:
945: returns undef on error, 1 on success.
946:
947: =cut
948:
949: ###############################
950: sub bulk_store_rows {
951: my ($table_id,$columns,$rows) = @_;
952: #
953: return undef if (! defined(&connect_to_db()));
954: my $dbh = &get_dbh();
955: return undef if (! defined($dbh));
956: my $table_status = &check_table($table_id);
957: return undef if (! defined($table_status));
958: if (! $table_status) {
959: $errorstring = "table $table_id does not exist.";
960: return undef;
961: }
962: #
963: my $tablename = &translate_id($table_id);
964: #
965: my $request = 'INSERT IGNORE INTO '.$tablename.' ';
966: if (defined($columns) && ref($columns) eq 'ARRAY') {
967: $request .= join(',',@$columns).' ';
968: }
969: if (! defined($rows) || ref($rows) ne 'ARRAY') {
970: $errorstring = "no input rows given.";
971: return undef;
972: }
973: $request .= 'VALUES ';
974: foreach my $row (@$rows) {
975: # avoid doing row stuff here...
976: $request .= '('.join(',',@$row).'),';
977: }
978: $request =~ s/,$//;
979: $dbh->do($request);
980: if ($dbh->err) {
981: $errorstring = 'Attempted '.$/.$request.$/.'Got error '.$dbh->errstr();
982: return undef;
983: }
984: return 1;
985: }
986:
987:
1.9 matthew 988: ###############################
989:
990: =pod
991:
992: =item &replace_row()
993:
994: Inputs: table id, row data
995:
996: returns undef on error, 1 on success.
997:
998: Acts like &store_row() but uses the 'REPLACE' command instead of 'INSERT'.
999:
1000: =cut
1001:
1002: ###############################
1003: sub replace_row {
1004: my ($table_id,$rowdata) = @_;
1005: #
1006: return undef if (! defined(&connect_to_db()));
1007: my $table_status = &check_table($table_id);
1008: return undef if (! defined($table_status));
1009: if (! $table_status) {
1010: $errorstring = "table $table_id does not exist.";
1011: return undef;
1012: }
1013: #
1014: my $tablename = &translate_id($table_id);
1015: #
1016: my $sth;
1017: if (exists($Tables{$tablename}->{'row_replace_sth'})) {
1018: $sth = $Tables{$tablename}->{'row_replace_sth'};
1019: } else {
1020: # Build the insert statement handler
1021: return undef if (! defined(&update_table_info($table_id)));
1022: my $replace_request = 'REPLACE INTO '.$tablename.' VALUES(';
1023: foreach (@{$Tables{$tablename}->{'Col_order'}}) {
1024: $replace_request.="?,";
1025: }
1026: chop $replace_request;
1027: $replace_request.=")";
1028: $sth=$dbh->prepare($replace_request);
1029: $Tables{$tablename}->{'row_replace_sth'}=$sth;
1030: }
1031: my @Parameters;
1032: if (ref($rowdata) eq 'ARRAY') {
1033: @Parameters = @$rowdata;
1034: } elsif (ref($rowdata) eq 'HASH') {
1035: foreach (@{$Tables{$tablename}->{'Col_order'}}) {
1036: push(@Parameters,$rowdata->{$_});
1037: }
1038: }
1039: $sth->execute(@Parameters);
1040: if ($sth->err) {
1041: $errorstring = "$dbh ATTEMPTED replace @Parameters RESULTING ERROR:\n".
1042: $sth->errstr;
1043: return undef;
1044: }
1045: $debugstring = "Stored row.";
1046: return 1;
1047: }
1048:
1.1 matthew 1049: ###########################################
1050:
1051: =pod
1052:
1.8 matthew 1053: =item &tables_in_db()
1.1 matthew 1054:
1055: Returns a list containing the names of all the tables in the database.
1056: Returns undef on error.
1057:
1058: =cut
1059:
1060: ###########################################
1061: sub tables_in_db {
1.3 matthew 1062: return undef if (!defined(&connect_to_db()));
1.5 matthew 1063: my $sth=$dbh->prepare('SHOW TABLES');
1.1 matthew 1064: $sth->execute();
1.19 matthew 1065: $sth->execute();
1066: my $aref = $sth->fetchall_arrayref;
1067: if ($sth->err()) {
1068: $errorstring =
1069: "$dbh ATTEMPTED:\n".'fetchall_arrayref after SHOW TABLES'.
1.3 matthew 1070: "\nRESULTING ERROR:\n".$sth->errstr;
1.1 matthew 1071: return undef;
1072: }
1.19 matthew 1073: my @table_list;
1.1 matthew 1074: foreach (@$aref) {
1.19 matthew 1075: push(@table_list,$_->[0]);
1.1 matthew 1076: }
1.19 matthew 1077: $debugstring = "Got list of tables in DB: ".join(',',@table_list);
1078: return(@table_list);
1.1 matthew 1079: }
1080:
1081: ###########################################
1082:
1083: =pod
1084:
1.8 matthew 1085: =item &translate_id()
1.1 matthew 1086:
1087: Used internally to translate a numeric table id into a MySQL table name.
1088: If the input $id contains non-numeric characters it is assumed to have
1089: already been translated.
1090:
1091: Checks are NOT performed to see if the table actually exists.
1092:
1093: =cut
1094:
1095: ###########################################
1096: sub translate_id {
1097: my $id = shift;
1098: # id should be a digit. If it is not a digit we assume the given id
1099: # is complete and does not need to be translated.
1100: return $id if ($id =~ /\D/);
1101: return $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.$id;
1102: }
1103:
1104: ###########################################
1105:
1106: =pod
1107:
1.8 matthew 1108: =item &check_table()
1109:
1110: Input: table id
1.1 matthew 1111:
1112: Checks to see if the requested table exists. Returns 0 (no), 1 (yes), or
1113: undef (error).
1114:
1115: =cut
1116:
1117: ###########################################
1118: sub check_table {
1119: my $table_id = shift;
1.3 matthew 1120: return undef if (!defined(&connect_to_db()));
1121: #
1.1 matthew 1122: $table_id = &translate_id($table_id);
1123: my @Table_list = &tables_in_db();
1124: my $result = 0;
1125: foreach (@Table_list) {
1.9 matthew 1126: if ($_ eq $table_id) {
1.1 matthew 1127: $result = 1;
1128: last;
1129: }
1130: }
1131: # If it does not exist, make sure we do not have it listed in %Tables
1132: delete($Tables{$table_id}) if ((! $result) && exists($Tables{$table_id}));
1133: $debugstring = "check_table returned $result for $table_id";
1134: return $result;
1135: }
1136:
1.5 matthew 1137: ###########################################
1138:
1139: =pod
1140:
1.8 matthew 1141: =item &remove_from_table()
1142:
1143: Input: $table_id, $column, $value
1144:
1145: Returns: the number of rows deleted. undef on error.
1.5 matthew 1146:
1147: Executes a "delete from $tableid where $column like binary '$value'".
1148:
1149: =cut
1150:
1151: ###########################################
1152: sub remove_from_table {
1153: my ($table_id,$column,$value) = @_;
1154: return undef if (!defined(&connect_to_db()));
1155: #
1156: $table_id = &translate_id($table_id);
1.17 www 1157: my $command = 'DELETE FROM '.$table_id.' WHERE '.$column.
1.5 matthew 1158: " LIKE BINARY ".$dbh->quote($value);
1159: my $sth = $dbh->prepare($command);
1.17 www 1160: unless ($sth->execute()) {
1.5 matthew 1161: $errorstring = "ERROR on execution of ".$command."\n".$sth->errstr;
1162: return undef;
1163: }
1.12 matthew 1164: $debugstring = $command;
1.5 matthew 1165: my $rows = $sth->rows;
1166: return $rows;
1167: }
1168:
1.14 matthew 1169: ###########################################
1170:
1171: =pod
1172:
1173: =item drop_table($table_id)
1174:
1175: Issues a 'drop table if exists' command
1176:
1177: =cut
1178:
1179: ###########################################
1180:
1181: sub drop_table {
1182: my ($table_id) = @_;
1183: return undef if (!defined(&connect_to_db()));
1184: #
1185: $table_id = &translate_id($table_id);
1186: my $command = 'DROP TABLE IF EXISTS '.$table_id;
1187: my $sth = $dbh->prepare($command);
1188: $sth->execute();
1189: if ($sth->err) {
1190: $errorstring = "ERROR on execution of ".$command."\n".$sth->errstr;
1191: return undef;
1192: }
1193: $debugstring = $command;
1.15 matthew 1194: delete($Tables{$table_id}); # remove any knowledge of the table
1.14 matthew 1195: return 1; # if we got here there was no error, so return a 'true' value
1196: }
1.16 www 1197:
1.26 matthew 1198: ##########################################
1.16 www 1199:
1.26 matthew 1200: =pod
1201:
1202: =item fix_table_name
1203:
1204: Fixes a table name so that it will work with MySQL.
1205:
1206: =cut
1207:
1208: ##########################################
1209: sub fix_table_name {
1210: my ($name) = @_;
1211: $name =~ s/^(\d+e\d+)/_$1/;
1212: return $name;
1213: }
1.16 www 1214:
1215:
1216: # ---------------------------- convert 'time' format into a datetime sql format
1217: sub sqltime {
1218: my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
1219: localtime(&unsqltime($_[0]));
1220: $mon++; $year+=1900;
1221: return "$year-$mon-$mday $hour:$min:$sec";
1222: }
1223:
1224: sub maketime {
1225: my %th=@_;
1226: return POSIX::mktime(($th{'seconds'},$th{'minutes'},$th{'hours'},
1227: $th{'day'},$th{'month'}-1,
1228: $th{'year'}-1900,0,0,$th{'dlsav'}));
1229: }
1230:
1231:
1232: #########################################
1233: #
1234: # Retro-fixing of un-backward-compatible time format
1235:
1236: sub unsqltime {
1237: my $timestamp=shift;
1238: if ($timestamp=~/^(\d+)\-(\d+)\-(\d+)\s+(\d+)\:(\d+)\:(\d+)$/) {
1239: $timestamp=&maketime('year'=>$1,'month'=>$2,'day'=>$3,
1240: 'hours'=>$4,'minutes'=>$5,'seconds'=>$6);
1241: }
1242: return $timestamp;
1243: }
1244:
1.5 matthew 1245:
1.1 matthew 1246: 1;
1247:
1248: __END__;
1.5 matthew 1249:
1250: =pod
1251:
1252: =back
1253:
1254: =cut
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to lonmysql.pm CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom / interface