Re: [sqlite] Encoding and Collation

[Jan Hudec]

On Sun, Sep 11, 2011 at 11:55:23 -0300, Antonio Maniero wrote:
> I doubt it that it would be ease to storing 8859 without string functions
> problems. The proper collation would be simple, of course, but probably I
> would need to re-implement all string functions too. Am I wrong?
> 
> I can use utf8 but for me SQLite won't be lite anymore without a simple
> utf8 implementation. Hopefully someone else could have a ready solution for
> collation, otherwise I will do my own implementation. It will never be
> correct, but it will be enough.

Actually your operating system probably provides the collation sequence and
binding it for sqlite is about 5 lines of C. The only problem is that it's
system specific, so it'd be quite hard to maintain in sqlite, which is
probably why it's not provided. But if your application is domain-specific
anyway (otherwise iso-8859-1 could never be good enough for you), it's quite
easy.

In Windows you'd use the 'CompareString' function (or something like that;
I am not looking at MSDN). You probably need to ask sqlite to give you UTF-16
strings (it will happily convert for you even when storing as UTF-8) and
select correct locale.

In Unix you should be able to use either 'strcoll' or 'wcscoll' (beware,
wchar_t is 32-bit in many unix compilers these days!), you just have to
figure out how to set the locale before calling it.

In either case the wrapping and registration with sqlite_create_collation_v2
is quite easy.

> Am I the only user that need a lite implementation of SQLite with case
> insensitive?

Well, maybe you are one of the only two or something like that.

Most users actually don't care about the collation sequence, because they
neither order by strings nor use like on strings (or only use them with
identifiers, that fit in ascii anyway). Most of the rest either wants the
collation to be correct in all cases (so they use ICU) or go with the
simplest solution (so they use ICU). The few that don't want ICU just define
the collation sequences themselves.

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

Re: [sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

[Jay A. Kreibich]

On Mon, Sep 12, 2011 at 06:56:50PM +0200, Stephan Beal scratched on the wall:
> On Mon, Sep 12, 2011 at 6:47 PM, Jay A. Kreibich  wrote:
> 
> > On Mon, Sep 12, 2011 at 12:29:56PM +0800, ?? scratched on the wall:
> > > is there any limit about the data size?
> >
> >   PRAGMA max_page_count should work on in-memory databases.
> 
> Isn't there also the limitation that the maximum db size == the max memory
> the system allocator can dole out, plus limitations imposed by the "bitness"
> of the platform? 

  Well, yes... you need some place to put the data, and 32-bit systems
  do have inherent limits on the amount of memory they can address...
  real, virtual, or otherwise.

> You can't (per my understanding) have a 10GB in-memory db
> on a 32-bit machine (regardless of how much RAM it has),

  Correct.

  Although you could have a 10GB anonymous database, which is kind of
  like an in-memory/on-disk hybrid.

> but you can have a 10GB on-storage db on that same machine.

  Assuming you have a storage device with at last 10 GB free, yes.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

[Stephan Beal]

On Mon, Sep 12, 2011 at 6:47 PM, Jay A. Kreibich  wrote:

> On Mon, Sep 12, 2011 at 12:29:56PM +0800, ?? scratched on the wall:
> > is there any limit about the data size?
>
>   PRAGMA max_page_count should work on in-memory databases.
>

Isn't there also the limitation that the maximum db size == the max memory
the system allocator can dole out, plus limitations imposed by the "bitness"
of the platform? You can't (per my understanding) have a 10GB in-memory db
on a 32-bit machine (regardless of how much RAM it has), but you can have a
10GB on-storage db on that same machine.

:-?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

[Jay A. Kreibich]

On Mon, Sep 12, 2011 at 12:29:56PM +0800, ?? scratched on the wall:
> Hi there,
> 
> I just have a question. If I am using JDBC driver to connect to sqlite using
> in-memory mode,
> is there any limit about the data size?

  PRAGMA max_page_count should work on in-memory databases.

  http://sqlite.org/pragma.html#pragma_max_page_count

  If you've ATTACHed the in-memory database, you'll need to issue the
  command like this:

PRAGMA .max_page_count = 

  Also note that this limits the page count, meaning the total size is
  dependent on the page size.  In-memory DBs live in the cache, which
  also has a small per-page overhead, so the total memory usage will be
  slightly more than (page_size * page_count).

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with a complicated join of two tables

[Igor Tandetnik]

On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote:


On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote:

Something like this:

select geo.id, min_age, max_age, age_bottom, age_top, name, color
from geo left join intervals i on i.id = (
select id from intervals
where age_bottom>=
(select age_bottom from intervals where name = geo.max_age)
and age_top<=
(select age_top from intervals where name = geo.min_age)
order by (age_bottom - age_top) limit 1
);




Thanks Igor. The above does work and produces the correct result. The
query speed, however, is pretty slow ~ 75 seconds. So, I created
indexes on intervals.name, geo.max_age, and geo.min_age, and that
brought the query time to ~ 11 seconds. Still too slow.


Indexes on geo.max_age and min_age are unlikely to help with this query 
(use EXPLAIN QUERY PLAN to see which indexes are actually used). An 
index on intervals.age_bottom might. So would an index on 
intervals.age_top (but not both at the same time).

--
Igor Tandetnik

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

Re: [sqlite] help with a complicated join of two tables

[Mr . Puneet Kishor]

On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote:

> Mr. Puneet Kishor  wrote:
 The table geo can also have rows with min_age = max_age. I want a result 
 set with geo.id, min_age, max_age, age_bottom, age_top,
 name, color like so:
 
 - every row should be for one and only one geo record. I have 39K rows in 
 "geo" table, so the result set should have 39K rows.
 
 - when min_age = max_age, list the corresponding intervals.name and color
 
 - when min_age != max_age, find the interval with the smallest different 
 between age_bottom and age_top that would span min_age
 and max_age of geo. In other words, the interval whose age_bottom is 
 bigger than the age_bottom of the max_age and whose age_top
 is smaller than the age_top of the min_age.
> 
> Something like this:
> 
> select geo.id, min_age, max_age, age_bottom, age_top, name, color
> from geo left join intervals i on i.id = (
>select id from intervals
>where age_bottom >=
>(select age_bottom from intervals where name = geo.max_age)
>and age_top <= 
>(select age_top from intervals where name = geo.min_age)
>order by (age_bottom - age_top) limit 1
> );
> 


Thanks Igor. The above does work and produces the correct result. The query 
speed, however, is pretty slow ~ 75 seconds. So, I created indexes on 
intervals.name, geo.max_age, and geo.min_age, and that brought the query time 
to ~ 11 seconds. Still too slow. So, I created an intermediate table to hold 
the results, and the speed is more satisfactory.

Many thanks again.

Puneet.

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Last error i would see is i have made an error with the assembly creation.
Is it possible for you joe, to send me your assemblies direct per email?
So I can exclude this error.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] splitting a line to produce several lines?

