[sqlite] schema creation

[Paolo Délano Alonso]

I noticed that the schema for my table is not being created even though my
object class has the proper attributes (PrimaryKey, Autoincrement).

My object class is located in a different solution in my VS2012 project. 

I moved the object class to the same solution where I´m calling the SQLite
methods and the schemas are created correctly.

 

Any thoughts on this?

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

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Tue, Mar 5, 2013 at 2:29 PM, Richard Hipp  wrote:

>
>
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
>
>>
>> I’ve encountered a problem, which is hardly reproducable on arbitrary
>> databases, therefore I attached one.
>>
>
> A simple, reproducible test case for (what we think is) your problem can
> be seen in this ticket:
>
>  www.sqlite.org/src/tktview/fc7bd6358f59b
>
> This bug has been latent in SQLite for almost four years and you are the
> first to hit it.  Probably this is because not many applications contain
> A=B in the WHERE clause where A is a text expression and B is an integer
> expression.  You can probably work around the problem by changing your
> schema so that entries.measurementid is an integer rather than text.  This
> does not excuse SQLite:  It is still getting the wrong answer and needs to
> be fixed.  We are working on a fix now.  But a simple change to your schema
> will work around the problem and get you going even before that fix is
> available.
>


This problem has now been fixed on trunk (see
http://www.sqlite.org/src/info/7097241c12 for details).  There is also an
amalgamation including the fix at the
http://www.sqlite.org/draft/download.html draft download page.

The release of version 3.7.16 will likely be delayed for a few days to give
folks a better changes to test this fix.  The version 3.7.16 status board
at http://www.sqlite.org/checklists/3071600 which was formerly showing a
lot of green has been reset as we intend to rerun all tests.


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

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:

>
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
>

A simple, reproducible test case for (what we think is) your problem can be
seen in this ticket:

 www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.  This
does not excuse SQLite:  It is still getting the wrong answer and needs to
be fixed.  We are working on a fix now.  But a simple change to your schema
will work around the problem and get you going even before that fix is
available.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Break on cumulative sum

[Petite Abeille]

On Mar 5, 2013, at 9:24 AM, Nico Williams  wrote:

> +1 re: recursive queries.  

There is a standard for that  (in ANSI SQL-99?): recursive 'with' clause, aka 
recursive subquery factoring, aka recursive common table expressions.

http://www.postgresql.org/docs/9.2/static/queries-with.html

This kills two birds with one stone: named, reusable subqueries *and* 
recursion. A double win. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Break on cumulative sum

[Petite Abeille]

On Mar 5, 2013, at 8:19 AM, James K. Lowden  wrote:

>> Postgresql has also had them for a while. 
> 
> Yes, and if I'm not mistaken Ingres's QUEL had them long before that.

Yes, many databases have them, from Postgres, MSSQL, Sybase IQ, some flavor of 
DB2, to Oracle, etc, ...

> (I see Microsoft calls some of them e.g.. PERCENTILE_RANK
> "analytical".  Why?  "analysis", after all, means "take apart".  They
> might be used for analysis, but window functions are every bit as
> synthetic as normal aggregate functions.)  

Oracle call them "analytic functions":

SQL for Analysis and Reporting
http://docs.oracle.com/cd/E14072_01/server.112/e10810/analysis.htm

Analytic functions really brings SQL to a whole new level of functionality and 
usefulness. Once one goes, errr, analytic, one never goes back.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Minor documentation correction for CREATE INDEX

[Peter Aronson]

No big deal, but I had to look at this recently, so I though I'd point it out  
before I forget about it.

First, according to the SQLite documentation for CREATE INDEX:
 
"If the UNIQUE keyword appears between CREATE and INDEX then duplicate index 
entries are not allowed. Any attempt to insert a duplicate entry will result in 
an error. For the purposes of unique indices, all NULL values are considered to 
different from all other NULL values and are thus unique. This is one of the 
two 
possible interpretations of the SQL-92 standard (the language in the standard 
is 
ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, 
and Oracle. Informix and Microsoft SQL Server follow the other interpretation 
of 
the standard."
 
But Oracle says this:
 
"To satisfy a composite unique key, no two rows in the table or view can have 
the same combination of values in the key columns. Any row that contains nulls 
in all key columns automatically satisfies the constraint. However, two rows 
that contain nulls for one or more key columns and the same combination of 
values for the other key columns violate the constraint."
 
Which contradicts what SQLite says, at least for multi-column unique 
constraints.  So I'd just drop Oracle from that list, since Oracle only treats 
NULLs as unique when all columns in the unique constraint are NULL.  (Which, if 
anyone should happen to ask,I thin  is a bit weird and counter-intuitive, but 
that's just me.)

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

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:

>
> The following query reports 18900080 rows (after some computation time):
>

Is this the correct answer for the query below?


>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
>


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

Re: [sqlite] Break on cumulative sum

[Joe.Fisher]

One of the reasons why we have to use PostgreSQL and/or Oracle is for 
the 'Window Functions' (AKA: Analytic Functions).
We use SQLite all the time and love it but this one missing feature 
bites us.
Could we get some advice from the SQLite core team on the feasibility of 
this?

Could it be just an extension or would it have to be more than that?

Joe


On Mar 4, 2013, at 1:32 AM, James K. Lowden  
wrote:

> What do you have in mind?  I've benn adding some user defined functions
> and am thinking of creating a repository for them.

All the so-called window functions from SQL:2003 (aka analytic functions):

"Windowed Tables and Window Functions in SQL"
http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/courses/SS2008/NEDM/RDDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf




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

Re: [sqlite] Full covering index without table

[Richard Hipp]

On Tue, Mar 5, 2013 at 12:11 PM, Tim Streater  wrote:

> On 05 Mar 2013 at 15:05, Richard Hipp  wrote:
>
> > Both implementations allow for reading just the prefix of the content
> blob
> > in order to access earlier fields of a table, leaving the tail of the
> blob
> > unread on disk.  So in all cases, it pays to put your frequently accessed
> > small fields early in your table, and your infrequently accessed
> > multi-megabyte BLOB columns at the end of the table.  That way you won't
> > have to read over a multi-megabyte BLOB just to get at the BOOLEAN value
> at
> > the end.
>
> This was interesting to read, and may result in me reordering some tables
> I have. But suppose one of my fields early in the tables is an integer
> whose value, so far, fits in 16 bits (say). What happens if a value in one
> row grows to require 24 or 32 bits to represent. Does that column get moved
> to the end of the row, past my large blobs?
>

The entire row is rewritten on any update.  So space for each integer can
be added as needed.

So, it also makes sense to store massive BLOBs in separate tables from
small integers and booleans, and do joins as needed, so that you can update
your integers and booleans without having to copy the huge BLOBs.

That said, the penalty for coping the huge BLOB is not all that great.  The
SQLite database that runs Fossil stores small integers together in the same
table with big BLOBs that hold checked-in file content.  And it sometimes
updates those integers without touching the blobs, causing the blobs to
have to be recopied.  And we've never had any performance problems (or at
least none in that particular area of the code).  So occasional updates
will be fine.  You probably only need to separate integers/booleans from
big BLOBs in extremely performance critical cases.

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

Re: [sqlite] Full covering index without table

[Tim Streater]

On 05 Mar 2013 at 15:05, Richard Hipp  wrote: 

> Both implementations allow for reading just the prefix of the content blob
> in order to access earlier fields of a table, leaving the tail of the blob
> unread on disk.  So in all cases, it pays to put your frequently accessed
> small fields early in your table, and your infrequently accessed
> multi-megabyte BLOB columns at the end of the table.  That way you won't
> have to read over a multi-megabyte BLOB just to get at the BOOLEAN value at
> the end.

This was interesting to read, and may result in me reordering some tables I 
have. But suppose one of my fields early in the tables is an integer whose 
value, so far, fits in 16 bits (say). What happens if a value in one row grows 
to require 24 or 32 bits to represent. Does that column get moved to the end of 
the row, past my large blobs?

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

Re: [sqlite] Full covering index without table

[Simon Slavin]

On 5 Mar 2013, at 3:05pm, Richard Hipp  wrote:

> I think you have misunderstood too.  Both SQLite3 and SQLite4 are
> row-oriented databases.  The storage engines are very different, but they
> still store each row as a single big blob

Okay, yep.  I did misunderstand what was posted earlier.

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

Re: [sqlite] mem1.zMalloc assertion fails

[Μάριος Φραγκούλης]

You are absolutely right.
I can't believe I didn't see that.
Tested with no complaints.

I will also update to latest version as noted.

Thanks!


2013/3/5 Dan Kennedy 

> On 03/05/2013 09:37 PM, Μάριος Φραγκούλης wrote:
>
>> I have not tapped the VVA_ONLY() definition.
>> NDEBUG is included in the compilation options
>> I use. Even if it wasn't included, SQLITE_DEBUG is not
>> defined so SQLite would define NDEBUG in
>> this case (if I understand correctly).
>>
>
> If I understand correctly that's a problem. mem1.zMalloc is
> initialized to zero inside a VVA_ONLY() macro. So if NDEBUG
> is defined mem1.zMalloc will never be initialized.
>
>
>
>
>> It may be of interest that I have tapped some
>> #if and #ifdef because of compiler complaints.
>> For example, a function was called after checking
>> that a compile option was defined, but the function
>> definition itself would happen either way. Thus the compiler
>> complained about unused functions.
>>
>> Also, I had to wrap a couple of assertions with TESTONLY() or
>> VVA_ONLY() (I noticed that this is done elsewhere in the source code)
>> because the compiler complained about
>> undeclared variables. The assertion that fails is not among those.
>>
>> I am using SQLite 3.6.22 .
>>
>> Thanks for taking the time to help me out.
>>
>>
>> 2013/3/5 Dan Kennedy 
>>
>>  On 03/05/2013 07:19 PM, Μάριος Φραγκούλης wrote:
>>>
>>>  Hi All,

 I have compiled SQLite as part of a Linux kernel module
 (Linux version 3.2.0.35-generic)
 having set SQLITE_OMIT_FLOATING_POINT,
 SQLITE_THREADSAFE=0, SQLITE_OMIT_LOAD_EXTENSION,
 SQLITE_OMIT_BUILTIN_TEST and NDEBUG.
 I have configured SQLite to operate in-memory at all times by setting
 :memory: as database name and by turning off the main and temp
 journals.
 SQLite behaves as expected but /var/log/syslog registers
 mem1.zMalloc == 0 assertion failures during queries.
 At the assertion point in sqlite3.c it is documented that mem1 is never
 malloced, hence the assertion. However, browsing the code
 I see various places where zMalloc is indeed set.

 Am I missing sth?
 If zMalloc is never malloced, are there any ideas
 about the root cause?


>>> Did the VVA_ONLY() macro get defined correctly? Was NDEBUG
>>> defined at build time?
>>>
>>> ___
>>> 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
>>
>>
> __**_
> 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] A question about the ancient history of SQLite triggers

[Dan Kennedy]

On 03/05/2013 09:59 PM, Jay A. Kreibich wrote:

On Tue, Mar 05, 2013 at 11:20:27PM +1100, Philip Warner scratched on the wall:

On 5/03/2013 9:53 PM, Richard Hipp wrote:

Recursive triggers (triggers that invoke themselves either directly or
indirectly) were added in version 3.6.18, 2009-09-11.


These are not strictly recursive; the 'when' clause means that trigger 1
will cause trigger 2 to be called etc.


   In this case, it is any trigger that invokes any other trigger.
   Prior to 3.6.18 there was no trigger "stack" and triggers could be
   only one layer deep.


I think that should have worked though. Trigger 1 should have been able
to invoke trigger 2. However, trigger 2 could not then invoke trigger 1.
So you could say that there was a kind of stack, but no single trigger
could appear within it more than once.




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

Re: [sqlite] mem1.zMalloc assertion fails

[Dan Kennedy]

On 03/05/2013 09:37 PM, Μάριος Φραγκούλης wrote:

I have not tapped the VVA_ONLY() definition.
NDEBUG is included in the compilation options
I use. Even if it wasn't included, SQLITE_DEBUG is not
defined so SQLite would define NDEBUG in
this case (if I understand correctly).


If I understand correctly that's a problem. mem1.zMalloc is
initialized to zero inside a VVA_ONLY() macro. So if NDEBUG
is defined mem1.zMalloc will never be initialized.





It may be of interest that I have tapped some
#if and #ifdef because of compiler complaints.
For example, a function was called after checking
that a compile option was defined, but the function
definition itself would happen either way. Thus the compiler
complained about unused functions.

Also, I had to wrap a couple of assertions with TESTONLY() or
VVA_ONLY() (I noticed that this is done elsewhere in the source code)
because the compiler complained about
undeclared variables. The assertion that fails is not among those.

I am using SQLite 3.6.22 .

Thanks for taking the time to help me out.


2013/3/5 Dan Kennedy 


On 03/05/2013 07:19 PM, Μάριος Φραγκούλης wrote:


Hi All,

I have compiled SQLite as part of a Linux kernel module
(Linux version 3.2.0.35-generic)
having set SQLITE_OMIT_FLOATING_POINT,
SQLITE_THREADSAFE=0, SQLITE_OMIT_LOAD_EXTENSION,
SQLITE_OMIT_BUILTIN_TEST and NDEBUG.
I have configured SQLite to operate in-memory at all times by setting
:memory: as database name and by turning off the main and temp
journals.
SQLite behaves as expected but /var/log/syslog registers
mem1.zMalloc == 0 assertion failures during queries.
At the assertion point in sqlite3.c it is documented that mem1 is never
malloced, hence the assertion. However, browsing the code
I see various places where zMalloc is indeed set.

Am I missing sth?
If zMalloc is never malloced, are there any ideas
about the root cause?



Did the VVA_ONLY() macro get defined correctly? Was NDEBUG
defined at build time?

__**_
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



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

Re: [sqlite] Full covering index without table

[Richard Hipp]

On Tue, Mar 5, 2013 at 9:45 AM, Simon Slavin  wrote:

>
> On 5 Mar 2013, at 2:37pm, Richard Hipp  wrote:
>
> > On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin 
> wrote:
> >
> >> I've seen programs written by people who always do 'SELECT *" even when
> >> they want just a couple of fields.  That's going to be a lot more
> expensive
> >> under SQLite4.  Maybe it'll teach them to be better programmers.
> >
> > Why will it be any more expensive under SQLite4 than it is under SQLite3?
>
> I probably misunderstood.  My impression was that under SQLite3, all the
> fields of one row were stored 'near' one-another, so getting the value of
> one of them would read the others into cache, whereas under SQLite4 this
> wasn't true.
>

I think you have misunderstood too.  Both SQLite3 and SQLite4 are
row-oriented databases.  The storage engines are very different, but they
still store each row as a single big blob that embeds the individual
fields.  The encoding format of this blob in SQLite3 is described at (
http://www.sqlite.org/fileformat2.html#record_format) and the encoding
format for this blob in SQLite4 is described at (
http://www.sqlite.org/src4/doc/trunk/www/data_encoding.wiki).  The
encodings are similar in philosophy but different in detail.  The SQLite4
version is a little more efficient and more extensible.

Both implementations allow for reading just the prefix of the content blob
in order to access earlier fields of a table, leaving the tail of the blob
unread on disk.  So in all cases, it pays to put your frequently accessed
small fields early in your table, and your infrequently accessed
multi-megabyte BLOB columns at the end of the table.  That way you won't
have to read over a multi-megabyte BLOB just to get at the BOOLEAN value at
the end.  Both implementations also allow you to do things like typeof(x)
and length(x) without actually reading the entire content off of disk.


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



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

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> Can you send the database directly to me at drh@... please?

It's already sent, and is also available in this shared folder:
https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing

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

Re: [sqlite] A question about the ancient history of SQLite triggers

[Jay A. Kreibich]

On Tue, Mar 05, 2013 at 11:20:27PM +1100, Philip Warner scratched on the wall:
> On 5/03/2013 9:53 PM, Richard Hipp wrote:
> > Recursive triggers (triggers that invoke themselves either directly or
> > indirectly) were added in version 3.6.18, 2009-09-11.
> 
> These are not strictly recursive; the 'when' clause means that trigger 1
> will cause trigger 2 to be called etc.

  In this case, it is any trigger that invokes any other trigger.
  Prior to 3.6.18 there was no trigger "stack" and triggers could be
  only one layer deep.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> First of all attachments are stripped out from this list, so nobody
> saw your database.

Sorry for that. Now I created a shared folder so everyone has access and can
take a look at it:
https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing

It contains the database and the full schema as sql script and as a png image
for easier overview.


> And second your above results can be not definitive if different
> datatypes and affinities come into play. So to check things out you
> should add la2.id to the above SELECT field list. If it is NULL then
> results of query with inner join are correct.

I tried your suggestion, but it gave the result I expected (i.e. correct
behavior): adding "la2.id" to the column list returned the row "47, 47", so
nothing is NULL (therefore I think they should be "innerjoinable").


> And to check why they
> are not what you expect you can do the following queries:
> 
> SELECT id, typeof(id) FROM labels WHERE id = 47;
> SELECT labelid, typeof(labelid) FROM interval2label WHERE labelid = 47;

The first query returns the row "47, integer" (as expected), the second query
returns the same row multiple times (as expected). ("labels" and
"entryintervals" are in many-to-many connection through "interval2label".)

So these debug queries return proper results, but the original problem still
exists.


> And you could show us schema of these two tables so that we could
> explain the results to you.

Here is the schema for the two requested tables (and you can find the whole
schema in the shared folder):

CREATE TABLE interval2label
  (id   INTEGER PRIMARY KEY AUTOINCREMENT,
   labelid  INTEGER,
   entry_intervalid INTEGER,

   FOREIGN KEY(labelid)REFERENCES labels(id),
   FOREIGN KEY(entry_intervalid) REFERENCES entryintervals(id),

   UNIQUE(entry_intervalid, labelid));

CREATE TABLE labels
  (id  INTEGER PRIMARY KEY AUTOINCREMENT,
   nameTEXT,
   groupid INTEGER,

   UNIQUE(name, groupid)
   FOREIGN KEY(groupid) REFERENCES labelgroups(id));


Thanks for all your hints so far! Hope my answer will help finding the cause...

Tamás

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

Re: [sqlite] Full covering index without table

[Simon Slavin]

On 5 Mar 2013, at 2:37pm, Richard Hipp  wrote:

> On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin  wrote:
> 
>> I've seen programs written by people who always do 'SELECT *" even when
>> they want just a couple of fields.  That's going to be a lot more expensive
>> under SQLite4.  Maybe it'll teach them to be better programmers.
> 
> Why will it be any more expensive under SQLite4 than it is under SQLite3?

I probably misunderstood.  My impression was that under SQLite3, all the fields 
of one row were stored 'near' one-another, so getting the value of one of them 
would read the others into cache, whereas under SQLite4 this wasn't true.

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

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> Could it be some of the features that SQLite doesn't support?
> 
> http://www.sqlite.org/omitted.html
> 
> /Patrik

No, I think this problem is independent from those thing. Only basic SQL
features are used.

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

Re: [sqlite] mem1.zMalloc assertion fails

[Richard Hipp]

On Tue, Mar 5, 2013 at 9:37 AM, Μάριος Φραγκούλης wrote:

> I am using SQLite 3.6.22 .
>
>
Why?  That version is over 3 years old.  The latest is 3.7.15.2 and 3.7.16
will be out soon.   Any of the latest versions should be a drop-in
replacement to 3.6.22 with the only noticeable difference being that the
newer versions will run faster.

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

Re: [sqlite] Full covering index without table

[Richard Hipp]

On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin  wrote:

> I've seen programs written by people who always do 'SELECT *" even when
> they want just a couple of fields.  That's going to be a lot more expensive
> under SQLite4.  Maybe it'll teach them to be better programmers.
>

Why will it be any more expensive under SQLite4 than it is under SQLite3?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mem1.zMalloc assertion fails

[Μάριος Φραγκούλης]

I have not tapped the VVA_ONLY() definition.
NDEBUG is included in the compilation options
I use. Even if it wasn't included, SQLITE_DEBUG is not
defined so SQLite would define NDEBUG in
this case (if I understand correctly).

It may be of interest that I have tapped some
#if and #ifdef because of compiler complaints.
For example, a function was called after checking
that a compile option was defined, but the function
definition itself would happen either way. Thus the compiler
complained about unused functions.

Also, I had to wrap a couple of assertions with TESTONLY() or
VVA_ONLY() (I noticed that this is done elsewhere in the source code)
because the compiler complained about
undeclared variables. The assertion that fails is not among those.

I am using SQLite 3.6.22 .

Thanks for taking the time to help me out.


2013/3/5 Dan Kennedy 

> On 03/05/2013 07:19 PM, Μάριος Φραγκούλης wrote:
>
>> Hi All,
>>
>> I have compiled SQLite as part of a Linux kernel module
>> (Linux version 3.2.0.35-generic)
>> having set SQLITE_OMIT_FLOATING_POINT,
>> SQLITE_THREADSAFE=0, SQLITE_OMIT_LOAD_EXTENSION,
>> SQLITE_OMIT_BUILTIN_TEST and NDEBUG.
>> I have configured SQLite to operate in-memory at all times by setting
>> :memory: as database name and by turning off the main and temp
>> journals.
>> SQLite behaves as expected but /var/log/syslog registers
>> mem1.zMalloc == 0 assertion failures during queries.
>> At the assertion point in sqlite3.c it is documented that mem1 is never
>> malloced, hence the assertion. However, browsing the code
>> I see various places where zMalloc is indeed set.
>>
>> Am I missing sth?
>> If zMalloc is never malloced, are there any ideas
>> about the root cause?
>>
>
> Did the VVA_ONLY() macro get defined correctly? Was NDEBUG
> defined at build time?
>
> __**_
> 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] Full covering index without table

[Simon Slavin]

On 5 Mar 2013, at 11:54am, Eleytherios Stamatogiannakis  
wrote:

> I'm not so sure how fast SQLite4 will be with respect to full scans. IMHO row 
> stores have an advantage when scanning over multiple columns.

I was thinking about that.  I've seen programs written by people who always do 
'SELECT *" even when they want just a couple of fields.  That's going to be a 
lot more expensive under SQLite4.  Maybe it'll teach them to be better 
programmers.

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

Re: [sqlite] SQLite3

[Gert Van Assche]

Matt,

it would be wise if you use a tool like the SQLite Expert; that will
generate statements that you can use on command line as well.


gert


2013/3/5 Duncan, Matthew 

> I am hoping you can help me.
>
> I have a file "DB2.db" in sqlite3 format. I am trying to view the
> database using the SQLite3 application but I am not familiar with the
> language to write prompt commands. Are you able to please help?
>
>
> Many Thanks & Best Regards
>
> Mat Duncan
>
>
> 
>
> This e-mail is intended for the use of the addressee(s) only and may
> contain privileged, confidential, or proprietary information that is exempt
> from disclosure under law. If you have received this message in error,
> please inform us promptly by reply e-mail, then delete the e-mail and
> destroy any printed copy. Thank you.
>
>
> 
> ___
> 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

[sqlite] SQLite3

[Duncan, Matthew]

I am hoping you can help me.

I have a file "DB2.db" in sqlite3 format. I am trying to view the database 
using the SQLite3 application but I am not familiar with the language to write 
prompt commands. Are you able to please help?


Many Thanks & Best Regards

Mat Duncan




This e-mail is intended for the use of the addressee(s) only and may contain 
privileged, confidential, or proprietary information that is exempt from 
disclosure under law. If you have received this message in error, please inform 
us promptly by reply e-mail, then delete the e-mail and destroy any printed 
copy. Thank you.



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

Re: [sqlite] mem1.zMalloc assertion fails

[Dan Kennedy]

On 03/05/2013 07:19 PM, Μάριος Φραγκούλης wrote:

Hi All,

I have compiled SQLite as part of a Linux kernel module
(Linux version 3.2.0.35-generic)
having set SQLITE_OMIT_FLOATING_POINT,
SQLITE_THREADSAFE=0, SQLITE_OMIT_LOAD_EXTENSION,
SQLITE_OMIT_BUILTIN_TEST and NDEBUG.
I have configured SQLite to operate in-memory at all times by setting
:memory: as database name and by turning off the main and temp
journals.
SQLite behaves as expected but /var/log/syslog registers
mem1.zMalloc == 0 assertion failures during queries.
At the assertion point in sqlite3.c it is documented that mem1 is never
malloced, hence the assertion. However, browsing the code
I see various places where zMalloc is indeed set.

Am I missing sth?
If zMalloc is never malloced, are there any ideas
about the root cause?


Did the VVA_ONLY() macro get defined correctly? Was NDEBUG
defined at build time?

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

Re: [sqlite] Break on cumulative sum

[James K. Lowden]

On Tue, 5 Mar 2013 02:24:53 -0600
Nico Williams  wrote:

> Lies!  :)  There's one more type of UDF in SQLite3: virtual tables.
> You could have a virtual table where selecting with an equality test
> for some column is "passing an argument" to a "table function".  I
> know, I've done this.  But it gets better!  SQLite3 is re-entrant, so
> you can actually format a SQL statement in the virtual table given its
> "arguments" and execute it (being careful to not create a SQL
> injection vulnerability).  IIRC FTS works this way.

Hmm, OK.  I think you're right; this could work, even without parsing
any SQL.  I'm imagining a virtual table like

CREATE VIRTUAL TABLE output
USING tfn( 'create table output ...', 'select ...' );

where the function executes the first argument to create a table,
solely to use the metadata to determine the names and types of its own
columns.  (That saves reinventing a column-defintion language.)  Then
it executes the SQL in the second argument, the output of which become
the rows in the table.  It could even be executed recursively (3rd
argument).  

> With a tiny bit of syntactic sugar we could have a way to define table
> functions given simple scalar functions, which would be very nice
> indeed.

I don't know if that's the route; perhaps the good doctor will offer
his views.  

I think of a table function not as a disaggregator -- ick! -- but as a
parameterized view.  Or, really, any function whose output can be
expressed as a relation using any combination of C and SQL.  

Most "real" DBMSs have some form of stored procedure that's just a
named blob of SQL.  Table functions could take the place of stored
procedures in SQLite in a more disciplined way.  That would indeed be
very nice.  

> +1 re: recursive queries.  Once or twice I've resorted to a UNION ALL
> of LEFT OUTER self-JOINs, each sub-query having more and more
> self-joins -- this limits recursion depth effectively, but there's
> going to be a limit anyways.  

I wrote a UDF that executes recursively and returns a string
indicating the number of recursions and total number of rows.  You pass
in the query and target table (because we don't have table functions!)
and then select from it.   That's not as bad as it sounds.   Consider
for example a table "tree" representing a filesystem hierarchy: 

create temporary table t 
( p int -- parent
, c int -- child
, primary key (c,p)
); 
insert into t 
select * from tree 
where inode in ( 
select p from t -- "recursion"
UNION 
select inode from inodes -- seed
where filename = 'usr'
)
and not exists (-- limit
select 1 from t 
where p = pnode 
and c = inode
)

can be invoked as 

$ sqlite3 -list ../inodes.db "create temporary table t (p int, c int,
primary key (c,p)); select recurse('insert into t select tree.* from
tree where inode in (select p from t UNION select inode from inodes
where filename = ''usr'') and not exists (select 1 from t where p =
pnode and c = inode)'); \
select catname(parent_name, filename) \
from inodes where inode in \
(select c from t) \
order by catname(parent_name, filename);"

17 rows returned in 10 iterations 
catname(parent_name, filename) 
/usr/src/external
/usr/src/external/gpl3
/usr/src/external/gpl3/gcc
/usr/src/external/gpl3/gcc/dist
/usr/src/external/gpl3/gcc/dist/gcc
/usr/src/external/gpl3/gcc/dist/gcc/testsuite
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg/cpp
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg/cpp/usr
/usr/src/gnu
/usr/src/gnu/dist
/usr/src/gnu/dist/gcc4
/usr/src/gnu/dist/gcc4/gcc
/usr/src/gnu/dist/gcc4/gcc/testsuite
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg/cpp
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg/cpp/usr

Unlike a bunch of self-joins, this produces a correct result regardless
of depth; the recurse() function stops when an iteration produces zero
rows.  But it bears the overhead of inserting into another table and
re-executing the query (and retesting the target) on every iteration.
Moving the recursion inside the query engine would avoid all that. 

--jkl


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

Re: [sqlite] A question about the ancient history of SQLite triggers

[Philip Warner]

On 5/03/2013 9:53 PM, Richard Hipp wrote:
> Recursive triggers (triggers that invoke themselves either directly or
> indirectly) were added in version 3.6.18, 2009-09-11.

These are not strictly recursive; the 'when' clause means that trigger 1 will 
cause trigger 2 to be called etc.

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

[sqlite] mem1.zMalloc assertion fails

[Μάριος Φραγκούλης]

Hi All,

I have compiled SQLite as part of a Linux kernel module
(Linux version 3.2.0.35-generic)
having set SQLITE_OMIT_FLOATING_POINT,
SQLITE_THREADSAFE=0, SQLITE_OMIT_LOAD_EXTENSION,
SQLITE_OMIT_BUILTIN_TEST and NDEBUG.
I have configured SQLite to operate in-memory at all times by setting
:memory: as database name and by turning off the main and temp
journals.
SQLite behaves as expected but /var/log/syslog registers
mem1.zMalloc == 0 assertion failures during queries.
At the assertion point in sqlite3.c it is documented that mem1 is never
malloced, hence the assertion. However, browsing the code
I see various places where zMalloc is indeed set.

Am I missing sth?
If zMalloc is never malloced, are there any ideas
about the root cause?
Memory management in the described environment
relies on kmalloc, kfree and krealloc. These are called
through defined macros in place of malloc, free and realloc
respectively.

Thanks a lot,

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

Re: [sqlite] Full covering index without table

[Eleytherios Stamatogiannakis]

I'm not so sure how fast SQLite4 will be with respect to full scans. 
IMHO row stores have an advantage when scanning over multiple columns.


Concerning dropping the index and recreating it on the other side. Its 
doable but very bad from a performance point of view.


If you know how the other side will access the data, then the best 
option is to build in parallel the indexes over all the data chunks (on 
every cluster node), and then send the indexed chunks on the other side. 
Having doubled data (table+full covering index) in these chunks, halves 
the I/O bandwidth of the whole cluster.


Also i should point that our main use case is OLAP processing and not OLTP.

lefteris.

On 05/03/13 10:51, Nico Williams wrote:

SQLite4 gets this right...  Of course, it's not been released.
___
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] A question about the ancient history of SQLite triggers

[Richard Hipp]

On Tue, Mar 5, 2013 at 1:11 AM, Philip Warner  wrote:

>
> What I am seeing in 3.5.9 on Android is that the triggers are executed
> precisely once each, rather than once for each row.
>

Recursive triggers (triggers that invoke themselves either directly or
indirectly) were added in version 3.6.18, 2009-09-11.


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

Re: [sqlite] Break on cumulative sum

[Dominique Devienne]

On Tue, Mar 5, 2013 at 9:47 AM, Nico Williams  wrote:
> Well, under the hood table functions could use temp, gensym'ed virtual
> tables for all I care.  The point is I want table functions :)

