Re: [sqlite] Why attach databases?

2008-02-07 Thread Samuel R. Neff

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! 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 PROTECTED] On Behalf Of Nicolas Williams
Sent: Thursday, February 07, 2008 5:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why attach databases?

Given the restrictions on views and triggers (they cannot make reference
to tables from more than one database) I'd say "stay away from ATTACH
where possible" -- don't create new uses of it without good reason.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why attach databases?

2008-02-07 Thread Samuel R. Neff

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 for those times when you only need to work with one
database, but then have the ability to attach databases when you need all
the data together.

Most if not all database engines have similar functionality, but syntax and
functionality differs in each.  SQLite syntax is easiest to use of the ones
I've used (MSSQL, Access, Sybase ASE, Oracle).

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jason Tudor
Sent: Thursday, February 07, 2008 5:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Why attach databases?

I have been playing around with attaching databases.  I'm not sure what the
point is.
Assume that I have two databases with the same schema, say db1 and db2, and
I attach them to one connection.

In order to get the count from a particular table, I could do the following:

SELECT COUNT id FROM main.table UNION
SELECT COUNT id FROM db2.table

I would then step through the result and add the two values to get a total
count.


If I have two separate connections, I could run two queries and add the
results.
Is there a speed difference?

In general, what is the benefit of attaching databases verses maintaining
multiple connections?

TIA
TUD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


RE: [sqlite] Using LIKE to check the first digits?

2008-02-01 Thread Samuel R. Neff
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 join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 31, 2008 9:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Using LIKE to check the first digits?


if GLOB is so good, I am curious what are the instances in which one
would prefer

  col LIKE '123%'

over

  col GLOB '123*'


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How to make correct transaction use only SQL?

2008-01-30 Thread Samuel R. Neff

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
sqlite> select * from t1;
sqlite>

.read aborts on first error, whereas piping doesn't know to do that (and
presumably there's no way it could know).

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Alexander Batyrshin [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 29, 2008 9:37 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to make correct transaction use only SQL?

For example i have this tabe:

CREATE TABLE t1 (
  id int unique ON CONFLICT ROLLBACK,
  val char
);

And I have to execute this sql file:

BEGIN TRANSACTION;
INSERT INTO t1 (id, val) VALUES(1, 'val1');
INSERT INTO t1 (id, val) VALUES(2, 'val2');
INSERT INTO t1 (id, val) VALUES(3, 'val3');
INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT
INSERT INTO t1 (id, val) VALUES(4, 'val5');
COMMIT;

If we execute this sql file, only INSERT before CONFLICT case will be
rollback-ed, but last one still will be executed and remains in
database...

cat test.sql | sqlite3 test.db
SQL error near line 11: column id is not unique
SQL error near line 13: cannot commit - no transaction is active
$ sqlite3 test.db
SQLite version 3.4.0
sqlite> select * from t1;
4|val5


I what that on conflict _whole_ transaction will ROLLBACK and state of
database will be exactly like at moment of execution "BEGIN
TRANSACTION". How it is possible using only SQL?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread Samuel R. Neff

I don't have PostrgreSQL installed, but the docs say IN requires a subquery.

http://www.postgresql.org/docs/8.2/interactive/functions-subquery.html

MySQL does not support this syntax either (that one I tested).

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 28, 2008 9:20 AM
To: sqlite-users@sqlite.org
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 from stuff);
> 
> SQLite shortened version is much nicer.. wish it was standard.
> 

SQLite also accepts the more verbose version shown above,
of course.  The two statements do *exactly* the same thing.

I cannot believe that I would have put in the short-hand
notation without having seen it somewhere else first.
Does PostgreSQL support the short-hand version?

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread Samuel R. Neff

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 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: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 28, 2008 7:54 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Number of elements in IN clause

>SELECT * FROM maintable WHERE key IN stuff;

Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.

RBS


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Foreign Constraint Triggers Across Attached Databases

2008-01-24 Thread Samuel R. Neff

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 TABLE Users (UserID, UserName);

then I also have

CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType,
UserID, UserName)

and to track transactions I have 

CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID)

and to track the single active transaction I have

CREATE TABLE ActiveTransaction(TransactionID)

which is always blank except when in the middle of a transaction (which
always starts with inserting a record to that table, and then deleting it
right before commit).


So I have triggers on the Users table that whenever a record is inserted,
updated, or deleted, the corresponding new values for insert/update and old
values for delete are inserted into the history table.  The triggers look
like this:

CREATE TRIGGER HI_Users
AFTER INSERT ON Users
FOR EACH ROW BEGIN

SELECT RAISE(ABORT, 'Can not update database when no transaction is
active.  Create a new transaction in the Transactions table and create an
associated record in the ActiveTransaction table.')
WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0;

INSERT INTO 
Users_History (
TransactionID,
ChangeType,
UserID, UserName
)
SELECT
(SELECT MIN(TransactionID) FROM ActiveTransaction),
'I',
NEW.UserID, NEW.UserName
;
END;


Due to the restriction that triggers cannot span databases, I have my main
data tables, history tables, and the ActiveTransaction table all in the same
database.  I'd really rather the history tables be in a separate database
because they can grow quite large and when I ask a customer to e-mail me
their database, I'd like them to be able to easily e-mail the main data only
without the extra history info.

Also, it would be much cleaner if the ActiveTransaction table was in TEMP
instead of in MAIN so each connection clearly has it's own table (except
where now they share the same table definition, just the data is never
shared due to convention of being populated only within a transaction).

I hope these examples are helpful.  I would like to see the ability to
create a trigger that spans database some day and would expect that the
trigger could be defined and simply would error out if at runtime the
required database was not present.

Thanks,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 24, 2008 6:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases


Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] startswith and contains

2008-01-24 Thread Samuel R. Neff

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 


---
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: Jonas Sandman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 24, 2008 7:28 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] startswith and contains

Oh I have no idea. I thought LIKE with '%' was a standard, % being the
wildcard.

/Jonas

On Jan 24, 2008 1:25 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote:
> I was still trying "*jup*" 
> Could you show me documentation page, where this is mentioned?
>
> Pavel Kosina
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] WHERE SomeColumn IN (@SQLiteParameter) ???

2008-01-15 Thread Samuel R. Neff
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


---
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: idkfa [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 14, 2008 3:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WHERE SomeColumn IN (@SQLiteParameter) ???


That definitely worked as you described, thank you.  However, performance is
key for us.  We'll need to fire off a query like that at a steady 15hz
heartbeat (a WHERE NOT IN actually).  Could you suggest a faster equivalent
to accomplish such a task?

What about dumping the string list into a #TempTable and then firing off a
SELECT WHERE NOT IN (SELECT value FROM #TempTable)?

R

-- 
View this message in context:
http://www.nabble.com/WHERE-SomeColumn-IN-%28%40SQLiteParameter%29-tp148
08093p14813594.html
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Aggregates in SELECT without GROUP BY

2008-01-14 Thread Samuel R. Neff

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 join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Lauri Nurmi [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 14, 2008 8:15 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Aggregates in SELECT without GROUP BY 

Hello,


SQLite seems to be accepting SELECT queries that use aggregate functions 
without a GROUP BY.  This is a little dangerous, because queries that 
should not work at all are returning sensible-looking results.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite --> PostGres

2008-01-11 Thread Samuel R. Neff

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 and it never turns out well.
PostgreSQL is an open source database that runs on many platforms, I don't
see any reason why you wouldn't develop against it from the outset if your
intent is to use it for production.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 11, 2008 2:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite --> PostGres

...
 I am just trying to see how much that philosophy can allow while making
SQLite into a great app development platform that can seamlessly be upsized
to a major database such as PostGres.
...
--
Puneet Kishor


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite --> PostGres

2008-01-11 Thread Samuel R. Neff

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 aliases into the standard distribution would be
a good thing, but it would be nice to have a way to define aliases that were
then handled and substituted by SQLite, so people can have a list of aliases
that each can load and then run the SQL dialect of their choice.  There is a
bigger problem though in areas that an alias can not handle, like keywords
that have to be placed in different parts of SQL (LIMIT vs TOP) or operators
that have different meaning (+ vs ||).

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 11, 2008 2:19 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite --> PostGres

I have been writing an app using SQLite (great to develop with, and to
serve with to an extent), always with the intent of one day upsizing
it to PostGres (pain in the tush to develop with, but great to serve
with to any extent). Much to my delight, I am finding that y'all (the
SQLite developers) have made many things (for example, datatypes)
similar to PostGres (yes, I know most all about how SQLite datatypes).
My question -- why not take it all the way, and make SQLite almost a
mini-PostGres... wait, before you chide me -- I don't mean in the "add
more features" way, but in the "make syntax and datatypes as similar
as possible" way.

For example, why have the "INTEGER PRIMARY KEY" when it could just as
easily be called "SERIAL"?

One way might be to allow for aliases -- so, SERIAL would mean the
same as INTEGER PRIMARY KEY, or CHAR(3) would mean the same as TEXT
with a CHECK CONSTRAINT, and so on.

Wouldn't that increase the already wildly popular appeal of SQLite
even more so without subtracting or weighing anything down?

By the way, I didn't find a BLOB kind in PostGres -- is that the same
as BYTEA? If yes, that would be another candidate for such an alias.


-- 
Puneet Kishor


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] free excel-like COLORFUL gui for sqlite

2008-01-08 Thread Samuel R. Neff
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 will not be free, it will require custom programming and you
should evaluate whether the cost is worth the savings that the new
application will bring to the company (and the answer very often is no).

Perhaps there is an off-the-shelf product which may or may not be free that
does what you want which would be a much cheaper option than custom
development.  It may not use SQLite but I don't see how that is a major
requirement for your situation anyways.

HTH,

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
contact [EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] no mention of SQLite :(

2008-01-07 Thread Samuel R. Neff

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 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 PROTECTED] 
Sent: Saturday, January 05, 2008 12:02 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] no mention of SQLite :(

There is a comment section.  Why don't you post a comment
asking why there is no mention of the embedded SQL
database that powers Mozilla Firefox, Symbian Smartphones,
Google Android and Gears, Adobe AIR and Lightroom, 
Apple Mail and Safari, and Skype, to name but a few.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] EXISTS and NULLs

