#959: Index Icons: Have icons visually identify UNIQUE, CLUSTERED indexes.

NielsGron reported 2008-01-19T04:57:25Z · last modified 2012-04-27T22:05:23Z

Index Icons: Have icons visually identify UNIQUE, CLUSTERED indexes.

Dev	ivangron
QA	NielsGron

Priority	Low
Complexity	Unknown

Component	App - Schema Browser
Version	11.0

Index Icons: Have icons visually identify UNIQUE, CLUSTERED indexes.

All Comments (17) Change History

NielsGron 2012-01-21T00:37:13Z

I added the files /elegant/tree/connection/*

index-nonuniuqe-clustered.png
index-nonuniuqe-nonclustered.png
index-uniuqe-clustered.png
index-uniuqe-nonclustered.png

ivangron 2012-01-26T20:45:17Z · (edited)

Apache -- complete

DB2 -- complete

DB2i -- ** filed as separate Issue ID 6353 **

DB2z -- complete

Informix -- complete

MySQL -- complete

nCluster -- starting...

Netezza -- ** filed as separate Issue ID 6354 **

Oracle -- complete

Paracel -- ** filed as separate Issue ID 6355 **

PostgreSQL-- complete

SQL Server -- complete

Sybase Anywhere -- complete

Sybase ASE -- complete

Sybase IQ -- complete

Teradata -- complete

* although the idea "Unique" follows a very common definition among databases the idea "Clustered" does not. I tried to associate the clustered icon to some form of index type accordingly by each database.

ivangron 2012-01-23T22:23:31Z · (edited)

DB2

Unique rule.

D = Permits duplicates -- will map to Non-Unique

U = Unique -- will map to Unique

P = Implements primary key -- will map to Unique

Type of index.

CLUS = Clustering index (controls the physical placement of newly inserted rows) -- will map to Clustered

BLOK = Block index -- will map to Non-Clustered

DIM = Dimension block index -- will map to Non-Clustered

REG = Regular index -- will map to Non-Clustered

XPTH = XML path index -- will map to Non-Clustered

XRGN = XML region index -- will map to Non-Clustered

XVIL = Index over XML column (logical) -- will map to Non-Clustered

XVIP = Index over XML column (physical) -- will map to Non-Clustered

DB2 luw also has system indexes, so if the index is a system index we display the system index icon, else we display the appropriate index icon according to the rules above.

ivangron 2012-01-24T07:48:19Z

Apache Derby: derby ony allows you to create unique or not unique indexes. The extraction is limited, but the Descriptor of the index object starts with UNIQUE for unique indexes.

if the descriptor starts with unique, will display the Unique-NonClustered icon else the NonUnique-NonClustered. Unless it is a system index, in which case it displays the system index icon.

ivangron 2012-01-24T17:27:06Z · (edited)

DB2 Z/OS

UNIQUERULE

D No (duplicates are allowed) -- mapped to Non-Unique

C Yes, and it is used to enforce the uniqueness of a UNIQUE constraint or hash key columns. -- mapped to Unique

U Yes -- mapped to Unique

P Yes, and it is a primary index (As in prior releases of DB2®, a value of P is used for primary keys that are used to enforce a referential constraint.) -- mapped to Unique

N Yes, and it is defined with UNIQUE WHERE NOT NULL -- mapped to Unique

R Yes, and it is an index used to enforce the uniqueness of a non-primary parent key -- mapped to Unique

G Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT -- mapped to Unique

X Yes, and it is an index used to enforce the uniqueness of values in a column that is used to identify or find XML values associated with a specific row. -- mapped to Unique

CLUSTERING

N No -- mapped to Non-Clustered

Y Yes -- mapped to Clustered

ivangron 2012-01-24T19:53:18Z · (edited)

Informix

Unique Rule

U = Unique -- mapped to Unique

D = Duplicates allowed -- mapped to Non-unique

G = Nonbitmap generalized-key index (XPS) -- mapped to Unique

g = Bitmap generalized-key index -- mapped to Unique

u = unique, bitmap -- mapped to Unique

d = nonunique, bitmap -- mapped to Non-Unique

other values -- mapped to Non-Unique

Cluster Rule

C = Clustered -- mapped to Clustered

other values -- mapped to Non-Clustered

ivangron 2012-01-24T23:36:33Z · (edited)

MySQL

Non_unique

0 if the index cannot contain duplicates -- mapped to unique

1 if it can -- mapped to non-unique

Index_type

The index method used (BTREE, FULLTEXT, HASH, RTREE) -- all mapped to non-clustered.

ivangron 2012-01-25T00:56:49Z · (edited)

nCluster

nCluster does not support clustered indexes.

create unique index is not supported but they can be created via constraints causing the system to create them. the only way to extract this is to parse the indexdef column of the nc_****_indexes catalog table. if the indexdef startswith "CREATE UNIQUE" making the icon to unique else to non-unquie.

^thought^ : scripting indexes will not script the UNIQUE key word as this is not supported even if the index is unique. this could cause confusion for the user.

ivangron 2012-01-25T02:05:04Z

Oracle

Cluster Rule ^note^ unlike other database this cluster icon will be representing an index on a cluster

CLUSTER -- mapped to clustered

NORMAL -- mapped to non-clustered

NORMAL/REV -- mapped to non-clustered

BITMAP -- mapped to non-clustered

FUNCTION-BASED NORMAL -- mapped to non-clustered

FUNCTION-BASED NORMAL/REV -- mapped to non-clustered

FUNCTION-BASED BITMAP -- mapped to non-clustered

IOT - TOP -- mapped to non-clustered

DOMAIN -- mapped to non-clustered

UNIQUE Rule

UNIQUE -- mapped to unique

NONUNIQUE -- mapped to non-unique

ivangron 2012-01-25T18:29:51Z · (edited)

Postgres 7.3 and above

pg_index.indisunique bool If true, this is a unique index -- mapped to unique

pg_index.indisclustered bool If true, the table was last clustered on this index -- mappend to clustered

ivangron 2012-01-25T19:24:06Z · (edited)

MS SQL SERVER

INDEXPROPERTY (tableID, indexNAME, 'IsUnique')

0 -- mapped to unique

1 -- mapped to non-unique

INDEXPROPERTY (tableID, indexNAME, 'IsClustered')

0 -- mapped to clustered

1 -- mapped to non-clustered

ivangron 2012-01-26T16:32:29Z · (edited)

Sybase Anywhere

sys_indexes [unique] if value is Y -- mapped to unique

N U I M or null mapped to non-unique

Clustered

version 9 and below - uses sysattribute table if the correct record is found index is clustered. record would need to be attribute_id = 2, object_type 'T' where attribute_value = index_id and object_id = table_id -- mapped to clustered if not null else non-clustered

version 10 and above - uses systab clustered_index_id exist for the index on the table -- mapped to clustered else non-clustered

ivangron 2012-01-26T17:02:41Z · (edited)

Sybase ASE

using sysindexes.status we extract the unique and clustered from the bit value

Decimal [2] Hex 0x2 = Unique index

Decimal [16] Hex 0x10 = Clustered index

ivangron 2012-01-26T18:37:12Z · (edited)

Sybase IQ

Unique: sysindex."unique" if "Y" -- unique else non-unique

Clustered: sybase iq doesn't have clustered type indexes. types: CMP (Compare), HG (High_Group), HNG (High_Non_Group), LF (Low_Fast), WD (Word), DATE (date), TIME (time), DTTM (Datetime) -- all non-clustered

ivangron 2012-01-26T19:21:43Z · (edited)

Teradata

system indexes have there own icon, user index icons defined with following info

UniqueFlag "Y" mapped to unique "N" to non-unique

IndexTypes are all mapped to non-clustered

P (Nonpartitioned Primary)

Q (Partitioned Primary)

S (Secondary)

J (join index)

N (hash index)

K (primary key)

U (unique constraint)

V (value ordered secondary)

H (hash ordered ALL covering secondary)

O (valued ordered ALL covering secondary)

I (ordering column of a composite secondary index)

M (Multi-Column Statistics)

D (Derived column partition statistics)

1 (field1 column of a join or hash index)

2 (field2 column of a join or hash index)

ivangron 2012-01-26T20:47:47Z

code completed and tested. if we want to change up any of the definitions of "Clustered" by index type for any specific database please let me know.

tariqrahiman 2012-04-27T22:05:23Z

This is implemented and verified in 11.0.0-rc-81

Search Tips

Aqua Data Studio / nhilam

Index Icons: Have icons visually identify UNIQUE, CLUSTERED indexes.

Issue #959

Completion

1 issue link

Issue #9631

Search Tips

Aqua Data Studio / nhilam

Title

Index Icons: Have icons visually identify UNIQUE, CLUSTERED indexes.

Issue #959

Completion

1 issue link

Issue #9631