[Black, Michael (IS)]

My point is that this normalization works on ALL databases.  Ergo my reference 
to "SQL" and not "SQLite".

MySql for example has split support but it's still overkill to implement 
something like what you want.
http://kedar.nitty-witty.com/blog/mysql-stored-procedure-split-delimited-string-into-rows


All too often people smash all their data into one table and then find out 
things work better when normalized.  Less space, faster queries, 
easier/faster/more accurate search (e.g. you can't index your combined field).

So...normalized you can do:
select * from species where name = 'g%';
Which can be indexed.
With just one table you have to do:
select * from species where name = '%/g%';
Which is a full table lookup.
And then hope nobody puts a slash in their name for some reason which would 
mess up your search.

The general rule is...if you are repeating values you probably need to 
normalize.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jean-Denis MUYS [jdm...@kleegroup.com]
Sent: Monday, September 12, 2011 7:29 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] splitting a line to produce several lines?



On 12 sept. 2011, at 13:26, Black, Michael (IS) wrote:

Since SQL is designed to return rows I believe the answer is "no" since you're 
data is not stored in rows.

I suspected such a limitation. Thanks for the confirmation.
(though I question the justification: "SQL is designed to return rows" is 
precisely the reason why one could hope it could "return rows from the results 
of a [split] function").


You need to do what's referred to as "normalizing" your data.

That's one goal of the migration.
Back to programming.

JD

[…]

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org 
[sqlite-users-boun...@sqlite.org] on 
behalf of Jean-Denis MUYS [jdm...@kleegroup.com]
Sent: Monday, September 12, 2011 3:28 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] splitting a line to produce several lines?


I am migrating data from a database which has a table that describes items by 
"kinds". Instances of those "kinds" are stored in one column of this table, 
listing each instance's id, with all ids separated by a slash "/".

Of course, this is a very poor design. The target system has a proper table for 
instances, with a one-to-many relationship between kinds and instances.

My question is: is there a pure SQL way to split the instances string, and 
generate the instance lines from the instance list string? contrived example:


Table Kind:

id  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
9   shark   3
10  cod 3

(the instance id is not especially important. it could as well be the 
concatenation of the kind id/name and the instance name)

Of course, I could write a program to do that, but if I could do it in a few 
SQL statements, it would happen earlier…

Thanks,

Jean-Denis

___
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] TransactionScope ON CONFLICT

[Steffen Mangold]

Steffen Mangold wrote:

>
> The important thing is that there is only one SaveChanges call (which 
> attempts to commit pending changes to the underlying database).
>

Yes, i do so.

>
> My test case does attempt to add rows that conflict with data already present 
> in the sample database.
>

Ok, just like in my test.

>
> I assume that is done in a separate transaction block?
>

Yes, a different context instance without transaction block, just like you 
would do it normally. (context closed after insert)

