Custom comments informix

[npetzall]

Informix doesn't have comments but this could be simulated?

@begooden-it

[npetzall]

Yes, there is a selectTableCommentsSql also one for columns that can be added to the databaseType.

It can be injected with schema, and it expects two columns in return.

table_name, comments

So something in the line of select table_name, comments from something join something where something = :schema

[begooden-it]

Sorry for the latency.

What do you need exactly? or can I do this myself in some "publicly" (i.e not in source-code) accessible parameters area ? OR is this something you will need to define within non public aread (source-code)? I just put a RFE ( request for enhancement) to IBM to implement this, but it may take time in the meantime to have it?

How could we proceed in the meantime?

[npetzall]

It's possible to do without any change to the code.

https://schemaspy.readthedocs.io/en/latest/configuration.html#databasetype

https://schemaspy.readthedocs.io/en/latest/configuration.html#other-properties

If it gets implemented we would like to add it to the default.

You should extend the normal informix databaseType

[npetzall]

If you supply the schema for comments, I'll help create the queries and databaseType file.

[begooden-it]

Hi,

this is my custom schema, the RFE will probably take time to implement

--drop table if exists qx_table_comments ; create table qx_table_comments ( tabid INTEGER, comments lvarchar(4096) ) ; create index fk_systables_qx_table_comm on qx_table_comments(tabid) ;

--drop table if exists qx_column_comments ; create table qx_column_comments ( tabid INTEGER, colno INTEGER, comments lvarchar(4096) ) ; create index fk_syscolumns_qx_column_comm on qx_column_comments(tabid,colno) ;

Here is how I insert data for the first time: insert into qx_table_comments SELECT t.tabid,"comments on " || t.tabname from systables t where t.tabid > 99 and t.tabid not in ( SELECT tabid from qx_table_comments );

insert into qx_column_comments SELECT c.tabid,c.colno,"comments on " || c.colname from syscolumns c where c.tabid > 99 and (c.tabid not in ( SELECT tabid from qx_column_comments ) and c.colno not in ( SELECT colno from qx_column_comments )) ;

and here is how I fetch the data: For the table comments: select t.tabname,c.comments from systables t,qx_table_comments c where t.tabid = c.tabid ;

for the column comments: select t.tabname,c.colname,qx_column_comments.comments from systables t,syscolumns c,qx_column_comments where t.tabid = qx_column_comments.tabid and c.tabid = t.tabid and c.colno = qx_column_comments.colno

[npetzall]

So file to same dir as schemaspy-jar named informix_qx_comments.properties with the following contents:

#
# see http://schemaspy.org/dbtypes.html
# for configuration / customization details
#

# Extended informix database type to support comments as described in https://github.com/schemaspy/schemaspy/issues/425
extends=informix

selectTableCommentsSql=select t.tabname as table_name, c.comments as comments from systables t,qx_table_comments c where t.tabid = c.tabid and t.owner = :schema

selectColumnCommentsSql=select t.tabname as table_name, c.colname as column_name, qx_column_comments.comments as comments from systables t,syscolumns c,qx_column_comments where t.tabid = qx_column_comments.tabid and c.tabid = t.tabid and c.colno = qx_column_comments.colno and t.owner = :schema 

Change -t informix in command-line to -t informix_qx_comments And any custom comments added as per above schemas will end up in the database model and such in the html report.

[npetzall]

All changes for this can be viewed at https://github.com/schemaspy/schemaspy/compare/master...npetzall:425_informix_custom_comments

It's only a test and a sql script. So no code change needed. Same thing can be done for any other database that hasn't got comments support.

[begooden-it]

Great !

I’ll apply ASAP

Thank you

Eric Vercelletto Data Management Architect Board of Directors, International Informix Users group

[image: ibm-champion-rgb-130px] Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com Vous parlez français et vous aimez Informix ? connectez-vous sur http://levillageinformix.blogspot.com IBM developperWorks : https://www.ibm.com/developerworks https://www.ibm.com/developerworks/community/profiles/html/profileView.do?key=ff73ff2a-989b-444e-bd23-8daccf091933&lang=en Looking for the best Informix technical profiles on earth ? Wanting your Informix profile to be more visible on earth ? check http://www.informix-swat.com

[image: cid:image001.jpg@01CDC3E9.1425CBB0]

[image: cid:image002.jpg@01CDC3E9.1425CBB0]

[image: cid:image003.jpg@01CDC3E9.1425CBB0]

