Re: [sqlite] Symmetric EXCEPT

[Clemens Ladisch]

Jamie Norrish wrote:
> Is there any optimisation done for the case of a symmetric EXCEPT, to
> avoid performing duplicate SELECT statements?

Some subqueries can be flattened by SQLite, but otherwise, subqueries
aren't really optimized at all.

> (SELECT A... EXCEPT SELECT B...) UNION (SELECT B... EXCEPT SELECT A...)
>
> would seem to be a poor approach to getting the set of rows in either A
> or B. Are there potentially better generic approaches, or other tricks
> I've missed?

The description "the set of rows in A or B, except those that are in
both A and B" leads to

  (SELECT A... UNION SELECT B...) EXCEPT (SELECT A... INTERSECT SELECT B...)

There doesn't seem to be much of a difference in practice; performance
probably depends on the size of the subquery results:

sqlite> explain query plan select * from a except select * from b union all
select * from (select * from b except select * from a);
sele  order  from  deta
  -    
2 0  0 SCAN TABLE a (~100 rows)
3 0  0 SCAN TABLE b (~100 rows)
1 0  0 COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE 
(EXCEPT)
6 0  0 SCAN TABLE b (~100 rows)
7 0  0 SCAN TABLE a (~100 rows)
5 0  0 COMPOUND SUBQUERIES 6 AND 7 USING TEMP B-TREE 
(EXCEPT)
4 0  0 SCAN SUBQUERY 5 (~100 rows)
0 0  0 COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)

sqlite> explain query plan select * from a union all select * from b except
select * from (select * from a intersect select * from b);
sele  order  from  deta
  -    
2 0  0 SCAN TABLE a (~100 rows)
3 0  0 SCAN TABLE b (~100 rows)
1 0  0 COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
6 0  0 SCAN TABLE a (~100 rows)
7 0  0 SCAN TABLE b (~100 rows)
5 0  0 COMPOUND SUBQUERIES 6 AND 7 USING TEMP B-TREE 
(INTERSECT)
4 0  0 SCAN SUBQUERY 5 (~100 rows)
0 0  0 COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE 
(EXCEPT)


If the rows can be represented by some key column(s), the lookups could
be implemented with indexes that could be created in advance, instead of
with temporary B-trees:

sqlite> explain query plan select * from a where not exists (select 1 from b 
where b.id = a.id)
 union all select * from b where not exists (select 1 from a 
where a.id = b.id);
sele  order  from  deta
  -    
1 0  0 SCAN TABLE a (~50 rows)
1 0  0 EXECUTE CORRELATED SCALAR SUBQUERY 2
2 0  0 SEARCH TABLE b USING AUTOMATIC COVERING INDEX (id=?) 
(~7 rows)
3 0  0 SCAN TABLE b (~50 rows)
3 0  0 EXECUTE CORRELATED SCALAR SUBQUERY 4
4 0  0 SEARCH TABLE a USING AUTOMATIC COVERING INDEX (id=?) 
(~7 rows)
0 0  0 COMPOUND SUBQUERIES 1 AND 3 (UNION ALL)


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

[sqlite] Symmetric EXCEPT

[Jamie Norrish]

Is there any optimisation done for the case of a symmetric EXCEPT, to
avoid performing duplicate SELECT statements? E.g.:

(SELECT A... EXCEPT SELECT B...) UNION (SELECT B... EXCEPT SELECT A...)

would seem to be a poor approach to getting the set of rows in either A
or B. Are there potentially better generic approaches, or other tricks
I've missed?

(At the moment I have a query that does not use EXCEPT but is instead
tied to the particulars of my schema/data, which is fine. I'm just
curious about the more general case.)

Jamie

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

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/13 06:01, abbood wrote:
> i tried using the memory diagnostics tools in Xcode/Instruments --
> Zombies, GuardMalloc, and Malloc Stack Logging.. but they didn't tell
> me much.. i'm assuming valgrind is better?

