I'm accessing a database from multiple threads, where each thread has a
separate database connection.
When some specific event happens, I want the threads to evaluate the
situation and act accordingly.
I have setup a temporary trigger which fires at the event and which in
turn calls a
In my little test below, both the "ON DELETE CASCADE" in table_2 and the
(later created) delete_trigger_2 are about to execute removal when a row
in table_1 is removed.
The table_3 also has the "ON DELETE CASCADE", but it doesn't yet have
any content when removing the row in table_1 so it's
I don't understand what condition you might be talking about.
As far as I know the functionality only allows you to simply drop a
trigger, in your case:
DROP TRIGGER trigger_name;
or:
DROP TRIGGER IF EXISTS trigger_name;
Best regards,
Daniel
techi eth skrev 2013-11-01 12:53:
How to
Hello,
I have an SQL query which fetches the requested data from the database,
but is using too much resources when doing so.
I would be happy to get advice on how to think / what to look for when
trying to optimize a query, views, adding indexes, optimizing schema
design and so on.
So far
12: You're correct that they're doing something related, but
is separated on purpose to let the user link data different ways
Thanks alot!
/Daniel
Simon Slavin skrev 2013-11-07 14:08:
On 7 Nov 2013, at 10:52am, Daniel Polski <dan...@agelektronik.se> wrote:
I would be happy to g
Hello!
--Table t1 contains some "base" data
CREATE TABLE t1 (
idINTEGER PRIMARY KEY,
name VARCHAR(10),
value INT NOT NULL
);
INSERT INTO t1 VALUES(1, 't1 1', 0);
INSERT INTO t1 VALUES(2, 't1 2', 0);
--Table t2 can contain extra data for a row in table t1
CREATE TABLE t2(
Den 2015-09-24 kl. 13:03, skrev Simon Slavin:
> On 24 Sep 2015, at 11:38am, Daniel Polski wrote:
>
>> -- Here is where my real question starts. Can I fetch a list of rows in a
>> trigger
>> -- and update different tables from that data?
> Create a VIEW which does this
Hello again,
I think I can simplify the actual question:
Can I use a SELECT returning multiple rows as input to some INSERT
logic, and in that logic INSERT into multiple tables?
Like if I have a table t1 containing id 1, 2, 3.
For each id in t1, do
INSERT INTO t2 (...)
INSERT INTO t3 (...)
I realized I never wrote back to the list about the solution (so others
who might get into the same kind problem can see what the actual problem
was for us).
The problem wasn't caused by the update to a newer version of sqlite. By
coincidence the firmware which included the new sqlite version
Hello,
I have a problem getting SQLITE_BUSY, and the lock won?t get released.
The problem *seems* to have started around the time we upgraded sqlite
from 3.8.10 to 3.9.0, but it's not confirmed. I can?t find anything in
our firmware logic which should be relevant to the changed locking
>> Any suggestions how to debug and find the reason why we get SQLITE_BUSY?
> Have you enabled error and warning logging?
> https://www.sqlite.org/errlog.html
Thanks, I have now.
> Can you enhance your existing log to report the
> sqlite3_extended_errcode() value in addition to the basic
Den 2016-01-21 kl. 11:30, skrev Simon Slavin:
> On 21 Jan 2016, at 9:44am, Daniel Polski wrote:
>
>> The Webserver/PHP can process up to 16 requests simultanuously and will
>> share one database connection among all instances.
>> The process for each request is:
>>
Hello,
BETWEEN doesn't give the result I would need when used with time before
& after midnight:
SELECT time('23:00') BETWEEN time('22:00') AND time ('23:30');
time
1
sqlite> SELECT time('23:00') BETWEEN time('22:00') AND time ('01:00');
time
0
Any suggestion how to tweak the query
Hello,
I'm redesigning parts of my database to normalize it better and to make
a more general solution available for other parts of the system.
My goal now is to create a view which will let me do a select in the
style of:
SELECT (unit id) (general option value) (color value) (number value)
Clemens Ladisch skrev 2012-11-09 17:00:
Thanks alot for the help! :)
Are you sure that the normalization hasn't gone a *little* bit too far?
Yes, it makes perfect sense in other parts of the application.
FOREIGN KEY( unit_id ) REFERENCES unit_types ( id )
Why is this column not called
Simon Slavin skrev 2012-11-09 17:32:
In answer to the original query, my instinct is to say that you should be using
your own software to collect the results of several SELECTs, not trying to do
everything inside a database engine.
That's an interesting point. I still think it's the most
Hello,
If I have a value in a table, and use INSERT OR REPLACE to update the
value, a trigger created with AFTER UPDATE ON won't fire. Shouldn't an
INSERT OR REPLACE to a table which already contain data which are beeing
replaced be considered an "update" of the table?
CREATE table t1 (
Ok thanks!
Simon Slavin skrev 2012-11-14 16:04:
On 14 Nov 2012, at 2:58pm, Daniel Polski <dan...@agelektronik.se> wrote:
If I have a value in a table, and use INSERT OR REPLACE to update the value, a trigger
created with AFTER UPDATE ON won't fire. Shouldn't an INSERT OR REPLACE to a
probability(EXPR, value)
- Would force the user to set their best guess. I would think that the
users guess would be more accurate than a general guess? What's
considered unlikely for the users data, 0.05? 0.20?
I would prefer if the mechanism could be handled with pragmas (or
something)
Hello,
Does the ANALYZE command gather statistics and optimize for views I've
created or only "real tables"?
Best regards,
Daniel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Hello,
I have a hard time finding the cause of a bug in my application. I
believe that it's me doing something wrong and not sqlite since I can't
reproduce the error in a simple example. I have 4 threads mostly reading
data from a database, and all threads open "own" database connections.
Nothing obvious springs to mind but I do see that you are not checking the
values returned by most of your sqlite_exec() calls. Perhaps you could write a
little routine that does the sqlite_exec() and then asserts that the value
returned is SQLITE_OK. It may be that it's actually one of the
One of the other threads is writing at the same time. SQLite only allows a
single writer at a time to a single database file. Others have to wait in
line.
Your solution is that when you get an SQLITE_BUSY, delay for a short while
and then try again. Keep trying until you break through.
Note
Hello again,
Attached is a test application which replicates the problem.
I expected the transactions to block each other exactly like they do in
the beginning (one connection successfully begins and the other receives
SQLITE_BUSY), but I didn't expect the blocked connection to never get
Pavel Ivanov skrev 2012-10-17 16:08:
The problem is you are starting read-only transaction by executing
SELECT and then try to convert this transaction into writing one by
executing BEGIN IMMEDIATE. If in such situation SQLITE_BUSY is
returned you have to finish the transaction and start it
The SELECT statement, including the _prepare() stage and all the _step()s until
you've reached the last row, and then the _finalize(), is all one process.
They're all part of the statement and you can assume that the database is still
locked until you do a _finalize().
If you are using the
Do your applications use any PRAGMAs ?
PRAGMA foreign_keys=ON;
PRAGMA journal_mode=WAL;
Are they running on the same computer both accessing storage held on that same
computer ?
It's different threads within the same application. Threads have "own"
sqlite connections.
There is no helper software, except maybe SQLite itself.
Ok, thanks for the information.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Do you have any suggestion about how to inspect the contents of a wal file?
The reason for the examination is that I suspect my application might
have a bug which makes one thread very occationally not see data in the
WAL file another thread has added.
Den 2016-10-05 kl. 14:51, skrev Michael Schlenker:
Hi,
have a look at the 'showwal.c' file in the sources.
https://www.sqlite.org/src/artifact/ec79959834f7b21f
It allows you to look at the WAL.., but not sure if it fits your problem.
Thanks!
That should be safe too. I hope someone else can help debug your problem.
Many bugs like the one you mentioned are a result of
a) having other parts of your program misuse SQLite file handles
b) having other parts of your program overwrite memory SQLite is using
c) using file storage
Replace != with IS NOT.
Regards,
Clemens
Thanks! Exactly what I looked for.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Hello,
I guess there is something I'm missing when trying to synchronize some
data with a trigger.
How do I get the trigger to fire when the comparison in either new.x or
old.x is null?
The below tested with 3.8.6 sqlite command line shell:
CREATE TABLE table1(
idINTEGER PRIMARY
Thank you, I see.
Any suggestion how to get the trigger to fire if (and only if) the
values are different, including if one of the "sides" of old/new are null?
Den 2016-10-13 kl. 11:56, skrev Kees Nuyt:
On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
<dan...@agelektronik.se>
select unit, sum(1 << bit_position) from table1 where val group by unit;
To make it more complex.. Is it possible to select into "different
bytes" depending on bit_position? (For example that bit_position 0-7
represent byte 1, bit_position 8-15 represent another)
To get a second "byte" I
Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik:
select unit, sum(1 << bit_position) from table1 where val group by unit;
Wow!!
Thanks alot!
/Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
Den 2016-10-17 kl. 16:40, skrev R Smith:
Luckily all data needed is in the table, and Igor's method will work
just dandy in your case. Why not store the flags in full rather than
per line though? You can easily manipulate the values by using
standard bit-masking and boolean bit-wise
Let's say I have a table like this:
CREATE TABLE table1(
idINT,
unitINT,
bit_positionINT,
valBOOL
);
INSERT INTO table1 VALUES(1,1, 0, 1);
INSERT INTO table1 VALUES(2,1, 1, 1);
INSERT INTO table1 VALUES(3,1, 4, 1);
INSERT INTO
The "PRAGMA optimize" looks interesting.
- Can using "PRAGMA optimize" in one thread create a situation where
"BEGIN IMMEDIATE TRANSACTION" in another thread fails? (The threads are
using different connections)
- Is there any risk of "optimization fighting" if several threads are
using the
Den 2017-03-24 kl. 08:09, skrev Daniel Polski:
The "PRAGMA optimize" looks interesting.
- Can using "PRAGMA optimize" in one thread create a situation where
"BEGIN IMMEDIATE TRANSACTION" in another thread fails? (The threads
are using different conn
I've found something weird in a log from a client.
Normally our WAL files are < 100kB, but in this log I noticed the file
was >40MB. This was totally unexpected since we run this call every minute:
int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL,
SQLITE_CHECKPOINT_TRUNCATE,
Den 2017-06-05 kl. 12:34, skrev Richard Hipp:
On 6/5/17, Daniel Polski <dan...@agelektronik.se> wrote:
Den 2017-06-02 kl. 16:07, skrev Richard Hipp:
and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
to completion.
Do you have a busy callback handler registered
Den 2017-06-07 kl. 17:09, skrev Simon Slavin:
On 7 Jun 2017, at 1:49pm, Daniel Polski <dan...@agelektronik.se> wrote:
Ok, have I understood this correctly:
If doing a manual checkpoint with SQLITE_CHECKPOINT_TRUNCATE, that call will
block for maximum the time set by the busy_timeout
Den 2017-06-05 kl. 17:48, skrev Simon Slavin:
On 5 Jun 2017, at 1:45pm, Daniel Polski <dan...@agelektronik.se> wrote:
How do I make the checkpointing work like the above documentation describes?
Set a timeout. Perhaps a very long one (one minute, which is what I use in
some places)
Den 2017-06-02 kl. 16:07, skrev Richard Hipp:
On 6/2/17, Daniel Polski <dan...@agelektronik.se> wrote:
I've found something weird in a log from a client.
Normally our WAL files are < 100kB, but in this log I noticed the file
was >40MB. This was totally unexpected since we run thi
Den 2017-06-02 kl. 16:07, skrev Clemens Ladisch:
Daniel Polski wrote:
Any ideas why I can end up with that large WAL file
Sounds like checkpoint starvation.
Does the checkpoint call actually succeed?
Unfortunately I don't know (adding a log message for that now).
Any suggestions about how
Den 2017-06-07 kl. 15:02, skrev Richard Hipp:
On 6/7/17, Daniel Polski <dan...@agelektronik.se> wrote:
Does the [TRUNCATE] checkpoint call lock out new requests which might prohibit
checkpoint progress while waiting for the timeout?
It prohibits new writers. New readers are a
Hello,
When reading the docs I don't get a clear understanding on what strategy
I could/should use to backup a heavily used database (with frequent
reads & writes).
I have one application (A) with multiple threads reading & writing to
the database. Application (A) handles write locking
Den 2020-03-10 kl. 12:31, skrev Daniel Polski:
Den 2020-03-10 kl. 02:33, skrev Rowan Worth:
On Mon, 9 Mar 2020 at 23:22, Daniel Polski
wrote:
Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to
figure
Den 2020-03-10 kl. 02:33, skrev Rowan Worth:
On Mon, 9 Mar 2020 at 23:22, Daniel Polski wrote:
Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to figure
out when the problem starts.
I don't get the message
Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to figure
out when the problem starts.
I don't get the message in versions <= 3.30.1.
(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened:
51 matches
Mail list logo