2008-01-02 Thread Samuel R. Neff

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 02, 2008 11:44 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] EXISTS and NULLs

The current behavior of SQLite is to not do anything special
with NULLs in an EXISTS operator.  For example:

   CREATE TABLE t1(x);
   INSERT INTO t1 VALUES(NULL);
   SELECT EXISTS(SELECT x FROM t1);

The final SELECT above returns 1 (true) because an entry exists
in t1, even though that entry is NULL.  This makes logical sense
because if you wanted to know if there were non-null entries
you would say:

   SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

But I have long ago learned that NULL values in SQL rarely
make logical sense, so I figure I better check.

Can somebody please confirm that this is the correct behavior
and that EXISTS does not do any special treatment of NULL
values?  Can somebody tell me what MySQL, PostgreSQL, Oracle,
and Firebird do in this case?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Samuel R. Neff

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
helpful in the end anyways.

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Clodo [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 20, 2007 5:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] suggestion for an optimized sql

Hi, i have a table like this with thousand of records:

CREATE TABLE Table01 (
  id   integer,
  value01  text,
  value02  text
);

I need to optimize the following sql:

SELECT * FROM Table01 WHERE VALUE01

[sqlite] table.* excludes ID field when using natural join ?

2007-12-17 Thread Samuel R. Neff

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 a(aid integer);
sqlite> create table b(bid integer, aid integer);
sqlite> insert into a values (1);
sqlite> insert into b values (2, 1);
sqlite> select * from a natural join b;
aid bid
--  --
1   2
sqlite> select b.* from a natural join b;
bid
--
2
sqlite>


I realize that in the natural join the field "aid" from the first "select *"
query is not considered to belong to either table a or table b, but still in
the second query, shouldn't "select b.*" include all fields from table b?

Thanks,

Sam




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] DeviceSQL

2007-12-14 Thread Samuel R. Neff
-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 addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  ...



John,

I'm not sure I see added value in incorporating procedural SQL in an
embedded database like SQLite or DeviceSQL.  Isn't the easily extensible
mechanism that SQLite has for creating custom functions in the host language
(C, C#, ActionScript, whatever) a fully valid replacement for whatever
procedure language could be provided through VDBE?  Also doesn't the fact
that SQLite (and any embedded db) runs in-process negate the importance of
SQL procedural code?  In my experience, stored procedures are usually used
to offload more work to the DB server and thus perform DB intensive code
closer to where the DB data is, which is not necessary when the DB is
already in-process?  

After moving from MSSQL to SQLite for our application, which previously used
stored procedures, we've never missed the functionality of TSQL and found
creating UDFs in C# for SQLite much easier and more powerful that using TSQL
UDFs, especially 'cause they run in-process and thus have full access to the
host application's objects and code.

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
contact [EMAIL PROTECTED]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Should the next release be 3.5.4 or 3.6.0?

2007-12-13 Thread Samuel R. Neff

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 PROTECTED] 
Sent: Thursday, December 13, 2007 11:41 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Should the next release be 3.5.4 or 3.6.0?

Ticket number #2822

  http://www.sqlite.org/cvstrac/tktview?tn=2822

has provoked extensive changes to the way SQLite handles
ORDER BY clauses.  The current algorithm goes like this:


...

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Samuel R. Neff

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. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 12:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite and Columnar Databases

Joe Wilson <[EMAIL PROTECTED]> wrote:

... The size of the bitmap is 256 bytes
per megabyte of database file assuming a 1KiB page
size.  That translates into about 52MB for a 200GiB
database.  ...

--
D. Richard Hipp <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-13 Thread Samuel R. Neff
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 significantly faster
than SQLite, can you show what tests you ran, on what platforms, and your
exact results?  I was particularly skeptical when you said "SQLite
performance, while poor on larger PCs" because in our own testing we've
found SQLite to be 4 times faster than MSSQL after we migrated.  If you're
finding SQLite performance to be poor at all, then most likely your
developers are doing something wrong in testing SQLite which of course would
invalidate your comparison to DeviceSQL.

In short, can you provide more details?  Personally I don't install demo
software just to learn what I should be able to get from the company website
(which I would hope is truly technical details, not just marketing fluff).

I tried searching online for information about DeviceSQL but pretty much
everything I found was regurgitation of marketing data from your company.
The only really compelling thing I found was this.

http://www.google.com/trends?q=sqlite%2C+devicesql

Best regards,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: steveweick [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 13, 2007 8:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard of
DeviceSQL?


oops, I guess I need to get used to this message list protocol.

First let me apologize for letting Richard get me mad. Most of my friends
would describe me as one of the most laid back people they know. Why am I
mad you ask?

We wrote Richard back in August to correct his misstatements then. He chose
to ignore the letter. Moreover he (or anyone) has been able to download our
product with all of its documentation since February or March of this year.
We encourage people to do so, because using the product is far more
convincing and informative than trying to plow through a bunch of marketing
blather.

By the way, I don't know where Richard got the stuff about me leaving the
mailing list... it never happened.

Steve



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Samuel R. Neff

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 table column has precedence in ORDER BY
  select a, a+b AS c from #t1 order by c;

a   c
--- ---
2   -998
1   3

(2 row(s) affected)

  -- See if aliases accepted in UNION/ORDER BY combination
  select a AS foo from #t1 union select b from #t1 order by foo;

foo
---
-1000
1
2

(3 row(s) affected)

  -- See if ambiguous column aliases, UNIONs and ORDER BY work together
  select a, a+b AS c from #t1 UNION ALL select a AS c, c AS a from #t1 order
by c;

a   c
--- ---
2   -998
1   3
1   4
2   5

(4 row(s) affected)


select @@version

Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) 
Feb 13 2007 23:02:48 
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)



---
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]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Any advantages of "varchar()" over "text"?

2007-12-07 Thread Samuel R. Neff
 
-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 a rat's batuti about what you define
a column as... for example,



... it does matter what datatype you use, just SQLite is very forgiving.
But read the section on "column affinity" in the datatypes page for the
importance of selecting the right types and aliases for the pre-defined
types.

http://sqlite.org/datatype3.html

HTH,

Sam


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Querying DATE column with date/time string.

2007-12-06 Thread Samuel R. Neff

'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 building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Doug Van Horn [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 06, 2007 10:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Querying DATE column with date/time string.


Thanks Dan, Igor, Donald, and Simon!

It's a problem with the Django library (a Python web framework), where that
between clause is being used for several different databases.  I'm going to
report back there with your (collective) explanation.  I appreciate the
help!


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite is in Android

2007-12-05 Thread Samuel R. Neff

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 join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Jiri Hajek [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 05, 2007 6:03 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite is in Android

> http://www.sqlite.org/famous.html

Btw, you write there:

> There are unconfirmed reports on the internet that Apple also uses SQLite
in the iPhone and in the iPod touch.

I'm pretty sure that SQLite is used there, I browsed my phone and saw
several instances of SQLite there, I'd say that they use it almost for
any data storage in iPhone... ;-)

Jiri


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite does not support multi-row inserts?

2007-12-04 Thread Samuel R. Neff

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 the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 04, 2007 10:47 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite does not support multi-row inserts?

I can't confirm whether it is, but Wikipedia claims it is:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Multirow inserts

An SQL feature (since SQL-92) is the use of row value constructors to insert
multiple rows at a
time in a single SQL statement:

 INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b,
...]), (value2a,
[value2b, ...]), ...

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and
H2.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Would SQLite be a good choice

