[sqlite] schema creation

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

Re: [sqlite] inner vs. outer join inconsistency

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

Re: [sqlite] inner vs. outer join inconsistency

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:

Re: [sqlite] Break on cumulative sum

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.

Re: [sqlite] Break on cumulative sum

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,

[sqlite] Minor documentation correction for CREATE INDEX

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

Re: [sqlite] inner vs. outer join inconsistency

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 =

Re: [sqlite] Break on cumulative sum

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

Re: [sqlite] Full covering index without table

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

Re: [sqlite] Full covering index without table

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 >

Re: [sqlite] Full covering index without table

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

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. >>

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

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.

Re: [sqlite] mem1.zMalloc assertion fails

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

Re: [sqlite] Full covering index without table

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 *"

Re: [sqlite] inner vs. outer join inconsistency

> 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

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

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

Re: [sqlite] inner vs. outer join inconsistency

> 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

Re: [sqlite] Full covering index without table

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

Re: [sqlite] inner vs. outer join inconsistency

> 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

Re: [sqlite] mem1.zMalloc assertion fails

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

Re: [sqlite] Full covering index without table

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. >

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

Re: [sqlite] Full covering index without table

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

Re: [sqlite] SQLite3

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

[sqlite] SQLite3

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

Re: [sqlite] mem1.zMalloc assertion fails

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

Re: [sqlite] Break on cumulative sum

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,

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

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] 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

Re: [sqlite] Full covering index without table

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

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

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

Re: [sqlite] Break on cumulative sum

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

Re: [sqlite] Full covering index without table

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

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"

Re: [sqlite] Break on cumulative sum

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

Re: [sqlite] Break on cumulative sum

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

Re: [sqlite] Full covering index without table

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