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);
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
wrote:
On Wed, Oct 31, 2012 at 9:54 AM, Samuel Neff srneff.li...@gmail.com
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
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
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:
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
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
, Simon Slavin slav...@bigfraud.org wrote:
On 11 Apr 2011, at 2:48am, Richard Hipp wrote:
On Sun, Apr 10, 2011 at 9:36 PM, Samuel Neff srneff.li...@gmail.com
wrote:
I'm sorry, my statement was misleading. I'm referring to immediately
after
our application is closed.
We're seeing
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
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 slav...@bigfraud.org wrote:
On 11 Apr 2011, at 2:04am, Samuel Neff wrote:
I'm interested in hearing anyone's experiences of using WAL journal mode
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 bart.smissa...@gmail.comwrote:
Have tried INDEXED BY and it does indeed work and force the use of the
specified index.
It
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
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
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
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,
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
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
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
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
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
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
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
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
] 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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...
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:
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.
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
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
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
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,
Here's two suggestions. First the simple suggestion is instead of this..
for (z=0;znumTwigs;z++) //there are 200
{
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.
}
try this
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
, 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
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
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
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
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
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
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
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
55 matches
Mail list logo