SQLite already has table functions of sort: PRAGMAs...

They have their own issues (not real tables, so cannot be used in joins,
AFAIK), and of course you cannot add your own PRAGMA in client code using
the SQLite3 C API.

I'm hoping there's no big leap between current PRAGMAs and real table
functions, with SQL grammar changes to be able to <<>>, and that Dr. Hipp agrees table functions
would be a valuable addition of course :). --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full covering index without table

[Nico Williams]

SQLite4 gets this right...  Of course, it's not been released.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Break on cumulative sum

[Nico Williams]

On Tue, Mar 5, 2013 at 2:44 AM, Dominique Devienne  wrote:
> On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams  wrote:

> Right. Virtual Tables are very flexible, but the syntax is indeed not
> practical, and it also forces you to name and "instantiate" tables, when
> often you want to use table functions on the fly in a JOIN.

I agree with everything you said.

> VTables just isn't the right fit for table functions IMHO. My $0.02. --DD

Well, under the hood table functions could use temp, gensym'ed virtual
tables for all I care.  The point is I want table functions :)

> PS: Table functions are basically the "reverse" of aggregate functions to
> me. They take scalar inputs, and generate rows, while aggregate functions
> take rows and generate scalar(s)..

Indeed!

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

Re: [sqlite] Break on cumulative sum