De : Nils Petzäll [mailto:notifications@github.com] Envoyé : lundi 24 septembre 2018 23:17 À : schemaspy/schemaspy Cc : begooden-it; Mention Objet : Re: [schemaspy/schemaspy] Custom comments informix (#425)

So file to same dir as schemaspy-jar named informix_qx_comments.properties with the following contents:

#

see http://schemaspy.org/dbtypes.html

for configuration / customization details

#

Extended informix database type to support comments as described in

https://github.com/schemaspy/schemaspy/issues/425

extends=informix

selectTableCommentsSql=select t.tabname as table_name, c.comments as comments from systables t,qx_table_comments c where t.tabid = c.tabid and t.owner = :schema

selectColumnCommentsSql=select t.tabname as table_name, c.colname as column_name, qx_column_comments.comments as comments from systables t,syscolumns c,qx_column_comments where t.tabid = qx_column_comments.tabid and c.tabid = t.tabid and c.colno = qx_column_comments.colno and t.owner = :schema

Now any custom comments added as per above schemas will end up in the database model and such in the html report.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/schemaspy/schemaspy/issues/425#issuecomment-424127976, or mute the thread https://github.com/notifications/unsubscribe-auth/AR3-TNqCUKn3D5i3n97KF0mb9fa9TGgfks5ueUuxgaJpZM4Wlzbk .

[begooden-it]

Sorry to come back, but:

which files do I need for the test? I tried adding the informix_qx_comments.properties file and ran the following command at prompt: java -jar "C:\Tools\SchemaSpy\schemaSpy.jar" -dp "java -jar "C:\Tools\SchemaSpy\schemaSpy.jar" -dp "C:\Program Files\ibm\csdk12.10.FC9\jdbc\lib\ifxjdbc.jar" -t informix_qx_comments.properties -db %1 -host 192.168.1.20:29140 -port 29140 -u informix -p informix -server production_tcp -o %2 -connprops="CLIENT_LOCALE\=en_US.utf8;DB_LOCALE\=en_US.57372" -charset UTF-8 -s informix\ifxjdbc.jar" -t informix -db %1 -host 192.168.1.20:29140 -port 29140 -u informix -p informix -server production_tcp -o %2 -connprops="CLIENT_LOCALE\=en_US.utf8;DB_LOCALE\=en_US.57372" -charset UTF-8 -s informix

But no comments appeared ( and no error). I must be missing something obious for you and not for me :-)

Thanks

[npetzall]

@begooden-it I assume some kind of copy paste issue, since that command line is really really strange.

I'll give it ago here

java -jar "C:\Tools\SchemaSpy\schemaSpy.jar" -dp "C:\Program Files\ibm\csdk12.10.FC9\jdbc\lib\ifxjdbc.jar" -t informix_qx_comments -db %1 -host 192.168.1.20 -port 29140 -u informix -p informix -server production_tcp -o %2 -connprops="CLIENT_LOCALE\\=en_US.utf8;DB_LOCALE\\=en_US.57372" -s informix

informix_qx_comments.properties should be located in C:\Tools\SchemaSpy

That should be it, I'm a bit unsure on the -connprops since they are weird. Something to change in v7 Only problem I could think of is if the qx_table_comments and qx_column_comments are in a different schema.

[begooden-it]

Hi,

sorry for the delay I have been caught by too many tasks...

I finally tested it on 6.0.0 rc2 and it works great as described. I now just have to write all the comments, but hey, what did you expect ;-) ?

REgarding the schema thing, what I can say is that the 'schema' functionality is not that much used at least by 'old' informix customers. Anyway, in informix, a table is always created in a database, not in a schema. Until those tables are not implemented by HCL/IBM as system tables, this can work like this.

I have been looking at the progression of my RFE at IBM, which is slowly collecting votes, but collecting. Implementation might be Q3 next year if we push more. I can also talk with Informix engineering and tell them I have developed the functionality and it works.

In the meantime, maybe we we could adopt more 'informix standard' names for the tables, such as systabcomments and syscolcomments ? So you could insert the functionality in a future release.

What do you think?

Have a great week Eric

[begooden-it]

Hi Nils,

and BTW Happy New year

Just wondering if the comments for Informix have been integrated in some newer version and if yes, what should be the syntax ?

I just posted another issue about Informix Check constraints not appearing. https://github.com/schemaspy/schemaspy/issues/637

Thank you Eric

[npetzall]

Hi, Thanks and double to you 2018->2019 and 2019->2020.

It has not been added to schemaspy, so you still need to use a custom databaseType (properties-file). But if you have some good news about the RFE, this can be added to schemaspy.