gmane.comp.db.sqlite.general

Re: SQLITE_REAL was not declared in this scope

Igor Tandetnik — 2013-05-18T18:10:52

Which part of the documentation suggested to you that SQLITE_REAL 
exists? You are probably thinking about SQLITE_FLOAT 
(http://sqlite.org/c3ref/c_blob.html)

SQLITE_REAL was not declared in this scope

Dulini Atapattu — 2013-05-18T18:05:35

Hi,

In C++ I am using SQLITE_INTEGER, SQLITE_TEXT etc. to identify integer and
text values respectively, int columns of sqlite resulting rows, but when I
use SQLITE_REAL to identify double values, the compiler says: 'SQLITE_REAL'
was not declared in this scope.

Can I please know the reason for this and how can I identify data type of a
column with a float value in sqlite?

Potential bug in crash-recovery code: unlink() and friendsare not synchronous

thanumalayan mad — 2013-05-18T08:41:29

Hi All,

I was testing out SQLite with a framework I developed. I believe, while
running on Linux, transactions might not be durable when a power crash
happens immediately after a commit. I observed this using "SQLite version
3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64". Steps to
reproduce:

1. Use a Linux machine with an Ext4 filesystem (default mount options).
2. Create a database file, set journal_mode to DELETE, perform a
transaction using "begin transaction ... commit;".
3. Pull the power plug immediately after "commit;" returns.
4. Put plug back in, power on the machine, open database file with SQLite,
and examine whether the transaction has been executed.

Expected result: You always find that the transaction had been executed.
Observed result: You sometimes find that the transaction did not execute.
(To increase the chance that you end with the transaction not having
executed, create an Ext4 partition in an unmounted hard disk, mount the
partition with the "-o commit=30" mount op

Re: Getting datatypes of columns in a resultset from an SQLITE view

Dulini Atapattu — 2013-05-18T05:37:05

Thanks


On Fri, May 17, 2013 at 11:08 PM, Igor Tandetnik <igor-KTNBCkpTCZjdtAWm4Da02A< at >public.gmane.org> wrote:

Re: Problem with index on multiple columns

Petite Abeille — 2013-05-18T03:44:09

On May 18, 2013, at 3:13 AM, Keith Medcalf <kmedcalf-q073bl4m4jLQT0dZR+AlfA< at >public.gmane.org> wrote:


Well spoken, Sir.

Re: Problem with index on multiple columns

Keith Medcalf — 2013-05-18T01:13:59


Tables in SQLite are indexes.  That is, tables are implemented as b-tree structures where the rowid (id -- integer primary key) is the unique key to the b-tree and thus to the entire row.  Or you could look at it that a Table is a covering index of all the columns in a table that does not exist.  (Not exactly -- as a Table has the row data as the payload, whereas an index includes the rowid (id) as part of the key and is payloadless).

This applies *only* to the rowid (integer primary key).  Other indexes (as in CREATE INDEX) are always unique since the key always contains the rowid as the final (unspoken) component.  A "unique" index must be unique without considering the (unspoken) rowid component of the key.  So a manual index that is created with the rowid as the final component is always unique (whether you specify it or not) and the rowid is placed in the key twice (once spoken, once unspoken).

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

Re: table with check

Roman Fleysher — 2013-05-17T20:08:41

Thank you, both 

typeof(handedness)='null'

and 

handedness is null

work. I see the problem and agree.

Roman
________________________________________
From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org [sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org] on behalf of Peter Aronson [pbaronson-fOdFMYwuEsI< at >public.gmane.org]
Sent: Friday, May 17, 2013 3:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] table with check

The "OR NULL" doesn't work the way you think -- it's going to make the whole
expression null, which apparently check constraints treat the same as not
false.  What you want there is "OR typeof(handedness)='null'".

Peter

----- Original Message ----
_______________________________________________
sqlite-users mailing list
sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: table with check

Peter Aronson — 2013-05-17T19:30:48

The "OR NULL" doesn't work the way you think -- it's going to make the whole 
expression null, which apparently check constraints treat the same as not 
false.  What you want there is "OR typeof(handedness)='null'".

Peter

----- Original Message ----
>

Re: table with check

Richard Hipp — 2013-05-17T19:25:27

On Fri, May 17, 2013 at 3:16 PM, Roman Fleysher <
roman.fleysher-z7/QA7fyNSQW5MiHCONvZA< at >public.gmane.org> wrote:


I think you want to say "... OR handedness IS NULL" not just "... OR NULL".

 );

table with check

Roman Fleysher — 2013-05-17T19:16:52

Dear SQLiters,

I am using sqlite shell, I believe version 3.7.16.2. I created a table with CHECK condition as:

CREATE TABLE subject(
  subjectID  INTEGER PRIMARY KEY,
  handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) 
);

in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail otherwise. But:

INSERT INTO subject (subjectID,"qqq");

