Re: [sqlite] Query problems

2013-09-04 Thread Keith Medcalf
> 1. If you define a column with NOCASE and later an index without, it > won't be possible to insert two values differing only by case, > because the column will reject it. Of course it will accept the value, unless you declared the column unique so that a unique index is created using the

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread James K. Lowden
On Tue, 3 Sep 2013 18:43:52 -0600 Jared Albers wrote: > When using relatively long table names like `TABLE_{table #}_{some > unique identifying name that adds 120 or so characters}`, creation of > a database with 10,000 tables takes approximately 60 seconds. I find

Re: [sqlite] Query problems

2013-09-04 Thread James K. Lowden
On Tue, 3 Sep 2013 23:50:09 +0200 Eduardo Morras wrote: > Don't know if column collation overrides index collation or viceversa. It's probably simpler to think of them as two things, table and index. Neither "overrides" the other. Ideally, they use the same collation. In

Re: [sqlite] BETWEEN and explicit collation assignment

2013-09-04 Thread James K. Lowden
On Tue, 3 Sep 2013 18:37:42 -0500 Nico Williams wrote: > > There's no need to qualify string literals, as it turns out. SQLite > > makes a reasonable choice in that context. When comparing a string > > literal to a column, the literal (in effect) takes on the collation >

Re: [sqlite] pDb->inTrans

2013-09-04 Thread E. Timothy Uy
Sorry, please ignore this message. I spaced out and forgot to 'fossil up' after 'fossil sync'. On Wed, Sep 4, 2013 at 2:48 PM, E. Timothy Uy wrote: > Hi, I'm getting the following error in the latest code (after merging with > my own). > > sqlite3.c(145194) : error C2039:

[sqlite] pDb->inTrans

2013-09-04 Thread E. Timothy Uy
Hi, I'm getting the following error in the latest code (after merging with my own). sqlite3.c(145194) : error C2039: 'inTrans' : is not a member of 'Db' sqlite3.c(9976) : see declaration of 'Db' sqlite3.c(145234) : error C2039: 'inTrans' : is not a member of 'Db' sqlite3.c(9976) :

Re: [sqlite] help needed for major SQLite problem

2013-09-04 Thread C M
On Mon, Sep 2, 2013 at 3:28 PM, E.Pasma wrote: > Op 2 sep 2013, om 17:58 heeft C M het volgende geschreven: > >> ... >> >> Is setting up APSW and making the transition from pysqlite2's wrapper >> fairly straightforward, or would I have to re-do all the database queries? >>

Re: [sqlite] Documentation update request

2013-09-04 Thread Simon Slavin
On 4 Sep 2013, at 3:05pm, Markus Schaber wrote: > Afaics, this applies to partial indices for similar reasons. I did not even know partial indices was implemented. Thank you. Simon. ___ sqlite-users mailing list

Re: [sqlite] Please help me optimize this LEFT JOIN query.

2013-09-04 Thread Yuzem
Thanks Igor but now it is taking even more time: 3.139s Any other idea? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71003.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

2013-09-04 Thread Igor Tandetnik
On 9/4/2013 10:21 AM, Yuzem wrote: SELECT genres name,count(genres.movies) count,substr(group_concat(grouped.movies,' '),1,40) src,substr(group_concat(grouped.icon_modified,' '),1,80) icon_modified FROM genres LEFT JOIN (SELECT movies,icon_modified FROM movies WHERE icon_modified != '') grouped

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Jared Albers
Hi Richard and others, The code to reproduce this problem can be found in a Stack Overflow question I created here: http://stackoverflow.com/questions/18603123/table-name-length-in-sqlite-affects-performance-why -Jared On Wed, Sep 4, 2013 at 5:36 AM, Richard Hipp wrote: > > >

[sqlite] Please help me optimize this LEFT JOIN query.

2013-09-04 Thread Yuzem
I have 2 tables: CREATE TABLE movies ( movies UNIQUE, name, icon_modified ); CREATE TABLE genres ( genres, movies, ); I want to construct genres icons and each icon must display 4 movies. I need to get: genres from genres and movies,icon_modified from movies. This is the query I have:

Re: [sqlite] Documentation update request

2013-09-04 Thread Markus Schaber
Hi, von Simon Slavin > Could the page > > > > please be updated to reflect the introduction of WAL mode ? I know this > doesn't change the format of the database file itself, but it does change the > format of files on disk, and it does mean that earlier

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

2013-09-04 Thread Eric Minbiole
We ran into a similar issue while working on our own (semi-custom) SQLite encryption mechanisms. In my case, it was caused by the SQLite page cache reading the "File Change Counter" (Header page, offset 24) directly from the ciphertext *before* decrypting the page. (Presumably, this is done for

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Dominique Devienne
On Wed, Sep 4, 2013 at 2:43 AM, Jared Albers wrote: > On my machine, when using relatively short table names like > `TABLE_{table #}`, creation of a database with 10,000 tables takes > approximately 14 seconds. These table names vary from 7 to a max of 11 > characters. >

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Marc L. Allen
Not to mention having to check each new table to see if it's already in the database and the associated physical reads that might be associated with that. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent:

Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Clemens Ladisch
Harry Beezhold wrote: > Sqlite - What a cool product! Do you really think buggy products are cool? ;-) > The following is a description of an apparent bug in > the calculation of a row count of a left joined table. > The leftjoin/count technique seems to work for each join/count, separately.

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Jay A. Kreibich
On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall: > On my machine, when using relatively short table names like > `TABLE_{table #}`, creation of a database with 10,000 tables takes > approximately 14 seconds. These table names vary from 7 to a max of 11 > characters. >