They are fairly lightweight and only catch the more simple errors.
valgrind is perfect - it runs your application in such a way that every
single access (read or write) of any byte is tracked as well as the status
of every byte of memory.  This makes things run considerably slower, but
the resulting notifications are top notch.

> i took a sneak peak at valgrind.. and it looked really complicated.. is
> it?

Not in my opinion, and no matter what this is the only tool that will help
you so you'll just have to figure it out.  You just run your app prefixed
with valgrind.  eg if you did this:

  $ myapp arg1 arg2

Then you do this:

  $ valgrind myapp arg1 arg2

It is recommended you compile your app with debugging. Chances are highly
likely that you will then be shown your issue.  It is possible for some
issues to be missed.  One example is that after memory is freed it is kept
in a pool for a while, and then released for reuse making it valid again.
 I have a massive pool so memory is not reused with the option (5GB pool):

  --freelist-vol=500

I also care about memory leaks which valgrind will show on exit.  These
options cause it to show a lot of detail:

  --leak-check=full --leak-resolution=high --show-reachable=yes

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEWueoACgkQmOOfHg372QQtaQCeNfCzwMp2UzNslMjoI538tjBf
0SEAoNEfzvG70jqtWeY7T2LcVfjcFYAM
=jmof
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] To import csv file in C#

[Simon Slavin]

On 9 Feb 2013, at 2:49am, mukesh kumar mehta  wrote:

> Is there any option to import csv file into sqlite database with the help
> of System.Data.Sqlite.dll.
> As like shell command ".import file_name table_name".
> 
> As like "bulk insert" which uses in sqlserver.
> As like "Load Data" which uses in mysql.

The part of the Shell tool which understands CSV format is in the Shell Tool, 
not part of the SQLite library.

There is no part of SQLite which understands CSV format.  If you want to import 
from a CSV file, you will have to put your understanding of CSV format in your 
own code, or find some other external library.  But unless you have tricky 
quotes to handle, CSV format is very simple: separate lines with returns and 
separate columns with commas.  It shouldn't take long to write your own code.

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

Re: [sqlite] Fwd: How to prevent View sqlite database structure and contents from database browsers

[Stephen Chrzanowski]

SQLite has no knowledge of users or password protection.  Unlike MSSQL or
MySQL or Oracle, you don't log into the database with a username and
password, and there is no real DBMS to handle permissions.  Filesystem
level locking is pretty much the closest thing you're going to get to
protecting the data within.

I'd suggest encryption to at least block people from getting access to the
file content, however, once the encryption is broken, R/W access will be
permitted.  If you want a total "black box" scenario, this is probably
going to be your best bet.  If you want to block write access, you could
create a new user account, give RW permissions to that user, then give just
R access to everyone else.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] To import csv file in C#

[mukesh kumar mehta]

Is there any option to import csv file into sqlite database with the help
of System.Data.Sqlite.dll.
As like shell command ".import file_name table_name".

As like "bulk insert" which uses in sqlserver.
As like "Load Data" which uses in mysql.


-- 
Thanks & Regards
Mukesh Kr. Mehta
Software Developer
Bonanza Portfolio Ltd.
Mob No:- 09311473211
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[abbood]

humm... i think you are right.. the last time i spoke with you the error
did indeed disappear.. but now it came back (embarrassed me infront of my
client lol)..

i tried using the memory diagnostics tools in Xcode/Instruments -- Zombies,
GuardMalloc, and Malloc Stack Logging.. but they didn't tell me much.. i'm
assuming valgrind is better?

i took a sneak peak at valgrind.. and it looked really complicated.. is it?
do you have pointers about some light weight tutorials or intros? i found
their documentation and tutorials a bit heavy handed.

A