>
> Also, did you try adding the manual opening of the connection in the previous 
> message I sent?  As follows:
>
> using (TransactionScope transaction = new TransactionScope()) {
>   context.Connection.Open(); // try adding this line.

Yes, i tried but no success, same result like before for me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> So you add all the 15 rows inside one transactionscope and with one
> context.SaveChanges() call?
>

Yes.

>
> Perhaps it makes a difference if the contains violation is inside the
> 15 rows you try to add (row 10 is incompatible with row 2 for example)
>

The important thing is that there is only one SaveChanges call (which
attempts to commit pending changes to the underlying database).

>
> If it is a contains violation with data already in DB.
>

My test case does attempt to add rows that conflict with data already
present in the sample database.

>
> For my test I add a single row to the DB just before I doing my
> transaction.
>

I assume that is done in a separate transaction block?

Also, did you try adding the manual opening of the connection in the
previous message I sent?  As follows:

using (TransactionScope transaction = new TransactionScope())
{
context.Connection.Open(); // try adding this line.

--
Joe Mistachkin

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Joe Mistachkin wrote:

>
> Are you sure the application is loading the new DLLs and not some stale DLLs 
> leftover from before?
>

Yes, i look with Visual Studio in the "Loading Modules" window. Path and 
version are correct (1.7.5) before I had installed only some 1.6 runtimes.
Hopes I do all right with compilation (thank again for your step-by-step help)

>
> Yes, my test case works properly (the first time).  The second time, all the 
> rows have already been added and the test case fails, which is still the 
> expected behavior.
>

Ok, now its getting really strange.

>
> The C# code for the test case is here (in the EFTransactionTest method):
>
> http://system.data.sqlite.org/index.html/finfo?name=testlinq/Program.cs
>
> One thing you may notice about the test case is that I try to add a total of
> 15 rows to
> the database.  The first five rows are added, the second five rows are NOT 
> added (because they would violate the PRIMARY KEY constraint), and the final 
> five rows are also added.
>
> The test case verifies that all 10 rows that should be added are in fact 
> added.  It also verifies that the appropriate exception is raised for the 
> PRIMARY KEY constraint violation.

So you add all the 15 rows inside one transactionscope and with one 
context.SaveChanges() call?
Perhaps it makes a difference if the contains violation is inside the 15 rows 
you try to add (row 10 is incompatible with row 2 for example) or
If it is a contains violation with data already in DB. For my test I add a 
single row to the DB just before I doing my transaction.

--
Joe Mistachkin

___
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] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> I try this now but with no success.
>

Are you sure the application is loading the new DLLs and not some stale DLLs
leftover
from before?

>
> Did it work at your test case?
>

Yes, my test case works properly (the first time).  The second time, all the
rows have
already been added and the test case fails, which is still the expected
behavior.

>
> If yes, can you send me your testcode, so I can look for difference to my?
>

The C# code for the test case is here (in the EFTransactionTest method):

http://system.data.sqlite.org/index.html/finfo?name=testlinq/Program.cs

One thing you may notice about the test case is that I try to add a total of
15 rows to
the database.  The first five rows are added, the second five rows are NOT
added (because
they would violate the PRIMARY KEY constraint), and the final five rows are
also added.

The test case verifies that all 10 rows that should be added are in fact
added.  It also
verifies that the appropriate exception is raised for the PRIMARY KEY
constraint violation.

--
Joe Mistachkin

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Igor Tandetnik wrotes:

>I'm not sure I understand this statement. What kind of "influence" do you want 
>to exert?

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Joe Mistachkin wrote:

>The test case I added for this issue is remarkably similar to your code, 
>except it uses a different schema (the Northwind sample database) and does not 
>re-throw the exception in the catch block (it simply writes it to the console 
>instead).
>The one
>modification I would make to your code is explicitly opening the connection 
>just inside the TransactionScope using block (like my test case does).  
>Without that, the .NET Framework may try to open more than one connection to 
>the underlying database, which could >cause some problems (it did for me).  
>For example, try this
>change:

I try this now but with no success. Did it work at your test case? If yes, can 
you send me your testcode, so I can look for difference to my?


>Also, keep in mind that only the rows of data that are not causing any error 
>will be added to the database.  Any rows that fail constraints will not be 
>added.

Yes this is ok, I don't want to destroy the Db. ;)
 
I just want the result like in the SQLite doku, like:

Dataset 1 (Success in DB)
Dataset 2 (Success in DB)
Dataset 3 (Failure not in DB)
Dataset 4 (Success in DB)
Dataset 5 (Success in DB)

But for now I only get:

Dataset 1 (Success in DB)
Dataset 2 (Success in DB)
Dataset 3 (Failure not in DB)
Dataset 4 (never happens)
Dataset 5 (never happens)

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

Re: [sqlite] help to get the value of file change counter

[Simon Slavin]

On 12 Sep 2011, at 1:35pm, Richard Hipp wrote:

> On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik  wrote:
> 
>> Simon Slavin  wrote:
>>> 
>>> 
>>> int sqlite3_total_changes(sqlite3*);
>>> 
>>> My understanding (which might be wrong) is that this count includes all
>>> changes made by all connections to that database: not
>>> only changes made using your connection but also chances made by another
>>> computer, process, or thread.
>> 
>> I'm 99% sure your understanding is wrong [snip]
> 
> Igor is right.  The sqlite3_total_changes() function only reports the number
> of rows that have been changed by the same database connection that issued
> the sqlite3_total_changes() call.

Okay,  I find that page in the documentation ambiguous then.  Could that 
sentence, or something like it, be added for clarity ?

> There is no API for accessing the database change counter.  But you can read
> it yourself by looking at bytes 24-27 of the database file.

How hard would it be to implement this safely using sqlite3's own filehandle to 
the database file ?  I know we're meant to treat sqlite3* as a black box but 
could it be done relatively safely, with a warning that it might fail under 
certain weird conditions ?  Would one use sqlite3_file_control() ?

Alternatively, suppose one was doing a lot of in-memory caching for a SQLite 
database but didn't want to block other apps from accessing it.  Is there a 
clean way to say "Has anyone been messing with this but me ?" ?

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

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> Hm... but the exception i get is an SQLite constraint exception. This means 
> that
> The error occurs at the moment where SQLite provider try to write data to the 
> DB.

Which is inside SaveChanges call.

> So I cannot have influence to this loop that you mean.

I'm not sure I understand this statement. What kind of "influence" do you want 
to exert?
-- 
Igor Tandetnik

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Hm... but the exception i get is an SQLite constraint exception. This means that
The error occurs at the moment where SQLite provider try to write data to the 
DB.
So I cannot have influence to this loop that you mean.


Igor Tandetnik wrote:

>Inside this call, a loop runs, with one INSERT statement executed for each 
>prior AddObject call.
>And here's where you commit the transaction, regardless of whether or not it 
>completed successfully.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] splitting a line to produce several lines?

