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 nocase collation.  Whether or not the 
index with the binary collation is unique is irrelevant since the two values 
are different, as far as that index is concerned.

> 2.  If you define a column with default collation and later an index
> with NOCASE (successfully, because at the time of creation no two
> values differed only by case) then henceforward it *also* won't be
> possible to insert two values differing only by case, because the
> index will reject it.

This would be true only if the index created with collation nocase were a 
unique index.  Otherwise, it would not care that you inserted rows with 
duplicate case-folded values.

> I suppose the current state of affairs -- where indexes have collation
> properties independent of the columns they index -- is more
> historical anomaly than design.  It's hard to see a reason to carry it
> forward in SQLite4.

With this I agree but generally because I do not see any purpose in having 
multiple collations apply to the same attribute under different circumstances.  
Perhaps there may be circumstances where it is useful, but I have never ever 
seen one.




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


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 this a very strange course of interrogation.  Tables are created
once.  Databases with 10,000 tables should be created never (to a
reasonable approximation).  

Is this just an exercise, or is there some horrible real application
out there being slowed down because it's creating thousands of tables a
minute?

--jkl

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


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 the event not, I would think
insertion would fail for any row not meeting both criteria.  That is,

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.  

2.  If you define a column with default collation and later an index
with NOCASE (successfully, because at the time of creation no two
values differed only by case) then henceforward it *also* won't be
possible to insert two values differing only by case, because the
index will reject it.  

I suppose the current state of affairs -- where indexes have collation
properties independent of the columns they index -- is more
historical anomaly than design.  It's hard to see a reason to carry it
forward in SQLite4.  

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


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
> > of the column.
> 
> But SQLite3 is dynamically typed.  Consider a query that uses a UNION
> query as a table source, where the corresponding columns of the
> sub-queries use different collations.  

Each column has a type, and every character column has a collation.  
Each individual value, each row, is compared to the string literal on
its own terms.  

When you say

where NAME = 'George'

the only reasonable thing to do is to compare each NAME with 'George'.
Even if different NAMEs have different collations (as in a UNION,
perhaps), each individual NAME has a particular collation, which
governs the comparison.  

> It's easy to build contrived (and real) queries where SQLite3 can't
> keep track of a collation -- not at run-time, much less at query
> compile-time.  

I might regret this, but if it's easy could you perhaps provide
an example?  

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


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: '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) : see declaration of 'Db'
>
> The problem appears to be that struct Db does not have inTrans, which it
> definitely doesn't.
>
> Before I dig too deep, has anyone else seen this? I wonder if it should
> actually be something other than pDb->inTrans... maybe p->inTrans?
>
> Cheers,
> Tim
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[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) : see declaration of 'Db'

The problem appears to be that struct Db does not have inTrans, which it
definitely doesn't.

Before I dig too deep, has anyone else seen this? I wonder if it should
actually be something other than pDb->inTrans... maybe p->inTrans?

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


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?
>> (Even so, I do them all in one utilities library, so perhaps it would be
>> not too much work).
>> ...
>>
> The transition from Pythons builtin SQLite wrapper to APSW is not too
> hard. But you must know that APSW assumes isolation_level = None (pysqlite
> connect parameter).
>
> I wrote an experimental module, apswdbapi2, that lets you try APSW in a
> pysqlite compatible mode including the isolation_level.
>
> http://pythonhosted.org/**sqmediumlite/src/apswdbapi2.**py.html


Thanks, that looks quite interesting.  I will be in touch with Roger
regarding APSW and if and once I get set up with that, I will give this a
try as well.  I may have a few questions for you if I try it.

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


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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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 on grouped.movies = genres.movies  GROUP BY
genres ORDER BY name"


Try

FROM genres LEFT JOIN movies grouped ON (genres.movies = movies.movies 
AND icon_modified != '')


The use of nested SELECT likely prevents SQLite from using the index on 
movies(movies).

--
Igor Tandetnik

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


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:
>
>
>
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[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:
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 on grouped.movies = genres.movies  GROUP BY
genres ORDER BY name"

The query works correctly but it is very very slow, it takes 2.474s on my
machine, if I run the same query but using JOIN instead of LEFT JOIN it only
takes 0.039s

Is there any way of optimizing these query?
Thanks in advance.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000.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] 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 and later versions of
> SQLite won't play nice under some circumstances.

