Re: [sqlite] enum in SQLite

[Derrell . Lipman]

Michael Scharf <[EMAIL PROTECTED]> writes:

> Jim C. Nasby wrote:
>> On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote:
>>
>>> create trigger EnumTrg before insert on MainTbl for each row
>>> when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
>>>   select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
>>> end;
>> Wouldn't that be a lot more efficient with some kind of EXISTS test
>> rather than a count(*)?
>
> No. Because the count is at most 1 and therefore its cheaper than as exists,
> because no additional nested query is needed.
>
> However if the count(*) would be big EXISTS or LIMIT would make sense:
>   select count(*) where EXISTS (select * from TABLE where ...)
> or
>   select count(*) from (select * from TABLE where ... LIMIT 1)
>
> Michael

If you have many enum values, for slightly better efficiency (since all rows
need not be scanned), you should be able to do something like this:

 CREATE TRIGGER EnumTrg 
   BEFORE INSERT ON MainTbl
   FOR EACH ROW
 WHEN (SELECT 1
 FROM EnumVals
 WHERE val = new.EnumCol
 LIMIT 1) IS NULL
   BEGIN
 SELECT raise(rollback, 'forign-key violation: MainTbl.EnumCol');
   END;

Derrell

Re: [sqlite] enum in SQLite

[Michael Scharf]

Jim C. Nasby wrote:

On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote:


create trigger EnumTrg before insert on MainTbl for each row
when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
  select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
end;



Wouldn't that be a lot more efficient with some kind of EXISTS test
rather than a count(*)?


No. Because the count is at most 1 and therefore its cheaper than as exists,
because no additional nested query is needed.

However if the count(*) would be big EXISTS or LIMIT would make sense:
  select count(*) where EXISTS (select * from TABLE where ...)
or
  select count(*) from (select * from TABLE where ... LIMIT 1)

Michael

Re: [sqlite] enum in SQLite

[Kurt Welgehausen]

> ... you'll also need to write an update trigger ...

True, and you may want to protect EnumVals with triggers after
you populate it, or put EnumVals is a separate read-only
database and attach it. On the other hand, being able to change
the allowed values without changing the schema may be an
advantage.

Regards

Re: [sqlite] enum in SQLite

[Jim C. Nasby]

On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote:
>  create trigger EnumTrg before insert on MainTbl for each row
>  when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
>select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
>  end;

Wouldn't that be a lot more efficient with some kind of EXISTS test
rather than a count(*)?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] enum in SQLite

[Clay Dowling]

Kurt Welgehausen said:
>> SQLite doesn't support enums natively.  You could emulate it using
>> triggers, although it would be somewhat hidden and definitely a pain in
>> the tucus to use.
>
> It's not really so hard.
>
>  create table MainTbl (
>  ...
>  EnumCol SomeType references EnumVals,
>  ...);
>
>  create table EnumVals (val SomeType);
>
> Now you have to enforce the foreign key with a trigger.
>
>  create trigger EnumTrg before insert on MainTbl for each row
>  when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
>select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
>  end;

That's a lot more elegant than what I had envisioned, which was a static
list of values.  Don't forget though that you'll also need to write an
update trigger, since it's pretty easy to write "UPDATE MainTbl SET
EnumCol='bogus'"

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] enum in SQLite

[Kurt Welgehausen]

> SQLite doesn't support enums natively.  You could emulate it using
> triggers, although it would be somewhat hidden and definitely a pain in
> the tucus to use.

It's not really so hard.

 create table MainTbl (
 ...
 EnumCol SomeType references EnumVals,
 ...);

 create table EnumVals (val SomeType);

Now you have to enforce the foreign key with a trigger.

 create trigger EnumTrg before insert on MainTbl for each row
 when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
   select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
 end;


Regards

Re: [sqlite] enum in SQLite

[Clay Dowling]

Rajan, Vivek K said:
> I would like to know the following:
>
> *Does SQLite support enum like MySQL? If yes, how to use it
>
> *If not, is there another way to model enumeration in SQLite?
> And/or any plans going forward to support enums natively in SQLite?

SQLite doesn't support enums natively.  You could emulate it using
triggers, although it would be somewhat hidden and definitely a pain in
the tucus to use.

If your database is only going to be accessed by applications that you
control, it's really easy to emulate enumerations in your own code, by
restricting the possible values that can be used for a field in code.  In
the languages that I'm familiar with (C, C++, Delphi, PHP) this is best
accomplished by making a data access class for each table and putting the
logic in that class.  In fact my preference is to encode the value as a
language enum where that's possible (I'm not sure if PHP handles enums).

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

[sqlite] enum in SQLite

[Rajan, Vivek K]

I would like to know the following:

*Does SQLite support enum like MySQL? If yes, how to use it

*If not, is there another way to model enumeration in SQLite?
And/or any plans going forward to support enums natively in SQLite? 

 

Rajan