Re: [sqlite] EXPLAIN QUERY PLAN
On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: > On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > > > I am trying to speed up our queries and normalize our DB and I am > > reading, > > > > http://www.sqlite.org/eqp.html > > > > But, I am missing a lot. Where do I read about the results and how to > > make changes to the DB to speed up/enhance the DB response? Thanks. > > > Normalize first. > Then work out what queries you need, and add appropriate indexes. > How fast do you need it to be? If it's still not fast enough, consider > denormalizing selectively, and what the overhead will be in maintaining > redundant data. "Normalize 'til it hurts. Denormalize 'til it works." (and in that order!) -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] ALTER COLUMN in sqlite
them. So going through this process isn't going to carry over indexes (they'll need to be recreated) but should, in the end, resolve other triggers that refer back to the table. To be honest, for something as simple as adding a NOT NULL constraint, I would just make sure the column has no NULL entries, set "pragma writeable_schema = 1", modify the CREATE TABLE statement in the sqlite_master table, restart everything, and be done with it. Not exactly proper, but fast and easy. ...and a bit dangerous. Make sure you have a backup first... modifying sqlite_master lets you play a lot of tricks, but it only takes one minor mistake to trash the database. -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] Dynamically generate SQL statements for SQLite
On Fri, Sep 13, 2013 at 09:19:23AM -0700, dochsm scratched on the wall: > I agree with the other poster. You can just use any string manipluation > routines you like to assemble the SQL into a string and then execute it. > Take care with literals, eg where name = 'fred' might have to become where > name = ''fred'' in Delphi (thats two single quotes at each end). To be more specific, you should never be dealing with literals if you construct an SQL statement. String manipulation is required for dynamic identifiers (table & column names), but *all* dynamic literals should be run through bind parameters. NEVER use string manipulations to deal with literal values... nearly all SQL Injection vulnerabilities could be avoided with this simple rule. -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] Table name length in sqlite affects performance. Why?
On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall:
> On my machine, when using relatively short table names like
> `TABLE_{table #}`, creation of a database with 10,000 tables takes
> approximately 14 seconds. These table names vary from 7 to a max of 11
> characters.
>
> When using relatively long table names like `TABLE_{table #}_{some
> unique identifying name that adds 120 or so characters}`, creation of
> a database with 10,000 tables takes approximately 60 seconds.
>
> Creating the database with long table names took over 4 times longer!
>
> Why is this the case? Is this expected behavior or a bug?
You're asking SQLite to deal with 10x the data, and it takes 4x
longer. That seems like a win to me.
Table names are stored as plain-text strings in the sqlite_master
table. Making the names much, much bigger is going to add more data
to the table, including more database pages. While sqlite_master is
not ordered, so the insertion shouldn't require shuffling the
internal B-tree, you're still dealing with a lot more pages, and
syncing all those pages to disk is going to take longer.
Like any other large insert, try wrapping the whole thing in a
transaction. SQLite is one of the few databases that allows
transactions on DDL.
-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] curious: Why no "shared library" or DLL?
On Fri, Aug 30, 2013 at 03:41:18PM +0100, Simon Slavin scratched on the wall: > The arguments against DLLs are not the normal arguments you see, > they're governed by an unusual combination of four characteristics of SQLite: > > A) SQLite is very much liked, and a huge number of programs use it. > B) SQLite is tiny. You don't waste much disk space by having lots of copies. > C) There are frequent version updates to SQLite which introduce new >facilities. > D) SQLite is detailed and complicated, so updates sometimes break >assumptions made by programmers. I'm not sure that last one is fair. Generally, the SQLite team makes a huge effort to avoid breaking backwards compatibility, both documented and assumed. There are a whole list of known bugs that will never get fixed because they might break existing applications. While there are some changes to the query optimizer that sometimes do odd things, overall this is a pretty poor reason. For me, the biggest issue is A and B, combined with the fact that many applications that very heavily use and integrate SQLite often have a custom compile with specific extensions, default values, and features turned on or off. An application that uses SQLite for its document format, for example, is going to be very intimately tied to their particular configuration of SQLite. It is much easier to just integrate that fine-tuned build directly into the application. The amalgamation encourages this. -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] recommended extension entry point name
On Fri, Aug 30, 2013 at 10:37:47AM +0200, Pepijn Van Eeckhoudt scratched on the wall: > http://www.sqlite.org/loadext.html states that: > ...omitting the second argument for the load_extension() SQL > interface - and the extension loader logic will attempt to > figure out the entry point on its own. It will first try the > generic extension name "sqlite3_extension_init". If that does > not work, it constructs a entry point using the template > "sqlite3_X_init" where the X is replaced by the lowercase > equivalent of every ASCII character in the filename... > > The documentation for sqlite3LoadExtension on the other hand says: > The entry point is zProc. zProc may be 0 in which case a > default entry point name (sqlite3_extension_init) is used. Use > of the default name is recommended. > > AFAICT the second description matches what the code actually does. The > example extensions in ext/misc all use sqlite3__init. > This avoids name clashes but makes it a little bit more cumbersome for > people to load the extensions. > > What is the recommended practice for naming the extension entry point? > Should I use sqlite3_extension_init or sqlite3__init? Although using the common name does, in theory, make it a bit easier to hand-load extensions, I've always recommended using a custom entry point, even before the extension loader enhanced to search for the "sqlite3__init()" format. The main benefit to using a common entry point is for people hand-loading modules into the sqlite3(1) command line tool. That tends to be a somewhat rare situation, and when it does happen, it tends to be done by people with a strong working knowledge of SQLite, the extension system, and whatever extension they're trying to load. It means you have to type a bit less, but it is more of a convenience thing then a end-user thing. The disadvantage of using a common entry point is much more significant (IMHO). It is pretty trivial to write a module that can be built into an SO/DLL/DYLIB and loaded dynamically, or use the same code to build the module statically directly into an SQLite library. ...*if* you use a custom entry point. If you use the generic sqlite3_extension_init() entry point, you'll quickly get into namespace issues, and it can get somewhat messy. Easier to just use a custom entry point for all your extensions. Best practices for designing extensions, including entry points are covered in some detail in chapter 9 of the book "Using SQLite". -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] Number of Colum!!!
On Tue, Aug 13, 2013 at 07:41:25PM +0530, techi eth scratched on the wall: > Is their any limit on number of Colum in SQLite3 table? Yes. http://www.sqlite.org/limits.html#max_column -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] can column name come from a table?
> I was going to say, try using a SQL prepared statement. > > For example, in Postgres you can write something like this: > > execute format( 'SELECT %I FROM table that has that columnName', >(SELECT columnName FROM columnNameTable WHERE condition how to > select limit 1) ); > > But I don't know if SQLite can do that in SQL; you might have to use > the host language. You have to use a host language. Bind parameters in prepared statements also won't work, because you cannot bind an identifier. -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] Passing collation to xBestIndex
On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall: > But it is the same as with new functions in sqlite3_module. Old SQLite > extensions does not implement xRename function which is now needed. Also, > new feature could be made optional using macro switch like some other > features in SQLite. Actually, xRename() was part of the v1 module interface. xSavepoint(), xRelease(), and xRollbackTo() were added in the v2 interface. All three of these functions are optional, and the fact they were added at the end of the data structure means a v1 module will run under a modern version of SQLite just fine. http://www.sqlite.org/c3ref/module.html -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] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 08:49:52PM +0200, Paolo Bolzoni scratched on the wall: > >From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. By default, the page-cache is 2000. Pages are typically 1KB, but have some minor overhead in the cache. Assuming you haven't turned the page cache down, until you top ~3MB there is nothing unusual at all. -j > On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal <sgb...@googlemail.com> wrote: > > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < > > paolo.bolzoni.br...@gmail.com> wrote: > > > >> So, sorry if the question sounds very vague. But what can > >> cause high memory usage in sqlite? A large transaction > >> maybe? > >> > > > > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i > > don't consider to be all that high considering what it's capable of doing > > for me. > > > > -- > > - stephan beal > > http://wanderinghorse.net/home/stephan/ > > http://gplus.to/sgbeal > > ___ > > 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 -- 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] sqlite3_auto_extension - unloaded DLL issue
On Sat, Jul 13, 2013 at 11:14:51AM +0200, Du?an Paulovi? scratched on the wall: > Hello, > we are currently facing problem with Access violation exception caused by > function sqlite3_open_v2 trying to load extensions from unloaded DLLs. > Also it would be fine to be able to load static extensions to separate > connections: > something like: > int sqlite3_load_static_extension(sqlite3 *db, void (*xEntryPoint)(void)); That would be sqlite3_load_extension(): http://sqlite.org/c3ref/load_extension.html You should be able to pass a NULL in for the filename to have the system search the current symbol table context (i.e. the plug-in DLL), rather than try to load another library. If this is an acceptable solution, I assume it works because each plugin manages its own connections to whatever SQLite databases you're using, and that the plugins do not cross-utilize extensions. In other words, the SQLite extensions used by a plugin are only used by that specific plugin, and that plugin "A" does not depend on SQLite extensions in plugin "B". In that respect, having all the extensions always auto-load is a bit of overkill, since each extension will have access to the SQLite extensions in every loaded plugin (and hence the issues with unloading). If that's the case, another solution is simply to include a copy of SQLite in each plugin. If each plugin has its own private copy of SQLite, then a call to sqlite3_auto_extension() will cause the plugin's extensions to only be installed in *that* plugin's database connections. It adds a bit of bulk to every plugin, but on any type of desktop system, it shouldn't be a big deal. You just need to integrate the SQLite code directly into the plugin, and make sure it is built in a way that the SQLite APIs are not exported from the plugin's DLL (otherwise the different plugins will clash). -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] sqlite3_auto_extension - unloaded DLL issue
On Sat, Jul 13, 2013 at 01:28:28PM +0100, Simon Slavin scratched on the wall: > > On 13 Jul 2013, at 10:14am, Dušan Paulovič <paulo...@gisoft.cz> wrote: > > > - These plugins can be loaded and uloaded by user. > > - Main application itself does not use SQLite, so each plugin using SQLite > > must be linked to it. > > This is a defect in the way plugins are implemented. The easiest cure > would be to have Bentley incorporate the SQLite API into its product > even if it wasn't called in its product. But you are not Bentley > and they may not do this just because you asked them. That would not actually solve the problem. No matter if SQLite is in a DLL or linked statically in the app, if there is sone master copy of SQLite you're going to have the same issues as application plugins are loaded and unloaded. I'd also point out that getting a DLL to link back against APIs that are part of an application is a bit of a trick. Although this is somewhat common place in Unix style systems, windows really likes to link "downstream" with DLLs. The common wisdom to do something like this would require extracting SQLite into a DLL so that both the application and the plugins could link it in. This is part of why so many Windows applications end up having dozens of DLLs, even when the code is part of their core. It is possible to get a dynamically loaded DLL to link "upstream" without passing a block of function pointers or some such nonesense, but it isn't the usual way of getting things done. -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] Array Accessing in SQLite3
On Fri, Jul 12, 2013 at 12:43:16PM +0530, techi eth scratched on the wall: > I have query regarding accessing single & multidimensional array in SQLite3. > > Example: I have created table with (test [10] INTEGER, name [50] TEXT). > > How do I pass a value to insert each element of array? > > How do I read back? (I am using callback function for read back) > > Please cover answer by considering multidimensional array case as well. Although the SQL99 and SQL2003 standard includes specs for an ARRAY column type, SQLite does not support them. As far as I'm aware, PostgreSQL is the only major database that supports the ARRAY column type. Oracle has VARRAYs, which are very similar, but the common wisdom seems to be that nested-tables are better. Arrays are a pretty specialized case. Most people deal with this type of storage need by using a one-to-many relationship to another table. -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] Literature on the information theory behind SQL(lite)?
On Tue, Jul 09, 2013 at 11:22:35PM +0200, Stephan Beal scratched on the wall: > On Tue, Jul 9, 2013 at 7:24 PM, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com > > wrote: > > > I am sorry the part that look Greek to you is actually fairly > > important base of the theory behind SQL, the relational algebra. > > Bad news for me ;), but you've given me a new search term: relational > algebra. I really wanted to put a chapter on the Relational Model into "Using SQLite", but there just wasn't room. The book went way over its page budget as it was. To jump start your searches a bit... Modern "relational databases" are called that because SQL is based off something called the Relational Model. The Relational Model is a formal mathematical system that combines Set Theory with information management. It is a "formal system" in the sense that it has rigid mathematical proofs, theorems, and all that kind of stuff. It was first defined in a 1969 IBM paper written by E.F. Codd. There are two mathematical systems that can be used to define and prove the self-consistency of the Relational Model. One system is called "Relational Algebra" and the other "Relational Calculus." The two systems start from slightly different base assumptions, and allow slightly different types of proofs, but you can more or less prove all of the Relational Model using either system. Unless you actually want postulate theorems or do proofs, the details of Relational Algebra and Relational Calculus aren't that important. If you trust that some very smart people did their math correctly, you can just trust that the proofs work out. What is useful and important is understanding the Relational Model itself, which gives you a much better idea of the fundamental operations behind SQL statements, as well as stuff like the Normal Forms and the theory behind them. I would argue, strongly, that DBAs and database developers that have a solid understanding of the Relational Model are much better at what they do. SQL is not purely Relational-- in fact, there are a lot of differences-- but having a strong grounding in the Relational Model will make you a better SQL developer. In a sense, the difference between the Relational Model and SQL is like the difference between Object Oriented Programming theory, and C++. If you learn C++, especially from a strictly syntax standpoint, you'll pick up a bit on object oriented programming, but you won't really *know* OOP. Similarly, even if you're an expert C++ developer, if C++ is you're only OOP language, you still don't really get what clean OOP is all about (because C++ sure as heck isn't that, even if it is a darn useful language). So it is with SQL-- darn useful, but not quite what the theory is about, and a very foggy glass to try to learn the theory through. If you want to learn more about the theory and concepts behind SQL, I would strongly recommend these two books: SQL and Relational Theory (2nd Ed) by C.J. Date http://shop.oreilly.com/product/0636920022879.do Relational Theory for Computer Professionals by C.J. Date http://shop.oreilly.com/product/0636920029649.do They'll get into some details about the theory, but not to the point of actual algebra or Greek math symbols to define the theory. I found them extremely useful in better understanding what I was trying to express in SQL, and how to write better, more direct SQL (and better database design). C.J. Date was one of the people that worked with E.F. Codd to refine the whole concept of modern database. He has some very strong views, some of which I don't agree with, but he's a very good writer and presenter. He's also a very, very theory heavy guy that tends to put elegance and theory before any practical concern-- which is great for research and teaching, but less useful for getting stuff done by a deadline. Still, in the last eight years or so he has visibly shifted his stance from something of a "SQL sux and must die" point of view to a more relaxed "since you're going to work in SQL anyways, you may as well do it right." And *that* I can agree with. -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] SQLite3 extended API usage
On Mon, Jul 08, 2013 at 06:43:33AM +0100, Simon Slavin scratched on the wall: > On 8 Jul 2013, at 5:15am, techi eth <techi...@gmail.com> wrote: > There is no call 'sqlite_get_table()' in SQLite version 3. Nor is there > anything like it. ...other than sqlite3_get_table()... http://www.sqlite.org/c3ref/free_table.html Which, as the docs state, is not recommended. -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] Is there a way to return the row number? (NOTthe rowid)
ping the defined row order. Views and sub-queries are the most obvious case. Views are a touchy subject, since some people will say that views can be ordered. Personally, I don't buy that. If a view is supposed to look like a table, smell like a table, and act like a table, then the view cannot have a pre-defined row order. It breaks way too many Relational ideas. Ordered views are ugly from a Relational standpoint, not just an SQL one. So if you want a view that provides any type of ordering, you need to be able to provide a rank column as part of the view result. Subqueries are usually a bit easier to understand, since it is obvious to see how a very complex query with many subqueries is not going to preserve ORDER BY output through a tree of subqueries. And, in the case of a ranking, it makes sense to order the result of a subquery, attach the query information, and then devolve the result set back into an arbitrary relation/table and let the rows scramble themselves for the next stage of the root query. More to the point, if I have a ranking functionality available, I'm happy to let the query engine do this, since I've explicitly defined the information I want to carry through the larger query. Additionally, it should be pointed out that most other databases provide a ranking function as part of their windowing function set, meaning a ranking could be applied explicitly over some data set; i.e. "RANK( total( orders ) )", rather than having ORDER BY applied over some data set and then pulling the rank information back out, i.e. "SELECT total( orders ), _row_number_ [...] ORDER BY 1". That's clearly perfectly Relational, since row order never comes into it. It is also likely to be higher performance, since the query engine understands what you want and may not have to do a full sort in order to provide it. In such a case the rows can always be treated as a proper set (i.e. Relationally). Of course, such a thing would require a large and complex update to the SQL syntax, so having a simple rank function that is dependent on ORDER BY to generate meaningful results makes some sense in the world of theory vs practicality. So, yes... overall I think some type of ranking functionality-- be it an explicit function or a virtual column or something else-- is a good idea. I think it actually improves the Relational aspects of these queries, since it allows the SQL programmer to explicitly define orders and ranking as data values, rather than having them implied in row order. This makes the result set much more Relational, since the defined row order can be dropped without losing query information. And, yes, it will be abused by people that don't really understand how to use it, or how it fits into the overall SQL language. You know, like every other aspect of the SQL language-- or any programming language-- is misused by clueless people. I don't care. It lets me sell more books. > Sort order isn't necessarily deterministic even if we know the column > order. So the possibility that we may not know it, makes life no worse. True, you can sort by random(). The sort *process* must be deterministic, however, or there isn't much point in having a sort. -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] Is there a way to return the row number? (NOTthe rowid)
On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the wall: > "select *" is shorthand for "all columns". You'll note that what's > returned isn't some kind of special '*' column, but all columns. The > order in which the columns are returned isn't meaningful because the > colums have labels -- names -- to tell you which is which. Rearranging > the column order doesn't change the answer. That's not quite true. What you say is more or less true in pure Relational Theory. Under Relational Theory, relational attributes (columns) are a proper set. The columns have no defined order (just as rows have no defined order), and can only be definitively reference by name. In SQL, columns are *not* a set. The order of the columns in any SQL query or operation is strictly defined. Columns cannot be referenced by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and multiple columns with the same name (SELECT 1 A, 1 A, 1 A;). SQL doesn't even strictly define the column name for a calculated column (SELECT avg( 1 )) and allows the DB to make up its own names. SQLite used to have several PRAGMAs to control short and long column names. Rather, in SQL, a column is definitively defined by its positional index in the table or result set. This is also why so many SQL APIs allow you to fetch column values by index, rather than by name (which would be a totally broken and dangerous API if columns could move around). It gets pretty messy... The SQL standard goes to some length to define a specific column order for stuff like JOIN operations, including edge-case details like NATURAL JOINs where the number of columns is reduced and somewhat ambiguously named. While rearranging the column order may not functionally change the answer, a database is not given that flexibility in SQL. For example, "SELECT *" *must* return the columns in the order they are defined in the table definition. It isn't that most databases just happen to do this-- the column order is actually predicated by the standard. > "sort by *" would imply that the order of the columns returned by '*' is > meaningful, which it is not. "sort by the arbitrary order produced by > 'select *'" isn't even deterministic. In SQL column order *is* deterministic, so the sort order would also be deterministic. Likely meaningless, but still deterministic. -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] Shell: .IMPORT does not seem to obey .BAIL setting
On Fri, Jun 28, 2013 at 10:56:08PM +0300, to...@acm.org scratched on the wall: > Trying to import data into a table the operation stops on first error > (actually a rollback seems to be performed) because of column number > mismatch. > > Why does it not follow the .BAIL ON/OFF setting? And, why rows before > the error are removed? > > So, in .BAIL OFF mode, I expected errors to display, but the rest of > the file to be processed. Actually, this a very nice way to filter > somewhat a file from unwanted, badly formatted lines. (Now, the only > option seems to be manual editing of the text file for each error line, > and try again.) > > Is this operation by design or a bug? By design. The .bail configuration applies to processing SQL batch files, not data imports. -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] sqlite3: .import command handles quotation incorrectly
On Wed, Jun 26, 2013 at 10:53:38AM -0700, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 26/06/13 05:07, Jay A. Kreibich wrote: > > A year or so ago there was some effort to write a plug-in that would > > use the standard Python CSV module (at least, I think it was Python) > > for data import into SQLite. The Python module is pretty good at > > importing most flavors of CSV... and as a result is an extremely > > complex bit of code. Anyone know if that project got any traction? > > I'd never heard of that. For several years I have maintained an alternate > shell as part of the Python SQLite wrapper APSW. Perhaps it is your work I'm remembering. -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] sqlite3: .import command handles quotation incorrectly
On Wed, Jun 26, 2013 at 12:35:55PM +0200, Nißl Reinhard scratched on the wall: > well we could discuss endlessly, what well formed CSV files are. > Given that we cannot fix the generation of the CSV file, why not > making the importer a little bit smarter? The standard answer to this is, "Go ahead." The relative simplicity of the .import command is an issue that comes up every year or so on this list. We've been through this before a half-dozen times. Everyone seems convinced it would be really easy and really simple to make just one small change so that the importer works with their version CSV. Unfortunately, when you actually start to dig in and make such changes, they're rarely simple. Making it better in one way often breaks it in other ways. Thanks to the poorly defined standard (or, rather, the fact there are so many different standards) there is no easy way out of this, and it quickly turns into a really, really complex issue. If it was simple, it would also be simple to write a program that converted files into a format that .import was happy with. At the end of the day, I'd MUCH rather have the SQLite team working on database features than on support commands in the shell. If you feel the change is simple, easy, and won't break any existing behaviors, feel free to submit a patch. A year or so ago there was some effort to write a plug-in that would use the standard Python CSV module (at least, I think it was Python) for data import into SQLite. The Python module is pretty good at importing most flavors of CSV... and as a result is an extremely complex bit of code. Anyone know if that project got any traction? > All I ask for is to treat an odd number of " in a cell as literal data > except when the rule for dequoting applies, i. e. when the cell starts > with a ". Sounds simple, except for the fact that quotes also help define cell boundaries (in cases when the data value has a comma). It isn't about even or odd quotes because you don't know where the cells start and stop until you untangle the quotes. -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] Issue with 2 connections getting out of sync, sample code included
On Tue, Jun 25, 2013 at 11:49:00PM +0100, Simon Slavin scratched on the wall: > > On 25 Jun 2013, at 11:45pm, Yuriy Stelmakh <yuriy...@gmail.com> wrote: > > > I assumed that commit would take care of sync, but because my > > statement was never finalized/reset after last use, it didn't it > > seems.. > > You always need to _finalize(). Getting the data you asked for is not > the end of the job. You have to _finalize() in order to let SQLite > clear up after the statement (which does more than just unlock the file, > it also does things about the memory your application uses). _finalize() isn't the only function that does this. You can also call _reset() to, well... reset the statement. This will clear all the locks and make it clear to the SQLite engine that you're done with the current execution of the statement, but will leave the statement ready to go for another execution. > So get used to this: for every _prepare() do a _finalize(). As > soon as practical. That's true, but it isn't the whole story, since there isn't a fixed relationship between _prepare() and statement executions. Using _reset() to clear the statement is perfectly acceptable. Your other point still stands, however... as soon as _step() returns SQLITE_DONE, it is best to call _reset() before doing anything else. _finalize() can also be called if you know you're done with the statement. -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] Problem with getting previous command in shell
On Thu, Jun 20, 2013 at 04:13:06PM +, Tilsley, Jerry M. scratched on the wall: > All, > > I have two environments; one production and one test/development. > Both are Redhat Linux 6.3 running SQLite 3.7.10. On the test/development > environment when I'm using the SQLite shell I can use the up/down arrows > to use previous commands, but on my prod environment I keep getting > "^]]A" instead of the previous command. I'm also running Putty to > connect via SSH to both environments with identical configurations. > > Can someone help shed some light on what I'm missing?? I know this > can be as much a OS problem as SQLite, but this group has a better > chance of figuring this out since it is occurring only inside the > SQLite shell and not at the OS level. One version of the "sqlite3" app was compiled with readline support, one was not. It is not on by default. You can re-compile with "-DHAVE_READLINE -lreadline". -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] Obtain limit-less count of previous select
On Tue, Jun 11, 2013 at 09:08:42AM -0400, Igor Tandetnik scratched on the wall: > On 6/11/2013 9:01 AM, Simon Slavin wrote: > > The only time that _prepare() will use a lot of memory is when it has to > > generate a temporary index because you have not created a table index > > suitable for the WHERE and ORDER BY clauses. > > I'm pretty sure this would happen on the first _step(), not on > _prepare(). Prepare is always nearly instantaneous. Yes. And it isn't always a temporary index. Many times, with an ORDER BY, SQLite will compute (and hold in memory) the full result set. -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] select count(*) does not use primary key??
On Thu, Jun 06, 2013 at 04:52:12PM +0200, Gabriel Corneanu scratched on the wall: > I was surprised to find that simple query "select count(*) from table" took > too much time, and found that it does NOT use the primary key index?? > e.g. > CREATE TABLE t(a); > explain query plan select count(*) from t > > I get : SCAN TABLE t (~100 rows) > > If I use CREATE TABLE t(a unique), then it uses the auto-generated cover > index. > Even if I write > select count(rowid) from t > it still uses scan table... > However I would expect that it should also use the primary key for > counting, or not?? What PK? Rowid is not a PK unless you define it as such. The table itself is stored in rowid order, so the "index" for rowid is the table itself-- there is no "other" index for rowid. > In my opinion, count(*) is the same as count(rowid) (I see that even > count() is accepted); I could say it's even the same as count(x) (any other > field). That is not true. The SQLite docs are quite clear: http://www.sqlite.org/lang_aggfunc.html#count count(X) count(*) The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group. If you provide an actual column name, count() only counts non-NULL rows. The two versions of the function are equivalent if "X" prohibits NULL entries (such as the rowid column), but not in the general case. This is not SQLite specific... this is standard SQL. -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] Serialize an in-memory database
On Wed, Jun 05, 2013 at 09:15:21PM +0200, Petite Abeille scratched on the wall: > > On Jun 5, 2013, at 9:10 PM, Philip Bennefall <phi...@blastbay.com> wrote: > > > Yes, I have seen the backup API. But I would like to avoid the disk > > file entirely and just serialize to and from memory. > > Lateral thinking? write your db to tmpfs? A few months back, wasn't there talk about a VFS that used a giant byte array, rather than a file? If someone actually wrote one of those, you could use the Backup API to blast the DB into a big chunk of memory. If such a VFS does not actually exist, it shouldn't be all that hard to write, and might come in useful for this and other reasons. -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] query help
On Mon, May 20, 2013 at 12:59:45PM +0100, Paul Sanderson scratched on the wall: > I have a table of the form > > create table tab (num int1 unique, num2, int) > > for each row for num2 there is usually a matching num1. But not always. > > I want to identify each row where num2 does not have a matching num1 > > example data might be > > num1 num2 > 1 3 > 2 3 > 3 2 > 4 1 > 5 11 > 6 3 > 7 9 > > in this example my query would return rows 5 and 7 as there is no match on > num1 for 11 and 9 Join the table to itself using an outer join on the condition that num2 == num1. Look for rows where num1 is NULL, indicating no join was found. Only works if num1 is never NULL in the DB. I need to run. Perhaps someone else can provide an example if that's not making sense. -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] Create Table oddity
On Sun, May 19, 2013 at 06:05:05PM -0400, Stephen Chrzanowski scratched on the wall: > I've been talking with Bogdan about a change to SQLite Expert and ran > across something I did NOT expect. > > I have this schema: > > CREATE TABLE [tApplicationPaths] ( > [AppID] INTEGER PRIMARY KEY, > [ApplicationName] CHAR, > [ApplicationMonitorPath] CHAR, > [SearchSubDirs] BOOL DEFAULT 1, > [SearchMask] CHAR DEFAULT AppID); > > When I insert a new row, I get the literal string "AppID" as a default > value for that new row. I was expecting a number or null, or something. > Not a string. Is this intended behavior? As shown here: http://sqlite.org/syntaxdiagrams.html#column-constraint bare words are considered literal values. If you want to reference a column, you need to use parens to create an expression. SQLite correctly identifies this as invalid: sqlite> CREATE TABLE [tApplicationPaths] ( ...>[AppID] INTEGER PRIMARY KEY, ...>[ApplicationName] CHAR, ...>[ApplicationMonitorPath] CHAR, ...>[SearchSubDirs] BOOL DEFAULT 1, ...>[SearchMask] CHAR DEFAULT (AppID) ); Error: default value of column [SearchMask] is not constant -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] How to read log file format into sqlite database?
On Sun, May 05, 2013 at 01:50:41AM -0700, Newbie89 scratched on the wall: > let say is .txt file 99+% of log files are text files. This tells us nothing useful. Is it an Apache log file? Or some other well-known format that someone might have already written a parser for? Does it have a fixed format? Does it have a fixed number of columns? How many columns does it have? How are the columns differentiated? Commas? White-space? Tabs? What are the types of each column? Integers? Text? Dates? How do you want to map columns to database tables? What is the format of the final table? You need to figure this out, not some magic one function library. All of these questions need to be asked before we even get to the import process, yet you provided none of this information. That makes it very difficult/impossible to help, except to help you ask better questions. This is long, but good: http://www.catb.org/esr/faqs/smart-questions.html In my own dealings with log files and SQLite, I usually avoid importing them all together. Importing large files is slow. Most log file searches require scans, due to the type of ad-hoc or summary queries that are typically run against logs. If the file has a well-defined format, I usually just write a virtual table module to scan the log file directly. This is MUCH faster than importing the file, and can be used against "live" log files. Chapter 10 of "Using SQLite" is all about virtual tables: http://shop.oreilly.com/product/9780596521196.do The big example in that chapter is about exposing Apache/httpd format log files the database through a virtual table. Example code can be found here: http://examples.oreilly.com/9780596521196/ -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] Feature request: Generate 'INSERT' command
On Thu, May 02, 2013 at 03:45:16PM +0100, Simon Slavin scratched on the wall: > I would like the introduction of a command which changes a SELECT the > same way EXPLAIN QUERY PLAN changes a SELECT. This one should take > the SELECT and instead of producing a table, sometimes with many > columns, produces a 1 column table with the SELECTed data shown > as INSERT commands, with values correctly single-quoted where necessary. You know the sqlite3 program already does this, right? .mode insert If you need the functionality built into your application, just rip out the code and use it. -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] Order of columns in group by statement affects query performance
On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall: > 2013/4/25 James K. Lowden <jklow...@schemamania.org> > > > > > Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an > > index ordered B,A. By permuting the order of the columns in the GROUP > > BY clause, it finds a match for the index and uses it. > > > > Yes, the problem is O(n^2), where n is the number of columns in the > > GROUP BY, but n is always small; even 7 columns could be checked in > > less than 50 iterations. > > > > I believe its O(n!), but still doable for small n. I don't know the inner > workings of the query optimizer but mabye instead of asking/check for a > index of every permutation of the columns in the group by, it could just > check if an index exists which covers all columns (even the sorting order > doesn't matter). (the virtual table api needs an addition for that to work) Permutations are O(N!), but that's not really what you want. Given a set of GROUP BY terms you want, generally, the index with the most terms in any initial order. You don't need a full match for the index to be a win. For example, GROUP BY A,B,C,D,E is likely to get a performance boost from an index on (A,D,B) and, *in general*, that should be a bigger win than an index on (B,C). Of course, since this is a query optimizer, there are always edge cases... For example, if there is an index over (E) that has 99% unique values, it is likely a better choice than (A,D,B)... it depends on the distribution of the index. Similarly, if any GROUP BY term maps to a unique index... boom, you're done. As with most things having to do with query optimization, the problem quickly explodes. On the other hand, SQLite must already have assumptions about index costs (with or without ANALYZE), so at least there's an existing set of weights and assumptions to work from. -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] Order of columns in group by statement affects query performance
On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall: > AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints > in the virtual table description). They're not the same clause, they don't do the same thing. Now, it is true that most database systems implement the first step of a GROUP BY by sorting the query using semantics that are similar to ORDER BY. That way all of the rows in a related group are next to each other, and they're easier to process. I assume SQLite does the same thing. It is, however, as they say, "an implementation detail." > IF you have an index that covers the GROUP BY clause in any other order, > then you still have the guarantee that all rows belonging to the same > group will be retrieved together, but the result rows will be ordered > in index order and not GROUP BY order. Except there is no such thing as "GROUP BY order". SQL Golden Rule: If there is no ORDER BY, the rows have no order. According to SQL, neither the groups, nor the rows within a group (as they are fed into aggregates) have a defined order. Any query that makes assumptions about the ordered result of a GROUP BY is broken. Use the out-of-order index. -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] Programming API vs console
On Tue, Apr 23, 2013 at 08:47:18PM -0700, Igor Korot scratched on the wall: > Here is the code: > > query = wxString::Format( "INSERT INTO playersdrafted VALUES( %d, %d, ( > SELECT ownerid FROM owners WHERE ownername = \"%s\" AND id = %d ), %d, %d, > \"%s\" );", player.GetPlayerId(), leagueId, const_cast( player > ).GetOwner(), leagueId, player.GetAmountPaid(), player.GetDraftOrder(), > const_cast( player ).GetDraftedPosition() ); Two side comments: 1) SQL string-literals use ' not ". 2) Using string manipulation functions to build statements is really bad form and can easily open the code up to SQL injection attacks and other problems. For example, if a player's owner string has a quote in it, this code won't work. Using bound parameters fixes all this. -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] Writing in a blob
On Tue, Apr 23, 2013 at 10:28:35AM -0400, Richard Hipp scratched on the wall: > Or, if you really want file-system semantics on top of your database (and > this is a reasonably idea, actually) you could write a wrapper library that > implemented file-system semantics on top of SQLite. As a side note, almost every video game console developer out there has some type of "filesystem in a file" library for dumping models, textures, animation, and level data into large chunks that can be streamed off optical drives. Optical systems really, really suck at seeks, so you tend to batch all the required data into a single mass, and the stream the whole thing off the disc when (for example) someone loads a new level. You really don't want to get the on-disc filesystem involved in loads if you can avoid it. Of course, since the data is read-only and the main goal is to avoid seeks, an SQLite solution would be a Very Bad Idea. Rather, it is common to base the embedded filesystem off an archive format, like tar. Using IFF files was also popular back on the cartridge days. -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] Variable-length records
On Fri, Apr 19, 2013 at 08:19:57AM +0200, Hick Gunter scratched on the wall: > IIRC temporary tables are limited to the connection that creates them. Yes. So are in-memory databases. -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] possible ordering issue since 3.7.15
On Sat, Apr 13, 2013 at 11:25:41PM +0700, Dan Kennedy scratched on the wall: > On 04/13/2013 11:22 PM, kenichi ishigaki wrote: > >Hi. > > > >I received a report that the result of the following SQL has changed > >since 3.7.15. > >I haven't looked into the sqlite source yet, but can we call this a bug? > > No. No "ORDER BY" clause means that the results are delivered in > an undefined order. So both results are the same. In fact, SQLite has a "PRAGMA reverse_unordered_selects" configuration that changes the default ordering. This specifically exists to test applications and make sure they don't assume a specific ordering in queries that lack an ORDER BY clause. You might consider this to test other areas of your application. http://sqlite.org/pragma.html#pragma_reverse_unordered_selects When enabled, this PRAGMA causes SELECT statements without an ORDER BY clause to emit their results in the reverse order of what they normally would. This can help debug applications that are making invalid assumptions about the result order. SQLite makes no guarantees about the order of results if a SELECT omits the ORDER BY clause. Even so, the order of results does not change from one run to the next, and so many applications mistakenly come to depend on the arbitrary output order whatever that order happens to be. However, sometimes new versions of SQLite will contain optimizer enhancements that will cause the output order of queries without ORDER BY clauses to shift. When that happens, applications that depend on a certain output order might malfunction. By running the application multiple times with this pragma both disabled and enabled, cases where the application makes faulty assumptions about output order can be identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite. -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] 64bit compatibility warnings
On Mon, Apr 08, 2013 at 07:41:20AM -0400, Richard Hipp scratched on the wall: > The other four appear to be due to an MSVC > compiler bug, since every (i64%int) operation will always yield a value > that can fit in an int, no? Only on systems where "int" is 32 bits or larger. OK, yes... that is nearly everything these days (and likely *everything* that supports an i64 type, even if running in 32-bit mode), but is not actually fixed by the language. Yeah, I don't buy it either. -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] Bug or some misunderstanding?
On Wed, Apr 03, 2013 at 02:28:07PM +0200, Dominique Devienne scratched on the wall: > On Wed, Apr 3, 2013 at 1:54 AM, Support <apps...@gmail.com> wrote: > > > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID > > like "USE%";' > > I get correct result. > > > > But when I call > > sqlite3 -line ~/Desktop/maps.db 'select * from airports where > > LocationID=="USE";' > > > > SQL uses =, not == (and string literals are in single-quotes, just in > case). --DD True, although SQLite supports several additional C style operators, including "==", to keep us all from going crazy. Perhaps not a good habit to get into, but perfectly valid for SQLite: http://www.sqlite.org/lang_expr.html Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. -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] ANN: user-defined functions
On Mon, Mar 25, 2013 at 08:32:12PM +0100, Jean-Christophe Deschamps scratched on the wall: > At 15:46 25/03/2013, you wrote: > > > The sqrt() function takes only one argument, at least. > > It checks assert( argc==2 ); at line 503 AFAIK. Line 503 of the version up on the website is in the middle of the pi() function. We must be looking at different files or versions. > > This library also contains several string functions, but it is meant > > as a general extension library, not a math specific library. > > I have my own set of Unicode aware string functions in another > extension, so those were useless. Fair enough. I can't say anything about how useful the string functions might be, I was simply pointing out that the library was intended as a general purpose extension library, not a math specific one. From that viewpoint, it isn't that unusual that it includes both string and math functions. -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] pragma table_info(database.table) not supported
On Wed, Mar 20, 2013 at 07:00:29PM +0100, Stephan Beal scratched on the wall: > On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > > That way I can use WHERE on them. In the past I've used virtual > > tables to wrap the PRAGMAs into something that looked and acted more > > like a real table. > > can you give us an example of how that is done? Chapter 10: http://shop.oreilly.com/product/9780596521196.do Also see the "Download Example Code" link on that page. ".../ch10/dblist.c" shows how to wrap "PRAGMA database_list" in a VT. It should be pretty easy to modify this so that it would work with any PRAGMA. There is nothing fancy going on here... the virtual table still calls the PRAGMA and sifts through the results. It just gets abstracted a bit. Somewhere I have a VT that will build a table out of any SQL statement. That sounds kind of dumb, since that's either redundant, or something you could do with a VIEW, but it can also be used for any PRAGMA. No idea where that code is off the top of my head however. -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] pragma table_info(database.table) not supported
On Wed, Mar 20, 2013 at 05:57:12PM +0100, Staffan Tylen scratched on the wall: > >PRAGMA table_info(database.tablename) > > > > Any chance that SQLite4 might change this, or perhaps accept both forms ? > In addition, how about a pragma to get the tables in a database? We > currently have pragma(database_list), pragma(index_list), and > pragma(table_info), but no pragma(table_list) - a SELECT against > sqlite_master is necessary to get hold of the table names. IMHO it's not > what one would expect. I would expect exactly the opposite. Part of the core philosophy of Relational systems is that all information, including self-inspection information, is available via tables-- specifically via the SELECT command. These are the "system catalogs" found in most DBs including Oracle, MySQL, Postgres, and many others. If there is any change I'd like to see, it is that all the PRAGMAs that return tabular data should really be system catalog tables. That way I can use WHERE on them. In the past I've used virtual tables to wrap the PRAGMAs into something that looked and acted more like a real table. -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] SQLITE_OPEN_MAIN_JOURNAL question
On Sun, Mar 17, 2013 at 11:21:54PM +0800, Neo Anderson scratched on the wall: > I'm trying to implement a VFS with encryption. I cannot find any detailed > documentation about some flags listed on http://www.sqlite.org/c3ref/vfs.html > > For example: > > SQLITE_OPEN_MAIN_JOURNAL > SQLITE_OPEN_MASTER_JOURNAL > SQLITE_OPEN_SUBJOURNAL > > There is a minimal mention of these flags in source code, but no > detailed explanation what should implementation take care of them. > > Wher can I find detailed documentation about these flags? I doubt there is any, beyond the source itself. As for what they do, the _MAIN_JOURNAL is a journal for a database. The _MASTER_JOURNAL is a control journal file that is used when a transaction spans more than one database (e.g. using ATTACH). It lives in the same location as the main database journals. I'm pretty sure _SUBJOURNAL is for statements and sub-queries. Some additional info can be found here: http://www.sqlite.org/tempfiles.html In the bigger picture, I assume you know that Hwaci (the company that employs the SQLite developers) offers an encryption extension: http://www.hwaci.com/sw/sqlite/see.html It is a paid product, but the license terms are extremely liberal. -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] 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] "SQLite" Pronunciation
On Wed, Feb 27, 2013 at 06:28:03PM -0500, f...@cetussoft.com scratched on the wall: > I guess, to some extent, it may depend on whether one pronounces (or > mispronounces) "SQL" as "see-kwel" or as "ess-kyoo-ell". As I understand it, the "ess-cue-ell" pronunciation is part of the ISO standard for SQL. > Is there a consensus here? (Yes, I know that pronunciation matters little > on a mailing list.) If you want to be real formal, it does (which means, for a mailing list, I guess it doesn't). When I was working on "Using SQLite" I got into a discussion with my editor about the usage "an SQL statement" vs "a SQL statement." (or "an SQLite database"... it goes on an on). We ended up going with "an SQL..." because it is more correct for the "ess-cue-ell" pronunciation. -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] like query
On Tue, Feb 26, 2013 at 12:34:03PM +, Simon Slavin scratched on the wall: > On 26 Feb 2013, at 7:39am, dd <durga.d...@gmail.com> wrote: > > This database has unicode strings(chinese/japanese/...etc strings). can > > you tell me which is the correct character to replace with z? > > Ah. There you have a problem because internally SQLite does not > handle language support within Unicode characters. I'm going to let > someone with SQLite/Unicode expertise answer this one, but it may be > that with Unicode even your LIKE command would not have worked > properly and you should use something like The only issue there is that the default case-insensitive nature of LIKE won't work. Otherwise LIKE should have no problems with matching unicode strings. -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] Return Value from sqlite3_exec()
On Fri, Feb 22, 2013 at 05:15:15PM -0500, Frederick Wasti scratched on the wall: > So, my question is: Is it correct for sqlite3_exec() to return SQLITE_OK if > the SQL query is doomed to failure (but is otherwise properly formed)? The SQL query did not fail. It did exactly what you asked: updated every single record that met the specified condition. -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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"
On Wed, Feb 20, 2013 at 09:25:54PM +0100, Petite Abeille scratched on the wall: > On Feb 20, 2013, at 2:15 PM, Richard Hipp <d...@sqlite.org> wrote: > > > SQLite automatically adds a LIMIT 1 to a scalar subquery. > > Yeah? that's a bit of a death trap though? would be much more productive > if SQLite would raise an exception instead of doing something covert > and random... Not covert... works as documented: http://www.sqlite.org/lang_expr.html Scalar Subqueries A SELECT statement enclosed in parentheses may appear as a scalar quantity. A SELECT used as a scalar quantity must return a result set with a single column. The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the expression is NULL. The LIMIT of a scalar subquery is always 1. Any other LIMIT value given in the SQL text is ignored. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. Not random either... at least, not any more random than any other query. Result order is never meaningful unless there is an ORDER BY. As for "productive", I suppose that depends on if you want SQL to find poorly thought out queries on behalf of the developer, or just assume the developer knows what they're doing and do the best it can with what it was given. For good or bad, SQL is definitely a "shoot yourself in the foot" language. -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] Strange eviction from Linux page cache
On Thu, Feb 07, 2013 at 04:11:18PM +0100, Eduardo Morras scratched on the wall: > If you need cache being persistent between process on the same server, > you can build a ram disk, write the db there and use it from any > process. This way you read the db only once from nfs. Even better, you > can shutdown nfs because a simple ftp/http server and wget/fetch can > do what you want, serve/receive read only files. It would be more straight forward to just have SQLite create an in-memory database, and then use the backup APIs to copy the central database to the in-memory database. Once that was done, all requests could be serviced out of the in-memory database. In this way, the database would always be in process memory, with no dependencies on either the NFS link (after load) or the file-system cache. It would also require a very minimal number of changes to the process code. -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] Are Foreign Keys indexed?
On Wed, Jan 23, 2013 at 02:12:39PM +0100, Krzysztof scratched on the wall: > Hi, > > I'm reading http://www.sqlite.org/foreignkeys.html but can't find answer. > Are SQLite FK indexed? For example: Not automatically. From that page: 3. Required and Suggested Database Indexes [...] Indices are not required for child key columns but they are almost always beneficial. -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] INSERT OR IGNORE - returning new or existing rowid
On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: > Hi, > > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. Is exists similar > solution which: > 1. If insert success then return new rowid > 2. If insert fail (record exists) then return rowid of existing record > > Can I get this information in one command? No. Mainly because your assumption that one and only one row is responsible for triggering the IGNORE conflict resolution is incorrect. For example, if a table has two or more UNIQUE indexes, the IGNORE resolution may be triggered by different rows through each index. -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] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 04:28:21PM -0500, Richard Hipp scratched on the wall: > On Wed, Dec 12, 2012 at 4:18 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > > Intresting, doesn't sqlite3FkCheck() already take these into account? > > > > Yes, it does. But not every home-brew solution to this problem does. > That's why we really ought to handle this inside of PRAGMA integrity_check, > rather than tossing the problem over the wall to applications developers, > as is done now. The whole point of SQLite is to free up app developers to > focus on their application logic by simplifying the storage and persistence > problem. Checking foreign key constraints really ought to be part of what > SQLite does automatically. I like the idea of SQLite handling this, but I'm not sure PRAGMA integrity_check is the right place. Unlike a corrupt database, this is the type of thing that an application can recover from. In order to do that, however, the application needs pretty specific information on the violations that were found. That means whatever is doing this check is likely to return a table with a full report of what was found, including table names, rowids, etc. While the current PRAGMA integrity_check does return errors, they're mostly text error messages that are designed for human consumption. Adding programmatic information in additional columns strikes me as a pretty significant change to the return value of a pretty important PRAGMA. I can also see situations when someone might want to run one set or the other set of checks. Breaking it out, so that these checks are done by a different PRAGMA (integrity_check_v2 ?) seems like a wise idea. Existing applications won't benefit from a new PRAGMA, but existing apps don't know how to react to any errors that might be found. -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] SQLite :Is there any subtle distinction between UNIQUE and DISTINCT?
On Wed, Dec 12, 2012 at 06:26:54AM -0800, Frank Chang scratched on the wall: > Hello, In the latest version of SQLite, is there any subtle distinction > between UNIQUE and DISTINCT? If there exists a subtle distinction between > UNQUE and DISTINCT in Sqlite, what is the appropriate Insert or replace > syntax, CREATE INDEX usage, and the SELECT statements? Thank you. Yes, there is a difference in how they handle NULLs. UNIQUE tests on "=" (equals), while DISTINCT tests on "IS". Basically UNIQUE considers any two NULLs to be unique, while DISTINCT considers any two NULLs to be identical. In short, if you have a table with UNIQUE constraints (but without a NOT NULL constraint) there may still a valid reason to use SELECT DISTINCT. As for syntax and so forth, the terms are not interchangeable. The docs on the sqlite.org website should explain when you can use one term or the other. -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] Database size bigger than before deleting records
On Sun, Dec 02, 2012 at 09:39:23PM +0100, Henry Huang scratched on the wall: > Good day everyone, > > I had a database file, and I deleted many records (tens of thousands) from > three tables, then, I did a VACUUM to that database. After I checked the > size of the database file, I was a bit surprised that it's even (although > slightly) bigger than before deletions. > > Is that possible? It is not all that unusual for a VACUUM to result in a slightly larger database, especially with a DB that has a fair number of indexes. This is usually not the case when rows have been deleted from the database, however. Not to ask the obvious, but are you sure the rows were actually deleted? Was auto-vacuuming on? -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] What is the easiest way of changing the field type?
On Sun, Dec 02, 2012 at 12:52:33PM -0800, Igor Korot scratched on the wall: > Jay, > > On Sun, Dec 2, 2012 at 12:16 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: > >> Hi, ALL, > >> ALTER TABLE command does not support changing the field type. > >> > >> What is the easiest and fastest way to change the field type from > >> integer to double? > > > > The easy, fast, and dangerous method is to edit sqlite_master directly. > > ;-) > I thought that the word "safest" is explicit. Guess not... Life is short. > So, does this mean that I need to drop the DB in the text file, edit > it and then re-create it from this file? That's the safest, but it might not be the fastest. Without the FK, it would normally be a simple matter of creating the new table, copying the data with a INSERT...SELECT (which should auto adjust the types), then swapping the tables. I'm not sure of the FK will allow you to do that or not. Worth a shot before you dump the whole database... you might need to update both tables at the same time within a transaction. -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] What is the easiest way of changing the field type?
On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: > Hi, ALL, > ALTER TABLE command does not support changing the field type. > > What is the easiest and fastest way to change the field type from > integer to double? The easy, fast, and dangerous method is to edit sqlite_master directly. > I know it is all saved as text No, it isn't. That was true of SQLite 2, but SQLite 3 stores types in their native format. -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] Memory using: is it normal
On Sat, Dec 01, 2012 at 06:00:59AM +0400, Уточкин Александр scratched on the wall: > Could you tell me please if it is normal that memory used by application > goes like this: > > SQLite3_Prepare_v2 - Memory used changes slightly > Binding values - Memory used changes slightly > Loop with sq3lite_step - Memory used growths by 16Mb > sq3lite_finalize - Memory used doesn't reduce The patterns sounds right. SQLite will allocate memory for the page cache as needed, but it does not release it. On most systems the default max cache size is 2000 pages. On most systems the default page size is 1K, but it can be 4K on some Windows systems. That makes the 16MB look a shade big, but it might be about right if you're running on a Windows system, or if you've adjusted the default page size and/or cache size. -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] Foreign keys needing an index
On Fri, Nov 30, 2012 at 02:57:30PM +, Simon Slavin scratched on the wall: > Need someone more familiar with the design philosophy and source code than I > am (which is not at all). > > <http://www.sqlite.org/foreignkeys.html#fk_indexes> > > Says you need an index for anything which acts as a parent. I can > understand why under normal circumstances (large table) this would > be desirable. But is there anything which would /require/ it ? It isn't just an index, it is a UNIQUE index. Parent columns must be unique, so a UNIQUE constraint (and the implicit index that comes with it) or an explicit UNIQUE index is required to enforce the uniqueness of the parent columns. Yes, it also happens to help with performance, but the primary motivation is to make sure FK references are unique. > I'm asking this because I'm in a situation where someone may make a > daughter table with a foreign key relationship and the code will not > know whether the appropriate index already exists. Either I can try > to parse various pieces of information to figure it out, or I can > have a rule that a new index is always created, and accept that this > index may sometimes be a duplicate of one which already exists. In theory, if the database is designed correctly, an FK will never reference something that doesn't already have a UNIQUE constraint on it. One might argue that if you're trying to setup an FK that references a column or set of columns that does not have a UNIQUE constraint, either the FK is broken or the parent table is broken. ...which is not to say a general purpose tool still needs to deal with this, as there are plenty of broken database designs out there. -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] Converting in-memory sqlite database to char array
On Thu, Nov 29, 2012 at 02:39:49PM +, Black, Michael (IS) scratched on the wall: > I thought a backup was using a snapshot and locking the database? No... the source DB remains available. That's largely the point of the API. In fact, the full name is the "Online Backup API." The fact that it can also be used to copy in-memory DBs is more of a side benefit, even if it was a much needed benefit. http://www.sqlite.org/c3ref/backup_finish.html SQLite holds a write transaction open on the destination database file for the duration of the backup operation. The source database is read-locked only while it is being read; it is not locked continuously for the entire backup operation. Thus, the backup may be performed on a live source database without preventing other database connections from reading or writing to the source database while the backup is underway. If the source database is modified by the same connection doing the backup, the page updates are written to both DBs. If the source DB is modified by any other connection, the backup automatically restarts. Because it is easy to imagine a case when the backup gets caught in a restart loop, some people choose to make the backup a more atomic operation by having the backup "step" function copy all the pages in one go. In that case it is likely that the majority of pages are written out in-order, but I wouldn't want to bank on that. -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] Converting in-memory sqlite database to char array
On Thu, Nov 29, 2012 at 02:05:02PM +, Black, Michael (IS) scratched on the wall: > And if you want to improve latency you can use fifo's on Unix or > anonymous pipes on Windows and run a thread to send your data > while it's writing since those methods are synchronous. I would not assume the backup API writes the file front to back, especially if the database is modified while the backup is taking place. A custom VFS that just "writes" the file to a big chunk of memory makes the most sense. -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] creating indexes on empty columns
On Sun, Nov 25, 2012 at 12:41:21PM -0700, Keith Medcalf scratched on the wall: > > On Sunday, 25 November, 2012, 11:58, Jay A. Kreibich wrote: > > > each column is usually undesirable. A given SELECT can usually only > > use one index per query (or sub-query), so it rarely makes sense to > > stack up the indexes... adding unused indexes only slows down > > insert/update/deletes, as well as makes the file size much larger. > > Generally speaking indexes should be treated as a performance trade-off. Ideally they're a performance gain. That's kind of the point. If they're not an overall gain, you likely shouldn't be using them. > When you create an index (other than a UNIQUE index used to enforce a > constraint, or an index on a parent or child key in a foreign-key > relationship, where such an index may greatly increase INSERT or UPDATE > performance) you are "moving" execution time from the retrieval > processing to the maintenance processing of your application. Yes and no. If you view an index as an optimization, then the idea is usually to increase overall performance, so that there is net win. It is true that an index will (hopefully) increase the performance of many queries at the possible cost of additional maintenance processing, but the net change depends on the application's read/write ratios, and the maintenance patterns. In that sense, I'd say the indexes do not move execution *time*, so much as move and adjust execution *cost*. If the application's needs and access patterns are such that the refactored set of costs is lower, the indexes are usually seen as a correct and positive optimization. If the new set of costs results in lower overall performance, the index is seen as a negative thing. There is also nothing special about an automatic UNIQUE index. The costs are the same. The only difference is the motivation for creating it, accepting the write performance cost as the price of enforcing the constraint. Adding a UNIQUE constraint (and the index that goes with it) is not an optimization, but an application requirement. > When you add an index, you are (usually) optimizing retrieval and > query operations. The execution time saved during such query > operations does not disappear (it is not recovered). These processor > cycles and I/O operations are "removed" from retrieval operations > and "spent" when you perform updates to the database to maintain > the indexes. I disagree with this idea, as it implies there is a 1:1 exchange in read performance gains and write performance loss. That is very rarely the case. Even the relative costs of a single read vs a single write are not linear or particularly related. All of these things are highly dependent on the table size, the different indexes, and the operation being performed. It isn't about moving costs from category A to category B, it is about finding efficient work flows that work faster and better. We already know there are some situations when an index will provide significant performance benefits, and other cases when the same index may slow things down. A big part of what the query optimizer must do is identify these situations and pick the best use of the available resources-- in many situations that may be to ignore the index. That's not about shifting execution time, it is about getting rid of it, and picking the fastest path for the situation at hand. The balance is that an index opens up more options to the query optimizer, but there is also an associated cost-- and that cost should only be paid if the index is used from time to time. This balance is true of all operations, not just SELECTs. For example, a good index can make UPDATE and DELETE operations faster, just as it may make SELECTs faster. That kind of blows the theory of moving execution time around, since there can be advantages in both types of operations. Index optimization is in no way a zero-sum game. The bag of water is *not* fixed, and it is just as possible to remove water as it is to add it. > > Additionally, indexing any column (even one used heavily in a query > > filter) is not useful if the index doesn't reduce the working data > > set to ~10% of the rows. In other words, having an index on a column > > that has all one value (be it NULL or anything else) accomplishes > > nothing but significantly slowing down queries that use the index. > > Same is true, for example, of a True/False column with an even > > distribution. Generally, unless the index can be used to eliminate > > the vast majority of rows, it will slow down, rather than speed up, a > > query. > > The exception to this is, of course, where the index created is a > covering index
Re: [sqlite] creating indexes on empty columns
On Sun, Nov 25, 2012 at 01:29:48PM +, Paul Sanderson scratched on the wall: > Yes NULL - > > I underastand that ecvery coumn needs to be read, that is self evident, > however my feeling (not tested) is that the process is much slower than it > needs to be, i..e the process of creating an index on a column whos values > are all NULL takes longer than just reading all of the columns - I have to > admit to not testing/timimg this though, I'll have a go when time permits. An index is essentially a specialized table that uses a different key. Tables and indexes use an almost identical data structure when stored. The main difference is that tables are sorted and keyed via the ROWID column, while indexes are sorted and keyed by one or more indexed columns. Every index has one row for each row in the table. So the question then becomes, if you were creating a one column table and inserted a large number of rows, would you expect a significant difference in the insert time if all the values were the same, or if they were different? > That does lead to another question. Is their a method of creating multiple > indexes at the same time, e.g. create an index on each (or specified) > column in a table in one pass - rather than do each column in turn. This > would save on the overhead of reading the entire table for each column. Scanning the table is not the expensive part of creating an index. Creating a large index requires a significant amount of memory to sort and rebalance the B-Tree used to store the index data. Doing more than one index at a time would make that problem much worse, and would likely be slower, overall, then creating the indexes one at a time. As others have pointed out, it might be worth backing and asking what you're trying to achieve with all these indexes. Putting an index on each column is usually undesirable. A given SELECT can usually only use one index per query (or sub-query), so it rarely makes sense to stack up the indexes... adding unused indexes only slows down insert/update/deletes, as well as makes the file size much larger. Additionally, indexing any column (even one used heavily in a query filter) is not useful if the index doesn't reduce the working data set to ~10% of the rows. In other words, having an index on a column that has all one value (be it NULL or anything else) accomplishes nothing but significantly slowing down queries that use the index. Same is true, for example, of a True/False column with an even distribution. Generally, unless the index can be used to eliminate the vast majority of rows, it will slow down, rather than speed up, a query. Indexes are not magic bullets, and using them properly requires understanding how they work and how they are used. -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] creating indexes on empty columns
On Sun, Nov 25, 2012 at 04:56:44PM +, Simon Slavin scratched on the wall: > > On 25 Nov 2012, at 1:29pm, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > I underastand that ecvery coumn needs to be read, that is self evident, > > however my feeling (not tested) is that the process is much slower than it > > needs to be, i..e the process of creating an index on a column whos values > > are all NULL takes longer than just reading all of the columns - I have to > > admit to not testing/timimg this though, I'll have a go when time permits. > > In SQLite, all columns are in all indexes even if the column contains a > NULL. NULL has a sorting order, and anything that does Rows, Simon, rows... not columns. Watch your terminology or your answers will be more confusing than the questions. -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] in memory to void *data (and back)
On Tue, Nov 13, 2012 at 06:08:17PM +0100, Gaspard Bucher scratched on the wall: > Hi there ! > > I am trying to figure out how to dump an in-memory database to some data > pointer and back (from a void*data chunk to in-memory). The simplest solution > I can figure out right now is: > > A. save to void*data > 1. backup to file > 2. read file ==> void*data > 3. remove file > > B. load from void*data > 1. write to file > 2. open from file > 3. backup to memory > 4. remove file > > Is there anything simpler that avoids the temp files ? Not simpler, but cleaner... write a VFS plugin that reads/writes to a memory block. Use the backup API to go straight in and out of that, rather then a file. -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] how to select "
On Wed, Oct 24, 2012 at 07:27:57AM +, YAN HONG YE scratched on the wall: > sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" >as img,pcs from engine where id>7;" >> n.html > here \"abc.jpg\" couldn't work. SQL string literals use single quotes. -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] VTab & xRename
On Tue, Oct 23, 2012 at 10:16:07PM +0200, gwenn scratched on the wall:
> Hello,
>
> The documentation says the xRename function is mandatory:
> http://sqlite.org/vtab.html#xrename
> "The xRename method is required for every virtual table implementation."
>
> But it seems possible to not specify it:
> static const sqlite3_module fts3aux_module = {
> ...
> 0, /* xRename */
> ...
> };
And when you attempt to rename the table, what happens?
The virtual table interface is advanced, in the sense that there are
very few safety nets or double-checks. It is designed to be used by
an intelligent programmer that knows their stuff. You need to do what
the docs say, exactly, or something bad can happen. That's not to
say something bad will happen right away. The fact that you can assign
a NULL function pointer to the xRename() function only means the system
is not double-checking your work when you pass in the structure... it
does not mean that passing a NULL is allowed. I strongly suspect that
if you do not provide a xRename() function, and someone attempts to
rename the table, the whole application will simply crash. Your fault.
-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] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL
On Mon, Oct 08, 2012 at 03:44:01PM +0200, Clemens Ladisch scratched on the wall: > Marcel Weso?owski wrote: > > Why there's no such functionality in SQLite? > > Because it's not called SQLFat. More to the point, it would be pretty trivial to write your own extension that includes a concat() function. That way you get the function you want and the behavior you want. -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] How much disk space is required to checkpoint?
the correct thing to do is notice that the write operations aren't working, report the error and exit. The open-and-unlink file will always be cleaned up. The normal file will hopefully be cleaned up by the application, assuming it remembers to do so correctly, even when exiting under and error condition. If the application is not so careful and crashes due to filling up /tmp, the open-and-unlink file will just be deleted by the OS and /tmp will have plenty of free space. The normal file will remain, keeping /tmp full, and likely confusing the heck out of the user. The savvy user/admin may be smart enough to look in /tmp, the mundane user will just see their machine get slow, many applications not work, and will likely reboot the machine because something is clearly borked. Clearly Linux/MacOS/Sun sucks. If the OS itself crashes, locks, panics, or loses power while an application is putting things into /tmp, then things get a bit more interesting. In the case of the open-and-unlink file, the file and its contents will be cleaned up when the disks are fsck'ed on reboot. The file will NOT be put into lost+found, since the reference count in the i-node is zero. It will simply be deleted and the space recovered. This is true of files created in /tmp, as well as files created on any other file system. While this is a process that only happens on reboot, it is recovering for a situation that can only happen due to unintentional shut-down. Normal files in /tmp will, in most cases, simply be deleted on reboot. That works for files in /tmp, but not in other places. It should also be noted that since cleaning up unlinked files is part of the mount process, the open-and-unlink files will be removed, and the space recovered, long before any /tmp cleanup script is run. So in every way, the open-and-unlink approach is better for the mundane user, as it always has an equal or better chance of returning the system to a usable state, even for poorly written applications, and even if there is a very long duration between reboots. The only situation when a normal file makes more sense is if something goes wrong, and a developer or administrator trying to debug the issue is trying to figure out why it went wrong. In short, situations when you need evidence left behind in the form of a big temp file that wasn't cleaned up after a crash. If that crash was caused by filling up /tmp or some similar issue, the application should have reported the error. If the crash was caused by some other problem, then the file is just going to be wasted space and may cause issues until it is cleaned up. In all those situations, it makes more sense to blame the application for playing fast and loose with error codes. > >> Is there any chance that the use of this trick can be discontinued ? > > > > This is not a trick, it's a widely used Unix idiom. > > It's widely used outside /tmp. It's widely used inside /tmp for anything that only needs to be seen by the process that created the file. When this technique is used correctly for files of small or moderate size, you (the admin or user) never see it. That's half the point. Just because you've never noticed it doesn't mean it isn't happening. Just because you don't know about it doesn't mean it hasn't been there all along. The standard C I/O library includes the tmpfile() call, which performs the whole process of generating a random temporary file name, opening the file, and then unlinking the file. It returns an anonymous file pointer with no associated file name that does not appear in the file system, and is deleted as soon as the file pointer is closed OR if the application terminates for any reason. It will create the file in the system's default temp space, which is /tmp in the case of UNIX systems. This call is part of the POSIX platform standard, as well as the ISO C-90 standard. I'm sure one could trace its roots back pretty far into the history of C and UNIX. There is a strong history of open-and-unlink being the standard practice for this kind of thing. It is exactly what I would expect SQLite to be doing with it's temp files. -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] Sending SQLite3 .timeout command in Command-Line Mode
On Thu, Sep 27, 2012 at 10:15:14AM -0400, John scratched on the wall: > As you can tell, I don't have much experience with sql. I was going in the > timeout direction because simply resending the command several seconds > after the locked error occurred seemed to return the correct value. My plan > is to implement Michael's suggestion and if the error continues to occur, > place a rollback in an error handler and move on from there. Is that > reasonable or am I still missing something? That sounds fine. The main point I was trying to make is that there are some (rare) situations when a timeout value will not solve every problem, even if the server has very light concurrency needs. There are situations when the handler will still return a SQLITE_BUSY error, and you're only choice is to rollback and start over. The timeout should catch and handle the vast, vast majority of SQLITE_BUSY errors, however. -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] Sending SQLite3 .timeout command in Command-Line Mode
On Thu, Sep 27, 2012 at 09:39:31AM -0400, John scratched on the wall: > Thank you Michael. I can't find anything in the documentation about the > -cmd switch. Will you point me in the right direction? Also, a 2 second > timeout would be .timeout 2000 , right? Do understand that this will not solve every problem. Even with a timeout, there are situations when you can still get a locking error and your only choice is to rollback and try again. -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] How much disk space is required to checkpoint?
On Wed, Sep 26, 2012 at 01:55:33PM +0100, Simon Slavin scratched on the wall: > There are problems with doing this under some versions of Unix/Linux, > and especially variants like Android. This technique is almost as old as Unix itself. If some OS versions can't deal with it, they're buggy. I would not consider it a "trick", but rather a standard best-practice for dealing with temp files. > For instance, another user/app can make a file with the same name. And how, exactly, would keeping the file around fix this? If the file is properly unlinked, it doesn't exist in the directory. It has no name, so it is impossible to create another file with the "same" name. Besides, if the you kept the file around, you'd have the exact same problem. There are also APIs to get unique temp file names. If an application isn't using them, its buggy. SQLite also uses the string "etilqs" in temp files to avoid collisions. > Or the system may crash while the file is still open. That's why file systems fsck when they remount. There are all kinds of things that can go wrong with a file system when a machine crashes. An application crash is no big deal, however. > I would believe that any Unix user who knows to look in /tmp could > deal with what they found there. No offense to the professional system administrators out there, but I think you're vastly over estimating the average sys-admin, especially when they're a teenager with a new MacBook and just enough knowledge of Unix to be dangerous. Richard made a post about it some months ago. SQLite is embedded in a lot of applications. Some of them are buggy, crashy applications. The reason SQLite uses "etilqs" rather than "sqlite" in the temp file names (as it used to) is because of such clueless people using bad software find the temp files and then go off ranting about how SQLite sucks and needs to be fixed. Flipping the name around weeds out enough Google searches to avoid such people. Of course, emails like this, that use the string, don't help the situation. PLEASE NOTE: if you found this message via a Google search at some future date, please re-read the previous paragraph until you understand the full ramifications of what it is saying. Don't be clueless. > I would say that the file should be deleted normally when SQLite is > finished with it rather than this trick being used. > > Of course, there may be a specific reason why the programmers of > SQLite decided to do this. Because it is the standard, time-tested way of doing this kind of thing on Unix-- for a lot of very good reasons. The file cannot be open by another process, period. Even a root process. So it provides security and isolation from stupid programs doing dumb things. It avoids file name collisions, as the file doesn't exist in the file tree. Deleting the file means that as soon as that SQLite process exits-- no matter how or why-- the file will be cleaned up and removed. That's important for files put outside of /tmp. It can be important for files inside /tmp... most systems only clean /tmp on reboot, and that can be months, if not years, on many Unix systems. Some don't clean /tmp at all. The "create and unlink" pattern is so common, many UNIX systems have a tmpfile() or similar library call to do the whole thing... create a unique file in /tmp, open it, unlink it, and return the file pointer. -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] Unofficial poll
On Sun, Sep 23, 2012 at 09:25:06PM +0400, Yuriy Kaminskiy scratched on the wall: > Jim Dodgen wrote: > > I program mostly on Perl on Linux and it is a beautiful fit. Example > > is I can have a date field with a POSIX time value (or offset) in it > > or another date related value like "unknown" > > Very bad example. Standard SQL NULL is much better fit for "unknown". > Besides, perl at least have "use strict;" and "use warnings;", sqlite does > not. Yet SQLite's types are often more clearly defined than Perl's. Every SQLite value has a specific, known type that will tell you exactly how the bits are stored. The only difference with SQLite is that columns are allowed to have mixed types. Don't confuse this with a loosely typed language, however... again: every SQLite value has a specific and known type. From a formal mathematical sense, a relational NULL is considered a "value-less type." That is, it is treated as a specific data type that's value domain is the null-set. So, if you want to get real formal, all relational databases allow multiple types (at least two) to be assigned to a row attribute. I know that sounds contrived, but when you start to look at NULL handling in SQL in this way, it suddenly makes a lot more sense. And it means that all SQL databases already deal with disjoint types within a column. If formal theory isn't your way thing, I'd point out that "traditional" database do all kinds of automatic type conversions. When you input a date in MySQL, you do so as a string. When you get a date or duration value back, it is usually as a string. If you compare a date column to a literal string (that, one assumes, represents a date) the database will do its best to covert that string to something that makes sense before doing the comparison. Similar things can be said of different numeric types... "WHERE floatCol < 3" will do automatic conversions and get on with it. The typical database has all kinds of automatic rules about dealing with different types involved in the same operation. SQLite has all these rules as well... and they're all clearly defined, and they all work pretty much the same way. The fact that a column is only loosely typed really doesn't come into play in a significant way, except that the conversion rules for a comparison may come up in a JOIN, while other databases would typically only see a converted comparison in a WHERE. The end result is not mass chaos but, rather, rarely a surprise. SQLite does a lot of type conversion-- just like every other database out there-- to deal with disjoint types. Those conversion rules are well documented and make sense. I'm a bit of purest, and when I first started using SQLite eight years ago, I was also a bit off-put by what I saw as "fast and loose" typing. Over many years of using SQLite for all kinds of things, I can say that this has never been an issue. It has never surprised me, it has never caused problems-- and it occasionally has been darn handy. And finally, for anyone that really wants strong typing, that's easy enough to do. Just add a check constraint to your column defs: CREATE TABLE t ( i integer CHECK ( typeof( i ) == 'integer' ), t text CHECK ( typeof( t ) == 'text' ), r float CHECK ( typeof( r ) == 'real' ), b blob CHECK ( typeof( b ) == 'blob' ) ); -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] Unofficial poll
On Sun, Sep 23, 2012 at 01:37:59PM +0300, Baruch Burstein scratched on the wall: > I am curious about the usefulness of sqlite's "unique" type handling, and > so would like to know if anyone has ever actually found any practical use > for it/used it in some project? I am referring to the typeless handling, > e.g. storing strings in integer columns etc., not to the non-truncating > system e.g. storing any size number or any length string (which is > obviously very useful in many cases). > Has anyone ever actually taken advantage of this feature? In what case? Yes. Several years ago I had a large read-only database I needed to fit onto a flash card. It mostly consisted of strings that were anywhere from a few dozen bytes to a few hundred K. I wrote something that attempted to compress the strings. If the compressed block was smaller than the original string (as was usually the case for the longer strings) the compressed string was stored as a BLOB. If there was no compression savings (which was not uncommon with the smaller strings) the string was simply stored as the string. With the addition of a VIEW and a decode function that basically did, "if this is a string, return it; if this is a blob, uncompress it and return the string", I was all set. I've done a number of similar things in other projects. Is it critical? No. I'm sure each of us could come up with a half-dozen ways to do this kind of thing in a traditional database. Did it make my life easier, the code simpler, and the database smaller and more compact? Heck, yes. -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] Virtual tables are driving me insane!
On Fri, Sep 21, 2012 at 11:44:09PM +0200, Jörgen Hägglund scratched on the wall: > I tried creating a super simple test which just return "default" > values on all calls. > This is the order my functions are being called: > xCreate (enter function) > xCreate (exit function) > xConnect (enter function) > xConnect (exit function) > At this point I get an access violation in SQLite3.dll (reading of > address 0008) > I have tried everything I can think of, but this is how far I get. Did you allocate a sqlite3_vtab structure and pass it back? You also need to set *errMsg and (*vtab)->zErrMsg to NULL. > I then tried a simple test in VS2010 where I include sqlite3.h (that > is, no DLL). > Using the same calls/queries as in Delphi, I get this order of calls: > xConnect (enter function) > xConnect (exit function) > xDisconnect (enter function) > xDisconnect (exit function) > xConnect (enter function) > xConnect (exit function) > xCommit (enter function) > xCommit (exit function) > xBestIndex (enter function) > xBestIndex (exit function) > And here the application crashes sending Windows to search for a solution. That's really odd since, xBestIndex is one of the functions that can do absolutly nothing but return, and everything should still work. > The SQL statement used to create my virtual table, in both cases, is: > CREATE TABLE x (Severity TEXT, Source TEXT, IP TEXT, Message TEXT) > > And, finally, both cases are run against a newly created database. > > Does anyone have any ideas to what I'm doing wrong? Virtual tables are powerful and advanced. There are no safe-guards or double-checks in the code, as there are with some of the more basic interfaces. SQLite expects your code to be perfect, and if it isn't, it will likely crash. Make sure you read the docs very, very carefully and do *exactly* what they say. Make no assumptions. > Anyone having some source of how to implement a really simple > virtual table (in any language)? There is a fairly large chapter in "Using SQLite" that attempts to cover virtual tables in some detail. It also goes through two full examples. Even if you don't want to buy the book, you can download the example code here: http://shop.oreilly.com/product/9780596521196.do Just use the "Download Example Code" link on the right side of the page. Have a look at the examples from chapter 10. > I am aware of that I am uncertain of how to implement xBestIndex, > but that is my next headache, I guess... :) Yeah, xBestIndex takes a bit to wrap your head around, but don't worry about it too much. Unless you're writing a VT that provides a specialized index, you can usually just ignore it and get the basic VT working with table scans before you worry about making the VT index aware. A lot of the VT modules I write don't use xBestIndex. -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] Count(*) help
On Mon, Sep 17, 2012 at 06:03:12PM -0600, Keith Medcalf scratched on the wall: > > There it is again. What does length(NULL) mean ? Should it be NULL because > > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > > negative integer representing how long the NULL value is ? I may have > > missed > > it, but I don't think the answer is in SQL92. The only solution is not to > > depend on any particular behaviour. > > The result of any operation involving NULL other than IS NULL and > IS NOT NULL must be NULL. Not true. There are formal rules for three-valued logic (or 3VL) that every database should follow. In 3VL, NULL is treated as "unknown", but there are cases when an operator can take a NULL argument, but still produce a known result. For example, in SQL "NULL OR 1" is 1 (true) and "NULL AND 0" is 0 (false). Arguments about the semantic details of Relational algebra aside, if you treat NULL as "unknown", most of the database operators and functions make sense. http://en.wikipedia.org/wiki/Three-valued_logic -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] WAL, synchronous=1 and durability
On Mon, Sep 10, 2012 at 09:50:58PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > > On 11 Sep 2012, at 12:55am, Keith Chew <keith.c...@gmail.com> wrote: > > > > and I know FULL (1) will provide that. The question is why > > > NORMAL (1) cannot provide the same. > > > > Because NORMAL doesn't flush changes to disk after every single > > transaction. It queues a few of them up and flushes them all in one go. > > That's not quite true. Oh wait, you're talking about WAL mode, aren't you? Sorry, apparently I wasn't following the whole thread as closely as I should have been. In that case, yes... the WAL file will "save up" several transactions and only fully sync them when the checkpoint happens to clear the WAL file. > > NORMAL) Faster but if you lose power more transactions are lost, but > > the database is still not corrupt. > > FULL) Every COMMIT will take more time, but a power-failure can > > lose a maximum of one transaction. > > That's also incorrect. In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full durability. -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] WAL, synchronous=1 and durability
On Tue, Sep 11, 2012 at 03:11:57PM +1200, Keith Chew scratched on the wall: > Hi Jay > > > http://www.sqlite.org/pragma.html#pragma_synchronous > > > > When synchronous is FULL (2), the SQLite database engine will use > > the xSync method of the VFS to ensure that all content is safely > > written to the disk surface prior to continuing. This ensures > > that an operating system crash or power failure will not corrupt > > the database. FULL synchronous is very safe, but it is also > > slower. When synchronous is NORMAL (1), the SQLite database > > engine will still sync at the most critical moments, but less > > often than in FULL mode. There is a very small (though non-zero) > > chance that a power failure at just the wrong time could corrupt > > the database in NORMAL mode. But in practice, you are more likely > > to suffer a catastrophic disk failure or some other unrecoverable > > hardware fault. > > > > >From what I have read so far, my understanding is consistent with your > explanation (except that I didn't realise corruption can happen in > NORMAL, only lost of data). In the case of both NORMAL and FULL (in the case of non-WAL mode), if the commit is successful, there should never be data loss and the changes described by the transaction should become part of the database state. If the commit did not finish, the changes described in the transaction were never part of the database state to start with and are not considered "lost". In no case should NORMAL or FULL allow a commit to succeed, but not have the transaction be durable. ...except if the disks lie. Which, as Simon has pointed out, is most of the time. > Regardless, I would really like to hear > from a developer that the above paragraph also applies to the WAL > journal mode, and not just the older journal modes, since WAL was > introduced later in 3.7 onwards. It does not. The transaction model in WAL mode is totally different, so the journal modes are as well. > Because of the architecture change in WAL, I was hoping that the > durability can still be preserved while using NORMAL. WAL plays by a slightly different set of rules. The docs describe this fairly well, but from the sound of it you need FULL for durability. On the other hand, WAL requires fewer write to commit a transaction, so (if I'm reading this correctly) FULL in WAL mode is much faster than FULL in non-WAL mode. -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] WAL, synchronous=1 and durability
On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > On 11 Sep 2012, at 12:55am, Keith Chew <keith.c...@gmail.com> wrote: > > and I know FULL (1) will provide that. The question is why > > NORMAL (1) cannot provide the same. > > Because NORMAL doesn't flush changes to disk after every single > transaction. It queues a few of them up and flushes them all in one go. That's not quite true. Committing a transaction takes more than one disk write. As I understand it, in FULL mode, the disk is sync'ed after each and every write. In NORMAL mode, all of the writes required to commit a transaction are made in quick succession, but the disk is only sync'ed after the last write. In most practical situations, my guess is that the sync takes longer than the writes (since the writes are mostly to the OS file-system buffers anyways). That means, for a very, very short time during the final commit process (microseconds, most likely), there are pending buffered writes. If the system were to lose power between one of these writes and the final sync, there is a very-small-but-non-zero chance the database could become corrupt. But the disk is still fully sync'ed, to the best of SQLite's ability, after each and every commit. In NORMAL mode, commits are still fully durable. > NORMAL) Faster but if you lose power more transactions are lost, but > the database is still not corrupt. > FULL) Every COMMIT will take more time, but a power-failure can > lose a maximum of one transaction. That's also incorrect. In both cases, if a transaction fully commits, you're golden. Transactions are fully durable. If COMMIT returned success, you should not lose the transaction. FULL mode, to the best of the SQLite developers' ability, protects against all corruption, but at a very high performance cost. NORMAL mode opens up an extremely small chance of corruption for a significant performance increase. The PRAGMA docs explain much of this: http://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. -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] some questions about sqlite
On Sat, Sep 01, 2012 at 11:56:33PM -0700, J Decker scratched on the wall: > On Sat, Sep 1, 2012 at 8:24 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > On Sat, Sep 01, 2012 at 07:37:04PM -0700, J Decker scratched on the wall: > >> On Sat, Sep 1, 2012 at 7:32 PM, Simon Slavin <slav...@bigfraud.org> wrote: > >> > On 2 Sep 2012, at 3:13am, shuif...@mail.ustc.edu.cn wrote: > > > >> >> 2??how many user connections the sqlite can handler > >> > > >> > There is no limit in SQLite for connections. > >> > >> It is not advised to have multiple processes connect to same file. .. > >> but in-process probably no limit of connections. > > > > I'm not sure where this idea comes from, as it is perfectly > > acceptable to have multiple processes accessing the same database > > file. > > > > SQLite makes very little distinction between different connections > > from the same process and different connections from different > > processes, so there isn't any significant limit that applies to one > > situation that doesn't apply to the other. > > > > Concurrency is always an issue, and you don't want hundreds of > > connections banging on the same file, but that's true no matter if > > the connections come from the same process or not. > the point would be 'and not have concurrancy issues that I'd have to > solve myself...' SQLite uses file locks to control concurrent access between all the connections. This is true if the connections come from the same process or different processes. Accessing the same SQLite file from multiple connections/threads/processes is perfectly safe. About the only time you run into concurrent access issues is if you attempt to access an SQLite database on a network-based file system. Most network-based file systems don't do locks correctly (and definitely don't do distributed locks correctly), so there can be issues. This case is well known and well documented and can be considered a bug in the network filesystem implementations. -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] some questions about sqlite
On Sat, Sep 01, 2012 at 07:37:04PM -0700, J Decker scratched on the wall: > On Sat, Sep 1, 2012 at 7:32 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 2 Sep 2012, at 3:13am, shuif...@mail.ustc.edu.cn wrote: > >> 2??how many user connections the sqlite can handler > > > > There is no limit in SQLite for connections. > > It is not advised to have multiple processes connect to same file. .. > but in-process probably no limit of connections. I'm not sure where this idea comes from, as it is perfectly acceptable to have multiple processes accessing the same database file. SQLite makes very little distinction between different connections from the same process and different connections from different processes, so there isn't any significant limit that applies to one situation that doesn't apply to the other. Concurrency is always an issue, and you don't want hundreds of connections banging on the same file, but that's true no matter if the connections come from the same process or not. -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] Any plans to provide the sqlite3RunParser in the API
On Mon, Aug 13, 2012 at 03:43:46PM +0200, Bishwa Shrestha scratched on the wall: > Hi, > > I've recently started using sqlite3 c-library. I'm using the > in-memory databases which are pretty fast since I'm loading data > dynamically into sqlite3 from a different backend (had to do this as > we wanted to provide an SQL interface to our users). If you want to provide an SQL interface to an existing data source, I would strongly recommend you look at Virtual Tables. They look and smell like normal tables, but all of their access and data generation is done via application level code. You can write a series of functions that essentially slip in between the SQL engine and the data access layer, and can redirect requests for table data to an external source. Virtual Tables can be read-write or read-only. Read-only tables are pretty fast to implement, while read-write tables would allow external applications to modify the backend (no idea if that is desirable or not in your case). Virtual Tables are an extremely powerful and often overlooked part of SQLite. You can do some pretty amazing and powerful things with them, as evident by the Full Text Search engine in SQLite, as well as the R-Trees extension, both of which are built on top of Virtual Tables. SQLite docs: http://www.sqlite.org/vtab.html http://www.sqlite.org/c3ref/create_module.html Virtual Tables are also covered fairly well in "Using SQLite": http://shop.oreilly.com/product/9780596521196.do -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] SQLITE_STATIC and temporary data
On Wed, Aug 01, 2012 at 04:48:48PM +, Rob Richardson scratched on the wall: > Is "acceptable" good enough? I admit I haven't played with this function > (actually, I never heard of it until today), but from what I read in the > documentation, the case described looked dangerous to me. It's somewhat undefined, and I suspect that's intentional. There are a lot of specifics that are undocumented because they change from version to version of SQLite. > SQLITE_STATIC > seemed to me to imply that the contents of the memory used by the sqlite > statement would never change over the life of the statement. It's more the lifetime of the binding. Which reminds me (and I see others on the list already pointed this out), freeing the memory after a call to _reset() would be a Bad Idea since the binding is still in effect. It would have to be _finalize() or _clear_bindings(). But yes... the key is that the memory remains valid for the lifetime of the binding, not the fact that is or isn't statically allocated. -j > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich > Sent: Wednesday, August 01, 2012 12:16 PM > To: General Discussion of SQLite Database > Cc: c...@iki.fi > Subject: Re: [sqlite] SQLITE_STATIC and temporary data > > On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall: > > Dear Friends, > > > > So SQLITE_STATIC is meant to be used for data that is static. However, > > would it still be safe when it is used with data that expires after > > the > > sqlite3_step() function? > > > > For example: > > > > string hello = "hello world"; > > > > sqlite3_bind(statement, 1, hello.c_str(), hello.size(), > > SQLITE_STATIC); > > > > sqlite3_step(statement); > > > > hello = "moo"; > > > > Would there be anything that can potentially go wrong? I mean, since > > SQLITE_STATIC is meant to imply static stuff, would sqlite cache the > > value in such a way that subsequence SELECT_ statements actually use > > this static value that was passed in through the bind function? > > It would be better to keep the value valid until _reset() or > _finalize() is called, but, yes... this use of SQLITE_STATIC is > acceptable (and somewhat common). > >-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] SQLITE_STATIC and temporary data
On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall: > Dear Friends, > > So SQLITE_STATIC is meant to be used for data that is static. However, > would it still be safe when it is used with data that expires after the > sqlite3_step() function? > > For example: > > string hello = "hello world"; > > sqlite3_bind(statement, 1, hello.c_str(), hello.size(), SQLITE_STATIC); > > sqlite3_step(statement); > > hello = "moo"; > > Would there be anything that can potentially go wrong? I mean, since > SQLITE_STATIC is meant to imply static stuff, would sqlite cache the value > in such a way that subsequence SELECT_ statements actually use this static > value that was passed in through the bind function? It would be better to keep the value valid until _reset() or _finalize() is called, but, yes... this use of SQLITE_STATIC is acceptable (and somewhat common). -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] C++ - Finalizing my SQLite interface
On Fri, Jul 27, 2012 at 03:42:57PM +0100, Simon Davies scratched on the wall: > On 27 July 2012 15:22, Arbol One <arbol...@gmail.com> wrote: > > Before calling the destructor, I would like to make sure that all the > > sqlite3_stmt have been 'finalized', is there a function in SQLite that that > > can help me do this, or should I just use 'NULL'? > > The documentation is there to help you... > http://www.sqlite.org/c3ref/next_stmt.html > http://www.sqlite.org/c3ref/finalize.html That's what you want. Of course, finding an unfinalized statement is really something of an error condition. It means your application lost track of something, and failed to free a dependent resource. Having your object blindly finalize statements is very likely to leave a dangling pointer elsewhere in the application. -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] select speed
On Thu, Jul 19, 2012 at 01:35:23PM +0100, Simon Slavin scratched on the wall: > > On 19 Jul 2012, at 11:54am, Live Happy <livehap...@gmail.com> wrote: > > > wish one is faster to make select from table with 20 column and alot of > > records or to divide data in more than table and made join select > > Single table with 20 columns. Unless your data is very unusual. However, insert/updates/deletes are likely to be faster on the smaller tables. Worry about design first, then optimize for speed. "Normalize 'till it hurts, denormalize until it works." -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] (no subject)
On Wed, Jul 18, 2012 at 02:10:52PM -0700, Roger Binns scratched on the wall: > On 18/07/12 13:00, Richard Hipp wrote: > > We have taken to requiring moderator approval before accepting new > > members onto this mailing list. > A better approach as used elsewhere (eg Google & Yahoo groups) is that the > first ~10 messages from someone are moderated. (The moderator can put > that person on the approved/banned list early.) Except that adds about 10x the workload for the moderators. I'd rather have the SQLite team working on code, even if it means suffering the occasional spam message. -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] Virtual Tables: xSync without xBegin
On Thu, Jul 12, 2012 at 03:05:39PM +0200, OBones scratched on the wall: > Hello again. > > Does anyone have any suggestion here? There is a footnote in "Using SQLite" (Chapter 10: "Virtual Tables and Modules," p242) on this. The text is: If you do need to support your own transactions, it is important to keep the program flow in mind. xBegin() will always be the first function called.** Typically, there will be calls to xUpdate() followed by a two-step sequence of calls to xSync() and xCommit(). [...] ** In theory. Currently, calls are made directly to xSync() and xCommit()following the call to xCreate(). It isn't clear if this is considered a bug or not, so this behavior may change in future versions of SQLite. "Using SQLite" was written before 3.7 was released, so this isn't a new thing. Overall, I'd call this a documentation bug, as the behavior makes sense to me. The system can't call xBegin() before xCreate(), yet creating the table is likely to be a transaction-based operation (just like xUpdate()). Calling xBegin() after doesn't make sense either. I'd try to work around it in a way that will still work correctly if it is changed. A simple "in transaction" flag would allow these calls to short-cut out. "Using SQLite": http://shop.oreilly.com/product/9780596521196.do -j > >I'm pushing my experiment with virtual tables a bit further by > >trying out the transaction functions. > >As a result, I gave values for the xBegin, xSync, xCommit and > >xRollback members of my sqlite3_module structure. > >Then after having registered the module, I sent those two statements: > > > >CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER); > >INSERT INTO SomeTable VALUES (50); > > > >via appropriate calls to sqlite3_exec > >However, this fails quite badly because the xSync function is > >called outside any transaction that would have been started by a > >call to xBegin. > >Basically, xBegin is never called in my simple test, despite the > >documentation saying that this should not happen. > > > >What have I done wrong here? > > > >Any suggestion is most welcome > > > >Regards > >Olivier > >___ > >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 -- 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] Multiple connections to in-memory database
On Thu, Jul 05, 2012 at 09:03:54AM -0400, Pavel Ivanov scratched on the wall: > So this feature shouldn't work for you. From my first message: > > > But this possibility was > > introduced in SQLite 3.7.13. So your asp.net provider should be > > compiled with the latest version of SQLite, otherwise it won't work. Also, not to state the obvious, but you can only share a :memory: database across connections that originate from the same process. -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] When to call VACUUM - revisited
On Thu, Jul 05, 2012 at 01:29:18PM +0100, Simon Slavin scratched on the wall: > > On 5 Jul 2012, at 9:34am, _ph_ <hauptma...@yahoo.com> wrote: > > > I already read your previous replies, but to revisit my scenaro: > > > > - My OS is "sensitive to fragmentation" > > - We are running with auto-vacuum enabled, so the freelist_count is usually > > small (not a good indicator) > > Ah. If you're always running auto-vacuum, then I don't think > explicitly issuing VACUUM is going to be useful at all. Don't bother. Yes, bother. Auto-vacuum only deals with free pages. It does not do all the other things a normal vacuum does. Even if you run auto-vacuum, it is still a good idea to vacuum a very dynamic database from time to time. Auto vacuum tends to *increase* the amount of fragmentation within the file, since it moves pages around to keep the free list short. This means that pages for a given object (table, index, etc.) may be spread out across the SQLite file, which can cause extra seeks during table/index scans. OS level (filesystem) defragmentation won't help with this. http://www.sqlite.org/pragma.html#pragma_auto_vacuum Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. On the other hand, if the internal structure of the SQLite file is badly fragmented, having the file be fragmented in the filesystem isn't such a big deal. You only take the hit once. > > but fragmentation supposedly gets worse > > Fragmentation of the database file on disk is something that SQLite > can't control, so you are down to the various defragmentation > facilities (including the one built into Windows) to solve that. You can also get fragmentation inside the SQLite file, in the way that the pages are used. VACUUM is the only way to fix this, since the defrag process has to do with moving SQLite pages around inside the SQLite file. > > -We use sqlite as application data format, a typical user has dozens > > of files. > > This makes "During a support call" is not an option Yes and no. If there is some hidden menu feature to force a VACUUM, that might come in handy if you have a customer with a particularly large (or slow) file. It is easy to put in "just in case", and doesn't change the customer experience if it isn't in their face. If you're using databases as application files, I'm assuming they're not all that huge, however. If the files are moderately small (a few dozen megs or less) you might just vacuum the file every time you open it (if file updates tend to be very dynamic) or every 20th time or something. A file that's only a few megs only takes a few seconds to VACUUM. You can put up a dialog that says "Optimizing file structure...". On the other hand, a file that's only a few megs is not likely to see much of a performance boost from a VACUUM. I'd be more concerned about filesystem fragmentation than I would be about SQLite fragmentation. > You could use the shell tool to turn the database file into SQL commands, > and then back into a new database file on disk. This will both > defragment the file, and make sure it's not using unneeded space. For all intents and purposes, this is what VACUUM does. -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] Standalone LUA regex that can be used in sqlite
On Wed, Jul 04, 2012 at 11:00:55AM +0200, Domingo Alvarez Duarte scratched on the wall: > Hello ! > > I did a modification to the LUA regex code to allow using it without > LUA dependency so it can be used with sqlite as regex function. Very handy! > It's very light and small thus a good candidate to be included on sqlite. It's a very useful thing to have around, but understand it is extremely unlikely that the code will ever be part of the SQLite code base. The difference in licenses makes this almost impossible. > If you are interested on it you can download it here > http://code.google.com/p/lua-regex-standalone/ , it's released under > the same licence of LUA (MIT license). I would suggest making the sqlite3-lua-regex.c file a full-blown extension. If you're careful about how you build the file, the code can be compiled as a static library, or as a dynamic extension. For examples on how to do this, have a look at the chapter 9 examples from "Using SQLite." The code can be downloaded here: http://examples.oreilly.com/9780596521196/ There are also several extensions here, of varying quality: http://www.sqlite.org/contrib/ Also see: sqlite3_auto_extension() http://www.sqlite.org/c3ref/auto_extension.html sqlite3_load_extension() http://www.sqlite.org/c3ref/load_extension.html -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] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 08:29:33AM -0500, Jay A. Kreibich scratched on the wall: > On Wed, Jul 04, 2012 at 01:09:01AM -0500, Nico Williams scratched on the wall: > > > But this would > > just be a glorified (if safer) variant of sqlite3_mprintf() -- for > > apps that allow users to manipulate the schema this could actually be > > a good improvement. > > The sqlite3_*printf() family supports the %w option specifically > for the safe formatting identifiers. ...and someone just pointed out that %w isn't documented on the SQLite site: http://sqlite.org/c3ref/mprintf.html Sorry about that. I'm not sure if that's an oversight in the docs, or if it is an undocumented feature. See "Using SQLite" <http://shop.oreilly.com/product/9780596521196.do>, Apdx G, p474-475 for more info. It seems these pages are included in Amazon's "Look Inside" feature (at least for me): <http://www.amazon.com/Using-SQLite-Jay-A-Kreibich/dp/0596521189/>. Or see the SQLite source. -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] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 09:06:16AM -0400, Igor Tandetnik scratched on the wall: > Nico Williams <n...@cryptonector.com> wrote: > > SQLite3 also needs to know the identifiers of schema elements at > > statement prep time. It might be nice to have a variant of > > sqlite3_prepare_v2() that takes a varargs list of parameters which > > must be identifiers, and then have a syntax for referring to > > identifier parameters as opposed to value parameters. > > That doen't make much sense. Yes it does. The identifiers would be baked into the statement before it is prepared. He's not trying to alter the identifiers after the statement is prepared, he's trying to prevent SQL-injection attacks while the statement is prepared. The #1 rule to prevent SQL-injection attacks is, "Never let user-generated strings pass through the SQL parser". Statement re-use is nice, but the real value in using SQL statements and parameters is that you avoid passing data values through the SQL parser, making SQL-injection attacks impossible. 99% of SQL-injection attacks could be avoided if the programmer had only used SQL parameters rather than string concatenation. It's also why sqlite3_exec() should really include a vararg option, so that one could pass in values outside the SQL string itself. Unfortunately, you can't use parameters for everything. If you get in a situation where you need to use a user-defined table name, parameters won't help you. The (distant) #2 rule to prevent SQL-injection attacks is, "Sanitize user-generated strings before they pass through the SQL parser." The issue with that is that too many programmers think themselves clever and smart, so they write their own sanitizer, and they do so poorly. Hence the popularity of "tried, true, and tested" string sanitizers being built into SQL libraries. That's what Nico is looking for. Thankfully, SQLite includes this functionality, just not in the _prepare() functions. SQLite supports several extensions to the standard printf() syntax in the sqlite3_*printf() family of functions. Both %q and %Q can be used for values, while %w can be used for identifiers. The sqlite3_*printf() functions will properly quote and sanitize any value in the generated string. There is also a %z and %p, but they're not really important for this discussion. -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] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 01:09:01AM -0500, Nico Williams scratched on the wall: > But this would > just be a glorified (if safer) variant of sqlite3_mprintf() -- for > apps that allow users to manipulate the schema this could actually be > a good improvement. The sqlite3_*printf() family supports the %w option specifically for the safe formatting identifiers. Like the %q option, you need to include the quotes in your string literal. So your modified prepare would look something like this: sql_str = sqlite3_mprintf( "DROP TABLE \"%w\"", table_name ); sqlite3_prepare_v2( db, sql_str, -1, , NULL ); sqlite3_free( sql_str ); -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] Bug in sqlite3_step
On Tue, Jul 03, 2012 at 04:26:42PM -0400, Richard Hipp scratched on the wall: > Actually, you can bind on a DDL statement, but bindings are only valid for > the lifetime of the statement itself, not for the whole lifetime of the > object created by the CREATE statement. Is that a side-effect of the fact that CREATE statements are copied into the sqlite_master table as literals, and not re-written? (Is that even true?) -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] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 03, 2012 at 03:21:57PM +0200, Paul van Helden scratched on the wall: > On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) > <michael.bla...@ngc.com>wrote: > > > And Oracle says the opposite: > > > > Yet they all give the same answer when done with "update testtable set > > testrow=null where testrow not null; > > You keep hammering this one, it is obvious, I understand, THANKS! Then why do you keep hammering on the idea that SQLite is somehow incorrect or wrong? You've explained what you're trying to do. We've explained there is a better way to do that, that also happens to provide the correct answer on all platforms, AND likely runs faster-- especially if any of those columns has an index on them. > What if the SET and WHERE contain many columns? Then you're asking for a more complex operation. Your SQL gets a bit more complex as well. > Now I have to add a "WHERE column<>mynewval" for every column in SET > to get the actual changes, something like UPDATE testtable SET col1=?1, > col2=?2, col3=? WHERE complex where clause > AND col1<>?1 AND col2<>?2 AND col3<>?3. > (passing a null parameter to the above won't even work!) Well, no, it won't, because you're using the wrong operator. Use "WHERE col1 IS NOT ?1 AND..." and it all works fine. > No surprises there. Oracle has never managed to impress me. I know what you mean. That MySQL database they make is difficult to take seriously. -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] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the wall: > > The statement "UPDATE table SET column=NULL" updates every row in the > > table. The fact that some rows may already have a NULL in that > > column is not important. > > > > Well, it is important to me, the word "change" means before != after :-) You can argue about the naming of the _change() function all you want. It is a non-standard extension and the function operates as documented. If you want to call it poorly named, go ahead. That doesn't change what it does. There is, however, little argument that the trigger is doing exactly what one would expect. You are applying an update operation to every row, and the trigger is firing for every row. -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] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 03, 2012 at 01:32:14PM +0200, Paul van Helden scratched on the wall: > Hi, > > Is this correct? Should update triggers not only fire for actual changes? I > have a large table with a column which contains all NULL values except for > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, > except it fires for every row. The statement "UPDATE table SET column=NULL" updates every row in the table. The fact that some rows may already have a NULL in that column is not important. If you only want the trigger to fire for non-NULL rows, you need to update only the non-NULL rows: UPDATE table SET column=NULL WHERE column IS NOT NULL; As for sqlite3_changes() returning 0, that doesn't sound right unless you're checking inside the trigger. -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] max size of a TEXT field
On Mon, Jul 02, 2012 at 10:02:29PM +0200, deltagam...@gmx.net scratched on the wall: > Hello, > > I couldnt find in the documentation what the max size of TEXT > field/column is. First item: http://sqlite.org/limits.html#max_length By default, 10^9 bytes (~1GB). Can be altered, but there is a hard limit of (2^31 - 1), or 2GB. -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] Consequences of lexicographic sorting of keys in SQLite4?
On Mon, Jul 02, 2012 at 10:13:13AM -0500, Nico Williams scratched on the wall: > That reminds me: it'd be nice to have a bit string type, since the > correct way to sort IPv4 CIDR blocks is as bit strings. This is also > a proper way to sort IPv6 blocks. Alternatively, it'd be nice to have > native IP address types in SQLite4, as otherwise one has to jump > through hoops to handle IP addresses properly. I'd suggest something bigger, as long as we're putting a lot of options on the table. Postgres supports user-defined types on the server side. This is now most of their slightly esoteric types (such as CIDR addresses) are supported internally. To define a type, the server developer writes a server-side plugin that provides a few functions to the server. Required functions convert the in-memory representation of the type to/from strings (for SQL input/output) and also convert the in-memory representation to/from a bit stream for storing on disk. I think you can also provide a sort function. http://www.postgresql.org/docs/9.0/static/xtypes.html The idea of using a plugin system to expand database functionality seems to fit well with the SQLite way of getting things done. Functions, collations, and virtual tables are already done in a similar way. Extending that to types seems like a natural thing. You can, of course, use a user-defined function that just converts a string to a BLOB of some type. As long as you use the encoder function for inputs and the decoder for all outputs, you should be good. That starts to get deep into your SQL, however. The ability to define native types is similar in complexity to adding user-defined functions. Just a thought. Any opinions? -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] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail
On Fri, Jun 29, 2012 at 05:27:25PM -0400, Jeff Archer scratched on the wall: > >Pavel Ivanov paivanof at gmail.com Fri Jun 29 17:06:42 EDT 2012 > > > >Because SQLite successfully re-prepared this statement behind the > >scenes and thus was able to successfully finish sqlite3_step() > >function. > > What could cause it to "re-prepare" the statement? Usually a change in the database schema, including the creation or dropping of tables, indexes, etc. > Is this something I need to find and fix? Generally, no. The fact that _prepare_v2() deals with this condition automatically is generally considered to be a feature. -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] sqlite time is 2 hours to late
On Wed, Jun 27, 2012 at 05:45:41PM +0200, deltagam...@gmx.net scratched on the
wall:
> Am 27.06.2012 17:40, schrieb Jay A. Kreibich:
> >On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on
> >the wall:
> >
> >>Hello,
> >>
> >>>sqlite3 event.db "select datetime('now')";
> >>gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
> >>than my system time ( win 7 ) 17::33:13
> >>
> >>How can this be fixed ?
> > Move two timezones to the west.
> >
> > (By default all times and dates are UTC.)
> I use this from within a c++ application
> char create_sql[] = "CREATE TABLE if not exists eventlog ("
> "id INTEGER PRIMARY KEY,"
> "eventdate DATETIME default current_timestamp,"
> "eventtype TEXT,"
> ")";
>
> How do I get the right time in the the column eventdate ?
UTC is "the right time." If you're doing anything with dates and
times I would STRONGLY recommend that all recorded times are in UTC.
Anything online and anything mobile tends to be used from different
timezones.
As for converting to the local time for display purposes, see:
http://sqlite.org/lang_datefunc.html
In specific, the "localtime" modifier.
> How to move timezones ?
Car, usually.
-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] sqlite time is 2 hours to late
On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on the
wall:
> Hello,
>
> >sqlite3 event.db "select datetime('now')";
> gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
> than my system time ( win 7 ) 17::33:13
>
> How can this be fixed ?
Move two timezones to the west.
(By default all times and dates are UTC.)
-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
