String sql = "CREATE COLLATION CASE_INSENSITIVE FOR INFORMATION_SCHEMA.SQL_TEXT FROM EXTERNAL('CaseInsensitive.coll1');";
this.execSql(database, sql);
sql = "CREATE TABLE \"t_user\" ( " +
" \"c_credential\" VARCHAR(50) NOT NULL, " +
" \"c_email\" VARCHAR(255) NOT NULL, " +
" \"c_first\" VARCHAR(50) NOT NULL, " +
" \"id\" INTEGER NOT NULL, " +
" \"c_last\" VARCHAR(50) NOT NULL, " +
" \"c_name\" VARCHAR(50) NOT NULL COLLATE CASE_INSENSITIVE, " +
" );";
this.execSql(database, sql);
sql = "SET UNIQUE = 1000000 FOR \"t_user\";";
this.execSql(database, sql);
sql = "ALTER TABLE \"T_USER\" ADD PRIMARY KEY (\"ID\") NOT DEFERRABLE INITIALLY IMMEDIATE;";
this.execSql(database, sql);
ERXMigrationTable userTable = database.existingTableNamed("t_user");
userTable.addUniqueIndex("uniqueUser", userTable.existingColumnNamed("c_name"));
One other note is that if you have an existing table that you don’t want to have to drop and recreate I’m not sure how you would add the collate command to an existing column.
Jeff
On Apr 14, 2017, at 10:18 AM, Musall, Maik <
email@hidden> wrote:
I doubt you can create a function based index through the ERXMigration API.
Use plain SQL for this one.
Maik
Just getting around to adding this, but can’t figure out how to do this as part of my migration code:
Currently I have:
userTable.addUniqueIndex("uniqueUser", userTable.existingColumnNamed("c_name"));
Is there something along the lines of:
userTable.addCaseInsensitiveUniqueIndex("uniqueUser", userTable.existingColumnNamed("c_name"));
Or is there some way of adding such a constraint using EOModeler?
thanks,
Jeff
On Mar 27, 2017, at 3:52 AM, Musall, Maik <
email@hidden> wrote:
Hi,
I would just create a unique function based index, like this:
CREATE UNIQUE INDEX indexname ON MyTable( UPPER(columnName) );
No extensions required. Works with every RDBMS that supports function based indexes.
Maik
Hi Jeff,
On 25 Mar 2017, at 04:16, Jeff Schmitz <
email@hidden> wrote:
Just a quick question on how to create a case insensitive unique index in an ERXMigration?
As Samuel mentioned, this is going to be database-dependent. We’ve been using PostgreSQL’s CITEXT type for a year or so now, and it works as designed. Because it’s an extension type, you need to run:
CREATE EXTENSION IF NOT EXISTS citext;
at some point—we do this in a migration upgrade(). You can then add and alter columns and add indexes in the usual way. There’s a brief discussion on performance here:
though that’s not specific to indexing that column type.
(Finally, if you are using PostgreSQL, and you do need to add this extension to an existing database during a migration, there is a small issue with the JDBC info not being available to EOF quite early enough, which is easily fixed. I can dig up the thread if you need it.)
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden