[LON-CAPA-cvs] cvs: loncom /interface lonmysql.pm
matthew
lon-capa-cvs@mail.lon-capa.org
Tue, 30 Jul 2002 18:26:40 -0000
This is a MIME encoded message
--matthew1028053600
Content-Type: text/plain
matthew Tue Jul 30 14:26:40 2002 EDT
Modified files:
/loncom/interface lonmysql.pm
Log:
Many changes to documentation and many code cleanups. In particular, the
package variable %Tables was redefined.
&get_table_info was renamed &update_table_info and completely rewritten.
&create_table now has a new calling structure.
More error checking has been introduced to many functions.
--matthew1028053600
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20020730142640.txt"
Index: loncom/interface/lonmysql.pm
diff -u loncom/interface/lonmysql.pm:1.2 loncom/interface/lonmysql.pm:1.3
--- loncom/interface/lonmysql.pm:1.2 Sun Jul 28 14:21:13 2002
+++ loncom/interface/lonmysql.pm Tue Jul 30 14:26:40 2002
@@ -1,7 +1,7 @@
# The LearningOnline Network with CAPA
# MySQL utility functions
#
-# $Id: lonmysql.pm,v 1.2 2002/07/28 18:21:13 matthew Exp $
+# $Id: lonmysql.pm,v 1.3 2002/07/30 18:26:40 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -79,12 +79,10 @@
auto_inc => 'yes'
}
verbage => { type => 'TEXT' },
- idx_verbage => { type => 'FULLTEXT',
- target => 'verbage'
- }
},
- column_order => [qw/id verbage idx_verbage/]
- });
+ column_order => [qw/id verbage idx_verbage/],
+ fulltext => [qw/verbage/],
+ });
The above command will create a table with two columns, 'id' and 'verbage'.
@@ -94,7 +92,7 @@
text string to be stored. Depending on your intentions for this database,
setting restrictions => 'NOT NULL' may help you avoid storing empty data.
-'idx_verbage' sets up the 'verbage' column for 'FULLTEXT' searching.
+the fulltext element sets up the 'verbage' column for 'FULLTEXT' searching.
@@ -120,10 +118,6 @@
autoincrement, providing a value is unnecessary even though the column was
marked as 'NOT NULL'.
-In the future an array of arrays or hashes may be supported, but currently
-the system only performs one insert at a time. Given the nature of this
-interface, transactions (locking of the table) are not supported.
-
=item Retrieving rows
@@ -168,24 +162,79 @@
=over 4
-=item columns
+=item Name
+
+Table name.
+
+=item Type
+
+The type of table, typically MyISAM.
+
+=item Row_format
+
+Describes how rows should be stored in the table. DYNAMIC or STATIC.
+
+=item Create_time
+
+The date of the tables creation.
+
+=item Update_time
+
+The date of the last modification of the table.
+
+=item Check_time
+
+Usually NULL.
+
+=item Avg_row_length
+
+The average length of the rows.
+
+=item Data_length
+
+The length of the data stored in the table (bytes)
+
+=item Max_data_length
+
+The maximum possible size of the table (bytes).
-The columns information required by &create_table.
+=item Index_length
-=item column_order
+The length of the index for the table (bytes)
-Reference to an array containing the order of columns in the table.
+=item Data_free
-=item table_info
+I have no idea what this is.
-Set to the results of &get_table_info.
+=item Comment
-=item row_insert_sth
+The comment associated with the table.
+
+=item Rows
+
+The number of rows in the table.
+
+=item Auto_increment
+
+The value of the next auto_increment field.
+
+=item Create_options
+
+I have no idea.
+
+=item Col_order
+
+an array reference which holds the order of columns in the table.
+
+=item row_insert_sth
The statement handler for row inserts.
=back
+Col_order and row_insert_sth are kept internally by lonmysql and are not
+part of the usual MySQL table information.
+
=cut
##################################################
@@ -283,15 +332,6 @@
}
return undef;
}
- # The code below will let us switch to a different database.
- # my $db_command = "USE $db;";
- # my $sth = $dbh->prepare($db_command);
- # $sth->execute();
- # if ($sth->err) {
- # # Unable to use the database. Interesting...
- # $dbh->disconnect;
- # return undef;
- # }
$debugstring = "Successfully connected to loncapa database.";
return 1;
}
@@ -334,20 +374,16 @@
Input: table identifier
-Returns: the number of rows in the given table.
+Returns: the number of rows in the given table, undef on error.
=cut
###############################
sub number_of_rows {
my ($table_id) = @_;
- # Update the table information
- my %Table_Info = %{&get_table_info($table_id)};
- # return the number of rows.
- if (defined(%Table_Info)) {
- return $Table_Info{'Rows'};
- }
- return undef;
+ return undef if (! defined(&connect_to_db()));
+ return undef if (! defined(&update_table_info($table_id)));
+ return $Tables{&translate_id($table_id)}->{'Rows'};
}
###############################
@@ -388,14 +424,16 @@
=pod
-=item &get_table_info($table_id)
+=item &update_table_info($table_id)
Inputs: table id
-Returns: undef or a pointer to a hash of data about a table.
+Returns: undef on error, 1 on success.
-&get_table_info returns all of the information it can about a table in the
-form of a hash. Currently the fields in the hash are:
+&update_table_info updates the %Tables hash with current information about
+the given table.
+
+The default MySQL table status fields are:
Name Type Row_format
Max_data_length Index_length Data_free
@@ -403,14 +441,24 @@
Avg_row_length Data_length Comment
Rows Auto_increment Create_options
+Additionally, "Col_order" is updated as well.
+
=cut
###############################
-sub get_table_info {
+sub update_table_info {
my ($table_id) = @_;
+ return undef if (! defined(&connect_to_db()));
+ my $table_status = &check_table($table_id);
+ return undef if (! defined($table_status));
+ if (! $table_status) {
+ $errorstring = "table $table_id does not exist.";
+ return undef;
+ }
my $tablename = &translate_id($table_id);
- return undef if (! &check_table($table_id));
- my %tableinfo;
+ #
+ # Get MySQLs table status information.
+ #
my @tabledesc = qw/
Name Type Row_format Rows Avg_row_length Data_length
Max_data_length Index_length Data_free Auto_increment
@@ -419,20 +467,37 @@
my $sth = $dbh->prepare($db_command);
$sth->execute();
if ($sth->err) {
- # Unable to use the database. Interesting...
$errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n".
$sth->errstr;
- $dbh->disconnect;
+ &disconnect_from_db();
return undef;
}
#
my @info=$sth->fetchrow_array;
for (my $i=0;$i<= $#info ; $i++) {
- $tableinfo{$tabledesc[$i]}= $info[$i];
+ $Tables{$tablename}->{$tabledesc[$i]}= $info[$i];
+ }
+ #
+ # Determine the column order
+ #
+ $db_command = "DESCRIBE $tablename";
+ my $sth = $dbh->prepare($db_command);
+ $sth->execute();
+ if ($sth->err) {
+ $errorstring = "$dbh ATTEMPTED:\n".$db_command."\nRESULTING ERROR:\n".
+ $sth->errstr;
+ &disconnect_from_db();
+ return undef;
+ }
+ my $aref=$sth->fetchall_arrayref;
+ $Tables{$tablename}->{'Col_order'}=[]; # Clear values.
+ # The values we want are the 'Field' entries, the first column.
+ for (my $i=0;$i< @$aref ; $i++) {
+ push @{$Tables{$tablename}->{'Col_order'}},$aref->[$i]->[0];
}
#
$debugstring = "Retrieved table info for $tablename";
- return \%tableinfo;
+ return 1;
}
###############################
@@ -454,7 +519,6 @@
restrictions => 'NOT NULL' or empty,
primary_key => 'yes' or empty,
auto_inc => 'yes' or empty,
- target => 'colB' (only if type eq 'FULLTEXT'),
}
colB => { .. }
colZ => { .. }
@@ -469,23 +533,20 @@
###############################
sub create_table {
- return undef if (!&connect_to_db($dbh));
+ return undef if (!defined(&connect_to_db($dbh)));
my ($table_des)=@_;
#
# Build request to create table
##################################
my @Columns;
my $col_des;
- my $tableid = &get_new_table_id();
- my $tablename = &translate_id($tableid);
+ my $table_id = &get_new_table_id();
+ my $tablename = &translate_id($table_id);
my $request = "CREATE TABLE IF NOT EXISTS ".$tablename." ";
foreach my $column (@{$table_des->{'column_order'}}) {
$col_des = '';
my $coldata = $table_des->{'columns'}->{$column};
- if (lc($coldata->{'type'}) eq 'fulltext') {
- $col_des.='FULLTEXT '.$column." (".$coldata->{'target'}.")";
- next; # Skip to the continue block and store the column data
- } elsif (lc($coldata->{'type'}) =~ /(enum|set)/) { # 'enum' or 'set'
+ if (lc($coldata->{'type'}) =~ /(enum|set)/) { # 'enum' or 'set'
$col_des.=$column." ".$coldata->{'type'}."('".
join("', '",@{$coldata->{'values'}})."')";
} else {
@@ -501,12 +562,17 @@
if (exists($coldata->{'default'})) {
$col_des.=" DEFAULT '".$coldata->{'default'}."'";
}
- $col_des.=' AUTO_INCREMENT' if (exists($coldata->{'auto_inc'}));
- $col_des.=' PRIMARY KEY' if (exists($coldata->{'primary_key'}));
+ $col_des.=' AUTO_INCREMENT' if (exists($coldata->{'auto_inc'}) &&
+ ($coldata->{'auto_inc'} eq 'yes'));
+ $col_des.=' PRIMARY KEY' if (exists($coldata->{'primary_key'}) &&
+ ($coldata->{'primary_key'} eq 'yes'));
} continue {
# skip blank items.
push (@Columns,$col_des) if ($col_des ne '');
}
+ if (exists($table_des->{'fulltext'})) {
+ push (@Columns,'FULLTEXT ('.join(',',@{$table_des->{'fulltext'}}).')');
+ }
$request .= "(".join(", ",@Columns).") ";
unless($table_des->{'permanent'} eq 'yes') {
$request.="COMMENT = 'temporary' ";
@@ -517,22 +583,17 @@
#############################################
my $count = $dbh->do($request);
if (! defined($count)) {
- $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n".
+ $errorstring = "$dbh ATTEMPTED:\n".$request."\nRESULTING ERROR:\n";
return undef;
}
#
# Set up the internal bookkeeping
#############################################
delete($Tables{$tablename}) if (exists($Tables{$tablename}));
- my @column_order_copy = @{$table_des->{'column_order'}};
- $Tables{$tablename} = {
- columns => $table_des->{'columns'},
- column_order => $table_des->{'column_order'},
- table_info => &get_table_info($tableid),
- };
- $debugstring = "$dbh Created table $tablename at time ".time.
+ return undef if (! defined(&update_table_info($table_id)));
+ $debugstring = "Created table $tablename at time ".time.
" with request\n$request";
- return $tableid;
+ return $table_id;
}
###############################
@@ -562,22 +623,6 @@
=pod
-=item &execute_db_command
-
-Currently unimplemented
-
-=cut
-
-###############################
-sub execute_db_command {
- my ($tablename,$command) = @_;
- return 1;
-}
-
-###############################
-
-=pod
-
=item &get_rows
Inputs: $table_id,$condition
@@ -593,6 +638,13 @@
###############################
sub get_rows {
my ($table_id,$condition) = @_;
+ return undef if (! defined(&connect_to_db()));
+ my $table_status = &check_table($table_id);
+ return undef if (! defined($table_status));
+ if (! $table_status) {
+ $errorstring = "table $table_id does not exist.";
+ return undef;
+ }
my $tablename = &translate_id($table_id);
my $request = 'SELECT * FROM '.$tablename.' WHERE '.$condition;
my $sth=$dbh->prepare($request);
@@ -628,30 +680,37 @@
###############################
sub store_row {
my ($table_id,$rowdata) = @_;
+ #
+ return undef if (! defined(&connect_to_db()));
+ my $table_status = &check_table($table_id);
+ return undef if (! defined($table_status));
+ if (! $table_status) {
+ $errorstring = "table $table_id does not exist.";
+ return undef;
+ }
+ #
my $tablename = &translate_id($table_id);
- my $table = $Tables{$tablename};
+ #
my $sth;
- if (exists($table->{'row_insert_sth'})) {
- $sth = $table->{'row_insert_sth'};
+ if (exists($Tables{$tablename}->{'row_insert_sth'})) {
+ $sth = $Tables{$tablename}->{'row_insert_sth'};
} else {
- # We need to build a statement handler
+ # Build the insert statement handler
+ return undef if (! defined(&update_table_info($table_id)));
my $insert_request = 'INSERT INTO '.$tablename.' VALUES(';
- foreach (@{$table->{'column_order'}}) {
- # Skip the 'fulltext' columns.
- next if (lc($table->{'columns'}->{$_}->{'type'}) eq 'fulltext');
+ foreach (@{$Tables{$tablename}->{'Col_order'}}) {
$insert_request.="?,";
}
chop $insert_request;
$insert_request.=")";
$sth=$dbh->prepare($insert_request);
+ $Tables{$tablename}->{'row_insert_sth'}=$sth;
}
my @Parameters;
if (ref($rowdata) eq 'ARRAY') {
@Parameters = @$rowdata;
} elsif (ref($rowdata) eq 'HASH') {
- foreach (@{$table->{'column_order'}}) {
- # Is this appropriate? Am I being presumptious? ACK!!!!!
- next if (lc($table->{'columns'}->{$_}->{'type'}) eq 'fulltext');
+ foreach (@{$Tables{$tablename}->{'Col_order'}}) {
push(@Parameters,&Apache::lonnet::escape($rowdata->{$_}));
}
}
@@ -678,12 +737,12 @@
###########################################
sub tables_in_db {
- return undef if (! &connect_to_db()); # bail out if we cannot connect
+ return undef if (!defined(&connect_to_db()));
my $sth=$dbh->prepare('SHOW TABLES;');
$sth->execute();
if ($sth->err) {
- $errorstring = "$dbh ATTEMPTED:\n".'SHOW TABLES'."\nRESULTING ERROR:\n".
- $sth->errstr;
+ $errorstring = "$dbh ATTEMPTED:\n".'SHOW TABLES'.
+ "\nRESULTING ERROR:\n".$sth->errstr;
return undef;
}
my $aref = $sth->fetchall_arrayref;
@@ -732,8 +791,9 @@
###########################################
sub check_table {
my $table_id = shift;
+ return undef if (!defined(&connect_to_db()));
+ #
$table_id = &translate_id($table_id);
- return undef if (! &connect_to_db());
my @Table_list = &tables_in_db();
my $result = 0;
foreach (@Table_list) {
@@ -751,4 +811,3 @@
1;
__END__;
-
--matthew1028053600--