Afaics, this applies to partial indices for similar reasons.


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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
efficiency: No point in decrypting the page if nothing has changed.)

In my case, I had organized my ciphertext so that there happened to be some
unencrypted housekeeping data at that location, such that the File Change
Counter did not appear to change.  (It did change in the *decrypted* page;
just not in the raw, encrypted format.)  This caused exactly the issues you
report: Changes from one client were not seen by another, quickly leading
to database corruption.  The solution, in my case, was to ensure that
encrypted header bytes 24-27 always changed when the page was re-written,
to ensure that other clients knew there had been a change.

Not sure if that's what's happening here with System.Data.SQLite, but it
does sound very similar to what I encountered.

~Eric


On Wed, Sep 4, 2013 at 8:32 AM, Brzozowski, Christoph <
christoph.brzozow...@siemens.com> wrote:

> 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
> simultaneously by multiple processes on the same machine.
>
> Initially we used a database without password protection and some of our
> synchronization mechanisms which periodically polled data from the database
> worked as intended and our application performed as expected.
>
> When we switched the database to an encrypted one, by adding a password to
> the connection string passed to the ADO.NET Sqlite provider, the
> synchronization mechanisms ceased to work.
>
> Before this modification every time one process wrote some data to the
> database by issuing an UPDATE Sql statement, the modified data was
> instantaneously available to other simultaneously running processes when
> requerying it through a SELECT statement.
>
> After activating password encryption, the read/write behavior seemed to
> change, as the modifications made by one process, were not visible to other
> processes reading from the database at the same time. If you for example
> add some rows to a table in one process, and then refresh the view of the
> same table in another process by requerying and redisplaying it, you would
> not see the additional rows when using an encrypted database. In contrary
> when using an unencrypted database the new rows would get displayed as
> expected.
>
> Even worse, hard-killing all processes accessing the encrypted database
> with eventually running write operations would lead to a corrupt database.
> At least I assume that the database got corrupted as performing subsequent
> read operations using the DataReader classes of System.Data.SQLite to
> obtain table row data led to an AccessViolation exception. This does not
> happen when I repeat the same scenario with a database where encryption is
> disabled. All data is intact, no exceptions when reading it.
>
> Is this behavior a bug caused by some caching introduced, when encryption
> is enabled, or is this a known limitation of System.Data.SQLite when using
> encrypted databases? I.e. encrypted databases should not be used in a
> multi-user or multi-process scenario.
>
> Many thanks and kind regards,
> Christoph Brzozowski
> ___
> 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] 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.
>
> 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.
>
> Creating the database with long table names took over 4 times longer!
>

Have you tried putting your CREATE TABLE statements inside a transaction?

14 sec seems quite slow. Unlike Oracle*, DDL statements in SQLite do not
imply a commit.
Unless you do not put them in a TX, in which case you COMMIT 10,000x times
in your case.

It could be dramatically faster with a TX. Just my $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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: Wednesday, September 04, 2013 8:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Table name length in sqlite affects performance. Why?

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.
> 
> 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.
> 
> Creating the database with long table names took over 4 times longer!
> 
> Why is this the case? Is this expected behavior or a bug?

  You're asking SQLite to deal with 10x the data, and it takes 4x
  longer.  That seems like a win to me.

  Table names are stored as plain-text strings in the sqlite_master
  table.  Making the names much, much bigger is going to add more data
  to the table, including more database pages.  While sqlite_master is
  not ordered, so the insertion shouldn't require shuffling the
  internal B-tree, you're still dealing with a lot more pages, and
  syncing all those pages to disk is going to take longer.

  Like any other large insert, try wrapping the whole thing in a
  transaction.  SQLite is one of the few databases that allows
  transactions on DDL.

   -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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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.
> However, if done together, the counts appear to have been multiplied

I created a simplified example:

  CREATE TABLE persons(personID);
  INSERT INTO "persons" VALUES('I');
  CREATE TABLE parentschildren(parentID, childID);
  INSERT INTO "parentschildren" VALUES('father','I');
  INSERT INTO "parentschildren" VALUES('mother','I');
  INSERT INTO "parentschildren" VALUES('I','son');
  INSERT INTO "parentschildren" VALUES('I','daughter');

A single join works as expected:

  SELECT personID, parents.parentID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID;

  personIDparentID
  --  --
  I   father
  I   mother

