View Issue Details

IDProjectCategoryView StatusLast Update
0001385SymmetricDSBugpublic2021-08-13 18:34
Reporterdouga Assigned Tochenson  
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.5.3 
Fixed in Version3.7.3 
Summary0001385: dbimport fails to create SQLite tables with foreign key restraints
DescriptionIf the dbimporter is used with an XML input format and a SQLite database it will always fail to generate tables that have foreign key restraints.
Steps To ReproduceSetup one of the properties files in the engines folder to use SQLite database:
EG
db.driver=org.sqlite.JDBC
db.url=jdbc:sqlite:/opt/symmetric-ds/store-001/store.sqlite

Run dbimporter with sample create file

../bin/dbimport --engine ssm-001 --format XML ../samples/create_sample.xml
Additional Information[] - JdbcSqlTemplate - [SQLITE_ERROR] SQL error or missing database (near "CONSTRAINT": syntax error). Failed to execute: ALTER TABLE "item_selling_price"
ADD CONSTRAINT "fk_price_item_id" FOREIGN KEY ("item_id") REFERENCES "item" ("item_id")

In SQLite foreign key constraint must be specified in table create statement
EG
CREATE TABLE "item_selling_price" (
   ...> "item_id" INTEGER NOT NULL PRIMARY KEY,
   ...> "store_id" VARCHAR NOT NULL,
   ...> "price" FLOAT NOT NULL,
   ...> "cost" FLOAT NOT NULL,
   ...> FOREIGN KEY(item_id) REFERENCES item(item_id)
   ...> );
Tagsddl/schema, dialect: sqlite

Relationships

duplicate of 0002124 closedchenson Support foreign keys in ddl builder for sqlite 

Activities

rudiejd

2021-08-13 18:32

developer   ~0001990

In 3.12, this is working. I believe it was fixed by Chris's commit here: https://github.com/JumpMind/symmetric-ds/commit/35a4ef9d1ff01d6e78a1673a301707de12394022 for related issue 0002124.

Issue History

Date Modified Username Field Change
2013-08-15 14:51 douga New Issue
2019-04-22 13:21 elong Tag Attached: ddl/schema
2019-04-22 13:21 elong Tag Attached: sqlite
2019-04-23 20:51 admin Tag Renamed sqlite => dialect: sqlite
2021-08-13 18:32 rudiejd Note Added: 0001990
2021-08-13 18:34 rudiejd Assigned To => chenson
2021-08-13 18:34 rudiejd Status new => resolved
2021-08-13 18:34 rudiejd Resolution open => fixed
2021-08-13 18:34 rudiejd Fixed in Version => 3.7.3
2021-08-13 18:34 rudiejd Relationship added duplicate of 0002124