using column comment to store metadata
Hi, I'm thinking of using the COMMENT of columns when creating tables to store some metadata about the column, for instance: valid_range of numbers, validation regexp, etc. I did some tests with including this information as a JSON (JavaScript Object Notation) string which can then be easily decoded into an actual data structure for the scripting language (Python in my case) being used. The setup seems to work well. One shortcoming is that the maximum size of a COMMENT is 256 characters. Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? I am currently using mysql 5.0.27-standard-log. Thanks in advance for your comments, Cheers, Ezequiel -- Ezequiel Panepucci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
Hi, I'm thinking of using the COMMENT of columns when creating tables to store some metadata about the column, for instance: valid_range of numbers, validation regexp, etc. I did some tests with including this information as a JSON (JavaScript Object Notation) string which can then be easily decoded into an actual data structure for the scripting language (Python in my case) being used. The setup seems to work well. One shortcoming is that the maximum size of a COMMENT is 256 characters. Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. In short, I would say that the use of COMMENT isn't what it actually does. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
On 9/17/07, Martijn Tonies [EMAIL PROTECTED] wrote: Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. In short, I would say that the use of COMMENT isn't what it actually does. No arguing here. Thanks for you comment. Ezequiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
Do you have any comments about this approach? What could possibly go very bad about relying on this usage of the COMMENT? Yes, in different storage engines, different stuff is returned. For example, in InnoDB, what you get returned from SHOW FULL COLUMNS is not what you stored in the Comment part. I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. Try it on columns with a foreign key constraint. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
I currently use MyISAM, but I just tried a simple
create(InnoDB)/show full columns from/ and
it actually does return the JSON strings I put in.
Try it on columns with a foreign key constraint.
I did and it still works (maybe the behaviour is version
dependent?).
Here is what I did:
CREATE TABLE parent (
id INT NOT NULL comment '{com: the primary key of the parent.}',
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT comment '{com: the primary key}',
parent_id INT comment '{com: the foreing key}',
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
mysql show full columns from parent;
+---+-+---+--+-+-+---+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
Privileges | Comment
|
+---+-+---+--+-+-+---+-+---+
| id| int(11) | NULL | NO | PRI | | |
select,insert,update,references | {com: the primary key of the
parent.} |
+---+-+---+--+-+-+---+-+---+
1 row in set (0.00 sec)
mysql show full columns from child;
+---+-+---+--+-+-+---+-++
| Field | Type| Collation | Null | Key | Default | Extra |
Privileges | Comment|
+---+-+---+--+-+-+---+-++
| id| int(11) | NULL | YES | | NULL| |
select,insert,update,references | {com: the primary key} |
| parent_id | int(11) | NULL | YES | MUL | NULL| |
select,insert,update,references | {com: the foreing key} |
+---+-+---+--+-+-+---+-++
2 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using column comment to store metadata
I currently use MyISAM, but I just tried a simple create(InnoDB)/show full columns from/ and it actually does return the JSON strings I put in. Try it on columns with a foreign key constraint. I did and it still works (maybe the behaviour is version dependent?). Hmm, could be. I remember all sorts of information being returned in the COMMENTS section, all that was different from my actual comment :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment
Hi, The thing is you want to add comments to column, which is presently null, as per SHOW FULL COLUMNS FROM table; Then add COMMENT 'your comment for the column name' , for the columns you want comments. For Instance, mysql create table test (topic varchar(10)default null COMMENT 'topic for the forum', title varchar(10) default null COMMENT 'title for the forum topic'); mysql show full columns from test\G *** 1. row *** Field: topic Type: varchar(10) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: topic for the forum *** 2. row *** Field: title Type: varchar(10) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: title for the forum topic 2 rows in set (0.01 sec) Hope Useful ! and Welcome for your interest. Thanks ViSolve DB Team. - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, February 09, 2007 6:54 AM Subject: Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment Hello, thanks again to ViSolve DB Team. so, it turns out that for MySQL4.1, il will have to built a sql file, built with script (shell or perl). [ note that so far I use MySQL 4.1 on production server, and I only test features on MySQL 5 on an other machine linux Debian]. at last, my LAST QUESTION concerns column Comment In MySQL 5, I can do : mysql select column_name,COLUMN_COMMENT from information_schema.columns where table_schema=a_base and table_name='a_tab'; in MySQL 4.1 , the equivalent would be : mysql select distinct Column_name from columns_priv where Db='a_base' and Table_name='a_tab'; AND THERE IS NO COMMENT. but, from the page : http://dev.mysql.com/doc/refman/4.1/en/charset-show.html it seems that one could use Comment mysql SHOW FULL COLUMNS FROM a_tab; displays : Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | I cannot find how to set these Comment ? An idea ? thanks. -Gilles- On Thu, 8 Feb 2007, ViSolve DB Team wrote: Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment
Hello, thanks again to ViSolve DB Team. so, it turns out that for MySQL4.1, il will have to built a sql file, built with script (shell or perl). [ note that so far I use MySQL 4.1 on production server, and I only test features on MySQL 5 on an other machine linux Debian]. at last, my LAST QUESTION concerns column Comment In MySQL 5, I can do : mysql select column_name,COLUMN_COMMENT from information_schema.columns where table_schema=a_base and table_name='a_tab'; in MySQL 4.1 , the equivalent would be : mysql select distinct Column_name from columns_priv where Db='a_base' and Table_name='a_tab'; AND THERE IS NO COMMENT. but, from the page : http://dev.mysql.com/doc/refman/4.1/en/charset-show.html it seems that one could use Comment mysql SHOW FULL COLUMNS FROM a_tab; displays : Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | I cannot find how to set these Comment ? An idea ? thanks. -Gilles- On Thu, 8 Feb 2007, ViSolve DB Team wrote: Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column comment
Hi, I am aware of the fact that with 4.1.0, you can specify comments on a column by column by basis. I was wondering if this functionality could somehow be simulated in 4.0.x? It is high priority for me, but I cannot deploy the alpha stage 4.1.0 in a production environment. Basically, what I would like to do is associate a human readable label with each column (in the comment field) so that I can automatically generate web-based forms for any given table. The script would look at the column's metadata, display the appropriate HTML element, and display the corresponding label read from the column field. Is there any way this can be accomplished with MySQL 4.0.12? Thanks, Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Column comment / metadata tables
Basically, what I would like to do is associate a human readable label with each column (in the comment field) so that I can automatically generate web-based forms for any given table. The script would look at the column's metadata, display the appropriate HTML element, and display the corresponding label read from the column field. MySQL could do something that would make MySQL more complete and more able to satisfy these Codd rules: 0. A relational database must be able to manage databases entirely through its relational capabilities. 1. The Information Rule. All information in a relational database (including table and column names) is represented explicitly as values in tables. The metadata would be loaded into tables (real, virtual, heap or temporary?). It would be refreshed as necessary by mysqld, when processing ALTER, DROP, CREATE, etc. Normal SELECTs would be used to efficiently view those metadata tables. Why should it be necessary for tools such as MySQLfront to use?: SHOW TABLE STATUS, SHOW FIELDS FROM table, SHOW KEYS FROM table. Thanks, Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