If you join that with another table, you get more records because _each_
"I" record matches two children records:

  SELECT personID, parents.parentID, children.childID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID
  LEFT JOIN parentschildren AS children ON personID = children.parentID;

  personIDparentIDchildID
  --  --  --
  I   father  daughter
  I   father  son
  I   mother  daughter
  I   mother  son

This is how SQL joins work.

If you want to get _independent_ parent and children counts, you should
use correlated subqueries instead:

  SELECT personID,
 (SELECT COUNT(*) FROM parentschildren WHERE childID  = personID) AS 
parentCount,
 (SELECT COUNT(*) FROM parentschildren WHERE parentID = personID) AS 
childCount
  FROM persons;

  personIDparentCount  childCount
  --  ---  --
  I   22


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


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.
> 
> 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.
> 
> Creating the database with long table names took over 4 times longer!
> 
> Why is this the case? Is this expected behavior or a bug?

  You're asking SQLite to deal with 10x the data, and it takes 4x
  longer.  That seems like a win to me.

  Table names are stored as plain-text strings in the sqlite_master
  table.  Making the names much, much bigger is going to add more data
  to the table, including more database pages.  While sqlite_master is
  not ordered, so the insertion shouldn't require shuffling the
  internal B-tree, you're still dealing with a lot more pages, and
  syncing all those pages to disk is going to take longer.

  Like any other large insert, try wrapping the whole thing in a
  transaction.  SQLite is one of the few databases that allows
  transactions on DDL.

   -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] 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 not have a "file", where does it
store long term data? If that storage area does not look like a file system
(i.e. no fopen/fread/fwrite type I/O functions), you will need to write
what is called a VFS (Virtual File System) to make it look like a file to
sqlite. More on that is documented here: http://www.sqlite.org/vfs.html

There is some example VFS code, in C, distributed with sqlite. This is
something else which I am not too familiar with.



