|
[
Permalink
| « Hide
]
Andrei Bautu added a comment - 13/Jul/08 09:25 PM
On the "Schema should never be imported, only used for comparison" statement: importing the schema might be a good thing though. Imagine this scenario: you have a user how uses Moodle without knowledge of phpmyadmin or mysql tool; (s)he installed Moodle by providing the database params received from the hosting provider. after a code update that trigger a database update, the db transfer tool will refuse to import an older database because the versions do not match.
Message from me to Penny:
- Here are some things I think we should discus: 1 & 2. About the CONTENT tag:
3. The export file currently contains the schema for the data taken from the install.xml files (for core and modules). I think data import should be like copying a file. It should happen in one of the following ways:
We could also add you're suggested scenario (compare schemata and import or refuse data), but I think this is the least useful case. If we leave only this situation, the tool will be too restrictive. People won't be able to use their own data backups after a database upgrade (which should be possible to do). Another example: if you take you're database snapshot from an installation with some module and try to copy it on an installation without that module, you won't be able (because you don't have the required install.xml to check it against). I can think of other bad things that might happen. 4. Same as 1 and 2. No new tags, unless proved necessary. Therefore, no import_record method. But if you really want it, I think it should be in the database_manager class where all the code for processing XMLDB objects is and because it's kind of database independent. Moreover, this method isn't something for every day use, like get_records, and putting it in moodle_database will do two things: make people abuse it and increase memory usage (not much, but 1k here, 1k there, soon we'll have 64M 5. The reset_sequence is useless if you'll agree with my opinions on point 3 (from the db transfer tool point of view, although it might be useful for other tasks). I can't check right now (my work PC is broken since Friday night 6. I like this idea: "During moodle installation, add hook to install from a file". Things related to remote databases can be done, but "blindly" because we can't get a correct schema from a database (i.e. if you export a database's schema, you will not get the same xml data that was used to setup the database). However, currently you can export your source database as an XML file and import it in a target database. It's a two steps process. If you agree, I would post a message on moodle forums to ask people to test the current tool and comment on it. Reply from Penny: I definitely respect your right to disagree with what we've talked about So can you please add your point of view to that bug and we can discuss it there tomorrow. I think that discussions of this nature are good because it thrashes out the best solution, and that it's important for them to be publicly archived so that people in future can go back and read through to understand why we choose a particular way. STATEMENT tag was designed for very simple inserts - it does not do proper inserts; my +1 to remove the STATEMENT tag completely and replace it with new section - fortunately backwards compatibility is not an issue here. I wonder what will Eloy say, I guess he is the one to decide whether to keep current STATEMENT tag and improve it OR remove it and add new CONTENT top level tag instead.
Ddl is really not designed to handle content inserts, dml is designed to do that - any new content import code that does the actual database inserts must go there. The main reason is lobs and oracle hacks - each driver may use different code to send these into database == it does not belong into sql_generator, sorry. We can not use current $DB->insert_record because it can not support id field. I personally do not think that this should be extremely "easy to use" because it would be extremely easy to shoot yourself in the foot (or both feet) Reset of sequences is imho required, I do not understand how would you solve it without this - am I missing something? After using inserts with ID you need to adjust the sequences in some databases, right? We need some consistency check tool that compares existing table structure with xml file, the test should be also done before the export - recently we had many reports of broken database tables caused by the groups upgrade in 1.8.x which was uncovered later during upgrade to 1.9.x hmm, there are probably several other problems, for example:
1/ during the export nobody should be logged-in and working, or else the database could be full of inconsistencies - admins may tweak firewall rules, filter access by IP in config.php or we could improve the maintenance mode 2. Several gigabytes big the xml file? I don't have such a file, but I estimate that it will take at least 15% than the actual file size (PHP will store all the data, the name of the object fields and the references between objects). This is why I've done the on-the-fly transfer method.
3. This is hard to do. Not only absolute links might get break, but also relative links. Also, I believe this is outside of this project's agenda. Penny? You're right that it's sort of outside the project's agenda but I agree that it's important. backup and restore does this, I don't think it would be too hard. Put it at the end of your task list and see what happens.
Andrei - do you have any further comments in response to Petr? Q: "After using inserts with ID you need to adjust the sequences in some databases, right?"
A: I was about to say that "they are not necesary if things are done the way I suggested (only use blank database or drop tables if they exist, or in other words, never reuse existing tables)". However, from what I recall, MySQL and SQLite adjust their sequences according to inserts, but PostgreSQL doesn't (Penny, am I right?). I don't know about MSSQL. So, yes, a set_sequence method is necesary. But, what's the "reset" in the name? Am I missing something. On previous post: "at least 15% than the actual file size" should be "at least 15% more than the actual file size"
Postgres doesn't, you need to set the sequence after insert. reset = reset to correct value. set_sequence would imply that you have to tell it what to set the sequence to. reset_sequence means, just reset it to the right place.
Hi,
well, it seems that this topic is evolving really fast, cool! B-) Here there are some comments about what "vision" I had for the migration utility: 1) New XML files will be 100% new and completely unrelated to XMLDB. I see the process of exporting like: 1) Under admin, there are one "Create DB Image" option. The admin selects it. And the process of importing like: 1) Under admin, there are one "Import DB Image" option. The admin selects it. That's all. That would be enough for people wanting to switch DB (it's only a matter of installing a new Moodle pointing to target DB) and also to developers. Keeping it apart from XMLDB stuff (but checking things are ok before exporting). How does it sound? About the XML format we can discuss but any simple <RECORD fieldname="fieldvalue" fieldname2="fieldvalue2".....> or CONTENT or whatever is ok for me as far as that info in 100% DB independent (nulls and empties are the critical ones). It's only how I thought it should work, more or less. Of course, feel free to change everything. But I think the ideas above are simple enough (and well separated), so we can have export/import working, and then add efforts to the checker and so on. Ciao the recoding of wwwroot - we have an unsupported replace.php script in admin that does that already, there are several requests fro improvemetns some with patches also; this is not related to backup restore
this is relatively easy to do imho yup. we can filter that both on export and import.
Sounds easy and powerful, replacing every $CFG->wwwroot occurrence by something like $@WWWROOT@$ (and the opposite on import) +1 Ciao B-) After talking with Petr on irc, the following (related to Eloy's comment) became clear:
Andrei - it is proposed that we have a meeting tomorrow to discuss this between me Petr Eloy and you. Petr has suggested 11am his and Eloy's time, CEST, which is 10am for me in the UK.
We're currently using jabber for communication - you should be able to use your gmail account, I am not sure what Windows jabber clients there are. Can you have a think before then about what tasks are required and add them as subtasks to the bug that I created for you? Eloy can you please create the room and invite us to it on the jabber server? Andrei has a gmail account that will probably do.
I've created the:
gsoc-20080716-db-migration room in talk.moodle.org jabber server. it's open, so feel free to connect there. Anyone can invite others. Ciao Here is the proposed XML Schema (and example) to be used for database migration:
<?xml version="1.0" encoding="utf-8" ?> <?xml version="1.0" encoding="utf-8"?> I do not understand the difference between string "null" and real NULL value, could you give some example, please?
Petr, in our discussion we thought the best way to handle nulls was as follows:
<field name="field" value="content">null</field> the first one says that there is data, and it is the string 'null'. The second says the field had a null value. Most of the type, value="content" is implied (as in the above example, most cases) so it is left out (implied) aah, forgive my XML ignorance, I am no expert
I expected something like <field name="photo" /> where no content means NULL I'm not sure parsers would differentiate between those, could be wrong.
Penny is right. <field name="photo" /> is a shortcut for <field name="sometext" ></field>. They mean the same thing.
Summary of today meeting + some additions:
(EXP) The export process will do the following steps: Special cases for 1:
Special situations for 2:
About wwwroot changing - I understand the hesitation about doing it, but I don't think its particularly risky (i've piped db dumps through sed to achieve this many times
).
One thing to note is that there is some stuff 'encoded' in the DB where it can't be found and replaced easily. I can't remember the major culprits off hand, but I suspect blocks are probably one - IIRC the config is serialized and stored in the db that way. But also perhaps the cache databases (perhaps cache data could be ignored from export?) I browsed my database shortly and didn't noticed any serialized data. It's posible that I've missed it, but this a good point.
Anyway, my point is that not all wwwroot appearences should be changed and we can't say which should and which should not. And now that I think about what Eloy said, this is a db transfer tool, not a site transfer tool. (e.g. I do my site with SQLite and later on, I move to MySQL or PostgreSQL). We could provide an option for the admin to decide and let him/her choose, but warn about possible problems. Special cases 2 - I really do not like them at all, nobody is going to stop export/import no matter how much it complains, then when it breaks several months later people would just file bug reports and expect us to fix their database somehow, which might not be possible
 Only people that really know what they are doing should be able to recover broken export file - we could add something like special attribute safetyoff="true" which would enable those special cases and extra recovery options. You would have to edit the export XML file by hand - this would be officially unsupported of course. yes, blocks are the encoded trouble, we also need to blacklist some columns The changing of www root is imo part of migration - at present this is not yet officially supported, but used very often.
You do not need to implement it now, it could be implemented separately or later, no problem When I say stop I mean it doesn't start at all and not that it will stop during import. The admin will answer all questions before any changes to the database are made. Is this better?
hmm, some clicking and confirming should not enable non-supported operations. We really do have major support problems here in tracker caused by broken database schema. This is a critical operation, there can not be any guessing - either by admins or by the moodle code itself
My +1 to make this db migration either 100% success or 0% (==prevented), nothing in between by default.
In any case, pretty please, implement the migration of correct data now and only after it gets committed start with potential improvements. We do need smaller patches with minimal implementation first, the special cases would make it much harder to review |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Bug
Open
Minor
[gsoc] database migration utility
Resolved
 |
|
