General developer forum

 
 
Picture of Dan McCuaig
Databases: Database model
 

Howdy,

Has anybody created an ER Diagram of the Moodle database?  This would be nice to have.

I created a rough Visio drawing of the tables.  I will attach three files to this message

  • text file of table descriptions retrieved from the moodle database
  • Windows Meta File of tables
  • Visio drawing of tables

The other files will  be included in following messages

 
Average of ratings: -
Picture of Dan McCuaig
Re: Databases: Database model
 
Here is the Visio file
 
Average of ratings: -
Picture of Dan McCuaig
Re: Databases: Database model
 
Here is the WMF file
 
Average of ratings: -
Picture of Jeremy Winch
Re: Databases: Database model
 

Here is a layout of all of the tables and field names in moodle. I'm pretty sure this is fairly accurate.

 
Average of ratings: -
Picture of Runy Calmera
Re: Databases: Database model
 

Hi,

Look at the opensource case tool DBdesigner at: http://fabforce.net/dbdesigner4/

With this tool you could reverse engineer moodle database schema and get your datamodel.

Runy

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
I have just had a go at creating an ERd for the quiz module using dbdDesigner and have attached it to this post. There are bits missing (see all tables on far right) and probably some errors but if somone can give me the relationships as text as in "One question has many answers" I could create a  complete version. dbdDesigner has a few quirks but a great deal of functionality and it comes under our favorite licesnse and is available for Windows and Linux.

Marcus

 
Average of ratings: -
Picture of Christian Milani
Re: Databases: Database model
 

Hello!

Great job! If u go in the post Moodle Database Study I created an Excel file with nearly every foreign key.

A table with a fk its the many table(i.e. mdl_forum has a field named course and is the many, mdl_course is the one). 
It will be hard to create relationship with tables that have a filed as FK but the elements in this filed are "comma" separated (its a many-to-many relationship in a "strange" way to avoid a "relationship table").

If you need help just contact me, i need the ER diagram too!

Cya

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers

That spreadsheet looks very useful, I will work on completing more ER stuff using it. Would you be interested in the xml file to use in DBDesigner, and of course I can make the graphic available as well.

Marcus

 
Average of ratings: -
Picture of Christian Milani
Re: Databases: Database model
 
Yes that would be great!

Thx again!

Cya!
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
Here is the DBDesigner graphic for the quiz ERD, xml for dbdesigner to follow. The 1:1 relations imply the primary keys are based on two columns and I don't know how to stop it doing that but it might be helpful in understanding the tables.XML to follow

Marcus

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
Here is the xml for DB Designer
 
Average of ratings: -
Picture of PROFessor VAZZ - Brazil
Re: Databases: Database model
 
Hi,

Can I Help me ?
About MDL_LOG and MDL_CACHE_TEXT

Can I empty them ?
Will I have problems with my courses and students ?

Tks.

PROFessor VAZZ
professor-vazz@professor-vazz.com
Brazil
 
Average of ratings: -
Picture of roberto pinto
Re: Databases: Database model
 

Hola a todos

     Con referencia a las tablas de cursos,talleres,consultas,evaluacion no poseen el DER ????

     Si alguien tiene Informacion sobre ello agradeceria que me la haga llegar porfavor.

 
Average of ratings: -
Picture of Marilia Amaral
Re: Databases: Database model
 
Hi,
We are a group in UFSC, an university in Brazil.
We created an ER Diagram of Moodle database with DBDesigner, and it is at http://www.labcal.ufsc.br/portal/artigos/modelo_db_moodle/files/.
I hope it's usefull...!!
 
Average of ratings: -
wen photo
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
I think it would be great if there is some kind of comparison diagram or tool to list (diff) database changes from 1.4.x to 1.5.x to 1.6.x and 1.7.x. Does anyone know of any such tool or document that is available online for Moodle? Thanks!
 
Average of ratings: -
Picture of Gustavo Santos
Re: Databases: Database model
 
Ola Marilia,

Seria possivel enviar o modelo em outro formato? Ambos os arquivos sao dificeis de visualizar.

Gustavo
 
Average of ratings: -
Picture of src src
Re: Databases: Database model
 

Hi,

I would like to see your model, but i can because the link is broken.

 
Average of ratings: -
Picture of Walace S. Bonfim
Re: Databases: Database model
 

Hi Marilia,

This link doesn't work. It points to a non existent page. Could you please check it out?

http://www.labcal.ufsc.br/portal/artigos/modelo_db_moodle/files/

Regards,

Walace (walace_b)

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers

You are looking at some very old threads. You can see some Moodle ER diagrams at the following locations

 

