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);
sq
; 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 cros
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 ";"
sq
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.co
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
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 t
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
mos
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 ap
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 e
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
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
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
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 PRO
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
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
> befo
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
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 f
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 [EMA
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 'caus
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 po
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
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,
o
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 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)
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
t;[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, jus
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 ta
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 s
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
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 upd
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 P
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 t
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
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
> th
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
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 l
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
> are
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 ba
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
AIL 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
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: SE
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...
Mic
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
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. T
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
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 IDStude
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
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 Le
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 cust
eb 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,
> Joh
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
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
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 fo
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
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
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 creat
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.
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?)
58 matches
Mail list logo