Personally, I think the column names given to a result set from a SELECT by
itself should be the same column names given to a table created through
CREATE TABLE AS.
For example, the following sql:
.headers on
create table a(col1 text, col text);
create table b(col2 text, col text);
insert into a(
Personally I think a database should provide long-term storage for your
application's state, not drive the application's UI. If you need to update
views based on state then that state should be in memory with mechanisms to
easily detect changes.
If you're too far along and need to use the databa
You could do this with a group by and use a min or max aggregate function on
the "ignored" column
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team products.
Position is in the Washington D.C. metro area. If interested contact
I for one would love to see SQLite support TOP as well as LIMIT (internally
it can just translate TOP to LIMIT). It would greatly help in situations
where we want to support multiple database engines in an application.
Shouldn't be a huge addition to the size of the engine either... :-)
Thank
The SQLite optimization faq [1] mentions a PRAGMA default_temp_store which
should set temp_store on a per-database level. However this doesn't seem to
be correct--the official docs [2] don't mention default_temp_store and
calling "PRAGMA default_temp_store;" always returns nothing (although
calli
Martin,
Thank you for the testing and information.
We're just starting to use SQLite and are using SQLite.NET. This library
doesn't support connection pooling and we've been discussing whether to
implement connection pooling external to the library. Your post certainly
makes it look worthwhile.
ition is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: Monday, February 26, 2007 12:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?
Samue
iring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Samuel R. Neff [mailto:[EMAIL PROTECTED]
Sent: Monday, February 26, 2007 11:58 PM
To: 'sqlite-users@
It seems I can't use string concatenation within a RAISE call. This code:
SELECT RAISE(ROLLBACK, 'test');
Works fine but this code
SELECT RAISE(ROLLBACK, 'test' || 'ing');
Gives an error
SQL error: near "||": syntax error
Is this correct behavior? I found a work-arou
One last set of performance numbers for opening a connection. :-)
This is after adding in check constraints for types and foreign key triggers
(which may only be used during development anyways).
Non-Pooled: 17,515.6 ms
Pooled with Reset :562.5 ms
Pooled without Rese
ED]
Sent: Wednesday, February 28, 2007 9:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote:
> Some of this performance gain is probably related to caching data and
query
> plan, not just opening the connection, but still
While cursors are generic to all databases, to me the test and code sample
seems to be very specific to Python. Looking at the code I can't imagine
there are actually any SQLite C calls within the cursor() method.
sqlite3.Connection() undoubtedly maps to sqlite3_open() and cursor.execute()
would
Just out of curiosity, why is the range for an 8 byte integer in SQLite one
number off from the 8 byte bigint in MSSQL?
SQLite: -9223372036854775807 through 9223372036854775807
MSSQL : -9223372036854775808 through 9223372036854775807
Values are from testing with SQLite 3.3.12 and MSSQL 2005.
S
Try this:
UPDATE items
SET price = (
SELECT price
FROM month
WHERE id = items.id)
WHERE id IN (SELECT id from month);
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in t
I'd suggest Mike Owens book "The Definitive Guid to SQLite". We just
started working with SQLite and are very impressed by the book. It's a very
easy read, well written, and covers a ton of details of internals on SQLite.
There are several chapters with examples on using the C API (as well as
ot
Actually converting everything to upper-case (or lower-case) does not
achieve case-insensitive comparison in all locales.
Here's a thread on the subject with examples in C#, but assuming consistent
i18n support the issue would affect all programming environments.
http://groups.google.com/group/m
I originally wasn't going to buy the book because according to the TOC it
look like it was generally about SQL and the C API and not too much that
would be helpful for us being knowledgeable in SQL in general and not using
the C API. We did buy it 'cause we're basically betting our new product on
pragma database_list;
will list all db's including attached (i.e., main, temp, plus attached).
Remember attached db's are per-connection only.
Personally I'd like to see an "attach if not attached" command 'cause the
way I work with connections and attached db's it would have been much easier
to
Extensions monitored by Windows Desktop Search are stored in the registry:
HKEY_CURRENT_USER\Software\Microsoft\RSSearch\ContentIndexCommon\Filters\Ext
ension
http://addins.msn.com/devguide.aspx
I don't know what the default extension list is.
Also, the wiki has information about how and what
Personally I'd forgo a few millennia in exchange for more accuracy :-)
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-
The original post said the goal is to teach algorithms, not programming
language design. So for teaching algorithms I'd have to agree that using a
custom language is not the best choice. For teaching how to write a
programming language, then of course you want to learn how to create your
own.
M
give the nested select an alias so you can reference it:
SELECT *
FROM Patient_Text
INNER JOIN
(
SELECT *
FROM Patient
INNER JOIN Opnamen
ON Patient.PatNr = Opnamen.PatNr
) N
ON N.PatNr = Patient_Text.PatNr
But if this is your actual query then I would suggest either joining all
Igor,
I was able to run the query with just a sub-select alias and no column
aliases. No error on the duplicated PatNr column from the inner select *.
I agree that I would expect sqlite to produce some kind of ambiguous column
name error or something but didn't get one.
Best regards,
Sam
Apress does have an online index they call "superindex" which indexes all
Apress books. But I just did some test searches and found it to be equally
useless. You can't search a specific book and some searches that obviously
should have returned results didn't.
In case you have better luck, the
Is there any chance of getting an international-aware case-insensitive
collation option put into the core language, at least as a compilation
option? Seems that if SQLite is so focused on UTF data and so many users
are international, the limitation of available options being English A-Z
compariso
Dennis,
Do any database systems actually implement this part of the standard? MSSQL
used the term information schema in their MSSQL 2000 version of metadata
access but afaik it was not close to the ANSI standard at all. It's a been
a while for me but I think the Oracle stuff is totally different
ssage-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 15, 2007 1:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Re: Meta Information: How to retrieve the column
names of a table ?
Samuel R. Neff wrote:
> Dennis,
>
> Do any database systems actually implemen
It's much easier to load all the data into a in-memory hierarchical
structure and work with it from memory than try to come up with individual
SQL statements. Some things you could do easily like find the leaf nodes
(i.e., use a sub-select and identify nodes that nobody else uses as a
parent) or
If the database schema is not small then use opening a connection does make
a difference. In initial testing we found opening a connection with 100
simple test tables take 3 ms. Later testing with our actual schema which
has fewer tables but is more complex takes 17ms (~65 tables with indexes an
If you're using ASP.NET I'd suggest using the ADO.NET wrapper available
here: http://sqlite.phxsoftware.com/
It's an excellent implementation of ADO.NET for SQLite and would be far less
trouble than using sqlite directly from .NET code.
HTH,
Sam
---
We'
> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it.
You don't need to write a totally custom function, just something that
bridges the two API's. Create a function that implements the expected
SQLite call spec and the
lf Of Mitchell
Vincent
Sent: Friday, April 06, 2007 11:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Currency Formatting within SQLite
It sounds so easy when you say it like that! :-)
Thanks Mr, Neff! I'll get to reading!
On 4/6/07, Samuel R. Neff <[EMAIL PROTECTED]> wrot
Instead of using LIKE use '<' to get a count of records before the one your
targeting. Something like this would work (names are from my schema):
SELECT PermissionRef
FROM LU_Permissions
LIMIT 10
OFFSET (
SELECT COUNT(*)
FROM LU_Permissions
WHERE PermissionRef < 'Sc'
Might I suggest trying the exact same scenario with FTS1 instead of FTS2?
It won't help solve the problem directly but if it works with FTS1 then it
identifies where the problem is--your code or the FTS code.
Also the same scenario can be tried with plain tables instead of FTS at all,
again just
Then perhaps an insert into a VIEW with an INSTEAD OF trigger would be
appropriate?
Best regards,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
conta
I personally would see value in supporting quasi-nested transactions where
they are nested in name only--increment decrement a counter and commit on
last commit, rollback entire transaction on first rollback. This would have
the advantage that the library would support issuing multiple BEGIN
TRANS
Aren't there different levels of backwards compatibility in play here?
Backwards compatibility of the API and making sure begin/commit/rollback
processes work the same with old and new versions is not the same as having
the ability to take a crashed SQLite db including it's rollback journal and
re
CTED]
Sent: Tuesday, April 10, 2007 5:45 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite and nested transactions
At 9:20 AM -0400 4/10/07, Samuel R. Neff wrote:
>Under what circumstances would an older version of SQLite be
>used to rollback a newer journal?
Situations I am thinking
Not that I would suggest it, but you could create a separate database for
each table and then attach the databases to a single connection to join data
together. The indexes will need to be in the same database as the table
they index.
But why do you want each table in a different file?
Sam
---
Are there any tools to help analyze the performance of components with a
particular SQLite statement? I'm aware of the EXPLAIN option which can show
what VBDE code was used to execute a statement, but afaik there is no way to
tell the time each step took.
Basically I want to know how long the di
Andy's answer and explanation is consistent with my experience and
expectations too.. mostly from MSSQL and Access background.
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metr
Wouldn't implementation dependent mean it's not really standardized? The
way I read it the query could still be considered legal in some dbms and not
in others (which stinks).
Besides, the current version of SQLite seems to match on the first tables
names which is consistent with expectations
2007 11:09 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296
On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
>
>
> Wouldn't implementation dependent mean it's not really standardized? The
> way I read it the query could still b
I would suggest including the SQL that was being processed, including all
parameters, in the error message. Even better would be to keep a log of all
SQL messages sent--perhaps keep the last X calls in memory and when an error
occurs log all of those calls and then the offending one.
log4net h
You can break up the db into multiple databases and attach them to the same
connection. That would be easiest approach (as long as one individual table
is not bigger than 2gb).
Sam
---
We're Hiring! Seeking a passionate developer to join our team buildi
We got bit by this when moving from MSSQL 2000 to MSSQL 2005. MSSQL
returned rows by default in PK order and one of our former developers
depended on this so when the behavior changed in MSSQL 2005 (which is fine
'cause it wasn't documented or expected behavior) our app broke in
unexpected ways.
afaik SQLite will only use one index per table so if you have a where clause
"WHERE public = 1 and _rowid IN (...)" it will use an index on public and
not _rowid. Swapping the where clause around should have a significant
impact:
select
_rowid,
public_id,
vote_count,
It looks like short_column_names pragma is ignored when GROUP BY is used in
a query. Is this considered expected behavior? I hope not.. :-)
Thanks,
Sam
sqlite> pragma short_column_names;
short_column_names
--
1
sqlite> pragma full_column_names;
full_column_names
--
One thing to note is that the SQLite.NET wrapper by default issues all
transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a
transaction it will be within the context of an exclusive transaction
(that's what BEGIN IMMEDIATE means, right?).
You can override this by using the
cache is per-connection so if you open and close connections a lot then a
large cache won't help your program. The command line app is a single
connection so a large cache there will help (although not with the first
queries--only subsequent ones).
Synchronous off is dangerous. Search the histor
e: [sqlite] Still getting "Insertion failed because database
isfull." errors
OK, now I am confused...
On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
>
>
> One thing to note is that the SQLite.NET wrapper by default issues all
> transactions as "BEGIN IMMEDIAT
So as of the next version of SQLite, transactions on reads for performance
will no longer be necessary.. That's great!
Thanks,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. m
Another option is to change the SQLite.NET wrapper to automatically retry on
SQLITE_FULL error similar to the way it handles a schema error. Then it
would be transparent to your app. It would have to close and reopen the
connection of course, not just retry, but still the solution is manageable
a
This is great! The main reason we decided not to use FTS in our project was
lack of prefix searching. With this new functionality we'll probably switch
to using FTS in a future update.
One suggestion though, instead of (or in addition to) using '*' as the
prefix operator perhaps '%' would be mor
Will the original poster still run into performance problems where sqlite
will only use one index per table so if targetid matches on a ton of rows
sqlite has to scan them all for the matching sourceid? Perhaps a
multi-column index would be appropriate here to index both "target.id" and
"source.i
Most anti-virus software allows you to specify an exception folder and/or
file. Tell the anti-virus to ignore sqlite db and the journal.
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington
You'll be better off converting the target age back to a date and then
search for the date. That way SQLite can use an index in your query (it
can't use an index when the filter is on an expression).
HTH,
Sam
---
We're Hiring! Seeking a passionate devel
I wonder if it would be beneficial to add an additional where clause which
can prefilter the data so you only need to perform the full calculation on a
subset of records.
I haven't done the math, but let's supposed that point_x is 10 and that for
any result of your long calculation to be true,
We don't do it in SQLite but as an example of how large a legitimate SQL
statement can be, in a previous project we generated a pseudo-cube from the
current database in a single sql statement. Basically we wanted to
implement a fast complex search routine where users can choose any fields
from an
Traditionally we've found that it's better to issue a few larger queries
against a database (such as MSSQL or Oracle) even when the results required
some processing to separate out the data because much of the cost of running
a query was communication and networking overhead. Since SQLite is an
i
filter on a nested select.
select
id,
( select sum(i2.size)
from items i2
where i2.id <= i1.id
) sum_size
from items i1
where sum_size < 5;
I'm sure performance sucks :-) Something like this would be much faster to
do in a pro
Try this
UPDATE table1
SET column3 = (
SELECT column3
FROM table2
WHERE table2.column1 = table1.column1
AND table2.column2 = table1.column2)
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team bui
Actually I'd say he gave a great explanation of why the wrapper approach is
so important. Robert went through all the work to make SQLite perform in a
scenario compatible with many other databases so now the users of his
wrapper don't have to.
Saying not to use wrappers when programming in strai
SQLite's typelessness is an asset if you work only with SQLite but in any
application that uses multiple database engines of which SQLite is only one
supported engine, the non-standard typelessness is something that has to be
worked around. I for one would be in favor of an option to enforce stri
afaik strict affininity mode hasn't been implemented.
>From
http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
"
Q) How can the strict affinity mode be used which is claimed to exist on
http://www.sqlite.org/datatype3.html
A) This has not been implemented as of version 3.3.13.
"
Sam
--
If option (b), using a single thread for writing and a multi-threaded write
queue works in your situation, then that would probably provide best
concurrency and performance. The only downside to this is the delayed
writes mean you don't as easily get feedback to the original writer if a
write fai
Nice analogy, but in the case the cat really does have 9 lives (or many
more) 'cause with SQLITE_BUSY you can just retry and while retrying is a
performance penalty in my experience SQLITE_BUSY is a very rare occurrence.
All I'm saying is don't fix a perceived problem until you've tested to be
su
When you have a connection with multiple attached databases and the
connection acquires an exclusive lock, does it always lock all attached
databases or does it keep track of which databases require the lock? Does
using separate databases and attaching them improve concurrency (by
providing finer
SQLite doesn't have any internal notion of a date, only numbers or strings.
You can choose to store dates as numbers and SQLite has some conversion
functions to help you work with dates. Personally I store dates as strings.
These are not properly formatted dates:
1997-6-17
1998-5-6
1997-6-24
19
We chose SQLite for many reasons:
- zero configuration/installation
- availability of wonderful ADO.NET 2.0 wrapper
- easily extensible with custom functions
- performance (4x faster than MSSQL in our tests)
- available commercial support
The only thing we don't like is the lack non-standard loos
Not specific to SQLite, but we're working on an app that needs to keep
versioned data (i.e., the current values plus all previous values). The
versioning is integral to the app so it's more than just an audit trail or
history.
Can anyone share experiences with the database structure for this typ
Daniel,
We already have an excellent ADO.NET compliant provider for SQLite that
Robert mentioned. What advantages does your wrapper provide of the existing
one? SQLite.NET already provides full ADO.NET support including custom
functions and collation sequences written in any .NET languages and b
Since you use C# (mentioned in a different message) you can easily write a
custom collation function with SQLite.NET.
http://sqlite.phxsoftware.com/
Look in the help for SQLiteFunction and particularly FunctionType.Collation.
HTH,
Sam
---
We're Hiring!
You could achieve this with a trigger + custom function.
MSSQL 2005 now has this ability (though no SQL syntax applies, it's built
into the ADO.NET 2.0 provider) and it's a really nice feature.
HTH
Sam
---
We're Hiring! Seeking a passionate developer t
Yes, there will be a performance hit 'cause when you access a column SQLite
will loop through the columns in the row to find the target column. If the
data is that sparse then I would suggest a different format. One that I've
used a lot and have been pleased with is the following:
Results - Res
You can do it with a JOIN instead of IN and I'm pretty sure it will still
use an index.
SELECT
COUNT(*)
FROM
guid_version_map M
LEFT JOIN
latest_version V
ON
M.guid = V.guid AND M.version = V.version
WHERE
Use UNION to run queries against each db and return a single result.
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
--
There are a few .NET wrappers for SQLite. I would suggest
System.Data.SQLite available here:
http://sqlite.phxsoftware.com/
And for ADO.NET 2.0 development use version 1.0.43. For LINQ stuff use
2.0.35.
Also wrapper-specific questions will probably get quicker responses in their
dedicated foru
If you're using a recent version of MONO then I assume you're using
System.Data.SQLite from Robert Simpson which is now bundled with MONO,
right? I use a custom version of the same provider which has connection
pooling implemented in the wrapper. We've found it to be a huge performance
improveme
Even without having FTS1 loaded, can't you delete the *_content and *_term
tables directly and that would be effectively the same as deleting the
virtual table?
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Pos
Are you using sqlite directly or some wrapper? If you're using a wrapper
then most likely you can write the MSSQL integration code in the same
language as the wrapper which would undoubtedly be easier than writing it in
straight C.
Sam
---
We're Hiring!
I believe the most important factor in whether connection pooling will be
useful is how complex your schema is. The more complex your schema, the
longer it will take to open a new connection.
Remember the choice should not be between using a single static connection
and a connection pool but s
I think you'll see the biggest difference when you run the same statement
many times with different bound variables (vs recompiling each time).
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Wa
Rollback is automatic if no Commit is issued in SQLite.NET.
If you want custom logic, such as including additional exception
information, then use try/catch
BeginTransaction();
try {
..
Commit();
} catch(Exception ex) {
RollBack();
throw new Exception("An exception occurred and the trans
Note that by default the ADO.NET wrapper executes transactions in immediate
mode which is not desirable for read-only data. To start a deferred
transaction, you need to use the SQLite.NET-specific overload
BeginTransaction(true) which is not available if using the DbProvider object
model.
Best r
SQLite parses the schema every time you open a new connection so the more
complex the schema the longer it will take to connect. We have 74 tables in
our database with a lot of triggers and it takes 17ms to open a connection.
So even if it will let you create 10,000, the performance impact of par
Unless something changed recently that I missed, fts2 is not a standard part
of sqlite so including it in the standard amalgamation would be a big
change. Having two distributions, one with and one without, might make
sense though.
Or including it with an OMIT flag would work too (default to omi
I don't think there's any built-in way but you can create a custom function
for it pretty easily. Are you using sqlite directly or a wrapper?
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Was
Is there a roadmap of major planned features in upcoming releases? I didn't
see anything on the wiki or site..
Thanks,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro are
Use of either "OR" or "Lower/Upper" will bypass any index and force a full
table scan. Much better to use COLLATE NOCASE instead or a custom collation
if you need internationalized comparisons.
Sam
---
We're Hiring! Seeking a passionate developer to join
+1 for fts3 or fts2_1 :-)
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Scott Hess [mailto:[EMA
With FTS3 can you specify the rowid to use in SQL or is it always automatic?
It seems like most commonly you'd want the FTS data to match up with a real
table using the same key and not have to store the FTS key in a separate
table. Ideally I'd want to be able to include a single foreign key inde
I get a crash with 3.3.12 but correct syntax error in 3.4.2 on Windows XP
using exe's downloaded from sqlite.org.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\sam>sqlite3
SQLite version 3.3.12
Enter ".help" for instructions
sqlite> crea
loadable extensions are not required to create custom functions, and having
access to source is not required for custom functions either. SQLite.NET
provides very clean support for custom functions written in any .NET
language and they are loaded automatically by the wrapper from any DLL
present
Method 3, normalization, is the right route but I think the implementation
needs a little more work. First don't store both Artist ID and Artist Name
in the Music table--only store the ID. This goes for AlbumID/Name and
GenreID/Name as well. Then reorder the columns to put the integers first in
"
I'm having the same problem with .net, cant find a function which does
this...
"
Which .NET wrapper are you using?
System.Data.SQLite has FTS2 precompiled. http://sqlite.phxsoftware.com
Sam
---
We're Hiring! Seeking a passionate developer to join ou
Wouldn't it be a lot easier to just create a custom function? What's the
advantage (other than pretty syntax) of using a custom operator?
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washing
The /10 syntax makes sense to programmers but I think users are going to
forget it pretty quickly. Same with "OR" an "NEAR" being required to be all
caps (I didn't know that). Ideally the UI an application exposes would show
the user that OR and NEAR were interpreted as keywords and not tokens (
If you only need Windows compatibility you can use the System.Data.SQLite
port. It's made for ADO.NET but it is also binary compatible with SQLite
and can be used from C code. It includes Windows-specific encryption and is
free.
http://sqlite.phxsoftware.com/
With the ADO.NET stuff it's larger
I see the same behavior--begin exclusive causes db locked error when same db
is attached twice. However, why would you want to attach the same database
twice?
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> attach 'c.dat' as c1;
sqlite> attach 'c.dat' as c2;
sqlite> begin exclusive;
1 - 100 of 144 matches
Mail list logo