http://docs.moodle.org/en/Development:Database_schema_introduction

 
Average of ratings: -
Picture of Claire Browne
Re: Databases: Database model
 
Hi,

Have you got a Database Model for the Forum module?
 
Average of ratings: -
Frank Ralf
Re: Databases: Database model
Group Documentation writers
Hi Claire,

Please see Database FAQ for some pointers.

hth
Frank
 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

help me pls. i want ot see the entity reltioshipt use rto course

 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

how about the log in?

 
Average of ratings: -
Picture of tran van thuong
Re: Databases: Database model
 
hi every body, the ERD of moodle that create by DBDesigner is very usefull. but nowaday, we have use moodle 1.9.n, so we need to create ERD of moodle with present version. if you have moodle schema 1.9. please share it.
 
Average of ratings:Useful (1)
wen photo
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
I will second this. Just did a google search, and the closest that I could find is this page:

http://docs.moodle.org/en/Development:Database_Schema

But apparently this is outdated (no 1.9.x and 2.0 dev).

By the way, if you have SQLyog, you could mouse right click on the database, and choose the "Create Schema For Database in HTML (Ctrl + Shift + Alt + S). This will give you a good schema (although there is NO comments for each table and fields, which would be great if someone has a schema that is "commented"). Thanks!

 
Average of ratings: -
Frank Ralf
Re: Databases: Database model
Group Documentation writers
Hi,

You might find some more interesting links at Database FAQ.

hth
Frank
 
Average of ratings: -
Picture of Cristian Arroyo
Re: Databases: Database model
 
Hello you can send model ER Please, I need it urgent.
Mariela Greetings.

email: morrego@alumno.utalca.cl

Excuse I do not speak ingles.
 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

how about course

 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 
how about activities
 
Average of ratings: -
Picture of Gerber Incacari
Re: Databases: Database model
 

Hi all

I don't speak english, but interesting database model

Gerber

 
Average of ratings: -
Picture of Cristian Arroyo
Re: Databases: Database model
 
if, I need model BD moodle.
Greetings.
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
I have used DBDesigner (GPL freely downloadable etc) to create another ER Diagram, which you can see at

moodle.png

I would post it here but it is about 130Kb and I didn't want to post such a fat file. I can post the xml which can be edited in DBDesigner if anyone is interested. Again that is around 130Kb.

Marcus
 
Average of ratings:Useful (4)
Picture of Rose Rosse
Re: Databases: Database model
 
I would be interested in your XML file...
By the way, which is your Moodle version? Is the last one (1.4+)?
Anyway, I would appreciate if you could post it here...
Thanks a lot in advance. sorridente
 
Average of ratings: -
Picture of ryan wise
Re: Databases: Database model
 
I'd be interested too. Thanks for the help.
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
You should all be able to access the DBDesigner xml from the following link

http://www.jchq.net/moodle.xml

Note that there are probably many, many innaccuracies and I am not absolutly what version of moodle this was taken from, though I think it is 1.4. I would appreciate any feedback/corrections to the design, and it seems most appropriate to post it here for others to see.
 
Average of ratings: -
Picture of Jose A. Ruiz
Re: Databases: Database model
 
TKZ
 
Average of ratings: -
Picture of Junior Calzado Muñoz
Hola desde cuba
 
Deseo establecer uan amistad contigo si es posible.Gracias
 
Average of ratings: -
Picture of mana sopa
Re: Databases: Database model
 
please., I want MOODLE DB xml which can be edited in DBDesigner
 
Average of ratings: -
Martin Langhoff - Sailing
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
I don't have that fancy XML file, but I've been playing with an alternative DB Schema graphig tool called schemaball. Fun!

It has highlights for the key tables, and all relations to those tables. The regexes didn't catch all the relations, I should polish them a bit.

 
Average of ratings: -
Martin Langhoff - Sailing
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
Some nice colours graded on how far a link and a table are from the "key" tables user and course.

 
Average of ratings: -
Picture of mana sopa
Re: Databases: Database model
 
thank you very much
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
http://www.jchq.net/moodle.xml

Note i do not claim it is particularly accurate, it is just a starting point (see my earlier posting)
 
Average of ratings: -
Picture of Antonio Martinez
Re: Databases: Database model
 

Hello you can send model ER Please, I need it urgent.

email: tigrewtono@hotmail.com

           tigrewtono@yahoo.com.mx

Excuse I do not speak ingles.
 
Average of ratings: -
Picture of Jose Peña
Re: Databases: Database model
 
Yo tambien necesito el modelo ER de moodle, si lo encuentras me colaboras
Jose
 
Average of ratings: -
Picture of Aldin Pagdonsolan
Re: Databases: Database model
 
hi marcus,

