Re: [sqlite] max() with LIMIT
Ivan Shmakov <i...@gray.siamics.net> wrote: >>>>>> Tobias Vesterlund writes: >> Is it possible to get the highest value in a "limited column" when >> using LIMIT? > > Sure. > > SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); This only works by accident. There's no requirement that the subselect return rows in any particular order. It can, in principle, choose any ten rows. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
Tobias Vesterlund <tobias.vesterl...@ericsson.com> wrote: > But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will return 99. > > My logic, which may be flawed in this case, tells me the third SELECT should > return 10 and not 99. > > Is it possible to get the highest value in a "limited column" when using > LIMIT? You seem to want the tenth smallest ID. Try this: select id from t where id > 0 order by id limit 1 offset 9; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 linking error on 64 bit linux machine
Tarun <er.tarun.9...@gmail.com> wrote: > I am trying to use SQLite3 library on 64 bit machine. I have copied > libsqlite3.so from /usr/lib/ on 32bit linux machine to 64bit linux > machine. How do you expect this to work? The library from 32-bit machine contains 32-bit code. You can't link that with your 64-bit program. You'll likely have to build 64-bit SQLite library from sources. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 8/31/2011 5:56 PM, Tim Streater wrote: In the above, each database is newly created as shown. What I had forgotten to do was to create the "test" table in the second database before copying the data. What seems to happen is that, lacking a "test" table in the test2 database, SQLite appears to assume that I must mean the "test" table in the test1 database - it tries to copy data from the table into itself and so gets the error above. Yes. This is documented behavior - see http://sqlite.org/lang_attach.html. Is this reasonable behaviour? I might have expected to have a "no such table" error. Which part of the documentation might have led you to expect that? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_step causing Segmentation Fault
Rafael Toledo <rafaeldtol...@gmail.com> wrote:
> rc = sqlite3_prepare_v2(mydb, "INSERT INTO users (name, isadmin,
> photo) VALUES (?, 1, ?)", -1, , NULL);
> if (rc != SQLITE_OK) {
> sqlite3_finalize(statement);
If prepare fails, statement is never updated. So you are passing garbage to
sqlite3_finalize. You shouldn't call it at all.
> sqlite3_close(banco);
What's the relationship between variables named banco and mydb? Why are you
using one in open, but the other in close?
> rc = sqlite3_bind_blob(statement, ++i, (void*) user.getPhoto()[0],
I suspect that should be ()[0]. Rule of thumb: if you can't get
it to compile without a cast, you are likely doing something wrong. My psychic
powers tell me that user.getPhoto() returns vector& or similar. You then
take the first byte of that vector, and interpret it as a pointer - which of
course is a complete nonsense.
> rc = sqlite3_step(statement); // The app crashes here!
This is where SQLite tries to dereference that bogus pointer for the first time.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode Confusion and Database Size
On 9/1/2011 10:24 AM, Mohit Sindhwani wrote: I understand that the database could be either UTF-8 or UTF-16 - but that would apply to the full DB not to a single column, right? Right. If that is the case, would it not make the database larger if we had a lot of content that was originally ASCII? UTF-8 is a superset of ASCII. Strings that consist entirely of 7-bit ASCII characters are represented exactly the same way in ASCII and in UTF-8. That's probably what you want to stick with. On the other hand, the other language that we are storing seems to require 3 bytes in UTF-8. Given that, it would appear that using UTF-8 would be a better idea since it will store more "efficiently". If you have lots of Chinese (or Japanese or Korean) text to store, then UTF-16 might be more compact. For these languages, one character takes three bytes in UTF-8 but only two in UTF-16. On the other hand, plain ASCII characters take one byte in UTF-8 but still two bytes in UTF-16. So if you have a mix of the two, the issue gets murky. In addition, there are a few other questions: - FTS would work fine on both UTF-8 and UTF-16 databases, right? I believe so, but I'm not very familiar with FTS. - Can we attach two databases that have different encodings? Yes. SQLite automatically converts between them as needed, in a transparent fashion. - When using Wide Strings in Windows CE, is one encoding more preferable over the other to minimize conversions? Native API in Windows uses UTF-16. You can request UTF-16 strings even from UTF-8 database - like I said, SQLite converts between them transparently. The cost of conversion is likely negligible compared to the other costs of maintaining a database. In fact, UTF-8 might win simply because it means less data to read from hard drive, even if it requires conversion. The only way to be sure is to test and measure. I already have a database that has a couple of tables that are in UTF-8 - is there an easy way for me to build a database from this that is UTF-16? Using sqlite3 command line utility, run .dump command on the old database. Create a new database. Use "PRAGMA encoding" to set it to UTF-16. Run .import command on it using the dump file from the old one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA user_version
On 9/2/2011 11:02 PM, Walter wrote: Is there any way to get the user_version from an Attached database PRAGMA attachedName.user_version; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA user_version
Walter <rick...@iinet.net.au> wrote: > Thank you Igor I had the database name but did not > think of the the dot in between. Perhaps some one could update the > documentation to show this Perhaps someone could read the documentation before complaining about it. http://sqlite.org/pragma.html "A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be "main" or "temp" for the main and the TEMP databases. If the optional database name is omitted, "main" is assumed." -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to return these data from the DB
LiranR <liran.rit...@gmail.com> wrote: > For > example: > Time |data > --- > 5 |x > 7 |y > 9 |z > 11 | i > 13 | j > 15 | k > > Now, when i want from time 8 (8 is not in the table) to time 13 i will get : > > 9 z > 11 i > 13 j > > How do i do it? select * from MyTable where Time between 8 and 13; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite+ICU library and usage of LIKE
On 9/6/2011 11:41 AM, Sreekumar TP wrote: If I modify the statement to return all strings which match 'м' , No strings are fetched. zSQL = sqlite3_snprintf(1024,temp2,"SELECT * FROM l1 WHERE data LIKE 'м' ;"); You probably want LIKE 'м%'. It's rather pointless to use a LIKE operator with no wildcards. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite+ICU library and usage of LIKE
sreekumar...@gmail.com wrote: > The _ operator( match any single char in the string) does not seem to work.. > % is ok.. Show your data and your statement. Explain what outcome you observe, and how it differs from your expectations. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Ian Hardingham <i...@omroth.com> wrote: > Again, I have: > > eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo > FLOAT) > > with: > > CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC) > > If I have the id of a row in eloResultTable, I wish to find how far down > the eloResultScore index it is (I basically want to find a player's rank > when ordered by elo). Is there a way to do this? select count(*) from eloResultTable where elo >= (select elo from eloResultTable where id = ?); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Ian Hardingham <i...@omroth.com> wrote: > Hey Igor, thanks for the reply. > > Is this O(1)? Or... I guess it's probably low-magnitude O(log n) ? It's O(n). There's no shortcut to count(*), it walks through and counts all the records satisfying the condition. A B-tree doesn't provide a fast way to obtain a number of all the elements smaller than a given element. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UNIQUE in PRAGMA Table_Info
Thomas Baumann <softwaretoas...@yahoo.de> wrote: > can you please add a column to the result of PRAGMA Table_Info() that > indicates this column is UNIQUE? What should be reported for this table definition: create table FancyUnique(a, b, c, d, unique(a, b), unique(c, d) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encoding and Collation
Antonio Maniero <mani...@klip.net> wrote: > Why SQLite dropped the 8859 or single byte support for text? Is there > any technical reason? What do you mean, dropped? What exactly used to worked before and has stopped working now? What event has occurred between then and now that you attribute the problem to? > Is there any ready simple solution to use case insensitive collation on > SQLite to work with non-English (Latin) characters? I don't need and I don't > want a full ICU implementation Then write your own custom collation suited to your particular needs, whatever they might be. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encoding and Collation
Antonio Maniero <mani...@klip.net> wrote: >>> Why SQLite dropped the 8859 or single byte support for text? Is there >>> any technical reason? >> >> What do you mean, dropped? What exactly used to worked before and has > stopped working now? What event has occurred between then and now that you > attribute the problem to? > > Maybe I had misunderstood some old documentation and release notes talking > about 8859. Specially from http://www.sqlite.org/c_interface.html : I see. Well, SQLite2 is ancient: that ship has sailed and it's not coming back. Did SQLite2 actually implement case-insensitive comparison on accented Latin characters? I honestly don't know - by the time I got involved with SQLite (in late 2005), SQLite2 was already history, and its original documentation doesn't seem to exist anymore. > Version 3 keeps support for 8859? No, not really. But, again, it won't prevent you from storing 8859-encoded strings in the database, and installing a custom collation that understands them, if you are so inclined. Personally, I'd seriously consider switching to UTF-8. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encoding and Collation
Simon Slavin <slav...@bigfraud.org> wrote: > Though I'm having trouble pointing to a page for the SQLite3 ICU stuff at the > moment. It would be here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt but the server seems to be down at the moment. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
Mr. Puneet Kishor <punk.k...@gmail.com> wrote: > geo table: 39K rows > id max_age min_age > --- --- > 1 Holocene Holocene > 5 Cambrian Silurian > 12 Cambrian Ordovician > 229 Cretaceous Quaternary > > intervals table: ~450 rows > id age_bottom age_top name color > --- -- --- --- --- > 3 0.0117 0. Holocene #FEF2E0 > 105 443.7000 416. Silurian #B3E1B6 > 112 488.3000 443.7000 Ordovician #009270 > 421 2.5880 0. Quaternary #F9F97F > 122 542. 488.3000 Cambrian #7FA056 > 33 145.5000 65.5000 Cretaceous #7FC64E > > Keep in mind, max_age is older than min_age, and age_bottom is older than > age_top. > > The table geo can also have rows with min_age = max_age. I want a result set > with geo.id, min_age, max_age, age_bottom, age_top, > name, color like so: > > - every row should be for one and only one geo record. I have 39K rows in > "geo" table, so the result set should have 39K rows. > > - when min_age = max_age, list the corresponding intervals.name and color > > - when min_age != max_age, find the interval with the smallest different > between age_bottom and age_top that would span min_age > and max_age of geo. In other words, the interval whose age_bottom is bigger > than the age_bottom of the max_age and whose age_top > is smaller than the age_top of the min_age. I'm not sure I understand. Let's take geo.id = 5, max_age=Cambrian, min_age=Silurian. You say you want a record whose age_bottom is greater than that corresponding to Cambrian, that is 542.; and whose age_top is smaller than that corresponding to Silurian, or 416.. I don't seem to see any such record in your example. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
Mr. Puneet Kishor <punk.k...@gmail.com> wrote: >>> The table geo can also have rows with min_age = max_age. I want a result >>> set with geo.id, min_age, max_age, age_bottom, age_top, >>> name, color like so: >>> >>> - every row should be for one and only one geo record. I have 39K rows in >>> "geo" table, so the result set should have 39K rows. >>> >>> - when min_age = max_age, list the corresponding intervals.name and color >>> >>> - when min_age != max_age, find the interval with the smallest different >>> between age_bottom and age_top that would span min_age >>> and max_age of geo. In other words, the interval whose age_bottom is bigger >>> than the age_bottom of the max_age and whose age_top >>> is smaller than the age_top of the min_age. Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom >= (select age_bottom from intervals where name = geo.max_age) and age_top <= (select age_top from intervals where name = geo.min_age) order by (age_bottom - age_top) limit 1 ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help to get the value of file change counter
Simon Slavin <slav...@bigfraud.org> wrote: > <http://www.sqlite.org/c3ref/total_changes.html> > > int sqlite3_total_changes(sqlite3*); > > My understanding (which might be wrong) is that this count includes all > changes made by all connections to that database: not > only changes made using your connection but also chances made by another > computer, process, or thread. I'm 99% sure your understanding is wrong, and this function only reports changes made by the connection passed in as a parameter. There is simply no machinery in SQLite that would have allowed one connection to indicate to another how many changes it has made. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TransactionScope ON CONFLICT
Steffen Mangold <steffen.mang...@balticsd.de> wrote: > Ok I'm tested it now in a simple test environment. > > Conditions: > 1 table with two columns "TimeStamp" (PK) and "SensorID" (simple value). > > First I add a row with "TimeStamp" "2011-01-01 01:05:00" (Success) > Then I doing a Transaction with 10 "TimeStamps" from "2011-01-01 01:00:00" to > "2011-01-01 01:10:00". (Failure) > > An Exception show in Debug Output Window > "SQLite error (19): abort at 21 in [INSERT INTO [SensorData]([SensorID], > [TimeStamp]) > VALUES (@p0, @p1);]: columns TimeStamp are not unique" > > In the data base are now 6 rows, that mean all after the failing insert are > not executed be the transaction. Is this perhaps because your loop is terminated by an exception, and never gets around to actually insert the remaining rows? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TransactionScope ON CONFLICT
Steffen Mangold <steffen.mang...@balticsd.de> wrote:
> No i think i can exclude this as the problem.
> Because my code goes like this:
>
> try
>{
> context.SaveChanges(); // save changes with transaction !exception raised here
Inside this call, a loop runs, with one INSERT statement executed for each
prior AddObject call.
> }
> catch (Exception)
>{
> //throw;
>}
>finally
> {
>transaction.Complete(); // end transaction
>context.AcceptAllChanges();
And here's where you commit the transaction, regardless of whether or not it
completed successfully.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TransactionScope ON CONFLICT
Steffen Mangold <steffen.mang...@balticsd.de> wrote: > Hm... but the exception i get is an SQLite constraint exception. This means > that > The error occurs at the moment where SQLite provider try to write data to the > DB. Which is inside SaveChanges call. > So I cannot have influence to this loop that you mean. I'm not sure I understand this statement. What kind of "influence" do you want to exert? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom>= (select age_bottom from intervals where name = geo.max_age) and age_top<= (select age_top from intervals where name = geo.min_age) order by (age_bottom - age_top) limit 1 ); Thanks Igor. The above does work and produces the correct result. The query speed, however, is pretty slow ~ 75 seconds. So, I created indexes on intervals.name, geo.max_age, and geo.min_age, and that brought the query time to ~ 11 seconds. Still too slow. Indexes on geo.max_age and min_age are unlikely to help with this query (use EXPLAIN QUERY PLAN to see which indexes are actually used). An index on intervals.age_bottom might. So would an index on intervals.age_top (but not both at the same time). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
Tim Streater <t...@clothears.org.uk> wrote:
> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
> source database):
>
> $dbh->query ("attach database ':memory:' as mem");
> $dbh->query ($create_messages); // Create the messages table in
> the memory database
> $dbh->query ("attach database '" . $mailbox . "' as dst");
> $dbh->query ("insert into mem.messages select * from main.messages where
> absid='$absid'");// *** The failing statement ***
I'm not familiar with PDO and PHP, but my educated guess is, the language
binding layer running on top of SQLite has cached the prepared INSERT statement
from prior execution. But that statement's handle has been invalidated by
intervening ATTACH statements (see http://www.sqlite.org/c3ref/prepare.html ,
in particular the difference between sqlite3_prepare and sqlite3_prepare_v2).
See if there's any way to instruct the binding to clear its cache of prepared
statements.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on BOOLEAN field
François <francois.goldgewi...@gmail.com> wrote: > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table > may contain up to 100 000 entries and those entries can be selected > using "WHERE ITEM.FLAG = ?" conditions. > > Is then a good or a bad practice to add an index on this field if we > want to improve SELECT time execution ? This may help if and only if a) you have many more records with FLAG=1 than with FLAG=0 (or vice versa); and b) most of the time, you are looking up the records belonging to the small subset. For example, if there's a small number of "active" or recent records that need to be processed, and a large archive of "processed" records. However, in such a case, you might be even better off splitting the small subset into its own separate table. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compare time stamp
Akash Agrawal <akash2ha...@gmail.com> wrote: > I have table in which i have column of Date contain both *date and > time*when compare the value in my c++ program it is not giving me > correct > result . can you help to solve my problem. Show how you are inserting the data into the table, and how you are retrieving it. Explain the results you observe, and how they differ from your expectations. What exactly do you mean by "not correct"? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on BOOLEAN field
Simon Slavin <slav...@bigfraud.org> wrote: > On 13 Sep 2011, at 1:07pm, François wrote: > >> Can we consider that creating an index for an integer field is a good >> practice if this field has at least 3 possible values ? Or more ? > > It's about chunkiness, and which of the values you're looking for. > > If all possible values are equally distributed, and you frequently look for a > particular value, the index will help even if you > have only two possible values. If you have almost all '2011' rows, and > you're searching for '2011', then the index won't help > much. Actually, the break-even point is roughly 1/10: an index helps if you are selecting 10% or fewer of the records in the table; otherwise, a linear scan is faster. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
On 9/14/2011 2:07 PM, Jan Hudec wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: select geo.id, min_age, max_age, age_bottom, age_top, name, color >from geo left join intervals i on i.id = ( select id from intervals where age_bottom>= (select age_bottom from intervals where name = geo.max_age) and age_top<= (select age_top from intervals where name = geo.min_age) order by (age_bottom - age_top) limit 1 ); Looking at the query I'd say the needed indices are: create index intervals_name on intervals (name) create index intervals_ages on intervals (age_bottom, age_top) separate indices on age_bottom and age_top are much less useful to this query. I don't believe an index on intervals(age_bottom, age_top) can be used here. Or rather, it can be, but really only one half of it, to satisfy age_bottom>=X condition. The other half, age_top<=Y, needs to be satisfied with a linear scan. That's why I said that an index on intervals(age_bottom) would help, or one on intervals(age_top), but not both at the same time. Think about it this way. You have a phone book, where names are sorted by last name, then first name. You want to find all people whose last name is greater than 'Smith' and first name less than 'John'. The alphabetic order helps you with the first half, but not really with the second half - names satisfying both conditions don't appear sequentially in the list. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
On 9/14/2011 2:55 PM, Magnus Thor Torfason wrote: I then ran a query grouping employees by job: select ename, job from emp group by job; "ENAME", "JOB" == "FORD", "ANALYST" "MILLER", "CLERK" "CLARK", "MANAGER" "KING", "PRESIDENT" "TURNER", "SALESMAN" Now, I get a list of the jobs, and a random selection of employees. I would have expected an error here. It's a SQLite-specific extension. Very useful in certain cases. So getting an explicit error here would have made things simpler. Is there a way to do that? None that I know of. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does coalesce terminate early?
On 9/14/2011 9:03 PM, Sam Carleton wrote: Forgive me, fore I have forgotten the term used to describe the behavior if a C if statement where it stops executing on the first false statement, but... Does coalesce do that? The word you are looking for is "short-circuit", and no, unfortunately, coalesce doesn't do it in SQLite. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does coalesce terminate early?
Richard Hipp <d...@sqlite.org> wrote: > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton > <scarle...@miltonstreet.com>wrote: > >> Forgive me, fore I have forgotten the term used to describe the behavior if >> a C if statement where it stops executing on the first false statement, >> but... Does coalesce do that? >> > > "Short-circuit evaluation" is the usual term applied to this kind of thing, > and yes, COALESCE() does short-circuit evaluation. If you say > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a > significant performance win if, for example, B is a complex subquery. When did this start, with what SQLite version? I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at the moment), but in the version I use, in expression coalesce(someField, customFunction()) I definitely see customFunction() called even when someField is not null. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE of field deletes record
Jim Michaels
<jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org>
wrote:
> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012
Here's a CREATE TABLE statement from this sample:
CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_id_c
PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT , longdesc TEXT NOT
NULL DEFAULT , priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8) NOT
NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT , duedt VARCHAR(25) NOT
NULL DEFAULT , completeddt VARCHAR(25) NOT NULL DEFAULT );
It's invalid - DEFAULT keyword must be followed by an expression. Let's assume
you meant DEFAULT '' .
Your example creates a table named "todolist", then purports to create a bunch
of indexes on a non-existent table "events" (with no error checking). All these
statements fail.
Anyway, here's a transcript of a SQLite session that attempts to reproduce your
example to the extent possible:
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_i
d_c PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT '', longdesc TEXT
NOT NULL DEFAULT '', priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8)
NOT NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT '', duedt VARCHAR(25
) NOT NULL DEFAULT '', completeddt VARCHAR(25) NOT NULL DEFAULT '');
sqlite>
sqlite> INSERT INTO todolist(priority,state,shortdesc,startdt,completeddt,duedt,
longdesc) VALUES('0', '0', 'get hp50g calculator batteries', '', '', '', '');
sqlite>
sqlite> UPDATE todolist SET shortdesc='calc batteries' WHERE shortdesc='get hp50
g calculator batteries';
sqlite>
sqlite> SELECT DISTINCT shortdesc FROM todolist ORDER BY shortdesc ASC;
calc batteries
sqlite>
Looks OK to me.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] regexp within trim function
Linuxed <linux...@yahoo.com> wrote: > Is it possible to use regexp within a trim function? For example, if I wanted > to remove every alpha-numeric character at the end > of a string I could use the statement below. Is there any way to > simplify/condense the statement through regexp or a wildcard > character? Not with a built-in function. You can always write a custom function, and do whatever you want there. > Also, if I want to remove single or double quotes in addition to > alpha-numeric characters how can I include them in > the string of characters to trim? I've tried \' and \" without success. You include double quotes as-is. You escape an apostrophe by doubling it up: 'Here''s a "quote"' -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select where date < 30 days ago
Miklos Koren <miklos.ko...@gmail.com> wrote:
> This is probably a basic SQL question, but I could not make it work under
> sqlite. How do I select records for which a datetime column is less than
> today - 30 days? I tried
>
> SELECT * FROM ticket WHERE time < DATETIME('now','-30 days');
>
> but it does not give the intended results.
Show your data, show the results you get from the statement, and explain how
the observed outcome differs from your expectations.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select where date < 30 days ago
Miklos Koren <miklos.ko...@gmail.com> wrote:
> I was finally able to make it work with
>
> WHERE CAST(time AS int)/100/86400+2440588 < JULIANDAY('now','-30days')
It appears your timestamp is in microseconds since 1/1/1970. In this case, it
might be a bit more straightforward to write
where time < strftime('%s', 'now', '-30 days') * 100
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance problem LEFT OUTER JOIN and string data fromright table
Mira Suk <mira@centrum.cz> wrote: > query written here is a lot simplified (for example "Points" column is > filtered using custom function) however main culprit seems > to be LEFT OUTER JOIN as accessing that same column in query which only has B > table in it is lightning fast. > > result of query is > just around 40 rows, (due to Parent filter) You may want an index on A(Parent) then. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance problem LEFT OUTER JOIN and stringdatafromright table
Mira Suk <mira@centrum.cz> wrote: >> Mira Suk wrote: >>> query written here is a lot simplified (for example "Points" column is >>> filtered using custom function) however main culprit >>> seems to be LEFT OUTER JOIN as accessing that same column in query which >>> only has B table in it is lightning fast. >>> >>> result of query is >>> just around 40 rows, (due to Parent filter) >> >> You may want an index on A(Parent) then. > > A.Parent is has Index Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT syntax missing key SQL syntax
Jim Michaels <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org> wrote: > INSERT is supposed to handle multiple rows for VALUES. Supposed by whom? What is the basis for this claim? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE of field deletes record
Jim Michaels <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org> wrote: > cancel the bug report. further testing revealed that it does NOT > exhibit a bug, except for the fact that I can't do multirow INSERTs > (that, unfortunately is not in the manual, and should be a standard > feature). Should it be? In which standard is this feature mandated? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance problem LEFT OUTER JOINandstringdatafromright table
Mira Suk <mira@centrum.cz> wrote: > test 1. > > query > SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text] > FROM [IndexME] LEFT OUTER JOIN [ItemsME] > ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE > [IndexME].[Parent] = ?1 AND > (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR > (TZB_ISCHILD([IndexME].[Status]) AND TZB_MATCHDIM([ItemsME].[Status], > [ItemsME].[Points]))) > ORDER BY [IndexME].[Order]; > > test 2. (TZB_MATCHDIM removed) > > query > SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text] > FROM [IndexME] LEFT OUTER JOIN [ItemsME] > ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE > [IndexME].[Parent] = ?1 AND > (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR > (TZB_ISCHILD([IndexME].[Status]))) > ORDER BY [IndexME].[Order]; The difference is that #2 mentions only one field from ItemsME, namely IDR. The value of that field comes from the index, the table itself doesn't need to be read at all. It's not even clear why #2 bothers to join with ItemsME at all - it's a no-op. #1 uses more fields from ItemsME, so it needs to actually look up and read records from that table. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] julianday and unixepoch
Petite Abeille <petite.abei...@gmail.com> wrote: > Say, I would like to convert that date to a julian day: > >> select julianday( date( 1030561675, 'unixepoch', 'utc' ) ); > 2452514.5 > > Oooops... where does that .5 comes from? In Julian calendar, 0 represents noon, and .5 represents midnight. > I was more expecting the following: >> select cast( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) as integer >> ); > 2452514 What was that expectation based on? > So how come does julianday take the time part of date into consideration even > when it's not provided? It doesn't. > JD 2452514.50 is > CE 2002 August 28 00:00:00.0 UT Wednesday > > JD 2452514.00 is > CE 2002 August 27 12:00:00.0 UT Tuesday Looks OK to me. What again seems to be the problem? > Or did I miss something? Apparently. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could someone explain why this query is so slow
Paul Sanderson <sandersonforens...@gmail.com> wrote: > select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY > afo > > explain query plan gives the following for the initial query > 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) > 0|0|0 USE TEMP B-TREE FOR ORDER BY > > it seems that the extra time is taken creating a b-tree for the order > by but if correct why is the existing index not used? Only one index per table can be used. Imagine you have two lists of (the same) people, one sorted by first name and one by last name. You need to list all Peters alphabetically by their last name. You can either use the first list to find all Peters, then sort them by hand. Or you can use the second list to enumerate everyone in the order of last name, and select only Peters. But you can't use both lists. > is there anyway of speeding this up? A single index on (md5, afo) may help. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could someone explain why this query is so slow
On 9/21/2011 12:19 PM, Paul Sanderson wrote: Thanks Igor That makes sense but if I drop MD5 from the query (the vast majority of MD5 values would be null anyway) and use select ID FROM rtable WHERE search> 0 and isf = 0 ORDER BY afo The result from explain query plan is 0|0|0 SCAN TABLE rtable (~3 rows) 0|0|0 USE TEMP B-TREE FOR ORDER BY Which seems to indicate that the b-tree is still being created (I'll test shortly, but running another long test at the moment) Double-check that you indeed have an index on afo. Show the output of this statement: select * from sqlite_master where tbl_name='rtable'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c-api document suggestion
On 9/21/2011 3:05 PM, Sean Pieper wrote: There's an apparent inconsistency in the behavior of sqlite3_bind_text and sqlite3_prepare_v2. If the user supplies the length of the argument rather than using -1, bind_text expects that this length exclude the null termination, You can include the NUL terminator, if you want it to actually be stored in the database. whereas prepare apparently expects it to include the null termination. What makes you believe that? As far as I know, it should still work if you don't include it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c-api document suggestion
Mira Suk <mira@centrum.cz> wrote: > On 9/21/2011 21:22 Igor Tandetnik wrote: > >> You can include the NUL terminator, if you want it to actually be stored >> in the database. > > Actually you can't - if you do all SQL string functions will not work. > to be clear - > SELECT TRIM(what ever text column you stored with including null on end of > string) > will not trim that string. It does work - it removes all whitespace from the end of the string, up to but not including the first non-whitespace character. Which happens to be NUL. It follows the spec perfectly, it's your expectations that are wrong. Note that I didn't say it was wise to store NUL characters as part of the string - I only said that you could do it if you wanted to. sqlite3_bind_text takes the length parameter at face value, and stores exactly as many bytes as you tell it to store. It's up to you to ensure that the values actually make sense for your application. Garbage in/garbage out and all that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DISTINCT on a JOIN
Simon Slavin <slav...@bigfraud.org> wrote: > I have a setup which I will simplify as follows: > > There is a table of courses. > Every course can have any number of people working on it. > Every course involves any number of tasks. > > I want to make a SELECT which will return a table as follows: > > course1 number-of-people-involved-in-course1 > number-of-tasks-involved-in-course1 > course2 number-of-people-involved-in-course2 > number-of-tasks-involved-in-course2 > course3 number-of-people-involved-in-course3 > number-of-tasks-involved-in-course3 > course4 number-of-people-involved-in-course4 > number-of-tasks-involved-in-course4 select name, (select count(*) from people where course=courses.id), (select count(*) from tasks where course=courses.id) from courses; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c-api document suggestion
Mirek Suk <mira@centrum.cz> wrote: > I just find entire nul handling in SQLite strange. it's C API why not > expect C (that is nul terminated) strings. Because some people do want to store strings with embedded NULs, for various reasons. If you don't, just pass -1 for length and be done with it. > man says > "Strings returned by sqlite3_column_text() and sqlite3_column_text16(), > even empty strings, are always zero terminated." > "The values returned by sqlite3_column_bytes() > <http://www.sqlite.org/c3ref/column_blob.html> and > sqlite3_column_bytes16() <http://www.sqlite.org/c3ref/column_blob.html> > do not include the zero terminators at the end of the string. For > clarity: the values returned by sqlite3_column_bytes() > <http://www.sqlite.org/c3ref/column_blob.html> and > sqlite3_column_bytes16() <http://www.sqlite.org/c3ref/column_blob.html> > are the number of bytes in the string, not the number of characters." > > If I include my nul does this mean string is my nul terminated or sqlite > appends another one ? SQLite appends another one. > is this auto-added null included in size returned No. But your own (if any) is. > are my nuls removed from string size or not ? No. You get back exactly the sequence of bytes you put in. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I still need an index if I have a composite Primary Key(or UNIQUE)
Florian Kusche <hj2...@k1k.eu> wrote: > in my application, an Icon is made up of one or more layers of images: > > CREATE TABLE IconLayer > ( > IconID INTEGER NOT NULL, > Order INTEGER NOT NULL, > ImageID INTEGER NOT NULL REFERENCES Image( _id ), > > PRIMARY KEY ( IconID, Order ) > ); > > During runtime, I want to get the layers of my Icons: > > SELECT ImageID FROM IconLayer WHERE IconID=xyz ORDER BY Order > > Do I still need an index on the column IconID, or is the primary key > sufficient, because it already contains the IconID? Primary key is sufficient. For the future, run your query with the words "EXPLAIN QUERY PLAN" prepended in front - this will tell you exactly which indexes are used for which tables when executing the query. > Is this dependant on the order of the columns in the primary key? Yes. The order you have them in is will suited for this query. > i.e.: if no additional index is needed, would it still work, if > the primary key was "PRIMARY KEY ( Order, IconID )" ? This index could still be used, but only to satisfy ORDER BY clause. A condition on IconID would have required a full table scan. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index usage when querying views
Nikolaus Rath <nikol...@rath.org> wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run the same query on the view, SQLite scans through all > involved tables: A select from the view is transformed into SELECT 1 FROM (SELECT * FROM inode_blocks UNION SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id IS NOT NULL ) WHERE inode=42; SQLite's optimizer isn't really that smart - definitely not smart enough to move the condition into the sub-select and duplicate it into each subquery. That's a rather non-trivial transformation. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] binary key, blob or text?
Petite Abeille <petite.abei...@gmail.com> wrote: > From an efficiency point of view, would one be better off storing that key as > a blob or text? In other words, store the raw byte > sequence directly or use a hex text representation of it? Blob > So, binary: > >hashblob not null collate binary, -- as the raw byte sequence Collation doesn't apply to blobs - they are always compared as binary. > Any gotchas in using blob as keys (unique or otherwise)? None that I know of. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Sam Carleton <scarle...@miltonstreet.com> wrote: > Is there any way to "disable" a trigger in sqlite? DROP TRIGGER -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Sam Carleton <scarle...@miltonstreet.com> wrote: > Is there any way to "disable" a trigger in sqlite? If you have control over the trigger's definition, you could do something like this: create trigger MyTrigger on ... when (select enabled from TriggerControl where name='MyTrigger') begin ... end; where TriggerControl(name text, enabled integer) is a table with a row for each trigger you want to manage. You can effectively turn a trigger on and off with update TriggerControl set enabled=? where name='MyTrigger'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
BareFeetWare <list@barefeetware.com> wrote: > On 03/10/2011, at 2:12 PM, Sam Carleton wrote: > >> Ok, how do I list what a trigger is so that I can add it back once I want to >> "reactive" it? > > select SQL from SQLite_Master where name = 'trigger name' and Type = 'trigger' And be careful to run this statmenet *before* you drop the trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do automatic rollback on any error?
On 10/3/2011 3:21 PM, Pero Mirko wrote: If I do BEGIN EXCLUSIVE . insert / update / delete queries here COMMIT does it guarantee it will automatically rollback if the error occurs or do I have to specify it manually? http://sqlite.org/lang_conflict.html What happens if I have 20 queries, 10 execute successfully (after COMMIT) and then it fails - will the database insert / update / delete first 10 or will it return to initial state before any inserts? The default behavior is ABORT, which means the statement that caused the error is rolled back, but the transaction stays open with any prior changes still in place. If you COMMIT at this point, those changes will get committed. Or do I have to use something like INSERT INTO [table] OR ROLLBACK... or something like that? Either that, or you'll have to check for errors and, if any occur, issue a ROLLBACK statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?
Ivan Shmakov <i...@gray.siamics.net> wrote: > Well, this case is somewhat weird. I have a number of tables > like: > > PRAGMA "foreign_keys" = 1; > > CREATE TABLE "foo-L" ( >key INTEGER PRIMARY KEY >REFERENCES "foo" (key), >value INTEGER NOT NULL); > > Which are tied to a single table, like: > > CREATE TABLE "foo" ( >key INTEGER PRIMARY KEY, >value INTEGER NOT NULL); > > This structure is, obviously, could just as well be represented > with, e. g.: > > CREATE TABLE "foo" ( >"key" INTEGER PRIMARY KEY, >"value" INTEGER NOT NULL, >"value-1" INTEGER, >… >"value-N" INTEGER); Or else with this: CREATE TABLE "foo" ( key INTEGER PRIMARY KEY, value INTEGER NOT NULL); create table fooDetails( key INTEGER PRIMARY KEY REFERENCES "foo" (key), L integer not null, value integer not null); That's what a fully normalized schema would look like for your data. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do automatic rollback on any error?
On 10/5/2011 12:42 PM, Pero Mirko wrote: and then it fails - will the database insert / update / delete first 10 or will it return to initial state before any inserts? The default behavior is ABORT, which means the statement that caused the error is rolled back, but the transaction stays open with any prior changes still in place. If you COMMIT at this point, those changes will get committed. That doesn't make sense because if I start transaction I get SQLITE_OK, then I do several "INSERT INTO" queries, they also pass as SQLITE_OK. Finally on COMMIT - I get error. What kind of error? Some errors are so severe that there's no way to recover, and SQLite does perform automatic rollback. E.g. failure to write to journal file will do that, I think. Others are recoverable and leave the transaction open - e.g. SQLITE_BUSY (someone else is reading the same database; wait a bit and retry). In any case, if you only get an error on COMMIT, then doing INSERT OR won't change the situation - INSERT statements aren't failing, so they will have no reason to exercise their conflict clause, whether implicit or explictily specified. By the docs I should be able to put ROLLBACK - before the COMMIT. Now *that* makes no sense. Where in the docs do you see this? Once you execute ROLLBACK, the changes are reverted and the transaction is closed. There's nothing to commit. This isn't a problem for single INSERT or UPDATE statement as I can always do INSERT OR ROLLBACK INTO [tablename] but for BEGIN/COMMIT blocks it treats the entire block as it seems as a single statement. If you want to always roll back on any error, what's stopping you? Just do that - whether the error comes from COMMIT or any other statement. I don't understand the problem you (think you) have. Furthermore doing something like: BEGIN EXCLUSIVE INSERT OR ROLLBACK INTO table INSERT OR ROLLBACK INTO table INSERT OR ROLLBACK INTO table COMMIT also doesn't make sense - because if first insert is successful and second is not it would keep first? Or not as it is within BEGIN/COMMIT block? If INSERT fails due to constraint violation (the only kind that conflict resolution clause affects), then OR ROLLBACK clause will roll back the whole transaction, including any prior statements. In my tests it never did partial inserts in BEGIN/COMMIT block but the docs do specify to issue ROLLBACK on error. Structure your tests so as to trigger a constraint violation (e.g. try to insert the same value twice into a UNIQUE column), if you want to see conflict resolution clause in action. So I'm puzzled by all this. The only thing I want is to return database to initial state if any command or COMMIT fails. That's what ROLLBACK is for. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do automatic rollback on any error?
On 10/5/2011 1:40 PM, Pero Mirko wrote: So in other words a pseudo function like this: BEGIN EXCLUSIVE if anyerror return INSERT INTO [table] if anyerror ROLLBACK INSERT INTO [table] if anyerror ROLLBACK UPDATE [table] if anyerror ROLLBACK UPDATE [table] if anyerror ROLLBACK COMMIT if anyerror ROLLBACK return Would that guarantee ROLLBACK on any error? Looks good to me. Does last ROLLBACK knows it should rollback from BEGIN EXCLUSIVE or should I still mark it with SAVEPOINT? No, you don't need any SAVEPOINT. The following was what I did with my tests 1. if i did say 10 "INSERT INTO" - and it failed at #7 - first 6 were kept, 7 was deleted 2. if i did the same but with INSERT OR ROLLBACK INTO... the same as 1. Without an explicit BEGIN, every statement is executed in its own implicit transaction. As soon as the statement finishes successfully, that transaction is automatically committed. Fruther, since there's only one statement in the transation, there's no difference in behavior between the default ABORT clause and ROLLBACK clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTimeOffset in SQLite
Steffen Mangold <steffen.mang...@balticsd.de> wrote: > how to use DateTimeOffset with Sqlite, if it is possible? What's DateTimeOffset? Offset from what to what? What exactly are you trying to achieve? See if this helps: http://www.sqlite.org/lang_datefunc.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using COUNT(*) in unusual circumstances
Simon Slavin <slav...@bigfraud.org> wrote: > I'm trying to write some code which has to be useful under many different > circumstances. Something I want to be able to do is to > take an arbitrary SELECT statement and replace the columns which would > normally be returned with COUNT(*) to find out how many > rows would be returned. To do this I replace the text between "SELECT" and > "FROM". An alternative might be to wrap the counting SELECT around the original statement: select count(*) from ( select ... ); > I suspect this won't work well with unusual SELECTs which include sub-selects. I don't think subselects between SELECT and FROM should be any problem. Of course you'd need to count parentheses and quotes and square brackets carefully to find the right FROM. SQLite has a non-standard extension whereby aliases assigned to expressions in the SELECT clause may be used in the WHERE and other clauses: select 1+2 as alias from mytable where alias > 0; > I'm trying to get my head around whether JOINs could be a problem. I don't see how. > Also, does anyone know whether some combination of NULs might make COUNT(*) > give the wrong result ? No. COUNT(*) counts the number of rows, regardless of what those rows contain. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?
Frank Chang <frank_chan...@hotmail.com> wrote: > Hi, We are using the following schema : > CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, > [Vertices] BLOB ) > > index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3| > > > This table could potentially hold 10 to 40 million rows. We are using the > following query to obtain the minumum rowid for each > unique LastName: > > sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest > t1 > GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM > BlobLastNameTes > t where FieldName = t1.FIELDNAME); This query doesn't do what you seem to think it does. If it works, it's only by accident. You probably want something as simple as select FieldName, min(rowid) from BlobLastNameTest group by FieldName; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pzTail parameter to sqlite3_prepare_v2()?
James Hartley <jjhart...@gmail.com> wrote: > The documentation speaks that multiple SQL statements can be passed to > sqlite3_prepare_v2() which will only compile the first statement of the > stream. Upon return, pzTail points to the first character of the next SQL > statement which has not been compiled. From this, I assume that *pzTail > will be set to NULL after compiling the stream's last statement? I suspect it'll point to the terminating NUL character. In any case, it should be easy to figure out experimentally. > Also when processing multiple SQL statements, does sqlite3_reset() need to > be called before calling sqlite_prepare_v2() again using the last value of > pzTail as the next SQL statement to compile? No. The two sqlite_prepare_v2() calls and the two statement handles they produce are completely independent. It doesn't matter at all that the original strings from which they were compiled just happened to reside in the same char[] array. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large datatabase table
Frank Chang <frank_chan...@hotmail.com> wrote: > So, why is my query just working accidently? I take it back - the behavior of your query is well-defined, for the simple reason that FieldName is unique, so every group only has a single row in it (as someone else has kindly pointed out - I missed this detail on the first reading). For that same reason, the whole GROUP BY and sub-select dance is completely pointless. Your query is just a very elaborate and wasteful way to write select FieldName, rowid from BlobLastNameTest; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize a query on a very large database table?
Frank Chang <frank_chan...@hotmail.com> wrote: > Florian Weimar and Igor Tadetnik, > > When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, > > select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r > owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = > t1.FIELDNAME) > order by 1; This query doesn't make any sense, no more than the one with GROUP BY did. Let's step back for a minute - what exactly are you trying to achieve? In what way does this simple query fail to reach your goal, whatever that may be: select FieldName, rowid from BlobLastNameTest; Explain the problem you are trying to solve, *not* your proposed solution. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to design this table?
张一帆 <zyf01...@gmail.com> wrote: > i have some data like "a and b or c ...",there will be a word 'and' or > 'or' which means the Logical relations between each item.So how to design a > table to store the data in best way? Best way to achieve which goals? What operations do you need to perform on said data? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE LIMIT clause
cricketfan <srtedul...@yahoo.co.in> wrote: > SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; > > Since I have the index on PK1, I believe the rows will be returned in the > ORDER of PK1. Putting an ORDER BY clause will be a no-op. Probably, but that's an implementation detail. If you rely on a particular property of a resultset, it's best to request it explicitly, rather than hoping that the implementation just happens to tilt your way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table.
Frank Chang <frank_chan...@hotmail.com> wrote:
> Igor Tandetnik,
> The fieldname groups in our BlobLastNameTable consist of
> multiple rows where each pair of columns [FieldName,
> BLOB[Vertices]] is unique.
How so? You have FieldName declared as PRIMARY KEY. From your original post:
CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, [Vertices]
BLOB )
PRIMARY KEY means there can only be one row with any given value of FieldName.
If you have since changed your schema, then post the new CREATE TABLE statement
for BlobLastNameTest table, and any CREATE INDEX statements related to it.
> Therefore, every fieldname group does not just have a single row but instead
> 1000's or
> 1's rows. So that is why we use a group by/order by and
> subselect clause to locate the first/minimum row id row
> in each fieldname group.
Assuming this is true, what's wrong with
select FieldName, min(rowid) from BlobLastNameTest group by FieldName;
> Once we know the first/minimum row id of each unique fieldname group, we
> would lke to write a
> sqlite UPDATE Statement to accumulate all the BLOB vertices of all the rows
> with that unique fieldname into the first(i.e
> MIN(ROWID)) row's BLOB(Vertices)column for each unique fieldname group.
What do you mean by "accumulate"? Concatenate? I don't think you can do that
with SQL alone - you'll have to write some code. Personally, I'd do something
like this (in pseudocode):
stmt = prepare("select FieldName, rowid, Vertices from BlobLastNameTest order
by FieldName, rowid")
currentFieldName = "";
firstRowId = -1;
blob = ""
while (stmt.Step) {
if (currentFieldName != stmt.FieldName) {
commitBlob(firstRowId, blob)
currentFieldName = stmt.FieldName
firstRowId = stmt.rowid
blob = ""
}
blob += stmt.Vertices // whatever you mean by "accumulate", do it here
}
commitBlob(firstRowId, blob)
function commitBlob(rowid, blob) {
if (rowid > 0) {
execute "update BlobLastNameTest set Vertices = ? where rowid = ?;"
with parameters (blob, rowid)
}
}
> Then we would like to discard all the rows in each
> fieldname group of rows that have an rowid different from the first row
That one's easy:
delete from BlobLastNameTest where rowid !=
(select min(rowid) from BlobLastNameTest t2
where t2.FieldName = BlobLastNameTest.FieldName);
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty acceptance of non-aggregate value that is not ingroup by part of the SELECT statement
Frank Missel <fr...@missel.sg> wrote: > I would have expected an error message here to the effect that a non > aggregate value was not part of the grouping. SQLite allows this as an extension. When this happens, a value from an arbitrary row within the group is reported. This is often convenient. > The result is strange and misleading and can easily lead to data errors. If you don't like this facility, don't use it in your queries. No one's forcing you. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table
Frank Chang <frank_chan...@hotmail.com> wrote: >The explain query plan for select FieldName, min(rowid) from > BlobLastNameTest group by FieldName shows a full index scan. Of course. How else do you expect to be able to look at every FieldName? You seem to be expecting some kind of a "compressed" index - an index with as many entried as there are distinct values of FieldName, each entry pointing to... I'm not sure what, exactly. There ain't no such thing. An index on FieldName still has as many entries as there are rows in the underlying table - it's just sorted by FieldName. To get a list of all distinct values of FieldName, SQLite has to scan this index, and simply discard any value that is equal to that from previous row. If you think you need such a "compressed index", you would have to maintain it yourself, as a separate table. Personally, based on the description of your problem, I don't think you need any such thing. > Here is how I might do the update: > > 1. CREATE TABLE FOO(FIELDNAME CHAR(25), IDROW INT); > 2 INSERT INTO FOO select FieldName, min(rowid) from BlobLastNameTest > group by FieldName > 3. INSERT OR REPLACE INTO BLOBLASTNAMETEST SELECT t1.FIELDNAME, > UDF(t1.ROWID,t1.FIELDNAME,this,'BLOBLASTNAMETEST') FROM > FOO WHERE BLOBLASTNAMETEST.FIELDNAME = FOO.FIELDNAME AND > BLOBLASTNAMETEST.ROWID = FOO.IDROW. This last query makes no sense to me. It refers to BLOBLASTNAMETEST in WHERE clause though it was never mentioned in FROM clause. It refers to identifiers "t1" and "this" that were never declared. How about this? Drop steps 1 and 2, and run this statement instead: insert or replace into BlobLastNameTest(rowid, FieldName, Vertices) select min(rowid), FieldName, MyAccumulation(Vertices) from BlobLastNameTest group by FieldName; where MyAccumulation is a custom aggregate function that does whatever you mean by "accumulate". This way, you should be able to do everything in a single pass. See http://sqlite.org/c3ref/create_function.html , the description of xStep and xFinal parameters, for an explanation of how to set up a custom aggregate function. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty acceptance of non-aggregate value that isnot ingroup by part of the SELECT statement
Frank Missel <i...@missel.sg> wrote: >> SQLite allows this as an extension. When this happens, a value from an >> arbitrary row within the group is reported. This is often convenient. >> > Ha ha, you must be joking, right? > Otherwise, you must enlighten me on how it could have a practical use to > have an arbitrary value in the group returned together with the total number > of records across all the groups. It's not useful in your specific query, but it is useful in others. Yours is not the only system in the world that uses SQLite, you know. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty acceptance of non-aggregate value that is notin group by part of the SELECT statement
Frank Missel <i...@missel.sg> wrote: > As for the advantages, I just don't see how it could be practical to have an > arbitrary group value together with the total number of records in an > application. Sometimes, you know that the value of a particular column is in fact unique across the group (in which case it doesn't matter which row it's taken from). This knowledge could come from invariants being maintained that are not perhaps formally captured in the database schema, or else flow from the particular join and WHERE conditions. In such cases (which come up surprisingly often, in my experience), it's convenient to be able to just use the column name. I also work with MySQL a bit, which doesn't allow that, so you have to wrap the column name in min() or max() (doesn't matter which, as all values are the same). Personally, I find it annoying. It makes the database engine do unnecessary comparisons, thus hurting performance (though I admit that the difference is likely to be immeasurably small), and more importantly, it makes the statement more verbose and difficult to read and understand. Now, if there were some kind of a PRAGMA that would turn this behavior off and enforce stricter syntax rules, I wouldn't be against it. I'd likely just never use it. Please feel free to try and convince SQLite developers (of which I'm not) to add such a pragma (but don't expect me to pitch in for the cause). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed of sorting - diff between integer and string
On 10/11/2011 9:31 PM, Shorty wrote: Here is an example table: COLUMNS: grocery_type, description, price ROWS: fruit, apple, 1.23 fruit, banana, 5.35 vegetable, carrot, 1.55 vegetable, spinach, 6.85 -- HERE IS MY QUESTION: -- Is faster for the sqlite database to have the grocery_type as a string or integer? I doubt there'll be a noticeable difference until your table grows to hundreds of thousands of rows. But of course integers are somewhat more efficient. It's not so much the cost of comparisons: integers have a more compact representation, so the database file is smaller, so there's less disk I/O to perform. And disk I/O is were the bottleneck is, most of the time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very largedatabase
Frank Chang <frank_chan...@hotmail.com> wrote:
> void cIntersectingGroupCache::AGGREGATEBLOBFunc(sqlite3_context *context, int
> argc, sqlite3_value **argv){
How come you only show one function? A user-defined aggregate function is
actually represented by two C[++] functions - one that is called for every row
and performs actual aggregation, and another that's called at the end of each
group, reports the result and resets the state machine to prepare for the next
group. You can use sqlite3_context to store state between invocations - see
sqlite3_aggregate_context.
In light of this, it's not clear why you need PreviousFieldName.
> switch( sqlite3_value_type(argv[0]) ){
Why would argv[0] be anything other than a blob? Are you storing different
types of data in Vertices column?
> case SQLITE_INTEGER: {
> iVal = sqlite3_value_int64(argv[0]);
> iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
> sqlite3_result_int64(context, iVal);
Again - you are not supposed to report the result until you've seen all rows in
a group (at which point your xFinal callback is called).
> case SQLITE_BLOB: {
> size = sqlite3_value_bytes(argv[0]);
> ip2 = (int *)sqlite3_value_blob(argv[0]);
> for (int i = 0; i < size/sizeof(int); i++){
> ((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(ip2[i]);
> }
Assuming Column3 is a vector (or a similar STL container), you can replace
your loop with something like this:
vector& v =
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3;
v.insert(v.end(), ip2, ip2 + size/sizeof(int));
> sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.size()*sizeof(int),NULL);
And again - you shouldn't report the result until the whole group is processed
and xFinal is called.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create sqlite3_value * from scratch?
Ivan Shmakov <i...@gray.siamics.net> wrote: > I wonder, is it possible to create sqlite3_value * from scratch > from within SQLite library's user code? There's a circuitous route that leads there. You can prepare a statement of the form "select ?;", bind the parameter with one of sqlite3_bind_* functions, then step it once, and retrieve the value right back with sqlite3_column_value. Note that the resulting sqlite3_value is "unprotected" (see http://sqlite.org/c3ref/value.html) and can only be used in limited ways. And of course it's only valid until the "select ?;" statement is reset or finalized. > Given some way to construct a sqlite3_value wrapping object, I > could instead rely on sqlite3_bind_value () alone, thus > eliminating the necessity of type specifiers in the interface. Well, a hypothetical API that constructs sqlite3_value from raw data would have to take the type specifier anyway, wouldn't it? You would just be moving the same logic to another place. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OFFSET Performance
Fabian <fabianpi...@gmail.com> wrote: > This query returns the results as expected, and performs well. But as soon > as I raise the OFFSET to a large value (for pagination) the performance > drops drastically. See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare statement in separate function
enjoythe...@hushmail.com wrote: > I was allocating memory because I wanted to prepare the statement > in a separate function. Just prepare the statement, and return sqlite3_stmt* by value. You are not allocating memory when returning, say, an int, right? sqlite3_stmt* is comparable in size, and can be treated similarly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OFFSET Performance
Fabian <fabianpi...@gmail.com> wrote: > 2011/10/12 Igor Tandetnik <itandet...@mvps.org> > >> >> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > > I tried to implement the method as suggested in the article, but it will > only work for pagination where the user is only allowed to go 1 page back or > 1 page forward (since you have to remember the last rowid). In my case, the > user is allowed to jump to the last page, without visiting any of the > previous pages, making the suggested method impossible to implement. To get to the last page, you could reverse the ORDER BY, effectively reading backwards. Of course you'll have to reverse again in the application code, for presentation. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time and date functions
Nando <fl446...@gmail.com> wrote:
> SQLite system for working with dates is very powerful, but I find it
> strange. I'm trying to select the dates between the beginning and end of a
> week defined from the date of a given day. If I understand the documentation
> thing this code should work:
>
>
> select * from TABLE where DATE between date('now','-7 days','Weekday 0')
> and date('now','-7 days','Weekday 6');
Today is Saturday, 10/15. date('now','-7 days') is the last Saturday, 10/8.
date('now','-7 days','Weekday 0') is the nearest Sunday on or after 10/8 - that
is, the Sunday of 10/9. Similarly, date('now','-7 days','Weekday 6') is the
nearest Saturday on or after 10/8 - that is, 10/8 itself. Since 10/9 is greater
than 10/8, there are no dates that fall between them.
This inversion would hapeen every day except on a Sunday.
You might want something like this:
between date('now','Weekday 0', '-7 days') and date('now', 'Weekday 0', '-1
days')
That's always the nearest previous Sunday through the following Saturday (which
may be in the past or in the future). Adjust to taste.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time and date functions
Nando <fl446...@gmail.com> wrote:
> I just realized that the "weekday" is not working as and believed, but is
> much easier. I can make simple queries of the form:
>
> select * from dates WHERE date = date ('2011-12-09 ',' weekday 5 ');
>
> Or
>
> select * from dates WHERE date <= date ('2011-12-04 ',' weekday 6 ');
>
> And they work properly. But I can not use this selection criterion with
> periods defined by the operator "between" using "X> = Y and X <= Z"
You can. But you have to carefully ensure that, in fact, Y <= Z. Otherwise the
condition won't hold for any X. In your original post, you ended up with Y > Z.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3_step
Simon Slavin <slav...@bigfraud.org> wrote: > The first step has to make a lot of decisions about what plan to follow in > retrieving the rows: Which index is the best to use ? > Is it going to be necessary to sort the rows even after that index ? These > things do take some extra time. These decisions are made by sqlite3_prepare, before the first sqlite3_step. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3_step
Sreekumar TP <sreekumar...@gmail.com> wrote: > I do not have a ORDER BY , only a WHERE clause. So sorting should not be > the cause for the overhead. Show your query, and the output of EXPLAIN QUERY PLAN on it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "is not null" and index
Yoav Apter <yo...@checkpoint.com> wrote:
> I have the following table:
>
> CREATE TABLE x ('col1', 'col2', 'col3')
> Create col1index on x ('col1')
>
> When I run this query: "select * from x where col1 is null" I see the index
> on x is used.
> When I run this query: "select * from x where col1 is NOT null" I see the
> index on x is not used.
Why is that suprising? Imagine you are given a book with an index at the end,
and are asked to enumerate all pages where a particular term does *not* appear.
Would an index be helpful in this task?
> How can indexes be used with "not null" queries?
They cannot.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "is not null" and index
Black, Michael (IS) <michael.bla...@ngc.com> wrote: > What does distinct do? > sqlite> explain query plan select distinct (col1) from x where col1 is not > null; > sele order from deta > - > 0 0 0 SCAN TABLE x USING COVERING INDEX col1index > (~50 rows) > OK...we're still using an index here... In a sense, yes - but note that you have SCAN TABLE USING INDEX; compare and contrast with SEARCH TABLE USING INDEX. DISTINCT effectively implies ORDER BY. The way DISTINCT is implemeted, SQLite enumerates the rows in order (and the index is helpful here) and discards any where the value is the same as in the previous row. You've just replaced a full table scan with a full index scan (and changed the meaning of the query in the process). > so using this subselect we do this: > sqlite> explain query plan select * from x where col1 in (select distinct > (col1) from x where col1 is not null); > sele order from deta > - > 0 0 0 SEARCH TABLE x USING INDEX col1index (col1=?) > (~250 rows) > 0 0 0 EXECUTE LIST SUBQUERY 1 > 1 0 0 SCAN TABLE x USING COVERING INDEX col1index > (~50 rows) So now you are scanning the table, and also searching on top of that. How is this an improvement? > Performance probably depends on how many "not null" things there are...if not > many of them this may not be any faster. In fact, this will always be strictly slower than a straightforward table scan. > A count() could be a lot faster though I'd think. How so? I'm not even sure how you would use count() here, let alone use it in a way that leads to performance gains. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "is not null" and index
Jean-Christophe Deschamps <j...@antichoc.net> wrote:
>>> How can indexes be used with "not null" queries?
>>
>> They cannot.
>
> If one sees NOT NULL as the complement of NULL, i.e. values in the
> range {min_value, max_value} (min and max depending on the column
> expected content and type), then couldn't an index help?
> select * from table where col between min_value and max_value
If you have an extrinsic knowledge of the domain of the column values, then
yes, you can reformulate the query as
where col1 between minValue and maxValue
I don't think SQLite would be able to rewrite the query this way automatically.
> Of course if there are only few NULLs then a table scan will probably
> be faster.
In fact, unless some 90% of the rows contain NULLs, a table scan will probably
be faster. Using an index trades O(N) performance for O(M log N), where M is
the number of rows actually satisfying the condition. This is clearly an
improvement when M is much smaller than N, and a pessimization when M is close
to N. A rule of thumb is that the break-even point is somewhere around M = 0.1*N
And if 90% of the rows do contain NULL in this column, I'd consider splitting
the data into two tables - one with three columns (containing all non-NULL rows
from the original table) and the other with two columns (containing the
remaining rows).
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "is not null" and index
On 10/17/2011 9:30 AM, Black, Michael (IS) wrote: According to this benchmark the break-even point is at 40% nulls. I asssume you have a different test? I did mention "rule of thumb". Specific cases may vary. I must admit I'm too lazy to build tests for someone else's problem. The fact that all NULL values are clustered together in rows with sequential rowids might have skewed the results in your test. Better locality of reference, fewer pages to read from disk, improved cache utilization. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
Barry1337 <jmichiels...@gmail.com> wrote: > So I need, for every record in STAYSPEC, to find another record in STAYSPEC > that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I > want to replace the date_out from STAYSPEC with that date (in text format). > > If such a record does not exist (EXISTS) then it needs to take the date_out > from the STAYHOSP table where the STAYNUM is the same. Try this: update STAYSPEC set date_out = coalesce( (select date_in from STAYSPEC ss2 where ss2.staynum = STAYSPEC.staynum and ss2.order_spec = STAYSPEC.order_spec + 1), (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum) ); > Whenever I execute the above query it doesn't give an error or something, > it's just keeps running without ever stopping ! Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
Frank Missel <i...@missel.sg> wrote: > I think that the sqlite-users e-mail list has enough traffic to warrant a > proper forum. For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing list-to-NNTP gateway and happens to carry this list. I'm old-fashioned enough to believe that an NNTP newsgroup *is* the proper forum. Can't stand modern Web-based forum interfaces. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
On 10/19/2011 6:34 PM, Joanne Pham wrote: Curently I had the table with the plain text and I want to encrypt these passwords by using the following sql statement but I got the error mesages.. Any suggestion? update vpn set password = AES_ENCRYPT(select password from mytable, "abcddsfddafdasfddasd"). Do you want vpn.password set to the same value in all rows? I would have expected a WHERE clause on the select statement that somehow correlates mytable with vpn. Anyway, the immediate cause of the syntax errors is the fact that a subselect needs to be enclosed in parentheses: update vpn set password = AES_ENCRYPT((select password from mytable), "abcddsfddafdasfddasd"); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
On 10/19/2011 7:23 PM, Joanne Pham wrote: update vpn set password = AES_ENCRYPT((select password from vpn) , "abcddsfddafdasfddasd"). I suspect you want update vpn set password = AES_ENCRYPT(password, 'abcddsfddafdasfddasd'); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql statement to update the data in the table
On 10/19/2011 8:49 PM, Joanne Pham wrote: Yes, That is what i want but it seems like it didn't work. For example the password is 'password'. I ran the update statement below and do the AES_DECRYPT the password is null instead of 'password'. Any idea? You'll have to raise the issue with the author of custom functions AES_ENCRYPT and AES_DECRYPT. They are not part of SQLite proper, you must be using some kind of third party extension library. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string conatenated sql statements
Gert Corthout <gert_corth...@hotmail.com> wrote: > My argument so far is that parametrized queries are way faster if used > properly. > The next obvious argument is sql injection. On all string input a simple > conversion is done: any ' is replaced by '', that's it. > This seems to block off any sql injection right there as the escape character > \ doesn't work in sqlite. Yes, this should be sufficient to prevent the attack. %q specifier in sqlite3_mprintf performs the same manipulation, for the same reasons: http://www.sqlite.org/c3ref/mprintf.html > Alternatively can I make sql statements fail by including funky characters or > character combinations? It would be difficult to get SQLite to crash outright. It would take any sequence of bytes and stuff it into the database as-is. That said, you might get strange results with strings that are not well-formed UTF-8 or UTF-16 sequences (depending on which API flavor you are using). However, this is equally true for strings bound as parameters as well as string literals embedded directly into the statement. Performance is really the strongest argument. sqlite3_prepare is a fairly expensive operation, it's beneficial to run it once and reuse the statement many times with different parameters. Plus the time you save on not having to pre-process the strings, plus the peace of mind knowing that you haven't accidentally missed a spot where such pre-processing would be necessary. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string conatenated sql statements
Gert Corthout <gert_corth...@hotmail.com> wrote: > I can see only 1 very long-shot security issue. Assuming I am a malafide > programmer at our company I could add ESCAPE ']' to a > vital query that takes user input and then use ]' to break out and inject > some SQL in the live system, right? A malicious developer with access to the codebase would likely have lots of ways to wreak havoc, with or without prepared statements. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrent readonly access to a large database.
Bo Peng <ben@gmail.com> wrote: > I have a database with about 5000 tables each with more than 1 million > records. I needed to get some summary statistics of each table but > find that it will take days to run 'SELECT count(*) FROM table_XX' > (XX=1,...,5000) sequentially. I therefore created 10 threads, each > having its own database connection (sqlite3.connect() from python). To > my dismay, it appears that only one thread was allowed to access the > database at any time so the overall speed was not improved. You may create multiple threads, but your hard drive only has one set of heads. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with binding parameters to LIKE
Navaneeth.K.N <navaneet...@gmail.com> wrote: > I am trying to use parameters in a LIKE query. I have the following > code which uses Sqlite C/C++ API. > > const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%' > ORDER BY freq DESC LIMIT 10;"; > > int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL); > if ( rc != SQLITE_OK ) > return false; > > sqlite3_bind_text ( stmt, 1, data , -1, NULL ); > > Unfortunaltly, this won't work. It should. Check the value of "data" variable - you are probably passing something other than what you think you are. I don't think anything wrong with the code you've shown - the problem must lie in the code you haven't. > When I execute > the same statement after removing parameters it works perfectly. > Something like, > > const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%' > ORDER BY freq DESC LIMIT 10;"; That can't be right - there's an extra apostrophe before % sign. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UNIQUE constraint
Fabian <fabianpi...@gmail.com> wrote: > I have a column with a normal INDEX, and I would like to turn it into an > UNIQUE index, but I'm a bit worried about the performance implications for > inserts. Can someone give some insight into how UNIQUE is implemented in > SQLite, does it create extra tables compared to a normale index, are there > many extra checks? No, a UNIQUE index and a regular index are implemented the exact same way. It's just that, at INSERT and UPDATE time, after finding a proper place to insert the new value, an additional check is made that the place isn't already occupied. > And a related question: I need to insert 1 million rows, and currently I > create the INDEX afterwards, because that should be faster. I cannot create > the UNIQUE INDEX afterwards, because there's a slight possibility there will > be 1 or 2 duplicates, which will make the creation fail. I now have the > possibility to specify UNIQUE upfront (in the TABLE definition) or manually > filter out any doubles before the insert (which will also take CPU time). > Would there be any advantage doing this manually, or will SQLite do it just > as efficiently? I suspect the way you are going to manually filter duplicates will involve inserting them into a moral equivalent of a UNIQUE index. The performace is probably going to be similar to that of just creating a UNIQUE index up front. But only measurements with your particular data can tell for sure. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by creating function is not sorting for input order and alphabet
On 10/24/2011 8:19 PM, ChingChang Hsiao wrote: case SQLITE_INTEGER: sqlite3_result_int64( context, sqlite3_value_int64(argv[0]) ); break; case SQLITE_NULL: sqlite3_result_null( context ); break; If you want to just return an argument unchanged, you can use sqlite3_result_value. In this case, you can probably do this whenever the type is anything other than SQLITE_TEXT. string token; token = sqlSortHelper.GetAlphaNumericOrderToken( (char*)sqlite3_value_text(argv[0]) ); sqlite3_result_text( context, token.c_str(), token.length(), NULL ); NULL as the last parameter of sqlite3_result_text is the same as SQLITE_STATIC, telling SQLite that the string will persist sufficiently long beyond sqlite3_result_text call. This is not the case here - the string is destroyed and becomes garbage soon after sqlite3_result_text call. You should be passing SQLITE_TRANSIENT as the last parameter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] triggers : NEW keyword with multiple tables
On 10/25/2011 10:59 AM, Sébastien Escudier wrote: Hello, I used to do something like this on older sqlite versions : (this does not really makes sense here, but this is a simplified) CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER JOIN table2 ON table1.id = table2.id; CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view BEGIN INSERT INTO table1(type) VALUES(NEW.table1.type); INSERT INTO table2(type) VALUES(NEW.table2.type); END; Try NEW."table1.type" and NEW."table2.type" -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove from mailing list please.
On 10/26/2011 9:49 PM, G. J. Weinberger wrote: Please remove me from mailing list. One kind person helped me; as for the rest, the issues discussed are way over my head. Every message you get from the list displays a link at the bottom where you can go to edit your membership. Here it is again: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ORDER BY disregarded for GROUP BY
On 10/28/2011 12:42 PM, Tobias Sjösten wrote: But when I group it by 'g' it completely disregards the ordering: SELECT g,v FROM t GROUP BY g ORDER BY v ASC; a|3 b|3 What seems to be the problem? The resultset is ordered by the second column, isn't it? What did you expect to happen differently? Using descending order does not matter, btw. The result is the same. You have two identical values - why would you expect the order to matter? I'm not sure if this is the expected behavior in SQLite but for me it certainly wasn't. I was thinking SQLite would act as MySQL and respect the ordering when grouping. I'm pretty sure MySQL would produce an error on your query. I don't believe it allows a column that is neither in a GROUP BY clause nor in an argument of an aggregate function. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referring to column alias
On 10/28/2011 4:28 PM, Pete wrote: I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst ... gives an error " no such column: Total". I can just repeat "col1 - col2" of course, but wondering if there is a way to refer to Total within the SELECT. This is by design, blessed by SQL-92 standard. The closest you can get is something like SELECT Total, Total * price FROM (select col1 - col2 as Total, price from tst); This will likely be noticeably slower though. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