[Max Vlasov]

On Mon, Sep 12, 2011 at 12:28 PM, Jean-Denis MUYS  wrote:
>
> My question is: is there a pure SQL way to split the instances string, and 
> generate the instance lines from the instance list string? contrived example:
>

This kind of questions appear from time to time (including my brain :)
and there are tricky solutions like for example using specially
implemented virtual tables (read my recent post here:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html)

Thanks to your example I added delimiter support in the virtual table
initialization and tested the concept on a real data, seems like it
works. The query in this case looks like this

SELECT TestTable.Id, TestTable.Name, cmlist.value FROM TestTable LEFT
JOIN cmlist On Instances=commalist

For those who familiar with sqlite virtual tables implementation this
concept is probably an hour to implement. If you're on windows and
ready to test your conversion with my tool, consider writing to me
directly

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

Re: [sqlite] Contrib: extensions.-

[Freddy López]

Thank you Kevin.

:-)

On Mon, Sep 12, 2011 at 02:19, Kevin Benson wrote:

> On Mon, Sep 12, 2011 at 12:50 AM, Freddy López  >wrote:
>
> > Hello.
> >
> > I have been having problems with page www.sqlite.org/contrib and I need
> > file
> > extension-functions.c (50.96 KB) contributed by Liam Healy... There exist
> > some alternative place where can I download it?
> >
> > Thanks.
> >
> Here's a recent pastebin:
>
> http://pastebin.com/QF6WbrW5
>
>
> other possibles:
>
>
> http://sqlite.xerial.googlecode.com/hg/sqlitejdbc/ext/extension-functions.c?r=6a7956dc569c393bb84303a5f5f4900cb0eaef84
>
>
> http://my-test-apps.googlecode.com/svn-history/r745/trunk/wxSQLite3/sqlite3/extensionfunctions.c
>
>
> http://www.xerial.org/trac/Xerial/browser/XerialJ/trunk/sqlite-jdbc/sqlitejdbc/ext/extension-functions.c?rev=3588
>
> --
>   --
>  --
> --ô¿ô--
>K e V i N
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
«But Gwindor answered: 'The doom lies in yourself, not in your name.'»

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

Re: [sqlite] TransactionScope ON CONFLICT

[Joe Mistachkin]

Steffen Mangold wrote:
>
> No i think i can exclude this as the problem.
> Because my code goes like this:
>

The test case I added for this issue is remarkably similar to your code,
except it
uses a different schema (the Northwind sample database) and does not
re-throw the
exception in the catch block (it simply writes it to the console instead).
The one
modification I would make to your code is explicitly opening the connection
just
inside the TransactionScope using block (like my test case does).  Without
that,
the .NET Framework may try to open more than one connection to the
underlying
database, which could cause some problems (it did for me).  For example, try
this
change:

