Indexes, Foreign Keys

[TRPB]

Currently Maphper does not add indexes. Any column which is searched on could be auto-indexed

It should be fairly trivial to add CREATE INDEX IF NOT EXISTS if editmode = true on any columns used in a WHERE/ORDER/GROUP statement (Which will also catch foreign keys).

However, proper foreign keys are significantly more work. Maphper has enough information to know which columns are foreign keys, however as it supports mapping data sources that are different this won't always be possible. It could be smart enough to add foreign keys to tables where required... again, however, this would have to be done each time a field is added. This also requires breaking encapsulation somewhere because the DataBaseAdapter must know about the relationships which are only currently relevant to the Maphper instance.

Both of these could be checked every time a query is performed while editmode = true, but is it worth the performance overhead even during development?

[thipages]

• Indexes : adding unique index(s) will be useful.
• FK : creating on index on dev instance should be quick most of time, but can be long*. A time measure test may be operated before executing indexation (?)

*mysql-how-long-to-create-an-index

Something else : maphper always adds, has it a mechanism to remove? (tables, fields, indexes)

[TRPB]

Maphper did have functionality for removing unused columns and optimising column types, however this was disabled as there were a few bugs.

For unique indexes, there's no way for maphper to know whether a field is unique (other than primary keys) so I don't think we can add this. If you add a record and trigger index creation, maphper cannot know whether the index should be unique or not. Just because there are unique values in the column at the moment doesn't mean there always will be.

Foreign keys are a bad idea from an OOP perspective. The more logic you have in the database, the less easy it is to change the data source. Foreign keys, constraints, triggers, stored procedures all move business logic into the database where it doesn't belong. If you do this, you can't then swap out a mysql database for a json file or a web service. Using the Data Mapper pattern, mappers should be interchangeable regardless of data source. Foreign key constraints makes this more difficult. Instead, Maphper will add indexes to foreign keys

[thipages]

ok. I need to immerge more in the project for unique index & removals
Agree for FK body guarding