Troubles with MySQL database mapping

I am trying out LightSpeed for a small project I am making. I already have implemented a MySQL database and it works fine.

Now I want to use LightSpeed to easier query the database and have it implement the models in code. Using the LightSpeed Model editor I have dragged the tables from my MySQL database and the model gets generated. But when doing simple queries I quickly find out that when LightSpeed tries to query the database it looks up on the wrong table names. It uses its own generated names, i.e. if I have a table called affiliations with two columns, one called location_id another called person_id, then LightSpeed calls the table Affiliations and the columns LocationId and PersonId. It also uses these names for looking up in the database, which obviously fails.

I have tried manually adding Table Name and Column Name in the model editor without making any difference, it still tries to look up on the generated names.

I have uploaded the project I am working on (don't mind the strange names :P)

Thank you in advance, Tomasz

Your problem seems to be that the Affiliation entity maps the location_id column twice -- you have fixed up the column mapping in one place, but the other place is still causing a problem.

The two places are (1) the composite identity, where the LocationId field is mapped to location_id and (2) the Location association, which implicitly creates a LocationId field to hold the foreign key. The implicit field generated by the association isn't shown on the designer, but you can see it if you look at the generated code.

The fix is to tell LightSpeed that the Location association is NOT backed by its own foreign key, but is actually backed by another field that you have already created in the model. To do this, use the Key Property Reference setting. Click on the association arrow, go to the Properties window, and set Key Property Reference to Id.LocationId. This stops LightSpeed from generating a foreign key field named LocationId, and maps the association to use the LocationId field of the composite identity instead.

You will need to repeat this process with any other associations where the foreign key in the database is part of a composite primary key in the database.

(Note: normally if an association had a foreign key which didn't follow the XxxId convention, you would use the association's Column Name setting. That won't work in this case because it would still leave you with two fields, the identity and the foreign key, mapped to the same column. You have to use Key Property Reference to tell LightSpeed that the association shares a column with a 'normal' field.)

For more info, see "Foreign Keys That Are Part of a Composite Key" in http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/Using-Composite-Keys.

Also, you don't say whether your project is a greenfield development, or whether you have an existing database schema that you need to keep for compatibility reasons. If you're creating a model from scratch, we strongly recommend that you do not use composite keys -- it's much easier to work with LightSpeed if your database follows LightSpeed's expected conventions. In this case, developing the model in the designer then using Update Database or migrations to create the database is a great way to make sure that the database works smoothly with LightSpeed's default conventions. Obviously that won't be an option if you have to work with an existing schema though!