2007-11-16 Thread Samuel R. Neff
 
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
could be used for this purpose.   If you need more help on implementing that
part I'm sure a PHP-specific mailing list would be able to provide better
assistance.

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: FredAt [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 16, 2007 10:17 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Would SQLite be a good choice

Thank you!  I like the idea of batching the writes and dispensing with the
intermediate flat file.  However, it is not clear to me where I would run
the dequeuer from. The only thing I can think of is running the dequeuer as
a PHP script which I arrange to call every few minutes.  I can certainly do
this from one of my development machines and there is probably a way to run
the script directly on my server but I don't know of that.  What did you
have in mind?


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Would SQLite be a good choice

2007-11-16 Thread Samuel R. Neff

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
view of writing to the db.  There is a risk of data loss for whatever is
queued up and not yet written to the db but as long as you're running the
writer thread often enough it's not a great concern (especially since the
data is not critical in that if you lose a few records it's no big loss).

By batching the writes you also can take advantage of transactions to speed
up writes.

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: FredAt [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 16, 2007 8:26 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Would SQLite be a good choice


Hello All,

I have used SQLite off and on in Windows applications I have written and I
really like it.  However, what I am now considering doing is using a SQLite
database to log access to one of my websites - I need to get a great deal
more information than I can get via normal log files.  Doing this will
involve making the following entries to SQLite tables each time someone
views a document on my site

1. A 20 byte string providing me with information regarding the user's
computer.
2. 48 bytes of data, in three columns, in another table to allow me to track
user activity during a session.

On the server side I will be manipulating my SQLite databases using PHP. My
site currently has around a 1000 page views per day and the number is
growing.  Is SQLite a good choice for the job?  I like it and I want to
avoid getting messy with more heavyweight options but I need to be sure that
it is going to work.  I would much appreciate any advice.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Request for help with the SQLite Website

2007-11-15 Thread Samuel R. Neff

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.  This
provides for a large number of advantages when in-process db is appropriate
such as vastly easier integration with he client and almost completely
removal of need to marshal data around (certainly not across process
boundaries).

For example when running performance analysis of MS SQL server you usually
look at both the time it takes to execute any given query as well as the
time it takes to transfer the resulting data from server to client.  Since
SQLite is in-process, the transfer time is essentially zero.

You're right about the grammatical error though.. should be "an in-process".
The original proposed text on this list was "SQLite is a high-efficiency,
in-process, transactional" but "high-efficiency" was dropped without
changing "a" to "an".

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Dr Gerard Hammond [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 14, 2007 10:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Request for help with the SQLite Website

The first few words sound incorrect to me.

Shouldn't it be.

"SQLite is an in-process"

and even then I don't know what 'in-process' actually means.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Request for help with the SQLite Website

2007-11-14 Thread Samuel R. Neff

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 the amount of text on the
front page.  I liked the previous version with just a paragraph a lot more.
Also I'd like to see the language bindings added to "Common Links".

HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 14, 2007 3:59 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Request for help with the SQLite Website

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> I probably am misunderstanding something.  The box scales down to
> narrower windows just fine, so why can't the box scale until it hits
> the width of my browser, and _then_ start doing the vertical-wrapping
> thing?
> 

There is a CSS parameter that limits the width.

max-width: 800px;

People who know tell me this is a good thing.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Samuel R. Neff

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 propose something along these lines..


SQLite is a high-efficiency, in-process, transactional database engine that
supports the majority of SQL92 syntax, stores data in a single a disk file,
and requires zero maintenance.


HTH,

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 13, 2007 8:40 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Request for help with the SQLite Website

The new look for the SQLite website is now in place,
if you haven't already noticed:

http://www.sqlite.org/


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Samuel R. Neff

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
the reverse also true? 

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 09, 2007 1:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Suggests for improving the SQLite website

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> It takes time to get all popular browsers working, but it leaves a
> good first impression with potential users of your software.
> 

It seems like a better solution would be to do the website
without any CSS and then spend the days or weeks of frustration 
saved working on SQLite instead.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Is possible put a password in database ?

2007-11-08 Thread Samuel R. Neff
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 products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: paulito santana [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 08, 2007 3:08 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is possible put a password in database ?

Hei all,
is possible i put a password in the file that represents a SQLite database
??  There is any instruction in SQLite command line that allows this ?



Regards,
Paulito


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???

2007-11-06 Thread Samuel R. Neff

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
write your own implementation based on the specs on their website.

The download package does have a "vbclient" folder but the code in it does
not appear to compile (I don't have VB6 so I can't say for sure).

However, my suggestion would be not to use uSQLiteServer.  If you really
need a server based database, then use something designed for it like MSSQL
or PostgreSQL.  Alternatively, there are other network wrappers for SQLite
which internalize the network layer and provide the same C API that SQLite
provides which should allow for easier integration--google SQLite server.

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]
 
-Original Message-
From: paulito santana [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 06, 2007 3:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???

Hello friends,
i use this code to work with a  "SQLite" database and works good :

...

What changes i must to do, for use the  "uSQLiteServer" wrapper ??? Please
help me !!

Thanks,
Paulito


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How many virtual table implemenations are there out there?

2007-11-02 Thread Samuel R. Neff

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
fact that the mechanism which provides these tables is different does not
mean they are not both validly virtual tables.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 01, 2007 5:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How many virtual table implemenations are there out
there?

I agree.

How about "Synthetic Table" or "Abstract Table"?

--- Darren Duncan <[EMAIL PROTECTED]> wrote:
> While you're at it, I strongly recommend changing the feature name 
> from "virtual table" to "federated table", or at least not something 
> called "virtual", because the older/current name is a source of 
> confusion.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Impact of no.of tables and records on Startup time

2007-10-25 Thread Samuel R. Neff

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 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: Kiran Kumar.M.R [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 25, 2007 5:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Impact of no.of tables and records on Startup time

Hi,

When SQLite is used in on disk mode,

What is the impact of no. of tables and records on the startup time.

For example,

1. number of tables (10, 100, 1000 tables,  each having 100 records, each
record 256 bytes)

2. number of records in a single table (1 table, having 10,100,1000,10
records, each record 256 bytes)

Will the startup time get affected? If yes in what order?

Thanks,

Kiran


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Help with an unusual query

2007-10-24 Thread Samuel R. Neff

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,

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]
 
-Original Message-
From: Rich Rattanni [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 24, 2007 9:46 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Help with an unusual query

All:
I have the following table


key  data
1  'Version 1'
1  'Version 2'
1  'Version 3'
2  'Version 4'
2  'Version 5'

(obviously key is not primary)

I want to write a query that returns

key data
1 'Version 1 Version 2 Version 3'
2 ' Version 4 Version 5'


Basically I want a row returned for each unique key, but i want the
data column for each key (string data) concatenated together (and
seperated with spaces if possible :) ).

Thanks,
Rich Rattanni


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] how to get file handle from sqlite3 object?

2007-10-11 Thread Samuel R. Neff

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 team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Virgilio Fornazin [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 11, 2007 2:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to get file handle from sqlite3 object?

I'm thinking about this.. to get a 'exact' copy of a sqlite database we
should implement a new public API:

sqlite3 * sqlite3_clonedb(sqlite3 * db, const char * szOutput);
sqlite3 * sqlite3_clonedb16(sqlite3 * db, const void * szOutput); // utf-16
version

this api can do the real file copy without exposing any characteristics of
the sqlite3 database (this db could be copied from / to :memory: databases
on this way) and also deal with internal VFS driver intrinsics, such as
sector size, and etc.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Getting an Array or list in a select statement...

2007-10-11 Thread Samuel R. Neff

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 = R.RoleID
GROUP BY
  U.UserName
ORDER BY
  U.UserName, 
  R.RoleName

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]
 


using System;
using System.Collections.Generic;
using System.Data.SQLite;
using Cube.MasterService.Common;

namespace Cube.MasterService.Storage
{
  /// 
  /// Creates a list of data from the aggregate source.
  /// 
  [SQLiteFunction(Name = "DisplayList", Arguments = -1, FuncType =
FunctionType.Aggregate)]
  public class SQLiteDisplayList : SQLiteFunction
  {
public override void Step(object[] args, int stepNumber, ref object
contextData)
{
  if (args.Length == 0)
  {
if (contextData == null)
{
  contextData = new ArgumentException("DisplayList requires at least
one argument.");
}
return;
  }

  DisplayListContext displayListContext;

  if (contextData == null)
  {
contextData = displayListContext = new
DisplayListContext(args.Length > 1 ? DbConvert.ToString(args[1]) : ",",
 
args.Length > 2 ? DbConvert.ToBoolean(args[2]) : false);
  }
  else
  {
displayListContext = (DisplayListContext) contextData;
  }

  displayListContext.Add(DbConvert.ToString(args[0]));
}

public override object Final(object contextData)
{
  if (contextData == null)
  {
return null;
  }

  if (contextData is Exception)
  {
return contextData;
  }

  return contextData.ToString();
}

private class DisplayListContext
{
  private readonly string _separator;
  private readonly bool _sort;
  private readonly List _data = new List();

  public DisplayListContext(string separator, bool sort)
  {
_separator = separator;
_sort = sort;
  }

  public void Add(string value)
  {
_data.Add(value);
  }

  public override string ToString()
  {
if (_sort)
{
  _data.Sort();
}
return String.Join(_separator, _data.ToArray());
  }
}
  }
}



-Original Message-
From: jose isaias cabrera [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 11, 2007 1:48 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Getting an Array or list in a select statement...


Greetings.

So, I know that I can do a single select and get a list of the different 
items found on a column, but what I would like to do is to get this same 
list on another select statement.  Let me explain;

I have this select statement,

"select ProjID,
   cust,
   proj,
   PClass,
   PSubClass,
   min(bdate),
   max(ddate),
   max(edate),
   lang,
   vendor,
   sum(invoice),
   sum(ProjFund),
   sum(PMTime),
   A_No from LSOpenJobs
 where ProjID = 24
 AND
  (
(bdate BETWEEN '2007-09-01' AND '2007-09-01') AND
(ddate BETWEEN '2007-09-01' AND '2007-09-01') AND
(edate BETWEEN '2007-09-01' AND '2007-09-01')
  )
 AND
  PClass!='Quote' group by ProjID;";

And this is working fine.  However, I would like to get a list of all the 
different PSubClass items found on this select statement.  Can this be done 
on this select or do I have to do the single select to get this item also?

thanks,

josé 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Seeking advice on On Demand Loading

2007-09-28 Thread Samuel R. Neff

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 (...)".
This is much faster than using LIMIT/OFFSET but has overhead of maintaining
an array (or whatever) of ID's (find for a few thousand, millions could be
problematic, but then again, a list with millions of records is problematic
no matter how you do it).

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]
 
-Original Message-
From: Olaf Beckman Lapré [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 28, 2007 3:48 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Seeking advice on On Demand Loading

Hello,

I'm looking for some advice on implementing 'On Demand Loading' in my
program. Some GUI controls such as a Listview have a 'virtual mode' where
the control only needs to be loaded with the items currently shown. The
Listview will call a callback function where it indicates which items need
to be loaded. BTW I'm using wxWidgets for my GUI library.

My question centers on how to implement this using SQLite as I'm not really
sure how to retrieve 'record numbers 445 to 456' using SQL statements
efficiently.

Any thoughts on this?

Kind regards, Olaf



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff

Funny, the first "bug" you pointed out is a question I asked which was
answered same day and was a report of an inconsistency with how
System.Data.SQLite tries to map strong typing on top of SQLite.  Certainly
wouldn't call it big issue and I worked around it in just as much time as it
took to post the question (the post was only to point out the
inconsistency).

Anyways, we're getting a bit off topic and from your earlier post I think we
both agree on recommending System.Data.SQLite which was the important part.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 20, 2007 9:31 AM
To: 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.com/forums/t/795.aspx
"Cannot retrieve data from a column; inconsistent results"

Another bug I reported was indeed replied quickly, but the forum didn't
notify me although specified. (I'll now do what the reply says...) So
the new forum software has other bugs, too.

http://sqlite.phxsoftware.com/forums/t/866.aspx
"Something's not cleaned up correctly"


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite.Net

2007-09-20 Thread Samuel R. Neff

Reports of two bugs would not have any bearing on my assertion of the
quality of SQLite.Data.NET, but can you be more specific?  Thread links.. 

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]
 
-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:
> 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.

Ehm, wait, I know of two bugs that have been reported in their forum and
were not even replied to for weeks. The one bug should have been fixed,
but isn't. But these errors are not critical and can be worked around in
the application code (probably decreasing the performance).

Still, this is my recommendation for using SQLite in .NET.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite.Net

2007-09-19 Thread 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 code to use
the wrapper much easier to write for an experienced .NET developer and more
consistent with all other .NET database code.  I personally believe the
small amount of time it would take to convert would be worthwhile.

Perhaps changing the subject line to mention the specific wrapper
"phpguru.org's SQLite.NET wrapper" can attract the right attention.  You can
also e-mail the author of the wrapper since there is no mailing list for the
wrapper.  http://www.phpguru.org/static/freelance.html

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]
 
-Original Message-
From: Michael Martin [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 19, 2007 11:29 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite.Net

Ok, but why I can't use this wrapper
http://www.phpguru.org/static/SQLite.NET.html ?


-Message d'origine-
De : Robert Simpson [mailto:[EMAIL PROTECTED] 
Envoyé : mercredi 19 septembre 2007 17:16
À : sqlite-users@sqlite.org
Objet : RE: [sqlite] SQLite.Net

And for good reason ... Adapting the sqlite3 api to .NET is not trivial. 

> -Original Message-
> From: Michael Martin [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 8:09 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite.Net
> 
> I am using this http://www.phpguru.org/static/SQLite.NET.html
> 
> I have the "attempted to read or write protected memory" 
> exception with this wrapper.
> 
> If I try to define my own wrapper with sqlite3.dll it crashes 
> on select statement.
> 
> -Message d'origine-
> De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> Envoyé : mercredi 19 septembre 2007 17:00
> À : sqlite-users@sqlite.org
> Objet : RE: [sqlite] SQLite.Net
> 
> If you're using the Finisar library, it doesn't work with the 
> later sqlite3
> libraries.  If you're using the PHP one, it hasn't been 
> updated in almost 3
> years so it probably has issues as well.
> 
> Your best bet is to use the ADO.NET 2.0 provider at
> http://sqlite.phxsoftware.com
> 
> Robert


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] multiple databases

2007-09-19 Thread Samuel R. Neff

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 over a
shared file server, but then you're really doing file i/o and not db i/o).
I think you need a network database for this type of simulation.
Additionally, I'm sure the results will be very different depending on the
database since they all have their own protocol with different bandwidth and
usage characteristics, so you need to take a lot of care in choosing the
right database for your simulation needs and possible would want to try with
a few different db engines.

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]
 
-Original Message-
From: nadiap [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 19, 2007 8:33 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] multiple databases


Hello,
i am a newbie and i would like to ask if it is possible to use sqlite in
order to simulate a wireless network where each node will have each own
database. I mean, can i attach each node to a database?  What shall i do? I
am sorry if my question seems naive, but i am just learning
Please help me.
Thank you
-- 
View this message in context:
http://www.nabble.com/multiple-databases-tf4480719.html#a12776486
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Filesystem Layer problems

2007-09-18 Thread Samuel R. Neff

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 than SQLite but you can also compile it
without the ADO.NET parts pretty easily.

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]
 
-Original Message-
From: Luís Santos [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 18, 2007 7:53 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Filesystem Layer problems

Hi, people

We have managed to add a simple obfuscation layer to SQLite on windows 
platform; this has been working since version 3.0.8, but We've found a 
problem on the last versions.

We've introduces a simple stream criptography layer on two functions on 
/os_win.c/ (winRead and winWrite).

The problem is: everytime a database crashes and has to be restored, we 
loose it. That happens also when an error is thrown within a trigger.

We haven't followed so closely the last changes on SQLite 
infrastructure, so I need ask for your help into it.

Is there something else I need to do now to implement my layer, what 
changed in the journal?

Thanks in advance.

--
Jason Santos


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Adding additional operators to FTS3

2007-09-14 Thread Samuel R. Neff

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
course that's up to individual application developers and not an issue for
sqlite/fts).

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]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 14, 2007 3:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Adding additional operators to FTS3

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> 
> One thing I'll think on in the background as a how-to-integrate
> question is the balance between sophistication for query experts
> versus the approachability for non-experts.  For some systems, having
> things like proximity queries complicates the query language to no
> particular end, while in other systems proximity queries might be
> essential.  Insofar as more sophisticated query forms don't interfere
> with simpler forms, they can just be ignored, but it would be nice if
> they didn't crop up in warts like unexpected results for a search
> 'stoplight near krispy kreme' where you no longer find documents where
> stoplight is more than 10 terms away from krispy.  We've discussed
> having the ability to express both more ad-hoc and more stylized
> queries, maybe this is something to think about along those lines.
> 

The OR operator has to be all caps in order to be recognized as
an operator and not the word "or".  Presumably the NEAR operator
would work the same way.  That would minimize the chance of a
collision with the word "near".  We might also require the "/10"
after the NEAR keyword or else it goes back to just being the
token "near" and not an operator.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] New Operator Support

2007-09-07 Thread Samuel R. Neff

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 Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 07, 2007 5:17 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] New Operator Support