do you have a complete ERD of moodle? Can you share it with me.. I need to see the ERD specifically the Course schema.

Thanks!
 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

can ahve copy of that entity pls

 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

how about the activities?

 
Average of ratings: -
Picture of danding aganon
Re: Databases: Database model
 

how about activties entity?

 
Average of ratings: -
Picture of anne villanueva
Re: Databases: Database model
 
how come you don't know how to speak English?
if you don't mind your message is already composed of English words,wink

 
Average of ratings: -
Picture of Pablo Picasso
Database Fields Descriptions NEEDED!!
 

hi!

is there anybody who knows where i can get an detailed descriptions of the most important tables and fields in the Moodle DB?

i'm searching for a syntax like this one:

TABLE USER:

FIELD                         DESCRIPTION

id                              user's id
name                         user's name
emails                        users's email address

THANKS FOR HELP!

P.S. Please contact me via Email if you have any suggestions: pawel.lenart@fh-campuswien.ac.at

 
Average of ratings: -
Picture of Aileen Moran
Re: Database Fields Descriptions NEEDED!!
 

Hi! I was looking for that description too, and I found something very usefull in other forum.

I hope it's usefull for you too.

 
Average of ratings: -
Picture of Howard Miller
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Just a small caveat - there are one or two places where the Moodle database doesn't quite fit the true relational model. This will probably result in important relationships not being shown in some of these modelling programs. For example the link between the modules list in a course and the actual modules tables is based on the *name* of the module table. Viz. this is a Quiz (say), so look up it's id in the Quiz table - not a proper relation.
 
Average of ratings: -
Picture of Gert Sauerstein
Re: Databases: Database model
 
Year!

I found another sidekick yesterday in the relation between mdl_course_module and mdl_course_sections. The relation between both tables is made by a MEMO field in mdl_course_sections called sequence. It is realized ONLY by the PHP course format library.

The problem is that the MEMO field is also not indexed, so it is really impossible to join both tables using my DBMS. I currently try to develop a new course format applying some filters to the course sections and the current implementations is really slow because i have to do all the calculations on this MEMO field in my main moodle application in system memory instead of having done this by DBMS.

I am sure there are many more such "dirty" relations in moodle database and i hope that they will be cleaned out in an upcoming future version.

Yours, Gert
 
Average of ratings: -
Picture of nicolas estrada cruz
Re: Databases: Database model
 