using (TransactionScope transaction = new TransactionScope())   //begin
transaction
{
context.Connection.Open();

Also, keep in mind that only the rows of data that are not causing any error
will be
added to the database.  Any rows that fail constraints will not be added.

--
Joe Mistachkin

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

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> No i think i can exclude this as the problem.
> Because my code goes like this:
>
> try
>{
> context.SaveChanges(); // save changes with transaction !exception raised here

Inside this call, a loop runs, with one INSERT statement executed for each 
prior AddObject call.

> }
>  catch (Exception)
>{
>   //throw;
>}
>finally
>   {
>transaction.Complete(); // end transaction
>context.AcceptAllChanges();

And here's where you commit the transaction, regardless of whether or not it 
completed successfully.
-- 
Igor Tandetnik

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

No i think i can exclude this as the problem.
Because my code goes like this:

using (dataDBEntities context = new dataDBEntities())   
//create context
{
using (TransactionScope transaction = new TransactionScope())   //begin 
transaction
{
foreach (object data in objectIWantToAdd)   
// add all object to context
{
context.AddObject("DataObjects", data); // !no 
exceptions raised here
}

try
{
context.SaveChanges();  // save 
changes with transaction !exception raised here
}
catch (Exception)
{
 //throw;
}
finally
{
transaction.Complete(); 
// end transaction
context.AcceptAllChanges();
}
}
}

So there is no loop to break by an exception. :(


Igor Tandetnik wrotes:

> Is this perhaps because your loop is terminated by an exception, and never 
> gets around to actually insert the remaining rows?

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

Re: [sqlite] help to get the value of file change counter

[Richard Hipp]

On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik  wrote:

> Simon Slavin  wrote:
> > 
> >
> > int sqlite3_total_changes(sqlite3*);
> >
> > My understanding (which might be wrong) is that this count includes all
> changes made by all connections to that database: not
> > only changes made using your connection but also chances made by another
> computer, process, or thread.
>
> I'm 99% sure your understanding is wrong, and this function only reports
> changes made by the connection passed in as a parameter. There is simply no
> machinery in SQLite that would have allowed one connection to indicate to
> another how many changes it has made.
>

Igor is right.  The sqlite3_total_changes() function only reports the number
of rows that have been changed by the same database connection that issued
the sqlite3_total_changes() call.

There is no API for accessing the database change counter.  But you can read
it yourself by looking at bytes 24-27 of the database file.

Caution: if you open and close the database file on unix using some
mechanism other than SQLite and if you have other threads using SQLite at
the same time, then you will likely corrupt the database file. This is a
design bug in posix advisory locking which is impossible for us to work
around.  See paragraph 2.2 of http://www.sqlite.org/howtocorrupt.html for
further information.



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



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

Re: [sqlite] splitting a line to produce several lines?

[Jean-Denis MUYS]

On 12 sept. 2011, at 13:26, Black, Michael (IS) wrote:

Since SQL is designed to return rows I believe the answer is "no" since you're 
data is not stored in rows.

I suspected such a limitation. Thanks for the confirmation.
(though I question the justification: "SQL is designed to return rows" is 
precisely the reason why one could hope it could "return rows from the results 
of a [split] function").


You need to do what's referred to as "normalizing" your data.

That's one goal of the migration.
Back to programming.

JD

[…]

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org 
[sqlite-users-boun...@sqlite.org] on 
behalf of Jean-Denis MUYS [jdm...@kleegroup.com]
Sent: Monday, September 12, 2011 3:28 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] splitting a line to produce several lines?


I am migrating data from a database which has a table that describes items by 
"kinds". Instances of those "kinds" are stored in one column of this table, 
listing each instance's id, with all ids separated by a slash "/".

Of course, this is a very poor design. The target system has a proper table for 
instances, with a one-to-many relationship between kinds and instances.

My question is: is there a pure SQL way to split the instances string, and 
generate the instance lines from the instance list string? contrived example:


Table Kind:

id  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
9   shark   3
10  cod 3

(the instance id is not especially important. it could as well be the 
concatenation of the kind id/name and the instance name)

Of course, I could write a program to do that, but if I could do it in a few 
SQL statements, it would happen earlier…

Thanks,

Jean-Denis

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

Re: [sqlite] TransactionScope ON CONFLICT

[Igor Tandetnik]

Steffen Mangold  wrote:
> Ok I'm tested it now in a simple test environment.
> 
> Conditions:
> 1 table with two columns "TimeStamp" (PK) and "SensorID" (simple value).
> 
> First I add a row with "TimeStamp" "2011-01-01 01:05:00" (Success)
> Then I doing a Transaction with 10 "TimeStamps" from "2011-01-01 01:00:00" to 
> "2011-01-01 01:10:00". (Failure)
> 
> An Exception show in Debug Output Window
> "SQLite error (19): abort at 21 in [INSERT INTO [SensorData]([SensorID], 
> [TimeStamp])
> VALUES (@p0, @p1);]: columns TimeStamp are not unique"
> 
> In the data base are now 6 rows, that mean all after the failing insert are 
> not executed be the transaction.

Is this perhaps because your loop is terminated by an exception, and never gets 
around to actually insert the remaining rows?
-- 
Igor Tandetnik

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

[sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

[鲍少明]

Hi there,

I just have a question. If I am using JDBC driver to connect to sqlite using
in-memory mode,
is there any limit about the data size?

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

Re: [sqlite] help to get the value of file change counter

[Igor Tandetnik]

Simon Slavin  wrote:
> 
> 
> int sqlite3_total_changes(sqlite3*);
> 
> My understanding (which might be wrong) is that this count includes all 
> changes made by all connections to that database: not
> only changes made using your connection but also chances made by another 
> computer, process, or thread. 

I'm 99% sure your understanding is wrong, and this function only reports 
changes made by the connection passed in as a parameter. There is simply no 
machinery in SQLite that would have allowed one connection to indicate to 
another how many changes it has made.
-- 
Igor Tandetnik

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

Re: [sqlite] help with a complicated join of two tables

[Igor Tandetnik]

Mr. Puneet Kishor  wrote:
>>> The table geo can also have rows with min_age = max_age. I want a result 
>>> set with geo.id, min_age, max_age, age_bottom, age_top,
>>> name, color like so:
>>> 
>>> - every row should be for one and only one geo record. I have 39K rows in 
>>> "geo" table, so the result set should have 39K rows.
>>> 
>>> - when min_age = max_age, list the corresponding intervals.name and color
>>> 
>>> - when min_age != max_age, find the interval with the smallest different 
>>> between age_bottom and age_top that would span min_age
>>> and max_age of geo. In other words, the interval whose age_bottom is bigger 
>>> than the age_bottom of the max_age and whose age_top
>>> is smaller than the age_top of the min_age.

Something like this:

select geo.id, min_age, max_age, age_bottom, age_top, name, color
from geo left join intervals i on i.id = (
select id from intervals
where age_bottom >=
(select age_bottom from intervals where name = geo.max_age)
and age_top <= 
(select age_top from intervals where name = geo.min_age)
order by (age_bottom - age_top) limit 1
);

-- 
Igor Tandetnik

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

Re: [sqlite] splitting a line to produce several lines?

[Black, Michael (IS)]

Since SQL is designed to return rows I believe the answer is "no" since you're 
data is not stored in rows.

You need to do what's referred to as "normalizing" your data.
If you normalize the answer becomes a natural query.

create table kind (kind_id int primary key, name text);
insert into kind values(1,'Bird');
insert into kind values(2,'Mammal');
insert into kind values(3,'Fish');
create table species (species_id int primary key, name text,kind_id int);
insert into species values(1,'eagle',(select kind_id from kind where 
name='Bird'));
insert into species values(2,'seagull',(select kind_id from kind where 
name='Bird'));
insert into species values(3,'hen',(select kind_id from kind where 
name='Bird'));
insert into species values(4,'dog',(select kind_id from kind where 
name='Mammal'));
insert into species values(5,'cat',(select kind_id from kind where 
name='Mammal'));
insert into species values(6,'cow',(select kind_id from kind where 
name='Mammal'));
insert into species values(7,'rabbit',(select kind_id from kind where 
name='Mammal'));
insert into species values(8,'tuna',(select kind_id from kind where 
name='Fish'));
insert into species values(9,'shark',(select kind_id from kind where 
name='Fish'));
insert into species values(10,'cod',(select kind_id from kind where 
name='Fish'));
sqlite> select species_id,species.name,kind.name from species join kind on 
species.kind_id=kind.kind_id;
1|eagle|Bird
2|seagull|Bird
3|hen|Bird
4|dog|Mammal
5|cat|Mammal
6|cow|Mammal
7|rabbit|Mammal
8|tuna|Fish
9|shark|Fish
10|cod|Fish

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jean-Denis MUYS [jdm...@kleegroup.com]
Sent: Monday, September 12, 2011 3:28 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] splitting a line to produce several lines?


I am migrating data from a database which has a table that describes items by 
"kinds". Instances of those "kinds" are stored in one column of this table, 
listing each instance's id, with all ids separated by a slash "/".

Of course, this is a very poor design. The target system has a proper table for 
instances, with a one-to-many relationship between kinds and instances.

My question is: is there a pure SQL way to split the instances string, and 
generate the instance lines from the instance list string? contrived example:


Table Kind:

id  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
9   shark   3
10  cod 3

(the instance id is not especially important. it could as well be the 
concatenation of the kind id/name and the instance name)

Of course, I could write a program to do that, but if I could do it in a few 
SQL statements, it would happen earlier…

Thanks,

Jean-Denis

___
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] TransactionScope ON CONFLICT

