Jeff,
With FrontBase, I usually create the table with the migration and execute this
SQL to alter the column collation:
"ALTER COLUMN \"TableName\".\"columnName\" TO COLLATE
\"CaseInsensitive\";"
Same logic to initialize unique sequence to lower value:
"SET UNIQUE = 10 FOR \"TableName\";"
This way, the custom parts are put in evidence in the code.
Samuel
> Le 17 avr. 2017 à 23:50, Jeff Schmitz <[email protected]> a écrit :
>
> 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 protected]
>> <mailto:[email protected]>> 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 protected]
>>> <mailto:[email protected]>>:
>>>
>>> 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 protected]
>>>> <mailto:[email protected]>> 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 protected]
>>>>> <mailto:[email protected]>>:
>>>>>
>>>>> Hi Jeff,
>>>>>
>>>>> On 25 Mar 2017, at 04:16, Jeff Schmitz <[email protected]
>>>>> <mailto:[email protected]>> 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 ([email protected]
>>>>> <mailto:[email protected]>)
>>>>> 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 [email protected] <mailto:[email protected]>
>>>>
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list ([email protected]
>>>> <mailto:[email protected]>)
>>>> 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 [email protected] <mailto:[email protected]>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list ([email protected]
>> <mailto:[email protected]>)
>> 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 [email protected] <mailto:[email protected]>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected]
> <mailto:[email protected]>)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com>
>
> This email sent to [email protected] <mailto:[email protected]>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
