[sqlite] Handling the whole select query from an index
On 20 Sep 2015, at 11:18pm, James K. Lowden wrote: > Rob Willett wrote: > >> Thanks. We?ve got 100 requests a second which may be enough. We?ll >> keep looking though for any time. > > I suppose you know that 100/sec is about 0.1% of what the machine is > capable of. SQLite is really a trivial part of this. Doing things in the obvious way OP is triggering SQLite commands from a web-based interface, with a new process created for each command. Then the new process needs a SQLite database connection and only then can you get down to issuing a SQLite command. OP could redesign his app to call a persistent SQLite server which runs constantly in the background, holding a connection to the SQLite database open. Requires more memory and more programming but it should speed up things quite a bit. Simon.
[sqlite] disable update's & delete's via triggers?
On 20 Sep 2015, at 11:13pm, James Hartley wrote: > As I look at the flowchart for triggers at the following: > > https://www.sqlite.org/lang_createtrigger.html > > It appears that the body of a trigger must specify some kind of statement. > > I ask because I am wanting to log all activity on another table. I can > successfully do this with triggers. What I am curious to do is disable > UPDATE's & DELETE's on the log tables themselves. You can create TRIGGERs which operate BEFORE, AFTER or INSTEAD OF. So perhaps just create an INSTEAD OF trigger which does nothing. Perhaps a SELECT command or a DELETE command with a WHERE clause which is never satisfied (e.g. rowid < 0). Then your next concern is over how to zero-out the log if you really needed to do that. Simon.
[sqlite] disable update's & delete's via triggers?
On 9/20/2015 6:13 PM, James Hartley wrote: > I ask because I am wanting to log all activity on another table. I can > successfully do this with triggers. What I am curious to do is disable > UPDATE's & DELETE's on the log tables themselves. Something along these lines, perhaps: create trigger no_delete before delete on log_table begin select raise(IGNORE); end; -- Igor Tandetnik
[sqlite] disable update's & delete's via triggers?
I appreciate the discussion thus far. I tried the following syntax: CREATE TRIGGER update_history_table_trigger INSTEAD OF UPDATE ON history_table BEGIN UPDATE history_table SET new_value = 1 WHERE 0 = 1; END ; CREATE TRIGGER delete_history_table_trigger INSTEAD OF DELETE ON history_table BEGIN DELETE FROM history_tble WHERE 0 = 1; END ; ...received the following error: "cannot create INSTEAD OF trigger on table: history_table" Googling revealed the following link from 2012: http://stackoverflow.com/questions/9487381/why-sqlite-error-cannot-create-instead-of-trigger-on-table ...which states that INSTEAD OF triggers are to be used with views, not tables. Is this still true? I'm using SQlite 3.8.9. Thanks, again. On Sun, Sep 20, 2015 at 6:34 PM, Simon Slavin wrote: > > On 21 Sep 2015, at 12:21am, James Hartley wrote: > > > I am assuming that dropping the trigger > > will re-enable any action which had been disabled by creating the > trigger. > > > > Is this incorrect? > > Nope. That's one way to do it. Another is to set "PRAGMA > recursive_triggers = OFF" then have a TRIGGER make changes to the table. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
hi, Scott Robison wrote: > > 3. Some time passes and some external process may alter the schema. > is it really possible ? if the answer is yes (I thought when a process open a communication with the DB, other processes can't alter the DB), for me that would mean that the design(s) of the program(s) wich communicates with the DB is bad. If I would like several process to communicate with a DB, I write first a `demon` wich receive/send the querries one by one to the DB and send one-by-one the results to the processes. So with that design I consider avoiding any overlapping. regards, nicolas
[sqlite] I don't understand how to use NOT EXISTS
On 20 Sep 2015, at 7:15pm, James K. Lowden wrote: > Simon Slavin wrote: > >> Constructions like this >> >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) >>> SELECT 'evil little sister' >> >> should be rewritten so that you are not trying to do a SELECT in the >> middle of your INSERT. > > Why in the world would you say that? That's the SQL assignment > function, the equivalent of > > A = A + B I would argue that that would be UPDATE, not INSERT. But I see your side of the issue. > Anything you'd do to rewrite it would result in something more complex, > doubly so if it included the atomicity guarantee of the original. Suppose the SELECT doesn't return anything. Do you still want to do the INSERT ? Do you now have to look up the values to INSERT elsewhere ? Put the commands in a transaction if that worries you. You're right -- I should probably have written "I preffer" instead if making it an absolute statement. Simon.
[sqlite] I don't understand how to use NOT EXISTS
On Sun, 20 Sep 2015 19:33:35 +0100 Simon Slavin wrote: > On 20 Sep 2015, at 7:15pm, James K. Lowden > wrote: > > > Simon Slavin wrote: > > > >> Constructions like this > >> > >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > >>> SELECT 'evil little sister' > >> > >> should be rewritten so that you are not trying to do a SELECT in > >> the middle of your INSERT. > > > > Why in the world would you say that? That's the SQL assignment > > function, the equivalent of > > > > A = A + B > > I would argue that that would be UPDATE, not INSERT. But I see your > side of the issue. I actually think that's the crux of the matter, Simon. If you think you're operating on a row, then A = A + B is UPDATE, yes. If you think you're operating on tables, then A = A + B is INSERT, and UPDATE is more like A = (A - C) + B where C is the set of rows being replaced by B. > Suppose the SELECT doesn't return anything. Do you still want to do > the INSERT ? If I said WHERE NOT EXISTS, why would I still want to "do the insert"?! I'm with Yoda here: there is no try. > Do you now have to look up the values to INSERT elsewhere ? No, I do not, not if the values I didn't insert can be specified, e.g. insert into S select * from T where exists (select 1 from R where ... ); If the values can't be specified in like manner, that would suggest to me a problem with the database design. TIMTOWTDI, for sure. More than one way to think about it, too. But I see no downside to using INSERT...SELECT, except that it's startling to someone unfamiliar with it. --jkl
[sqlite] disable update's & delete's via triggers?
On Sun, Sep 20, 2015 at 5:20 PM, Simon Slavin wrote: > > Then your next concern is over how to zero-out the log if you really > needed to do that. > > That is a valid concern, however, I am assuming that dropping the trigger will re-enable any action which had been disabled by creating the trigger. Is this incorrect?
[sqlite] Handling the whole select query from an index
On Sat, 19 Sep 2015 14:24:24 +0100 Rob Willett wrote: > Thanks. We?ve got 100 requests a second which may be enough. We?ll > keep looking though for any time. I suppose you know that 100/sec is about 0.1% of what the machine is capable of. You spoke of read-only data that changes infrequently, and you wanted maximum speed. I would sort them into a static C array, and use std::lower_bound to search it. I would expose that as a function in a shared library, and publish updates by updating the shared library. I would expect at least 100,000 invocations per second, with the added benefit that the iterator returned by lower_bound instantly answers the question of existence for the provide string. Everything DHR said of advantages to using SQLite is true. If what you want is to minimize lookup time on static data, though, searching sorted data will give you better locality of reference and fewer machine instructions than any interpreted b-tree. HTH. --jkl
[sqlite] using mprint as a global variable inside a query
Here is still a follow up of last weeks discussion "Worked in 3.8.9
but no longer in .." which is about the following case:
a large table includes a datetime field. From the points in time one
wants to report time intervals (from - to).
The datetime field is indexed.
The fastest solution that was proposed involves the creation of a temp
table.
Na?vely I searched a faster solution that should just do a single scan
of the existing index on datetime. And to report intervals this
somehow needs to access the result of two rows at the time, say the
current row and the previous row.
Assuming the execution plan is as expected, this can be achieved with
a sort of global variable. That is to be set at each row and hold the
point in time and recalled at the next row to report it as the from
date.
An SQL function is required then to set and get the value. I had a
look at sqlite3_mprintf and this comes close if it would write the
string in some global variable.
Based on that I created function mprint() as limited version of
mprintf. It accepts no format, only a value parameter. Furthermore, if
called with no parameter at all, it returns the last printed value.
The query is now:
.load mprint
CREATE TABLE securities(calc_date);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE
x<630)
INSERT INTO securities(calc_date)
SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0)
FROM c;
CREATE INDEX securities_calcdate ON securities(calc_date);
select mprint() as calc_date0, --previous value
mprint(calc_date) as tmw --new value
from (select mprint(''))--initial value
join securities
group by calc_date
having strftime('%w',calc_date)<>'0'
;
and the result is as expected and comes almost twice as fast.
Possibly I am a bit flattered by this result,
Is a function like this of any gene ral use?
Should I mail the extension source?
~
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Sun, Sep 20, 2015 at 5:53 PM, Nicolas J?ger wrote: > hi, > > > Scott Robison wrote: > > > > > 3. Some time passes and some external process may alter the schema. > > > > is it really possible ? if the answer is yes (I thought when a process > open a communication with > the DB, other processes can't alter the DB), for me that would mean that > the design(s) of the > program(s) wich communicates with the DB is bad. > > If I would like several process to communicate with a DB, I write first a > `demon` wich receive/send > the querries one by one to the DB and send one-by-one the results to the > processes. So with that > design I consider avoiding any overlapping. > It is possible and is by design. Whether or not you have to worry about this is a different story: if you don't have any processes changing the schema, it is unlikely the schema will change under you. The reason the sqlite3_prepare_v2 interfaces were created was because of the need to recompile queries if the schema changed. The older interfaces returned an error when the schema changed, and the v2 interfaces automated the process. The only time this should be possible is after a prepared statement has been stepped until done, then restarted. There is a window of opportunity where another process can execute other queries, including schema changing queries. > > regards, > nicolas > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Sun, Sep 20, 2015 at 5:47 PM, Simon Slavin wrote: > > On 21 Sep 2015, at 12:32am, Scott Robison wrote: > > > 4. Step through the prepared statement from #1 again after doing any > > necessary reset. No finalize / re-prepare step. > > I believe that if you have done the first _step() then the SELECT will not > notice any schema change until you have received SQLITE_DONE or done > _reset() or _finalize(). In other words, you don't have to worry about the > issue. It might be interesting to check this with and without WAL > journalling. > > I agree that if you do a _reset() then you might have to worry about > columns being changed. I believe that _reset() releases locks, just like > the other two things I mentioned above. > Right, and that seems to be the crux of the OP question. Can he tell when a statement has been recompiled due to a schema change, because it might invalidate information he has cached locally in process. This may be a case of premature optimization, worrying about how much time a language boundary transition might take. Or perhaps the OP has found a use case where regular but not frequent schema changes might be called for. -- Scott Robison
[sqlite] disable update's & delete's via triggers?
Not really. Normal increment of the rowid increments by 1: sqlite> create table x(x); sqlite> insert into x (rowid, x) values (-100, 1); sqlite> insert into x values (2); sqlite> insert into x values (3); sqlite> insert into x values (4); sqlite> select rowid, x from x; -100|1 -99|2 -98|3 -97|4 AUTOINCREMENT will make the value "greater than the largest value that has ever been in the table" and will indeed make sure that autoincremented rowid's are greater than 0, however, it does not prevent a rowid with a value less than 0. sqlite> create table y(rowid integer primary key autoincrement, x); sqlite> insert into y (rowid, x) values (-100, 1); sqlite> insert into y (x) values (2); sqlite> insert into y (x) values (3); sqlite> insert into y (x) values (4); sqlite> delete from y where x=4; sqlite> insert into y (x) values (5); sqlite> select * from y; -100|1 1|2 2|3 4|5 sqlite> > On 20 Sep 2015, at 11:59pm, Keith Medcalf wrote: > > > The RowID is an integer. It is perfectly possible to have RowID's with > a value less than 0. > > > > sqlite> create table x(x); > > sqlite> insert into x (rowid, x) values (-1, -1); > > sqlite> select rowid, x from x; > > -1|-1 > > Yep. But it's unlikely in a world where you allow it to be set by > autoincrement. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Sun, Sep 20, 2015 at 8:11 AM, Simon Slavin wrote: > > On 20 Sep 2015, at 2:55pm, gwenn wrote: > > > But if a column is added to one table in your database, your statement > > is successfully/transparently recompiled (when calling sqlite3_step) > > and the column count may be changed. > > Let's assume that you have not used any PRAGMAs to defeat the concurrency > and locking operations of SQLite, to share the cache, or anything else > which is obviously unsafe in a multi-access scenario. If this is so then > you do not have to worry about this problem. > > Depending on your settings one of two things will happen. > > A) Use of ALTER TABLE ... ADD COLUMN requires locking the database. This > will not be permitted because the database will already be locked, from the > first execution of sqlite3_step() until sqlite3_step() returns SQLITE_DONE > or an error message. > > B) The connection executing sqlite3_step() reads from the database. The > connection executing ALTER TABLE ... ADD COLUMN creates a modification of > the database which the first connection can't see. It won't be able to see > that change until it has released its lock on the database. > > Which of these happens depends at least on the journal mode of the > database, and perhaps on other things I can't think of right now. But it > will always be one or the other. > If I am understanding correctly, what the OP wants to know is: 1. Prepare a statement such as "select * from some_table". 2. Step through it, caching column count information after the first step. 3. Some time passes and some external process may alter the schema. 4. Step through the prepared statement from #1 again after doing any necessary reset. No finalize / re-prepare step. 4a. The column count from the first run of the query was cached back during step 2. Can it be determined if the schema changed without making an "expensive" call to get the column count again? Obviously sqlite is able to detect that the schema changed and only re-prepare the query if necessary. Is that functionality available to ordinary applications? I suspect that the answer is either "no" it is not directly available, or it will involve calling an equally expensive API function to detect the schema signature value. Either way, something will have to be called in the sqlite3 api and that will be potentially expensive if it involves a boundary transition in JNI or go or whatever examples were cited previously. -- Scott Robison
[sqlite] disable update's & delete's via triggers?
On Sun, Sep 20, 2015 at 5:18 PM, Simon Slavin wrote: > > On 20 Sep 2015, at 11:59pm, Keith Medcalf wrote: > > > The RowID is an integer. It is perfectly possible to have RowID's with > a value less than 0. > > > > sqlite> create table x(x); > > sqlite> insert into x (rowid, x) values (-1, -1); > > sqlite> select rowid, x from x; > > -1|-1 > > Yep. But it's unlikely in a world where you allow it to be set by > autoincrement. > Then just go with something like ((rowid - rowid) <> 0). The basic premise still holds that it should be easy to come up with a condition that is always false. -- Scott Robison
[sqlite] disable update's & delete's via triggers?
As I look at the flowchart for triggers at the following: https://www.sqlite.org/lang_createtrigger.html It appears that the body of a trigger must specify some kind of statement. I ask because I am wanting to log all activity on another table. I can successfully do this with triggers. What I am curious to do is disable UPDATE's & DELETE's on the log tables themselves. Can anyone confirm that this is not allowed with the current triger grammar? Thanks!
[sqlite] disable update's & delete's via triggers?
> You can create TRIGGERs which operate BEFORE, AFTER or INSTEAD OF. So > perhaps just create an INSTEAD OF trigger which does nothing. Perhaps a > SELECT command or a DELETE command with a WHERE clause which is never > satisfied (e.g. rowid < 0). The RowID is an integer. It is perfectly possible to have RowID's with a value less than 0. sqlite> create table x(x); sqlite> insert into x (rowid, x) values (-1, -1); sqlite> select rowid, x from x; -1|-1
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
Sorry for my bad description. Imagine that you have a cache of statements (like in tclsqlite.c) created using sqlite3_prepare_v2. And you want to avoid calling sqlite3_column_count/sqlite3_column_name each time you reuse (sqlite3_step) the same statement (with Java (JNI) or Go (cgo), a native call is not cheap). Everything works fine if the database schema is not touched. But if a column is added to one table in your database, your statement is successfully/transparently recompiled (when calling sqlite3_step) and the column count may be changed. My problem is that I don't know that the statement has been recompiled and that I need to invalidate/update the column count. If there is no way to know that the statement has been recompiled, I guess that the column count should not be cached... But maybe there is already such feature provided by the SQLite API ? Regards. On Sat, Sep 19, 2015 at 10:14 PM, R.Smith wrote: > > > On 2015-09-19 06:41 PM, gwenn wrote: >> >> SQLITE_SCHEMA is returned only on failure. >> But, here, there is not failure. >> >> Maybe I should not store the column count ? >> Maybe I should look at schema version >> (http://sqlite.org/pragma.html#pragma_schema_version): >> "The schema version is used by SQLite each time a query is executed to >> ensure that the internal cache of the schema used when compiling the >> SQL query matches the schema of the database against which the >> compiled query is actually executed." >> Is there a better solution ? > > > I too may be reading this thread wrong, I am not sure we're on the same > page, but I /THINK/ maybe you are worrying about an unnecessary thing. > The only way the column count can change for a prepared statement object, is > if you changed it. > If you have a prepared statement like "SELECT * FROM t..." then the > definition might change once the SCHEMA has changed - but that will only > have effect outside of the implicit transaction you are in, as in you will > only notice when you reset the prepared statement - and you should always > check column counts then. > > The bit I am not sure about is whether, after a schema change and reset, > whether that select statement will give an error or simply recompile with a > new column count - but that can be checked rather easily and then respond > accordingly. > > Hope this makes some sense, > Ryan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 20 Sep 2015, at 2:55pm, gwenn wrote: > But if a column is added to one table in your database, your statement > is successfully/transparently recompiled (when calling sqlite3_step) > and the column count may be changed. Let's assume that you have not used any PRAGMAs to defeat the concurrency and locking operations of SQLite, to share the cache, or anything else which is obviously unsafe in a multi-access scenario. If this is so then you do not have to worry about this problem. Depending on your settings one of two things will happen. A) Use of ALTER TABLE ... ADD COLUMN requires locking the database. This will not be permitted because the database will already be locked, from the first execution of sqlite3_step() until sqlite3_step() returns SQLITE_DONE or an error message. B) The connection executing sqlite3_step() reads from the database. The connection executing ALTER TABLE ... ADD COLUMN creates a modification of the database which the first connection can't see. It won't be able to see that change until it has released its lock on the database. Which of these happens depends at least on the journal mode of the database, and perhaps on other things I can't think of right now. But it will always be one or the other. Simon.
[sqlite] I don't understand how to use NOT EXISTS
On Wed, 16 Sep 2015 08:23:04 +1000
Barry Smith wrote:
> As for your original query: think about just the select clause (you
> can run it independently). This will return ('magnetohydrodynamics',
> 1) for each row in the table.
It took me a bit to understand what you meant. I also think there's a
better answer than resorting to LIMIT 1.
To clarify, the OP's query is (reformatted):
> INSERT INTO TAGS ( NAME, COUNT )
> SELECT 'magnetohydrodynamics', 1
> FROM TAGS -- <- the error
> WHERE NOT EXISTS (
> SELECT * FROM TAGS
> WHERE NAME = 'magnetohydrodynamics'
> );
The existence test is against the whole TAGS table. As long as the
name 'magnetohydrodynamics' appears in the table, SELECT will return
zero rows. But -- your point -- if the name tested does *not* appear in
the table, SELECT will return as many rows as are in the table.
The solution is simply to say what's meant instead:
INSERT INTO TAGS ( NAME, COUNT )
SELECT 'magnetohydrodynamics',1
WHERE NOT EXISTS (
SELECT 1 FROM TAGS
WHERE NAME = 'magnetohydrodynamics'
);
As to the OP's question about where he went wrong, the query as
presented should not have created the results he showed. Those results
could be explained, though, if "magnetohydrodynamics" was misspelled in
the WHERE clause. I suspect that's what went wrong.
--jkl
[sqlite] I don't understand how to use NOT EXISTS
On Wed, 16 Sep 2015 19:40:23 +0100 Simon Slavin wrote: > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. Why in the world would you say that? That's the SQL assignment function, the equivalent of A = A + B Anything you'd do to rewrite it would result in something more complex, doubly so if it included the atomicity guarantee of the original. --jkl
[sqlite] Fwd: OT: Oracle functions for SQlite
On 13 September 2015 at 10:06, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > Hello ! > > Due the way sqlite manages it's source code (with fossil-scm) I propose to > anyone that has any extension/custom sqlite code fork this project on > github: > > > https://github.com/mackyle/sqlite > > And publish it there. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > Is there an equivalent collection of Tcl - sqlite functions anywhere? I have my own 'initcap' and 'decode' functions. John Gillespie
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 9/20/2015 9:55 AM, gwenn wrote: > If there is no way to know that the statement has been recompiled, I > guess that the column count should not be cached... You could use sqlite3_prepare (no _v2), then you'd get an error on schema change. You would then re-prepare the statement and update your caches. -- Igor Tandetnik
