Re: [sqlite] detect database/table/field use

[Nelson, Erik - 2]

Roger Binns wrote on Thursday, June 26, 2014 5:38 PM
> >On 26/06/14 12:58, Nelson, Erik - 2 wrote:
> > I'd like to record which databases/tables/fields are accessed.  Is
> > there any not-too-difficult way of doing this?
> 
> The authorizer interface will address your issue.  You can just record
> what it tells you, or prevent/replace with null certain columns.
> 
>   https://sqlite.org/c3ref/set_authorizer.html
> 
> That page doesn't give any examples of what you see.  The doc for my
> python SQLite wrapper shows three examples:
> 
>   http://rogerbinns.github.io/apsw/example.html#authorizer-example
> 

Thanks!

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect database/table/field use

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/06/14 12:58, Nelson, Erik - 2 wrote:
> I'd like to record which databases/tables/fields are accessed.  Is
> there any not-too-difficult way of doing this?

The authorizer interface will address your issue.  You can just record
what it tells you, or prevent/replace with null certain columns.

  https://sqlite.org/c3ref/set_authorizer.html

That page doesn't give any examples of what you see.  The doc for my
python SQLite wrapper shows three examples:

  http://rogerbinns.github.io/apsw/example.html#authorizer-example

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOskrUACgkQmOOfHg372QS4VQCePMhPvn4E1GfpBCaDzMFX9lHn
lbwAnje0488t9WFZLZztJSK05ScR6ZRK
=v6gt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

OK, thank you all for your support.


On Thu, Jun 26, 2014 at 3:22 AM, Igor Tandetnik  wrote:

> On 6/25/2014 8:48 PM, João Ramos wrote:
>
>> Now that you mentioned the WAL, shouldn't this actually help prevent this
>> scenario?
>>
>
> Quite the opposite - WAL helps enable this scenario. With traditional
> rollback journal, the writer would be unable to write at all while a reader
> is active, so issues of visibility fail to arise. Thus, as long as at least
> one connection to the shared cache has an open statement, so does the
> cache's connection to the underlying file, and the writer will be locked
> out.
>
>
>  I've never looked at SQLite implementation, but if a transaction
>> starts after a successful commit (T1), the new data it tries to access
>> shouldn't be cached because it was just now written to the WAL, correct?
>>
>
> Yes. But remember - this is true for "real" connections to the underlying
> file. All "pseudo"-connections to the shared cache use the same underlying
> "real" connection. The transaction on the "real" connection starts when the
> number of "pseudo" transactions on "pseudo" connections goes from 0 up to
> 1, and ends when that number goes from 1 down to 0.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sequential numbers

[Roman Fleysher]

> Example:
>
>
> CREATE TABLE aaa (i, seqnr);
> INSERT INTO "aaa" VALUES(10,NULL);
> INSERT INTO "aaa" VALUES(20,NULL);
> INSERT INTO "aaa" VALUES(50,NULL);
> INSERT INTO "aaa" VALUES(30,NULL);
> INSERT INTO "aaa" VALUES(20,NULL);
>
> UPDATE aaa  SET seqnr=(SELECT count() FROM aaa smaller where
> smaller.rowid <= aaa.rowid);
>
> select * from aaa;
> i|seqnr
> 10|1
> 20|2
> 50|3
> 30|4
> 20|5
>
>
> Regards
>
> Rob Golsteijn

In my opinion, the dispenser (the code that generates the insert commands) is 
the one that should be assigning the sequence numbers. It is this code only 
that knows which goes first, especially if we are talking about multithreaded 
computation. ROWID is "order as inserted by SQLite" not "order as inserted by 
dispenser". Order in which SQLite inserts is important for SQLite and its 
developers.

Roman

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

[sqlite] detect database/table/field use

[Nelson, Erik - 2]

I work with a C++ application that embeds the SQLite source and executes 
user-supplied queries against SQLite database(s).

I'd like to record which databases/tables/fields are accessed.  Is there any 
not-too-difficult way of doing this?

Poking around in the source, it seems like a fundamental hook might be either 
in the lookupName() function or the internals of the Parse structure.

Anyway suggestions would be appreciated.

Erik




--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Merge join in SQLite

[Richard Hipp]

On Thu, Jun 26, 2014 at 11:41 AM, Eleytherios Stamatogiannakis <
est...@gmail.com> wrote:

> Hi,
>
> I've seen that SQLite supports the "SorterOpen" Op, to do merge sorting.
> So based on that, does SQLite support merge joins when both inputs of the
> join are sorted?
>

No, not at this time.  Note that a merge-join is more complicated than it
appears at first glance for the common case where the join key is not
unique in one or the other of the two tables being joined.


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

[sqlite] Merge join in SQLite

[Eleytherios Stamatogiannakis]

Hi,

I've seen that SQLite supports the "SorterOpen" Op, to do merge sorting. 
So based on that, does SQLite support merge joins when both inputs of 
the join are sorted?


