Pretty printer for SQL output?

mmanzo · March 6, 2009, 8:41am

Hey guys,

Now that I got the patch with the indexes for modified tables, I wonder if there’s any way that you can tell DB-VA to pretty-print the output scripts.

the problem is that… I want to keep the generated scripts in a subversion repository for easy access for the rest of the team, but obviously, the standard output of the scripts are kind of unreadable, is there any way to change that?

Thanks in advance,

LilianWong · March 6, 2009, 12:00pm

Hi Mmanzo,

Thanks for your post. Could you please tell me your expectation on output scripts? Any sample/reference for us to take a look?

Best regards,
Lilian Wong

mmanzo · March 9, 2009, 8:41am

Hi Lilian,

Thank you again for your prompt response. Look, I have no “specific” desire for the output, just something particularly human-readable.

Normally what I do with DB-VA output scripts is, I just open them and copy/paste the statements into: http://www.dpriver.com/pp/sqlformat.htm

That website has a pretty-printer but sometimes it doesn’t detect nested structures and doesn’t indent them.

LilianWong · March 9, 2009, 2:30pm

Hi Mmanzo,

Thanks for replying. Our idea is that scripts are for applications to run, they are not expected to be modified the so it’s not necessary to be human-readable. Anyway, we will consider this in the future. If there is any news on this issue, I’ll come back to you immediately.

Best regards,
Lilian Wong

mmanzo · March 10, 2009, 8:26am

Hi Lilian,

And I agree completely, automatically generated scripts are applications that their main goal is to be executed directly, but sometimes output scripts can be tuned, modified or, like I mentioned before, added to a repository where other developers can take a look at them and work on them. For instance, think about the issue where modified columns are not generating indices, you have to add them manually, among other things.

I’ll look forward to have such a useful feature in the future. Thanks a lot, as always

LilianWong · March 10, 2009, 9:29am

Hi Mmanzo,

Thanks for replying. I’ll pass your opinions to our development team for consideration.

Best regards,
Lilian Wong

LilianWong · April 15, 2009, 2:35pm

Hi Mmanzo,

I would like to notify you that the formatted SQL feature will be available in coming release (next week). If you like, I can arrange Release Candidate for you to test.

Best regards,
Lilian Wong

mmanzo · April 29, 2009, 8:37am

Those are amazing news, you guys fixed both the indexes of existent columns and added a pretty printer, that’s just amazing.

Great job guys.

LilianWong · April 29, 2009, 5:20pm

Hi Mmanzo,

Thanks!

Best regards,
Lilian Wong

mmanzo · May 6, 2009, 2:07am

Hi Lilian,

One thing that I noticed with the pretty printer and the new 5.0 version is that… it only works when you click on “Create Database”, it doesn’t work for me (at least under the OS X version that I downloaded) for “Update database”.

Is that a known bug? or it’s only “my” bug?

Thanks in advance,

LilianWong · May 7, 2009, 2:09pm

Hi Mmanzo,

Sorry for my late response. Would you mind to attach the generated ddl and tell me more details about how it does not work? Thanks in advance!

If you do not want the ddl to be accessed by public, please send it to lilian@visual-paradigm.com

Best regards,
Lilian Wong

mmanzo · May 11, 2009, 12:30am

Hi Lilian,

Yeah here’s the output of “Update Database” to the same diagram:


CREATE TABLE LFWEBCRM.WC_D_PKG (  PKG_KEY        number(10) NOT NULL,   PKG_ID         varchar2(50) NOT NULL,   PKG_CD         varchar2(24) NOT NULL,   PKG_LIC_CD     varchar2(4) NOT NULL,   PKG_SRC_CD     varchar2(4) NOT NULL,   PKG_NM         varchar2(100),   PKG_URL        varchar2(255),   PKG_CKSUM      varchar2(16),   RMD_CREAT_DT   date DEFAULT sysdate  NOT NULL,  RMD_CREAT_PRCS varchar2(50) NOT NULL,   RMD_IN_USE_IND char(1) DEFAULT 'Y'  NOT NULL,   RMD_PRODTN_DT  date,   RMD_MODF_DT    date DEFAULT sysdate  NOT NULL,   RMD_MODF_PRCS  varchar2(50) NOT NULL,   CONSTRAINT PKC_WC_D_PKG    PRIMARY KEY (PKG_KEY));
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_KEY IS 'Internal unique identifier for a package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_ID IS 'Unique identifier for a package, normally generated by an external system.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_CD IS 'A type qualifier for a specific package type.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_LIC_CD IS 'A type qualifier for a license type.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_SRC_CD IS 'A type qualifier for a system/component that defines the source of a package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_NM IS 'An optional human-readable name for a given package.                ';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_URL IS 'The universal resource locator for a given package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_CKSUM IS 'An optional MD5-checksum for a given package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_CREAT_DT IS 'The date on which the table row was created.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_CREAT_PRCS IS 'The identification of the the person or process that created the table row.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_IN_USE_IND IS 'An indicator to show whether or not the data in the row is still being used.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_PRODTN_DT IS 'The date on which the row was extracted for input to the operational application.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_MODF_DT IS 'The date on which the table row was changed.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_MODF_PRCS IS 'The identification of the person or process that changed the table row';
CREATE TABLE LFWEBCRM.WC_VT_PKG_ACCESS_CD (PKG_ACCESS_CD  varchar2(4) DEFAULT '0'  NOT NULL,  DESCR        varchar2(100), RMD_CREAT_DT   date DEFAULT sysdate  NOT NULL,  RMD_CREAT_PRCS varchar2(50) NOT NULL,   RMD_IN_USE_IND char(1) DEFAULT 'Y'  NOT NULL,   RMD_PRODTN_DT  date,   RMD_MODF_DT    date DEFAULT sysdate  NOT NULL,   RMD_MODF_PRCS  varchar2(50) NOT NULL,   CONSTRAINT PKC_WC_VT_PKG_ACCESS_CD     PRIMARY KEY (PKG_ACCESS_CD));