Hi,

Just to get more hands on Sqlite i want to
write a custom operator. Pls suggest how i can do it.

For example
select * from tablex where column1 ~ '123';

I want implement it similar to '='. Can anyone help me
what all steps,files i need to change?

regards
ragha


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] FTS2 Question again Python + .NET

2007-08-20 Thread Samuel R. Neff

"
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 team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 11:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] FTS2 Question again Python + .NET



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] To increase search speed

2007-08-20 Thread Samuel R. Neff

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
the field order for the Music table, especially the foreign keys.  Finally,
create appropriate indexes.  Keep in mind that SQLite will only use one
index per table in a query so most likely you'll want a compound index that
indexes multiple foreign key fields at once.  

For queries that used to be "select distinct AlbumName from music" now just
query "select AlbumName from Albums" and you'll see a huge performance
increase (notice they don't need the music table at all).

Also, I would suggest using consistent names across tables.  So instead of
naming all your id fields "id" use something more specific like "ArtistID"
and "AlbumID" that way the same field name is used in both the Albums table
as well as the Music table.  This will make it easier to program against (no
effect on performance, just your sanity).
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]
 
-Original Message-
From: Sreedhar.a [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 1:23 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] To increase search speed


Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

"create table  ALBUMS (id integer primary key,Album
text,unique(Album));"
"create table  ARTISTS (id integer primary key,Artist
text,unique(Artist));"
"create table  GENRES (id integer primary key,Genre
text,unique(Genre));"