Kind regards,

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

[sqlite] affinity critical problem with 3.8.5 - IN single value optimisation

[Guillaume Fougnies]

Hi,

It seems there's a problem with 3.8.5 and its affinity behavior.
It's quite critical.

--- CUT ---
sqlite> CREATE TABLE T (v text);
sqlite> insert into T values('1');
sqlite> insert into T values('2');
sqlite> select v from T where v=1;
1
sqlite> select v from T where v='1';
1
sqlite> select v from T where v IN(1);
sqlite> select v from T where v IN('1');
1
sqlite> select v from T where v IN(1,2);
1
2
sqlite> select v from T where v IN('1','2');
1
2
--- /CUT ---


It must be linked to this change:

"Render expressions of the form "x IN (?)" (with a single value in the list on 
the right-hand side of the IN operator) as if they where "x==?", Similarly 
optimize "x NOT IN (?)""

Best regards,
--
Guillaume FOUGNIES
Eulerian Technologies
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sequential numbers

[E.Pasma]

Op 26 jun 2014, om 10:32 heeft Rob Golsteijn het volgende geschreven:


Hi Dave,


You can of course also calculate a new sequence number based on the  
row ids. Just count the number of records with a smaller or equal  
rowid. This way it doesn't matter if rowid starts at 1 or if there  
are any gaps in the range.



Example:


CREATE TABLE aaa (i, seqnr);
INSERT INTO "aaa" VALUES(10,NULL);
INSERT INTO "aaa" VALUES(20,NULL);
INSERT INTO "aaa" VALUES(50,NULL);
INSERT INTO "aaa" VALUES(30,NULL);
INSERT INTO "aaa" VALUES(20,NULL);

UPDATE aaa  SET seqnr=(SELECT count() FROM aaa smaller where   
smaller.rowid <= aaa.rowid);


select * from aaa;
i|seqnr
10|1
20|2
50|3
30|4
20|5


Regards

Rob Golsteijn



I had the same idea, only using a view:

CREATE TABLE aaa (i);
INSERT INTO "aaa" VALUES(10);
INSERT INTO "aaa" VALUES(20);
INSERT INTO "aaa" VALUES(50);
INSERT INTO "aaa" VALUES(30);
INSERT INTO "aaa" VALUES(20);

CREATE VIEW vaaa AS
SELECT  aaa.i,
(SELECT count() FROM aaa smaller where  smaller.rowid <=  
aaa.rowid) AS seqnr

FROM aaa;

select * from vaaa;



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

Re: [sqlite] How to get access to SQLite Test Harness #3(TH3)

[Richard Hipp]

On Thu, Jun 26, 2014 at 1:55 AM, Kishore Reddy  wrote:

>
> *Richard Hipp,*
> I am planning to use SQLite library in *Avionics Level B software*.
>

SQLite is developed using a DO-178B compatible process.  The 100% MC/DC
testing is just one aspect of that.  Contact our office for details.

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

Re: [sqlite] SQLite performance with NOR FLASH

[Simon Slavin]

On 26 Jun 2014, at 7:42am, Vivek Ranjan  wrote:

> Code looks like this:

Thanks.  I was wondering whether you called _step() with strange value but 
you're calling it with -1, which seems to be the best thing to do in your case. 
 And I don't see anything else wrong with your code.

I hope one of the devs can look into this.  Writing any disk file four bytes at 
a time is going to be bad not only for Flash in mini devices but also for 
computers which use SSD for main filestore.

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

Re: [sqlite] Sequential numbers

[Rob Golsteijn]

Hi Dave,

 
You can of course also calculate a new sequence number based on the row ids. 
Just count the number of records with a smaller or equal rowid. This way it 
doesn't matter if rowid starts at 1 or if there are any gaps in the range.

 
Example:

 
CREATE TABLE aaa (i, seqnr);
INSERT INTO "aaa" VALUES(10,NULL);
INSERT INTO "aaa" VALUES(20,NULL);
INSERT INTO "aaa" VALUES(50,NULL);
INSERT INTO "aaa" VALUES(30,NULL);
INSERT INTO "aaa" VALUES(20,NULL);

UPDATE aaa  SET seqnr=(SELECT count() FROM aaa smaller where  smaller.rowid <= 
aaa.rowid);
 
select * from aaa;
i|seqnr
10|1
20|2
50|3
30|4
20|5

 
Regards

Rob Golsteijn

 
 