And here’s the output of “create database” of the same diagram:


CREATE TABLE LFWEBCRM.WC_D_PKG (
  PKG_KEY        number(10) NOT NULL, 
  PKG_ID         varchar2(50) NOT NULL, 
  PKG_CD         varchar2(24) NOT NULL, 
  PKG_LIC_CD     varchar2(4) NOT NULL, 
  PKG_SRC_CD     varchar2(4) NOT NULL, 
  PKG_NM         varchar2(100), 
  PKG_URL        varchar2(255), 
  PKG_CKSUM      varchar2(16), 
  RMD_CREAT_DT   date DEFAULT sysdate  NOT NULL, 
  RMD_CREAT_PRCS varchar2(50) NOT NULL, 
  RMD_IN_USE_IND char(1) DEFAULT 'Y'  NOT NULL, 
  RMD_PRODTN_DT  date, 
  RMD_MODF_DT    date DEFAULT sysdate  NOT NULL, 
  RMD_MODF_PRCS  varchar2(50) NOT NULL, 
  CONSTRAINT PKC_WC_D_PKG 
    PRIMARY KEY (PKG_KEY));
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_KEY IS 'Internal unique identifier for a package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_ID IS 'Unique identifier for a package, normally generated by an external system.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_CD IS 'A type qualifier for a specific package type.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_LIC_CD IS 'A type qualifier for a license type.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_SRC_CD IS 'A type qualifier for a system/component that defines the source of a package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_NM IS 'An optional human-readable name for a given package.                ';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_URL IS 'The universal resource locator for a given package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.PKG_CKSUM IS 'An optional MD5-checksum for a given package.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_CREAT_DT IS 'The date on which the table row was created.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_CREAT_PRCS IS 'The identification of the the person or process that created the table row.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_IN_USE_IND IS 'An indicator to show whether or not the data in the row is still being used.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_PRODTN_DT IS 'The date on which the row was extracted for input to the operational application.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_MODF_DT IS 'The date on which the table row was changed.';
COMMENT ON COLUMN LFWEBCRM.WC_D_PKG.RMD_MODF_PRCS IS 'The identification of the person or process that changed the table row';
CREATE TABLE LFWEBCRM.WC_VT_PKG_ACCESS_CD (
  PKG_ACCESS_CD  varchar2(4) DEFAULT '0'  NOT NULL, 
  DESCR          varchar2(100), 
  RMD_CREAT_DT   date DEFAULT sysdate  NOT NULL, 
  RMD_CREAT_PRCS varchar2(50) NOT NULL, 
  RMD_IN_USE_IND char(1) DEFAULT 'Y'  NOT NULL, 
  RMD_PRODTN_DT  date, 
  RMD_MODF_DT    date DEFAULT sysdate  NOT NULL, 
  RMD_MODF_PRCS  varchar2(50) NOT NULL, 
  CONSTRAINT PKC_WC_VT_PKG_ACCESS_CD 
    PRIMARY KEY (PKG_ACCESS_CD));

As you can see, only the second is spitting the output pretty printed.

Cheers,

LilianWong · May 12, 2009, 3:17pm

Hi Mmanzo,

Sorry for my late response. I’m sorry that I cannot repeat your problem. Would you mind to send me a sample project file for investigation? Thanks in advance!

If you do not want the project file being accessed by public, please send to lilian@visual-paradigm.com

Best regards,
Lilian Wong

mmanzo · May 13, 2009, 12:17am

Well…

The problem with sending the project is that “Update Database” won’t work because you don’t have the entire database to generate the diff for the new project.

But I’ve been able to reproduce to all of my projects that are “updates”.

Just try it with any test database (I’m using Oracle), create some new tables get the “Update Database” part working and you’ll see.

Does it work for you?

LilianWong · May 13, 2009, 6:33pm

Hi Mmanzo,

Thanks for replying. Unfortunately we still cannot repeat the problem. Could you please download the latest build from the following link to see if the problem still exists?

http://files2.visual-paradigm.com/200905/Patch/20090508h/VP_Suite_MacOSX_4_0_20090508h.dmg

Best regards,
Lilian Wong

mmanzo · May 31, 2009, 12:39pm

Hi guys,

Sorry for the late response, with the latest version that you released, formatting the output SQL using ‘update database’ works perfectly fine.

Great job,

thanks!

LilianWong · June 1, 2009, 2:31pm

Hi Mmanzo,

You are welcome. If you have any suggestion or inquiry about our product, please feel free to contact us.

Best regards,
Lilian Wong

Pretty printer for SQL output?

Product

Support

Learn

About