[LON-CAPA-cvs] cvs: loncom /interface lonmysql.pm lonsearchcat.pm
matthew
lon-capa-cvs@mail.lon-capa.org
Thu, 13 Mar 2003 19:08:52 -0000
This is a MIME encoded message
--matthew1047582532
Content-Type: text/plain
matthew Thu Mar 13 14:08:52 2003 EDT
Modified files:
/loncom/interface lonmysql.pm lonsearchcat.pm
Log:
lonmysql:
Added &replace_row, which is analogus to &store_row but it does a
'REPLACE' instead of 'STORE'.
Changed somewhat the table definition. PRIMARY KEY, KEY, INDEX,
UNIQUE [INDEX], and FULLTEXT [INDEX] are now able to be specified.
Previously only FULLTEXT was allowed.
lonsearchcat.pm:
Updated to use new mechanism for specifying FULLTEXT indexing.
--matthew1047582532
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20030313140852.txt"
Index: loncom/interface/lonmysql.pm
diff -u loncom/interface/lonmysql.pm:1.8 loncom/interface/lonmysql.pm:1.9
--- loncom/interface/lonmysql.pm:1.8 Mon Mar 10 16:22:36 2003
+++ loncom/interface/lonmysql.pm Thu Mar 13 14:08:52 2003
@@ -1,7 +1,7 @@
# The LearningOnline Network with CAPA
# MySQL utility functions
#
-# $Id: lonmysql.pm,v 1.8 2003/03/10 21:22:36 matthew Exp $
+# $Id: lonmysql.pm,v 1.9 2003/03/13 19:08:52 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -71,17 +71,18 @@
for &create_table for a description of what is needed.
$table_id = &create_table({
- columns => {
- id => {
- type => 'INT',
- restrictions => 'NOT NULL',
- primary_key => 'yes',
- auto_inc => 'yes'
- }
- verbage => { type => 'TEXT' },
- },
- column_order => [qw/id verbage idx_verbage/],
- fulltext => [qw/verbage/],
+ id => 'tableid', # usually you will use the returned id
+ columns => (
+ { name => 'id',
+ type => 'INT',
+ restrictions => 'NOT NULL',
+ primary_key => 'yes',
+ auto_inc => 'yes'
+ },
+ { name => 'verbage',
+ type => 'TEXT' },
+ ),
+ fulltext => [qw/verbage/],
});
The above command will create a table with two columns, 'id' and 'verbage'.
@@ -230,6 +231,10 @@
The statement handler for row inserts.
+=item row_replace_sth
+
+The statement handler for row inserts.
+
=back
Col_order and row_insert_sth are kept internally by lonmysql and are not
@@ -362,6 +367,9 @@
if (exists($Tables{$_}->{'row_insert_sth'})) {
delete($Tables{$_}->{'row_insert_sth'});
}
+ if (exists($Tables{$_}->{'row_replace_sth'})) {
+ delete($Tables{$_}->{'row_replace_sth'});
+ }
}
$dbh->disconnect if ($dbh);
$debugstring = "Disconnected from database.";
@@ -530,6 +538,18 @@
...
},
],
+ 'PRIMARY KEY' => (index_col_name,...),
+ KEY => { name => 'idx_name',
+ columns => (col1,col2,..),},
+ INDEX => { name => 'idx_name',
+ columns => (col1,col2,..),},
+ UNIQUE => { index => 'yes',
+ name => 'idx_name',
+ columns => (col1,col2,..),},
+ FULLTEXT => { index => 'yes',
+ name => 'idx_name',
+ columns => (col1,col2,..),},
+
}
Returns:
@@ -546,7 +566,12 @@
##################################
my @Columns;
my $col_des;
- my $table_id = &get_new_table_id();
+ my $table_id;
+ if (exists($table_des->{'id'})) {
+ $table_id = $table_des->{'id'};
+ } else {
+ $table_id = &get_new_table_id();
+ }
my $tablename = &translate_id($table_id);
my $request = "CREATE TABLE IF NOT EXISTS ".$tablename." ";
foreach my $coldata (@{$table_des->{'columns'}}) {
@@ -577,8 +602,33 @@
# skip blank items.
push (@Columns,$col_des) if ($col_des ne '');
}
- if (exists($table_des->{'fulltext'}) && (@{$table_des->{'fulltext'}})) {
- push (@Columns,'FULLTEXT ('.join(',',@{$table_des->{'fulltext'}}).')');
+ if (exists($table_des->{'PRIMARY KEY'})) {
+ push (@Columns,'PRIMARY KEY ('.join(',',@{$table_des->{'PRIMARY KEY'}})
+ .')');
+ }
+ foreach ('KEY','INDEX') {
+ if (exists($table_des->{$_})) {
+ my $text = $_.' ';
+ if (exists($table_des->{$_}->{'name'})) {
+ $text .=$table_des->{$_}->{'name'};
+ }
+ $text .= ' ('.join(',',@{$table_des->{$_}->{'columns'}}).')';
+ push (@Columns,$text);
+ }
+ }
+ foreach ('UNIQUE','FULLTEXT') {
+ if (exists($table_des->{$_})) {
+ my $text = $_.' ';
+ if (exists($table_des->{$_}->{'index'}) &&
+ $table_des->{$_}->{'index'} eq 'yes') {
+ $text .= 'INDEX ';
+ }
+ if (exists($table_des->{$_}->{'name'})) {
+ $text .=$table_des->{$_}->{'name'};
+ }
+ $text .= ' ('.join(',',@{$table_des->{$_}->{'columns'}}).')';
+ push (@Columns,$text);
+ }
}
$request .= "(".join(", ",@Columns).") ";
unless($table_des->{'permanent'} eq 'yes') {
@@ -652,7 +702,13 @@
return undef;
}
my $tablename = &translate_id($table_id);
- my $request = 'SELECT * FROM '.$tablename.' WHERE '.$condition;
+ my $request;
+ if (defined($condition) && $condition ne '') {
+ $request = 'SELECT * FROM '.$tablename.' WHERE '.$condition;
+ } else {
+ $request = 'SELECT * FROM '.$tablename;
+ $condition = 'no condition';
+ }
my $sth=$dbh->prepare($request);
$sth->execute();
if ($sth->err) {
@@ -725,6 +781,67 @@
return 1;
}
+###############################
+
+=pod
+
+=item &replace_row()
+
+Inputs: table id, row data
+
+returns undef on error, 1 on success.
+
+Acts like &store_row() but uses the 'REPLACE' command instead of 'INSERT'.
+
+=cut
+
+###############################
+sub replace_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 $sth;
+ if (exists($Tables{$tablename}->{'row_replace_sth'})) {
+ $sth = $Tables{$tablename}->{'row_replace_sth'};
+ } else {
+ # Build the insert statement handler
+ return undef if (! defined(&update_table_info($table_id)));
+ my $replace_request = 'REPLACE INTO '.$tablename.' VALUES(';
+ foreach (@{$Tables{$tablename}->{'Col_order'}}) {
+ $replace_request.="?,";
+ }
+ chop $replace_request;
+ $replace_request.=")";
+ $sth=$dbh->prepare($replace_request);
+ $Tables{$tablename}->{'row_replace_sth'}=$sth;
+ }
+ my @Parameters;
+ if (ref($rowdata) eq 'ARRAY') {
+ @Parameters = @$rowdata;
+ } elsif (ref($rowdata) eq 'HASH') {
+ foreach (@{$Tables{$tablename}->{'Col_order'}}) {
+ push(@Parameters,$rowdata->{$_});
+ }
+ }
+ $sth->execute(@Parameters);
+ if ($sth->err) {
+ $errorstring = "$dbh ATTEMPTED replace @Parameters RESULTING ERROR:\n".
+ $sth->errstr;
+ return undef;
+ }
+ $debugstring = "Stored row.";
+ return 1;
+}
+
###########################################
=pod
@@ -800,7 +917,7 @@
my @Table_list = &tables_in_db();
my $result = 0;
foreach (@Table_list) {
- if (/^$table_id$/) {
+ if ($_ eq $table_id) {
$result = 1;
last;
}
Index: loncom/interface/lonsearchcat.pm
diff -u loncom/interface/lonsearchcat.pm:1.170 loncom/interface/lonsearchcat.pm:1.171
--- loncom/interface/lonsearchcat.pm:1.170 Mon Mar 10 16:22:36 2003
+++ loncom/interface/lonsearchcat.pm Thu Mar 13 14:08:52 2003
@@ -1,7 +1,7 @@
# The LearningOnline Network with CAPA
# Search Catalog
#
-# $Id: lonsearchcat.pm,v 1.170 2003/03/10 21:22:36 matthew Exp $
+# $Id: lonsearchcat.pm,v 1.171 2003/03/13 19:08:52 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -1951,7 +1951,7 @@
sub create_results_table {
my $table = &Apache::lonmysql::create_table
( { columns => \@Datatypes,
- fullindex => \@Fullindicies,
+ FULLTEXT => {'columns' => \@Fullindicies},
} );
if (defined($table)) {
$ENV{'form.table'} = $table;
--matthew1047582532--