#47 Automatic database creation via JDBC

[Randolf C. Richardson]

It would be very handy to have a JDBC option that sends
"SQL generation" commands (the ones that are normally
output to a file) directly to the connected database.

Currently I need to follow additional steps to execute the
SQL code seperately (generate the SQL code file,
disconnect Druid's JDBC connection, use my SQL tools
to drop and create the database, execute the generated
file, then return to Druid to re-connect to JDBC and
continue with the updated database structure).

If this was all possible via JDBC (don't worry about the
database drop/create commands because this can all be
added in the extra pre- and post- code options), overall
development time could be reduced for the user.

One alternative approach to consider with this is not to
use the SQL generated code; just program this JDBC
feature to compare entities in the database with Druid's
definition and use ALTER or DROP/CREATE commands
to synchronize everything (but do it all as one transaction
by default {this should be a checkbox option for those
who use non-transactional databases such as MySQL}
to prevent partial updates from making this look like a
broken feature). If this approach is to be used, then
consider adding to buttons to make it go: Update (to
change an existing structure using ALTER commands;
useful for minor changes), and Rebuild (to drop all
existing entities and re-create them; useful for major
changes)

Thanks in advance.

[Andrea Carboni]

Logged In: YES
user_id=109751

I don't understand the point. If you select a db or a table node you can do
a "rebuild via jdbc" (right click on the node) which does what you say. Do
I miss something?

[Randolf C. Richardson]

Logged In: YES
user_id=621016

Perhaps I missed something then. I'll look into this as soon
as I get back to the office.

Thanks for the quick response!

[Randolf C. Richardson]

Logged In: YES
user_id=621016

Ah yes, I see that I did miss this option -- I was expecting to
find something in the JDBC section, thanks for the pointer.

I'm unable to rebuild from JDBC successfully though due to
Foreign Keys that are defined. It would be helpful to have a
"DROP with JDBC" option in the same place as the Rebuild
options. Perhaps the menus will need to be restructured, and
a third "Compare structure in JDBC" option added as well, for
example (when right-clicking on an entity):

JDBC -> PostgreSQL -> Rebuild
JDBC -> PostgreSQL -> Drop
JDBC -> PostgreSQL -> Drop Cascade
JDBC -> PostgreSQL -> Compare structure

Obviously there would only need to be one "JDBC" item in the
menu, and the different database(s) they're using would show
up in place of PostgreSQL. Keeping the SQL server type in
immediately under JDBC (as exampled above) would also
allow for easy future inclusion of proprietary database features
as needed while also keeping the size of the right-click menu
down to a minimum.

In future, other options to backup/restore data from/to an entity
(e.g., when it's a table) could also be included. The data could
simply be backed up by writing SQL statements to the user's
choice of file to allow for easy editing later.

Thanks for everything you do with Druid, and for taking the
time to consider all my suggestions seriously.