Re: [sqlite] Full covering index without table
On Tue, Mar 5, 2013 at 1:59 AM, Carlos Milon Silva mi...@terra.com.br 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
Re: [sqlite] Break on cumulative sum
On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden jklow...@schemamania.org 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] Break on cumulative sum
On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams n...@cryptonector.com 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
On Tue, Mar 5, 2013 at 2:44 AM, Dominique Devienne ddevie...@gmail.com wrote: On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams n...@cryptonector.com 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] 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 9:47 AM, Nico Williams n...@cryptonector.com 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 select * from TABLE(my_table_func(...)), 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] A question about the ancient history of SQLite triggers
On Tue, Mar 5, 2013 at 1:11 AM, Philip Warner p...@rhyme.com.au 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] 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 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
[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] 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-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, 5 Mar 2013 02:24:53 -0600
Nico Williams n...@cryptonector.com 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] 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 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] 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 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] 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 mat.dun...@kcc.com 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
Re: [sqlite] Full covering index without table
On 5 Mar 2013, at 11:54am, Eleytherios Stamatogiannakis est...@gmail.com 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] 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 danielk1...@gmail.com 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-**usershttp://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
On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin slav...@bigfraud.org 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
On Tue, Mar 5, 2013 at 9:37 AM, Μάριος Φραγκούλης mfrank...@gmail.comwrote: 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] 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full covering index without table
On 5 Mar 2013, at 2:37pm, Richard Hipp d...@sqlite.org wrote: On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin slav...@bigfraud.org 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
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] 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 '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
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] Full covering index without table
On Tue, Mar 5, 2013 at 9:45 AM, Simon Slavin slav...@bigfraud.org wrote: On 5 Mar 2013, at 2:37pm, Richard Hipp d...@sqlite.org wrote: On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin slav...@bigfraud.org 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] 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 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 danielk1...@gmail.com 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-**usershttp://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
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
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 danielk1...@gmail.com 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 danielk1...@gmail.com 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-usershttp://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-usershttp://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-**usershttp://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-**usershttp://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
On 5 Mar 2013, at 3:05pm, Richard Hipp d...@sqlite.org 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] Full covering index without table
On 05 Mar 2013 at 15:05, Richard Hipp d...@sqlite.org 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
On Tue, Mar 5, 2013 at 12:11 PM, Tim Streater t...@clothears.org.uk wrote: On 05 Mar 2013 at 15:05, Richard Hipp d...@sqlite.org 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] 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 an extension or would it have to be more than that? Joe On Mar 4, 2013, at 1:32 AM, James K. Lowden jklow...@schemamania.org 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] inner vs. outer join inconsistency
On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix ratomat...@gmail.com 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
[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 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] Break on cumulative sum
On Mar 5, 2013, at 8:19 AM, James K. Lowden jklow...@schemamania.org 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
Re: [sqlite] Break on cumulative sum
On Mar 5, 2013, at 9:24 AM, Nico Williams n...@cryptonector.com 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] inner vs. outer join inconsistency
On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix ratomat...@gmail.com 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] inner vs. outer join inconsistency
On Tue, Mar 5, 2013 at 2:29 PM, Richard Hipp d...@sqlite.org wrote: On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix ratomat...@gmail.com 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
[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 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
