Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

[Joe Mistachkin]

Gilles Ganault wrote:
>
> Got it: "Dependency Walker" shows that the (no-)bundle versions rely
> on MSVCR110.DLL, which is part of the "Visual C++ Redistributable for
> Visual Studio 2012 Update 1", available here:
> 
> www.microsoft.com/en-us/download/details.aspx?id=30679
> 
> Once installed, the error goes away.
> 
> Maybe the wiki should be edited accordingly.
>

Actually, ALL the non-static packages on the download page require the MSVC
runtime libraries of the appropriate version to be installed. 

The download page does mention these requirements in each applicable
download
package section; however, perhaps there should be a more prominent notice on
the top of the download page?

--
Joe Mistachkin

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

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

[Gilles Ganault]

On Fri, 19 Apr 2013 23:42:19 +0200, Gilles Ganault
 wrote:
>Thanks the tip. More testing in the IDE shows that it works OK even
>when editing the PATH, and I can no longer trigger the  "Unable to
>load DLL 'SQLite.Interop.dll': The specified module could not be
>found. (Exception from HRESULT: 0x8007007E)". Weird.

Got it: "Dependency Walker" shows that the (no-)bundle versions rely
on MSVCR110.DLL, which is part of the "Visual C++ Redistributable for
Visual Studio 2012 Update 1", available here:

www.microsoft.com/en-us/download/details.aspx?id=30679

Once installed, the error goes away.

Maybe the wiki should be edited accordingly.

Thank you.

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

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

[Gilles Ganault]

On Thu, 18 Apr 2013 23:46:58 -0400, markus diersbock
 wrote:
>Since it worked, you can move the files to any final folder, and add a
>reference to System.Data.SQLite.dll
>
>And add your folder to PATH, to find SQLite.Interop.dll

Thanks the tip. More testing in the IDE shows that it works OK even
when editing the PATH, and I can no longer trigger the  "Unable to
load DLL 'SQLite.Interop.dll': The specified module could not be
found. (Exception from HRESULT: 0x8007007E)". Weird.

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

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Richard Hipp]

FWIW:  The following script demonstrates the problem:

CREATE TABLE t1(x INTEGER, y INTEGER);
CREATE INDEX t1xy ON t1(x,y);
CREATE INDEX t1y ON t1(y);

CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);

CREATE TABLE t3(x INTEGER PRIMARY KEY, y INTEGER);
CREATE INDEX t3y ON t3(y);

.wheretrace off
explain query plan
SELECT *
  FROM t1, t2, t3
 WHERE t1.x IN (1,2,3,4)
   AND t1.y IN (1,2,3,4)
   AND t1.y = t2.x
   AND t3.y = 0
   AND t3.x = t1.y
;
.print -
explain query plan
SELECT *
  FROM t1, t2, t3
 WHERE t1.x IN (1,2,3,4)
   AND t1.y = t2.x
   AND t1.y IN (1,2,3,4)
   AND t3.y = 0
   AND t3.x = t1.y
;


This is not technically a bug, since it gets the right answer in all
cases.  It is just that sometimes the answer arrives more slowly than you
would like.  The problem stems from the two constraints on t1.y, and the
fact that the query optimizer has to choose between them.

On Fri, Apr 19, 2013 at 9:30 AM, Mario M. Westphal  wrote:

>
> I'm currently uploading the database and will send you a link to it.
>
> -- Mario
>
>
> ___
> 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] Variable-length records

[Jay A. Kreibich]

On Fri, Apr 19, 2013 at 08:19:57AM +0200, Hick Gunter scratched on the wall:
> IIRC temporary tables are limited to the connection that creates them.

  Yes.  So are in-memory databases.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Richard Hipp]

On Fri, Apr 19, 2013 at 9:17 AM, Mario M. Westphal  wrote:

> Hi, thanks.
>
> But I'm not sure that I understand you correctly.
>
> Is this behavior considered as something that needs to be looked at by the
> SQLite Team to restore the original performance, or is this how "it is" now
> and I have to find a work-around for good (e.g. applying your suggestion
> with CROSS)?  If you look into this, I stick with the older SQLite version
> for now, which works perfectly for me.
>

I'm looking at it.  The workaround is just that - a way to allow you to
continue functioning until the issue is resolved.

Sending me the database will help.


>
> -- Mario
>
> ___
> 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] Potential problem in SQLite 3.7.16.2

[Mario M. Westphal]

Hi, thanks.
 
But I'm not sure that I understand you correctly.
 