[Steffen Mangold]

Ok I'm tested it now in a simple test environment.

Conditions: 
1 table with two columns "TimeStamp" (PK) and "SensorID" (simple value).

First I add a row with "TimeStamp" "2011-01-01 01:05:00" (Success)
Then I doing a Transaction with 10 "TimeStamps" from "2011-01-01 01:00:00" to 
"2011-01-01 01:10:00". (Failure)

An Exception show in Debug Output Window 
"SQLite error (19): abort at 21 in [INSERT INTO [SensorData]([SensorID], 
[TimeStamp])
 VALUES (@p0, @p1);]: columns TimeStamp are not unique"

In the data base are now 6 rows, that mean all after the failing insert are not 
executed be the transaction.


So I think if I'm doing all right with checking out your fix, it is not working 
at all. :(


Steffen Mangold wrote:

>Sorry, I make my test with wrong conditions it looks like It not works correct 
>at all.
>Please give me a sec to do some more tests and ignore my last message *shame*.
>I come back here after testing. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Ok im not clear if im doning it right. I follow your instructions and get a 
compliable version.
But how to get your branch? What I'm doing now is to download the zip under 
"Other links: Zip archive" under
http://system.data.sqlite.org/index.html/info/42af4d17a5 and copy it over the 
version from this fossil rep.
Is this correct?

Joe Mistachkin wrote:

>1. Download the Fossil binary for your platform (e.g. Windows):

>   http://www.fossil-scm.org/download/fossil-w32-20110901182519.zip

>2. Extract the ZIP file to some directory along your PATH.

>3. Open a "Command Prompt" window.

>4. Create a directory to hold the source tree, for example:

>   mkdir C:\dev\sqlite\dotnet

>5. Change to the directory created in step #4, for example:

chdir /D C:\dev\sqlite\dotnet

>6. Execute the following command to clone the repository:

>   fossil clone http://system.data.sqlite.org/ dotnet.fossil

>7. Execute the following command to open the repository:

>   fossil open dotnet.fossil

>8. Execute the following command to change to the build directory:

>   chdir Setup

>9. Execute the following command to build the managed project(s):

>   build.bat ReleaseManagedOnly Win32

>10. Execute the following command to build the native project(s) for the x86 
>processor architecture:

>   build.bat ReleaseNativeOnly Win32

>11. Now, all the binaries should be in the following directory:

>   C:\dev\sqlite\dotnet\bin\2010\Release\bin

>12. Copy the "SQLite.Interop.dll", "System.Data.SQLite.dll", and 
>"System.Data.SQLite.Linq.dll" files into your application directory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Sorry, I make my test with wrong conditions it looks like It not works correct 
at all.
Please give me a sec to do some more tests and ignore my last message *shame*.
I come back here after testing.

Steffen Mangold wrote:

>Nice job! :) It works now like expected. I have tested it with use of this 
>patch http://system.data.sqlite.org/index.html/info/42af4d17a5 .
>I also wrote a comment to the ticket [ccfa69fc32]. Thank you, great job.

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

