Hi all,
Having a rather odd problem. Been doing some cross-compatibility work on our MoodleTxt module. Got Moodle 1.7 installed on MS SQL Server 2005 OK, made my tables in the table designer, then exported them to db/mssql_n.sql in my module folder.
When I go to the Notifications page, I get MoodleTxt tables have been set up correctly," but the SQL script hasn't been executed. I checked the config.php file, and my dbtype is set to "mssql_n". Just in case, I tried changing the SQL filename to mssql.sql, but still no effect. Does anyone know why my SQL script would not be executed?
Thanks in advance.
Got it!
The script was being called, but then completely ignored. It's to do with the way MS SQL dumps its table data. Here's a few pointers for anyone else doing it.
Straight off, before every command, MS SQL will put "GO". Get rid of it. It seems to do nothing but cause problems, in my script.
Also, check out this snippet of code from /moodle/lib/dmlib.php, in the modify_database() function:
Secondly, the code finds the end of an SQL command by looking for the semicolon at the end of its line. However, MS SQL does not need or insert these semicolons. They are not picked up on within the script, and so the $command string that is started at the beginning of the script is never completed and never sent to the database, giving the impression that the SQL script was never executed.
The solution is easy. Simply modify all /** comments into single line hash or double-dash comments, and then insert a semicolon at the end of every SQL command in the script (laborious, but necessary). This will then be read correctly within Moodle, and the semicolon is dropped before being sent to the DB, so MS SQL has no problems either.
I've now got my script working properly. If there is a relevant piece of documentation, I will update it. Also, I would recommend that /** comments are added to the list of ignored comment styles in the Moodle code. Does anyone have an objection to me adding this to the Moodle code in the working repository?
Unfortunately, I can't see a workaround for the semicolon problem other than simply inserting them at the end of each command.
The script was being called, but then completely ignored. It's to do with the way MS SQL dumps its table data. Here's a few pointers for anyone else doing it.
Straight off, before every command, MS SQL will put "GO". Get rid of it. It seems to do nothing but cause problems, in my script.
Also, check out this snippet of code from /moodle/lib/dmlib.php, in the modify_database() function:
foreach ($lines as $line) {
$line = rtrim($line);
$length = strlen($line);
if ($length and $line[0] <> '#' and $line[0].$line[1] <> '--') {
if (substr($line, $length-1, 1) == ';') {
$line = substr($line, 0, $length-1); // strip ;
$command .= $line;
$command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes
if (! execute_sql($command)) {
$success = false;
}
$command = '';
} else {
$command .= $line;
}
}
}
Secondly, the code finds the end of an SQL command by looking for the semicolon at the end of its line. However, MS SQL does not need or insert these semicolons. They are not picked up on within the script, and so the $command string that is started at the beginning of the script is never completed and never sent to the database, giving the impression that the SQL script was never executed.
The solution is easy. Simply modify all /** comments into single line hash or double-dash comments, and then insert a semicolon at the end of every SQL command in the script (laborious, but necessary). This will then be read correctly within Moodle, and the semicolon is dropped before being sent to the DB, so MS SQL has no problems either.
I've now got my script working properly. If there is a relevant piece of documentation, I will update it. Also, I would recommend that /** comments are added to the list of ignored comment styles in the Moodle code. Does anyone have an objection to me adding this to the Moodle code in the working repository?
Unfortunately, I can't see a workaround for the semicolon problem other than simply inserting them at the end of each command.
Why not just use the generic XMLDB method and support all possible databases with one schema?
http://docs.moodle.org/en/Development:XMLDB_defining_an_XML_structure
http://docs.moodle.org/en/Development:XMLDB_defining_an_XML_structure
Couple of reasons, mainly backward compatibility if people need it, and that the XMLDB schema only supports installation, not runtime.
I will be including an XMLDB script, but also the .sql, to cover all bases.
I will be including an XMLDB script, but also the .sql, to cover all bases.
Also, while the XMLDB and its editor are excellent, they don't allow for the complete range of DB capabilities to be exploited. I could be wrong, but I can't find functionality for field constraints in PostrgreSQL, for instance. CHECK (candelete IN(0, 1)) is my best mate.
Just using the XMLDB editor now, and it's great. Hope I can get the tables ordered in the script correctly to work across all DBs. The entries are listed in a different order in my PostgreSQL and MS SQL scripts, and I'll be damned if I can remember why. I know they had to be ordered a certain way to make all the dependencies work. This could be fun.
Just using the XMLDB editor now, and it's great. Hope I can get the tables ordered in the script correctly to work across all DBs. The entries are listed in a different order in my PostgreSQL and MS SQL scripts, and I'll be damned if I can remember why. I know they had to be ordered a certain way to make all the dependencies work. This could be fun.
XMLDB schema supports runtime too
Here is a snippet from a patch Martin Langhoff is proposing for enrol/database/enrol.php:
require_once($CFG->dirroot . '/lib/ddllib.php');
$table = new XMLDBTable('enroldb_remoteenr');
// fields
$f = $table->addFieldInfo('userxid', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null, null, null);
$f = $table->addFieldInfo('coursexid', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null, null, null);
$f = $table->addFieldInfo('rolexid', XMLDB_TYPE_CHAR, '100', null, null, null, null, null, null);
// PK and indexes
$table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('coursexid,userxid'));
// Create and truncate the table
$temptable = create_temp_table($table);
if (empty($temptable)) {
debugging("Error creating temp table");
die;
}
$temptable = $CFG->prefix . $temptable;
execute_sql("TRUNCATE $temptable", false, false);
//
// Lots of non-relevant things to this example here
//
drop_table($temptable);
So as you see, you can do pretty much anything you want with XMLDB and related functions
Saludos. Iñaki.
Interesting, but I was referring to runtime SQL queries - whether we have XMLDB table setup or not, we still have a library of different custom SQL queries for each DB type. If I have to have different SQL for each DB at runtime, XMLDB installations aren't as big a help as they might have been.
Would be useful for upgrades though - do you have a link to a tutorial or somewhere I could learn how to do that? (Assuming it's possible.)
Wish XMLDB supported longer table names. Going to have to rename a table to get this to work.
Would be useful for upgrades though - do you have a link to a tutorial or somewhere I could learn how to do that? (Assuming it's possible.)
Wish XMLDB supported longer table names. Going to have to rename a table to get this to work.
There are functions in dmllib.php that should abstract out most of the differences between databases, for example sql_concat. Look in there. They should let you construct SQL for any database with one bit of code.
Fun times. I'll have a butchers, see if I can't do anything about this structure. Any documentation on that? (Like I need to ask 
)
One of these days I'm going to write "Developing for Moodle" or something, hehe.
One of these days I'm going to write "Developing for Moodle" or something, hehe.
All those funcitons are so simple, you just need to read the code.
Hehe, right you are. Thanks for your help. Should be able to knock something together.