Is this behavior considered as something that needs to be looked at by the
SQLite Team to restore the original performance, or is this how "it is" now
and I have to find a work-around for good (e.g. applying your suggestion
with CROSS)?  If you look into this, I stick with the older SQLite version
for now, which works perfectly for me.
 
-- Mario
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Richard Hipp]

On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal  wrote:

> We can provide a sample database etc. on request.
>

Can you send the database to my private email, please?

-- 
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] Potential problem in SQLite 3.7.16.2

[Richard Hipp]

The change in behavior occurs with http://www.sqlite.org/src/info/38852f158a

If you need a work-around, change INNER to CROSS in the queries and the old
query plan will be restored.

On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal  wrote:

> This is a SQL Script
>
>
> /*
> Application linking to SQLite using the Amalgation.
> Build Tool: C++, Visual Studio 2012, Windows 7 64-Bit
>
> The table schema and the query enclosed below are in use
> for over one year and various SQLite versions.
>
> After downloading and compiling in the SQLite 3.7.16.2, we have a problem.
>
> The INSERT statement which takes only a few seconds with previous builds
> now does not return, at least not within several minutes.
> It's hard to debug the amalgation in Visual Studio but it looks like
> SQLite would be caught in an internal loop inside the step() function call.
>
> Replacing the latest version of SQLite with 3.7.15.1 (which was the one we
> used before)
> and re-compiling our application solves the problem. The INSERT works again
> in a few seconds.
>
> We can provide a sample database etc. on request.
> */
>
>
> /* Create */
>
> CREATE VIRTUAL TABLE md_fts_core USING
> fts4(group_oid,tag_oid,file_oid,lang,data);
>
> CREATE TABLE md_fts_core_tag (oid INTEGER PRIMARY KEY, type INTEGER);
> CREATE INDEX idx_md_fts_core_tag_type ON md_fts_core_tag(type);
>
> CREATE TABLE md_tag (oid INTEGER PRIMARY KEY, class INTEGER, group_oid
> INTEGER, id TEXT, tag TEXT, idx INTEGER, dtype INTEGER, ntype TEXT, cnt
> INTEGER, repeat INTEGER, flags INTEGER, FOREIGN KEY(group_oid) REFERENCES
> md_tag_group(oid) ON DELETE CASCADE);
> CREATE INDEX idx_md_tag_tag ON md_tag(tag);
>
> CREATE TABLE md_tag_group (oid INTEGER PRIMARY KEY, src INTEGER, id TEXT);
>
> CREATE TABLE md_tag_data (oid INTEGER, tag_oid INTEGER, tdata TEXT, rdata
> TEXT, lang TEXT, flags INTEGER, FOREIGN KEY(tag_oid) REFERENCES md_tag(oid)
> ON DELETE CASCADE);
> CREATE INDEX idx_md_tag_data_flags ON md_tag_data(flags);
> CREATE INDEX idx_md_tag_data_oid ON md_tag_data(oid);
> CREATE INDEX idx_md_tag_data_oid_tag_oid ON md_tag_data(oid,tag_oid);
> CREATE INDEX idx_md_tag_data_tag_oid ON md_tag_data(tag_oid);
>
>
> /* This insert does not return (at least not within several minutes in
> 3.7.16.2, but takes about 5-10 seconds with build 3.7.15.1 */
>
> INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data)
>
> SELECT
> g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d
> INNER JOIN
> md_tag t ON d.tag_oid = t.oid
> INNER JOIN
> md_tag_group g ON t.group_oid = g.oid
> INNER JOIN
> md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid  /* which
> tags to include */
> WHERE
> d.oid IN
>
> (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
>
> 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
>
> ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
>
> 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107
> ,108)
> AND d.tag_oid IN (2157,7309,16265,16579)
>
> UNION
>
> SELECT
> /* We use group_concat to fold multiple values for one tag into one
> value for FTS */
> g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ')
> FROM
> md_tag_data d
> INNER JOIN
> md_tag t ON d.tag_oid = t.oid
> INNER JOIN
> md_tag_group g ON t.group_oid = g.oid
> INNER JOIN
> md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid /* which
> tags to include */
> WHERE
> d.oid IN
>
> (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
>
> 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
>
> ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
>
> 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107
> ,108)
> AND d.tag_oid IN (2157,7309,16265,16579)
> GROUP BY
> d.tag_oid,d.oid
>
>
> ___
> 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] Questions about Timeline

[Richard Hipp]

