TOPIC: Suneido database and SQL equivalent of NULL values
#1684
Suneido database and SQL equivalent of NULL values 8 Months, 3 Weeks ago
I have some tables where I have integer fields and I do a distinction betwen 0 and NULL.

There is a way to represent NULL values in Suneido database ?
 
 
#1688
Re:Suneido database and SQL equivalent of NULL values 8 Months, 3 Weeks ago
The closest equivalent to null is "", which is the "default" value for database fields.

Suneido database fields are not typed so you can store "" in a normally numeric field. Most UI controls and report formats will handle "" ok.

You could use something else, like false, but UI controls and report formats might not handle that properly.
 
 
andrew
 
#1781
Re:Suneido database and SQL equivalent of NULL values 8 Months, 1 Week ago
Well suneido database doesn't handle nulls as are you saying but probably we need a way to tell it to interprete "" as NULL for keys and indexes, for example I have in sqlite and postgresql a unique index composed by three fields (string, string, number) if any of then is NULL none is entered on the index and it's allowed to enter the record in the database.

table testkey(id integer primary key, kind varchar(4),
series varchar(4), number integer,
unique(kind,series,number))

insert into testkey(id, kind, series, number) values(1, 'fv','a',1);
insert into testkey(id, kind, series, number) values(2, 'fv','a',NULL);
insert into testkey(id, kind, series, number) values(3, 'fv','a',NULL);

The above will succed on SQL databases.

insert {id:1 kind:'fv' series:'a' number:1) into testkey;
insert {id:2 kind:'fv' series:'a' number:'') into testkey;
insert {id:3 kind:'fv' series:'a' number:'') into testkey;

But the above equivalent in suneido fails because it doesn't ignore index insertions when any of the components are empty "".

I propose a new keyword to index creation:

index lower deaccent nullempty (field1,field2,field3)

"deaccent" is an extension sugested by me, maybe it could be generalized to allow any user supplied function to be applied to an index before insertion.

"nullempty" will tell suneido database to ignore index insertion if any field component is empty.
 
 
Last Edit: 2009/12/18 18:07 By mingodad.
 
#1783
Re:Suneido database and SQL equivalent of NULL values 8 Months, 1 Week ago
Based on your insert commands it looks like you are trying to insert the value 1 into the id field for all 3 records. Is the id field your key?

Do you need to enforce uniqueness on the index or do you just need an index? If you don't specify "unique" when creating the index then the index values can be duplicated.
 
 
Jeff Ferguson
Suneido Software
 
#1784
Re:Suneido database and SQL equivalent of NULL values 8 Months, 1 Week ago
Thanks for pointing my mistake based on copy and paste, I edited the entry and corrected it.

I actually manage to modify suneido to allow unique index but ignoring empty keys.

I'm also trying to add autoincrement to it.

New syntax:

create table(field1, field2, field3, ...)
key autoincrement(field1)
index unique lower deaccent ignoreempty(field2, field3)

autoincrement: will set the numeric field only when empty to the highest existing one+1
deaccent: will remove accented chars
ignoreempty: if any field that compose a key is empty, nothing will be inserted on the index and the operation will succeed.
 
 
Last Edit: 2009/12/18 18:16 By mingodad.
 
#1785
Re:Suneido database and SQL equivalent of NULL values 8 Months, 1 Week ago
Also there is another problem with suneido database:

create tablea(id, name)
key(id)
key lower(name)

insert {id:1 name:'name1'} into tablea
insert {id:2 name:'name2'} into tablea

create tableb(id, name)
key(id)
key lower(name) in tablea(name)

insert {id:1 name:'name1'} into tableb
insert {id:2 name:'Name2'} into tableb

When specifying a foreign key with lower, suneido database doesn't apply lower before compare with a foreign key and throws an error when it should not like in the example above where the second insertion on tableb is conceptually valid but suneido reject it.
 
 
#1787
Re:Suneido database and SQL equivalent of NULL val 8 Months, 1 Week ago
The implementation for lower is not complete. It will create the indexes but that is all.
 
 
andrew
 
#1790
Re:Suneido database and SQL equivalent of NULL val 8 Months, 1 Week ago
Even on holidays you don't resist and take a look here !