Re: [sqlite] TransactionScope ON CONFLICT

[Steffen Mangold]

Nice job! :) It works now like expected. I have tested it with
use of this patch http://system.data.sqlite.org/index.html/info/42af4d17a5 .
I also wrote a comment to the ticket [ccfa69fc32]. Thank you, great job.

Steffen

Joe Mistachkin wrote:

>I believe that I've found and fixed an issue in the SQLiteConnection class 
>that could be responsible for the errant behavior you are seeing.

>The check-in is here (on the "bug-ccfa69fc32" branch):

>http://system.data.sqlite.org/index.html/ci/42af4d17a5

>Would it be possible for you to update your local System.Data.SQLite and see 
>if this fix corrects the behavior you are seeing?

>The fix is located on the "bug-ccfa69fc32" branch in Fossil.  If you need 
>information on checking out or building the System.Data.SQLite source code, 
>please let me know and I will send complete step-by-step instructions.

--
Joe Mistachkin

___
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] Multi-column unique constraint in SQLite

[liviodl]

Simon Slavin-3 wrote:
> 
> 
> I agree with Jay.  What's happening is this:
> 
> New row assembled.
> Attempt to INSERT new row (first attempt)
> INSERT fails UNIQUE contraint.
> Notes that it was an INSERT OR REPLACE, therefore ...
> DELETE original row.
> Attempt to INSERT new row (second attempt)
> INSERT works.
> 
> It might go around the loop more than once but it's something like that.
> 

Thank you all, you're definitely right... there was another primary key,
even if I was quite sure that it wasn't configured in that way. Removing it,
everything worked fine.

Thanks again,

Livio
-- 
View this message in context: 
http://old.nabble.com/Multi-column-unique-constraint-in-SQLite-tp32441748p32446094.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] TransactionScope ON CONFLICT (oops, missed a step)...

[Joe Mistachkin]

Oops, forgot to update to the bug-ccfa69fc32 branch, inserted new step #8
below to correct this.

Steffen Mangold wrote:
> 
> It would really help me if you send me your step by step instructions.
> I had some experience with SVN, but it will help for building.
> 
 