"create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.

 
Select distinct AlbumName from MUSIC;
 
Select  distinct ArtistName from MUSIC;
 
Select  distinct  GenreName from MUSIC;


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Problem loading extension in Delphi (FTS2)

2007-08-16 Thread Samuel R. Neff

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 in the application--they don't have to be added to the SQLite.NET
codebase.

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]
 
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 16, 2007 9:34 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2)

--- Ralf Junker <[EMAIL PROTECTED]> wrote:
> >Your product is not useful to a few users like me who require custom 
> >sqlite functions for their databases.
> 
> I am not sure I understand currectly. Only loadable extensions are
currently omited from
> DISQLite3.
> 
> sqlite3_create_function() is very well available in DISQLite3 Pro to
create custom SQL
> functions.

I assumed that people would want to load their custom functions from sqlite 
extension shared libraries. But if your customers get source code, I suppose
they can work around this.

I prefer to have a separate sqlite3 shared library so it can be customized 
and upgraded independently of the host application.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] why doesn't this work? (fts rowids)

2007-08-15 Thread Samuel R. Neff

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 indexed
integer field so a FTS table would like like:

OID
FK_ID
FTS_FIELDS

So you can associate multiple FTS records with a single standard record
without having to create a linking table (linking tables typically define a
many-to-many relationship and here we have a one-to-many relationship).

FTS's job isn't to enforce constraints, but it allowing users to link FTS
data to regular data. 

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
contact [EMAIL PROTECTED]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

2007-08-14 Thread Samuel R. Neff

+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:[EMAIL PROTECTED] 
Sent: Tuesday, August 14, 2007 8:22 PM
To: [EMAIL PROTECTED]
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> >
> > Unfortunately, code of the form:
> >
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> >
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
>
> It appears that you can set
>
>PRAGMA writable_schema=ON;
>
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

Ooh, I think that tips me away from fixing fts2, because it's scary
and Google Gears disables PRAGMA.

At least Joe threw in a vote for just versioning things to fts3 -
anyone want to vote against?

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Is SQLite Case Sensitive?

2007-08-07 Thread Samuel R. Neff

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 our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Tuesday, August 07, 2007 6:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is SQLite Case Sensitive?

SQL is not case-sensitive, but SQL comparisons are.

Use the following

SELECT * FROM table WHERE field1 = 'a' OR field1 = 'A'

you can also use

WHERE Lower(field1) = 'a'

or

WHERE Upper(field1) = 'A'



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] SQLite roadmap?

2007-08-03 Thread Samuel R. Neff

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 area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 02, 2007 8:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and
referential integrity

At 2:11 PM -0700 8/2/07, Mikey C wrote:
>Does anyone know if there is a plan to implement the enforcement of the
>SQL-92 FOREIGN KEY constraints?
>
>Seems to me the No.1 missing feature.  After all, data integrity, even in
an
>embedded DB is very important and bugs in client code can easily mess up
the
>referential integrity.

I believe that this is planned; however, some other planned upates 
have higher priority and are being done first.  Note also that 
traditional foreign key constraints can only implement some kinds of 
business rules, and there are lots of others whose violation could 
cause problems, that foreign keys wouldn't help with; in theory, the 
more generic format of triggers is a more complete solution, or more 
specifically, free-form check constraints that can be comprised of 
any query are a more complete solution. -- Darren Duncan


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] how do i generate a uniqueidentifier ?

2007-07-27 Thread Samuel R. Neff

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 Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Chase [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 27, 2007 4:03 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how do i generate a uniqueidentifier ?


sorry.  should have been more specific.  i'm talking about guids (or uuids).

here's the create table line:

CREATE TABLE foobar (id uniqueidentifier, foo text, bar text, PRIMARY 
KEY (id));

that works great, but i have not been able so far to generate a fresh 
guid to insert into the table.

in ms-sql, you'd use newid(), for example:

insert into foobar values (newid(), "Aaa", "Bbb");

and then you'd get something like:

select * from foobar;

idfoo  bar
___

{0109--0010-8000-00AA006D2EA4}"Aaa""Bbb"


so how is this done in sqlite3?

- chase


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] fts2 in the amalgamation source?

2007-07-26 Thread Samuel R. Neff

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 omit).

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]
 
-Original Message-
From: David Crawshaw [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 26, 2007 3:39 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] fts2 in the amalgamation source?

Hello all,

I was wondering if it would be possible to include fts2 in the
amalgamated version of the source code. It looks like all that needs
to be done is add

tclsh $(TOP)/ext/fts2/mkfts2amal.tcl

to the end of the target_source target in Makefile.in and then add

fts2amal.c

to the end of the "foreach file" loop in tool/mksqlite3c.tcl. I
hesitate because with the scripts effectively written for this, there
is probably a reason why fts2 has been omitted.

Thank you,

David


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How many table can i create in a db?

2007-07-20 Thread Samuel R. Neff

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 parsing
the schema each time may be prohibitive (although if you can open one
connect and leave it open for a long period of time, you can mitigate the
parsing issue).

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]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 20, 2007 4:25 AM
To: SQLite
Subject: [sqlite] How many table can i create in a db?

I open one db,then create table.
How many table can i create?
10,100?
what is the max table num in one db?

BR
allen.zhang


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-17 Thread Samuel R. Neff

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 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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 16, 2007 4:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Does Transaction object roll back automatically on
exceptions?

Hi all,

Is it necessary to run a "SELECT" command in between a transaction?  I
have few places in my code where I have a command that reads some data
from a table, and I wonder if I should begin/commit a transaction? Is
there any performance issues if I did or didn't do that?

Regards



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Does Transaction object roll back automatically on exceptions?

2007-07-16 Thread Samuel R. Neff

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 transaction has been
rolled back.", ex);
}


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]
 
-Original Message-
From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 16, 2007 10:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Does Transaction object roll back automatically on
exceptions?

Hi all,

I have tried this question in the forum with no answer, so I decide to
give it a try here :)

I am using the "Using" statements in C# to handle the closing of SQLite
connection, transaction and command objects.  I have this setup in
different places in my code (Generic methods returning SQLite objects)
  
using (IDbConnection conn = CreateConnection())
 {
   conn.Open();
   using (IDbTransaction transaction = conn.BeginTransaction())
{
Dosomething();
transaction.Commit();
}  
   } 

My question, what happens if there was an exception inside the
transaction using block? I know that the transaction object would be
closed and disposed, but does the traction roll back automatically by
the SQLite engine, or do I need to have special logic for that? What is
the suggested best practices in this case?
 
Cheers


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Binding vs No Binding

2007-07-12 Thread Samuel R. Neff

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 Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Nathan Biggs [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 12, 2007 4:30 PM
To: SQLITE
Subject: [sqlite] Binding vs No Binding

I have been trying see just how fast I can get SQlite to run, so I have 
been playing with binding data to pre-compiled statements versus having 
to prepare each statement individually.  So far, I have not seen a huge 
performance increase between the two.  I expected the pre-compiled 
statements to be much faster.  When I ran the test, I used a simple 
insert statement with just 4 variables.  Will you only see a big 
performance difference if the statement is more complicated?


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] FTS and upgrades