> Hi all,
>
>I have some rows in a table (not very many, typically less than 20) and I
>want to generate a unique, sequential number for each row. In another dbms
>I've used a row_number function (amongst others) to achieve this but I can't
>see anything with equivalent functionality in sqlite3. My apologies if I've
>missed something.
>
> 
>
>I thought about using the 'rowid' and in some simple testing that seems to
>give me what I want. But I need to check a couple of things.
>
> 
>
>1)  Is there a function that will give me unique, sequential numbers?
>
>
>
>2)  Assuming that my processing follows this pattern: empty table T1
>completely, insert a number of rows, insert/select from T1 into T2. On the
>'select' processing will the 'rowid' 
>** always ** start at 1?
>
>
>
>3)  If I repeat the processing pattern shown in #2 above, will
>subsequent selects always have rowid that starts from 1?
>
>
>
>Yes, I know that I could select the rows back to my application, generate
>the numbers and then insert rows back into the table but I'm trying to do
>this within the dbms.
>
>
>
>All help or ideas gratefully received.
>
> 
>
>Cheers,
>
>Dave



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

Re: [sqlite] Sequential numbers

[Hick Gunter]

>
>-Ursprüngliche Nachricht-
>Von: RSmith [mailto:rsm...@rsweb.co.za]
>Gesendet: Mittwoch, 25. Juni 2014 21:54
>An: sqlite-users@sqlite.org
>Betreff: Re: [sqlite] Sequential numbers
>
>
>On 2014/06/25 21:38, Dave Wellman wrote:
>> Hi Petite,
>> Many thanks fo rthsuggestion, it works a treat!.
>>
>> Hi Simon,
>> Thanks for the thoughts but in this particular processing that is not
>> going to happen (delete a few rows). In this processing we always
>> empty the table completely before re-populating it.
>>
>> I've gone with the TRIGGEr solution, it works really well.
>>
>
>Hi Dave, as a side note - if you never remove rows, AND in stead of emptying 
>the table (Truncation) you actually DROP and Re-CREATE it... then a standard 
>AUTOINCREMENT rowid alias is >almost guaranteed to actually produce you 
>sequential numbering from 1 onwards. It really only starts doing funnies when 
>you remove rows - but if you don't break the chain, the rowid >won't either. I 
>know the documentation does not "guarantee" it simply because the behaviour 
>can go haywire after amendments to the table, but I have yet to see a rowid 
>NOT start at 1 >in a brand-new table, or indeed just randomly "miss a beat" as 
>you add rows. never happens - until that first row is deleted.
>
>Sometimes however one doesn't intend to delete things, but it happens, so I 
>think the trigger solution by Mr. Bee is still safer - though this would 
>equally mess up the sequence if a >deletion happens - BUT, you can just 
>manually go change the values in that column then until they are happy again 
>and the trigger should perform business-as-usual from then on.
>
>Just another thought,
>Have a great day!
>

SQLite will do that automagically

http://www.sqlite.org/compile.html#omit_truncate_optimization

A default build of SQLite, if a DELETE statement has no WHERE clause and 
operates on a table with no triggers, an optimization occurs that causes the 
DELETE to occur by dropping and recreating the table. Dropping and recreating a 
table is usually much faster than deleting the table content row by row. This 
is the "truncate optimization".

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


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with NOR FLASH

[Vivek Ranjan]

Hello Simon,

Code looks like this:


 /* move content */
  sqlite3_backup *pBackup;

  pBackup = sqlite3_backup_init(destDBHandle, "main", sourceDBHandle,
"main");

  if(pBackup)
  {
 int sqlErrno;
 if ((sqlErrno = sqlite3_backup_step(pBackup, -1)) != SQLITE_DONE)
 {
Tr_Err("SwapDB sqlite3_backup_step sqlErrno %d", sqlErrno);

result = false;
 }
 if ((sqlErrno = sqlite3_backup_finish(pBackup)) != SQLITE_OK)
 {
Tr_Err("SwapDB sqlite3_backup_finish sqlErrno %d", sqlErrno);

result = false;
 }
  }
  else
  {
 Tr_Err("SwapDB could not be init");
 result = false;
  }


Br
Vivek


On Wed, Jun 25, 2014 at 10:14 PM, Vivek Ranjan 
wrote:

> Dear All,
>
> I am unsing SQLite on NOR Flash with Sqlite version 3.8.5 with default
> setting i.e. sector and page size. Writing to NOR Flash taken around 10
> seconds ( SQLite file size of 400KB, which is somehow not acceptable). Upon
> inserting additional debug prints, I found that since I use the  following
> API
>
> sqlite3_backup_init()
> 
> sqlite3_backup_step()
> 
> sqlite3_backup_finish()
> 
>
> SQLite writes data in very small chunks i.e. 1, 4, bytes which causes the
> poor performance while writing to Flash
>
> Please suggest if there are some experiences to fine tune the writes to
> NOR Flash.
>
> Kind Regards
> Vivek
>
>


-- 
Vivek(Mobile) :  +49 17655 040018
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for concurrent transactions

[Constantine Yannakopoulos]

On Jun 26, 2014 4:06 AM, "Simon Slavin"  wrote:
>
> Of course, you do get the increased time (it takes time to open and parse
the database file) and memory overhead.

One could setup a simple connection pooling mechanism in order to minimize
_open() overhead. Standard practice in servers that provide access to the
same database to many clients at the same time and can afford to trade
connection establishment overhead with memory overhead.

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