Re: [sqlite] incorrect where clause does not throw error

[John Machin]

On 10/05/2009 6:53 AM, Stefan Finzel wrote:
> Porting an application to sqlite3.6.13 on Linux i made a mistake 
> creating a illegal query on a character field:
> 
> select * from Test where Remark = NULL
> 
> select * from Test where Remark <> NULL
> 
> I was confused as there were neither data nor an error. Shouldn't this 
> cause at least an error message?

No. It's not "illegal", and it's not just SQLite.

http://en.wikipedia.org/wiki/Null_(SQL)

HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] incorrect where clause does not throw error

[Stefan Finzel]

Porting an application to sqlite3.6.13 on Linux i made a mistake 
creating a illegal query on a character field:


select * from Test where Remark = NULL

select * from Test where Remark <> NULL

I was confused as there were neither data nor an error. Shouldn't this 
cause at least an error message?



With the correct queries

select * from Test where Remark is NULL

select * from Test where Remark not NULL

everything is fine


--
_
Stefan G. R. Finzel, Dipl. Phys. (Univ.)
  Ochsenkopfstr. 5, D-95478 Kemnath, +49 (0)9642 704448
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Igor Tandetnik]

"Sam Carleton" 
wrote in message
news:8d38ca0a0905090811g7415ebereb4eb241a44e4...@mail.gmail.com
> This is my first BEFORE INSERT trigger in SQLite and I am getting an
> error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields.  I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach?  I would think
> not.

You have two options. You can have an AFTER INSERT trigger that updates 
the freshly inserted record with your desired values. Or, you can have a 
BEFORE INSERT trigger that inserts the values you want, then does SELECT 
RAISE(IGNORE); to suppress inserting the original record (one that 
caused the trigger to run in the first place).

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] estonian collation

[Igor Tandetnik]

"Allar Ounapuu" 
wrote in message news:678377.45281...@web50107.mail.re2.yahoo.com
> I'ld like to sort fields in estonian way (alphabet is
> abcdefghijklmnoprsszztuvwoauxy). I'ld like to add my own collation
> rule, but have no idea about it.

http://sqlite.org/c3ref/create_collation.html
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] estonian collation

[Allar Õunapuu]

Hello

I'ld like to sort fields in estonian way (alphabet is 
abcdefghijklmnoprsšzžtuvwõäüxy). I'ld like to add my own collation rule, but 
have no idea about it.

Allar Õunapuu

allarpuu.active.ee


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Sam Carleton]

Actually that will not work the long term.  I am going to have a
update trigger to do the same basic thing and default values will not
work there;) I would also really like to enforce on insert that both
inserter and updater be the same.

Sam

On 5/9/09, Swithun Crowe  wrote:
> Hello
>
> SC My goal is that on an insert only the insertedby value is provide. The
> SC trigger will set that to the updatedby, insertedon and updatedon
> SC fields.  I searched the web and the only examples I could find was of
> SC an AFTER INSERT, am I better off with that approach?  I would think
> SC not.
>
> How about using default values when you create the table, e.g.
>
> CREATE TABLE Customer (
> CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
> IsInSlideShow INTEGER NOT NULL,
> Username CHAR(50) NOT NULL,
> Password CHAR(50),
> insertedby CHAR(50) NOT NULL,
> instertedon DATE DEFAULT CURRENT_DATE,
> updatedby CHAR(50) NOT NULL,
> updatedon DATE DEFAULT CURRENT_DATE,
> UNIQUE (username));
>
> You can then do an insert as:
>
> INSERT INTO Customer (insertedby, updatedby) VALUES('me', 'me');
>
> i.e. pass the same value for both columns.
>
> Hope this helps.
>
> Swithun.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Simon Davies]

2009/5/9 Sam Carleton :
> This is my first BEFORE INSERT trigger in SQLite and I am getting an error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields.  I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach?  I would think
> not.

An UPDATE on a record can only work AFTER it has been inserted.

>
> Here is SQL for the table and trigger:
> ---
> CREATE TABLE Customer (
>        CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
>        IsInSlideShow INTEGER NOT NULL,
>        Username CHAR(50) NOT NULL,
>        Password CHAR(50),
>        insertedby CHAR(50) NOT NULL,
>        instertedon DATE NOT NULL,
>        updatedby CHAR(50) NOT NULL,
>        updatedon DATE NOT NULL,
>        UNIQUE (username));
>
> CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
> BEGIN
>  new.instertedon = DATETIME('NOW');
>  new.updatedon = new.instertedon;
>  new.updatedby = new.insertedby;
> END;

The trigger must be a valid sql statement.

As in:

sqlite> create table tst( id integer primary key, data real,
insertDate real, updateDate real );
sqlite> create trigger tstTrig after insert on tst begin update tst
set updateDate=insertDate where id=new.id; end;
sqlite>
sqlite> insert into tst( data, insertDate ) values( 1, julianday( '2009-05-09 12
:00:00' ) );
sqlite> insert into tst( data, insertDate ) values( 2, julianday( '2009-05-09 13
:00:00' ) );
sqlite>
sqlite> select * from tst;
1|1.0|2454961.0|2454961.0
2|2.0|2454961.0417|2454961.0417
sqlite>

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Pavel Ivanov]

According to the syntax of CREATE TRIGGER statement you can use in the
trigger body only insert/update/delete/select statements. There's no
support of simple assignments or any other programming language
extensions.

Pavel

On Sat, May 9, 2009 at 11:11 AM, Sam Carleton
 wrote:
> This is my first BEFORE INSERT trigger in SQLite and I am getting an error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields.  I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach?  I would think
> not.
>
> Here is SQL for the table and trigger:
> ---
> CREATE TABLE Customer (
>        CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
>        IsInSlideShow INTEGER NOT NULL,
>        Username CHAR(50) NOT NULL,
>        Password CHAR(50),
>        insertedby CHAR(50) NOT NULL,
>        instertedon DATE NOT NULL,
>        updatedby CHAR(50) NOT NULL,
>        updatedon DATE NOT NULL,
>        UNIQUE (username));
>
> CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
> BEGIN
>  new.instertedon = DATETIME('NOW');
>  new.updatedon = new.instertedon;
>  new.updatedby = new.insertedby;
> END;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Swithun Crowe]

Hello

SC My goal is that on an insert only the insertedby value is provide. The 
SC trigger will set that to the updatedby, insertedon and updatedon 
SC fields.  I searched the web and the only examples I could find was of 
SC an AFTER INSERT, am I better off with that approach?  I would think 
SC not.

How about using default values when you create the table, e.g.

CREATE TABLE Customer (
CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
IsInSlideShow INTEGER NOT NULL,
Username CHAR(50) NOT NULL,
Password CHAR(50),
insertedby CHAR(50) NOT NULL,
instertedon DATE DEFAULT CURRENT_DATE,
updatedby CHAR(50) NOT NULL,
updatedon DATE DEFAULT CURRENT_DATE,
UNIQUE (username));

You can then do an insert as:

INSERT INTO Customer (insertedby, updatedby) VALUES('me', 'me');

i.e. pass the same value for both columns.

Hope this helps.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED

[Florian Weimer]

* D. Richard Hipp:

> On May 3, 2009, at 11:15 AM, Florian Weimer wrote:
>
>> The documentation suggests that I can pass SQLITE_UTF16_ALIGNED.
>> However, the logic in main.c:createCollation() assumes that
>> SQLITE_UTF16_ALIGNED is ORed with another encoding flag value
>> (presumably SQLITE_UTF16).  If I specify SQLITE_UTF16_ALIGNED alone, I
>> end up with a crash due to a double free bug inside SQLite.
>>
>> (This has been observed with SQLite 3.6.13.)
>
> This has already been fixed.  See
> http://www.sqlite.org/cvstrac/chngview?cn=6558

Thanks.  But isn't this a backwards-incompatible API change?  The
changes to sqlite/src/test1.c suggest this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] setting a date in a BEFORE INSERT trigger

[Sam Carleton]

This is my first BEFORE INSERT trigger in SQLite and I am getting an error:

SQL error: near "new": syntax error

My goal is that on an insert only the insertedby value is provide.
The trigger will set that to the updatedby, insertedon and updatedon
fields.  I searched the web and the only examples I could find was of
an AFTER INSERT, am I better off with that approach?  I would think
not.

Here is SQL for the table and trigger:
---
CREATE TABLE Customer (
CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
IsInSlideShow INTEGER NOT NULL,
Username CHAR(50) NOT NULL,
Password CHAR(50),
insertedby CHAR(50) NOT NULL,
instertedon DATE NOT NULL,
updatedby CHAR(50) NOT NULL,
updatedon DATE NOT NULL,
UNIQUE (username));

CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
BEGIN
  new.instertedon = DATETIME('NOW');
  new.updatedon = new.instertedon;
  new.updatedby = new.insertedby;
END;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select performance with join

[Jim Wilcoxson]

Thanks for the explanation.  I recall seeing posts suggesting the use
of union instead of or, and thought "if it's that easy, why doesn't
SQLite do it?"  The optimizer documentation says:

---
Suppose the OR clause consists of multiple subterms as follows:

  expr1 OR expr2 OR expr3

If every subterm of an OR clause is separately indexable and the
transformation to an IN operator described above does not apply, then
the OR clause is coded so that it logically works the same as the
following:


  rowid IN (SELECT rowid FROM table WHERE expr1
UNION SELECT rowid FROM table WHERE expr2
UNION SELECT rowid FROM table WHERE expr3)

The implemention of the OR clause does not really use subqueries. A
more efficient internal mechanism is employed. The implementation also
works even for tables where the "rowid" column name has been
overloaded for other uses and no longer refers to the real rowid. But
the essence of the implementation is captured by the statement above:
Separate indices are used to find rowids that satisfy each subterm of
the OR clause and then the union of those rowids is used to find all
matching rows in the database.
---

It sounds like it might use indexes for an OR after all.

Jim

On 5/8/09, Igor Tandetnik  wrote:
> "Jim Wilcoxson"  wrote
> in message
> news:c5830b750905080813p6bf901bn526c81ca8ce7a...@mail.gmail.com
>> I don't know if it makes any difference, but is that where clause the
>> same as:
>>
>> WHERE name< 'zUmM' OR (name= 'zUmM' AND S.id< 8122)
>
> SQLite's optimizer cannot use an index for any condition involving OR.
> That's why it's common to write an equivalent but somewhat unnatural
>
> name <= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>
> This way, at least the first condition has a chance of being satisfied
> with an index.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database creation and inserts speedup

[Radu Lodina]

Hi,

General consideration:

A. Run insert in "bulk fashion":
BEGIN
INSERT INTO  /* ~1 - 2000 (or more) inserts line*/
COMMIT

B. If index have complex clause please don't create index until you finish 
data upload in tables.

Good look


- Original Message - 
From: "J. R. Westmoreland" 
To: "'General Discussion of SQLite Database'" 
Sent: Friday, May 08, 2009 4:45 PM
Subject: [sqlite] Database creation and inserts speedup


>I have a program that creates a database with a number of tables and a few
> indexes.
>
> I'm just inserting in this program and not doing any lookups.
>
> I create the indexes after all records have been inserted.
>
> The input for the database is a LARGE number of records.
>
>
>
> The data is mapping data for the U.S. and therefore contains millions of
> entries.
>
> The current run is taking about three days for the entire U.S. to 
> complete.
>
> A single state, say California, is taking several hours.
>
>
>
> Any suggestions on speed-ups?
>
>
>
> Granted this only has to be done once but if there is a failure along the
> line it could be bad. 
>
>
>
> Thanks,
>
> J. R.
>
>
>
>
>
>
>
> 
>
> J. R. Westmoreland
>
> E-mail: j...@jrw.org
>
> Twitter: GeneralJR
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] make test: Tests fail on Linux running on MIPS (Big endian)