2007-07-10 Thread Samuel R. Neff

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. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
On 7/9/07, Scott Hess <[EMAIL PROTECTED]> wrote:
>
> If you have not compiled in fts1, and try to drop an fts1 table,
> you'll get an error.  I don't think you'll get a crash, but sqlite
> will simply not know how to deal with the table.
>
> I can think of two ways to deal with this.  When builidng a new
> version, you could just leave the fts1 code in place.  Otherwise, you
> could put the fts1 table in an attached database, which you could
> delete using filesystem operations when you upgrade.
>
> I would recommend just having both fts1 and fts2 available.  If you're
> super-concerned about code size, you could pretty easily strip out all
> of the interesting code from fts1.c and leave only enough to
> successfully drop tables.  A little more aggressive would be to leave
> enough code to do read-only queries without the fulltext index (useful
> for pulling all the content data across to fts2).
>
> [Let me know if I misread your question.]
>
> -scott


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How to implement connection pooling

2007-07-10 Thread Samuel R. Neff

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
improvement for our typical business application since most user requests
include a small number of short database queries.  The time to establish a
connection varies by schema and with ours we measured it at 17ms, so saving
17ms on every db call is huge (especially since most db calls average under
1ms).

There have always been some warnings about using connections across threads
but I think as long as the pool is managed properly and an individual
connection is only used on one thread at any given time and all statements
are properly closed before being released back to the pool, there are no
problems.  We've done a lot of testing and haven't run into any troubles.

We'll be releasing our customized version of SQLite.NET wrapper with
connection pooling as soon as we have some time to integrate the latest
SQLite & SQLite.NET versions.

BTW, I would request you make a post on the official System.Data.SQLite
forum since the author hasn't expressed any interest in connection pooling
yet even though there has been a lot of community interest (he does read
this list as well though).  

http://sqlite.phxsoftware.com/forums/thread/3011.aspx

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]
 
-Original Message-
From: Dinesh Ramdass [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 10, 2007 3:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to implement connection pooling

HI ,

*How we use SQlite:*

We  use MONO and SQLite 3 for our project which runs on Linux. It is
going to be used as an embedded database in a device.

*What i want to do:*

I want to use connection pooling either using some feature provided by
SQLite or someone else

*What i want to know:*

   I read in SQLite FAQs that we can have multiple connections. But there
was a limitation with using same connection across threads. I think that u
were referring to use of Connection pooling. So my question is around this

*Question 1:* Does SQLite direct provide support for connection pooling in
form of any library or opensource binary?
*Question 2:* If there is no direct support for connection pooling, then is
it becos we should not use? Does SQLite not advise the use of implementing
connection pooling?
*Question 3:* If my database access is going to be often but only for 5-6
rows CRUD operation at a time, then do u advice me to open a new connection
and close it after use instead of maintaing an open connection for a long
time or resetting a connection using timeout?

Kindly reply A.S.A.P. Its pretty urgent .
*Also i need to give a justification for not implementing CONNECTION POOLING
to my client.*

Kinldy provide me a justification.

be Happy,
Dinesh.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Using SQlite with .NET

2007-07-06 Thread Samuel R. Neff
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 forums.

http://sqlite.phxsoftware.com/forums/default.aspx

I haven't used the feature but I'm pretty sure that provider adds support
for viewing/editing SQLite within Visual Studio 2005.

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]
 
-Original Message-
From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 06, 2007 2:02 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Using SQlite with .NET

Hi,

 

I am trying to evaluate the use of SQLite with .NET in out project. I
have downloaded the ADO.NET 2 adaptor for SQLite from source forge. But
I was not sure which version do I need to download (if any) from the
download page to get myself started. Could you please give me some
direction, links would be great. Also, is there a database
viewer/updater to populate tables with some test date in SQLite?

 

Regards

Ahmed



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] attached databases

2007-07-04 Thread Samuel R. Neff

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]
 
-Original Message-
From: Tom Deblauwe [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 04, 2007 5:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] attached databases

Hello,

As I understand it, you can attach databases together with sqlite and 
then perform 1 query on all the databases together.

I have this situation where I have 3 disks, and every disk contains a 
database, and each of those databases has the same structure: ID, 
TimeFrom, TimeTo, Events

I would like to do a query on all 3 databases, where I check the bitmask 
of 'Events' for a certain value and sort the results in ascending order 
based on TimeFrom.

Can I do this with SQlite? How does the query look like, and what 
columns are returned then? Hopefully the full list with only 4 columns, 
not 12(3 db's x 4 columns)?

Thanks for your time,
Best regards,
Tom,


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Multi-column "in" clause supported?

2007-06-29 Thread Samuel R. Neff

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
V.guid IS NULL;
 

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]
 
-Original Message-
From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 29, 2007 5:35 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Multi-column "in" clause supported?

I'm attempting to execute this SQL statement ( using SQLiteSpy, if that
matters, which is based on 3.3.16 ):

select count(*) from guid_version_map
where (guid,version) not in (select guid, version from latest_version)

Basically I want to retrieve the rows in guid_version_map whose primary key
(guid,version) are not present in some other table.The error reported is
a "syntax error near ",", which I assume is where the portion of the "where
(guid,version)" tuple gets defined.

Does this imply that SQLite does not support the multi-column "in" clause?

I can code this up in a procedural way, but I love making the database
execute as much stuff as possible, reducing the amount of inter-database
interface to my C++ code as possible.


--andy


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Sparse Data Sets

2007-06-29 Thread Samuel R. Neff

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 - ResultID, ResultName (or whatever primary identifiers there are)
ResultDetails - ResultDetailID, ResultID, FieldID, Value
Fields - FieldID, FieldRef

so for each "record" in your original you create a Results record and then
for each "column" in the original you get a ResultDetails row.  The data is
quickly retrieved and the advantage is the format is very flexible and can
store pretty much anything.  If your data is that sparse and I would assume
flexible in that you may be adding columns as time goes on, then the above
format should work well for you.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Carl Jacobs [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 29, 2007 2:33 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Sparse Data Sets

I'm planning to use SQLite in an application that has a lot of rows (1M+) 
with a moderate number of columns (about 50) with the data being somewhat 
sparsely populated - ie each row may only have data in a primary column and 
5 other columns. Will I get a performance hit - in terms of the size of the 
file - for all the unused columns? The other option would be to have a table

per column, but then I need an extra column (primary) per table to tie the 
data together.

I want to store (about 50) pieces of information each of which may or may 
not be logged at a reasonably high rate. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Sqlite - LISTEN/NOTIFY

2007-06-28 Thread Samuel R. Neff

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 to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Shilpa Sheoran [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 27, 2007 5:09 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite - LISTEN/NOTIFY

All,
  Does sqlite implement LISTEN or NOTIFY commands?
Basically if one application modifies the database will sqlite notify
other interested applications about the modification?


Shilpa


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How to sort not binary?

2007-06-25 Thread Samuel R. Neff

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! 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: Yves Goergen [mailto:[EMAIL PROTECTED] 
Sent: Saturday, May 12, 2007 11:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to sort not binary?

Hi,

I'm trying to get my table sorted the way how for example Windows
Explorer or other file managers are sorting names. Most of all, accented
characters should not be listed at the end of the list but near their
non-accented character instead. I can only see the built-in collations
BINARY and NOCASE. Is there something else?

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Introducing... ManagedSQLite

2007-06-25 Thread Samuel R. Neff
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 built-in
encryption.

Thanks,

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]
 
-Original Message-
From: WHITE, DANIEL [mailto:[EMAIL PROTECTED] 
Sent: Saturday, June 23, 2007 10:29 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Introducing... ManagedSQLite

Howdy all!

I am just writing tonight to let you know that a project of mine has
opened up to being open source -- ManagedSQLite.  It is a light wrapper
around SQLite 3.4.0 that was originally written by Rob Groves.  I have
added support for Unicode to his wrapper, then added my Managed (.NET)
port to the mix.  One DLL file unlike others out there.  Supports FTS1
and FTS2 out of the box!!!

I am looking for help with this little project.  I think we could
eventually make an entire ADO.NET wrapper with this thing.  Thanks.

http://code.google.com/p/managed-sqlite/

Thanks for reading.

Daniel A. White 
{ Kent State University: Computer Science major }
{ JMC TechHelp: Taylor Hall, server techie }
{ E-mail: [EMAIL PROTECTED] }
{ Colossians 3:17 }



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Data structure for versioned data

2007-06-20 Thread Samuel R. Neff

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 type of
requirement or point me to helpful resources?

Thanks,

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]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Why do you use SQLite? Comments for an article needed

2007-06-07 Thread Samuel R. Neff

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 loose data type
behavior and lack of many data types (particularly dates).

Best regards,

Samuel Neff
Sr Software Engineer
B-Line Medical
http://www.blinemedical.com


---
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: Tim Anderson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 07, 2007 1:50 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Why do you use SQLite? Comments for an article needed

I'm writing an article about SQLite and I'd love to get some comments
from users about why you use it. Performance? Features? Reliability?
Cost? Is the open source aspect important? Anything else? For that
matter, anything you really don't like about SQLite?

You can email me at tim(at)itwriting.com or comment here if you prefer -
but to use your quote I'd need at least a full name, what you do and the
company you work for

Thanks in advance for your help.

Tim
http://www.itwriting.com/blog



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] extracting and comparing dates

2007-06-04 Thread Samuel R. Neff

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
1998-5-6
2000-3-15

should be

1997-06-17
1998-05-06
1997-06-24
1998-05-06
2000-03-15 

this

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1

should be

sqlite> select First_Capture from PIT_manatees where
First_Capture<'2000-01-01'

and this

sqlite> select First_Capture from PIT_manatees where
First_Capture select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.

Any help most appreciated.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Does Attach improve Concurrency ?

2007-06-01 Thread Samuel R. Neff

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-grained locking)?

