DDL Import

[mbordin]

Hello,
is it possible to import DDL into a package?
We have a DDL file, a file contains SQL infrastructural instruction (CREATE TABLE, ALTER TABLE, CREATE INDEX ...Etc.etc) is it possible to import this file into EA like a reverse from a database instance?

[Geert Bellekens]

No, I'm afraid you'll first have to create the database (using the script) and then reverse engineer it from there

Geert

[tjs]

The ability to reverse engineer DDL seems like a commonly requested feature since 2002.  As far as I can tell, the functionality does not exist within EA v11.  Instead loading an actual DBMS schema w/ DDL is required.  The ability to import DDLs is important in facilitating collaboration with DBAs and maintaining traceability and synchronization throughout the entire design stack.

• Why has it not been implemented yet?  
• Will it be implemented?   If so, when?
• Is there a way to to view the product pipeline?

[qwerty]

I guess, it's the same answer like for so many other features not being implemented ever since: Sparx is not willing and/or not able. My expectations are near zero now. Basically is the same Ford T-Model  since the beginning. It now has a couple of over-paintings and a couple of new bells and whistles. But that's it. We still would drive the T-Model if no other automobile companies had developed. As long as there is no real rivaling product we will not see any real change  

q.

[skiwi]

Funny you should mention this,
It is a feature I dearly need.
On the proviso that it can also be used to update a previous import
(unlike the XML/XSD import which, AFASIC, does not).

What I have been debating is whether to ask SPARX for this, or suggest it the the DBMode people who might find it a useful feature to have to sell.

[qwerty]

Actually the DBMode people are Sparx too. Though not from Down Under but from Japan (and maybe more open to marketing a good idea). Too bad Sparx does not see the chance in opening a market place for useful add-ins. Clearly the market is not that from Apple's App Store but having some of those add-ins in a store would (even more) clearly increase the value of EA.

q.

[skiwi]

Hello,
is it possible to import DDL into a package?
We have a DDL file, a file contains SQL infrastructural instruction (CREATE TABLE, ALTER TABLE, CREATE INDEX ...Etc.etc) is it possible to import this file into EA like a reverse from a database instance?

Gosh bump

[George..Brennan]

I just been working on developing this MSSQL ddl file import scripting functionality.
Working for create table.

[MaXyM]

IMO, this way is not worth an effort due to complexity of implementation.
To import an SQL script, a parser covering complete syntax for particular RDBM has to be utilized. Then, considering differences in DDLs between databases EA would need to maintain parsers for all supported databases.

In fact it's easier to do RE from physically existing database, since most needed data are available in information_schema, and the rest is accessible from database system tables.

[George..Brennan]

Normally I'd agree but sometimes reading DDL is the only way where security means you simply can not access a production database.

I'm genuinely puzzled as to why you think I'd need parsers for the complete syntax for all supported databases?
Parsers aren't hard, the complexity is in the grammars so I'll extend it as needed....

My use cases were Oracle's then MSSQL's simple Create table syntax for a model so why do more, partitions etc?

[Geert Bellekens]

Normally I'd agree but sometimes reading DDL is the only way where security means you simply can not access a production database.

We usually create a local (empty) database from the DDL and then reverse engineer that into EA.
No problems with security or anything.

Then thing is that the reverse engineering imports much more than only the tables. It als import views, indexes, constraints, stored procedures, functions, sequences, etc....
Creating a DDL parser that supports all that, in all the different valid ways that can be specified in SQL, for all different database systems? That is indeed not a trivial task at all.

Reading all that from ODBC is (in theory) the same for any database.

Geert

[George..Brennan]

I agree and I normally avoid it like the plague but... sometimes organisational constraints means you simply can't avoid it.
So only parse the minimum required.

Over the years a couple of the more irritating things have been...
  'The approvals will take <insert number here> weeks and we need it this week'
  or more bluntly...
  'We use Sql Server, the consultancy looks after the Oracle database and no you can't have access to the 460 tables'
  'We have Db2/AS400 and no you can't have an account'

I've even hacked it into MS Access before now.

[Modesto Vega]

Just a number of random comments:

• Reverse engineering is, based on my experience, the best way forward with Sparx EA. Generally speaking, Sparx EA has no problem with this
• If you have worked with more than one RDBMS, or the same RDBMS, in different operating systems (and I include cloud editions under this banner), you probably have come across many nuances a parser has to handle and may have real trouble handling them.
• In my experience, when an organisation cannot "give" you a script of a production database (with no data in it). There is always an ulterior reason for it: the RDBMS is too exotic, somebody is trying to protect intellectual property, the data landscape has evolved organically and the organisation has way too many RDMBs in the estate, often out of support.
• INMHO, the cloud is not making any of this easier, essential because it is locking data structures away on virtual environments to which access is restricted.