On Thu, Apr 18, 2013 at 12:51 PM, Aimard Janvier  wrote:

>
>
> Hello,
>
> I would like to know how to access more than 200 entries (previous months
> or years) on your Timeline.
>

Look on the URL and find the place where is says "n=200".  Change the 200
to whatever number you want.

-- 
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] Potential problem in SQLite 3.7.16.2

[support]

Hi, Celemens

I created the requested log data by running the same operations in my
application, once compiled with the 3.7.15.1 version (OK) and once with the
3.7.16.2 (BAD performance). The query plans are quite different.

1. --- The query my application runs for this test
 
INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) 
 
SELECT g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d 
INNER JOIN md_tag t ON d.tag_oid = t.oid 
INNER JOIN md_tag_group g ON t.group_oid = g.oid 
INNER JOIN md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid 
WHERE d.oid IN
(1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
85,86,87,88,89,90,91,92,93,94,95,96,97,98)  
AND d.tag_oid IN (2157,7309,16265,16579)  
 
UNION 
 
SELECT g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') FROM
md_tag_data d 
INNER JOIN md_tag t ON d.tag_oid = t.oid 
INNER JOIN md_tag_group g ON t.group_oid = g.oid 
INNER JOIN md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid 
WHERE d.oid IN
(1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
85,86,87,88,89,90,91,92,93,94,95,96,97,98)  
AND d.tag_oid IN (2157,7309,16265,16579)  
 
GROUP BY d.tag_oid,d.oid 
 



 
2. --- Plan of 3.7.15.1  (GOOD)
 
SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid
(tag_oid=?) (~65 rows)
EXECUTE LIST SUBQUERY 2
EXECUTE LIST SUBQUERY 2
SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1
rows)
SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid
(tag_oid=?) (~65 rows)
EXECUTE LIST SUBQUERY 3
EXECUTE LIST SUBQUERY 3
SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1
rows)
SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
USE TEMP B-TREE FOR GROUP BY
COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
 

3. --- Plan of 3.7.16.2 (BAD)
 

SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4
rows)
EXECUTE LIST SUBQUERY 2
SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows)
EXECUTE LIST SUBQUERY 2
SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid
(tag_oid=?) (~2 rows)
EXECUTE LIST SUBQUERY 2
EXECUTE LIST SUBQUERY 2
SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4
rows)
EXECUTE LIST SUBQUERY 3
SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows)
EXECUTE LIST SUBQUERY 3
SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid
(tag_oid=?) (~2 rows)
EXECUTE LIST SUBQUERY 3
EXECUTE LIST SUBQUERY 3
USE TEMP B-TREE FOR GROUP BY
COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
 

 

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

Re: [sqlite] Variable-length records

[Bk]

Thank You



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Variable-length-records-tp68277p68300.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

[sqlite] Questions about Timeline

[Aimard Janvier]

Hello,

I would like to know how to access more than 200 entries (previous months or 
years) on your Timeline.

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

Re: [sqlite] Variable-length records

[Hick Gunter]

IIRC temporary tables are limited to the connection that creates them.

-Ursprüngliche Nachricht-
Von: Simon Slavin [mailto:slav...@bigfraud.org]
Gesendet: Donnerstag, 18. April 2013 19:30
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Variable-length records


On 18 Apr 2013, at 10:38am, Bk wrote:

> can some one tell me which of the tcl test script tests below two
> points of SQLite ?
>
> 1)  Variable-length records

If you're using the term 'variable length' because you're used to a different 
SQL engine, then there's a paradigm shift: almost all SQLite records are 
variable-length.  SQLite does not support fixed-length text fields: all text 
fields can be of any length.  It even uses variable numbers of bytes to encode 
integer values.  So almost all testing you do of SQLite will be testing 
variable length fields.



> 2) Internal or temporary databases: load the data into an in-memory
> SQLite database and use queries with joins and ORDER BY clauses to
> extract the data in the form and order needed

SQLite distinguishes between 'memory' and 'temporary'.  The two things do 
different things.  You can even have something which combines the two.  This is 
how you do memory:



You create TEMPORARY TABLES using "CREATE TEMPORARY TABLE".

Temporary tables are usually stored on disk.  The only difference is that 
closing the database automatically deletes those tables.  (Actually, I'm not 
sure how multiple connections to a temporary TABLE may or may not work.)

There are numerous tests in the test suites which test memory and/or temporary 
features.  If you run the whole test suite, you'll get them all.

Simon.
___
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