On Sat, Jan 26, 2013 at 6:26 PM, Roger Binns [via SQLite] <
ml-node+s1065341n66730...@n5.nabble.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 25/01/13 23:45, abbood wrote:
> > i fixed it!! you were right! it's not to do with the guts of sql..
> > rather it's to do with my incorrect sql statement..
>
> Huh?  There is no SQL statement, valid or not, that can cause memory
> errors.  It looks like SQLite is the victim of some other memory
> mismanagement in your app and changing the SQL has just changed what code
> will fall victim to it.
>
> It is a very good idea to run valgrind before proceeding.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlEEA6AACgkQmOOfHg372QSswACgoIg1jidTzar4EVVfQmFZlDwb
> ZuAAn1iIuUz84T4Gpzgv2Q58U1zYq9MR
> =z+X5
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> [hidden email] 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://sqlite.1065341.n5.nabble.com/using-the-same-sqlite-parameter-more-than-once-causes-premature-memory-deallocation-tp66687p66730.html
>  To unsubscribe from using the same sqlite parameter more than once causes
> premature memory deallocation, click 
> here
> .
> NAML
>




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/using-the-same-sqlite-parameter-more-than-once-causes-premature-memory-deallocation-tp66687p66974.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ranking in a view

[Michael Black]

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2013-February/04
4367.html


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer
Sent: Saturday, February 09, 2013 7:01 AM
To: SQLite-user.org
Subject: [sqlite] ranking in a view

There was recently a post where someone was trying to update the ranking of
records in a table and someone else presented a clever view which did the
ranking and thus required no table update.  I wanted to try and understand
how this view worked but when I have looked back to find it, I can't.
Could someone please repost that view or help me find it in some way?

Thanks in advance,

Jeff
___
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

[sqlite] ranking in a view

[Jeff Archer]

There was recently a post where someone was trying to update the ranking of
records in a table and someone else presented a clever view which did the
ranking and thus required no table update.  I wanted to try and understand
how this view worked but when I have looked back to find it, I can't.
Could someone please repost that view or help me find it in some way?

Thanks in advance,

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

[sqlite] From SQLITE_CONSTRAINT to something more specific

[Eric Sink]

I'm trying to use information from sqlite3_errmsg() to figure out what 
*kind* of SQLITE_CONSTRAINT happened.


Cruising the archives of this mailing list, I see past discussions about 
the desire for really complete error information, including the name of 
the specific constraint that failed (and ideally in a format which does 
not need to be parsed or is designed to be parsed).  I add my vote for 
how cool that functionality would be. For now, I'm just looking for a 
way to distinguish between the major types of constraint violations, 
(unique, check, foreign key, etc).


I would welcome remarks from the SQLite developers on the code snippet 
below.


Specific questions:

(1)  What is the likelihood of future changes to the phrasing of 
sqlite3_errmsg() strings?


(2)  Is there any situation where (0 == strcmp(sqlite3_errmsg(db), 
"constraint failed")) and the violation was NOT a CHECK constraint?


(3)  Dare I hold out hope for extended error codes like the following?

#define SQLITE_CONSTRAINT_UNIQUE  (SQLITE_CONSTRAINT | (1<<8) )
#define SQLITE_CONSTRAINT_FOREIGN_KEY (SQLITE_CONSTRAINT | (2<<8) )
#define SQLITE_CONSTRAINT_CHECK   (SQLITE_CONSTRAINT | (3<<8) )
#define SQLITE_CONSTRAINT_NOT_NULL(SQLITE_CONSTRAINT | (4<<8) )

My code snippet:


if (SQLITE_CONSTRAINT == rc)
{
const char* psz_errmsg = sqlite3_errmsg(psql);

// We're on thin ice here.  sqlite doesn't make any promises about
// what the errmsg string will contain for various constraint 
violations.
// Nonetheless, this works.  If sqlite changes the errmsgs in the 
future,

// this will break.  We've got test cases to detect this.

if (strstr(psz_errmsg, "not unique"))
{
// TODO should occur at the end of the string
...
}
else if (strstr(psz_errmsg, "foreign key"))
{
// TODO could check for exact strcmp match to "foreign key 
constraint failed"


...
}
else if (strstr(psz_errmsg, "constraint failed"))
{
// TODO could check for exact strcmp match to "constraint failed"
// TODO riskiest one, since sqlite doesn't say it was a check 
constraint
// TODO but it seems to use a more specific phrase in all other 
cases except check constraints


...
}
}


Thanks!

--
E

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