[sqlite] LIMIT expression in subquery cannot reference outer query tables

[Samuel Neff]

When using LIMIT in a subquery it seems the expression cannot access the
outer query's tables.  Other complex expressions are supported in LIMIT, so
I was curious if this is a defined limitation or something else?

Example:

sqlite> create table test (f int);
sqlite> insert into test values (1);
sqlite> insert into test values (2);
sqlite> insert into test values (3);
sqlite> insert into test values (4);
sqlite> insert into test values (5);
sqlite> select avg(f) from test;
3.0
sqlite> select * from test limit (select avg(f) from test);
1
2
3
sqlite> select *, (select count(*) from test t1 limit t2.f) from test t2;
Error: no such column: t2.f
sqlite> select *, (select count(*) from test t1 where t1.f <= t2.f) from
test t2;
1|1
2|2
3|3
4|4
5|5
sqlite>

So the average is intentionally a value that losslessly converts to an
integer.  I can use a subquery inside the LIMIT clause without issue.
However, when I use limit inside a subquery that correlates to the outer
query, I get no such column.  A similar subquery with WHERE instead of
LIMIT works fine.

Thanks,

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

Re: [sqlite] "natrual" join (not "natural")

[Samuel Neff]

oh, that makes sense.  Thanks for clarifying.

Sam


-
We're Hiring! Seeking passionate Flex or C# developer in the Washington
D.C. Contact care...@blinemedical.com


On Wed, Oct 31, 2012 at 10:18 AM, Richard Hipp  wrote:

> On Wed, Oct 31, 2012 at 9:54 AM, Samuel Neff 
> wrote:
>
> > We wrote a query and intended to use a "natural" join but had a typo and
> > wrote "natrual" join instead.  We were surprised this query was processed
> > without error and performed a cross join.
> >
> > Example:
> > sqlite> select * from a natrual join b;
> >
>
> The "natrual" became an alias for table "a".  As if you had written:
>
> SELECT * FROM a AS natrual JOIN b;
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "natrual" join (not "natural")

[Samuel Neff]

We wrote a query and intended to use a "natural" join but had a typo and
wrote "natrual" join instead.  We were surprised this query was processed
without error and performed a cross join.

Example:

SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(id int, t text);
sqlite> insert into a values (1,'a1');
sqlite> insert into a values (2,'a2');
sqlite> create table b(id int, btext text);
sqlite> insert into b values (1,'b1');
sqlite> insert into b values (3,'b3');
sqlite> select * from a natural join b;
id  t   btext
--  --  --
1   a1  b1
sqlite> select * from a natrual join b;
id  t   id  btext
--  --  --  --
1   a1  1   b1
1   a1  3   b3
2   a2  1   b1
2   a2  3   b3
sqlite>

Thanks,

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

[sqlite] System.Data.SQLite and Shared Cache

[Samuel Neff]

Can the System.Data.SQLite provider add support for Shared Cache?  There's
some old code in interop.c that's commented out that enables it, but no
indication of why it's commented out.

There's some discussion of it from five years ago here, with a patch to add
support: http://sqlite.phxsoftware.com/forums/t/485.aspx?PageIndex=1

Thanks,

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

Re: [sqlite] Understanding table-level locking in shared-cache mode

[Samuel Neff]

Oracle's distribution of BerkleyDB with SQLite has page-level locking
instead of database-level locking.  If you need the increased concurrency
that is an option.

http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html

Supposedly it's a drop-in replacement for sqlite.  I haven't used it yet
though so I can't comment on how valid that statement is.

HTH,

Sam



On Fri, Jul 15, 2011 at 2:24 PM, Igor Tandetnik  wrote:

> On 7/15/2011 2:15 PM, Steven E. Harris wrote:
>
> To the best of my knowledge, there is simply no way in SQLite to write
> to two tables at the same time. That's pretty much what makes it "lite".
> --
> Igor Tandetnik
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Integrate page-level locking into main SQLite like BerkleyDB's SQLite ?

[Samuel Neff]

The BerkleyDB backed version of SQLite from Oracle supports page level
locking.  Apparently this is where the real performance gains come from, not
swapping out the back-end.

http://www.oracle.com/technetwork/database/berkeleydb/downloads/index.html

Are there any plans to add page-level locking to the main SQLite
distribution?  BerkleyDB includes full source including the modified SQLite
source, but has license restrictions than would not allow integrating that
code into the public domain SQLite.

Thanks,

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

[sqlite] Discussing and contributing to new System.Data.SQLite project

[Samuel Neff]

Who is maintaining the new System.Data.SQLite project at
http://system.data.sqlite.org?  Is there a separate mailing list for that
project or should questions on that project be sent to the sqlite-users this
mailing list?

My company is interested in knowing what the plans are going forward and
most specifically how can we contribute.  We have a patch that exposes the
native backup API via the .NET wrapper which we'd like to contribute.  We
also are planning on writing a new connection pool implementation that is
not dependent on the garbage collector and is compatible with WAL mode.

Thanks,

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

Re: [sqlite] Effect of using WAL on technical support

[Samuel Neff]

Thanks for confirming this.  I did some more testing and turned off
connection pooling in SQLite.NET and then the connections got closed
correctly.

I'll plan on rewriting my own connection pool implementation and not using
the one provided by the wrapper.

Sam


On Sun, Apr 10, 2011 at 10:08 PM, Simon Slavin  wrote:

>
> On 11 Apr 2011, at 2:48am, Richard Hipp wrote:
>
> > On Sun, Apr 10, 2011 at 9:36 PM, Samuel Neff 
> wrote:
> >
> >> I'm sorry, my statement was misleading.  I'm referring to immediately
> after
> >> our application is closed.
> >>
> >> We're seeing that even if the application is gracefully shut down, the
> -wal
> >> and -shm files are still there.  In order to clear them I need to open
> the
> >> database files with sqlite3.exe and issue a "pragma wal_checkpoint".
> >
> > The -wal and -shm are deleted when the last connection to the database
> > closes.  If you are having -wal and -shm files left over, that implies
> that
> > you are not closing all your database connections before you exit.
>
> Yes !  If your application has exited and you still have a journal file,
> something is wrong.  Are you closing your connection correctly, however
> ASP.NET wants you to do it ?
>
> Simon.
> ___
> 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] Effect of using WAL on technical support

[Samuel Neff]

I'm sorry, my statement was misleading.  I'm referring to immediately after
our application is closed.

We're seeing that even if the application is gracefully shut down, the -wal
and -shm files are still there.  In order to clear them I need to open the
database files with sqlite3.exe and issue a "pragma wal_checkpoint".

I'm testing on Windows 7 with ASP.NET applications.

Thanks,

Sam



On Sun, Apr 10, 2011 at 9:10 PM, Simon Slavin  wrote:

>
> On 11 Apr 2011, at 2:04am, Samuel Neff wrote:
>
> > I'm interested in hearing anyone's experiences of using WAL journal mode
> on
> > technical support.  We often have to copy databases to attach to customer
> > reports and if the someone were to copy the database file while there is
> an
> > active -wal file then we would very likely be missing the most up-to-date
> > data in the copy.  I'm not sure if we can rely on support to issue a
> pragma
> > wal_checkpoint prior to doing the copy.
>
> My understanding is that no matter what journaling mode you're using, a
> straight copy of the data file is not 'safe' while the database is open.  If
> you want to backup the data, keep the database closed, or use the special
> SQLite backup API.
>
> Simon.
> ___
> 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] Effect of using WAL on technical support

[Samuel Neff]

I'm interested in hearing anyone's experiences of using WAL journal mode on
technical support.  We often have to copy databases to attach to customer
reports and if the someone were to copy the database file while there is an
active -wal file then we would very likely be missing the most up-to-date
data in the copy.  I'm not sure if we can rely on support to issue a pragma
wal_checkpoint prior to doing the copy.

Thanks,

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

Re: [sqlite] Force the use of a specified index?

[Samuel Neff]

If neither index individually offers a performance boost, it's possible a
single multi-column index might be better.
Sam