[Dominique Devienne]

On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams  wrote:
> On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden
> > 2.  Aggregation.  The engine passes the rows to be aggregated to the
> > function.  After the last row, it calls the function's "xFinal"
> > function, which returns a value.  Example: median().
>
> Lies!  :)  There's one more type of UDF in SQLite3: virtual tables.
> You could have a virtual table where selecting with an equality test
> for some column is "passing an argument" to a "table function".  I
> know, I've done this.  But it gets better!  SQLite3 is re-entrant, so
> you can actually format a SQL statement in the virtual table given its
> "arguments" and execute it (being careful to not create a SQL
> injection vulnerability).  IIRC FTS works this way.
>
> With a tiny bit of syntactic sugar we could have a way to define table
> functions given simple scalar functions, which would be very nice
> indeed.

Right. Virtual Tables are very flexible, but the syntax is indeed not
practical, and it also forces you to name and "instantiate" tables, when
often you want to use table functions on the fly in a JOIN.

Tables functions are very useful to "re-tabulate" de-normalized data
stuffed into a CSV or JSON or XML text column for example.

Once you have table functions, windowing analytics probably becomes easier
as well to express (although not necessarily efficient).

VTables just isn't the right fit for table functions IMHO. My $0.02. --DD

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

PS: Table functions are basically the "reverse" of aggregate functions to
me. They take scalar inputs, and generate rows, while aggregate functions
take rows and generate scalar(s)..
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Break on cumulative sum

