DB conversion script fails to update MySQL Lessons index that has changed

Hi, Charles.
Ah, sorry, I did neglect to tell you which LB code we have... We have 1.4.x lessonbuilder code from the 1.4.x branch, as of 8-Feb-2012. There weren't any tags or versions at the time for the latest Lessons source, so this was what you suggested to us as we worked on our 2.8.1 upgrade last year.

We do run with auto.ddl 'true' when restarting tomcat after a successful build and deploy of a new version of Sakai. So that will be fine for us. Prior to running with auto.ddl, we convert our Sakai database (in this case it is 2.8.1) by running the DB conversion scripts. So I can easily run SQL scripts to modify Lessons DB objects after other 291 conversions scripts are run – and then startup with auto.ddl on.

If the current 2.9.0 main Sakai DB conversion script continues to be required in an upgrade to 2.9.1, I'll continue to include the LB index modifications I've just made for the 2.9.0 upgrade – for our version of LB.

Thanks, again.
Cynthia Gast, UVa

The 2.9.1 upgrade script would include upgrades from 2.9.0 to 2.9.1, as 2.9.0 was the first release with Lessons. As it happens, there isn't anything from 2.9.0 to 2.9.1. There will be something for 2.9.2, and you might choose to do it now. (It changes lesson_builder_items.html from text to mediumtext).

Before 2.9.0 people installed Lessons themselves. So knowing you're on 2.8.1 doesn't tell me what version of lessons you're on. But the recommended approach is to turn on autoddl for one startup. Then hibernate will update the tables, and ./components/target/classes/mysql/simplepage.sql, which will be run automatically, will add indices. At that point the only update script you need is components/target/classes/mysql/simplepage.pre29-to29.sql. It has a couple of commands that those automatic methods won't catch.Everything else will be handled by autoddl.

If you can't enable auto.ddl I have a script that will produce an update file between any two versions.It will do what hibernate will do. So without auto-ddl

• prepare inputs for schemaupdate. See the README. These inputs are .sql files representing the old and new schema.

• use hbm/src/ddl/schemaupdate to produce an update .sql file

• run the .sql file

• run ./components/target/classes/mysql/simplepage.sql,

• run components/target/classes/mysql/simplepage.pre29-to29.sql.

Hi Charles:

I understand your approach, and it seems reasonable to me for this 281 to 29x conversion. A quick clarification question about this, regarding when we move to 2.9.1 (as we intend to do asap when its released).

Does this mean the 2.9.1 main DB conversion script (oracle and mysql) will omit the Lessons DB upgrade SQL currently found in the 2.9.0 DB conversion script, and include any (or no) Lessons SQL needed to move to 2.9.1 (from 2.8.1)? My question is, will we still need to work around this issue for the Sakai 2.9.1 DB conversion?

To illustrate, for our installation at UVa, when we move from 2.8.1 to 2.9.1, I'm assuming we will run MySQL DB conversion scripts, as follows:
1) 2.8.1 --> 2.8.2
2) 2.8.2 --> 2.8.3
3) 2.9.1 main Sakai conversion script
4) additional Lessons-specific SQL updates, indicated in Lessons README

Is this how you envision the process working?
Thanks very much,
Cynthia Gast, UVa

Note that the 2.9.1 related change is really just documentation. Since Lessons wasn't in 2.8, and there's no telling what version sites have that installed it themselves, I don't recommend putting anything involving Lessons in the 2.9 conversion scripts. There should be no change to the database between 2.9.0 and 2.9.1. All the newer features require database changes, so if we put any new features in 2.9.2, conversion scripts will be supplied.

Let me explain the approach I've come up with. THis is complex because I'd like people to update Lessons between Sakai releases.

WIth autoddl on, Hibernate will do most changes, and there's a file components/src/ddl/mysql/simplepage.sql that will do most of the rest.

There are a couple of exceptions that for one reason or another I can't make happen automatically. They will be in special files such ascomponents/src/ddl/mysql/simplepage.pre29-to29.sql

For people who run with autoddl off, I will supply scripts that do the stuff hibernate would do, for all Sakai releases. If you use code between releases, I have a script that will give you the update between any two versions. It's a combination of a way to generate the SQL that Hibernate would generate to build the tables, and an SQL diff program that will generate a script to change between any two copies of that SQL file.

This is all documented in the main README.