actually inserts qqq. Am I doing something wrong? I read manual that newer versions of sqlite should enforce CHECKs.

Thank you,

Roman

Re: Getting datatypes of columns in a resultset from anSQLITE view

Igor Tandetnik — 2013-05-17T17:38:25

Yes, as long as p_Stmt is positioned on a row (that is, the last call to 
sqlite3_step returned SQLITE_ROW).

In SQLite, columns don't really have types; only individual values do.

Igor Tandetnik

On 5/17/2013 1:32 PM, Dulini Atapattu wrote:

Re: Getting datatypes of columns in a resultset from anSQLITE view

Dulini Atapattu — 2013-05-17T17:32:33

Adding more to my question, is it possible to get the datatypes using
sqlite3_column_type(p_Stmt, iCol)?


On Fri, May 17, 2013 at 11:00 PM, Dulini Atapattu <dulini.atapattu-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org

Getting datatypes of columns in a resultset from an SQLITEview

Dulini Atapattu — 2013-05-17T17:30:21

Hi,

Is it possible to get the data types of columns in a result set that is
obtained using a view in an sqlite database?

Re: Problem with index on multiple columns

GB — 2013-05-17T16:03:09

Konstantinos Alogariastos schrieb am 17.05.2013 17:41:

Kostas,

in this case looking up by "class" first would mean to scan through 
2/5th of your data (assuming even distribution). So how long is that 
"long list of integers"? Does it reference 40% of your data? If not, 
you're better off with looking up by "id" anyway. Try for yourself.

regards
Gerd

Re: Problem with index on multiple columns

Michael Black — 2013-05-17T16:00:27

Do you have to have an autoincrement column?
You can implement a non-primary key column in a trigger that fills itself
from the rowid after insert giving you the same thing.
Mike

Re: Problem with index on multiple columns

GB — 2013-05-17T15:46:34

Richard Hipp schrieb am 17.05.2013 17:06:

It's not necessarily faster. If using the INTEGER PRIMARY KEY index 
means to scan through thousands of rows then an index lookup of only a 
handful of rows would be faster. That's why I'd like the implicit index 
to be ANALYZEd and weighted as well (at least with SQLITE_ENABLE_STAT3).

regards
Gerd

Re: Problem with index on multiple columns

Konstantinos Alogariastos — 2013-05-17T15:41:37

Thank you all for your answers. It seems indeed that the PRIMARY KEY column
is causing the problem.
The rationale behind using both "Id" and "class" in the index is that, in
my actual use case, class can have only 5 distinct values, while Id will
reach to the millions, and the table cannot be modified. Also, the
constraint for class is IN(0,1), while for id is IN ( "a large list of
integers inside here"). Therefore I thought it made more sense to first
filter on class and then on id. Now that I see that this is not possible I
should rethink my query.

Best regards,
Kostas


2013/5/17 Richard Hipp <drh-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org>

Re: Problem with index on multiple columns

Ryan Johnson — 2013-05-17T15:29:59

Could you explain that last bit? I always thought UNIQUE was implemented 
under the hood with a regular index. How would simply knowing something 
is unique make it so much easier to find the needle in the haystack 
without an index?

Do normal indexes map index keys to primary keys, so you still have to 
drill the PK index afterward? (BerkeleyDB does that, but it does it for 
all indexes, not just user-created ones).

Thanks,
Ryan

Re: Problem with index on multiple columns

Richard Hipp — 2013-05-17T15:06:15


Yes.  If "id" is the INTEGER PRIMARY KEY then that messes up everything.
Don't do that.  Change the index to omit the "id" and you'll get better
results.

Furthermore, the "id" is unique so if there is a constraint on the "id" the
query planner will always use that constraint to look up the rows in the
table directly, rather than going through an index, since doing so will be
about twice as fast as using an index.

Re: Problem with index on multiple columns

Michael Black — 2013-05-17T15:10:19

Indeed would seem so.  Remove the primary key and idx_test is used.
This is on 3.7.16.2

CREATE TABLE test(
              id INTEGER,
              class INTEGER NOT NULL);
CREATE INDEX idx_test ON TEST(class,id);
 EXPLAIN QUERY PLAN SELECT * FROM test WHERE id IN (0,1) AND class IN (3,4);
0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=? AND id=?)
(~36 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1

-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org] On Behalf Of Richard Hipp
Sent: Friday, May 17, 2013 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with index on multiple columns

On Fri, May 17, 2013 at 10:26 AM, Konstantinos Alogariastos <
marauber-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org> wrote:


Collating orders and affinities might be disqualifying the constraint on
"id" from being used with the index.


rows)

Re: Problem with index on multiple columns

GB — 2013-05-17T15:02:59

Richard Hipp schrieb am 17.05.2013 16:37:
It just came to my mind that "id" is an INTEGER PRIMARY KEY column and 
as such is part of every index anyway. Could it be that the additional 
"id" part of that index is silently ignored in this case?

regards
Gerd