On Wed, Sep 4, 2013 at 12:27 AM, 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. As far as I have read I suppose that sqlite treat a database as a
> file. Adding data to the database and retrieving data from the database is
> equivalent to write() and read() file operations. But in board where can I
> create that database file like creating one in computer? Is it possible?
> Also for communicating with the sqlite library do I need to use command
> line interface always? Because in board its not possible. Can I call those
> library functions directly from the application?
>  It will be really helpful if you can provide me with
> some inputs on these
>
> Regards
> Pratheek
>
>
-- 
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[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 simultaneously by multiple 
processes on the same machine.

Initially we used a database without password protection and some of our 
synchronization mechanisms which periodically polled data from the database 
worked as intended and our application performed as expected.

When we switched the database to an encrypted one, by adding a password to the 
connection string passed to the ADO.NET Sqlite provider, the synchronization 
mechanisms ceased to work. 

Before this modification every time one process wrote some data to the database 
by issuing an UPDATE Sql statement, the modified data was instantaneously 
available to other simultaneously running processes when requerying it through 
a SELECT statement. 

After activating password encryption, the read/write behavior seemed to change, 
as the modifications made by one process, were not visible to other processes 
reading from the database at the same time. If you for example add some rows to 
a table in one process, and then refresh the view of the same table in another 
process by requerying and redisplaying it, you would not see the additional 
rows when using an encrypted database. In contrary when using an unencrypted 
database the new rows would get displayed as expected.

Even worse, hard-killing all processes accessing the encrypted database with 
eventually running write operations would lead to a corrupt database. At least 
I assume that the database got corrupted as performing subsequent read 
operations using the DataReader classes of System.Data.SQLite to obtain table 
row data led to an AccessViolation exception. This does not happen when I 
repeat the same scenario with a database where encryption is disabled. All data 
is intact, no exceptions when reading it.

Is this behavior a bug caused by some caching introduced, when encryption is 
enabled, or is this a known limitation of System.Data.SQLite when using 
encrypted databases? I.e. encrypted databases should not be used in a 
multi-user or multi-process scenario.

Many thanks and kind regards,
Christoph Brzozowski
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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
(which requires a GROUP BY phrase). And you then need to count the number
of distinct service_no values which pass that test.

I am somewhat hesitant to post the actual code. Why? Because it seems like
something which an SQL person should know. But I will be somewhat more
helpful that I normally am, and post my solution.

select count(distinct a.service_no) as count
 from service_table as a
 inner join service_port_table as b
 on a.service_no = b.service_no
where a.service_type='e-lan'
group by a.service_no
having count(a.service_no) > 2
;

The output is a bit difficult, for me, to read when I enter that in the
sqlite3 program, so I also did:

.separator "\t"
.headers on





On Tue, Sep 3, 2013 at 6:00 PM, ChingChang Hsiao <
chingchang.hs...@overturenetworks.com> wrote:

>
> 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? The answer is 1. Could it be described as one sql statement?
>
>
>
> CREATE TABLE service_table (
> service_nameTEXT, /* name of the service (unique) */
> service_no  INTEGER,  /* internal service number, assigned by
> the system. */
> service_typeTEXT, /* Type of Service. Should be one of
>  the following:
>  e-lan, e-line, e-tree, ip-forward
>  The default should be e-line */
> learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default
> */
> col_status TEXT,
> cfg_status TEXT);
> INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
> INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
> INSERT INTO "service_table"
> VALUES('uni1-evc1',3,'e-line',1,'','committed');
> INSERT INTO "service_table"
> VALUES('zero-touch-test',5,'e-lan',1,'','committed');
>
>
> CREATE TABLE service_port_table(
> sp_no   INTEGER,  /* Range 1 to 256 */
> service_no  INTEGER,  /* The Service this service port belongs
> to */
> sp_idx  INTEGER,  /* This is used to identify the
> classification rule:
>  This should range from 1 to 1 */
> if_idx  INTEGER);  /* Interface Index of the underlying
> interface */
>
> INSERT INTO "service_port_table" VALUES(1,1,1,26);
> INSERT INTO "service_port_table" VALUES(2,1,2,43);
> INSERT INTO "service_port_table" VALUES(1,2,3,53);
> INSERT INTO "service_port_table" VALUES(2,2,4,26);
> INSERT INTO "service_port_table" VALUES(1,3,5,29);
> INSERT INTO "service_port_table" VALUES(2,3,6,54);
> INSERT INTO "service_port_table" VALUES(1,5,20,12);
> INSERT INTO "service_port_table" VALUES(2,5,21,58);
> INSERT INTO "service_port_table" VALUES(3,5,22,27);
>
>

-- 
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Although it might be nice to have the OP's own data, this is a consistently 
reproducible characteristic.  Just write a script or program to generate 
sequential or random table names of different lengths.  I used a spreadsheet to 
generate corresponding 'CREATE TABLE' commands, then pasted them all in a text 
file and used the shell tool.

I'm not certain this can really be characterised as a bug worth fixing.  
Generating hashes will always take longer for longer text.  Nobody expects 
table names to be over 100 characters long.  And the task described above is 
obviously more suited to having all the data in one table, with an extra column 
to distinguish which 'table number' is being used.  But I'll leave that up to 
the team.

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


[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 won't play nice under some circumstances.

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


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 how 
> both version of SQLite would co-exist? If I don't compile TDBC and SQLite 
> packages, a script written in TCL using sqlite3 command does not work, don't 
> know why?
> 
> 2.   Similarly Python 2.6.8 and above and Perl DBD and DBI distribution 
> from CPAN comes with their own version of SQLite integrated with the 
> respective languages. So should I go ahead and install them? Will that 
> interfere with the actual SQLite distribution? Can all the different (now 4) 
> SQLite version refer to the same database for querying, inserting records 
> etc.?

Because SQLite is just a set of file-handling routines, and has no 'server' or 
memory-resident code, there is no central 'distribution' or 'installation' for 
SQLite.  There's no central place that all programs or programming languages 
look for it, and if, for example, Perl offers to 'install' SQLite which it 
means is it will install a version for Perl to use, not for everything else to 
use.

Each program or programming language may have its own copy of the SQLite 
library and all of them may access the same database files without problems.  
The last change in file format for the SQLite database file came with version 
3.3.7 which was issued in 2006.  As long as all your languages and software are 
using version 3.3.7 or later, they can all access the same database files.  At 
the same time.

There is little problem with WAL mode, which is not supported by versions 
before 3.7.0 in 2010 but if you are using any version earlier than that just 
don't use WAL mode in any of your applications.  They'll be a little slower.

What I would suggest you do is download the shell tool from the SQLite site.  
This was written by the SQLite team and gives you a 100% bullet-proof guide as 
to what's in a database.  It's useful if any of your different languages or 
packages claims that the database is corrupt or does not contain the data you 
thought it did.



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


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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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 |sqlite3
>> create table t1(id INTEGER);
>> create table t2(id INTEGER, v INTEGER);
>> insert into t1 values(1);
>> select distinct t1.id from t1 left join t2 on t2.id = t1.id order by
>> t2.v is null;
>> Segmentation fault (core dumped)
>
>
> Thanks for the simple test case!  See
> http://www.sqlite.org/src/tktview/be84e357c03 for the ticket.  We'll get to
> work on this right away.

Seen on the ticket this has been fixed already, thanks for the quick reaction!

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


[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 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.

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.

Creating the database with long table names took over 4 times longer!

Why is this the case? Is this expected behavior or a bug?

And since creating tables with long names negatively affects
performance, this leads me to wonder if query performance on such a
database would also be negatively affected.

Thoughts?

P.S.: I'm using the latest amalgamated version of sqlite (3.8). There
is also a Stack Overflow question covering this topic here:
http://stackoverflow.com/questions/18603123/table-name-length-in-sqlite-affects-performance-why

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


[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? The answer is 1. Could it be described as one sql statement?



CREATE TABLE service_table (
service_nameTEXT, /* name of the service (unique) */
service_no  INTEGER,  /* internal service number, assigned by the 
system. */
service_typeTEXT, /* Type of Service. Should be one of
 the following:
 e-lan, e-line, e-tree, ip-forward
 The default should be e-line */
learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default */
col_status TEXT,
cfg_status TEXT);
INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('uni1-evc1',3,'e-line',1,'','committed');
INSERT INTO "service_table" 
VALUES('zero-touch-test',5,'e-lan',1,'','committed');


CREATE TABLE service_port_table(
sp_no   INTEGER,  /* Range 1 to 256 */
service_no  INTEGER,  /* The Service this service port belongs to */
sp_idx  INTEGER,  /* This is used to identify the 
classification rule:
 This should range from 1 to 1 */
if_idx  INTEGER,  /* Interface Index of the underlying interface */

INSERT INTO "service_port_table" VALUES(1,1,1,26);
INSERT INTO "service_port_table" VALUES(2,1,2,43);
INSERT INTO "service_port_table" VALUES(1,2,3,53);
INSERT INTO "service_port_table" VALUES(2,2,4,26);
INSERT INTO "service_port_table" VALUES(1,3,5,29);
INSERT INTO "service_port_table" VALUES(2,3,6,54,);
INSERT INTO "service_port_table" VALUES(1,5,20,12);
INSERT INTO "service_port_table" VALUES(2,5,21,58);
INSERT INTO "service_port_table" VALUES(3,5,22,27);



This email and attachments may contain privileged or confidential information 
intended only for the addressee(s) indicated. The sender does not waive any of 
its rights, privileges or protections respecting this information. If you are 
not the named addressee, an employee, or agent responsible for sending this 
message to the named addressee (or this message was received by mistake), you 
are not authorized to read, print, retain, copy or disseminate this message or 
any part of it. If received in error, please notify us immediately by e-mail, 
discard any paper copies and delete all electronic files of the email.

Computer viruses can be transmitted via email. The recipient should check this 
email and any attachments for viruses. Email transmission cannot be guaranteed 
to be secured or error-free as information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses. The sender 
accepts no liability for any damage caused by any transmitted viruses or errors 
or omissions in the contents of this message.

Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 
www.overturenetworks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[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 program.  I have a
workaround

so I AM NOT IN A HURRY FOR A RESPONSE.

 

 

The attached database (view.db) has 3 tables

1,  persons (personID)

2.  parentsChildren (parentID, childID)

3.  spouses (spouse1ID, spouseID)

 

The leftjoin/count technique seems to work for each join/count, separately.

However, if done together, the counts appear to have been multiplied;

Example:  person #4 should have 2 parents, 10 children and 2 spouses.

Instead it gets 20,20 for 2 joins and 40,40,40 for 3 joins.

 

I hope the following queries will tell the story.

I can be contacted at:

harry beezhold

ha...@beezhold.com

 

708-459-8600

 

 

 

 

 

 

**   JOIN parents 

**   looks OK 

**   #4 = 2   

 

SELECT persons.personID,

 count(parents.parentID) as parentCount

FROM  persons 

LEFT JOIN parentsChildren AS parents  ON personID = parents.childID

GROUP BY personID

 

10

22

30

42 These numbers look good.

50 The query asks:

61 How many in the parentsChildren table has my personID in their
childID.

71 They would be my parents

82

92

10   0

11   0

12   2

13   2

14   0

15   0

I stopped it

 

 

**   JOIN children

**   looks OK 

**   #4 = 10  

 

SELECT persons.personID,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

11

21

31

410   Yes, he did have 10 children

52

60

70

82

92

10   1

11   1

12   0

13   0

14   1

15   1

 

*   JOIN spouses

**   looks OK 

**   #4 = 2  

 

SELECT persons.personID,

 count(spouses.spouse1ID) as spouseCount

FROM  persons 

LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))

GROUP BY personID

 

11

21

31

42he had 2 wives

50

60

70

81

91

10   1

11   1

12   1

13   1

14   1

15   1

 

**   JOIN 2 from same table   

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(parents.parentID) as parentCount,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN parentsChildren AS parents  ON personID = parents.childID

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

 

101

222   < bug

301

420   20  < bug   

502

610

710

844   < bug

944   < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   JOIN 2 diff tables/seq   

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(spouses.spouse1ID) as spouceCount,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

101

222   < bug

301

420   20  < bug

502

610

710

844  < bug

944  < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   3   JOIN 

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(parents.parentID) as parentCount,

 count(children.childID) as childCount,

 count(spouses.spouse1ID) as spouseCount

FROM  (((persons 

LEFT JOIN parentsChildren AS parents  ON personID 

[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 arises:


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 how both 
version of SQLite would co-exist? If I don't compile TDBC and SQLite packages, 
a script written in TCL using sqlite3 command does not work, don't know why?

2.   Similarly Python 2.6.8 and above and Perl DBD and DBI distribution 
from CPAN comes with their own version of SQLite integrated with the respective 
languages. So should I go ahead and install them? Will that interfere with the 
actual SQLite distribution? Can all the different (now 4) SQLite version refer 
to the same database for querying, inserting records etc.?

Please suggest me ways to have a SQLite database and support from all the 3 
scripting language to query the database.

Thanks,
Ashutosh

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


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 read I suppose that sqlite treats
> a database as a file. 

Yes, a SQLite database is a file. SQLite will also creaste
journal files (in the same directory as the database) and
possibly temporary files (elsewhere in the filesystem).

> Adding data to the database and retrieving data from the
> database is equivalent to write() and read() file
> operations. But in board where can I create that database
> file like creating one in computer? Is it possible? 

Creating a database is as simple as opening it, and
creating one or more tables using SQL statements.
In principle you only have to implement a VFS to port
SQLite to a new platform, the VFS is so to speak the
operating system abstraction layer (See my previous message).
The VFSses for Windows and POSIX (Unix and the like) are 
included in the source tree, some other people may have 
implemented VFSses for other operating systems.

As far as SQLite is concerned, a C program using SQLite will
be the same on any operating system, and the database file
itself is portable between all platforms.

> Also for communicating with the sqlite library do I need
> to use command line interface always? 

No, the command line interface is a reference
implementations and development tool. It can also be used
productively from shell scripts.

> Because in board its
> not possible. Can I call those library functions directly
> from the application?

SQLite is an embedded SQL database library. Typically it
is used via the C API, or by using a wrapper for other
languages. Canonical program structure:

Program init
sqlite3_open_v2()
sqlite3_prepare_v2()

Statement execution
loop
sqlite3_bind_*()
sqlite3_step()
sqlite3_column_*()
end loop
sqlite3_reset()

Program exit
sqlite3_finalize()
sqlite3_close()

Please note that some of these entry points have a _v2()
version, which is the preferred version.
Please read the docs of each of those API entry points
carefully, and don't forget to check the status after each
and every call.


> It will be really helpful if you can provide me with some
> inputs on these

You'll have to read more of the documentation, and experiment.

Here are a few more pointers:

http://sqlite.org/docs.html

http://sqlite.org/c3ref/intro.html
http://sqlite.org/cintro.html

http://sqlite.org/arch.html

Coding examples:
http://icculus.org/~chunky/stuff/sqlite3_example/

Last but not least: The hint of Donald Griggs of about
a day ago is very valuable!


-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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