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