Re: [sqlite] detect database/table/field use
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
-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
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
> 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
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
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
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
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
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)
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
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
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
