Re: [sqlite] [EXTERNAL] Trigger Question

[Stephen Chrzanowski]

Thanks for this.  I've not implemented this yet as I discovered a minor
issue with the wrapper I'm using in that it doesn't like sending multiple
SQL commands in one go, so I need to adapt my code, or adapt the wrapper to
allow for multiple statements.

On Thu, Oct 12, 2017 at 9:38 AM, David Raymond 
wrote:

> At the bottom of http://www.sqlite.org/lang_createtrigger.html you can
> find the raise function info and decide which of ignore, rollback, abort or
> fail works best for you. Descriptions here: http://www.sqlite.org/lang_
> conflict.html
>
> Insert the priority 0 row first, then do something like...
>
> create trigger pri0_stop_insert
> before insert on ColorScheme
> when new.PriorityLevel = 0
> begin
>   select raise(abort, 'No inserting priority 0 colors');
> end;
>
> create trigger pri0_stop_update
> before update on ColorScheme
> when old.PriorityLevel = 0
> begin
>   select raise(abort, 'No updating priority 0 colors');
> end;
>
> create trigger pri0_stop_delete
> before delete on ColorScheme
> when old.PriorityLevel = 0
> begin
>   select raise(abort, 'No deleting priority 0 colors');
> end;
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Hick Gunter
> Sent: Thursday, October 12, 2017 3:01 AM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] [EXTERNAL] Trigger Question
>
> INSTEAD OF triggers are only allowed on views.
>
> From the documentation I would suggest using a BEFORE trigger and calling
> RAISE() in the trigger program. I expect RAISE(IGNORE) to silently abort
> the calling statement.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Stephen Chrzanowski
> Gesendet: Donnerstag, 12. Oktober 2017 08:36
> An: General Discussion of SQLite Database  sqlite.org>
> Betreff: [EXTERNAL] [sqlite] Trigger Question
>
> I'm setting up a resource string to drop, recreate, and repopulate a table
> to use as a type of default values for colors on the UI.  Essentially a
> default set of priority levels and FG/BG colors associated with that
> priority color.
>
> The table schema is as such:
>
> CREATE TABLE [ColorScheme](
> [PriorityLevel] INTEGER PRIMARY KEY ON CONFLICT REPLACE NOT NULL,
> [ForegroundColor] BIGINT NOT NULL DEFAULT 0,
> [BackgroundColor] BIGINT NOT NULL DEFAULT 12632256,
> [PriorityText] CHAR NOT NULL);
>
> I then insert a bunch of default values I want, looking like this:
>
> insert into ColorScheme values
> (0,000+000*256+000*65536,192+192*256+192*65536,'Unknown');
>
> Essentially hard coding the default color when I display something on the
> UI that is of priority level 0.
>
> What my objective is, once I populate the initial set of data, I want to
> block any and all attempts that my code COULD do to update this exact row
> above.  So on any insert, delete, or update, if PriorityLevel=0, I want a
> NOOP.  This row does not update, it does not get deleted, this row
> essentially becomes bulletproof until I delete the table or trigger.  The
> user is not informed of the inaction.
>
> Since I'm never going to have a priority of -1, I decided to try a trigger
> like this:
>
> Create Trigger ColorSchemeInsert instead of insert on ColorScheme for each
> row when NEW.PriorityLevel=0 begin delete from ColorScheme where
> PriorityLevel=-1; end;
>
> However, SQLite Expert is throwing an error saying "cannot create INSTEAD
> OF trigger on table: ColorScheme".
>
> If I remove the INSTEAD OF statement, the trigger is created without
> error, BUT, based on this ON CONFLICT REPLACE statement, the insert happens
> anyways.
>
> Thoughts?
>
> (For now, I'm not going to worry about the trigger, but it is something I
> want in) ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
> 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Wout Mertens]

I feel safer now :)

On Fri, Oct 13, 2017, 12:57 PM Rowan Worth  wrote:

> In that case you would be well advised to use a monotonic clock source,
> rather than a "date-generating" clock. In linux this is the difference
> between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
> to clock_gettime().
>
> But any API you might use to set a trigger for 2 seconds into the future is
> probably already based on a monotonic clock. Polling a realtime clock would
> make for a pretty convoluted implementation!
>
> -Rowan
>
> On 13 October 2017 at 18:42, Wout Mertens  wrote:
>
> > Thank you, very interesting!
> >
> > The leap second behavior is slightly worrying, basically anything
> > time-based (animations etc) will take a second longer? What if you want
> an
> > engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> > then it's burning 50% longer?
> >
> > On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> > wrote:
> >
> > > This has not much to do with the original question, but as physicist I
> > > cannot resist:
> > >
> > > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> > name
> > > just a few) provide reference times in UTC, which are distributed
> > nowadays
> > > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > > computers, smart phone etc. are, if at all, synchronized more or less
> > > successfully to UTC, and the timestamps that a software like Sqlite
> > handles
> > > are in the vast majority UTC, possibly plus a timezone offset. For
> > example,
> > > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > > 2017.*
> > >
> > > What can go wrong?:
> > >
> > > 1) The local clock is synchronized now and then, in some cases as a
> > > consequence of the well-know leap seconds, by setting it abruptly to a
> > new
> > > time. Obviously this does not guarantee that the timestamps become
> > ordered
> > > the same as the events really happened. In the worst case a timestamp
> has
> > > the wrong day or even the wrong year (with potentially legal
> > consequences,
> > > e.g. for financial transactions).
> > >
> > > When is it right?:
> > >
> > > 2) Using the information from NTP, only the clock speed is adjusted to
> > > compensate for drifts. Leap seconds are announced in advance via NTP.
> But
> > > none of the major operating systems, Windows, Linux, Unix can
> internally
> > > represent times within leap seconds. Therefore the system clock is
> halted
> > > for the leap second. Calls for the system time within a leap second
> > return
> > > time stamps just before the leap second, having a small difference
> > between
> > > them such that their order is correct.
> > >
> > > Sqlite and applications are here at the mercy of the underlying system,
> > no
> > > matter how the time at the Sqlite level is presented, as floating point
> > > Julian day numbers, (milli- or micro) second counters from a certain
> > point
> > > in time (epoch) or so. Normally leap seconds don't need to be
> > > representable, as Sqlite/applications are not going to get exposed to
> > such
> > > time stamps (all the OSs cannot). But any timestamps are almost
> certainly
> > > (supposed to be) UTC, plus timezone offset.
> > >
> > > Finally UT1:
> > >
> > > 1) Unless you need to do stuff like tracking satellites, planets, stars
> > and
> > > other celestial objects with high precision from the Earth, you don't
> > need
> > > to know what it is.
> > >
> > > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> > in
> > > SI seconds to UTC, distinguishing between predicted and final
> correction.
> > > As such UT1 does not have seconds, days etc. You can of course define a
> > UT1
> > > day as between the times when the corrected UTC passes midnight, and
> then
> > > divide this "day" into 86400 "seconds". From the physical viewpoint
> this
> > > would be a bit weird because these seconds then have a different length
> > > than the standard SI second, and their length also varies from day to
> > day.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Keith Medcalf]

Interesting, because you cannot have two rows (two tile_id) for the same 
combination of tile_zoom / tile_row / tile_column since the latter are a 
required to be unique primary keys.  That is, the map table is declared as if 
it were the dictionary:

map[tile_zoom, tile_row, tile_column] = tile_id

Unless your keys are not of the correct type somewhere, it is impossible to 
have the same dictionay selectors have two results.  You need to check the 
typeof() each of your keys aka:

select *, typeof(tile_zoom), typeof(tile_row), typeof(tile_column), 
typeof(tile_id), '"' || tile_id || '"' from map where ;

is returning duplicate rows.  You must have one of them with an incorrect 
(different) type.  Also, your tile_id could have "trailing spaces" that you 
cannot see thus counfounding your delete request (which will be shown by the 
above select).

so your delete statement is effectively:

if map[tile_zoom, tile_row, tile_column] == tile_id:
del map[tile_zoom, tile_row, tile_column]

Have you run an integrity_check on the database to make sure it is not 
corrupted (because it certainly appears that it is).  Either that or you have 
simply inserted some bad data.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Fiona
>Sent: Thursday, 12 October, 2017 20:33
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not
>working with large db file(>280GB)
>
>>> SQLite does none of those.  Have your program print out the actual
>command
>it’s trying to execute.  Then try typing it manually.  See if it
>works when
>you type it by hand.
>
>Yes, I manually type all the command, also I check all the records I
>wanna
>delete by SELECT with the same where clause. Here are the scripts.
>
>schema:
>
>
>delete/insert operations:
>
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Simon Slavin]

On 13 Oct 2017, at 3:32am, Fiona  wrote:

> delete/insert operations:
>  

The two screenshots are useful.  Your two commands do not have the same WHERE 
clause.  I agree that it looks like they should have the same result, but 
obviously this is not working.

Please take a backup copy of your database.  Then, using the SQLite shell tool, 
starting with the same PRAMGA settings, I would like to see the results from 
these lines

.mode quote
PRAGMA count_changes;
PRAGMA integrity_check;
SELECT COUNT(*) FROM map WHERE tile_id='a37e1dba ….';
SELECT * FROM map WHERE zoom_level=18 AND tile_column=214233 AND 
tile_row=147702 AND tile_id='a37e1dba ';
DELETE FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702 
AND tile_id='a37e1dba ';
SELECT * FROM map WHERE zoom_level=18 AND tile_column=214233 AND 
tile_row=147702;
DELETE FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702;