Re: [sqlite] SQLite port to RTOS

2013-09-04 Thread John McKown
I can answer one question. There is an entire C language API of sqlite functions that your application can call. You don't need to create and execute some sort of "command line" interface. The C API is documented here: http://sqlite.org/c3ref/intro.html I know nothing about RTOS, but if it does

[sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

2013-09-04 Thread Brzozowski, Christoph
Hello, Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) in a multi user scenario, where multiple users on different machines access the same database, which lies on a network share, or in a single user scenario, where the database lies locally but is accessed

Re: [sqlite] count from 2 tables

2013-09-04 Thread John McKown
The answer to you exact question is "Yes". Didn't help much did it? But I will at least start off by giving some hints. You need to do an inner join on the common column (service_no). You need to use the HAVING clause to select only those entries with more than 2 entries in the result table

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Simon Slavin
On 4 Sep 2013, at 1:43am, Jared Albers wrote: > On my machine, when using relatively short table names like > `TABLE_{table #}`, creation of a database with 10,000 tables takes > approximately 14 seconds. These table names vary from 7 to a max of 11 > characters. > >

[sqlite] Documentation update request

2013-09-04 Thread Simon Slavin
Could the page please be updated to reflect the introduction of WAL mode ? I know this doesn't change the format of the database file itself, but it does change the format of files on disk, and it does mean that earlier and later versions of SQLite

Re: [sqlite] Tcl/Perl/Python Integration with SQLite3

2013-09-04 Thread Simon Slavin
On 3 Sep 2013, at 2:31pm, ashutosh_maheshwa...@dell.com wrote: > 1. Documentation says, SQLite has in-build support for TCL with sqlite3 > command. But TCL 8.6 itself is coming with TDBC options for SQLite and the > TCL distribution has got its own SQLite package in it. My problem is

Re: [sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Richard Hipp
On Tue, Sep 3, 2013 at 8:43 PM, Jared Albers wrote: > Attached is a code example that reproduces > the problem. > The sqlite-users@sqlite.org mailing list strips off attachments. Can you send a link to your code? -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Richard Hipp
On Tue, Sep 3, 2013 at 10:41 AM, Harry Beezhold wrote: > > > The attached database (view.db) has 3 tables > The sqlite-users@sqlite.org mailing list strips off attachments. Can you send a link instead? -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] [CRASH] Segmentation fault since 3.8.0

2013-09-04 Thread Alexandre Courbot
On Tue, Sep 3, 2013 at 9:20 PM, Richard Hipp wrote: > On Mon, Sep 2, 2013 at 11:07 PM, Alexandre Courbot wrote: >> >> Hi everyone, (not subscribed to the ML, please CC) >> >> The following happens since 3.8.0 (tested on both 3.8.0 and 3.8.0.1): >> >> $ cat

[sqlite] Table name length in sqlite affects performance. Why?

2013-09-04 Thread Jared Albers
I'm noticing that the length of table names affects performance during creation of those tables. Attached is a code example that reproduces the problem. To compile the example: gcc main.c sqlite3.c -O3 -DLONG_NAMES -DNDEBUG gcc main.c sqlite3.c -O3 -DNDEBUG On my machine, when using relatively

[sqlite] count from 2 tables

2013-09-04 Thread ChingChang Hsiao
There are 2 related table above. There are service_no 1,2,3,5. Service 1,2,3 are type e-line, service 5 is type e-lan. Service 1,2,3 own 2 service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3). What is the count of services have more than 2 service_port(sp) and type is e-lan?

[sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Harry Beezhold
Hi, Sqlite - What a cool product! The following is a description of an apparent bug in the calculation of a row count of a left joined table. In the intended application I plan to use this type of query to feed and filter the list on the "choose a person" popup in a genealogy

[sqlite] Tcl/Perl/Python Integration with SQLite3

2013-09-04 Thread Ashutosh_Maheshwari1
Hi, In my project, We are using all the 3 scripting language mentioned in the subject line. There is a requirement to support a database in my project/product and we should be able to write scripts to access/modify the database. We selected SQLite to suit our requirements. Now the problem

Re: [sqlite] SQLite port to RTOS

2013-09-04 Thread Kees Nuyt
On Wed, 4 Sep 2013 10:57:44 +0530, Pratheek Prakash wrote: > Hi Kees Nuyt, > > That was really helpful. Also I have another doubt. > Eventually I will be running sqlite integrated with other > modules in a board. That is what SQLite is made for. > As far as I have