Thanks,

Sam




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Concurrency

2007-06-01 Thread Samuel R. Neff

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
sure your perception is correct.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 01, 2007 9:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concurrency

You make a very good point.  Someone called it the "cat running over the 
road" situation.  A cat runs across a busy road without looking by going 
as fast as possible and rarely does one get run over.  On the other hand 
roads are lined with dead armadillos.

If you synchronization logic is sound and you avoid polling or waits the 
faster your database runs the more concurrent transactions per second 
you will achieve.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Concurrency

2007-06-01 Thread Samuel R. Neff

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 fails (bad data for example).  You could build delayed
feedback/callback into the system, just depends on your application
architecture.

Also, I would strongly suggest doing some actual testing and confirming
there is a bottleneck without any custom code to increase concurrency before
programming workarounds for an expected problem.  What we've found is that
SQLite is so much faster than other databases (MSSQL in particular) that
concurrency is greatly improved simply because each write is faster and thus
the time the db is locked is less even though the whole db is locked.

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]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 01, 2007 5:18 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency

Hi,

I am having a scenario where I have one reader/writer and many writer
threads.
All writers are pretty basic (single INSERT INTO; some sort of a logging
info
what a thread has done).

I believe I will receive many BUSY return codes and I don't like these
spinlock-like retries. The problem I am having with this design is that I
would
like to complete the thread ASAP, so that I don't have many threads idling
and
consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every writer thread
populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't want to
use
other database, because I think Sqlite is great for an embedded system that
I
am using.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: CAST

2007-05-30 Thread Samuel R. Neff

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

---
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 30, 2007 12:04 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: CAST


> I for one would be in favor of an option to enforce strict
> typing (compile time option).

"SQLite version 3 will feature two other affinity modes, as follows:
Strict affinity mode. In this mode if a conversion between storage classes
is 
ever required, the database engine returns an error and the current 
statement is rolled back."

I hope it means that SQLite will behave like any other database, and errors 
during insert will be detected while they are made, not in some unspecified 
time in the future.
This is probably not a problem with machine generated SQLs, but if a human
is 
allowed to enter SQLs, working with a database may be difficult.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: CAST

2007-05-30 Thread Samuel R. Neff

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 strict
typing (compile time option).

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 6:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You are looking for a fit to one particular restrictive, proprietary 
environment.  Our approach has been to work with the spirit of Sqlite 
and to its strengths and to that end we designed out environment 
accordingly.  Sqlite's typing has become a major asset, not a difficulty.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: CAST

2007-05-29 Thread Samuel R. Neff

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 straight C and using only
SQLite is one thing, but of course when developing in any other language or
when supporting multiple databases wrappers are essential (all of our
applications are in .NET and some support both SQLite and MSSQL).  We would
never have considered using SQLite for our product if it wasn't for Robert's
SQLite.NET wrapper.

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]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 11:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have just given an excellent explanation of why the wrapper approach 
is flawed.  Think about it.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Why doesn't this UPDATE work?

2007-05-25 Thread Samuel R. Neff
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 building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Brian Albert [mailto:[EMAIL PROTECTED] 
Sent: Friday, May 25, 2007 11:30 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Why doesn't this UPDATE work?

I've been struggling with the following:

update table1 set column3 = (select i.colum3 from table2 i, table1 t  
where t.column1 = i.column1 and t.column2 = i.column2)

Reading the manual and this list I learned that this statement will  
grab the first result of my sub-select and populate it in every row  
(cruel, but I can see the logic).  However what I want is for the  
different values that I get from the sub-select to be entered into  
the appropriate rows of column3.

I looked at using INSERT or REPLACE but the subselect (when run  
standalone) returns 34001 rows, and table1 has 34004 rows - the  
result is all 125000 rows of table2 being appended to table1 (with  
NULLs in all but column3).

Neither table has a primary key and I'm running 3.3.17 on MacOSX 10.4.9

Is UPDATE the correct way to do this? Also, is there an SQLITE  
chatroom on IRC?

Many thanks in advance.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Limit selection by rolling sum?

2007-05-14 Thread Samuel R. Neff
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 procedural language instead of in SQL.

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]
 
-Original Message-
From: Steve Krulewitz [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 14, 2007 8:26 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit selection by rolling sum?

Hey all --

I have a table that I would like to select the first N records where
the rolling sum of a given column is less than some literal value.
SQL for this might look like:

select item_id from items where rolling_sum(item_size) < 1 order
by item_name;

Is there a way to do this?  Would sqlite know to stop processing the
query once the limit is reached?  Manually doing the rolling sum while
reading the query result is not a good option for me as I am working
with an API on top of sqlite that does not allow this.

I am already using a custom collation sequence, so adding a custom
function would not be a problem :)

cheers,
-steve


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Many small queries vs fewer large queries

2007-05-14 Thread Samuel R. Neff

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
in-process/in-thread database engine, the communication overhead is now zero
and we believe it may be more efficient to switch to chattier database
communications.

What is everyone else's opinions on using many small queries vs one big one?

Thanks,

Sam




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Longest "real" SQL statement

2007-05-10 Thread Samuel R. Neff

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 any tables and we can filter quickly.  

The sql statement was an INSERT statement with several hundred fields and
40+ joined tables (no where clause).  Initially it took 30 seconds to run on
Sybase ASE but after some optimization (mostly of the OS, not sql) it ran in
under a second for typical production data.  Of course we removed all
indexes during the actual insert operation.

If we did the same thing in our current project we'd have 242 fields in the
select list and 69 tables in the FROM clause.  No where clause or order by.

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]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 09, 2007 7:33 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Longest "real" SQL statement

I'm looking for an upper bound on how big legitimate 
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

Thanks.
--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] perfomance degradation for expr "foo = X or bar =X"

2007-05-09 Thread Samuel R. Neff
 
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, then x must be between 5 and
15, then you can use the where clause

WHERE
X BETWEEN 5 AND 15
AND (point_x - x)^2 + (point_y -y)^2 < R^2;


If you can make this type of pre-calculation and get a proper range for X
then it can reduce the number of records that need to be checked.  I'm
assuming SQLite will use an index on X for BETWEEN (I don't know for sure).
Also you should do testing to be sure this query really is faster in
practice--I'm only theorizing here.  :-)

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]
 
-Original Message-
From: bash [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 09, 2007 10:33 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

On Wed, 9 May 2007 18:13:07 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

Im currently thinking about this table:

CREATE TABLE map (
x int,
y int,
name char
);
CREATE INDEX map_x ON map(x);
CREATE INDEX map_y ON map(y);

And query for it will be something like this (circle):
SELECT name
  FROM map
  WHERE (point_x - x)^2 + (point_y -y)^2 < R^2;

How SQLite will works? Is there any benefit in indexes?



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Search on Age, from DOB column

2007-05-03 Thread Samuel R. Neff

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 developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 6:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column

Hi,

I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Transaction journal corrupted by antivirus

2007-05-03 Thread Samuel R. Neff

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 D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Voxen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 2:58 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Transaction journal corrupted by antivirus

Hi,

My application is storing email messages in a SQLite database (raw email  
messages as they are downloaded from the mail server).
This is done under a transaction.

Sometimes I get the SQLITE_MISUSE error and by watching the antivirus  
(Avast) log, I found this:
Sign of "Uruguay 6/7/8" has been found in "C:\Documents and  
Settings\Voxen\Application Data\MyApp\mail.db-jounal" file.

This means the antivirus found a virus in the transaction journal file and  
removes it. Its altering the journal file and then produces a  
SQLITE_MISUSE error.

How can I work around this?
Do I need to ZIP or encode the email message before storing it in the  
database?

Thanks
Voxen



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] FW: Performance problem with complex where clause

2007-05-02 Thread Samuel R. Neff

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" in the same index.

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]
 
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 02, 2007 10:58 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FW: Performance problem with complex where clause

...

This should run in a reasonable time given that you have indexes on 
Link("Target.Id") and Link("Source.Id")

HTH
Dennis Cote



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Prefix searching for fts2.

2007-05-01 Thread Samuel R. Neff
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 more appropriate in order to be closer
to the LIKE operator.

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
contact [EMAIL PROTECTED]
 
-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 01, 2007 2:36 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Prefix searching for fts2.

