[WWcvs] webwork-modperl: This change affects course creation using mysql.

[jj v. a. <we...@ma...>]

Log Message:
-----------
This change affects course creation using mysql.  Field types are now
determined by DB/Record/*.pm in the function SQL_TYPES.  Key fields
which are text are set to binary types (blobs) so that indexing on those
fields is case sensitive.  The net result should be fast database access
while still having it be case-sensitive in the searching.

Modified Files:
--------------
    webwork-modperl/lib/WeBWorK/Utils/CourseManagement:
        sql.pm
        sql_single.pm

Revision Data
-------------
Index: sql_single.pm
===================================================================
RCS file: /webwork/cvs/system/webwork-modperl/lib/WeBWorK/Utils/CourseManagement/sql_single.pm,v
retrieving revision 1.4
retrieving revision 1.5
diff -Llib/WeBWorK/Utils/CourseManagement/sql_single.pm -Llib/WeBWorK/Utils/CourseManagement/sql_single.pm -u -r1.4 -r1.5
--- lib/WeBWorK/Utils/CourseManagement/sql_single.pm
+++ lib/WeBWorK/Utils/CourseManagement/sql_single.pm
@@ -74,6 +74,8 @@
 		debug("$table: WeBWorK field names: @fields\n");
 		my @keyfields = $recordClass->KEYFIELDS;
 		debug("$table: WeBWorK keyfield names: @keyfields\n");
+		my @fieldtypes = $recordClass->SQL_TYPES;
+		debug("$table: WeBWorK field types: @fieldtypes\n");
 		
 		if (exists $params{fieldOverride}) {
 			my %fieldOverride = %{ $params{fieldOverride} };
@@ -83,20 +85,23 @@
 			debug("$table: SQL field names: @fields\n");
 		}
 		
+		my %fieldtypehash =();
+		for my $cnt (0..(scalar(@fields)-1)) {
+			$fieldtypehash{$fields[$cnt]} = $fieldtypes[$cnt];
+		}
 		# generate table creation statement
 		
 		my @fieldList;
+		# special handling of psvn's is now taken care of by
+		# its entry in %fieldtypehash, which comes from SQL_TYPES
 		foreach my $field (@fields) {
-			# a stupid hack to make PSVNs numeric and auto-increment
-			if ($field eq "psvn") {
-				push @fieldList, "`$field` INT NOT NULL PRIMARY KEY AUTO_INCREMENT";
-			} else {
-				push @fieldList, "`$field` TEXT";
-			}
+			push @fieldList, "`$field` $fieldtypehash{$field}";
 		}
 		foreach my $start (0 .. $#keyfields) {
 			my $line = "INDEX ( ";
-			$line .= join(", ", map { "`$_`(16)" } @keyfields[$start .. $#keyfields]);
+			# we only need to limit the length of the value for
+			# types text and blob, but can't do it for int.
+			$line .= join(", ", map { "`$_`". (($fieldtypehash{$_} =~ /int/i) ? "" : "(16)") } @keyfields[$start .. $#keyfields]);
 			$line .= " )";
 			push @fieldList, $line;
 		}
Index: sql.pm
===================================================================
RCS file: /webwork/cvs/system/webwork-modperl/lib/WeBWorK/Utils/CourseManagement/sql.pm,v
retrieving revision 1.2
retrieving revision 1.3
diff -Llib/WeBWorK/Utils/CourseManagement/sql.pm -Llib/WeBWorK/Utils/CourseManagement/sql.pm -u -r1.2 -r1.3
--- lib/WeBWorK/Utils/CourseManagement/sql.pm
+++ lib/WeBWorK/Utils/CourseManagement/sql.pm
@@ -71,6 +71,8 @@
 		debug("$table: WeBWorK field names: @fields\n");
 		my @keyfields = $recordClass->KEYFIELDS;
 		debug("$table: WeBWorK keyfield names: @keyfields\n");
+		my @fieldtypes = $recordClass->SQL_TYPES;
+                debug("$table: WeBWorK field types: @fieldtypes\n");
 		
 		if (exists $params{fieldOverride}) {
 			my %fieldOverride = %{ $params{fieldOverride} };
@@ -80,20 +82,19 @@
 			debug("$table: SQL field names: @fields\n");
 		}
 		
+		my %fieldtypehash =();
+                for my $cnt (0..(scalar(@fields)-1)) {
+                        $fieldtypehash{$fields[$cnt]} = $fieldtypes[$cnt];
+                }
 		# generate table creation statement
 		
 		my @fieldList;
 		foreach my $field (@fields) {
-			# a stupid hack to make PSVNs numeric and auto-increment
-			if ($field eq "psvn") {
-				push @fieldList, "`$field` INT NOT NULL PRIMARY KEY AUTO_INCREMENT";
-			} else {
-				push @fieldList, "`$field` TEXT";
-			}
+			push @fieldList, "`$field` $fieldtypehash{$field}";
 		}
 		foreach my $start (0 .. $#keyfields) {
 			my $line = "INDEX ( ";
-			$line .= join(", ", map { "`$_`(16)" } @keyfields[$start .. $#keyfields]);
+			$line .= join(", ", map { "`$_`". (($fieldtypehash{$_} = /int/i) ? "" : "(16)") } @keyfields[$start .. $#keyfields]);
 			$line .= " )";
 			push @fieldList, $line;
 		}