On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert wrote:

> Have tried INDEXED BY and it does indeed work and force the use of the
> specified index.
> It didn't however make the query faster, so maybe the SQLite plan
> generator is better than I thought!
>
> RBS
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any plans to make REPLACE conflict resolution invoke DELETE triggers? Docs say maybe...

[Samuel Neff]

The documentation for ON CONFLICT REPLACE has this clause:

"When this conflict resolution strategy deletes rows in order to satisfy a
constraint, it does not invoke delete triggers on those rows. This behavior
might change in a future release."

Are there any plans to actually change this behavior?  I'm looking at using
REPLACE instead of some existing UPDATE statements to simplify the SQL and
processing, but if in the future it starts invoking DELETE triggers it would
break my app.

Thanks,

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

[Samuel Neff]

Thanks, I didn't think REPLACE would work here but you're right, it does do
exactly what I need.

Best regards,

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]


On Fri, Aug 22, 2008 at 3:13 AM, Francis GAYREL <[EMAIL PROTECTED]>wrote:

> I met the same issue. REPLACE is the right way to override it.
> Assuming C1 is the primary key or at least unique,
> instead of
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

[Samuel Neff]

I'm trying to update records in one table based on joined data in another
table.  MSSQL has support for a "FROM" clause within an UPDATE statement
which makes this type of thing very easy.  Is there any equivalent in
SQLite?  The only way I've found to achive the same results is to use a
subselect within the SET clause of the UPDATE statement, but that requires
duplicating the WHERE clause within the subselect which is a lot of extra
typing and I'm sure a lot of extra work for SQLite.

MSSQL:

UPDATE T1
SET
A = T2...,
B = T2...,
C = T2...,
FROM T1 INNER JOIN T2 ON 

SQLite:

UPDATE T1
SET
A = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
B = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
C = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
...

Here are samples of equivalent code in MSSQL and SQLite.  Is there a way to
simplify the UPDATE statement in the SQLite code?

I'm not replacing the target row entirely, I don't think INSERT OR REPLACE
will work in this scenario.

Thanks,

Sam

--
-- MSSQL
--

CREATE TABLE #T1(
ID INTEGER PRIMARY KEY IDENTITY,
A VARCHAR(100),
B VARCHAR(100),
C VARCHAR(100));

CREATE TABLE #T2(
ID INTEGER PRIMARY KEY IDENTITY,
A VARCHAR(100),
B VARCHAR(100),
C VARCHAR(100));

INSERT INTO #T1 VALUES ('a1', 'b1', 'c1');
INSERT INTO #T1 VALUES (NULL, 'b2', 'c2');
INSERT INTO #T1 VALUES ('a3', NULL, 'c3');
INSERT INTO #T1 VALUES ('a4', 'b4', NULL);

INSERT INTO #T2 VALUES ('A1', 'B1', 'C1');
INSERT INTO #T2 VALUES ('A2', NULL, 'C2');
INSERT INTO #T2 VALUES ('A3', 'B3', NULL);
INSERT INTO #T2 VALUES (NULL, 'B4', 'C4');

SELECT * FROM #T1;
SELECT * FROM #T2;

