Re: [sqlite] is this possible
On Thursday, 28 March, 2019 13:21, Mark Wagner wrote: >Imagine I have these two tables and one view defining a join. >CREATE TABLE t (foo); >CREATE TABLE s (bar); >CREATE VIEW v as select * from t join s on (foo = q); >I appear to be able to do this query: >select 20 as q, * from t join s on (foo=q); Really, this is: select 20 as q, * from t, s where foo == q; q is an alias for the constant 20. So what you are really saying is this: select 20 as q, * from t, s where foo == 20; which is valid. >But apparently I cannot do this: >sqlite> select 20 as q, * from v; >Error: no such column: q No, because q is not a column in either t or s. >It's interesting because it allows me to define the view and at that >point it knows nothing about q so I would have assumed it could be >"supplied" later. Defining a View is nothing more than storing the definition of the view in a table. It is not parsed until you use it (though it is syntax checked so if you make a syntax error you will be told about then when you attempt to create the view). You can define the view before defining the tables s and t, or after, or betwixt defining them. You can even drop the tables (either or both) and recreate them (or not). However, at the time you want to EXECUTE (use) the view v the tables s and t must exist (or you will get a no such table error), and the columns foo and q must be defined in one of those tables (or you get a no such column error). >Is this just how it is or perhaps my syntax is off? Or maybe I'm >just confused. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. I don't believe you can create a parameterized view. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
From the page https://sqlite.org/lang_createview.html : "The CREATE VIEW command assigns a name to a pre-packaged SELECT statement ..." So, it seems that the command "CREATE VIEW" just creates a name for a SELECT statement, and checks nothing more than syntax. On 28.3.2019 21:21, Mark Wagner wrote: CREATE VIEW v as select * from t join s on (foo = q); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is this possible
Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); I appear to be able to do this query: select 20 as q, * from t join s on (foo=q); But apparently I cannot do this: sqlite> select 20 as q, * from v; Error: no such column: q It's interesting because it allows me to define the view and at that point it knows nothing about q so I would have assumed it could be "supplied" later. Is this just how it is or perhaps my syntax is off? Or maybe I'm just confused. Just curious. Thanks! -- Mark ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JSON1: queries on object keys
Hmmm right, valid points. The queries I'm doing are on slices of data that are preselected using indices, and then right now I'm post-filtering them in the application, and I was just wondering if I could already do better filtering on the db side before paying the serialization costs. In fact, I'm not facing performance issues right now and I'm more idly musing in order to better know the tools at my disposal. I might also be lobbying for a JSON1 function that extracts keys from an object, if that would make sense. Wout. On Thu, Mar 28, 2019 at 7:50 PM Warren Young wrote: > On Mar 28, 2019, at 4:15 AM, Wout Mertens wrote: > > > > - I don't see how json_type can help > > I don’t see “json_type” in this thread at all, other than this message. > > > - Schemaless data is really nice to work with > > Sure, but it has a cost. Unless you’re willing to give us a *lot* more > information, you’ll have to decide if you’re willing and able to pay it, > given your application constraints. > > By “more information,” I mean to a level equivalent to “hire one of us as > a consultant on your project.” We’d need full schema info, number of rows, > queries per second stats, time-to-answer budgets, representative sample > data… > > > the wrapper I use does > > allow putting parts of the JSON object into real columns but changing > the > > production db schema all the time isn't nice > > You only have to change the DB schema each time you discover something new > you want to index. If you don’t even know yet what you need to index, how > can you expect us to tell you, especially given how thin the information > you’ve provided is? > > > - I suppose I was hoping for some performance discussion of the > queries, > > I gave you performance information based on my data, in my schema, with my > queries. You’ve given us your queries but no data and a faux schema, so > naturally no one’s dissected your queries’ performance. > > Despite Jens’ objection, I’ll stand by my observation that since you don’t > show any indices, we must assume that your queries are full-table scans, > which in this case involves re-parsing each JSON object along the way. > > > perhaps how to implement it using json_each? > > How would that solve any performance problem? It’s still a full-table > scan, lacking an index. > > I guess this is coming from the common belief that it’s always faster to > put the code in the database query, as opposed to doing it in the > application code, but that’s only true when the DB has more information > than you do so it can skip work, or because doing the processing at the DB > level avoids one or more copies. I’m not seeing that those apply here. > > “Put it in the database” can also avoid a lot of IPC overhead when using a > client-server DB, but that cost isn’t one that happens with plain SQLite. > > > - I'm thinking it would be nice if the JSON1 extension had a function > to > > extract object keys as an array. > > If you don’t even know what keys you need to operate on until you see > what’s available in each record, I’d say most of your processing should be > at the application code level anyway. And in that case, I’d tell you to > just pull the JSON data as a string, parse it in your program, and iterate > over it as necessary. > > SQL is meant for declarative queries, where you say “I need thus-and-so > data,” which you can specify precisely. It sounds like you cannot specify > that query precisely, so it should probably be done with application logic. > ___ > 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] JSON1: queries on object keys
On Mar 28, 2019, at 4:15 AM, Wout Mertens wrote: > > - I don't see how json_type can help I don’t see “json_type” in this thread at all, other than this message. > - Schemaless data is really nice to work with Sure, but it has a cost. Unless you’re willing to give us a *lot* more information, you’ll have to decide if you’re willing and able to pay it, given your application constraints. By “more information,” I mean to a level equivalent to “hire one of us as a consultant on your project.” We’d need full schema info, number of rows, queries per second stats, time-to-answer budgets, representative sample data… > the wrapper I use does > allow putting parts of the JSON object into real columns but changing the > production db schema all the time isn't nice You only have to change the DB schema each time you discover something new you want to index. If you don’t even know yet what you need to index, how can you expect us to tell you, especially given how thin the information you’ve provided is? > - I suppose I was hoping for some performance discussion of the queries, I gave you performance information based on my data, in my schema, with my queries. You’ve given us your queries but no data and a faux schema, so naturally no one’s dissected your queries’ performance. Despite Jens’ objection, I’ll stand by my observation that since you don’t show any indices, we must assume that your queries are full-table scans, which in this case involves re-parsing each JSON object along the way. > perhaps how to implement it using json_each? How would that solve any performance problem? It’s still a full-table scan, lacking an index. I guess this is coming from the common belief that it’s always faster to put the code in the database query, as opposed to doing it in the application code, but that’s only true when the DB has more information than you do so it can skip work, or because doing the processing at the DB level avoids one or more copies. I’m not seeing that those apply here. “Put it in the database” can also avoid a lot of IPC overhead when using a client-server DB, but that cost isn’t one that happens with plain SQLite. > - I'm thinking it would be nice if the JSON1 extension had a function to > extract object keys as an array. If you don’t even know what keys you need to operate on until you see what’s available in each record, I’d say most of your processing should be at the application code level anyway. And in that case, I’d tell you to just pull the JSON data as a string, parse it in your program, and iterate over it as necessary. SQL is meant for declarative queries, where you say “I need thus-and-so data,” which you can specify precisely. It sounds like you cannot specify that query precisely, so it should probably be done with application logic. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL grows without bounds, short concurrent writes & reads
Hi,
A very simple reproducer bash script using the sqlite3 CLI is appended
at the end.
I am using WAL mode in a setting with sequential writes and many
concurrent reads. Due to WAL mode the readers don't get blocked, which
is great and since writes are sequential, they never get blocked either.
However, I am seeing the WAL grow on every write without ever restarting
at the beginning of the file if there is a constant influx of new reads
(with limited lifetime).
This causes the WAL file to grow to many GB within minutes, even if the
database state fits into a few MB or even kB after closing all connections.
The output of "PRAGMA wal_checkpoint(PASSIVE);" usually looks like this:
"0|123|123", which I interpret as the checkpointer being caught up on
the current state. I believe the reason that new writes are appended at
the end, instead of restarting the WAL, is that while reads are
short-lived, there is always at least one going on, so the log of the
last write has to be kept, which in turn prevents a reset of the WAL.
An example read (r) write (w) pattern could look like this (b: begin, e:
end):
r1_b; w1; r2b; r1e; w2; r2b; w3; r3b; r2e ...
A solution could be to start a second WAL when the first one exceeds
some size threshold, which would allow resetting the first one after all
readers finish that started before the wal_checkpoint finished, even if
there are new writes in the second WAL. Then the roles/order of the two
WALs flipped, allowing the second WAL to be reset regardless of
read/write frequency.
I believe that would limit the total WAL size to about 2 times of the
size of writes happening within the timespan of a single read.
This solution has been suggested previously on this list by Mark
Hamburg, but the threads lack a simple reproducer and detailed problem
description.
Best regards,
Florian
Test script:
rm -f test.sqlite
./sqlite3 test.sqlite <<< "
PRAGMA journal_mode=WAL;
CREATE TABLE t (value INTEGER);
REPLACE into t (rowid, value) VALUES (1,0);
"
for i in {1..1000}
do
./sqlite3 test.sqlite <<< "
BEGIN;
SELECT value FROM t WHERE rowid=1;
.system sleep 0.2
SELECT value FROM t WHERE rowid=1;
COMMIT;
" &
sleep 0.1
./sqlite3 test.sqlite <<< "
BEGIN;
REPLACE into t (rowid, value) VALUES (1,$i);
.print inc
COMMIT;
"
wc -c test.sqlite-wal
done
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On 28 Mar 2019, at 10:25am, Dominique Devienne wrote: > Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree > to, and accept to "publicly document" and thus support would be nice, You want something like EXPLAIN EFFECTS OF and it should answer with zero or more lines. Each line contains a single string column. The strings are things like 'change data', 'change schema', 'change pragma', 'return nothing', 'return table', 'return one row'. The 'change' results do not mean that anything actually changes, they mean that the command is the kind of command intended to make that change. For example, an UPDATE command that changes no rows (or perhaps even refers to a table which doesn't exist) still returns 'changes data'. The 'return' results are similar. 'return table' means the command can return any number of rows, not how many rows it actually will return. If 'changes pragma' appears, then perhaps another line could say which one, like 'changes pragma journal_mode'. This would be useful for people writing a SQLite tool, or those with a setup which might involve an injection vulnerability. Whether it's actually worth building into SQLite I have no idea. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: dynamically referenced bind parameters
Oooo this is really neat. Thanks! > On Mar 27, 2019, at 5:12 PM, Richard Hipp wrote: > > See https://www.sqlite.org/carray.html > > -- > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clear sqlite3 terminal enhancement
On Thu, Mar 28, 2019 at 1:16 PM Clemens Ladisch wrote: > Jeffrey Walton wrote: > > When working in the Linux terminal we can clear the scrollback with > > the 'clear' command; and we can delete all history and scrollback with > > the 'reset' command. I am not able to do the same within the sqlite3 > > terminal. > > Those are programs run from the shell. So you can use ".shell clear" or > ".shell reset". > Nice trick, thanks for sharing. `.shell cls` on Windows worked for me. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Feature request, sqlite3_stmt_action
On Thu, Mar 28, 2019 at 1:35 PM Hick Gunter wrote: > IMHO the sqlite3_set_authorizer() interface already does a pretty decent > job of providing the requested information: > True, but only if you are fully in control, because authorizer do not "stack". There's only one, you can't get to restore one a previous one. Which is logical since used for "security". But when you want do it both for security, and introspection, and are you are part of a larger application using SQLite, it makes things more complicated than it should be. This was NOT designed for statement introspection after all... --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Feature request, sqlite3_stmt_action
IMHO the sqlite3_set_authorizer() interface already does a pretty decent job of providing the requested information: asql> explain insert into tx7300.vals(keyid,value,sync_offset) ...> select k.id,tx.retailer_loc_id,tx.sync_offset from tx7300.keys k, atx_txlog tx where k.name='retailer_loc_id' and tx.period_no = 7300 and retailer_loc_id; 2019-03-28 13:27:27.821: AUTH: T: vals C: (null) D: tx7300 A: (null) P: Insert 2019-03-28 13:27:27.821: AUTH: T: (null) C: (null) D: (null) A: (null) P: Select 2019-03-28 13:27:27.821: AUTH: T: keys C: id D: tx7300 A: (null) P: Read 2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: retailer_loc_id D: main A: (null) P: Read 2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: sync_offset D: main A: (null) P: Read 2019-03-28 13:27:27.821: AUTH: T: keys C: name D: tx7300 A: (null) P: Read 2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: period_no D: main A: (null) P: Read 2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: retailer_loc_id D: main A: (null) P: Read -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 28. März 2019 11:26 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] Feature request, sqlite3_stmt_action On Thu, Mar 28, 2019 at 10:59 AM R Smith wrote: > Maybe even, if possible, This query updates these tables: x1, x2, x3... > etc. (some of which might hide behind an FK relation or Trigger) but > I know this is pushing my luck. :) > What I ended-up doing is introspecting the VDBE program of the statement. It's not exactly easy, and can be brittle too, since the output is not "publicly documented" so subject to change w/o notice, but I consider this approach less brittle than parsing the SQL. > Plus, I really do not mind if this explain takes some time, it will be > faster and more future-proof than any self-parsing one can do. Right. Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree to, and accept to "publicly document" and thus support would be nice, good idea. No an AST of course, but would go a long way already, for those of us that need/wish for that. I'll put it on my Xmas list :). --DD ___ 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
Re: [sqlite] Clear sqlite3 terminal enhancement
Jeffrey Walton wrote: > When working in the Linux terminal we can clear the scrollback with > the 'clear' command; and we can delete all history and scrollback with > the 'reset' command. I am not able to do the same within the sqlite3 > terminal. Those are programs run from the shell. So you can use ".shell clear" or ".shell reset". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Clear sqlite3 terminal enhancement
Hi, When working in the Linux terminal we can clear the scrollback with the 'clear' command; and we can delete all history and scrollback with the 'reset' command. I am not able to do the same within the sqlite3 terminal. I'd like to request a '.clear' command and a '.reset' command to do the same in the sqlite3 terminal. They should perform the same actions that are performed in a typical shell. Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: UPSERT with multiple constraints
I think it can all be done in a single table, with columns, A, B, C,
type, Name, and other things, with indexes/constraints
UNIQUE(A, B, C, Type)
UNIQUE(Name, Type)
and quite possible an addition PRIMARY index, perhaps the default ROWID
one, as neither of those UNIQUE indexes look to be ideal as a primary
key for other tables that might want to refer to an item to use.
On 3/28/19 1:59 AM, Roger Schlueter wrote:
> Following Simons' comment, changing the schema to conform to SQL
> expectations would involve having at least two tables. Consider your
> second uniqueness criterion; Let's call those items a "Widget" so your
> Widget table would be:
>
> WIDGETS
> {A, B, C, .} UNIQUE(A,B,C)
>
> Let's call your items whose Name is unique "Gadgets" so your Gadgets
> table would be:
>
> GADGETS
> {Name, A, B, C, } UNIQUE(Name)
>
> I assume there are other things:
>
> THINGS
> {Type, Name, A, B, C, .} No(?) uniqueness
>
> Knowing the Type of items to be updated, you know which table to use.
>
> On 3/27/2019 15:59, Thomas Kurz wrote:
>>> Can I ask what it is that you're trying to do ? This smacks of
>>> trying to add 1 to an existing value or something like that.
>> Sure. I have a table of items. Each item has a type, a name, and
>> properties A, B, C (and some more, but they're not relevant here).
>>
>> I want to enforce that items of a certain type and name are unique:
>> UNIQUE (type, name). But there can be multiple items with the same
>> name as long as they are of different types.
>>
>> Furthermore, items of a certain type that have identical properties
>> A, B, C are also considered equal, regardless of their name: UNIQUE
>> (type, A, B, C).
>>
>> I cannot use UNIQUE (type, name, A, B, C), as this would mean that
>> there can be two items with the same A, B, C (and type, of course),
>> but different name. On the other hand, there could be two items with
>> the same same (and type, of course) but different A, B, C.
>>
>> Now when inserting an item that already exists (according to the
>> uniqueness definition above), the existing item should be updated
>> with the new name and A, B, C properties.
>>
>> ___
>> 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
--
Richard Damon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On Thu, Mar 28, 2019 at 10:59 AM R Smith wrote: > Maybe even, if possible, This query updates these tables: x1, x2, x3... > etc. (some of which might hide behind an FK relation or Trigger) but I > know this is pushing my luck. :) > What I ended-up doing is introspecting the VDBE program of the statement. It's not exactly easy, and can be brittle too, since the output is not "publicly documented" so subject to change w/o notice, but I consider this approach less brittle than parsing the SQL. > Plus, I really do not mind if this explain takes some time, it will be > faster and more future-proof than any self-parsing one can do. Right. Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree to, and accept to "publicly document" and thus support would be nice, good idea. No an AST of course, but would go a long way already, for those of us that need/wish for that. I'll put it on my Xmas list :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JSON1: queries on object keys
To answer all emails in this thread: - I don't see how json_type can help, I want to query the keys of objects - Schemaless data is really nice to work with, the wrapper I use does allow putting parts of the JSON object into real columns but changing the production db schema all the time isn't nice - I suppose I was hoping for some performance discussion of the queries, perhaps how to implement it using json_each? - I'm thinking it would be nice if the JSON1 extension had a function to extract object keys as an array. I suppose something like `SELECT json_group_array(key) FROM foo,json_each(j) GROUP BY foo.rowid ORDER BY key;` is silly… ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On 2019/03/28 9:07 AM, Olivier Mascia wrote: Le 27 mars 2019 à 18:04, siscia a écrit : I would like to propose a function (named `sqlite3_stmt_action` for the sake of discussion) that allow to understand if a specific statement is either a SELECT, UPDATE, DELETE or INSERT. There is probably a much more complex need that I did not grasp reading this request. What stops you from parsing the beginning of the statement text to decide if it is a select, update, delete or insert? Having done this already, allow me to offer some recounting of the difficulties: First there are typically two things a programmer is interested in (well, if you maintain an SQLite management utility or the like): - First: Will the Query produce data output back that I need to show to the user?, or will it silently execute? - If it does produce output, is this confirming the state (such as when calling a pragma command), or is this output that I need to show the user, or perhaps log? - Then: Will the query change the database? - Put another way, will it work on a read-only file? - or, will it alter the table content that is currently displayed? Do I need to re-run the display query? - or will it change the schema? - Do I need to re-parse the schema to show the user the DB layout after executing? Some of these SQLite does cater for, but many not, and there are some work-aroundy ways of accomplishing it. For instance, you might reparse the schema after ANY non-select query. But then - how do I know if it is anything other than a SELECT query? The obvious answer is not to see if it's any of INSERT, UPDATE, CREATE, etc... but to simply see if it is indeed a SELECT query. Right? But then, what about CREATE TABLE t AS SELECT a,b,c, FROM. Or if it is a CTE, consider these two: WITH X(z) AS (SELECT 1) SELECT z FROM X; vs. WITH X(z) AS (SELECT 1) INSERT INTO t(z) SELECT z FROM X; These are already difficult to self-parse, and they are extremely simple examples. I would even be happy to have something like extending the EXPLAIN QUERY PLAN sql interface to include something like: EXPLAIN QUERY RESULT ; which outputs a simple row of values that can tell me: - This query produces results - YES/NO (even if those results may be empty, is it the /intent/ of the query to produce results?), - It updates the data - YES/NO, - It updates the schema - YES/NO - It is a pragma or setting adjustment - YES/NO Maybe even, if possible, This query updates these tables: x1, x2, x3... etc. (some of which might hide behind an FK relation or Trigger) but I know this is pushing my luck. :) Plus, I really do not mind if this explain takes some time, it will be faster and more future-proof than any self-parsing one can do. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row locking sqlite3
You can use a simple 'L' flag on the rows you want locked and add a where to don't touch them. I'm afraid things are more complicated in many real-world cases. Locking a single row isn't enough. What if the UPDATE or DELETE forces deep changes in one or more indices? What if the UPDATE or DELETE cascades to 1 to N levels? What if some change launches a trigger that itself changes things elsewhere, possibly a LOT of things, that may themselves change several other parts of the DB? In all cases above, if a read operation occurs in the middle of the write process, DB integrity is jeopardized. I haven't looked at the proposed patch but I seriously doubt all of this is taken care in all situations. JcD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On Thu 28 Mar 2019 at 08:07, Olivier Mascia wrote: > > > Le 27 mars 2019 à 18:04, siscia a écrit : > > > > I would like to propose a function (named `sqlite3_stmt_action` for the > sake > > of discussion) that allow to understand if a specific statement is > either a > > SELECT, UPDATE, DELETE or INSERT. > > There is probably a much more complex need that I did not grasp reading > this request. What stops you from parsing the beginning of the statement > text to decide if it is a select, update, delete or insert? Because it’s never as simple as it looks... CTEs anyone ? It can be approximated sure. But will typically be brittle. I’ve long wished for an AST for SQLite statements but in fact the grammar actions directly build the internal data structures, it’s not two phase > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
> Le 27 mars 2019 à 18:04, siscia a écrit : > > I would like to propose a function (named `sqlite3_stmt_action` for the sake > of discussion) that allow to understand if a specific statement is either a > SELECT, UPDATE, DELETE or INSERT. There is probably a much more complex need that I did not grasp reading this request. What stops you from parsing the beginning of the statement text to decide if it is a select, update, delete or insert? -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
