Context CreateDatabaseScript() major changes 5.7 <=> 6.x

ponette75 · Post by **ponette75** » Wed 26 Jan 2011 16:15

Hi there,

I have finally moved to version 6.0 which was fixing the reverse bug Number(10) => Int64.

The new fonctionnality Update from/to Database/Model has major impacts
on Context.CreateDatabaseScript() function.

here are two tables creation difference :

Code: Select all

--
-- Devart v5.7
--
-- context.CreateDatabaseScript();
--
-- Table AUTOSEM.OPERATION_TYPE
--
CREATE TABLE AUTOSEM.OPERATION_TYPE ( 
  ID NUMBER(18) NOT NULL,
  NAME NVARCHAR2(256) NULL,
  CODE NVARCHAR2(40) NULL,
  CREATE_LOG_ID NUMBER(18) NULL,
  UPDATE_LOG_ID NUMBER(18) NULL,
  IS_DELETED NUMBER(1) NULL
);

ALTER TABLE AUTOSEM.OPERATION_TYPE
  ADD CONSTRAINT PK_OPERATION_TYPE PRIMARY KEY (ID);
  
----------------------------------------------------------------
----------------------------------------------------------------

--
-- Devart v6.x
--
-- context.CreateDatabaseScript();
--
-- Table AUTOSEM.OPERATION_TYPE
--
CREATE TABLE AUTOSEM.OPERATION_TYPE ( 
  ID NUMBER(18) NOT NULL,
  NAME NVARCHAR2(128) NULL,
  CODE NVARCHAR2(20) NULL,
  CREATE_LOG_ID NUMBER(18) NULL,
  UPDATE_LOG_ID NUMBER(18) NULL,
  IS_DELETED NUMBER(1) NULL,
  PRIMARY KEY (ID)
);

CREATE SEQUENCE AUTOSEM.OPERATION_TYPE_SEQ;

CREATE OR REPLACE TRIGGER AUTOSEM.OPERATION_TYPE_INS_TRG
  BEFORE INSERT ON AUTOSEM.OPERATION_TYPE
  FOR EACH ROW
BEGIN
  SELECT OPERATION_TYPE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
;

First quick remark note this last ";" is a bug in DDL code generation.

the major (and some annoying) differences are :
- PK constraint are not named anymore.
As a consequence it is not possible anymore to move the PK to a tablespace different from the table tablespace

Code: Select all

ALTER INDEX PK_OPERATION_TYPE REBUILD TABLESPACE AUTOSEM_IDX

- Sequence and triggers on table using sequence are automatically generated, I used to generated this with T4.

Code: Select all

CREATE SEQUENCE AUTOSEM.SEQ_OPERATION_TYPE_ID;

DROP TRIGGER AUTOSEM.TS_OPERATION_TYPE_SEQ_OPERAT_0;

CREATE OR REPLACE TRIGGER AUTOSEM.TS_OPERATION_TYPE_SEQ_OPERAT_0 BEFORE INSERT
ON AUTOSEM.OPERATION_TYPE FOR EACH ROW
BEGIN
    SELECT SEQ_OPERATION_TYPE_ID.nextval INTO :new.Id FROM DUAL;
END;
/

This is a cool feature except the fact you loose control on the sequence naming.
Further note/remark : The PK field / StoreGeneratedPattern identity is not "protected" with an Update trigger.

Code: Select all

DROP TRIGGER BOTECH.TSU_OPERATION_TYPE_SEQ_OPERAT_0;

CREATE OR REPLACE TRIGGER BOTECH.TSU_OPERATION_TYPE_SEQ_OPERAT_0 AFTER UPDATE OF Id
ON BOTECH.OPERATION_TYPE FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20010,'Cannot update column Id in table Operation_Type as it uses sequence.');
END;
/

the sequence generation is OK for me but I think the PK generation naming loss is much more a problem for me.

Is this an omission/bug or will it stay like this in the future ?

best regards
Francis de Fouchier

AndreyR · Post by **AndreyR** » Thu 27 Jan 2011 15:03

Thank you for the remarks, we will discuss the possibilities of changing the behaviour.
I will let you know about the results.

AndreyR · Post by **AndreyR** » Fri 28 Jan 2011 11:50

We plan to implement the possibility of configuring the CreateDatabase() behaviour:

Create named/unnamed PK;
Create sequences and/or triggers for Identity columns;
Add prefixes (suffixes) to the names of Primary Keys, Foreign Keys, sequences, and triggers etc.

This functionality will not be available in the nearest builds, but it is planned .
We appreciate your feedback, please leave some comments and/or suggestions on this list.

ponette75 · Post by **ponette75** » Mon 31 Jan 2011 14:21

AndreyR wrote: We appreciate your feedback, please leave some comments and/or suggestions on this list.

1) no real need but may be generate Update trigger to avoid programmers from updating PK Fields (as option when calling CreateDatabaseScript()).
This pattern was given to me by Toad Data Modeler way of generating DDL script for oracle.

2) may be think about a way of handling tablespaces for tables & indexes

this is what I did "before"

Code: Select all

--
-- dotconnect for oracle CreateDatabaseScript
--

CREATE TABLE AUTOSEM.OPERATION_TYPE ( 
  ID NUMBER(18) NOT NULL, 
  NAME NVARCHAR2(256) NULL, 
  CODE NVARCHAR2(40) NULL, 
  CREATE_LOG_ID NUMBER(18) NULL, 
  UPDATE_LOG_ID NUMBER(18) NULL, 
  IS_DELETED NUMBER(1) NULL 
); 

ALTER TABLE AUTOSEM.OPERATION_TYPE 
  ADD CONSTRAINT PK_OPERATION_TYPE PRIMARY KEY (ID); 

--
-- T4 helper to move table to data tablespace & PK to index tablespace
--

ALTER TABLE AUTOSEM.OPERATION_TYPE MOVE TABLESPACE AUTOSEM_DATA;

ALTER INDEX PK_OPERATION_TYPE REBUILD TABLESPACE AUTOSEM_IDX ;

cdt
FdF

ponette75 · Post by **ponette75** » Mon 31 Jan 2011 14:27

AndreyR wrote: We appreciate your feedback, please leave some comments and/or suggestions on this list.

also, is it possible to add an option "do not regenerate layout .edml.view"
when reversing a DB to Model.

I had this problem in 5.7 but did not test on 6.x since update Model from DB appeared (I guess the layout is kept since the wizard process keeps all entities)

cdt
FdF

AndreyR · Post by **AndreyR** » Tue 01 Feb 2011 13:18

Thank you for the suggestions, we will take them into account in the process of the CreateDatabaseScript() behaviour improvement.
If you have more improvement suggestions, please share them at our forum.
As for Entity Developer, the latest build does not perform unnecessary Layout of entities.

AndreyR · Post by **AndreyR** » Tue 15 Mar 2011 12:04

We have created a new UserVoice about Entity Framework support.
Your CreateDatabase improvement suggestion was added there, you can vote for it and for other suggestions as well.