UPDATE#T1
SETA = COALESCE(#T1.A, #T2.A),
B = COALESCE(#T1.B, #T2.B),
C = COALESCE(#T1.C, #T2.C)
FROM#T1 INNER JOIN #T2 ON #T1.ID = #T2.ID;

SELECT * FROM #T1;

DROP TABLE #T1;
DROP TABLE #T2;

--
-- SQLite
--

CREATE TEMP TABLE T1(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
A TEXT,
B TEXT,
C TEXT);

CREATE TEMP TABLE T2(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
A TEXT,
B TEXT,
C TEXT);

INSERT INTO T1 VALUES (NULL, 'a1', 'b1', 'c1');
INSERT INTO T1 VALUES (NULL, NULL, 'b2', 'c2');
INSERT INTO T1 VALUES (NULL, 'a3', NULL, 'c3');
INSERT INTO T1 VALUES (NULL, 'a4', 'b4', NULL);

INSERT INTO T2 VALUES (NULL, 'A1', 'B1', 'C1');
INSERT INTO T2 VALUES (NULL, 'A2', NULL, 'C2');
INSERT INTO T2 VALUES (NULL, 'A3', 'B3', NULL);
INSERT INTO T2 VALUES (NULL, NULL, 'B4', 'C4');

SELECT * FROM T1;
SELECT * FROM T2;


-- here's the ugly statement I'd like to simplfy

UPDATET1
SETA = (
SELECT COALESCE(InnerT1.A, T2.A)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
  AND T2.ID = T1.ID
),
   B = (
SELECT COALESCE(InnerT1.B, T2.B)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
  AND T2.ID = T1.ID
),
   C = (
SELECT COALESCE(InnerT1.C, T2.B)
FROM T1 InnerT1, T2
WHERE InnerT1.ID = T1.ID
  AND T2.ID = T1.ID
)
;

SELECT * FROM T1;

DROP TABLE T1;
DROP TABLE T2;


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inconsistent error: "temporary storage cannot be changed from within a transaction"

[Samuel Neff]

On Thu, Jul 17, 2008 at 12:53 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> The error only occurs if the temp_store value really is trying to
> change.  If the new temp_store value is the same as the old, no error
> is generated.  Are you sure you had not already issued the PRAGMA once
> before, prior to starting the transaction?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>

We searched our entire codebase and that is only set in one place and always
within a transaction (yes, bad, we'll fix it for future builds).  Even in
our unit testing we queried the value before our call and it was 0, we
confirmed we were in a transaction, then we issued the pragma without error,
and requeried the value and got back 2.  I don't know if that is considered
a change though--if 0 (default) results in being memory and then explicitly
changing it to memory.

We're on Windows XP and 2003 using sqlite 3.5.9 built from amalgamation and
sqlite.net 1.0.48.0.

Thanks,

Sam



-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inconsistent error: "temporary storage cannot be changed from within a transaction"

[Samuel Neff]

We're inconsistently getting this error:

PRAGMA temp_store = MEMORY
 ---> System.Data.SQLite.SQLiteException: SQLite error
temporary storage cannot be changed from within a transaction

We now realize that we are in fact issuing the PRAGMA inside a transaction,
but the weird thing is it works without error on a half dozen development
boxes, half dozen test boxes, and a few dozen or so client computers that
have this version.  Only one client computer fails with this error.  We
issue the hardware and image the machines so all computers are the same,
only the data on them is different.

We're not sure why this error doesn't occur on all boxes.  We stepped
through our unit test that covers this code and confirmed that in fact even
in development there is a transaction already in place prior to this
statement and that the value is changing (from 0 to 2).

So besides having a transaction in place, what exatly triggers this
message?  We'll move the code for future versions but are trying to identify
why it fails only for this one client machine and if we can get them working
without waiting for the next version of our software.

Thanks,

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] code formatting in list messages

[Samuel Neff]

I think it's a problem with [EMAIL PROTECTED]'s e-mail client.  All the
messages from him have this problem and it looks like only messages from him
have this problem.  I see other messages in the list with C code and no
escaping.

Looking at the raw message I received via the sqlite mailing list from him
the header says it's in plain text format but then it clearly has some HTML
coding in it.  Seems like a client problem to me.

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Thu, Jul 10, 2008 at 9:49 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> [EMAIL PROTECTED] wrote:
> >
> > actually after a quick and dirty hack in btree.c's sqlite3BtreeOpen()
> things _seem_ to work correctly. Of course this is not in any way how it
> should be done, but I didn't see a quick way to give in-memory databases
> a name so that can be used in the search for existing in-memory databases.
> But it indicates that the btree can be shared also for in-memroy databases.
> >
> > if( (flags & BTREE_PRIVATE)==0   //FIXME-XXX originally this is
> “&& isMemdb==0”      && isMemdb==1
>     && (db->flags & SQLITE_Vtab)==0     
> && zFilename && zFilename[0]     )  // FIXME-XXX
> originally this is strcmp(zFulPathname, …)if(
> 0==strcmp("", sqlite3PagerFilename(pBt->pPager))
>     && sqlite3PagerVfs(pBt->pPager)==pVfs ){
> >
>
> All,
>
> What is causing the goofy formatting of some of the code samples I am
> getting from the list over that little while?
>
> The above sample looks like this in my email client, Thunderbird.
>
> if( (flags & BTREE_PRIVATE)==0   //FIXME-XXX originally this is
> “&& isMemdb==0”      && isMemdb==1
>   && (db->flags & SQLITE_Vtab)==0
>    && zFilename && zFilename[0]     )
>   // FIXME-XXX originally this is strcmp(zFulPathname, …)if(
> 0==strcmp("", sqlite3PagerFilename(pBt->pPager))
>    && sqlite3PagerVfs(pBt->pPager)==pVfs ){
>
> It is totally unreadable, at least I'm not going to take the time to try
> to figure out what it says.
>
> It looks like this is some sort of attempt to escape the text for HTML
> mail or something. Is that perhaps the case?
>
> Dennis Cote
> ___
> 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] Clear Screen Command

[Samuel Neff]

I've wished there was a ".clear" command often.

And I'm sure it's much easier to implement than output coloring.  :-)

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Tue, Jul 8, 2008 at 3:35 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Keith Goodman wrote:
>
>> My guess is that that is too OS specific to add to sqlite. If you are
>> using linux you could try control-l to clear the screen.
>>
>
> I don't think it's too OS-specific - I think it's really outside the scope
> of the sqlite3 shell. The shell is really not intended to be a presentation
> layer, it's supposed to be a demo application, debugging helper, and
> quick-and-dirty manipulation tool. If a clear screen command were to be
> implemented, then what next? Cursor positioning? Perhaps output coloring?
> And so on :)
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple problem ?

[Samuel Neff]

you can do it with a subquery, like

select o.date, (select sum(credit - debit) from bank i where i.rowid <
o.rowid) from bank o order by rowid;

but it would be _much_ more efficient to handle it in your host application
as you loop through the data.

Notice that I used rowid instead of date 'cause rowid will be more precise,
whereas dates could be duplicated and may not give desired results.  This
assumes records are always inserted in chronological order.

HTH,

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Thu, Jul 3, 2008 at 12:03 PM, c.panel <[EMAIL PROTECTED]> wrote:

>
> Hello,
> I'm learning SQL and have some difficulties to resolve some simple problems
> with SQL.
>
> one example:
> Suppose I have a table with column DATE, CREDIT, DEBIT
> I want to create a new column that is the balance of account (ACCOUNT).
> My first approach is to index the table on dates, then starting with 0,
> then
> ACCOUNT = preceding ACCOUNT + CREDIT - DEBIT.
> But how can I do this in SQL with no cursor ?
>
> I'm afraid of complexity of SQL for simple problems.
> What do you think of this ?
>
> Does SQL is reserved for one sort of database management ?
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Adapter Update Question

[Samuel Neff]

Robert,

I looked at CoreLab's provider recently and didn't get the impression that
they used your code at all.  It's separate DLL's, very different classes, is
missing some functionality that you provide (like custom functions written
in .NET) but has other functionality (like robust connection pooling).  Also
the interop is very different and it uses three dll's, one of which they
install to system32 (sucks).

We were interested in their product precisely because it is commercial and
thus includes support.  Lack of custom functions is a deal breaker
though--we use them extensively.


Richard,

CoreLab's product is commercial.  Did you try asking their support?

In any case, if you're considering a switch to Mono in the future than I'd
highly recommend Robert's provider which we use and is very well written and
is included with Mono (with some alterations).

http://sqlite.phxsoftware.com

Thanks,

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Wed, Jun 25, 2008 at 8:17 PM, Robert Simpson <[EMAIL PROTECTED]>
wrote:

> I'm not exactly sure what they've built on top of my (free) provider, but
> it
> looks like a good portion of their core codebase is my code.  It's a bummer
> they decided to split with the free spirit of SQLite and charge for their
> provider (based on my public domain code), but to each his own.
>
> Robert
> http://sqlite.phxsoftware.com
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The APress SQLite Book

[Samuel Neff]

I loved the book.  It had a very good introduction to SQL and even as an
experienced SQL developer I learned a few things from that, the coverage of
SQLite was a wonderful introduction, and it covered some advanced topics
well.  SQLite changes so much that a lot of things don't apply any more, so
I wouldn't even think of using it as a reference.  sqlite.org is much better
for that, and System.Data.SQLite wrapper comes with the SQLite help in a
windows help file for very quick reference.

I still have all of our developers read "The definitive guide to SQLIte"
when starting on one of our SQLite related products and several of our
support people have read it as well.

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Wed, Jun 18, 2008 at 11:01 AM, Stefan Arentz <[EMAIL PROTECTED]>
wrote:

> Sorry if this is a little offtopic but I am curious what other people
> thing.
>
> Is it just me, or is the APress book 'The definitive guide to SQLIte'
> not very high quality?
> ...
>  S.
> ___
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] quick transaction question

[Samuel Neff]

If I launch two sqlite3.exe processes to the same database and do "begin
exclusive" in one and "begin" in the second I do not get a busy/locked error
in the second (not until you run some other sql like select or insert).
What situation can cause "begin" to get a busy/locked error?  (plain begin,
of course begin immediate or begin exclusive can get locked errors).

Thanks,

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]

On Tue, Jun 10, 2008 at 6:04 PM, Ken <[EMAIL PROTECTED]> wrote:

> Yes the BEGIN Transaction may get a BUSY/LOCKED error.
>
> If the BEGIN fails then do not commit, If you do commit you'll get an error
> indicating that the commit failed due to no active transaction
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] simultaneous transactions

[Samuel Neff]

using BEGIN IMMEDIATE would prevent this situation from happening, right?
Process 2 would get the lock error when it tries to begin the transaction
and thus never obtain a reserved lock which prevented process 1 from
promoting to an exclusive lock for commit.

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]

On Tue, Jun 10, 2008 at 7:19 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Dave Dyer <[EMAIL PROTECTED]> wrote:
> > I don't understand why the following transaction
> > behavior is correct or necessary.  The question
> > involves two simultaneous transactions on the same
> > database
> >
> > Process 1 Process 2
> >
> > BEGIN
> >
> > BEGIN
> >
> > insert...
> >
> > insert... fails "locked"
> >
> > end also fails "locked"
> >
> > It seems that the end of a transaction can
> > fail even if all the intermediate actions
> > succeeded.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "database disk image is malformed" error but on restart of application everything is fine

[Samuel Neff]

We see an error "database disk image is malformed" in our logs sometimes and
if we restart our application then it can continue fine--the db is not
corrupt.  We can open the db in sqlite3.exe CLI and running "PRAGMA
integrity_check;" returns OK.  What can cause this error if the db is
(thankfully) not really corrupt?

We're using 3.5.9 from amalgamation on Windows XP and 2003 with
System.Data.SQLite wrapper 1.0.48.0.

Thanks,

