This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product. Otherwise - post away!
With regards to deploying applications that use Lightspeed, do you have any support within Lightspeed for creating and updating databases on the client.
This particular app is a winforms client and the user can select different database types (eg file based, sql server or mysql) and the app sets the LS provider type & connection on startup. Because the app is for users that are often challenged to do anything beyond surfing the web the app needs to take care of all the database creation and schema updates.
Is there anything in the LS libraries that I can use to do this? for sqlite, the app can use System.Data.SQLite.SQLiteConnection.CreateFile() to create a new database file, but it still needs to create the tables etc. Sure I could log the sql generated by the LS designer and execute it manually, but that won't cope with changes to the schema.
You can use the LightSpeed 3 migrations feature to create and update database schemas on the client. For creating migrations, see the Help Topics > Migrations section in the help file. For running migrations programmatically, see the Mindscape.LightSpeed.Migrations namespace in the documentation, especially the Migrator and AssemblyMigrationLoader classes. We don't have samples for this yet but would be happy to advise.
However this does not take care of initial creation of the database. So you would need to call SQLiteConnection.CreateFile() or some other database-specific creation API. You could then pass the connection string for this newly created database into the migrations API and use that for table creation and updates.
(NOTE: We have had reports of an issue with hosting migrations in your own app where it fails to bootstrap a required versioning table when working on a completely empty database. So if you get an error that refers to the DBVersion table please let us know as it will help us investigate the issue and in the meantime we will be able to provide you with a workaround.)
I now have a migrations project and seem to be on the right track to using the LS migrations to populate/update the tables, but have run into the dbVersion table not existing. What is this workaround you mentioned?
I assume this is correct?
Mindscape.LightSpeed.Migrations.AssemblyMigrationLoader l = new Mindscape.LightSpeed.Migrations.AssemblyMigrationLoader("Tradetool3Migrations.dll");
Yep, that code looks correct.
The DBVersion workaround is just to fire up ADO.NET and create the DBVersion table manually. Here is some rough code to do that:
You may need to re-create the migrator if the exception fires -- this is code adapted from one of our other customers (thanks Thomas!) and I haven't investigated the behaviour in detail because we're more focused on fixing the underlying issue.
I have identified an issue with when I was calling the method which validates that the database has a DBVersion table and creates it if necessary. Unfortunately I was calling it a little too late in the process which was causing the issues you're experiencing.
I have made an update to the migrations code so that this should be resolved in the nightly build dated the 22nd of January.
Please let me know if that build improves the situation for you.
next question: should the migration be adding the intial value to the keytable?
The migration that i am using to create all the tables ends with
however, this appears to only create the table and not put in a default value. This is with VistaDB, haven't tested it with other provider types.
Yes, if you want to use KeyTable then you would need to put in an initial value.
You can do this using ADO.NET and an INSERT statement. However, I hope that a future release will change AddKeyTable to automatically insert a starter value, so I would strongly encourage you to perform a SELECT on the newly created KeyTable and check that it is indeed empty before performing the INSERT. This will ensure that your code does not insert a duplicate row if we later "fix" the AddKeyTable API.
If you're using a different identity method, of course, you can just delete the AddKeyTable call.