thank`s for you help, in this days i send the model of the database in xml
 
Average of ratings: -
Picture of Daniela Vielma
Re: Databases: Database model
 
Hola Nicolás, por casualidad tienes el archivo xml de la base de datos de moodle para para DBdesigner 4?

Si lo tienes me lo podrías facilitar?

Gracias
 
Average of ratings: -
Picture of Katarzyna Potocka
Re: Databases: Database model
 
Hello,
I do some kind of changes with the assignment module and tried to find out what the "format" - field in the mdl_assignment_submissions table is for.
Does anybody know if this is the same as the entry in the mdl_assignment table -> "format"

thanks for help
KAsia
 
Average of ratings: -
Picture of Maram Meccawy
Re: Databases: Database model
 
Hi,
I'm examining moodle's 1.7 DB structures and all the files that have been provided here were really helpful..thanks a lot every one.

The problem that I have is that my mysql DB doesn't show all those tables. For example I don't have the following tables:

mdl_user_students
mdl_user_teachers
mdl_user_coursecreators
mdl_user_admins

has things changed between versions or am I missing something here?

I need this information (especially students info.) and hence would really want to know what replaced those tables?

I have found some new tables such as:
mdl_user_info_category
mdl_user_info_data
mdl_user_info_field
mdl_user_info_lastaccess
mdl_user_info_preferences


but the first 3 were empty and I'm not sure that they do replace the other tables.

I'm doing my PhD research using moodle and it would help me a lot to get each and every detail here. Thanks a lot again.

Maram
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Hard-coded teachers/students etc. went out in Moodle 1.7. See http://docs.moodle.org/en/Roles and http://docs.moodle.org/en/Development:Roles

The list of new tables is to do with allowing more flexible user profiles.
 
Average of ratings: -
Picture of Maram Meccawy
Re: Databases: Database model
 
Thanks a lot Tim!

Did any provide similar files (XMLs, EXCEL, Visio..etc) showing this update information and table structure?


 
Average of ratings: -
wen photo
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
it would be good to have a moodle 1.8 vesrion of these documents (XMLs, visio, etc.) too! Is this information currently available on docs.moodle.org? thanks!
 
Average of ratings: -
Picture of Jaime Martínez Corbatón
Re: Databases: Database model
 
Hi all!

I need an ER diagram about roles.

I need to apply some roles to my users using mysql and I don't know how can I do this. It will also be very usefull a table description for these tables:

mdl_role, mdl_role_allow_assign, mdl_role_allow_override, mdl_role_assignments, mdl_role_capabilities, mdl_role_names, mdl_context, and mdl_capabilites.

I don't know the relations between all this tables.

Can you help me?

Thx!

Sorry for my english.
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
 
Average of ratings: -
Picture of Pete Richardson
Re: Databases: Database model
 

I have also been analysing log data in Moodle v1.4 & v1.5.

I need to work out which fields in v1.8 now contain information about who is acting as a teacher for each course?

I was using the fields; id, userid and course from the table mdl_user_teachers

Any pointer would be helpful.

TVM
Pete

 
Average of ratings: -
Picture of Jas Sahota
Re: Databases: Database model
 

Thank you all for the diagrams and other very useful infomation.

It had really helped me.

Thanks again!

Jas

 
Average of ratings: -
Picture of Myra Oliveros
Re: Databases: Database model
 
hi everyone!

i need a database schema for moodle 1.8. hope someone can help me out.

thanks,
myra
 
Average of ratings: -
Picture of isaac cueto
Re: Databases: Database model
 
Hi all, I'm working on my tesis about moodle, and algo wanted a ERD-diagram for moodle, so I take DBdesigner and start working on a ERD-diagram for moodle 1.8.2+ since it apears to be some changes between version and I think that a updated moodle ERD-diagram is a "must have thing".

but I have some troubes,

first the DBdesigner hang out went I put "built relations" so I think there is no other way than creating the relation manually

second as many has said there has been changes in the names of the tables

so if I compare the ERD-diagrams posted here and a moodle 1.8 ER-diagram I find out that in moodle 1.8 there are more tables and some of them has others names. I would apreciate a little help with the name of the tables and their relations.

thanks in advance.

I have attach the XML model.
 
Average of ratings:Useful (1)
Picture of Hector Fuertes Hernan
Re: Databases: Database model
 
Hi,

I'm working for my university as well with the ER-diagram and I would like to know if you got something with DBDesigner4 or with any other tool. I have been looking for the Modlee ER-diagram (version 1.8 or later) and I haven't found anything.

Isaac, I sent you a message to work together but I don't know if you have read it.

Thank you all very much,


Héctor Fuertes


 
Average of ratings: -
wen photo
Re: Databases: Database model
Group DevelopersGroup Particularly helpful Moodlers
Hi Isaac, thanks for your file, it was really helpful! By the way did you get a chance to try out "build relations" to using the Moodle 1.9 codebase, is it still hang in DBdesigner? Thanks!
 
Average of ratings: -
Picture of Melvin Campos
Re: Databases: Database model
 

Hi everyone!!

I need your help

Im looking for the ER for Moodel 1.9.

Does anyone has it?

It will be very helpful!!!

Because I need to figure out how to comunicate Moodle with other system.

I need to transfer info. from Moodle to others.

thanks in advance.

 
Average of ratings: -
Picture of TEC Services
Re: Databases: Database model
 

Hello everyone,

Does someone have (or know where I can get) an ERD for the base install of v1.9? We're on a Windows system with SQL Server 2005 that does not show the table relationships after installing Moodle, and we don't have an easy way to setup a LAMP system.

Any help would be GREATLY appreciated! Thanks in advance!

Sincerely,
Jason

 
Average of ratings: -
Picture of rafael de alencar lacerda
Re: Databases: Database model
 
Hello everyone,

I need the moodle 1.9.3 or upper database model.

I can't find it on the web.

Who can help me?

Thanks
Rafael
 
Average of ratings: -
Picture of Sven Trümper
Re: Databases: Database model
 
I also... WANT! ERD for 1.9.n! Please!
Sad that the core developers don't provide a ERD for a huge product/project like Moodle.

so... please, give us some ERD! Please! </beg>


regards,
sven
 
Average of ratings: -
Picture of Giridhar S
Re: Databases: Database model
 
hey man,,,why ru searching so hard,, i will help u?

i dont know this will u? but i think it will work for u!

here it is http://download.moodle.org/download.php/stable19/moodle-weekly-19.zip
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
No, we don't have an ERD diagram. Do you think an ERD diagram with 200 tables on it would be any use to anyone?

What we do have is Development:Database_schema_introduction, which breaks the tables down into moderately sized groups, which may help you to understand them.

Also, if download Moodle 2.0 dev and go to HEAD ► Administration ► Development ► XMLDB editor you get a [Doc] link next to each plugin, which will automatically generate documentation for each table, based on the comments in the database definition files. (Which are, admittedly, of mixed quality. Now that we have this script that actually uses them, hopefully we can start improving them.)
 
Average of ratings: -
Picture of Stephen Martin
Re: Databases: Database model
 
Unfortunately there is a lot missing from the Development:Database_schema_introduction pages. An ERD would at least give an idea of how the tables are connected.
 
Average of ratings: -
Picture of Michael Thrower
Re: Databases: Database model
 
Yes it would be a tremendous help. 200 table erd's are more important than 10 table db erd's. thanks for the work so far on schema docs.
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
I think it would be enormously helpful.
 
Average of ratings: -
Picture of michael jeffreys
Re: Databases: Database model
 
yes yes yes. an ERD would be very helpful. Or at least let us know what the joins are and I will build one myself.
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
As I think has been said several times before, all the foreign key relationships are defined in the install.xml files, even though for historical reasons, they are not set up in the database.
 
Average of ratings: -
Picture of michael jeffreys
Re: Databases: Database model
 
Great, thanks. I will try and work with that. Sorry that I have not read all of the messages
 
Average of ratings: -
Picture of Remy furtado
Re: Databases: Database model
 

where is the install.xml file located?

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Throughout the code-base, there are folders called db (approximately one per plugin, since Moodle is modular, plus the main one lib/db), and the install.xml files live in there.

Use, a file search utility to find them all.
 
Average of ratings:Useful (1)
Picture of Zahra E
Re: Databases: Database model
 
Hi guys,
I am using moodle 1.8.2+ and want to have a report of the students of each course.
I don't know which tables can help me in this subject
I should add this point that my moodle DB doesn't have table mdl_user_student.
may you help me?

 
Average of ratings: -
Chris on a big rock
Re: Databases: Database model
 
"Do you think an ERD diagram with 200 tables on it would be any use to anyone?"

It would be a heck of a lot more useful than the complete lack of 1.9x diagrams that currently seems to be the case.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
By the way, if you now download a copy of Moodle 2.0 dev, then under Admin -> Developer -> XMLDB Editor there is a [Doc] link at the top that generates human-readable HTML documentation from all the install.xml files.

Of course, it is only as good as the comments embedded in the table definitions, and somethings had changed between 1.9 and 2.0, but you may still find it useful.
 
Average of ratings:Useful (1)
Picture of Fatemeh Orooji
Re: Databases: Database model
 
Hi, I can not find Moodle 2 in above address. Since I need the ER for my development, Is there any where I can find it?
 
Average of ratings: -
Picture of Seyedeh Fatemeh Noorani
نظر در مورد: Re: Database model
 
where is Moodle 2. Dev?
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: نظر در مورد: Re: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
You can download the unstable development version of Moodle 2.0 from the bottom of http://download.moodle.org/. (Sorry for using cryptic short-hand.)
 
Average of ratings: -
Computer Dream Team
Database model
 
Hi Moodlers!

Does anyone gets the v1.9.5 model from DBDesigner, I have one but I don´t know how to get a single relation, appeared a lot of relations for a single table, I can post that but help me to find the correct relations between tables. I only include a few tables because I considered that are the most important.

If anyone has the 1.9.5 model please post it.

Greetings from Mexico.
 
Average of ratings: -
Frank Ralf
Re: Databases: Database model
Group Documentation writers
Hi Javier,

Please have a look at Database_FAQ#b.29_Moodle_database for further information.

hth
Frank

PS
You should definitely upgrade from Moodle 1.9.5
 
Average of ratings: -
Picture of Carlos Andrade
Re: Databases: Database model
 
Hello, it's been a while i've been trying to dig this ERD for my university, which uses Moodle and I see that, like mention before a 200+ table would be sort of useless to a staff if all together with a hell load of lines everywhere, but I noticed something very nice on http://moodle.org/file.php/11/moddata/forum/110/268325/db-edudist-Moodle-1.7_.rar

We can actually see separated xml diagrams with the releations, while on the 1.8 that is not avaible, but there is now an overall view, which is also great.

I've tested few tools so far(all on Ubuntu 9.04):

DBVisualizer, WorkBench and DBDesigner4(Which unfortunately only worked using wine).

Since Workbench itself is a 'new version' or so i believe to be of DBDesigner4, I was able to import all data from the http://docs.moodle.org/en/Development:Database_Schema section.

None of the tools i tried, however, using Reverse Engineering provide me the relations between the tables (which would be nice if displayed like in the diagrams of 1.7), I read on some other board post here on .org that would be due to something related to foreign keys, since workbench only show releationships to them (Correct me if i'm wrong please, i'm not sure..)

So I was wondering if both the xml diagrams were hand made by Alberto Giampani, Dario Toledo and Isaac Cueto. (the colorful big blocks that contain the small ones that contain the ids in 1.8x and the relations on 1.7x), or some tool like DBDesigner4 did the trick.

In case it was hand made, is there any real difference beetween the 1.98 and the 1.8x? Would it be possible to hand create an 1.98 version using as reference the 1.7x and 1.8x to organize an reverse engineering 1.98 model on workbench or would be there too much divergence?

We really want these diagrams so that the staff that administrate moodle on our university get used to it since most of us are scholars and undergraduate students on computer science, so it would be really nice.

Sorry if i repeated something stated previously, and thank you very much for your attention.

Edited: http://docs.moodle.org/en/Development:Database_schema_introduction

I've read this page, seems a very good start as well, altough it seems some documentation still missing on the sub blocks description (not sure). Which program is generating them? Are those models up to date on 1.9.8 or theyre standard for any version?

Carlos



 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Another way you can get documentation these days is to go to http://qa.moodle.net/admin/xmldb/. (You need to log in as admin, which is explained on the login page.) Then click the [Doc] link at the top of the page.

That gives you documentation of the (Moodle 2.0) database structure, compiled from all the install.xml files.

Of course, that will be more useful once Moodle 2.0 is actually released, and everyone is using it.

It would also be more useful if it contained more comments.

Still, it exists.
 
Average of ratings: -
Picture of Carlos Andrade
Re: Databases: Database model
 
Hello Tim Hunt,

First, thank you very much for your fast reply! This extra information will sure be very useful and i'll share with the staff at my uni.

But i'm still curious about the old schemes I've mentioned due to the information being organized as blocks, any clue about using them?

Thank you again,

Carlos
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
I don't know anything about the old database diagrams.

I do know about most of the little diagrams of parts of the database that are in Moodle Docs, because I drew a lot of them! I mostly learn about the database by reading the Moodle code (and once you are familiar with the Moodle way of doing things, it is easy to follow. Most of the database structure is pretty simple and logical.) If enough people ask about a bit of the database I know about, then I normally end up drawing a diagram some time, as a way of avoiding real work wink

Anyway, the reason that most database reverse-engineering tools don't work with Moodle is that by default, Moodle does not create the foreign key relationships in the database, even though they are defined in the XML.

That may seems strange, but you have to remember that Moodle started out on MySQL 3, which could not cope with foreign keys. Therefore Moodle never had foreign keys in the database. Therefore, there is probably plenty of places in the code that break referential integrity. So, if you turn foreign keys on now, things would randomly break (where now they appear to work, but leave garbage in the database).

Anyway, if you want to use a reverse engineering tool, what you can do is install a test copy of Moodle with the foreign key constraints in the database. I think all you have to do that is to edit http://cvs.moodle.org/moodle/lib/xmldb/classes/generators/XMLDBGenerator.class.php?view=markup&pathrev=MOODLE_19_STABLE before you install Moodle. Change the line

var $foreign_keys = false; // Does the generator build foreign keys

to
var $foreign_keys = true; // Does the generator build foreign keys

If I have remembered that correctly, then if you do that, reverse engineering tools should work after you have installed Moodle. However, only try that on a development set-up.
 
Average of ratings: -
Picture of Carlos Andrade
Re: Databases: Database model
 
Hello again Tim,

Thanks to clearfy the reason so many programs were not showing the relationship. I tried installing another local moodle with the foreign_keys set as 'true' but it was no use, the releationships still not showing on workbench sad

Since the little ones are yours I guess they're ok for 1.98 as well, i'll use them there smile

Regards,

Carlos
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
See http://moodle.org/mod/forum/discuss.php?d=108886#p479212 - if you are using MySQL MyISAM, then you won't get foreign keys. You need to use a real database like Postgres wink
 
Average of ratings: -
Picture of Bruce Cota
Re: Databases: Database model
 
I tried that several months ago. First, it attempts to create the foreign keys on a table when the table itself is created which is often before the referenced table is created, so the foreign key constraint fails and the database creation stops. I hacked around and got it to create all the foreign keys last, but then found that the initial data in a table was violating a foreign key by inserting 0's into id fields that were supposed to be foreign keys, so database creation again failed. Apparently "0" is used in moodle instead of "null" smile I fixed that, and then found another table doing the same thing. I fixed that and found another table .... so I decided it was probably a waste of time.

I suppose that could be done just to create the foreign keys and get a document, but since the foreign keys are not actually used in the code they have to be understood the way you understand a programmer's inline comments, not as real foreign keys.

Another odd thing I've noticed about moodle is that sometimes an "id" field seems to be a foreign key into *multiple* tables, determined by other columns in that table. So that is important information that wouldn't be captured in any reverse engineered diagram.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
You have neatly elucidated why turning on foreign keys is hard.

I would still really like to make the effort to get it turned on in some future Moodle version. If we can only get there, it would automatically find a lot of subtle bugs that are currently going unnoticed. But it is a lot of work, so I am not holding my breath.

I think what would be reasonable is to start enabling foreign-keys on a case by case bases, particularly for new tables. That is easier than fixing all the legacy mess. However, it is not yet supported. However, for some new development, I have manually created the foreign key constraints in the database, to catch bugs while I am doing development.
 
Average of ratings: -
Picture of Bruce Cota
Re: Databases: Database model
 
Actually the lack of foreign key constraints and transactions would make me very nervous about using moodle for a large scale deployment.

The problem is that on a large busy site, with servers getting restarted, every possible race condition getting exercised, custom code getting installed and pulled out, people running SQL by hand, basically any kind of data that is permitted by the database schema actually gets created at some point.

All code is written to assume certain relationships in the data and breaks when those relationships don't exist. So when "invalid data" gets into the database something often breaks and somebody -- usually a well paid DBA -- has to spend hours or days figuring out what's happening. On a large, busy site if integrity constraints aren't declared and enforced in the database and/or the application doesn't use transactions and concurrency control appropriately, bad data generates a steady stream of customer complaints and fixing it turns into a full time job.
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers
Can anyone contradict my assumption that no live installations have foreign key constraints implemented with an Innodb database (i.e. where it would be recorded/stored and actually do anything).

I have been exchanging emails with Carlos (see this thread) about automagically generating an ER diagram, but the underlying design of the tables is not entirely relational so any diagram generated will not tell the whole store about how data is to be used.
 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers

I have been working on creating an ER diagram for the Moodle2 RC1  using MySQL Workbench reverse engineering from an install. I have attached a file that illustrates some bits of the Quiz/Questions parts of the system. If anyone is interested I can make the .mwb file available containing all the tables for a fresh install so you can play with the bits you are interested and lay it out for ease of understanding. It includes the table comments but not indexes.


 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Of, course, these parts of the databse structure are already documented: Development:Question_database_structure and Development:Quiz_database_structure

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers

Indeed Tim, your documentation of the Quiz parts is excellent. I only started with that as I am quite interested in quiz engines (I spent a lot of time creating one once). I have done some more work on a diagram of the whole system.

I have attached a cropped down version to this post and you can see the full sized png at

http://www.jchq.net/ mdl2erd.png delete space between domain and file to make it work, otherwise this forum seems to bring in the actual image

If anyone is interested I can post the Workbench file and any feedback would be most appreciated.



 
Average of ratings:Useful (1)
Picture of revo hsalf
Re: Databases: Database model
 

Hi Marcus,

Thanks for the diagram ;)

Can you pls share the workbench version ?

No hell way to get it done with my machine and ressources :/

 
Average of ratings: -
Picture of Marcus Green
Re: Databases: Database model
Group Particularly helpful Moodlers

Revo...Its a bit too big to attach as a posting in this forum (400 Kb ish). So I have sent it to you via email. If anyone can suggest how I can post the workbench file somewhere at moodle.org. I can of course copy it to some of my own web space and post a link to that.

 
Average of ratings: -
Frank Ralf
Re: Databases: Database model
Group Documentation writers
Hi Marcus,

You could upload it to Moodle Docs. The maximum file size is 2 MB there: http://docs.moodle.org/en/Special:Upload

hth
Frank
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
You would have thought that lack of integrity constraints and transactions would lead to problems. Certainly that is what one is taught on a database course.

However, it seems that in practice that just doesn't happen. And plenty of people out there do run Moodle at large scale.

I can think of two main reasons why we get away with it:

1. The stateless natures of HTTP really helps, and the fact that Moodle processes each request separately.

2. The relatively simple nature of most relations in Moodle. In particular, a lot of the data is somehow linked to a particular user, and people tend to do one thing at a time.

3. Orphaned records in the data (for example posts belonging to a forum that has been deleted) don't acutally do any harm.


Dan Poltawski did some investigation of the Moodle sites he is responsible, using a script that read the foreign-keys from the install.xml files, and then constructed queries to see where they were violated. See MDL-17623.

There is also an automated version of that report in the XMLDB editor in Moodle 2.0.


Also note that Moodle 2.0 will be strongly encouraging InnoDB so we can start using transactions.
 
Average of ratings: -
Picture of Bruce Cota
Re: Databases: Database model
 
Actually I've never taken a database course, I was speaking from experience on a couple of sites with high traffic. I always seem to spend a lot of time hunting down problems due to dirty data and then add a constraint or trigger or transaction or some kind of pessimistic locking (which requires transactions) to prevent it from happening again.

So when I look at Moodle and see none of the above I'm kinda freaked out smile

Are you telling me that if you have a site processing, say, 10 web page views a second and a web server crashes -- nobody's data in the database will be hosed? Or if there's some kind of issue and the site gets slow and users start double and triple clicking, nothing goes wrong?
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
If you have a MySQL database, with MyISAM tables, and your server crashes, then you have problems.

At the OU, we have a Moodle site that gets about 50,000 unique visitors a day, about 500,000 page views, which is about 5 per second.

That is running on Postgres with 4 load-balanced web servers in front. I am not aware of us getting data corruption problems.
 
Average of ratings: -
Picture of Bruce Cota
Re: Databases: Database model
 
Well, out of those 5/second most are probably read-only/mostly, but I'm still surprised there aren't any problems at all. Certainly with that much traffic there must be some opportunity for race conditions to show up.

Is there nowhere in moodle for example where somebody wrote a POST operation that does something like this

1. Read A from database

2. Make changes in database based on old value of A

3. store new value of A into the database


Because without transactions for concurrency control in the database, I don't know how you can prevent a process from executing step 1 just before a second process starts step 3, in which case the first process creates invalid data in step 2. I've seen this (not in Moodle) even when A is user-specific data, particularly when there are temporary performance issues and the processes slow down and/or impatient users start resubmitting requests.

Have all issues of this kind been stomped out in moodle? Or is there a fairly high tolerance for this kind of error in the moodle community? Or has moodle just been lucky smile

[Now I am the topic of integrity constraints and into transactions and concurrency control.]

Because I've seen that to be a fairly common pattern
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
I am fairly familiar with concurrency issues (for example, I have written multi-threaded Java code in the past). I agree that it seems crazy that Moodle can work without transactions, etc. But it does seem to be a complete non-issue.

There are loads of bugs reported to the tracker, and I don't recall seeing one that is related to the lack of transactions. If there are things you want to worry about in the Moodle codebase, there are more important issues than this.


I suppose that even on a busy site ... well to continue with the OU example. So, we have about 5 requests per second, but actually those are spread (somewhat unevenly) over about 500 active courses, which suddenly takes the concurrency estimate way down again. On top of the point you make that there will be a lot of reads.


The worst-case scenario is probably when you have a whole class doing some interactive activity together. But if you want to do a back-of-the envelope calculation about what might go wrong, you start thinking about the nature of the activities.

Quiz, assignment, choice, glossary, ... each student's contributions are more-or-less independent of each other.

Something like forum, well students are only adding posts to a thread. You can only really get an issue if a moderator deletes a post at the same moment a user tries to post a reply, and seemingly the probability of that is vanishingly small.

Chat, again, is all inserts, and probably only inserts into a single table, so actually the key bit of that will be atomic.

I don't like to think what would happen if a student opened their quiz in IE and Firefox, and clicked Submit all and Finish in both in quick succession - but students don't tend to do that. (Also, I am currently rewriting the quiz, and the new version will have transactions.)

Also, if two teachers were editing the same course simultaneously, and moving activities, that might be bad, but it does not seem to happen in the real works.


Actually, one place I know where you get problems is restoring a backup (which you often do to copy a course). If you get an error half-way through a restore, then you get a lot of garbage in the database. So, I know that at the OU we wrapped the restore code in a transaction. And most of the OU custom code does use transactions, because we can, and obviously, if you know how, you just write code like that.

But anyway, whatever your previous experience tells you, I don't think you need to worry about the lack of transactions in Moodle.
 
Average of ratings: -
Picture of jason ott
Re: Databases: Database model
 
A 30,000 ft view of locking:
MySQL uses table, read/write level locking to manage concurrency (among several other locking mechanisms). The table level locking prevents starvation and helps hold integrity.

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html
 
Average of ratings: -
Picture of Carlos Andrade
Re: Databases: Database model
 
Oh i'm using Mysql, and i'm not used to Postgrees so that might take sometime. Thanks for all your help, and thanks for your report too Bruce Cota. I'll take a note on that to share with staff as well.

By the way, thanks (a lot) for the other link, i didn't see that one and it got a shot of what ive been looking for the whole day! Too bad the file for the workbench is not working anymore, i'm gonna post there asking about it.

Edit: Its actually working, you just need to copy and paste instead of click! Awesome smile

Must make a short map with all the information I gathered today, very helpful and will sure make a huge difference for all the new scholars who will join our project soon (and for me too!).

Best Regards and big thanks³!

Carlos
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Database model
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Well, the InnoDB storage engine is MySQL can also do foreign keys, so you can use that as well.
 
Average of ratings: -
Picture of ram g
about installation moodle db
 

ok

 
Average of ratings: -