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
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
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]
last insert ids from insert select
Dear All, I've just posted this to MySQLdb help forum but maybe there is a mysql way of getting what I want and you could, time permitting, help me. First my setup: * Python 2.5 * MySQL 5.0.27-standard-log (MYISAM tables) * MySQLdb.version_info (1, 2, 2, 'gamma', 1) I have the following statement which I currently cursor.execute: num_lines = cursor.execute(insert into AAA (name,desc) select name,desc from BBB) The primary key in AAA is an auto_increment `id`. The return is the number of lines which were inserted and cursor.lastrowid (LAST_INSERT_ID()) evaluates to the *first* `id` which was created in AAA. So, if after executing the statement above I get: num_lines = 3 cursor.lastrowid = 5 Is there any guarantee that the `id` for all of my new rows will be 5,6,7 ? Or could there have been a parallel insert happening which would make the `id` for my 3 rows be *non-consecutive*? Is there full-proof way of doing what I want, i.e. knowing *all* of the inserted ids and not just the first? Thanks a lot in advance. Zac -- Ezequiel Panepucci Phone: +41 (0)56 310 5267 Cell : +41 (0)79 598 6946 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-workbench
Dear list, Is mysql-workbench, the product derived from DBDesigner, a commercial product only? I have an alpha version on my computer but now it seems from the MySQL webpage http://dev.mysql.com/downloads/gui-tools/5.0.html that the workbench application is no longer available. On http://www.mysql.com/products/tools/ we find that query-browser and administrator have download links but workbench does not. The alpha version just dies every now and then and is really not reliable enough (Ubuntu 6.06). Comments? Thanks for the great database, Zac . --.. . --.- ..- .. . .-.. .--. .- -. . .--. ..- -.-. -.-. .. Ezequiel Panepucci, Ph.D. | Paul Scherrer Institut Phone: +41 (0)56 310 5267 | Swiss Light Source - WSLA/216 Fax : +41 (0)56 310 5292 | 5232 Villigen PSI, Switzerland Cell : +41 (0)79 598 6946 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-workbench
I hate answering myself... RTFWBF (Read The Fine WorkBench Forum) http://forums.mysql.com/read.php?113,142277,142309#msg-142309 Sorry for the wasted bandwidth, Zac Ezequiel Panepucci wrote: Dear list, Is mysql-workbench, the product derived from DBDesigner, a commercial product only? I have an alpha version on my computer but now it seems from the MySQL webpage http://dev.mysql.com/downloads/gui-tools/5.0.html that the workbench application is no longer available. On http://www.mysql.com/products/tools/ we find that query-browser and administrator have download links but workbench does not. The alpha version just dies every now and then and is really not reliable enough (Ubuntu 6.06). Comments? Thanks for the great database, Zac -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