Sam



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in Washington D.C. Contact [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted sqlite_sequence table

[Samuel Neff]

great, thanks!

On Mon, Jun 2, 2008 at 11:55 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Samuel Neff wrote:
> > Were you able to successfully reproduce the corruption using the scripts
> and
> > databases I sent?  We're having a lot more trouble with this problem and
> our
> > earlier workaround is proving troublesome in some situations.
> >
>
> I think it has been fixed. See
> http://www.sqlite.org/cvstrac/tktview?tn=3148 for details.
>
> HTH
> Dennis Cote




-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted sqlite_sequence table

[Samuel Neff]

Were you able to successfully reproduce the corruption using the scripts and
databases I sent?  We're having a lot more trouble with this problem and our
earlier workaround is proving troublesome in some situations.

Thanks,

Sam



On Wed, May 28, 2008 at 7:25 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On May 28, 2008, at 7:21 PM, Samuel Neff wrote:
>
> > It happens every time.  I can send you a db and the update scripts,
> > but I'll
> > need you to keep it confidential (not signed affidavit or anything
> > like
> > that, just understanding that it's confidential).
> >
> > Please confirm this is ok and also which address I should send it to
> > (if
> > other than the one you're using for this list).
> >
>
> No one will see the database besides me.  I will delete it once the
> bug is fixed.
> Send to the address below.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted sqlite_sequence table

[Samuel Neff]

It happens every time.  I can send you a db and the update scripts, but I'll
need you to keep it confidential (not signed affidavit or anything like
that, just understanding that it's confidential).

Please confirm this is ok and also which address I should send it to (if
other than the one you're using for this list).

Thanks,

Sam


On Thu, May 22, 2008 at 9:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On May 22, 2008, at 7:02 PM, Samuel Neff wrote:
>
> > I have a corrupt sqlite_sequence table.  It has table names in the
> > "seq"
> > field.
> >
>

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Corrupted sqlite_sequence table

[Samuel Neff]

I have a corrupt sqlite_sequence table.  It has table names in the "seq"
field.

here is a trimmed version of data in sqlite_sequence:


-- Loading resources from C:\Documents and Settings\sam/.sqliterc
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> .width 50 50
sqlite> select * from sqlite_sequence;
nameseq
--
--
Transactions2
ActiveTransaction   2
AnnotationIcons_History 21
TransactionSequences494
CaptureDeviceTransactions   8
Annotations
CaptureDeviceTransactions
Annotations_History 24
ChecklistVersions   Annotations_History
ChecklistVersions_History   4
sqlite>



basically it gets corrupt when I run a bunch of scripts that update from one
schema to a new one (usually add new columns).  The queries do a lot of

ALTER TABLE x RENAME TO y;
CREATE TABLE x (...);
INSERT INTO x SELECT ... FROM y;
DROP TABLE y;

and after that the sqlite_sequence table is messed up as shown above.  It
doesn't happen every time--in the above listed data both Annotations and
Annotations_History were updated this way, but only Annotations got messed
up.

We cache all the data in sqlite_sequence before we do our updates and our
intention is to update it afterwards (we haven't written this part yet due
to sqlite_sequence corruption, so at this point we're only reading from the
table, not updating/inserting into it).

Our app is using sqlite 3.5.9.  I'm still using sqlite3.exe 3.5.7 but I see
the same corrupted data in both.

Please help.

Thanks,

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

Re: [sqlite] Locking causing exponentially degraded peformance with multiple processes

[Samuel Neff]

Yes, for write transactions, begin immediate will cause the second write
transaction to wait on the first.  However, the reads are done without an
explicity transaction so the reads should still proceed while the writes
have a reserved lock in place, right?

And even if there is no concurrency, at worst the 4 process test should take
just slightly more than 4 times longer than one process, or 2 seconds.  Not
exponentially longer, 10, 20, 45 seconds, which is what I'm seeing.  right?

Thanks,

Sam


On Fri, May 16, 2008 at 9:59 AM, John Stanton <[EMAIL PROTECTED]> wrote:

> By using BEGIN IMMEDIATE you lose any chance of concurrency.
>
> Samuel Neff wrote:
> > We're running into a lot of very slow queries and db locks when running
> with
> > multiple processes accessing the same database.  As a test we created a
> > small application that has only two threads and a small single table
> > database.
> >
> > RunRead reads all data from the one table (100 records).
> >
> > RunWrite updates all the records in the table (no add/delete, just
> update).
> >
> > When run by itself with just one process, each read/write concurrent
> > operation runs in 500ms.  It's synchronized to start both read/write at
> the
> > same time and then wait for each to finish before starting the next test,
> > and then loops.  It's pretty consistent around 500ms.
> >
> > When I run the same app multiple times (multiple concurrent processes),
> the
> > operations degrade very quickly.  It starts off taking 1-5 seconds for
> each
> > read/write concurrent operation but after a few minutes it often takes
> 20-30
> > seconds for a a single operation and sometimes 45 seconds.
> >
> > The transactions are all BEGIN IMMEDIATE and the noticable time taken is
> > during COMMIT.  The reads are not run within a transaction.
> >
> > I'm using sqlite 3.5.9 in SQLite.NET.  Full C# test code follows.
> >
> > Is there something I'm doing wrong that is causing this lock contention?
>  Is
> > there anything I can do to improve performance in a multi-process
> > application?
> >
> > Thanks,
> >
> > Sam
> >
> >
> >
> >
> > using System;
> > using System.Data;
> > using System.Data.SQLite;
> > using System.Diagnostics;
> > using System.IO;
> > using System.Threading;
> >
> > namespace test
> > {
> >   public class DbLockTest
> >   {
> > private static readonly Random _random = new Random();
> >
> > private readonly ManualResetEvent _start = new
> ManualResetEvent(false);
> > private readonly ManualResetEvent _readDone = new
> > ManualResetEvent(false);
> > private readonly ManualResetEvent _writeDone = new
> > ManualResetEvent(false);
> > private Stopwatch _timer;
> >
> > public static void Run()
> > {
> >   if (!File.Exists("DbLockTest.dat"))
> >   {
> > using (SQLiteConnection cnn = CreateConnection())
> > {
> >   using (SQLiteTransaction trans = cnn.BeginTransaction())
> >   {
> > using (SQLiteCommand cmd = cnn.CreateCommand())
> > {
> >   cmd.CommandText = "CREATE TABLE Data (id INTEGER PRIMARY
> KEY
> > AUTOINCREMENT, text TEXT);";
> >   cmd.ExecuteNonQuery();
> > }
> >
> > for (int i = 0; i < 100; i++)
> > {
> >   using (SQLiteCommand cmd = cnn.CreateCommand())
> >   {
> > cmd.CommandText = "INSERT INTO Data (text) VALUES
> (@text);";
> > cmd.Parameters.AddWithValue("@text", new string((char)(65
> +
> > i), i * 100));
> > cmd.ExecuteNonQuery();
> >   }
> > }
> > trans.Commit();
> >   }
> > }
> >   }
> >
> >   for (int i = 0; i < 50; i++)
> >   {
> > new DbLockTest().RunImpl();
> > Thread.Sleep(1);
> > Console.WriteLine();
> > Console.WriteLine();
> > Console.WriteLine();
> >   }
> >
> >   Console.WriteLine("Done.  Hit any key.");
> >   Console.ReadKey();
> > }
> >
> > public void RunImpl()
> > {
> >   _timer = Stopwatch.StartNew();
> >
> >   Console.WriteLine("{0:0,000} - MAIN  - Queuing threads",
> > _timer.ElapsedMilliseconds);
> &g

[sqlite] Locking causing exponentially degraded peformance with multiple processes

[Samuel Neff]

We're running into a lot of very slow queries and db locks when running with
multiple processes accessing the same database.  As a test we created a
small application that has only two threads and a small single table
database.

RunRead reads all data from the one table (100 records).

RunWrite updates all the records in the table (no add/delete, just update).

When run by itself with just one process, each read/write concurrent
operation runs in 500ms.  It's synchronized to start both read/write at the
same time and then wait for each to finish before starting the next test,
and then loops.  It's pretty consistent around 500ms.

When I run the same app multiple times (multiple concurrent processes), the
operations degrade very quickly.  It starts off taking 1-5 seconds for each
read/write concurrent operation but after a few minutes it often takes 20-30
seconds for a a single operation and sometimes 45 seconds.

The transactions are all BEGIN IMMEDIATE and the noticable time taken is
during COMMIT.  The reads are not run within a transaction.

I'm using sqlite 3.5.9 in SQLite.NET.  Full C# test code follows.

Is there something I'm doing wrong that is causing this lock contention?  Is
there anything I can do to improve performance in a multi-process
application?

Thanks,

Sam




using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Threading;

namespace test
{
  public class DbLockTest
  {
private static readonly Random _random = new Random();

private readonly ManualResetEvent _start = new ManualResetEvent(false);
private readonly ManualResetEvent _readDone = new
ManualResetEvent(false);
private readonly ManualResetEvent _writeDone = new
ManualResetEvent(false);
private Stopwatch _timer;

public static void Run()
{
  if (!File.Exists("DbLockTest.dat"))
  {
using (SQLiteConnection cnn = CreateConnection())
{
  using (SQLiteTransaction trans = cnn.BeginTransaction())
  {
using (SQLiteCommand cmd = cnn.CreateCommand())
{
  cmd.CommandText = "CREATE TABLE Data (id INTEGER PRIMARY KEY
AUTOINCREMENT, text TEXT);";
  cmd.ExecuteNonQuery();
}

for (int i = 0; i < 100; i++)
{
  using (SQLiteCommand cmd = cnn.CreateCommand())
  {
cmd.CommandText = "INSERT INTO Data (text) VALUES (@text);";
cmd.Parameters.AddWithValue("@text", new string((char)(65 +
i), i * 100));
cmd.ExecuteNonQuery();
  }
}
trans.Commit();
  }
}
  }

  for (int i = 0; i < 50; i++)
  {
new DbLockTest().RunImpl();
Thread.Sleep(1);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine();
  }

  Console.WriteLine("Done.  Hit any key.");
  Console.ReadKey();
}

public void RunImpl()
{
  _timer = Stopwatch.StartNew();

  Console.WriteLine("{0:0,000} - MAIN  - Queuing threads",
_timer.ElapsedMilliseconds);

  ThreadPool.QueueUserWorkItem(RunRead, _random.Next(15));
  ThreadPool.QueueUserWorkItem(RunWrite, _random.Next(15));

  Thread.Sleep(100);

  Console.WriteLine("{0:0,000} - MAIN  - Signaling threads",
_timer.ElapsedMilliseconds);

  _start.Set();

  _readDone.WaitOne();
  Console.WriteLine("{0:0,000} - MAIN  - Read done received",
_timer.ElapsedMilliseconds);
  _writeDone.WaitOne();
  Console.WriteLine("{0:0,000} - MAIN  - Write done received",
_timer.ElapsedMilliseconds);
}

private void RunRead(object state)
{
  try
  {
Console.WriteLine("{0:0,000} - READ  - Waiting for signal",
_timer.ElapsedMilliseconds);

_start.WaitOne();
/*
int wait = (int) state;
Console.WriteLine("{0:0,000} - READ  - Sleeping {1} ms",
_timer.ElapsedMilliseconds, wait);
Thread.Sleep(wait);
*/
IDataReader reader;

Console.WriteLine("{0:0,000} - READ  - Opening connection",
_timer.ElapsedMilliseconds);

SQLiteConnection cnn = CreateConnection();
using(SQLiteCommand cmd = cnn.CreateCommand())
{
  cmd.CommandText = "SELECT * FROM Data";
  Console.WriteLine("{0:0,000} - READ  - Getting reader",
_timer.ElapsedMilliseconds);
  reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

Console.WriteLine("{0:0,000} - READ  - Looping through data",
_timer.ElapsedMilliseconds);
int i = 0;
while (reader.Read())
{
  if (i++ % 10 == 0)
  {
Thread.Sleep(1);
  }
}

Console.WriteLine("{0:0,000} - READ  - Closing reader (and thus
connection)", _timer.ElapsedMilliseconds);
reader.Close();

Console.WriteLine("{0:0,000} - READ  - Signaling done",
_timer.ElapsedMilliseconds);
  }
  ca

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Samuel Neff]

On Tue, May 13, 2008 at 7:51 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> for version 3.6.0 we are considering a behavior change in which a call
> to sqlite3_close() will silently and automatically call
> sqlite3_finalize() on all outstanding prepared statements.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
We would be very much in favor of this change.  While calling
sqlite3_close() without first finalizing all statements is a programmer
error, if this occurs returning SQLITE_BUSY doesn't help much, it seems to
compound the problem.  We actually run into this problem a lot recently
after some new features have exponentially increased the concurrent access
in our application.  the sqlite3_close() error occurs for us as a secondary
error.  Usually we get db locked first, and then fail to finalize the
statement (our bad), but then it's compounded when sqlite3_close() fails.
Unfortunately due to how connection pooling works in SQLite.NET this
sqlite3_close() failure ends up happening on a garbage collector thread
which in MS's infinite wisdom leads to a full application crash (at least it
did initially--we've fixed that since this started).

Interestingly, we're getting these errors in SQLite.NET which Robert said
earlier in this thread already handles finalizing dangling statements.
We're not using the official SQLite.NET distribution, we have some small
modifications, but the code he mentioned is in place in the version we're
using and is untouched.

Thanks,

Sam

-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailing list

[Samuel Neff]

On Tue, May 13, 2008 at 3:13 PM, <[EMAIL PROTECTED]> wrote:

>
> I think mailing lists worked fine 20 years ago but does it have any
> advantage today ? :)
> Rado
>
>
That's just an opinion.. personally I prefer mailing lists because I can see
all seven lists I subscribe to in one place and check them daily.  On the
other hand the 4 bulletin boards that I try to follow I often forget about
and don't get to more than once a week because I have to go to each one
individually and each has it's own mechanisms for tracking new messages and
such.

Luckily as Igor mentioned there are several options for viewing this and
many other mailing lists through an online or nntp interface so that can
give the subscriber the choice.  Choice is good.

Sam

-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot get amalgamation built from CVS to compile

[Samuel Neff]

Matthey,

Thanks for making this change.  We got latest from CVS today and
configure/make worked great.

One other issue we're having and are not sure about is we get a compiler
error on sqlite3_profile and sqlite3_trace.  We need to remove these two
lines from the def file included with the sqlite source in order to get
everything to compile ok on VS 2008.  are sqlite3_profile and sqlite3_trace
included by default in both the source and def or is there a mismatch?  Or
is there something else we should be doing besides editing the def file
manually?

Best regards,

Sam


On Tue, May 6, 2008 at 10:48 PM, Matthew L. Creech <[EMAIL PROTECTED]>
wrote:

> In the latest CVS, you should now also be able to do what you intended
> in the first place.  Namely:
>
> ./configure
> make sqlite3.c
>
> I thought about it, and there's no good reason to inline the
> auto-generated config.h file in to the amalgamation like we were
> doing, so now it keeps it as an #include that's only performed if
> building in-tree.  Let me know if you see any more problems.
>
> --
> Matthew L. Creech
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting records in random order

[Samuel Neff]

On Wed, May 7, 2008 at 5:25 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

>
> I couldn't see how these would be different so I fired up the explain
> command. As I expected, these two produce identical code (except for the
>  integer id assigned to the ephemeral table used for the sort). I don't
> think here will be any difference in speed between these two statements.
>
> sqlite>
>
> Dennis Cote
>  
>


My bad--when I ran my tests I ran the second query through far less
iterations than the first query (typo).  Sorry for the misinformation.

Sam


-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting records in random order

[Samuel Neff]

This query runs slow:

SELECT id FROM data ORDER BY random();

but this equivalent query runs very fast:

SELECT id FROM (SELECT id, random() r FROM data) ORDER BY r;

HTH,

Sam


On Wed, May 7, 2008 at 2:37 PM, Barbara Weinberg <[EMAIL PROTECTED]> wrote:

> Hi
> I was wondering whether anyone had tried sorting records in random order
> using sqlite3. I tried sorting by random() and randomblob() but it was
> very
> slow and chewed up lots of resources. Any suggestions?
> Barbara
> ___
>


-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot get amalgamation built from CVS to compile

[Samuel Neff]

Matthew,

Thanks!  After deleting everthing and re-checking out from cvs, using the
pre-build makefile worked great.

Best regards,

Sam


On Tue, May 6, 2008 at 4:46 PM, Matthew L. Creech <[EMAIL PROTECTED]>
wrote:

>
> If you want to create a generic amalgamation (without pre-defined
> features like HAVE_STDINT_H), at least currently, you shouldn't use
> the configure script to generate your makefile, since the point of
> using autoconf is to detect resources (header files, etc.) that aren't
> portable.  You can instead start with the pristine source, then:
>
> cp Makefile.linux-gcc Makefile
> make sqlite3.c
>
> bypassing the configure script altogether.  That should do what you want
>
> --
> Matthew L. Creech
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot get amalgamation built from CVS to compile

[Samuel Neff]

On Tue, May 6, 2008 at 2:42 PM, Matthew L. Creech <[EMAIL PROTECTED]>
wrote:

>
>
> By default things like HAVE_GMTIME_R aren't defined, so you'd have to
> add those to your CPPFLAGS or something if you wanted to build a
> generic amalgamation with those features included.  The datatypes that
> aren't defined will use less accurate types that are "good enough", so
> that e.g. UINT32_T might be "unsigned long" rather than "uint32_t",
> which might be 64 bits.  This is under discussion right now: whether
> we even need the specifically-sized types at all.  If not, the
> inclusion of  and definition of those types may disappear in
> the interest of portability.
>
> --
> Matthew L. Creech


we ran

configure
make sqlite3.c

and got an amalgamation with those types defined using unmodified sources
from CVS.  Are you saying the types should not be defined?

We modified the sqlite3.c file by hand as Brad suggested and it compiles and
runs fine, but I agree with Brad that it seems odd to need to edit the file
manually.

Thanks,

Sam



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Cannot get amalgamation built from CVS to compile

[Samuel Neff]

We're trying to build an amalgamation from CVS to use within our application
for the first time.  However, when we try to compile we get an error on this
line:


#ifdef HAVE_STDINT_H
#include 
#endif

fatal error C1083: Cannot open include file: 'stdint.h': No such file or
directory


We tracked back the difference between that distribution and the
amalgamation that we build and the major changes start here

>From sqlite3.c in 3.5.8 distribution:

#ifndef _CONFIG_H_
#define _CONFIG_H_

/* We do nothing here, since no assumptions are made by default */

#endif


>From sqlite3.c in our amalgamation built from CVS:


#ifndef _CONFIG_H_
#define _CONFIG_H_



/*
** Data types
*/

/* Define as 1 if you have the int8_t type */
#define HAVE_INT8_T 1

...

/* Define as 1 if you have the stdint.h header */
#define HAVE_STDINT_H 1

...

/* End of header */
#endif


Is this related to a change in the CVS source or is there something we're
doing wrong in building the amalgamation?

We're building the amalgmation on Fedora Core release 4 (Stentz),
2.6.17-1.2142_FC4smp #1 SMP i686 i686 i386 GNU/Linux

We're compiling sqlite in Microsoft Visual Studio 2008 as part of
System.Data.SQLite (.NET) which uses sqlite3.c and compiles fine with
sqlite3.c from the 3.5.8 distribution on the sqlite.org website.

Any help would be appreciated.

Thanks,

Sam


-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in DotNet environment and virtual tables

[Samuel Neff]

While System.Data.SQLite does not support virtual tables, it does support
custom funcions written in .NET and is open source, so that should get you a
long way towards writing virtual tables in .NET (but still will require some
C interop programming).

http://sqlite.phxsoftware.com/

HTH,

Sam


On Wed, Apr 16, 2008 at 4:53 AM, Sylko Zschiedrich <[EMAIL PROTECTED]>
wrote:

> Hi all,
>
> we are using SQLite in a DotNet environment and want to support virtual
> tables via sqlite3_load_extension.
>
> Did anybody know a wrapper for SQLite or sample code for implementing
> virtual tables in DetNet. (C#)?
>
> I know there are many DotNet wrappers for SQLite but noone supports
> virtual tables. :-(
>
>
> Thanks
> Sylko
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to get more information than "constraint failed" when a constraint fails?

[Samuel Neff]

Roger,

Thanks for the link.  Still no indication of status though.. is this
something that may get improved in a future update of sqlite?

I updated the ticket I reported to include a link to the older existing
ticket.

Best regards,

Sam



On Tue, Apr 8, 2008 at 6:16 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

>
>
> Samuel Neff wrote:
> > I first reported this when I started working with SQLite.
> >
> > http://www.sqlite.org/cvstrac/tktview?tn=2258
>
> There was an earlier ticket with more details about why this happens:
>
>  http://www.sqlite.org/cvstrac/tktview?tn=1648
>
> Roger


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(1)

[Samuel Neff]

Scott,

Is it really a full table scan or just an index scan (at least in the case
where no data is needed from the table as in the original sample that had no
join or where clause).

Thanks,

Sam


On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> A little bit more info:  SELECT COUNT(*) is implemented as a full
> table scan, so SQLite is visiting every row in the table, which will
> get slower and slower as the table gets bigger and the database
> fragments.  This differs from many database engines (which implement
> an optimization for this)  Doing the trigger thing means that it only
> visits the specific row that contains the count.
>
> -scott
>
>
-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any way to get more information than "constraint failed" when a constraint fails?

[Samuel Neff]

Is there any way to get more information from SQLite when a constraint
fails, particularly which field caused the constraint to fail?

Ideally the error message should list the field name that caused the
constraint to fail, the bad value, and even the constraint itself.


This is what I get...

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t(a int check(typeof(a) = 'int'));
sqlite> insert into t values ('1');
SQL error: constraint failed
sqlite>


This is what would be nice

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t(a int check(typeof(a) = 'int'));
sqlite> insert into t values ('1');
SQL error: constraint failed: text value '1' failed to pass constraint
(typeof(a) = 'int')
sqlite>


I first reported this when I started working with SQLite.

http://www.sqlite.org/cvstrac/tktview?tn=2258

Thanks,

Sam

-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert of string with line breaks

[Samuel Neff]

How are you verifying that the db only received one line from the
description?  using sqlite3.exe or some other tool, or AIR?

SQLite itself does not have issues with storing line breaks, but it's
possible the AIR wrapper is losing them somewhere or more likely it's a
display/formatting issue.

One thing to keep in mind is that the Flash Player has funky internal
representations of line breaks in strings.  I believe it uses '\r' whereas
windows uses '\r\n' and *nix uses '\n'.  I had to make some changes in
Fluorine to handle line breaks properly with AMF and .NET integration.  It
could be that the line break is in the db but the tool you're using to view
the data doesn't recognize it as a valid line break.

HTH,

Sam



On Wed, Mar 26, 2008 at 10:33 AM, Raymond Camden <[EMAIL PROTECTED]>
wrote:

> I've got an AIR app that does simple inserts into a SQLite db. The
> insert looks like so:
>
> var insStmt:SQLStatement = new SQLStatement();
> insStmt.sqlConnection = dbconnection;
> sql = "insert into hours(description,projectidfk,clientidfk,hours,date)
> values(:description,:projectidfk,:clientidfk,:hours,:date)";
> insStmt.parameters[":description"] = descriptionfield.text;
> insStmt.parameters[":projectidfk"] = projectfield.selectedItem.id;
> insStmt.parameters[":clientidfk"] = projectfield.selectedItem.clientid;
>
> insStmt.parameters[":hours"] = hoursworkedfield.value;
> insStmt.parameters[":date"] = dateworkedfield.selectedDate;
>
> trace('desc is '+descriptionfield.text);
> insStmt.text = sql;
> insStmt.addEventListener(SQLEvent.RESULT, refreshHandler);
>
>
> Notice I'm using bound parameters for all my values. The description
> field is a text box. When I enter text with line breaks in it, the db
> only gets line one of description.
>
> Any ideas why? A string is a string even if it has line breaks in it -
> afaik.
>
>
> --
>
> ===
> Raymond Camden, Owner of Camden Media, Inc.
>
> Email: [EMAIL PROTECTED]
> Blog : www.coldfusionjedi.com
> AOL IM   : cfjedimaster
>
> "My ally is the Force, and a powerful ally it is." - Yoda
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] SQLite and VB.Net?

[Samuel Neff]

ADO.NET is part of the .NET framework.  There are no additional depencencies
and with SQLite there is nothing to setup outside your own application.

The performance impact of using ADO.NET vs direct is miniscule and greatly
outweighed by the improved efficiency in development.

Sam


On Sun, Mar 2, 2008 at 10:36 PM, Gilles Ganault <[EMAIL PROTECTED]>
wrote:

> On Sun, 2 Mar 2008 21:07:03 -0500, "Samuel Neff"
> <[EMAIL PROTECTED]> wrote:
> > I would go the ADO.NET route 'cause it'll fit into your .NET application
> much
> >better.  The overhead is minimal compared to the normal cost of running
> >database queries (in any database).
>
> The reason I'm concerned about using ADO.Net instead of hitting the
> SQLite library directly, is that this adds dependencies in addition to
> the .Net framework. Our customers are anything but computer-savvy, and
> most don't have anyone technical around in case things don't work, so
> that I'd like to minimize dependencies as much as possible.
>
> Also, what about performance when using the SQLite library directly
> vs. going through ADO.Net?
>
> Hopefully, I'll have VS2005/2008 and SQLite up and running by the end
> of the week, so I can check for myself.
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] SQLite and VB.Net?

[Samuel Neff]

The System.Data.SQLite wrapper that others have mentioned is wonderful.  I
would go the ADO.NET route 'cause it'll fit into your .NET application much
better.  The overhead is minimal compared to the normal cost of running
database queries (in any database).

However, if you really want to skip ADO.NET,you can use the transparent
wrappers that are included in System.Data.SQLite directly.  I believe they
have the exact same API as the core C library but can be called directly
from .NET.  Robert put a lot of work writing C wrappers for the sqlite
library that can be called from .NET, something to do with changing the call
specs but I don't know the details.  No reason to re-invent the wheel.  :-)

Also, the System.Data.SQLite wrapper allows you to easily write custom
functions in .NET, scalar, aggregate, and collation.  Doing that without his
wrapper would be a lot more work.

HTH,

Sam


On Sun, Mar 2, 2008 at 5:36 PM, nonags <[EMAIL PROTECTED]> wrote:

> Gilles
>
> I am a .Net developer and I use an excellent implementation
> System.Data.SQLite http://sqlite.phxsoftware.com/
>
> Regards
>
>

-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How To concatenate two fields in one

[Samuel Neff]

SQLite uses '||' as the concatenation operator (which is correct, MSSQL is
really wrong to accept '+' and not use '||').

HTH,

Sam

On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]>
wrote:

>
> What I would like to achieve is the same as writing in SQL Server
>
> SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students
>
>


-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization Question for SQLite Experts

[Samuel Neff]

shouldn't leafID be the primary key of your LEAVES table and thus already
indexed?  What does your create table statement look like?  I'd expect

CREATE TABLE Leaves (LeafID INTEGER PRIMARY KEY AUTOINCREMENT, ... other
columns ... )

As far as the create index failing, no idea there, sorry..

Sam


On Fri, Feb 29, 2008 at 12:23 PM, Mark Gilbert <[EMAIL PROTECTED]> wrote:

> Hey Samuel
>
> Thanks for your ideas.
>
> In fact we stumbled across the solution, and I am amazed we didnt
> think of it earlier, and no-one suggested it.  Basically our LEAVES
> table doesn't have an Index !!
>
>


-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization Question for SQLite Experts

[Samuel Neff]

Here's two suggestions.  First the simple suggestion is instead of this..

for (z=0;z wrote:

> Folks.
>
> Looking for some advice from hardened SQliters...
>
> ...

For each twig we  have to find all the leaves.  The Leaves table has
> maybe 15000 records and we have a query where we search the Leaves
> table once for each twig we find.  In our part of the tree there
> might be 200 twigs, and so we end up searching the leaves DB 200
> times, to build up the list of leaves attached to the twigs
>
> ...in pseudocode:
> for (z=0;z {
>sqlite3_get_table("select * from LEAVES where leafID =
> theTwig[z].childLeafID")
> // we end up performing this query 200 times, once for each of the
> parent twigs.
> }
> ...
>
> Thanks for any experience you can share..
>
> Mark
>  
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating values from multiple varchar fields

[Samuel Neff]

While '||' is the standard, unfortunately MSSQL uses '+' for string
concatentation so people that are used to using '+' are most likely coming
from MSSQL and thus using '||' does not make for portable SQL (MSSQL does
not support '||', at least not in version 2005).

In my applications I have a custom Concat() function to do string
concatenation and then write an implementation of this in both SQLite and
MSSQL so the same SQL can be run on both databases.

Sam


On Wed, Feb 27, 2008 at 2:48 AM, John Stanton <[EMAIL PROTECTED]> wrote:

>
> This is plain vanilla, standard SQL according to the standard.  You will
> find that Sqlite sticks to the standard quite well so if you write ANSI
> SQL it is not only understood by Sqlite but be portable.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detecting database table changes

[Samuel Neff]

yeah, that sounds right.  Docs say you can only choose one action for the
trigger and I couldn't find a way to have a trigger run based on multiple
actions.  Would be nice though.. I have similar requirements in my
application and I create 3 triggers per table.

Best regards,

Sam


On Mon, Feb 25, 2008 at 12:05 PM, John Karp <[EMAIL PROTECTED]> wrote:

> Okay, thanks. That sounds workable.
>
> Am I right to understand from the documentation that I have to create
> three triggers (insert, update, and delete) for each table concerned?
>
> Thanks again,
> John
>
> On 22/02/2008, Samuel Neff <[EMAIL PROTECTED]> wrote:
> > Use triggers to populate some table such as Changes or History or
> >  LatestChange or something.  Then you can just query this one table for
> >  updates.
> >
> >  HTH,
> >
> >  Sam
> >
> >
> >
> >  On Fri, Feb 22, 2008 at 4:28 PM, John Karp <[EMAIL PROTECTED]> wrote:
> >
> >  > Hi all,
> >  >
> >  > I'm using SQLite to maintain a database that is used by several
> >  > independent processes. One process is a 'viewer', and it is important
> >  > that it always be presenting the latest data. Currently, I am using
> >  > Linux's inotify feature to detect any changes to the file. This works
> >  > fairly well, except it has no way of knowing which particular tables
> >  > have changed. Is there some internal table-level timestamp or
> >  > versioning that I could access?
> >  >
> >  > Or will I need to create an auxiliary table that contains table
> >  > timestamps? Is there another, more elegant possibility I'm not
> >  > thinking of?
> >  >
> >  > Thanks in advance,
> >  > John
> >
> > > ___
> >  > 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
 Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detecting database table changes

[Samuel Neff]

Use triggers to populate some table such as Changes or History or
LatestChange or something.  Then you can just query this one table for
updates.

HTH,

Sam


On Fri, Feb 22, 2008 at 4:28 PM, John Karp <[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I'm using SQLite to maintain a database that is used by several
> independent processes. One process is a 'viewer', and it is important
> that it always be presenting the latest data. Currently, I am using
> Linux's inotify feature to detect any changes to the file. This works
> fairly well, except it has no way of knowing which particular tables
> have changed. Is there some internal table-level timestamp or
> versioning that I could access?
>
> Or will I need to create an auxiliary table that contains table
> timestamps? Is there another, more elegant possibility I'm not
> thinking of?
>
> Thanks in advance,
> John
> ___
> 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] Hierarchical Deletion via a Trigger?

[Samuel Neff]

I don't agree with the XML anaology.  As I understand it, recursion in  SQL
is referring to self-referencing tables that create a parent/child
relationship.  This generally does not apply to XML since XML is
hierarchical but usually not recursive (i.e., the children are not the same
elements as the parents).  Certainly it's possible and does happen, but for
the most part the relationship between XML elements can be easily mapped to
relational tables and does not require recursive SQL to process.

Oracle has had recursive SQL support for a while and it's very helpful when
appropriately used.  MSSQL adds hierarchical data types in 2008 to support
similar functionality.

HTH,

Sam


On Fri, Feb 22, 2008 at 11:32 AM, Nicolas Williams <[EMAIL PROTECTED]>
wrote:

> On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote:
> > As Nicolas said, SQL:1999 defines a standard method of doing this. It
> > uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been
> > widely implemented, but I believe that IBM's DB2 supports this
> mechanism.
>
> Ah, good to know.  Would there be interest in supporting that in SQLite?
> I think it'd be very useful (particularly given the populatiry of XML).
> ___
> 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] Multiple table SELECT help

[Samuel Neff]

Try this..


SELECT p.name, sum(s.stat1), sum(s.stat2)
FROM stats s JOIN Players p ON p.ID = s.playerID
WHERE s.gameID IN (SELECT gameID FROM games ORDER BY gameID DESC LIMIT 5)
GROUP BY p.playerID

HTH,

Sam



On Thu, Feb 21, 2008 at 10:25 AM, RBKanso <[EMAIL PROTECTED]> wrote:

>
> Assume the following table structure:
>
> http://www.nabble.com/file/p15613178/sbtables.jpg
>
> I can get overall player stats for ALL games using this select statement:
>
> SELECT p.name, sum(s.stat1), sum(s.stat2)
>  FROM stats s JOIN Players p ON p.ID = s.playerID
>  GROUP BY p.ID
>
> And I can get a summary of stats for the last FIVE games with this call:
>
> SELECT g.date, g.opponent, g.result, sum(s.stat1), sum(s.stat2)
>  FROM stats s JOIN games g ON g.ID = s.gameID
>  GROUP BY g.ID
>  ORDER BY g.date DESC
>  LIMIT 0,5
>
> But how would I get the overall PLAYER stats for the last FIVE games?
>
> I have tried sub-queries and additional joins but this type of SELECT is
> simply above my knowledge of SQL. I appreciate any help. Thanks.
> --
> View this message in context:
> http://www.nabble.com/Multiple-table-SELECT-help-tp15613178p15613178.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Blob incremental i/o via Python

[Samuel Neff]

Thanks for the correction.

Sam


On Thu, Feb 14, 2008 at 6:19 PM, <[EMAIL PROTECTED]> wrote:

> "Samuel Neff" <[EMAIL PROTECTED]> wrote:
> > If the images you're storing are larger than the defined page size for
> the
> > database (which is most likely the case) then you can get better
> performance
> > and reduced memory consumption by storing the images in the file system
> and
> > store only paths to the files in the database.  This means reading the
> large
> > amount of data directly from the file system instead of from sqlite's
> > linked-list of pages and bypassing the page caching layer (which you
> > probably don't want for images anyways) and freeing up more of the page
> > cache for real database data.
> >
>
> One would think.  And yet experiments suggest otherwise.  It
> turns out to be faster to read images directly out of SQLite
> BLOBs until the image gets up to about 15KB on windows and
> up to about 60KB on linux.  And even for much larger images,
> the performance difference between reading from SQLite and
> reading from a file is not that great, so it is a reasonable
> thing to do to read from SQLite if transactions are important
> to you or if it is just more convenient.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
> ___
> 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] Blob incremental i/o via Python

[Samuel Neff]

If the images you're storing are larger than the defined page size for the
database (which is most likely the case) then you can get better performance
and reduced memory consumption by storing the images in the file system and
store only paths to the files in the database.  This means reading the large
amount of data directly from the file system instead of from sqlite's
linked-list of pages and bypassing the page caching layer (which you
probably don't want for images anyways) and freeing up more of the page
cache for real database data.

HTH,

Sam


On Mon, Feb 11, 2008 at 10:29 PM, Norman Young <[EMAIL PROTECTED]>
wrote:

> The documentation outlines the C interface for incremental blob
> input/output, and mentions the C typedef for a blob handle.
>
> http://www.sqlite.org/c3ref/blob_open.html
> http://www.sqlite.org/c3ref/blob.html
>
> typedef struct sqlite3_blob sqlite3_blob;
>
>
> Can this same interface be accessed in Python?
>
> My application manipulates image and audio content. However, on my
> constrained platform (Python 2.5, sqlite3, Maemo, Nokia Internet Tablet),
> reading and writing the media data via the conventional blob interface (as
> illustrated in teh following www.initd.org SnippetsBlobs.py example) could
> consume excessive memory. Specifically, all of the binary data are read
> into
> blobdata object at once, via the read() call. Since the media files can be
> arbitrarily large, this call could easily exceed available memory.
>
> *blob*data = *open*('c:\\*sqlite*3\\img.jpg','rb').read()
>
> con = *sqlite*.connect(':memory:')
> cur = con.cursor()
>
> cur.execute("Create table picture_table(images)")
> cur.execute("Insert into picture_table(images) values
> (?)",(*sqlite*.Binary(*blob*data),))
> con.commit()
>
>
> Instead, I need to incrementally read and write media data to a blob, to
> avoid consuming arbitrary memory.
>
> Can this be done from Python via the sqlite3 module? Can you point me to
> examples?
>
> Thanks.
>
> Norm.
>
> www.nbyoung.com
> www.personalsyndication.com
> ___
> 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] Multi-valued attributes

[Samuel Neff]

It's called a many-to-many relationship and you use a cross-reference table
to represent the relationship.  Say you have table

Searches
-
SearchID
FromDate
ToDate
etc...



Users

UserID
FirstName
LastName


Then to define what users are associated with what searches, you create a
table

Xref_Searches_Users

SearchID
UserID


Then if you want to find all searches for a user, for example, then you do

SELECT Searches.*
FROM Searches NATURAL JOIN Xref_Searches_Users
WHERE UserID = @UserID

If you're going to be searching both for searches by users and users by
search, then you will likely want to create two indexes on the xref table,
one on "SearchID, UserID" and another on "UserID, SearchID".

HTH,

Sam


On Feb 13, 2008 8:00 PM, gongchengshi <[EMAIL PROTECTED]> wrote:

>
> I have a table were each row needs to be able to store a list of entries
> from
> another table as one of its attributes.  For instance the table is a
> collection of search filters.  The filters table has attributes: FromDate,
> ToDate, Users, Devices.  The Users attribute is not a single value but a
> list of Users contained in the Users table.  Same with the Devices
> attribute.  The Devices attribute is actually a list of Devices in the
> Devices table.
>
> How do you go about defining this schema in SQL?  The book I am reading
> "Database Systems" by Connolly and Begg say that you can have such
> relationships but they don't say how to create them.  I am using sqlite as
> my DBMS.
> --
> 
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

[Samuel Neff]

But the important point is that no matter how much discussion we have, we
will never all agree that untyped is better than typed or that typed is
better than typed.  That's why an option so individual developers can choose
is good.  We don't have to agree, with an option we can agree to disagree.

Sam


On Feb 7, 2008 11:46 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

>
> There are also a whole school of people who believe that dynamic typing
> as used in SQLite is far more productive and results in less code.   For
> example the Python programming language is typed in a similar way.
>
>   http://en.wikipedia.org/wiki/Duck_typing
>
> There are many sites with many discussion threads containing opinions,
> anecdotes and evidence and code supporting all the positions on this
> issue.  Feel free to discuss the merits there.
>
> Roger
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH

[Samuel Neff]

I would like to have strict affinity mode too.  In our schemas we use check
constraints to enforce strict affinity.  Unless you're working in a dynamic
typed environment, I can't imagine why you would want to have inconsistent
data within a single database field.  Also for consistency with (every?)
other database engine out there, a strict affinity mode would be good.
Strict affinity will also benefit all wrapper writers who write wrappers
following a framework that assumes strict field typing (which I think is
pretty much all of them since all other db's have strongly typed fields).

Thanks,

Sam


On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:

> I second the strict affinity mode as an optional feature, for the same
> reasons as Lee.
>
>A while back I ran into a problem while using the bit and feature of
> sqlite and got unexpected results because sqlite changed the type from a
> 64bit integer into a real. (I think)... In this case it would have been
> simpler to debug, if there had been a type conversion warning or a failure.
>
> Regards,
> Ken
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users