SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[10.0.187 Pro] - $COLUMNS(keys)$ issue in MySQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[10.0.187 Pro] - $COLUMNS(keys)$ issue in MySQL
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [10.0.187 Pro] - $COLUMNS(keys)$ issue in MySQL Reply with quote
It seems that the $COLUMNS(keys)$ macro does not respect the table having unique columns, neither as part of unique key constraint nor as simply defined as unique. The macro inserts the primary key columns into the editor but disregards the unique ones.

EDIT: As a feature request, it would be also rather useful if the macro could distinguish primary and non-primary keys.
Thu Dec 06, 2018 11:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Please take a look at "Columns (MSSQL) + Keys" query in the options DB Options -> DB Queries -> Columns (MSSQL) + Keys . This is designed for the intellisense popups where types of keys aren't that important, the key flag is used in the intellisense popups to show key columns with an icon, basically indicate which columns belong to a Primary, or Foreign, or Unique key (in this specific order), or if they are indexed or not. The same single attribute is used for all of them. It's kind of an aggregated value. The $COLUMNS(keys)$ Macro uses cached results of the same query. As a result, it cannot handle unique keys correctly if there are primary or foreign keys defined for the same columns. I imagine that in order to better support unique queues the internal column attributes need to change, each column would need to know about all of the keys it belongs to, which cannot be handled by a single aggregated key type attribute.
Fri Dec 07, 2018 9:45 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I understand that the internal handler might be at some difficulties in those special cases, however, the table in question does not qualify. It has a single column primary key and three separate single column unique keys. There's no overlap in key columns (see included CREATE statement).

Code:

CREATE TABLE `edit_mode` (
  `tid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Technical identifier.',
  `tag` varchar(100) NOT NULL,
  `megnevezes` varchar(100) NOT NULL,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`tid`),
  UNIQUE KEY `uk_edit_mode__tag` (`tag`),
  UNIQUE KEY `uk_edit_mode__value` (`val`),
  UNIQUE KEY `uk_edit_mode__megnevezes` (`megnevezes`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8


The snippet
Code:

keys: $COLUMNS(keys)$
updatable: $COLUMNS(updatable)$
nokeys: $COLUMNS(nokeys)$


returns the following:
Code:

keys: `tid`
updatable: `tag`, `megnevezes`, `val`
nokeys: `tag`, `megnevezes`, `val`



Also, the RDBMS I noticed this issue is MySQL, but I guess that's irrelevant. I've run the "Columns (MySQL) + Keys" query for the said table, it returned


Sorry for posting it as an image but when I try to copy from the Result Pane a rather strange thing happens if I select the whole table. Check this short video >>>here<<<.
Fri Dec 07, 2018 11:21 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.