[mwnn]

When i run the SQL queries from the failed test cases on an sqlite shell,
all of them seem to have the right opcodes. For Example, SQL queries that
are supposed to have OPENEPHEMERAL opcodes when executed on a shell lists
the OPENEPHEMERAL opcode. But when a "puts " printed
from within the test case, the OPENEPHEMERAL opcode is missing.
But for the failed count-* tests (these search for Count opcode), the opcode
was missing in both test scenarios i.e. SQLite shell as well as a puts from
the test script.

On Sat, May 9, 2009 at 12:39 AM, D. Richard Hipp  wrote:

>
> On May 8, 2009, at 2:11 PM, mwnn wrote:
>
> > Hi,
> >   Sorry about the previous mail.
> > The following test cases have failed when executing "make test" on
> > Linux
> > running on MIPS platform:
> > 28 errors out of 40151 tests
> > Failures on these tests: count-2.1 count-2.5 in3-1.6 in3-1.7 in3-1.8
> > in3-1.9
> > in3-1.11 in3-1.12 in3-1.13 in3-1.14 in3-3.5 in3-3.7 in3-4.3 in3-4.4
> > insert-5.3 insert5-2.2 insert5-2.3 insert5-2.4 insert5-2.5 insert5-2.6
> > insert5-2.8 misc3-6.10 nan-4.14 nan-4.15 nan-4.16 nan-4.17
> > trigger9-1.2.3
> > trigger9-1.5.2
>
> The nan-* errors are probably because your hardware does not support
> 80-bit floating point using "long double" and so the value 9.88e-324
> rounds to 0.0.  Not something to worry about.
>
> The other errors all seem to involve the use of EXPLAIN.  It would
> appear that EXPLAIN is busted on your build.  If you do not use
> EXPLAIN, then this is probably nothing to worry about either.
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users