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!
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 fo
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\s>sqlite3 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
sqli
rg
Subject: 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 fr
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! Seeking
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
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
-
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 joi
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 alias
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 wil
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 H
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
con
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 tab
-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 addi
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
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 products
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 signifi
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 or
-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 gi
'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 bu
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 jo
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 t
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 presume
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
vi
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 by
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 p
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
th
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 pr
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
wri
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. Th
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,
Sa
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 t
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
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 (...)"
: sqlite-users@sqlite.org
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.
n 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
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 co
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 ov
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;
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
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 (
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
"
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
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
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 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
+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
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
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
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
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
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
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
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
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
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
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!
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
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
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
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]
--
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
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 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
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!
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
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
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
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
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
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
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
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
--
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
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
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
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
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
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
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,
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
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
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
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
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
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
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
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
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
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
--
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,
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
1 - 100 of 144 matches
Mail list logo
