FW: [Info-Ingres] primary key as integer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leandro Pinto Fava - 09 Sep 2004 19:33 GMT

> > Could a key as was suggested be made case-insensitive? That
> would pretty
> > much be a requirement in this case, right?
>
> Absolutely!
>
> I'd probably impose a constraint, and if I were feeling
> generous I might
> also supply a rule to do automatic case folding. Unfortunately Ingres
> doesn't have a built-in namecase() function, so I'd have to code it
> (somehow).
>
> (Are there any open-source weenies out there who want to see
> my wish-list?
> :-)
>
> Roy

Hi all!

I know this is a bit different what you are talking about, but maybe is
interesting share this with you.

I can create a database with collation option using the -l flag:
createdb -l nocasefile anydb
Where nocasefile is a compiled file with nocase maps.
(II_SYSTEM/ingres/files/collation)

So, I can create a table in this database:

Create table a (a1 varchar(10) not null, a2 varchar(10), primary key
(a1));
Insert into a values ('Leandro', 'LEANDRO');
Insert into a values ('leandro', 'LEANDRO');
And the message E_US1194 Duplicate key on INSERT detected.

In that way, all char, varchar columns, in all tables of this database
are case insensitive.

Leandro Fava
DBA
Universidade de Santa Cruz do Sul-UNISC
http://www.unisc.br
Fone: +55 51 3717 7425

Reply to this Message

Spartacus - 20 Sep 2004 09:10 GMT

> I know this is a bit different what you are talking about, but maybe is
> interesting share this with you.
[quoted text clipped - 3 lines]
> Where nocasefile is a compiled file with nocase maps.
> (II_SYSTEM/ingres/files/collation)

How would I compile this file?

Emile

Reply to this Message

Emiliano - 20 Sep 2004 13:24 GMT

"Leandro Pinto Fava" <leandro@unisc.br> wrote...

Could I use a rule to force the case to lower for some text fields? The
way I read the SQL RefGuide, the rule (and thus the stored procedure)
fire after the change has already taken place. Or should I just do
UPDATE objects SET name=lowercase(name)
in the stored procedure?

Signature

Emiliano

Reply to this Message

Karl & Betty Schendel - 20 Sep 2004 15:06 GMT

>Could I use a rule to force the case to lower for some text fields? The
>way I read the SQL RefGuide, the rule (and thus the stored procedure)
>fire after the change has already taken place. Or should I just do
> UPDATE objects SET name=lowercase(name)
>in the stored procedure?

Sure, you can use a rule and DB procedure to lowercase specific
text fields. If those fields happen to be structure or index keys,
you'll cause some row moving-around, but it will work. Or you
can arrange for the application to insert both an exact-case version
and a lowercase version. Or just a lowercase version.

I wouldn't do a mass update (without a where clause) unless you really
want to update all rows in the table indiscriminately.

At 1:10 AM -0700 9/20/2004, Spartacus wrote:

>"Leandro Pinto Fava" <leandro@unisc.br> wrote in message news:<mailman.4.1094754827.655.info-ingres@ams.org>...
>
[quoted text clipped - 7 lines]
>
>How would I compile this file?

aducompile. Read all about it in the commands reference. There
might be some info on collation sequences elsewhere too, I'm guessing
in the DBA manual.

Karl

Reply to this Message

Emiliano - 20 Sep 2004 18:16 GMT

> Sure, you can use a rule and DB procedure to lowercase specific
> text fields. If those fields happen to be structure or index keys,
> you'll cause some row moving-around, but it will work.

I don't expect many updates to these fields. Lowercasing them would
simply mean I could use a simple unique constraint. So I guess that I
could just do (where 'username' would be the PK field)
UPDATE users SET username = lowercase(username) where username = new.username
in the SP triggered by the rule?

> Or you
> can arrange for the application to insert both an exact-case version
> and a lowercase version. Or just a lowercase version.

all-lowercase would do, but there'll be multiple apps talking to this
database. I'd rather see the database enforce the validity of the field.

> I wouldn't do a mass update (without a where clause) unless you really
> want to update all rows in the table indiscriminately.

Nope, I don't, I just wasn't really sure how I'd pick out the field that
was changed or added (I hope the above is correct). I was used to pre-
and post triggers, so I could just change values right before the actual
update/insert was even performed.

Signature

Emiliano

Reply to this Message

Leandro Pinto Fava - 21 Sep 2004 12:28 GMT

> At 1:10 AM -0700 9/20/2004, Spartacus wrote:
> >"Leandro Pinto Fava" <leandro@unisc.br> wrote in message
[quoted text clipped - 16 lines]
>
> Karl

Also, see good explanation in Ingres 2.6 System Administrator guide at
chapter 5 (Customization Options) page 5-3.

Leandro Fava
DBA
Universidade de Santa Cruz do Sul-UNISC
http://www.unisc.br
Fone: +55 51 3717 7425

Reply to this Message