Use the full value for tile_id where indicated.  I may also have made a mistake 
typing the other values above.  Please check they match yours.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Kees Nuyt]

On Thu, 12 Oct 2017 19:32:53 -0700 (MST), Fiona
 wrote:

> schema:
>  

Not related to your problem, just a hint:
Swapping the columns tile_data and tile_id may improve
performance significantly, especially if the BLOB can get bigger
than a database page.

Original:
CREATE TABLE images (
tile_data BLOB
,   tile_id VARCHAR(256) NOT NULL
,   PRIMARY KEY (tile_id)); -- might be slow

Improved:
CREATE TABLE images (
tile_id VARCHAR(256) NOT NULL
,   tile_data BLOB
,   PRIMARY KEY (tile_id)); -- could be faster


-- 
Regards,
Kees Nuyt

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Rowan Worth]

In that case you would be well advised to use a monotonic clock source,
rather than a "date-generating" clock. In linux this is the difference
between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
to clock_gettime().

But any API you might use to set a trigger for 2 seconds into the future is
probably already based on a monotonic clock. Polling a realtime clock would
make for a pretty convoluted implementation!

-Rowan

On 13 October 2017 at 18:42, Wout Mertens  wrote:

> Thank you, very interesting!
>
> The leap second behavior is slightly worrying, basically anything
> time-based (animations etc) will take a second longer? What if you want an
> engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> then it's burning 50% longer?
>
> On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> wrote:
>
> > This has not much to do with the original question, but as physicist I
> > cannot resist:
> >
> > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> name
> > just a few) provide reference times in UTC, which are distributed
> nowadays
> > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > computers, smart phone etc. are, if at all, synchronized more or less
> > successfully to UTC, and the timestamps that a software like Sqlite
> handles
> > are in the vast majority UTC, possibly plus a timezone offset. For
> example,
> > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > 2017.*
> >
> > What can go wrong?:
> >
> > 1) The local clock is synchronized now and then, in some cases as a
> > consequence of the well-know leap seconds, by setting it abruptly to a
> new
> > time. Obviously this does not guarantee that the timestamps become
> ordered
> > the same as the events really happened. In the worst case a timestamp has
> > the wrong day or even the wrong year (with potentially legal
> consequences,
> > e.g. for financial transactions).
> >
> > When is it right?:
> >
> > 2) Using the information from NTP, only the clock speed is adjusted to
> > compensate for drifts. Leap seconds are announced in advance via NTP. But
> > none of the major operating systems, Windows, Linux, Unix can internally
> > represent times within leap seconds. Therefore the system clock is halted
> > for the leap second. Calls for the system time within a leap second
> return
> > time stamps just before the leap second, having a small difference
> between
> > them such that their order is correct.
> >
> > Sqlite and applications are here at the mercy of the underlying system,
> no
> > matter how the time at the Sqlite level is presented, as floating point
> > Julian day numbers, (milli- or micro) second counters from a certain
> point
> > in time (epoch) or so. Normally leap seconds don't need to be
> > representable, as Sqlite/applications are not going to get exposed to
> such
> > time stamps (all the OSs cannot). But any timestamps are almost certainly
> > (supposed to be) UTC, plus timezone offset.
> >
> > Finally UT1:
> >
> > 1) Unless you need to do stuff like tracking satellites, planets, stars
> and
> > other celestial objects with high precision from the Earth, you don't
> need
> > to know what it is.
> >
> > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> in
> > SI seconds to UTC, distinguishing between predicted and final correction.
> > As such UT1 does not have seconds, days etc. You can of course define a
> UT1
> > day as between the times when the corrected UTC passes midnight, and then
> > divide this "day" into 86400 "seconds". From the physical viewpoint this
> > would be a bit weird because these seconds then have a different length
> > than the standard SI second, and their length also varies from day to
> day.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[R Smith]

On 2017/10/13 12:42 PM, Wout Mertens wrote:

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?


I sincerely hope you are making a bot for Kerbal space program and not 
actually working for NASA :)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Wout Mertens]

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?

On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert  wrote:

> This has not much to do with the original question, but as physicist I
> cannot resist:
>
> National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
> just a few) provide reference times in UTC, which are distributed nowadays
> also via the internet, e.g. the NTP protocol. Therefore clocks of
> computers, smart phone etc. are, if at all, synchronized more or less
> successfully to UTC, and the timestamps that a software like Sqlite handles
> are in the vast majority UTC, possibly plus a timezone offset. For example,
> the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> 2017.*
>
> What can go wrong?:
>
> 1) The local clock is synchronized now and then, in some cases as a
> consequence of the well-know leap seconds, by setting it abruptly to a new
> time. Obviously this does not guarantee that the timestamps become ordered
> the same as the events really happened. In the worst case a timestamp has
> the wrong day or even the wrong year (with potentially legal consequences,
> e.g. for financial transactions).
>
> When is it right?:
>
> 2) Using the information from NTP, only the clock speed is adjusted to
> compensate for drifts. Leap seconds are announced in advance via NTP. But
> none of the major operating systems, Windows, Linux, Unix can internally
> represent times within leap seconds. Therefore the system clock is halted
> for the leap second. Calls for the system time within a leap second return
> time stamps just before the leap second, having a small difference between
> them such that their order is correct.
>
> Sqlite and applications are here at the mercy of the underlying system, no
> matter how the time at the Sqlite level is presented, as floating point
> Julian day numbers, (milli- or micro) second counters from a certain point
> in time (epoch) or so. Normally leap seconds don't need to be
> representable, as Sqlite/applications are not going to get exposed to such
> time stamps (all the OSs cannot). But any timestamps are almost certainly
> (supposed to be) UTC, plus timezone offset.
>
> Finally UT1:
>
> 1) Unless you need to do stuff like tracking satellites, planets, stars and
> other celestial objects with high precision from the Earth, you don't need
> to know what it is.
>
> 2) UT1 is published by the https://www.iers.org/ as a daily correction in
> SI seconds to UTC, distinguishing between predicted and final correction.
> As such UT1 does not have seconds, days etc. You can of course define a UT1
> day as between the times when the corrected UTC passes midnight, and then
> divide this "day" into 86400 "seconds". From the physical viewpoint this
> would be a bit weird because these seconds then have a different length
> than the standard SI second, and their length also varies from day to day.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Script Embedded SQLite With TCL

[yaro]

Thanks for your reply Peter da Silva.
Since I want to embed TCL, my intent is to include only the TCL core code
and SQLite code in my project and build them into one exec file. This way I
can use SQLite in my C++ code as well as in TCL. Is this achievable? Or
should I build TCL with SQLite as the only included package and how can I do
it?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Richard Hipp]

On 10/12/17, Fiona  wrote:
>
> As you can see, insert works, and I can also delete/update this last
> inserted record. It seems some pages of my db file is locked or something.

Before running your query, enter ".mode quote".  Then show us what the
output of this query is:

SELECT typeof(tile_id), tile_id FROM map WHERE zoom_level=18 AND
tile_column=214233 AND tile_row=147702;



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Fiona]

>> SQLite does none of those.  Have your program print out the actual command
it’s trying to execute.  Then try typing it manually.  See if it works when
you type it by hand. 

Yes, I manually type all the command, also I check all the records I wanna
delete by SELECT with the same where clause. Here are the scripts.

schema:
 

delete/insert operations:
 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Fiona]

Sorry about my ambiguous description. 
Here is what I wanna do. Normally I use Python code to insert/update data of
two tables in my sqlite database file: *map and images*, table *map* stores
the indexs of pics, while table *images* stores the contents of these pics.
My Python code works well, untill the db file grows too big: I find some
pics I insert into the db file is incorrect,  then I rerun my Python code to
update these pics with right ones, but the select result of table images is
still not right. So i use command-line tool to test it, and there goes my
problem, I find the update/delete operation not working or giving any error
message, while I can still insert data into the same file.

>> Are you trying to use the CLI  the Python script is doing inserts?
Of course I use the CLI after my Python script finishes all inserts,
otherwise the db file is locked and all my operations in CLI would retrun
error.

>> If you do ".changes on" before running the query, does the reported
>> change count increase?
I can't find this command in my CLI, and I use count_changes,  here is the
schema and my commands. I think my delete operation returns correctly, but
after delete, the select result confuses me. 

Schem:
 

delete:
 

As you can see, insert works, and I can also delete/update this last
inserted record. It seems some pages of my db file is locked or something.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Andy Ling]

Try changing the "DELETE FROM table WHERE " to "SELECT COUNT(1) FROM table 
WHERE " and see if you get a number bigger than 0. If not, then your WHERE 
isn't matching the rows you think it should.

Regards

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fiona
Sent: Fri 13 October 2017 02:19
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with 
large db file(>280GB)

CAUTION - EXTERNAL EMAIL

>> This suggests INSERT works but UPDATE and DELETE does not.  Is this
>> correct ?

Thanks a lot! Yes, that's the situation. I'm using WHERE clause in my
UPDATE/DELETE sentences, and I'm pretty sure the syntax and my shell are not
the problem, because there has no retrun of error, and I also test the same
UPDATE/DELETE sentences with the same shell tool but in a small db file, it
works correctly.

I assume the problem is caused by some kind of *db file consistency check* I
don't know? Or sqlite is doing some *index rebuilding* for my UPDATE/DELETE
operation?  What I really can't understand is why INSERT still works in the
mean time.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users