Problem with db migration

[woblavobla]

Trying to use fb adapter with new version of redmine 3.3 (uses rails 4.2.7.1), but can't even migrate db.
The problem is in generated sql queries to firebird. Sometimes no quotes where it is necessary.

[woblavobla]

Example of problem:
ActiveRecord::StatementInvalid: Fb::Error: Undefined name Dynamic SQL Error SQL error code = -204 Table unknown PERMISSIONS At line 1, column 8 : INSERT INTO "PERMISSIONS" ("CONTROLLER", "ACTION", "DESCRIPTION", "SORT", "IS_PUBLIC", "ID") VALUES ('projects', 'show', 'label_overview', 100, 1, 1)

[rowland]

You've shown a symptom of the problem. You cannot insert a row into the permissions table because the table does not exist. What happens when you attempt the migration that should create the table?

[woblavobla]

@rowland I think, i found a problem. The problem is that the created tables wasn't commited to db. And when some statements trying to do something with this tables, it falls with exceptions. Temporal solution for me is to commit every change in metadata.

[woblavobla]

Another problem that some queries is incorrect, because some firebird keywords not quoted and not in upcase, when table has columns with keyword names
Example of bad query:
SELECT DISTINCT "ISSUES"."CREATED_ON", "ISSUES"."ID" FROM "ISSUES" INNER JOIN "PROJECTS" ON "PROJECTS"."ID" = "ISSUES"."PROJECT_ID" INNER JOIN "CUSTOM_VALUES" ON "CUSTOM_VALUES"."CUSTOMIZED_ID" = "ISSUES"."ID" AND "CUSTOM_VALUES"."CUSTOMIZED_TYPE" = 'Issue' WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((custom_values.custom_field_id IN (8,10,12,13,14,42,31,40,33,37) AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = custom_values.custom_field_id)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = 1 AND ifa.id = custom_values.custom_field_id) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = custom_values.custom_field_id)))))) AND (((custom_values.value LIKE '%tata%'))) ORDER BY "ISSUES"."CREATED_ON" DESC, "ISSUES"."ID" DESC)

[rowland]

Apparently most users of activerecord-fb-adapter color well between the lines, avoiding Firebird reserved words, or use SQL-based migrations like my shop does. The reserved words issue probably wouldn't be too difficult to solve with a judicious modification to the quote_column_name method. The issue of committing schema changes before data operations might depend on whether ActiveRecord has a hook we can tap into.

[woblavobla]

@rowland but the problem is, that quote_column_name not called for already defined string with sql ex(count(*) as count) ('where value = 3') and so on

[rowland]

While it's true that ActiveRecord (at least mostly) does not parse SQL and so raw, database-specific SQL employed by an app may break things for unsupported databases, the examples you cite are unlikely to be a problem. The Firebird convention is to use uppercase identifiers and unquoted identifiers are treated as uppercase. The adapter/driver lowercases resulting column names (except where they are mixed case) to match Rails conventions.

At the end of the day, Redmine will not support Firebird unless the team behind it decides to. You may get a particular version working, but the next version may break.

[woblavobla]

@rowland Right now i'm trying to make my fork 3.3 of redmine work. Now it is working good, but not all of it's plugins. It is so sad, that redmine developers using hardcoded sql queries

[woblavobla]

Fun part that redmine developers said, it is not their problem.