I just finished ( http://www.sqlite.org/cvstrac/chngview?cn=3893 )
checking in a string of changes to fts2.c to provide prefix search.
This works like:

...

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Still getting "Insertion failed because database isfull." errors

2007-04-18 Thread Samuel R. Neff

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
and can be encapsulated within the wrapper.

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]
 
-Original Message-
From: Joel Cochran [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 18, 2007 10:55 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still getting "Insertion failed because database
isfull." errors

OK, I understand.  This was my initial instinct, that it had to be coming
from the Database, which was why I contacted DRH.  His reponse was basically
that my symptoms didn't match a problem in SQLite, given the other
information at hand, and he is correct.  So really, it isn't SQLite's
problem OR the wrappers problem: it is the way the device handles its
handles regarding the removable media.

I'm testing it now with moving the database to internal memory.

Thanks,
Joel



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Efficiency and Pragmas

2007-04-18 Thread Samuel R. Neff
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 history for details.

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]
 
-Original Message-
From: Alberto Simões [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 18, 2007 3:59 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Efficiency and Pragmas

Hi

I am using these pragmas for efficiency in data loading (I hope)
PRAGMA page_size = 4096;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = 100;
PRAGMA synchronous = OFF;
PRAGMA count_changes = 0;

The truth is that adding these pragmas my inserts got quite faster.

Now the problem is querying. If I use sqlite3 shell I get answers in
one (two) second(s). If I use my program after these pragmas, I get a
slow query.

Is there any good reason for that?
Thanks

PS: let me know if more detail on the query or database can be handy
for answers.

Cheers
Alberto
-- 
Alberto Simões


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Still getting "Insertion failed because database isfull." errors

2007-04-18 Thread Samuel R. Neff

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 wrapper-specific
BeginTransaction(deferred) override but it is not accessible if using the
DbFactory standard interface. 

I completely agree with Dan that there is no way the wrapper is generating
this error message, however behavior in the wrapper such as the above could
be contributing to it.

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]
 
-Original Message-
From: Dan Kennedy [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 18, 2007 1:25 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still getting "Insertion failed because database
isfull." errors

> At first I thought this had solved the problem, because all in house
testing
> runs beautifully.  However, as soon as the device is sent to the field,
the
> error starts again.  Unfortunately, it means that I have never been able
to
> catch this in debug.  I did, however, change the error reporting a little
> and got some more information.  The SQLiteException I am not getting
> includes this information:
> 
> Insertion failed because the database is full

That message is from the wrapper.

> database or disk is full

And the above is from sqlite3. The corresponding return code is 
SQLITE_FULL. Search source file "os_win.c" for where SQLITE_FULL
errors can be generated - there's only a couple of places. Odds
on it's one of them. Looks like on windows, any error writing
or seeking a file is reported as SQLITE_FULL.

> at System.Data.SQLite.SQLite3.Reset()
> at System.Data.SQLite.SQLite3.Step()
> at System.Data.SQLite.SQLiteDataReader.NextResult()
> at System.Data.SQLite.SQLiteDataReader.ctor()
> at System.Data.SQLite.SQLiteCommand.ExecuteReader()
> at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader()
> at ... etc etc

So is this really a SELECT? Probably good to check that.

If it is a SELECT, why would it be filling up the database?
Is it rolling back a journal file and running out of space
like that? Or the user rolling back the journal file doesn't
have permission to write the database file and SQLite is 
reporting the error incorrectly.

Check for a journal file on the device after the error. Also
run the SQLite integrity-check on the database.

Dan.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] PRAGMA short_column_names ignored when GROUP BY is used

2007-04-17 Thread Samuel R. Neff
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
-
0
sqlite> select u.userid from users u limit 1;
UserID
--
1
sqlite> select u.userid from users u group by u.userid limit 1;
u.userid
--
1
sqlite>


---
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]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Insert order maintained?

2007-04-17 Thread Samuel R. Neff
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.  

With any db it's always best to specify an ORDER BY if you care about the
order.

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]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 17, 2007 11:18 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Insert order maintained?


> I know this is the behavior for MySQL, but not sure about SQLite.

I'v heard about some version of mysql that didn't return rows in the same 
order (but haven't seen it myselt). So unless this behaviour is documented 
in mysql manual, it's not a good idea to rely on this. Actually I'v seen 
only 1 database that allways returns rows in random order, but it doesn't 
mean that other databases guarantee anything.

--
Jak bedzie wygladac koniec swiata? >>> 
Zobacz >>> http://link.interia.pl/f1a38


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Optimize a query

2007-04-17 Thread Samuel R. Neff
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, 
status, 
summary, 
component, 
date 
(date_modified), 
quickfix 
from 
reports 
where 
_rowid IN  (
select distinct r._rowid 
from reports r, segments s 
where  
s.report_id = r._rowid 
AND r.public = 1 
AND s.public = 1 
AND  (r.summary LIKE '%server%' OR s.content LIKE
'%server%')
) 
AND public = 1
order by  vote_count DESC


but really the "public = 1" where clause on the outer query doesn't look
necessary since you already are checking public = 1 in the inner query.

Also, formatting SQL statements so they aren't a huge blob of sql will make
them easier to read, especially for mailing lists.  :-)

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]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

2007-04-13 Thread Samuel R. Neff

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 building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Ludvig Strigeus [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 13, 2007 2:32 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

Does Sqlite support databases larger than 2GB on FAT filesystems?

If not, how hard would it be to add so it uses additional files for the
pages that don't fit in the first file?

Thanks
Ludvig


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread Samuel R. Neff

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 has a nice logging appender for this where you can log debug
messages and it will keep a certain number in memory until an error occurs
and then when the error occurs it writes out the previous debug messages
leading up to the error in addition to the error itself.

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]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff

Still, I think backwards compatibility and consistency with other databases
would be most important in this situation.  I just checked MSSQL and it's
same as current sqlite which uses the first select statement's column names.

It doesn't just affect order by too.. based on the standard SQLite should be
returning a different column name entirely in the result set.  Surely it
would break a lot of code out there if SQLite suddenly started returning
seemingly random column names from union queries when the query didn't
specifically alias the columns.

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]
 
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 12, 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 be considered legal in some dbms and
> not
> in others (which stinks).


Samuel,

That's not what the standard says. It says the name assigned to the result
columns are implementation dependent, they could be sqlite_column_1 and
sqlite_column_2, or perhaps cnnn where nnn is a random number, but
they can not be the names of any of the columns in any of the tables in the
query. The query should generate a syntax error because it is trying to sort
on columns that are not, or at least should not be, present in the result.


Besides, the current version of SQLite seems to match on the first tables
> names which is consistent with expectations from other databases and not
> prohibited by the standard (in the way I read it) and backwards
> compatibility seems to be the most important thing here..
>
>
This behavior is prohibited by the standard.

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff
 
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 from other databases and not
prohibited by the standard (in the way I read it) and backwards
compatibility seems to be the most important thing here..


sqlite> create table t(a text, b text);
sqlite> insert into t values('one', 'two');
sqlite> insert into t values('three', 'four');
sqlite> select a, b from t union select b,a from t;
a   b
--  --
fourthree
one two
three   four
two one
sqlite> select a, b from t union select b,a from t order by a,b;
a   b
--  --
fourthree
one two
three   four
two one
sqlite> select a, b from t union select b,a from t order by b,a;
a   b
--  --
three   four
two one
fourthree
one two
sqlite>


So from this example seems like what we really need is to teach SQLite how
to count... four, one, three, two.. sheesh.  :-)

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]
 
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 12, 2007 6:05 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296

...

Otherwise, the  of the i-th column of TR is implementation 
dependent
and not equivalent to the  of any column, other than
itself, of any table referenced by any  contained in 
the SQL statement.

...

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Help with SQL syntax. Ticket #2296

2007-04-12 Thread Samuel R. Neff

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. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 12, 2007 3:42 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296

My understanding is:

  select a, b from t1
  union
  select b, a from t1

is equivalent to


  select a as a, b as b from t1
  union
  select b as a, a as b from t1

And therefore, the first sql statement controls the resulting column names,
and the order by applies to the column names (transitively) from the first
statement.

I'll find a reference in SQL89/SQL92 to support my understanding, but this
is how Oracle behaves. :)

--andy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Performance analysis of SQLite statements

2007-04-12 Thread Samuel R. Neff

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 different components of a single SQL
statement took relative to the whole statement.  Provide for more
fine-grained analysis than just comparing two SQL statements based on total
execution time.

Thanks,

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]
 
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 8:02 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Select columns & performance

...

Do a lot of benchmarking.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] storing the tables in separate files

2007-04-11 Thread Samuel R. Neff

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

---
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: Guilherme C. Hazan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 3:10 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] storing the tables in separate files

 Hi,
 
 From what i seen, sqlite stores all tables in a single file. Is there an
 easy way to change this behaviour and store the tables and indexes in
 separate files?
 
 thanks
 
 guich


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  1   2   >