General developer forum

Hey, when in Rome, I make sure I enjoy the pizza & pasta. When in Moodle, I make sure I get as much mileage out of the fact that we have easy to use datalib functions based on certain assumptions.

It actually works pretty well with an RDBMS that shall not be named which has problems (historically at least) with using multiple indexes per column. So selecting on your true unique set of values may be the relationally correct way, but pretty ineffective on M-named database engines.

And we use quite a few JOINs... and to scale better we should be pushing more work we do in PHP into smarter SELECTs that inevitably use JOINs (I'll dance and rejoice the day we can count on subselects to work). Arbitrary numeric id fields are great for that! JOINing on multiple keys gets us rather hard to maintain SQL.

I have some multi-page SQL statements from other projects that I am very proud of. But it's a dog to read and maintain. We do most of our work on Moodle without thinking much about SQL, and that's actually great. I'm pushing for a little bit more of SQL for better scalability, knowing that it'll have an impact on code maintainability and readability for new coders...

... now moving away from ids would mean we do all our SQL the hard way. Now, what feature or concrete benefit does it buy us? A bit less disk space usage?

It will force people to learn more about the table design they are dealing with, and that's good. Perhaps marginally faster on Postgres. But it will make the code more brittle and fiddly.

If it's about shutting up the RDBMS purists, earplugs are easier & cheaper

The University flew the two Martins (on separate occasions) over here late last year to give us training, presentations, etc. - and argument opportunities, of course.

By the way, so far in these forums I think you've only seen the two of us, mys'elf and Tim Hunt. We are on the same team, which is tasked with implementing Moodle features so that it's capable of doing the things our existing course website system does, and with a very slightly similar editing workflow facility. Our first release in May isn't going to actually do much that's available in Moodle, it's just going to do what our existing sites do. After that the intention is to gradually use more Moodle features.

Also on that team are Alan Carter and Derek Woolhead, who haven't posted here yet (and might be described as less outspoken, perhaps); the four of us share an office. The other key team is Rod Norfor and Nick Freear; they are dealing with tools, automated testing, source control, and 'our side' of the database import process from University systems. These people are working full-time on OU/Moodle stuff; there are also a few other developers working on related issues which might become part of Moodle in future.

(For example the e-portfolio system, which is initially using an outside solution. However the person in charge of that is also looking at the options available for Moodle, for later consideration... his comment in a meeting, 'Everybody seems to want e-portfolio systems at the moment.' my comment 'Except students.'

As for forks, I somehow don't think our management will accept a fork any time soon! We've done a lot of planning for our initial changes (the set we're making now, for that May release) to minimise the points where we have to touch core code.

But there are still quite a lot of changes - some of which are simple bugfixes or very minor tweaks (like making the Javascript function that hides/shows form elements able to work even if you don't have hidden fields that record their status, so that you don't have to include the fields if you don't care), or relatively minor extensions that directly follow the spirit of existing interfaces (like the 'letting modules control file download' thing I posted about). Those kind of things we would definitely like to contribute to the core. The rest of our diff... well, I guess managing it is Rod's problem.

(It's not that bad... but it would be better if we could get rid of that 'ought to be in core and the Moodle community wouldn't even object to it' stuff. I think we're going to take some time, perhaps after we've finished the work for our May release when hopefully our source code management is in a better state, to look at the differences and check which ones are OK to put in core. My post about the modules/file download thing is a first step at that, for a moderately interesting change that shouldn't be controversial.)

--sam

Yes, I've read it.

I think there are issues as to the way Moodle uses databases - the questionable use of IDs is certainly one, but the lack of referential integrity is more serious. [By the way, the '<table>id' convention suggested in the coding guidelines is at least a step up in this regard. We'll be following that one at least!]

There are of course big problems with adding referential integrity, for example if we add foreign key references to a courseid in some table, then we have to be sure the table entries get deleted by the course delete (either manually, which I think is probably better practice, or using the 'cascade' type things to make it automatically drop those rows if the course goes). So each time that happens it's a significant change and I can see why it hasn't happened (quite apart from issues like still supporting mysql 3).

Anyway so it isn't possible to address the database problems all at once but I think when making any changes we should ensure that we aren't shutting out the possibility for improvements in future. That was my initial concern with the changes to insert_record.

With respect to the initial subject of this thread though, having read the reasoning for it I think I can now agree with the decision to remove support for differently-named columns in insert_record. If get_record isn't going to work with tables that don't have an 'id' column, then it does make sense that insert_record won't allow it either; treat those utilities as a unit, so that you know that if you want to use one, you have to use the other.

If you're going to have to write SQL to access the table then you can deal with writing SQL to add items to it.

--sam

I clearly haven't re-read it recently enough. I had forgotten a few of the points. I am duly reminded.

And I think you have convinced me on the ID column thing.

 I think wiki-engine was picked out to make document pages to be supported by all community.  I make a first step to document one vexed aspect of moodle style and i'll be adding to that page some reasons if it come to my eyes, but i think this page will be helpful for community even if it stay as is now.

And i will happy if some of the main moodle developers add to that page self comments and reasons!

offtopic :

Teachers of this course!
I think it's time to change autolinkable resource like this: coding guide (http://moodle.org/mod/resource/view.php?id=2932) to web-page resource with link to moodle docs wiki

Fine by me.

This postgres query should list all tables without an id column.

SELECT pc.relname
FROM pg_class AS pc
WHERE
    NOT EXISTS(SELECT pa.attname FROM pg_attribute AS pa
                WHERE pa.attname = 'id' AND pa.attrelid = pc.oid)
    AND pc.relkind = 'r'
    AND pc.relname LIKE 'mdl_%'
ORDER BY pc.relname

It only finds mdl_log_display and mdl_sessions.