Support for SQLite modelling

[kodo65]

Hi!

I wonder if there is, or will be, support for database modelling towards SQLite-based physical databases?

Kind regards

[qwerty]

Should be possible using ODBC.

q.

[Aaron B]

From the release notes of the EA 9 beta:

SQLite added as a supported database for both ODBC import and DDL generation

[kodo65]

From the release notes of the EA 9 beta:

SQLite added as a supported database for both ODBC import and DDL generation

Great news! Thanks!

[kodo65]

Hmmmm, I just upgraded to v9.0.904 (Build: 904) and went into one of my physical db diagrams, double-clicked a table and... I couldn't find SQLite as a database type in the "General" setting.

Has this been postponed or...?

Cheers

[qwerty]

The ability to read... Aaron noted ODBC. So take that route.

q.

[kodo65]

Hi again!

Hmm, seriously! It's great that EA supports reverse engineering of SQLite-databases but generating DDL for that target is a chore currently! It can well be that I don't have a 100% grasp of *how* to do this in v9 of EA but all referential integrity stuff coupled with the primary key declarations MUST simply be known to the tool, otherwise there will be a huge need for manual tweaking of the resulting DDL-script. This is B.A.D.

Pity - I really like EA!

Cheers

[Aaron B]

It looks like there may be some issues with SQLite support in the current release of EA 9.  Please contact [email protected] for further assistance.

[Aaron B]

Users can now download the data types for SQLite for use in existing models from the Resources page.

Direct link: http://www.sparxsystems.com/downloads/datamodeling/SQLite3_datatypes.xml

Use the Import Reference Data command to import these types into your model.

[kodo65]

Hi!

Great news - thank you! I still have one more question though regarding referential integrity. In order to have EA generate valid foreign key constraints which will enable referential integrity in the physical database, it needs to know HOW to provide the correct syntax for SQLite. Again I might be "out in deep water here" but I think I'm correct when I say that EA needs to be tweaked somewhat more to fully accomodate SQLite or...? I know that alot of people think that SQLite is not "a proper db-engine" (I've definitely been one of them) but there are situations where it really shines! Please, Sparx, "spark up" the SQLite support just a little bit more:-)

Again - thanks for a great product, I hope I don't sound too fastidious:-)

[Aaron B]

Foreign keys are modeled in the same way for all database types.  For instructions on this process in EA 9, open the Learning Center window (View | Learning Center) and select Database Engineering > Physical Data Models > Add Foreign Key.

See also:
http://www.sparxsystems.com/enterprise_architect_user_guide/8.0/modeling_languages/foreignkeys.html

[kodo65]

Hi Aaron!

I was not referring to how to establish the foreign key association. I'm referring to how EA generates the resulting SQL DDL for each engine supported (Oracle, SQLServer2005/2008, Informix...). Unfortunately they all have their idiosyncrasis and SQLite is no exception to this fact.

The mere possibility to just establish foregin key relations between table stereotypes is very limited, unless the tool is aware of the differences between the physical implementation of them. Again, I prefer to have a pragmatic approach to this as it might be that I don't know of any esoteric setting in EA which will make it aware of these differences, but I'd thought it would definitely be "on" by default, but then again, I could be wrong...

Cheers

[kodo65]

Any comments on this?

Cheers

[Aaron B]

As far as I am aware, the DDL generated by EA will be appropriate to the Database type specified on your table elements.  EA should automatically output the appropriate syntax based on your specified database type.

If you have any problems with the DDL being generated, please submit a report with the details and include an example model if possible.
http://www.sparxsystems.com/registered/reg_support.html

[kodo65]

Hmmm, up 'til yesterday I'd used the beta v9 of EA and have experienced problems while trying to generate "proper" SQLite-DDL. Today I decided to give the production release of v9 a try and, voilá, the generated DDL checked out 100% = me VERY happy. It looks like I'm, from now on, able to model my SQLite databases in EA and then execute the DDL-script in, say, the SQLite Manager FF plugin:-)

Thanks!!!!!!!