[Nico Williams]

On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden
 wrote:
> Right you are, except it's not a question of efficient but of
> possible. A UDF works one of two ways:
>
> 1.  Per row.  The engine calls the function for each row.  The function
> transforms the input into the output.  Examples are sqrt() and pow().
>
> 2.  Aggregation.  The engine passes the rows to be aggregated to the
> function.  After the last row, it calls the function's "xFinal"
> function, which returns a value.  Example: median().

Lies!  :)  There's one more type of UDF in SQLite3: virtual tables.
You could have a virtual table where selecting with an equality test
for some column is "passing an argument" to a "table function".  I
know, I've done this.  But it gets better!  SQLite3 is re-entrant, so
you can actually format a SQL statement in the virtual table given its
"arguments" and execute it (being careful to not create a SQL
injection vulnerability).  IIRC FTS works this way.

With a tiny bit of syntactic sugar we could have a way to define table
functions given simple scalar functions, which would be very nice
indeed.

>> The engine would need to become window-aware, which is
>> probably enough work to delay its introduction until a consortium
>> member decides they need it.
>
> I would say the same.  It changes the grammar and almost certainly
> intrudes on the  GROUP BY implementation.  AFAIK it's not on the
> sqlite4 agenda.

I've often wondered if there's interest in a heavier-weight fork of
SQLite*.  Clearly there isn't: it'd have been done already.  Without
support from the consortium (and a fork wouldn't get that support) it
couldn't possibly hold a candle to SQLite in terms of robustness.

> Adding nonstandard equivalent functionality by extending aggregate
> functions might be easier. But the inputs to the OVER clause --
> partition, range, order -- would still be required.

For aggregate functions like group_concat() an ORDER clause to apply
to its inputs would be wonderful.

> I'm surprised there's much interest in using SQLite for fancy
> grouping.  My hobbyhorse is recursive queries, which IMO are much more
> of a nuisance to carry out.

+1 re: recursive queries.  Once or twice I've resorted to a UNION ALL
of LEFT OUTER self-JOINs, each sub-query having more and more
self-joins -- this limits recursion depth effectively, but there's
going to be a limit anyways.  I've also use recursive triggers to good
effect (though triggers slow things down plenty).

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

Re: [sqlite] Full covering index without table

[Dominique Devienne]

On Tue, Mar 5, 2013 at 1:59 AM, Carlos Milon Silva 
wrote:
> Also, You could not rebuild the index from the index, if necessary.

I'm not sure what your point is Carlos. The table is the index, so there's
no index to drop or rebuild.

What Eleytherios is referring to, is known in Oracle as an
Index-Organized-Table (IOT).

They have their uses (e.g. http://stackoverflow.com/questions/3382939),
although it's just an optimization. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users