[sqlite] combine SELECTs

[Lev]

On Sun, 30 Aug 2015 21:26:43 +0200
"R.Smith"  wrote:

> 1 - Sub-Queries:
> 
> SELECT A.Data, B.Data, C.Data
>FROM (SELECT KeyField, Col1 AS Data FROM someTable WHERE Col1 >
> 5)  AS A JOIN (SELECT KeyField, Col2 AS Data FROM someTable WHERE
> Col2 < 10) AS B JOIN (SELECT KeyField, Col3 AS Data FROM someTable
> WHERE Col3 = 20) AS C WHERE A.KeyField = B.KeyField  AND  B.KeyField
> = C.KeyField;
> 
> 
> 2 - Common table expression (CTE):
> 
> WITH
> A(KeyField, Data) AS (SELECT KeyField, Col1 FROM someTable WHERE Col1
> > 5), B(KeyField, Data) AS (SELECT KeyField, Col2 FROM someTable
> > WHERE Col2 
> < 10),
>C(KeyField, Data) AS (SELECT KeyField, Col3 FROM someTable WHERE
> Col1 = 20)
> SELECT A.Data, B.Data, C.Data
>FROM A
>JOIN B ON B.KeyField = A.KeyField
>JOIN C ON C.KeyField = A.KeyField
> ;
> 
> 
> You could of course add lots of other columns, this just the minimum
> to make the idea work.


Okay. Thanks for the hint. I think I go for 1.

Lev

-- 
73 de HA5OGL
Op.: Levente

[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Stephan Beal]

On Mon, Aug 31, 2015 at 4:50 AM, Nicolas J?ger 
wrote:

> I'm starting to use transaction in my C++ code to delete some entries
> (tags) in my db. during execution, the first transaction (each
> transaction is contained in one string), like :
>
> BEGIN TRANSACTION;
> DELETE FROM TAGS WHERE NAME = 'loki';
> DELETE FROM TAGSMAP WHERE COLLECTION_ID = '3' AND TAG_id = '54';
> COMMIT;
>
> is executed by running `sqlite3_prepare_v2()` and the `sqlite3_stmt`
> returns `SQLITE_DONE`. So I consider the transaction made and closed.
> but if I looked in the db I still see the row/entry for 'loki'
>

Nope - you have only run the BEGIN part of the transaction. prepare()
prepares only one single statement, not multiples (you have 4 statements in
your SQL). Thus when you try to run another transaction, that BEGIN is
still open.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Simon Slavin]

On 31 Aug 2015, at 2:23pm, Stephan Beal  wrote:

> Nope - you have only run the BEGIN part of the transaction. prepare()
> prepares only one single statement, not multiples (you have 4 statements in
> your SQL). Thus when you try to run another transaction, that BEGIN is
> still open.

In contrast sqlite_exec() can process multiple statements, the way you were 
trying to use _prepare(),_step(),_finalize().



"run multiple statements of SQL without having to use a lot of C code."

Simon.

[sqlite] Documentation Error: sqlite3_set_authorizer() interface

[Domingo Alvarez Duarte]

Hello !  

Also there is no way to link the callback to specific statement, I think that
an extra parameter with the whole sql statement would be needed and/or any
other way to link several calls to a statement.  

Maybe something like:  int sqlite3_set_authorizer(sqlite3*,int
(*xAuth)(void*,int,const char *sql_been_prepared, const char*,const
char*,const char*,const char*),void *pUserData  );

That way we can link successive calls to "sql_been_prepared" and know when a
new one starts/ends. Otherwise it's not clear how to use this correctly.

Bellow is an example to test the sqlite3_authorizer with it's output:

== pseudo code (scripting using https://github.com/mingodad/squilu)
local db_file_name = ":memory:";
local db = SQLite3(db_file_name);

local function authorizer_hook(udata, action_code, param3, param4, param5,
param6)
{
 print(action_code, param3 || "null", param4 || "null", param5 || "null",
param6 || "null");
 return db.SQLITE_OK;
}
db.set_authorizer(authorizer_hook, "all");

db.exec_dml("DROP TABLE IF EXISTS test;");
db.exec_dml("DROP VIEW IF EXISTS test_view;");
db.exec_dml("CREATE TABLE  test(id integer primary key, key varchar not null,
value varchar not null check(length(value) > 2), unique(key));");
db.exec_dml("CREATE VIEW test_view AS SELECT * FROM test;");
db.exec_dml("BEGIN;");
local stmt = db.prepare("INSERT INTO test(key, value) VALUES(?,?);");
stmt.bind(1, "key_1");
stmt.bind(2, "value_1");
stmt.step();
stmt.reset();
stmt.finalize();
db.exec_dml("COMMIT;");

stmt = db.prepare("SELECT * FROM test_view WHERE id = ?;");
stmt.bind(1,  1);
stmt.step();
stmt.reset();
stmt.finalize();

stmt = db.prepare("SELECT * FROM test WHERE key = ?;");
stmt.bind(1, "key_1");
stmt.step();
stmt.reset();
stmt.finalize();

stmt = db.prepare("SELECT * FROM test WHERE value = ? limit 1;");
stmt.bind(1, "value_1");
stmt.step();
stmt.reset();
stmt.finalize();

db.exec_dml("CREATE INDEX test_values_idx ON test(value);");

stmt = db.prepare("SELECT * FROM sqlite_master;");
stmt.finalize();

db.close();
==

==output of the above script
18 sqlite_master null main null
2 test null main null
18 sqlite_master null main null
1 sqlite_autoindex_test_1 test main null
18 sqlite_master null main null
31 null length null null
20 test value main null
23 sqlite_master type main null
23 sqlite_master name main null
23 sqlite_master tbl_name main null
23 sqlite_master rootpage main null
23 sqlite_master sql main null
20 sqlite_master ROWID main null
20 sqlite_master name main null
20 sqlite_master rootpage main null
20 sqlite_master sql main null
20 sqlite_master tbl_name main null
20 sqlite_master type main null
20 sqlite_master ROWID main null
20 test value main null
18 sqlite_master null main null
8 test_view null main null
23 sqlite_master type main null
23 sqlite_master name main null
23 sqlite_master tbl_name main null
23 sqlite_master rootpage main null
23 sqlite_master sql main null
20 sqlite_master ROWID main null
20 sqlite_master name main null
20 sqlite_master rootpage main null
20 sqlite_master sql main null
20 sqlite_master tbl_name main null
20 sqlite_master type main null
20 sqlite_master ROWID main null
22 BEGIN null null null
18 test null main null
22 COMMIT null null null
21 null null null null
20 test id main test_view
20 test key main test_view
20 test value main test_view
20 test_view id main null
20 test_view key main null
20 test_view value main null
20 test_view id main null
21 null null null test_view
21 null null null null
20 test id main null
20 test key main null
20 test value main null
20 test key main null
21 null null null null
20 test id main null
20 test key main null
20 test value main null
20 test value main null
18 sqlite_master null main null
1 test_values_idx test main null
18 sqlite_master null main null
27 test_values_idx null main null
20 sqlite_master name main null
20 sqlite_master rootpage main null
20 sqlite_master sql main null
20 sqlite_master name main null
20 sqlite_master type main null
20 sqlite_master ROWID main null
21 null null null null
20 sqlite_master type main null
20 sqlite_master name main null
20 sqlite_master tbl_name main null
20 sqlite_master rootpage main null
20 sqlite_master sql main null
==  

?

[sqlite] Documentation Error: sqlite3_set_authorizer() interface

[Mark Brand]

According to the documentations of The Authorizer Action Codes 
(http://www.sqlite.org/c3ref/c_alter_table.html):

Authorizer Action Codes

 /*** 3rd  4th 
***/

 #define SQLITE_SELECT   21   /* NULLNULL   
 */


However, in reality, the callback provides Table Name in the 4th parameter.

Mark

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Jean-Christophe Deschamps]

At 11:58 31/08/2015, you wrote:
 >---
>But the only sure way to prevent anyone else from installing its own
>authorizer is to change the function name in the SQLite library.
 >---

That wouldn't really work under Windows: GetProcAddress can provide 
access to a DLL function by index (ordinal value) without knowledge of 
its declared name. Also a quick look at the code with any hex editor 
will help finding the changed named.

>you cannot protect against anything that code does.

That's the true bottom line. 

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Clemens Ladisch]

Dominique Devienne wrote:
> One annoyance with the authorizer approach is that you cannot "stack them"

Write your own authorizer that implements its own callback list.

But the only sure way to prevent anyone else from installing its own
authorizer is to change the function name in the SQLite library.

> I guess that could be viewed as a security hole, but then there's no
> action-code for calling set_authorizer apparently,
> so anyone can override any one authorizer already in place, no?

You cannot set an authorizer with an SQL statement.

As for code you link into your program, and call: you cannot protect
against anything that code does.


Regards,
Clemens

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

[Dominique Devienne]

On Thu, Aug 27, 2015 at 4:21 PM, Dominique Devienne 
wrote:

> On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp  wrote:
>
>> On 8/27/15, Clemens Ladisch  wrote:
>> > Dominique Devienne wrote:
>> >> how can we programatically reliably discover which (v)tables a view
>> >> accesses, staying in documented behavior land?
>> >
>> > With an authorizer callback:
>> > http://www.sqlite.org/c3ref/set_authorizer.html
>> >
>>
>> Clever!  I was about to write back that SQLite does not provide the
>> capability that Dominique wants, but I think Clemens's answer is better!
>>
>
> Thanks Clemens and Richard. If VTable names are reported, this will work
> for us!
> I'll try it, to move away from our current reliance on undefined behavior.
> --DD
>

One annoyance with the authorizer approach is that you cannot "stack them",
since there's no get_authorizer (or set_authorizer returning the old one).
This is a large codebase, and it's also plugin-based, so knowing for sure
who sets one if problematic and error prone.

>From the doc:

> Only a single authorizer can be in place on a database connection at a
> time.
> Each call to sqlite3_set_authorizer overrides the previous call.
> Disable the authorizer by installing a NULL callback.
> The authorizer is disabled by default.


Any chance we could have a way to "stack" authorizers, in a future version
of SQLite?

I guess that could be viewed as a security hole, but then there's no
action-code for calling set_authorizer apparently,
so anyone can override any one authorizer already in place, no? Or am I
missing something?

Thanks, --DD