General developer forum

 
 
Picture of Mark Chaney
Does the XML installer create Foreign keys
 

I am in the process of setting up a block that I am converting from 1.9 to moodle 2.x. In the past i created foreign keys using the upgrade scripts. I am now trying to meet the Moodle standards as described by the XMLDB editor api docs (http://docs.moodle.org/dev/XMLDB_editor) and specifically where it says:

"About FOREIGN KEYS

  1. Under the tables of every XMLDB file, you must define the existing Foreign Keys (FK) properly. This will allow everybody to know a bit better the structure, allow to evolve to a better constrained system in the future and will provide the underlying code with the needed info to create the proper indexes.
  2. Note that, if you define any field combination as FK you won't have to create any index on that fields, the code will do it automatically!
  3. This convention is only applicable for relations INSIDE one file. Don't generate FK constraints against other files (courseid, userid), use indexes there."

I have 90 tables in my block and they reference each other like crazy. I have used the xmldb editor to set up the columns, indexes and foreign keys. After an install (Moodle 2.4.1) the tables are created, the columns are created and the indexes are created.

However no Foriegn Keys are created.

Does this mean I have to go in and manually create the forign keys?

Thanks

Mark

 
Average of ratings: -
Picture of Mark Chaney
Re: Does the XML installer create Foreign keys
 

I had a kind of 'Duh/Doh' moment after I posted this to see if I had Innodb enabled. I hadnt, DOH! But, I have enabled it (as far as I can tell) and it still isnt installing.

This is on a Windows 7 PC, using Xampp on a localhost. May try on a CENTOS server in a bit using MYSQL.

Anyone any ideas? Are there any settings I have to turn on anywhere for it to generate the forieign keys.

As a side note it did correctly create the foreign keys as Indexes. Thanks

Mark

 
Average of ratings: -
Picture of Marcus Green
Re: Does the XML installer create Foreign keys
Group Particularly helpful Moodlers

Why do you want to install foreign keys?  You can turn them on at

lib\ddl\sql_generator.php

But in my experience if you are using innodb it causes the install to fall over

You can read a bit about it at this link

http://www.examulator.com/er/er_howto.html

Which is part of the details on how to generate an ER diagram from a Moodle install  using reverse engineering.

In the words of Professor Piehead I would call this "a partial success"

 

 

 
Average of ratings: -
Picture of Mark Chaney
Re: Does the XML installer create Foreign keys
 

Hi, I am using Foreign keys to aid in referencial integrity and to also allow other users/developers to better understand my new plugin. I wanted to check when installing it into Moodle that it created the Foreign Keys. 

I will then use them to reverse engineer an ER diagram. 

Im not looking to do the other moodle core tables, just my own. 

I wasnt aware that you had to turn it on in the lib. Ill have a look. 

Thanks

Mark

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Does the XML installer create Foreign keys
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

The difficulty is that for the last 10 years, Moodle has not had foriegn keys. (When Moodle started, MySQL 3.x was the only DB it worked with, and that was completely incapable of supporting foreign keys.)

So now, almost every Moodle database contains data that violatest the foreign keys that should exist, and so trying to add them now would just lead to fatal errors.

So, while many developers would like to add foreign keys, for all the benefits you list, it would be very difficult to do successfully.

On some occasions, I have manually added the foreign keys while doing development, since that does catch bugs.

 
Average of ratings: -
Picture of Marcus Green
Re: Does the XML installer create Foreign keys
Group Particularly helpful Moodlers

To clarify Tims comment, there is generally no problem in creating the code to indicate foreign keys, the problem comes if the system tries to create them in a InnoDB database (i.e. to actually use them to enforce data integrity).

The subtle difference is what allows me to generate the ER diagrams. I get the system to spit out DDL/SQL designed to generate Foreign Keys but because it is going into a MyISAM database the information is silently thrown away. However the DDL/SQL is then used to reverse engineer the diagram.

It occured to me late last night  that we could progress towards allowing the genertion of actual FK rules by having some XML tag on installation that indicated if the FK information was for information or enforcement. 

 
Average of ratings: -
Picture of Mark Chaney
Re: Does the XML installer create Foreign keys
 

Ok, all makes sense and understandable. I had seen other forum posts and docs about the issues with installations to do with the core code. 

Is there a way to have moodle core install without foreign keys, then turn them on to install my plugin? Its purely for test purposes and would only be on my dev server. 

Or is there much call/possibility to have the ability to turn on foreign keys at a plugin level? Im trying to rack my brains to if thats even remotely possible?

Ill have a look at the other methods posted here to generate my ER diagram. 

Thanks

Mark

 
Average of ratings: -
Picture of Marcus Green
Re: Does the XML installer create Foreign keys
Group Particularly helpful Moodlers

I think the answer to your question is No, but my previous comment that mentioned XML was based on the idea that it would be a nice thing to be able to do.

I used to generate the diagrams by sucking the SQL DDL log out, but that was not very satisfactory for a whole install, but it might be doable for the few tables involved in a plugin.  What you are trying to do is a reasonable and desirable thing.

 
Average of ratings: -
C'est moi :-)
Re: Does the XML installer create Foreign keys
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Perhaps there should be FK in new Moodle installations only, but not on existing ones, when doing an upgrade...

Would there be problems after, if doing restore from older version?

 
Average of ratings: -
Picture of Mark Chaney
Re: Does the XML installer create Foreign keys
 

Hi guys, It wont uninstall!

I have successfully installed my plugin (its a block with mod extensions), The Block has 81 tables and all of my foreign keys are installed correctly when turning the Foriegn Keys to True in sql_generator.php. Brilliant, ive used them for testing and also for my ER diagram.

I went to uninstall the Plugin (using manage blocks in the admin plugin section) and it failed on the first Foreign Key Constraint. As far as I can work out its trying to delete the blocks in alphabetical order, or, in the order of the xml install script. What I would want is for it to do it in REVERSE order of the install xml file, thus it will delete the foriegn keys in the correct order. (unless im being completely wrong?)

Is there a setting for this? Or am I being really stupid again? Or, is it a bug?

Mark

 
Average of ratings: -