date:20120407

Re: [sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

2012-04-07 Thread Josh Gibbs

Thanks, but I can't do that because I'm batching up multiple writes in 
transactions to get performance.  The errors cause the whole transaction 
to need to be rolled back.


On 8/04/2012 11:20 a.m., Igor Tandetnik wrote:

Josh Gibbs  wrote:

The method that must be used is as follows:

CREATE TABLE test_table (property TEXT PRIMARY KEY, value TEXT);

SQLite: INSERT OR IGNORE INTO test_table VALUES('prop','val');
Postgres: INSERT INTO test_table SELECT 'prop','val' WHERE NOT EXISTS
(SELECT 1 FROM test_table WHERE property='prop');

Another option: use a plain vanilla INSERT, and simply ignore any 
constraint-related errors in your application.



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

Re: [sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

2012-04-07 Thread Igor Tandetnik

Josh Gibbs  wrote:
> The method that must be used is as follows:
> 
> CREATE TABLE test_table (property TEXT PRIMARY KEY, value TEXT);
> 
> SQLite: INSERT OR IGNORE INTO test_table VALUES('prop','val');
> Postgres: INSERT INTO test_table SELECT 'prop','val' WHERE NOT EXISTS
> (SELECT 1 FROM test_table WHERE property='prop');

Another option: use a plain vanilla INSERT, and simply ignore any 
constraint-related errors in your application.
-- 
Igor Tandetnik

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

[sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

2012-04-07 Thread Josh Gibbs


Looking for some performance advice before I go testing this myself.

I'm porting some code that's currently running with SQLite as its DB 
engine over to postgres.  SQLite will still be an option so I need to 
maintain compatibility across both engines.  I've run into the common 
postgres problem of having no direct equivalent method to 'INSERT OR 
IGNORE'.


The method that must be used is as follows:

CREATE TABLE test_table (property TEXT PRIMARY KEY, value TEXT);

SQLite: INSERT OR IGNORE INTO test_table VALUES('prop','val');
Postgres: INSERT INTO test_table SELECT 'prop','val' WHERE NOT EXISTS 
(SELECT 1 FROM test_table WHERE property='prop');


The Postgres syntax works fine on SQLite, but I'm wondering if there's 
any major performance difference between the two.  'EXPLAIN'ing the two 
shows 23 steps for INSERT OR IGNORE, and 53 steps for the WHERE NOT 
EXISTS method, but without setting up a large test I can't determine if 
there's a significant difference between the two.


My options are to either switch on the DB type, or just go with the 
compatible query, but the performance will be the determining factor.


Thanks for any info that can be provided.


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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

2012-04-07 Thread Yuriy Kaminskiy

On 2011/10/23, Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> Yuriy Kaminskiy wrote:
 When WHERE condition is constant, there are no need to evaluate and check 
 it for
 each row. It works, but only partially:
>>> ...
 [In fact, you can move out out loop not only *whole* constant WHERE, but 
 also
 all constant AND terms of WHERE, like this:
 SELECT * FROM t WHERE const1 AND notconst AND const2 ->
 SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
 I'll take a shot on that later.]
>>> Here it goes.
>>>
>>> Prerequisite: previous patch.
>>> Passes quick regression test (make test).
>>> Possible problem: short-circuits evaluation. Should not be problem, IMO, as 
>>> only
>>> constants references? Please verify.
>> Ping.
> Ping.
Ping.
For convenience all 3 patches collected below (needed no change for 3.7.11).
-- 
Part 1: Move whereSplit() to unbreak constant condition elimination.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:04:58.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:06:30.0 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   /* Special case: a WHERE clause that is constant.  Evaluate the
   ** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: optimize "WHERE const AND notconst" too

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:41:44.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:48:33.0 +0400
@@ -4636,6 +4636,21 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int j;
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+  }
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: Remove now-redundant sqlite3ExprIsConstantNotJoin call.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 21:23:00.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 21:25:06.0 +0400
@@ -4626,18 +4626,12 @@ WhereInfo *sqlite3WhereBegin(
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);

-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   {
-/* Move const in "WHERE const AND notconst" out of internal loop */
+/* Special case: constant AND subterm of WHERE clause. Evaluate the
+** expression and either jump over all of the code or fall thru.
+*/
 int j;
 for(j=i=0; inTerm; i++){
   if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){

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

Re: [sqlite] undefined symbol: sqlite3_stricmp

2012-04-07 Thread Alexey Pechnikov

Command-line shell works fine but tclsqlite package does not:

$ tclsh8.5 test.tcl
/usr/lib/libsqlitefts3.so: undefined symbol: sqlite3_stricmp
while executing
"db eval {SELECT load_extension('/usr/lib/libsqlitefts3.so')}"


$ sqlite3
SQLite version 3.7.12 2012-04-07 11:00:54
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT load_extension('/usr/lib/libsqlitefts3.so');
load_extension('/usr/lib/libsqlitefts3.so')
sqlite>



2012/4/7 Richard Hipp 

> On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov  >wrote:
>
> > Compiled as extension FTS3 module produce error "undefined symbol:
> > sqlite3_stricmp" by loading.
> > The problem exists in last release and current trunk.
> >
>
>
> See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231
>
> Probably you are trying to link against a pre-3.7.11 version of SQLite,
> perhaps the one that comes with your OS, instead of the latest version.
>
>
>
> >
> > --
> > Best regards, Alexey Pechnikov.
> > http://pechnikov.tel/
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined symbol: sqlite3_stricmp

2012-04-07 Thread Richard Hipp

On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov wrote:

> Compiled as extension FTS3 module produce error "undefined symbol:
> sqlite3_stricmp" by loading.
> The problem exists in last release and current trunk.
>


See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231

Probably you are trying to link against a pre-3.7.11 version of SQLite,
perhaps the one that comes with your OS, instead of the latest version.



>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Compiling with VS 2010 (force foreign key constraints)

2012-04-07 Thread Matjaž Cof

OK, i am getting somewhere.
I try to use SQLite with custom compiles dll (System.Data.SQLite,
System.Data.SQLite.Linq, SQLite.Interop) and it works. No error, which is a
huge think for me.
But for foregein key ID i can add anythink i want also ID that is not in
reference table.
I did edit SQLite.Interop\props\sqlite3.props and
SQLite.Interop\props\sqlite3.vsprops and add param to common_defines:
SQLITE_THREADSAFE=1;SQLITE_ENABLE_COLUMN_METADATA=1;SQLITE_ENABLE_STAT3=1;SQLITE_ENABLE_FTS3=1;SQLITE_ENABLE_LOAD_EXTENSION=1;SQLITE_ENABLE_RTREE=1;SQLITE_SOUNDEX=1;SQLITE_DEFAULT_FOREIGN_KEYS=1

I am close. Any idea now?

2012/4/7 Joe Mistachkin 

>
> Matjaž Cof wrote:
> >
> > Thank's to all.
> > Stupid me. But still not working. Is there any documentation how to
> compile
> > SQLite.NET.2010 solution with  SQLITE_DEFAULT_FOREIGN_KEYS=1.
> >
>
> This would require modifying the Visual Studio properties file for the
> correct version of
> Visual Studio (2010 in your case?) and then recompiling the SQLite.Interop
> project.
>
> The Visual Studio properties files are located in the source tree at the
> following location:
>
>
> http://system.data.sqlite.org/index.html/dir?name=SQLite.Interop/props
>
> The above location corresponds to the "SQLite.Interop\props" directory
> inside the root
> of the local source tree.  The file that you'll need to modify is either
> the
> "SQLite.Interop\props\sqlite3.vsprops" file (for Visual Studio 2005 or
> 2008)
> or the
> "SQLite.Interop\props\sqlite3.props" file (for Visual Studio 2010).
>
> Either way, the SQLITE_COMMON_DEFINES macro should be modified to add the
> extra preprocessor defines that you require.
>
> >
> > I think, that the problem is, that i am using
> > precompiled SQLite.Interop.dll (which is compiled without
> > SQLITE_DEFAULT_FOREIGN_KEYS=1 - i think - downloaded from
> >
>
> http://system.data.sqlite.org/downloads/1.0.80.0/sqlite-netFx40-binary-Win32
> -2010-1.0.80.0.zip
> > ).
> >
>
> Yes, that could be the problem.  The precompiled binary does not have that
> preprocessor
> define enabled.
>
> >
> > Is there any documentation, how is SQLite.Interop.2010 project integraded
> > into  System.Data.SQLite.2010 project?
> >
>
> Please refer to the following web pages for details on how the
> System.Data.SQLite build process
> works:
>
>http://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki
>
>http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki
>
> Other than that, having a good understanding of Visual Studio, interop with
> native code, and
> multi-language solutions is also very useful.
>
> --
> Joe Mistachkin
>
> ___
> 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] undefined symbol: sqlite3_stricmp

2012-04-07 Thread Alexey Pechnikov

Compiled as extension FTS3 module produce error "undefined symbol:
sqlite3_stricmp" by loading.
The problem exists in last release and current trunk.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

Re: [sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

[sqlite] INSERT OR IGNORE vs INSERT WHERE NOT EXISTS

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

Re: [sqlite] undefined symbol: sqlite3_stricmp

Re: [sqlite] undefined symbol: sqlite3_stricmp

Re: [sqlite] Compiling with VS 2010 (force foreign key constraints)

[sqlite] undefined symbol: sqlite3_stricmp

8 matches

Site Navigation

Mail list logo

Footer information