enum in SQLite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Reply | Threaded
Open this post in threaded view
|

Re: 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

Reply | Threaded
Open this post in threaded view
|

Re: 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
Reply | Threaded
Open this post in threaded view
|

Re: 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

Reply | Threaded
Open this post in threaded view
|

Re: enum in SQLite

Jim C. Nasby
In reply to this post by Kurt Welgehausen
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      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: enum in SQLite

Kurt Welgehausen
In reply to this post by Clay Dowling
> ... 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
Reply | Threaded
Open this post in threaded view
|

Re: enum in SQLite

Michael Scharf-3
In reply to this post by Jim C. Nasby
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

Reply | Threaded
Open this post in threaded view
|

Re: enum in SQLite

Derrell Lipman
Michael Scharf <[hidden email]> 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