1. Download the Fossil binary for your platform (e.g. Windows):

http://www.fossil-scm.org/download/fossil-w32-20110901182519.zip

2. Extract the ZIP file to some directory along your PATH.

3. Open a "Command Prompt" window.

4. Create a directory to hold the source tree, for example:

mkdir C:\dev\sqlite\dotnet

5. Change to the directory created in step #4, for example:

chdir /D C:\dev\sqlite\dotnet

6. Execute the following command to clone the repository:

fossil clone http://system.data.sqlite.org/ dotnet.fossil

7. Execute the following command to open the repository:

fossil open dotnet.fossil

8. Execute the following command to access the bug-ccfa69fc32 branch:

fossil update bug-ccfa69fc32

9. Execute the following command to change to the build directory:

chdir Setup

10. Execute the following command to build the managed project(s):

build.bat ReleaseManagedOnly Win32

12. Execute the following command to build the native project(s)
for the x86 processor architecture:

build.bat ReleaseNativeOnly Win32

13. Now, all the binaries should be in the following directory:

C:\dev\sqlite\dotnet\bin\2010\Release\bin

14. Copy the "SQLite.Interop.dll", "System.Data.SQLite.dll", and
"System.Data.SQLite.Linq.dll" files into your application directory.

--
Joe Mistachkin

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

[sqlite] splitting a line to produce several lines?

[Jean-Denis MUYS]

I am migrating data from a database which has a table that describes items by 
"kinds". Instances of those "kinds" are stored in one column of this table, 
listing each instance's id, with all ids separated by a slash "/".

Of course, this is a very poor design. The target system has a proper table for 
instances, with a one-to-many relationship between kinds and instances.

My question is: is there a pure SQL way to split the instances string, and 
generate the instance lines from the instance list string? contrived example:


Table Kind:

id  nameinstances
1   Bird'eagle/seagull/hen'
2   Mammal  'dog/cat/cow/rabbit'
3   Fish'tuna/shark/cod'

Possible outcome:

Table Instances:

id  namekind_id
1   eagle   1
2   seagull 1
3   hen 1
4   dog 2
5   cat 2
6   cow 2
7   rabbit  2
8   tuna3
9   shark   3
10  cod 3

(the instance id is not especially important. it could as well be the 
concatenation of the kind id/name and the instance name)

Of course, I could write a program to do that, but if I could do it in a few 
SQL statements, it would happen earlier…

Thanks,

Jean-Denis

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

Re: [sqlite] help to get the value of file change counter

[Akash Agrawal]

this is very helpful for me

thank  you
-- 
Regards
Akash Agrawal

On Mon, Sep 12, 2011 at 12:39 PM, Simon Slavin  wrote:

>
> On 12 Sep 2011, at 5:08am, Akash Agrawal wrote:
>
> > I'd like to be able to tell whether a SQLite database file has been
> updated
> > in any way. How would I go about implementing that?
> >
> > I find about some file change counter but how i will get the value of
> file
> > change counter through C program
>
> 
>
> int sqlite3_total_changes(sqlite3*);
>
> My understanding (which might be wrong) is that this count includes all
> changes made by all connections to that database: not only changes made
> using your connection but also chances made by another computer, process, or
> thread.
>
> Actually it might be worth clarifying the text on that page to state that
> explicitly.
>
> Simon.
> ___
> 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] help to get the value of file change counter

[Simon Slavin]

On 12 Sep 2011, at 5:08am, Akash Agrawal wrote:

> I'd like to be able to tell whether a SQLite database file has been updated
> in any way. How would I go about implementing that?
> 
> I find about some file change counter but how i will get the value of file
> change counter through C program



int sqlite3_total_changes(sqlite3*);

My understanding (which might be wrong) is that this count includes all changes 
made by all connections to that database: not only changes made using your 
connection but also chances made by another computer, process, or thread.

Actually it might be worth clarifying the text on that page to state that 
explicitly.

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

Re: [sqlite] Contrib: extensions.-

[Kevin Benson]

On Mon, Sep 12, 2011 at 12:50 AM, Freddy López wrote:

> Hello.
>
> I have been having problems with page www.sqlite.org/contrib and I need
> file
> extension-functions.c (50.96 KB) contributed by Liam Healy... There exist
> some alternative place where can I download it?
>
> Thanks.
>
Here's a recent pastebin:

http://pastebin.com/QF6WbrW5


other possibles:

http://sqlite.xerial.googlecode.com/hg/sqlitejdbc/ext/extension-functions.c?r=6a7956dc569c393bb84303a5f5f4900cb0eaef84

http://my-test-apps.googlecode.com/svn-history/r745/trunk/wxSQLite3/sqlite3/extensionfunctions.c

http://www.xerial.org/trac/Xerial/browser/XerialJ/trunk/sqlite-jdbc/sqlitejdbc/ext/extension-functions.c?rev=3588

--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users