• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Set case insensitive unique index with ERXMigrations
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Set case insensitive unique index with ERXMigrations


  • Subject: Re: Set case insensitive unique index with ERXMigrations
  • From: Jeff Schmitz <email@hidden>
  • Date: Mon, 17 Apr 2017 20:50:36 -0700

Just to finish this out, I ended up having to use the collation option as I couldn’t figure out how to implement Maik’s option in Frontbase SQL.  In the end, my table creation where the name field is created to be case insensitive looks like this in my migration function.  Note that the first sql statement below is unique to Frontbase.


      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

Am 14.04.2017 um 15:12 schrieb Jeff Schmitz <email@hidden>:

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

Am 27.03.2017 um 02:29 schrieb Paul Hoadley <email@hidden>:

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:

http://stackoverflow.com/questions/31133603/in-postgresql-weird-issue-about-citext-performance

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.)


-- 
Paul Hoadley
http://logicsquad.net/

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
  • Follow-Ups:
    • Re: Set case insensitive unique index with ERXMigrations
      • From: Samuel Pelletier <email@hidden>
    • Re: Set case insensitive unique index with ERXMigrations
      • From: "Musall, Maik" <email@hidden>
References: 
 >Re: Set case insensitive unique index with ERXMigrations (From: Jeff Schmitz <email@hidden>)
 >Re: Set case insensitive unique index with ERXMigrations (From: "Musall, Maik" <email@hidden>)

  • Prev by Date: Creating working wocontext()
  • Next by Date: Re: Set case insensitive unique index with ERXMigrations
  • Previous by thread: Re: Set case insensitive unique index with ERXMigrations
  • Next by thread: Re: Set case insensitive unique index with ERXMigrations
  • Index(es):
    • Date
    • Thread