It's particularly valuable when you want to run queries across databases.
INSERT INTO main.table
SELECT *
FROM newdata.table;
Also if it's possible for you to segment out your data to multiple databases
but normally only work with one of them, then you can increase performance
and concurrency
A blanket stay away is pretty extreme. I would rather be able to have
triggers across attached databases, but without that will use them less, but
still there are times when they're very useful and you have no need for
triggers.
Sam
---
We're Hiring!
If you need
LIKE 'abc%'
and for it to not be case insensitive, then LIKE is preferred. Also, LIKE
is standard SQL so if you're writing SQL that has to run across different
vendors, you'd use LIKE.
Sam
---
We're Hiring! Seeking a passionate developer to
Instead of piping the sql into sqlite3.exe, use the .read command instead.
C:\Temp\ssqlite3 test.dat
SQLite version 3.4.2
Enter .help for instructions
sqlite .read test.sql
SQL error near line 10: column id is not unique
SQL error near line 12: cannot commit - no transaction is active
sqlite
: Re: [sqlite] Number of elements in IN clause
Samuel R. Neff [EMAIL PROTECTED] wrote:
I don't think it is standard SQL. At the very least, it doesn't work in
MSSQL. Standard is
SELECT * FROM maintable WHERE key IN (select x from stuff);
SQLite shortened version is much nicer.. wish
I don't think it is standard SQL. At the very least, it doesn't work in
MSSQL. Standard is
SELECT * FROM maintable WHERE key IN (select x from stuff);
SQLite shortened version is much nicer.. wish it was standard.
Sam
---
We're Hiring!
You're right, % is standard. MS Access used * and more recently supports
both * and %. I'm not aware of any other DB that supports using * as
wildcard for LIKE.
It's in the docs, but is kinda buried in the middle of this page:
http://sqlite.org/lang_expr.html
Sam
I've run into two situations recently where I would have preferred to write
triggers across databases. Both related to audit tracking of data.
The first situation is that for every table, I have a corresponding history
table that records the history of every record. So let's say I have
CREATE
Since you need notification of data so quickly, perhaps it would be better
to use some type of notification table that indicates when new data is
available and a trigger to populate this table. Then you can query SELECT
MAX(ID) FROM Notifications which is ridiculously fast.
HTH,
Sam
I've run into this issue myself and had more trouble than necessary tracking
down problems related to it. Personally I would consider it a bug, but it's
been discussed hear as accepted behavior.
Sam
---
We're Hiring! Seeking a passionate developer to
That's funny, we're currently in the process of upsizing our application
from a MSSQL to SQLite. :-)
Besides, I don't think it's ever good to encourage people to develop on one
platform with the intent on deploying to another. I've seen people do it
with Access - MSSQL and MSSQL - Sybase ASE
The same could be said for pretty much any other database.. they're all
similar 'cause they all follow (to some extent) the same standard. If
aliases were defined for PostGres then why not for MySQL, Oracle, MSSQL,
Firebird, VistaDB, SAP/DB, DB/2, and on and on.
I don't agree that defining
SQLite is not an end-user tool (nor is any other database except *perhaps*
Access, but even then it just gets people into trouble).
What you want is not a free gui for SQLite but a custom application that
does what the user needs and uses SQLite as it's data storage mechanism.
This certainly
I got this when I tried to post a comment about missing SQLite in the
article..
* You have been banned from posting. If you feel this is an error, please
email [EMAIL PROTECTED]
I've never posted on eWeek before so this is upsetting.
Sam
---
We're
This behavior is consistent with MSSQL. EXISTS returns true for NULL fields
in MSSQL 2005.
Sam
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
You could create a field in the table Value01LessThanValue02 and use a
trigger to update this value whenever data is updated. Then you can search
on just this one field. However, it's a boolean result so depending on the
percentage of records that match this condition, the index may not be that
I would expect SELECT T.* to always return all fields from table T.
However this seems not to be the case when using natural join.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\sqlite3
SQLite version 3.4.2
Enter .help for instructions
sqlite create table
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Friday, December 14, 2007 3:55 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL
... is it not
directly comparable to DeviceSQL unless the external compiler handles
not only SQL but also PL/SQL. The
Steve,
I found the information you posted to be a good contrast and would love to
learn more, but you didn't include any technical details. You said you have
atomic commits without a rollback journal and instead use some revolutionary
new way of doing commits. You said DeviceSQL performs
That also brings up the simple solution in that if you know you're db is
going to be in the 200GiB range, declare a larger page size before creating
the DB.
Sam
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based
3.5.4
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL
MSSQL results match your MySQL and PostgreSQL results. (I only changed the
table name to be a temporary table)
create table #t1(a INT, b INT, c INT);
insert into #t1 values(1, 2, 4);
insert into #t1 values(2, -1000, 5);
(1 row(s) affected)
(1 row(s) affected)
-- See if select alias
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED]
Sent: Friday, December 07, 2007 5:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any advantages of varchar() over text?
I may be wrong, but my understanding is that other than INTEGER
PRIMARY KEY, SQLite doesn't give
'between' will work fine with your situation, you just have to use
between '2008-01-01' and '2008-01-31 23:59:59.99'
or even better
between '2008-01-01' and '2008-01-31Z'
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team
One of my co-workers was playing around with SQLite on his iPhone and was
able to access data including contacts and call log and pretty much
everything. It's a SQLite database and not encrypted.
Sam
---
We're Hiring! Seeking a passionate developer to
MS SQL 2008 will support multi-row insert statements too.
http://richardsbraindump.blogspot.com/2007/07/what-new-in-sql-2008-katmai.ht
ml
Sam
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in
You could maintain a queue in memory of all the data to be written, have
each page view queue up the new data and have a single db writer thread that
dequeues items and writes to the db. That way you get the benefit of
writing directly to the db, but do not have the extra overhead on each page
Most languages have the ability to kick off different threads that run in
the background. You can have a writer thread that dumps the queue and then
sleeps for another minute and then continues the loop. I don't know PHP,
but a quick search found that it does have a Thread class which I
The vast majority of database engines run as separate services on a machine
and clients communicate with them through a network protocol. SQLite runs
in the same process as the host application either as a statically linked or
a dynamically loaded library and shares memory space of the client.
Limiting the width is good, but the pixel-based limit can cause variations
on different resolutions and font settings. I would suggest this instead:
max-width: 60em;
Which will cause the max width to adjust based on text size settings.
With the most recent change, I feel overwhelmed
I think the about text misses some of what, to me, are the most important
parts of SQLite
- in-process
- zero maintenance
Also as a .NET developer I would be put off by the C-Library reference.
SQLite works very well in many languages regardless of the fact that it's
written in C.
I would
I hope this doesn't offend, but perhaps the best solution is to outsource
the website to someone or a company that specializes in websites and design
(with your stated simplicity goals in mind of course). We certainly
wouldn't want a graphic designer hacking away at the SQLite engine, so isn't
Paulito,
I believe from a previous post you're using the System.Data.SQLite wrapper,
right? That provides Windows-only encryption built in to the wrapper.
HTH,
Sam
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based
uSQLiteServer provides it's own network protocol implementation and it's own
API so using is nothing like using SQLite itself or any other SQLite
wrapper. If you feel you need to use uSQLiteServer then you'll need to
either use the C code provided for it's client API, convert it to VB.NET, or
I like the term virtual 'cause that's exactly what they are.. a table that
does not really exist in the db and is provided by some other system. This
is not inconsistent with other DBMS's which use terms like virtualized
view, both are tables that are not linked to underlying physical data. The
Complexity of the schema affects time required to open a connection since
the schema has to be read and processed. With about 70 tables each with
lots of indexes and triggers, it takes us 17ms to open the connection.
HTH,
Sam
---
We're Hiring! Seeking
This can be done with a custom aggregate function. I posted an example a
week or so ago here in the list (example in C#).
SELECT key, DisplayList(data)
GROUP BY Key
where DisplayList() is a custom function that concatenates it's values.
I don't think this can be done in straight SQL.
HTH,
We use a custom aggregate function called DisplayList to do exactly what
you're talking about. C# code follows.
SELECT
U.UserName,
DisplayList(R.RoleName)
FROM
Users U
INNER JOIN
Xref_Users_Roles X
ON
U.UserID = X.UserID
INNER JOIN
Roles R
ON
X.RoleID
the question of how to do a backup comes up a lot so this would be very
nice. If it was added with a compile-time OMIT flag then there shouldn't be
a big concern on bloating the library.
Sam
---
We're Hiring! Seeking a passionate developer to join our
I've found that the best trade-off in performance and memory for on-demand
loading is to first run a query which retrieves all the id's of the items
you want in the list and store the list in memory. Then you can use that to
run a second query for full data using a where clause with ID IN (...).
is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 20, 2007 8:34 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite.Net
On 19.09.2007 17:54 CE(S)T, Samuel R. Neff wrote
Subject: Re: [sqlite] SQLite.Net
On 20.09.2007 15:06 CE(S)T, Samuel R. Neff wrote:
can you be more specific? Thread links..
http://sqlite.phxsoftware.com/forums/t/731.aspx
SQLiteDataReader.GetValue() not returning DateTime
http://sqlite.phxsoftware.com/forums/t/795.aspx
Cannot retrieve data
Most likely if you're simulating networks where there is a db in each node
then you really want to simulate network traffic based on that db protocol.
SQLite is an embedded database that runs in-process and by definition has no
network traffic or protocol (except if you count opening a database
Michael,
I haven't used the wrapper you mentioned so I can't help with the specific
problem in that wrapper. However, I do use the one Robert suggested heavily
and can attest to the fact that it is extremely well written and works
without errors. It's also an ADO.NET implementation making the
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
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 (of
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
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 our
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
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
+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
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
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
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
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
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
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
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.
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
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]
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 -
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
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
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
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.id
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
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
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
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
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
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
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
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.
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
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 be considered legal in some dbms and
not
in others (which
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
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
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
, 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 of include wanting to use
1 - 100 of 133 matches
Mail list logo
