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 <m...@selbstdenker.ag
> <mailto:m...@selbstdenker.ag>> 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 <jeffschm...@icloud.com
>> <mailto:jeffschm...@icloud.com>>:
>>
>> 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 <m...@selbstdenker.ag
>>> <mailto:m...@selbstdenker.ag>> 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 <pa...@logicsquad.net
>>>> <mailto:pa...@logicsquad.net>>:
>>>>
>>>> Hi Jeff,
>>>>
>>>> On 25 Mar 2017, at 04:16, Jeff Schmitz <jeffschm...@icloud.com
>>>> <mailto:jeffschm...@icloud.com>> 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
>>>>
>>>> <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/ <http://logicsquad.net/>
>>>>
>>>>
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com
>>>> <mailto:Webobjects-dev@lists.apple.com>)
>>>> Help/Unsubscribe/Update your Subscription:
>>>> https://lists.apple.com/mailman/options/webobjects-dev/maik%40selbstdenker.ag
>>>>
>>>> <https://lists.apple.com/mailman/options/webobjects-dev/maik%40selbstdenker.ag>
>>>>
>>>> This email sent to m...@selbstdenker.ag <mailto:m...@selbstdenker.ag>
>>>
>>> _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com
>>> <mailto:Webobjects-dev@lists.apple.com>)
>>> Help/Unsubscribe/Update your Subscription:
>>> https://lists.apple.com/mailman/options/webobjects-dev/jeffschmitz%40icloud.com
>>>
>>> <https://lists.apple.com/mailman/options/webobjects-dev/jeffschmitz%40icloud.com>
>>>
>>> This email sent to jeffschm...@icloud.com <mailto:jeffschm...@icloud.com>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com
> <mailto:Webobjects-dev@lists.apple.com>)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/jeffschmitz%40icloud.com
>
> <https://lists.apple.com/mailman/options/webobjects-dev/jeffschmitz%40icloud.com>
>
> This email sent to jeffschm...@icloud.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
