Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[BareFeet]

Hi all,

On Mon, Oct 26, 2009 at 05:28:50PM -0400, Rob Sciuk scratched on the  
wall:
>
> I've just compiled the latest (3.6.19) with the
>
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.


This intrigues me. The functions that this enables looks very useful.

Please forgive my obvious ignorance here, but I'd appreciate some  
insight:

1. Why aren't these functions enabled by default?

2. Thus far I've been linking my Objective-C (Mac OS X) project  
against the operating system's built in libsqlite3.0.dylib framework.  
After downloading the 3.6.19 source, what steps do I need to follow to  
enable DSQLITE_ENABLE_COLUMN_METADATA, compile and link my XCode  
project against it?

Thanks,
Tom
BareFeet

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

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

On Mon, 26 Oct 2009, D. Richard Hipp wrote:

> sqlite3_column_table_name() (and all of the other METADATA functions) only 
> work on table columns, not on functions.  The documentation says as much, 
> though perhaps it could be worded more directly.  I'll make a note to clarify 
> the documentation.
>
>
> D. Richard Hipp
> d...@hwaci.com

I understand, and I must have missed the relevant documentation.  I'm 
wondering, though, there should be a reliable way to return a list of the 
affected tables from a prepared statement, no?

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

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

On Mon, 26 Oct 2009, Jay A. Kreibich wrote:

>  The documentation on this is fairly clear:
>
>  http://sqlite.org/c3ref/column_database_name.html
>
>  If the Nth column returned by the statement is an expression or
>  subquery and is not a column value, then all of these functions
>  return NULL. These routine might also return NULL if a memory
>  allocation error occurs. Otherwise, they return the name of the
>  attached database, table and column that query result column was
>  extracted from.

Hmm, fair enough.

>
>  Basically, in order for these functions to work, the returned column
>  needs to be a raw, unaltered, column directly from a specific table.
>  Any kind of expression (including aggregate functions) means the
>  data values in that result did not come directly from a specific
>  table column, and therefore these functions return NULL.
>
>  If you need a more general way to get the name of any column in any
>  query, you most likely want sqlite3_column_name().
>
>   -j

I actually need a general way to get the TABLE name from a query ... hmmm 
... back to the documentation ...

Thanks for your very kind reply.

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

Re: [sqlite] Conditional JOIN

[Jay A. Kreibich]

On Mon, Oct 26, 2009 at 05:32:27PM -0700, Peter Haworth scratched on the wall:
> Looking for a way to implement the following situation
> 
> I need to select entries form TableA and TableB.  The join needs to  
> happen using two separate fields.  One of this fields can be used in  
> the normal way but the other filed needs some special logic
> 
> The normal join field is ProdID and the unusual one is PriceTable.  If  
> there are no entries in Table B for the values of ProdID and  
> ProceTable in the TableA entry then the join must happen using the  
> value TableA.ProdID and a constant value "STANDARD" to join the  
> PriceTable entries in TableB to TableA
> 
> Here's an example
> 
> TableA.ProdID TableA.PriceTable   TableB.ProdID   Tableb.PriceTable
> 1 SPECIAL 1   SPECIAL
> 1 SPECIAL 1   SPECIAL
> 1 STANDARD1   STANDARD
> 2 SPECIAL 2   STANDARD
> 2 SPECIAL 2   STANDARD
> 

  You appear to have duplicate rows in your example, but here are two
  different ideas.  We'll start with this:

sqlite> CREATE TABLE TableA ( ProdID, PriceTable );
sqlite> CREATE TABLE TableB ( ProdID, PriceTable );
sqlite> 
sqlite> INSERT INTO TableA VALUES ( 1, 'STANDARD' );
sqlite> INSERT INTO TableA VALUES ( 1, 'SPECIAL' );
sqlite> INSERT INTO TableA VALUES ( 2, 'SPECIAL' );
sqlite> 
sqlite> INSERT INTO TableB VALUES ( 1, 'STANDARD' );
sqlite> INSERT INTO TableB VALUES ( 1, 'SPECIAL' );
sqlite> INSERT INTO TableB VALUES ( 2, 'STANDARD' );

  First idea:

sqlite> SELECT 
   ...>A1.ProdID,
   ...>A1.PriceTable, 
   ...>coalesce( B1.ProdID, B2.ProdID ),
   ...>coalesce( B1.PriceTable, B2.PriceTable )
   ...> FROM TableA AS A1
   ...> LEFT JOIN TableB AS B1
   ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = B1.PriceTable )
   ...> JOIN TableB AS B2 
   ...>ON ( A1.ProdID = B2.ProdID and 'STANDARD' = B2.PriceTable );
1|STANDARD|1|STANDARD
1|SPECIAL|1|SPECIAL
2|SPECIAL|2|STANDARD

  As I understand it, you're basically trying to match both a ProdID
  and a PriceTable, but if a PriceTable cannot be found, you want to
  use the 'STANDARD' PriceTable.

  This basically JOINs TableA to TableB twice, one with an exact match
  and one with a 'STANDARD' match.  The first match (A1/B1) is done as
  a LEFT OUTER JOIN, so any mis-matched rows are matched to NULL on the
  B1 side.  The second match (A1/B2) is done as a standard INNER JOIN
  and assumes there will always be a 'STANDARD' row in TableB for every
  possible ProdID in TableA.
  
  We then use the coalesce() function to return either the valid
  exact-matched values from B1.  If those are NULL, we return the
  'STANDARD' match rows from B2.

  Here is another apprach that uses a CASE statement:


sqlite> SELECT A1.*, B2.*
   ...> FROM TableA AS A1
   ...> LEFT JOIN TableB AS B1
   ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = B1.PriceTable)
   ...> JOIN TableB AS B2
   ...>ON ( A1.ProdID = B2.ProdID AND B2.PriceTable = (
   ...>CASE B1.PriceTable 
   ...>WHEN B1.PriceTable THEN
   ...>   A1.PriceTable
   ...>ELSE 
   ...>   'STANDARD'
   ...>END ) );
1|STANDARD|1|STANDARD
1|SPECIAL|1|SPECIAL
2|SPECIAL|2|STANDARD

  This is the same basic idea, in that we join TableA to TableB twice.
  The difference is that we only want the results of the second join
  (T1/B2) where we join against A1.PriceTable if we know we got a valid
  match the first time, or 'STANDARD' if we did not.  The key to making
  that work is that ( B1.PriceTable = B1.PriceTable ) will be FALSE
  (and return the ELSE value) if B1.PriceTable is NULL.




  I'm sure there are a few other ways you could factor this problem
  using a double-join, but that's one baisc approach.  The heart of the
  issue is that there is no way to test if a row has a match or not
  within a single JOIN.  You must do the LEFT JOIN to see if there was a
  match or not.  Then, based off the information of that JOIN, you can do
  the JOIN again and decide to take the values from the first match
  (if they're valid) or re-try with other values if they are not.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Conditional JOIN

[Darren Duncan]

You should be able to do what you want with a single SELECT.

You are on the right track with trying to use UNION and EXCEPT.  What you are 
trying to do can be accomplished with a generalization of an outer join.

Try something like this pseudo-code, which uses common-table-expressions for 
readability and to cut redundancy:

   with
   MatchA is (select TableA.* from TableA.* inner join TableB),
   NomatchA is (TableA except MatchA)
   (select * from MatchA inner join TableB)
   union
   (select * from NonmatchA inner join TableC)

... assuming TableC has the values you want to use instead of those from TableB 
for TableA records that don't match.

Or alter the above query to taste for the semantics you actually want.

-- Darren Duncan

Peter Haworth wrote:
> Looking for a way to implement the following situation
> 
> I need to select entries form TableA and TableB.  The join needs to  
> happen using two separate fields.  One of this fields can be used in  
> the normal way but the other filed needs some special logic
> 
> The normal join field is ProdID and the unusual one is PriceTable.  If  
> there are no entries in Table B for the values of ProdID and  
> ProceTable in the TableA entry then the join must happen using the  
> value TableA.ProdID and a constant value "STANDARD" to join the  
> PriceTable entries in TableB to TableA
> 
> Here's an example
> 
> TableA.ProdID TableA.PriceTable   TableB.ProdID   Tableb.PriceTable
> 1 SPECIAL 1   
> SPECIAL
> 1 SPECIAL 1   
> SPECIAL
> 1 STANDARD1   
> STANDARD
> 2 SPECIAL 2   
> STANDARD
> 2 SPECIAL 2   
> STANDARD
> 
> For ProdID 1, the entries in TableB wth ProdID 1 and PriceTable  
> SPECIAL should be selected.  The entry in TableB for ProdID 1 and  
> PriceTable STANDARD Should NOT be selected
> 
> For ProdID 2, the entries in TableB with ProdID 2 and PriceTable  
> STANDARD should be selected
> 
> I've tried JOIN with CASE, WHERE with CASE, compound SELECTs with  
> UNION, UNION ALL, INTERSECT and EXCEPT, but haven't managed to figure  
> out how to make this work.
> 
> Can this be done in a single SELECT?
> 
> Pete Haworth

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

[sqlite] Conditional JOIN

[Peter Haworth]

Looking for a way to implement the following situation

I need to select entries form TableA and TableB.  The join needs to  
happen using two separate fields.  One of this fields can be used in  
the normal way but the other filed needs some special logic

The normal join field is ProdID and the unusual one is PriceTable.  If  
there are no entries in Table B for the values of ProdID and  
ProceTable in the TableA entry then the join must happen using the  
value TableA.ProdID and a constant value "STANDARD" to join the  
PriceTable entries in TableB to TableA

Here's an example

TableA.ProdID   TableA.PriceTable   TableB.ProdID   Tableb.PriceTable
1   SPECIAL 1   
SPECIAL
1   SPECIAL 1   
SPECIAL
1   STANDARD1   
STANDARD
2   SPECIAL 2   
STANDARD
2   SPECIAL 2   
STANDARD

For ProdID 1, the entries in TableB wth ProdID 1 and PriceTable  
SPECIAL should be selected.  The entry in TableB for ProdID 1 and  
PriceTable STANDARD Should NOT be selected

For ProdID 2, the entries in TableB with ProdID 2 and PriceTable  
STANDARD should be selected

I've tried JOIN with CASE, WHERE with CASE, compound SELECTs with  
UNION, UNION ALL, INTERSECT and EXCEPT, but haven't managed to figure  
out how to make this work.

Can this be done in a single SELECT?

Pete Haworth














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

Re: [sqlite] sqlite3_release_memory() current status

[D. Richard Hipp]

On Oct 26, 2009, at 6:16 PM, D. Richard Hipp wrote:
>
> The memory management has undergone multiple rewrites in the previous
> two years, but as far as we know, sqlite3_release_memory() still works
> as advertised.  Please let us know if you find otherwise.

FWIW, with the latest changes we now have 100% branch test coverage  
when compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT.

>
> Though not related to your specific question, additional background
> information on the memory allocation subsystem in SQLite can be found
> at http://www.sqlite.org/malloc.html
>
> The code within the #if 0 was vestigial and has been removed.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Deadlock with two local instances

[Chris T]

Another odd thing is that when I call sqlite3_reset on the prepared
statement, it also returns SQLITE_BUSY.  Should I only reset the
statement when it has been executed successfully?

On Mon, Oct 26, 2009 at 2:40 PM, Chris T  wrote:
> I'm new to sqlite (and sql in general, actually) and came across
> something puzzling.
>
> I wrote a test program statically linked with the amalgamated sqlite
> code.  When I run a single instance, everything is fine.  When I start
> a second instance in the same directory they both deadlock.  Every
> call to sqlite3_step returns SQLITE_BUSY.
>
> The source code to my test program is attached.  It was written in
> Visual Studio, so feel free to remove the reference to windows.h and
> change the calls to Sleep( ) if you don't use Windows.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_release_memory() current status

[D. Richard Hipp]

On Oct 26, 2009, at 5:53 PM, Peter Kasting wrote:

> On Mon, Oct 26, 2009 at 2:48 PM, D. Richard Hipp   
> wrote:
>
>> On Oct 26, 2009, at 5:00 PM, Peter Kasting wrote:
>>> as
>>> far as
>>> I can tell the current code shouldn't cause any performance hit by
>>> enabling
>>> this flag.  There's no additional monitoring/tracking it causes; it
>>> just
>>> exposes a few functions to free memory.  Has the memory management
>>> implementation changed since the previous thread, or is this feature
>>> now
>>> broken?
>>
>> We run the "fulltest" test suite on a build of SQLite that include
>> SQLITE_ENABLE_MEMORY_MANAGEMENT prior to every release.  The
>> sqlite3_release_memory() interface works as documented, as far as we
>> know.  Are you seeing something that would suggest otherwise?
>>
>
> I think you're misunderstanding the thrust of my question.  I'm  
> saying that
> based on a glance at the current code, it doesn't look like past  
> comments
> about the perf impact of this flag would be accurate anymore.  I  
> want to
> know if my supposition is true.  Presumably, if it is true, it's  
> either
> because memory management has been rearchitected since that old  
> thread, or
> else because the feature has become broken.  I'm not saying I think  
> it's
> broken, simply that that would be one possible explanation for the
> discrepancy between what it looks like the code does and what it  
> sounds like
> it used to do.
>
> The other question in my email, regarding the reason for the #if 0  
> inside
> sqlite3_release_memory(), is eprhaps more interesting to me.

The memory management has undergone multiple rewrites in the previous  
two years, but as far as we know, sqlite3_release_memory() still works  
as advertised.  Please let us know if you find otherwise.

Though not related to your specific question, additional background  
information on the memory allocation subsystem in SQLite can be found  
at http://www.sqlite.org/malloc.html

The code within the #if 0 was vestigial and has been removed.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[D. Richard Hipp]

On Oct 26, 2009, at 5:28 PM, Rob Sciuk wrote:

>
> I've just compiled the latest (3.6.19) with the
>
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is  
> that
> when I use an aggregate function in a select, the table name is not  
> being
> returned from an sqlite3_column_table_name() is not returning the
> tablename as expected from a prepared statement, whereas if I actually
> request data from the table, it works fine:
>
> eg:
> If I prepare the statement "select * from q_user", q_user is  
> returned as
> expected from sqlite3_column_table_name().
>
> If I prepare the statement "select count(*) from q_user",
> sqlite3_column_table_name() returns NULL.

sqlite3_column_table_name() (and all of the other METADATA functions)  
only work on table columns, not on functions.  The documentation says  
as much, though perhaps it could be worded more directly.  I'll make a  
note to clarify the documentation.

>
> This is somewhat disconcerting, and seems repeatable.  Are aggregate
> functions handled differently than tuple data?
>
> Are there any other dependancies other than  
> SQLITE_ENABLE_COLUMN_METADATA?
>
> Is there anything I might have overlooked??
>
> Any ideas??
>
> Thanks in advance,
> Rob Sciuk
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Jay A. Kreibich]

On Mon, Oct 26, 2009 at 05:28:50PM -0400, Rob Sciuk scratched on the wall:
> 
> I've just compiled the latest (3.6.19) with the
> 
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is that 
> when I use an aggregate function in a select, the table name is not being 
> returned from an sqlite3_column_table_name() is not returning the 
> tablename as expected from a prepared statement, whereas if I actually 
> request data from the table, it works fine:
> 
> eg:
> If I prepare the statement "select * from q_user", q_user is returned as 
> expected from sqlite3_column_table_name().
> 
> If I prepare the statement "select count(*) from q_user", 
> sqlite3_column_table_name() returns NULL.
> 
> This is somewhat disconcerting, and seems repeatable.  Are aggregate 
> functions handled differently than tuple data?

  The documentation on this is fairly clear:

  http://sqlite.org/c3ref/column_database_name.html

  If the Nth column returned by the statement is an expression or
  subquery and is not a column value, then all of these functions
  return NULL. These routine might also return NULL if a memory
  allocation error occurs. Otherwise, they return the name of the
  attached database, table and column that query result column was
  extracted from.

  Basically, in order for these functions to work, the returned column
  needs to be a raw, unaltered, column directly from a specific table.
  Any kind of expression (including aggregate functions) means the
  data values in that result did not come directly from a specific
  table column, and therefore these functions return NULL.

  If you need a more general way to get the name of any column in any
  query, you most likely want sqlite3_column_name().

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_release_memory() current status

[Peter Kasting]

On Mon, Oct 26, 2009 at 2:48 PM, D. Richard Hipp  wrote:

> On Oct 26, 2009, at 5:00 PM, Peter Kasting wrote:
> > as
> > far as
> > I can tell the current code shouldn't cause any performance hit by
> > enabling
> > this flag.  There's no additional monitoring/tracking it causes; it
> > just
> > exposes a few functions to free memory.  Has the memory management
> > implementation changed since the previous thread, or is this feature
> > now
> > broken?
>
> We run the "fulltest" test suite on a build of SQLite that include
> SQLITE_ENABLE_MEMORY_MANAGEMENT prior to every release.  The
> sqlite3_release_memory() interface works as documented, as far as we
> know.  Are you seeing something that would suggest otherwise?
>

I think you're misunderstanding the thrust of my question.  I'm saying that
based on a glance at the current code, it doesn't look like past comments
about the perf impact of this flag would be accurate anymore.  I want to
know if my supposition is true.  Presumably, if it is true, it's either
because memory management has been rearchitected since that old thread, or
else because the feature has become broken.  I'm not saying I think it's
broken, simply that that would be one possible explanation for the
discrepancy between what it looks like the code does and what it sounds like
it used to do.

The other question in my email, regarding the reason for the #if 0 inside
sqlite3_release_memory(), is eprhaps more interesting to me.

PK

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

Re: [sqlite] sqlite3_release_memory() current status

[D. Richard Hipp]

On Oct 26, 2009, at 5:00 PM, Peter Kasting wrote:

> Close to two years ago, Shawn Wilsher from Mozilla asked about freeing
> memory used by sqlite (
> http://www.mail-archive.com/sqlite-users@sqlite.org/ 
> msg30585.html ).  At the
> time, some benchmarks showed that setting
> -DSQLITE_ENABLE_MEMORY_MANAGEMENT=1 caused a small performance hit.
>
> I'm looking into the same issue, this time for Google Chrome, and as  
> far as
> I can tell the current code shouldn't cause any performance hit by  
> enabling
> this flag.  There's no additional monitoring/tracking it causes; it  
> just
> exposes a few functions to free memory.  Has the memory management
> implementation changed since the previous thread, or is this feature  
> now
> broken?

We run the "fulltest" test suite on a build of SQLite that include  
SQLITE_ENABLE_MEMORY_MANAGEMENT prior to every release.  The  
sqlite3_release_memory() interface works as documented, as far as we  
know.  Are you seeing something that would suggest otherwise?

>
> Also, I notice that in malloc.c, inside the #if for the above define,
> there's an #if 0 protecting a call into the VDBE code.  Is this  
> because that
> code doesn't work correctly, or was it not helpful, or have the  
> reasons for
> the #if 0 disappeared?  It's not obvious to me what the code it  
> protects
> does, so I'm not sure how much interest in it I should have.
>
> Thanks,
> PK
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Deadlock with two local instances

[Chris T]

I'm new to sqlite (and sql in general, actually) and came across
something puzzling.

I wrote a test program statically linked with the amalgamated sqlite
code.  When I run a single instance, everything is fine.  When I start
a second instance in the same directory they both deadlock.  Every
call to sqlite3_step returns SQLITE_BUSY.

The source code to my test program is attached.  It was written in
Visual Studio, so feel free to remove the reference to windows.h and
change the calls to Sleep( ) if you don't use Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

[Rob Sciuk]

I've just compiled the latest (3.6.19) with the

-DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is that 
when I use an aggregate function in a select, the table name is not being 
returned from an sqlite3_column_table_name() is not returning the 
tablename as expected from a prepared statement, whereas if I actually 
request data from the table, it works fine:

eg:
If I prepare the statement "select * from q_user", q_user is returned as 
expected from sqlite3_column_table_name().

If I prepare the statement "select count(*) from q_user", 
sqlite3_column_table_name() returns NULL.

This is somewhat disconcerting, and seems repeatable.  Are aggregate 
functions handled differently than tuple data?

Are there any other dependancies other than SQLITE_ENABLE_COLUMN_METADATA?

Is there anything I might have overlooked??

Any ideas??

Thanks in advance,
Rob Sciuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_release_memory() current status

[Peter Kasting]

Close to two years ago, Shawn Wilsher from Mozilla asked about freeing
memory used by sqlite (
http://www.mail-archive.com/sqlite-users@sqlite.org/msg30585.html ).  At the
time, some benchmarks showed that setting
-DSQLITE_ENABLE_MEMORY_MANAGEMENT=1 caused a small performance hit.

I'm looking into the same issue, this time for Google Chrome, and as far as
I can tell the current code shouldn't cause any performance hit by enabling
this flag.  There's no additional monitoring/tracking it causes; it just
exposes a few functions to free memory.  Has the memory management
implementation changed since the previous thread, or is this feature now
broken?

Also, I notice that in malloc.c, inside the #if for the above define,
there's an #if 0 protecting a call into the VDBE code.  Is this because that
code doesn't work correctly, or was it not helpful, or have the reasons for
the #if 0 disappeared?  It's not obvious to me what the code it protects
does, so I'm not sure how much interest in it I should have.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Reid Thompson]

On Sat, 2009-10-24 at 16:33 +0200, Gilles Ganault wrote:
> On Sat, 24 Oct 2009 00:38:20 +0200, "Olaf Schmidt"
>  wrote:
> >Hmm in this case, what about:
> >http://www.realsoftware.com/realsqlserver/
> 
> Thanks much for the tip :) I'll give it a shot.
> 
> >So, on what platform(s) do your client-apps need to work?
> >In what (main)language do you develop your client-app?
> 
> Either Python or Delphi. Hopefully, they have connectors for those
> languages.
> 
> Thanks again.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

may be of interest  
http://sqlrelay.sourceforge.net/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Sam Carleton]

There is a WONDERFUL Firefox plug-in that I use, I love it!  I am guessing
it works on all OS's but I use it on Windows Vista and Windows 7.

Sam

On Mon, Oct 26, 2009 at 2:53 PM, Gilles Ganault wrote:

> On Mon, 26 Oct 2009 11:31:50 +0500, "SQL Maestro Group"
>  wrote:
> >SQLite Maestro allows you to copy query results to clipboard with ease:
> just
> >execute a query and press Ctrl+A, Ctrl+C.
>
> Thanks, I'll check it out.
>
> ___
> 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] SQLite server for Windows/Linux?

[Gilles Ganault]

On Sun, 25 Oct 2009 02:57:52 -0700 (PDT), George Black
 wrote:
>did you try www.SQLitening.com

David Morris just sent a pointer above. Thanks much for the info.

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

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Gilles Ganault]

On Mon, 26 Oct 2009 11:31:50 +0500, "SQL Maestro Group"
 wrote:
>SQLite Maestro allows you to copy query results to clipboard with ease: just
>execute a query and press Ctrl+A, Ctrl+C.

Thanks, I'll check it out.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Sat, 24 Oct 2009 17:37:31 -0700 (PDT), David Morris
 wrote:
>Have a peek at : http://www.sqlitening.com/support/index.php
>Different language and could run under Wine on *nix

Thanks for the pointer. I didn't know SQLitening.

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

Re: [sqlite] Degree character not displayed correctly.

[Jean-Christophe Deschamps]

Nico, Igor,


You're both right to point out that using SQLite would result in 
non-UTF-* compliant data producing unexpected results.  There is still 
the possibility to store such data as blobs.

Sorry for confusion.



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

Re: [sqlite] FTS3 - closed ticket 3950 - bug still exists in 3.6.19

[Wanadoo Hartwig]

Am 26.10.2009 um 18:49 schrieb P Kishor:

> On Mon, Oct 26, 2009 at 12:40 PM, Wanadoo Hartwig
>  wrote:
>>
>> Am 26.10.2009 um 15:27 schrieb P Kishor:
>>
>> On Mon, Oct 26, 2009 at 9:17 AM, Scott Hess  wrote:
>>
>> The bug was closed with "cannot reproduce".  So IMHO either the bug
>>
>> should be re-opened with the provided repro case, or a new bug should
>>
>> be opened with the provided repro case.
>>
>> As listed in my earlier email, it can be reproduced very simply as  
>> follows
>> --
>>
>> SQLite version 3.6.19
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
>> sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
>> sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR  
>> EACH ROW
>> BEGIN
>>  ...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
>> sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR  
>> EACH ROW
>> BEGIN
>>  ...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name);  
>> END;
>> sqlite> INSERT INTO Simple (Name) VALUES('one');
>> sqlite> INSERT INTO Simple (Name) VALUES('two');
>> sqlite> SELECT * FROM simple;
>> ID  Name
>> --  --
>> 1   one
>> 2   two
>> sqlite> SELECT last_insert_rowid();
>> last_insert_rowid()
>> ---
>> 2
>> sqlite> DELETE FROM Simple WHERE (ID = 1);
>> sqlite> INSERT INTO Simple (Name) VALUES('three');
>> sqlite> SELECT last_insert_rowid();
>> last_insert_rowid()
>> ---
>> 4
>> sqlite>
>>
>>
>>
>>  Then, if the team decides
>>
>> that this is works-as-designed, the team can close the bug with that
>>
>> resolution.
>>
>> When I brought this up on the list a year and a half ago, we didn't
>>
>> really come to a conclusion on how it should work.  I think there  
>> were
>>
>> arguments for both sides of the equation.  My personal preference
>>
>> would be for it to work like one would expect it to (the "interior"
>>
>> last-insert-id isn't exposed to "exterior" code, because it is none  
>> of
>>
>> that code's business).  But it needs to happen in a way which doesn't
>>
>> adversely impact performance.
>>
>> Performance is important, but getting back what one expects
>> (integrity) is perhaps more important. If my subsequent actions are
>> dependent on the correct last_insert_rowid(), then the way it works
>> right now doesn't work.
>>
>>
>>
>> I really think that this is a bug. The last-inserted-id is under  
>> normal
>> circumstances always the one that caused the trigger to fire (in  
>> case of an
>> insert trigger).
>
> I concur. Consider
>
> SQLite version 3.6.19
> sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, desc TEXT);
> sqlite> CREATE TRIGGER foo AFTER INSERT ON a
>   ...>   BEGIN
>   ...> INSERT INTO a (desc) VALUES ('after insert');
>   ...>   END;
> sqlite> INSERT INTO a (desc) VALUES ('insert');
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> ---
> 1
> sqlite> SELECT * FROM a;
> id  desc
> --  --
> 1   insert
> 2   after inse
> sqlite>
>
>

Yes, this is expected behaviour; exactly what I am saying. The last  
inserted row id is the one of the insert statement inserting 'insert'.  
Anything what you do INSIDE a trigger statement is not influencing the  
last-insert-id (unless you use FTS3).

Hartwig

>> Furthermore, I checked the documentation concerning virtual
>> tables and there is no exception mentioned that virtual tables  
>> should behave
>> differently than normal tables with respect to triggers (besides the
>> statement that virtual tables cannot fire triggers).
>> The only usable workaround I currently have is to simulate the  
>> trigger by
>> doing the corresponding calls explicitely.
>> Hartwig
>>
>> -scott
>>
>>
>> On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns  
>>  wrote:
>>
>> -BEGIN PGP SIGNED MESSAGE-
>>
>> Hash: SHA1
>>
>> Wanadoo Hartwig wrote:
>>
>> Actually, if you replace the FTS3 table with any other type of  
>> table -
>>
>> or as many tables as you like and do also in these tables insert
>>
>> operations - the last row id of the original table is correctly
>>
>> returned. Therefore, I assume (I do not have any details) that there
>>
>> is a bug in the trigger mechanism itself.
>>
>> The point that keeps being missed is that FTS3 is not a regular  
>> table.  It
>>
>> is a virtual table.  That means that the SQLite core calls it  
>> corresponding
>>
>> to various SQL operations but behind the scenes it is up the  
>> virtual table
>>
>> implementation what to do.  Read about them at:
>>
>>  http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>>
>> A virtual table implementation is free to do whatever it wants.   
>> When asked
>>
>> to insert one row, it could store 27 after converting all text to  
>> Klingon.
>>
>> It could instead delete whatever you asked it to 

Re: [sqlite] FTS3 - closed ticket 3950 - bug still exists in 3.6.19

[P Kishor]

On Mon, Oct 26, 2009 at 12:40 PM, Wanadoo Hartwig
 wrote:
>
> Am 26.10.2009 um 15:27 schrieb P Kishor:
>
> On Mon, Oct 26, 2009 at 9:17 AM, Scott Hess  wrote:
>
> The bug was closed with "cannot reproduce".  So IMHO either the bug
>
> should be re-opened with the provided repro case, or a new bug should
>
> be opened with the provided repro case.
>
> As listed in my earlier email, it can be reproduced very simply as follows
> --
>
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
> sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
> sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW
> BEGIN
>  ...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
> sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW
> BEGIN
>  ...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
> sqlite> INSERT INTO Simple (Name) VALUES('one');
> sqlite> INSERT INTO Simple (Name) VALUES('two');
> sqlite> SELECT * FROM simple;
> ID  Name
> --  --
> 1   one
> 2   two
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> ---
> 2
> sqlite> DELETE FROM Simple WHERE (ID = 1);
> sqlite> INSERT INTO Simple (Name) VALUES('three');
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> ---
> 4
> sqlite>
>
>
>
>  Then, if the team decides
>
> that this is works-as-designed, the team can close the bug with that
>
> resolution.
>
> When I brought this up on the list a year and a half ago, we didn't
>
> really come to a conclusion on how it should work.  I think there were
>
> arguments for both sides of the equation.  My personal preference
>
> would be for it to work like one would expect it to (the "interior"
>
> last-insert-id isn't exposed to "exterior" code, because it is none of
>
> that code's business).  But it needs to happen in a way which doesn't
>
> adversely impact performance.
>
> Performance is important, but getting back what one expects
> (integrity) is perhaps more important. If my subsequent actions are
> dependent on the correct last_insert_rowid(), then the way it works
> right now doesn't work.
>
>
>
> I really think that this is a bug. The last-inserted-id is under normal
> circumstances always the one that caused the trigger to fire (in case of an
> insert trigger).

I concur. Consider

SQLite version 3.6.19
sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, desc TEXT);
sqlite> CREATE TRIGGER foo AFTER INSERT ON a
   ...>   BEGIN
   ...> INSERT INTO a (desc) VALUES ('after insert');
   ...>   END;
sqlite> INSERT INTO a (desc) VALUES ('insert');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
---
1
sqlite> SELECT * FROM a;
id  desc
--  --
1   insert
2   after inse
sqlite>


> Furthermore, I checked the documentation concerning virtual
> tables and there is no exception mentioned that virtual tables should behave
> differently than normal tables with respect to triggers (besides the
> statement that virtual tables cannot fire triggers).
> The only usable workaround I currently have is to simulate the trigger by
> doing the corresponding calls explicitely.
> Hartwig
>
> -scott
>
>
> On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns  wrote:
>
> -BEGIN PGP SIGNED MESSAGE-
>
> Hash: SHA1
>
> Wanadoo Hartwig wrote:
>
> Actually, if you replace the FTS3 table with any other type of table -
>
> or as many tables as you like and do also in these tables insert
>
> operations - the last row id of the original table is correctly
>
> returned. Therefore, I assume (I do not have any details) that there
>
> is a bug in the trigger mechanism itself.
>
> The point that keeps being missed is that FTS3 is not a regular table.  It
>
> is a virtual table.  That means that the SQLite core calls it corresponding
>
> to various SQL operations but behind the scenes it is up the virtual table
>
> implementation what to do.  Read about them at:
>
>  http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>
> A virtual table implementation is free to do whatever it wants.  When asked
>
> to insert one row, it could store 27 after converting all text to Klingon.
>
> It could instead delete whatever you asked it to add.  It could do nothing.
>
>  The FTS3 implementation happens to have 3 other tables and so on an insert
>
> those are updated with extra information and you are seeing those after
> effects.
>
> Roger
>


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu

Re: [sqlite] FTS3 - closed ticket 3950 - bug still exists in 3.6.19

[Wanadoo Hartwig]

Am 26.10.2009 um 15:27 schrieb P Kishor:

> On Mon, Oct 26, 2009 at 9:17 AM, Scott Hess  wrote:
>> The bug was closed with "cannot reproduce".  So IMHO either the bug
>> should be re-opened with the provided repro case, or a new bug should
>> be opened with the provided repro case.
>
> As listed in my earlier email, it can be reproduced very simply as  
> follows --
>
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
> sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
> sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH  
> ROW BEGIN
>  ...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
> sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH  
> ROW BEGIN
>  ...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
> sqlite> INSERT INTO Simple (Name) VALUES('one');
> sqlite> INSERT INTO Simple (Name) VALUES('two');
> sqlite> SELECT * FROM simple;
> ID  Name
> --  --
> 1   one
> 2   two
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> ---
> 2
> sqlite> DELETE FROM Simple WHERE (ID = 1);
> sqlite> INSERT INTO Simple (Name) VALUES('three');
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> ---
> 4
> sqlite>
>
>
>
>>  Then, if the team decides
>> that this is works-as-designed, the team can close the bug with that
>> resolution.
>>
>> When I brought this up on the list a year and a half ago, we didn't
>> really come to a conclusion on how it should work.  I think there  
>> were
>> arguments for both sides of the equation.  My personal preference
>> would be for it to work like one would expect it to (the "interior"
>> last-insert-id isn't exposed to "exterior" code, because it is none  
>> of
>> that code's business).  But it needs to happen in a way which doesn't
>> adversely impact performance.
>
> Performance is important, but getting back what one expects
> (integrity) is perhaps more important. If my subsequent actions are
> dependent on the correct last_insert_rowid(), then the way it works
> right now doesn't work.
>
>

I really think that this is a bug. The last-inserted-id is under  
normal circumstances always the one that caused the trigger to fire  
(in case of an insert trigger). Furthermore, I checked the  
documentation concerning virtual tables and there is no exception  
mentioned that virtual tables should behave differently than normal  
tables with respect to triggers (besides the statement that virtual  
tables cannot fire triggers).

The only usable workaround I currently have is to simulate the trigger  
by doing the corresponding calls explicitely.

Hartwig

>>
>> -scott
>>
>>
>> On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns  
>>  wrote:
>>> -BEGIN PGP SIGNED MESSAGE-
>>> Hash: SHA1
>>>
>>> Wanadoo Hartwig wrote:
> Actually, if you replace the FTS3 table with any other type of  
> table -
> or as many tables as you like and do also in these tables insert
> operations - the last row id of the original table is correctly
> returned. Therefore, I assume (I do not have any details) that  
> there
> is a bug in the trigger mechanism itself.
>>>
>>> The point that keeps being missed is that FTS3 is not a regular  
>>> table.  It
>>> is a virtual table.  That means that the SQLite core calls it  
>>> corresponding
>>> to various SQL operations but behind the scenes it is up the  
>>> virtual table
>>> implementation what to do.  Read about them at:
>>>
>>>  http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>>>
>>> A virtual table implementation is free to do whatever it wants.   
>>> When asked
>>> to insert one row, it could store 27 after converting all text to  
>>> Klingon.
>>> It could instead delete whatever you asked it to add.  It could do  
>>> nothing.
>>>  The FTS3 implementation happens to have 3 other tables and so on  
>>> an insert
>>> those are updated with extra information and you are seeing those  
>>> after effects.
>>>
>>> Roger
>>> -BEGIN PGP SIGNATURE-
>>> Version: GnuPG v1.4.9 (GNU/Linux)
>>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>>
>>> iEYEARECAAYFAkrk/A4ACgkQmOOfHg372QTEWACggOjPYsHFzB00jNMcDkOmYQ5q
>>> KCgAniNN8LAdKea5ZhHYO5SgrsG7qhdw
>>> =R9aD
>>> -END PGP SIGNATURE-
>>> ___
>>> 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
>>
>
>
>
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> 

Re: [sqlite] Degree character not displayed correctly.

[Nicolas Williams]

On Mon, Oct 26, 2009 at 10:01:43AM -0700, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Jean-Christophe Deschamps wrote:
> > First decide or determine what is (or shall be) your database 
> > encoding.  Even if SQLite has no problem storing ANSI (or EBCDIC or 
> > anything else) strings untouched,
> 
> This isn't particularly good advice.  SQLite works solely in Unicode.  When
> you supply text it *must* be in either UTF8 or UTF16 according to the API
> being used.  Sometimes it will appear that you can get by using a different
> encoding but that is just luck and things that operate on text will fail.
> The actual encoding used by the database is pretty much irrelevant and other
> than the pragma you can't even tell what it is nor would you care.

Indeed.  IIRC SQLite3 is actually 8-bit clean, but that doesn't matter:
by stating that it uses UTF-8 (and UTF-16) the SQLite3 developers are
actually allowing themselves the freedom to do a variety of things that
would break your application if you used non-UTF-8 (and non-UTF-16)
text.

For example, the SQLite3 developers might add code to reject strings
with invalid UTF-8 sequences, or strings which use unassigned code
points (unassigned in the version of Unicode supported by the SQLite3
that you are running).  Or they might add support for case-insensitive
matching for non-ASCII text.  Or they might add normalization-
insensitive matching.  And so on.

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

Re: [sqlite] Degree character not displayed correctly.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jean-Christophe Deschamps wrote:
> First decide or determine what is (or shall be) your database 
> encoding.  Even if SQLite has no problem storing ANSI (or EBCDIC or 
> anything else) strings untouched,

This isn't particularly good advice.  SQLite works solely in Unicode.  When
you supply text it *must* be in either UTF8 or UTF16 according to the API
being used.  Sometimes it will appear that you can get by using a different
encoding but that is just luck and things that operate on text will fail.
The actual encoding used by the database is pretty much irrelevant and other
than the pragma you can't even tell what it is nor would you care.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrl1fQACgkQmOOfHg372QSC5ACfUDFnyFkDt7YE4d0BivC42eHt
6zYAnRwQ2Vnod9OEYM2flWdld+VC4L3L
=gFQA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Degree character not displayed correctly.

[Ted Rolle]

On Mon, 26 Oct 2009 16:06:33 +0100
Jean-Christophe Deschamps  wrote:

> Ted,
> 
> 
> >I didn't insert it.  I 'inherited' it from a (mercifully nameless)
> >predecessor.
> >I want to put this data into a database to make it easily accessible
> 
> I'm no SQLite guru (really NO), but here is my 2 cent advice.
> 
> First decide or determine what is (or shall be) your database 
> encoding.  Even if SQLite has no problem storing ANSI (or EBCDIC or 
> anything else) strings untouched, I would strongly recommend you
> select either UTF-8 or UTF-16 if your situation doesn't impose
> something else.  This way your data is garanteed to display stored
> data independant of the user's codepage (if applicable).  This choice
> is to be made at database creation and can't be changed, short of
> dumping the base and re-loading it into a fresh one using another
> (internal) encoding.
> 
> Independantly of the selected Unicode internal encoding, you can use 
> any two UTF interfaces to SQLite: the xxx or the xxx16 functions.
> But of course, supply data encoded consistently with the functions
> you invoke.
> 
> As I understand it, your data is not yet stored in the base.  When/if 
> this is the case, use whatever transcoding tool you find handy to 
> re-encode your data before pushing it into the SQLite base, if needed.
> 
> For instance, the 'degree symbol' is {0xB0} ANSI (Latin1 codepage),
> is {0xC2 0xB0 } as UTF-8 and {0x00B0} as UTF-16.  But even if the
> ANSI (Latin1) charset between 0x80 and 0xFF map to corresponding
> Unicode codepoints, beware that they need to be UTF-8 encoded if you
> want them to display correctly using a UTF-8 tool.
> 
> OTOH you can as well choose to store ANSI (for instance) data, but
> you need to retrieve/display back data using the same encoding.  The
> catch is that non-Unicode (e.g. ANSI) tools are fading away, even in
> the Win* world.
> 
> J-C
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I really appreciate you insights.
I did a 'pragma encoding;' and SQLite3 returned 'UTF-8'.  Perfect.  If
I had enough disk space, I'd have gone for UTF-16.  But this is good.
Apparently SQLite3 stores the characters correctly. (I just verified
this with TextPad.  Now it's just a representational (DOS?) issue.
Bit by bit, we're getting there!  Thanks.
Ted
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Degree character not displayed correctly.

[Pavel Ivanov]

> I didn't insert it.  I 'inherited' it from a (mercifully nameless)
> predecessor.
> I want to put this data into a database to make it easily accessible

I don't understand that. "Put data into a database" == "Insert data"
(read: into a database). So either you inserted (== want to put into
...) or not inserted (== you already have it in the database and
didn't insert). And regarding my other not answered questions: SQLite
doesn't display data by itself. You either retrieve it in your program
and display it in your program or you use sqlite3 command line tool to
retrieve and display. And now pay attention: when you insert data into
database you can do it in whatever encoding you like - SQLite doesn't
care, doesn't check and doesn't complain if something is incorrectly
encoded. When you retrieve data in your program you also can do it in
whatever encoding you like - SQLite doesn't care. But if you retrieve
data using command line tool sqlite3 - it does care and it assumes
that your data is in correct database encoding (UTF-8 in most cases by
default) and decodes it accordingly when tries to display it. So you
can get a problem here.

Pavel

On Mon, Oct 26, 2009 at 10:28 AM, Ted Rolle  wrote:
> On Mon, 26 Oct 2009 10:12:03 -0400
> Pavel Ivanov  wrote:
>
>> How do you insert it? How do you retrieve it? How do you display it?
>> I bet the problem is in the first question, not in the last one.
>>
>> Pavel
>
> I didn't insert it.  I 'inherited' it from a (mercifully nameless)
> predecessor.
> I want to put this data into a database to make it easily accessible
>
> Ted
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Degree character not displayed correctly.

[Jean-Christophe Deschamps]

Ted,


>I didn't insert it.  I 'inherited' it from a (mercifully nameless)
>predecessor.
>I want to put this data into a database to make it easily accessible

I'm no SQLite guru (really NO), but here is my 2 cent advice.

First decide or determine what is (or shall be) your database 
encoding.  Even if SQLite has no problem storing ANSI (or EBCDIC or 
anything else) strings untouched, I would strongly recommend you select 
either UTF-8 or UTF-16 if your situation doesn't impose something 
else.  This way your data is garanteed to display stored data 
independant of the user's codepage (if applicable).  This choice is to 
be made at database creation and can't be changed, short of dumping the 
base and re-loading it into a fresh one using another (internal) encoding.

Independantly of the selected Unicode internal encoding, you can use 
any two UTF interfaces to SQLite: the xxx or the xxx16 functions.  But 
of course, supply data encoded consistently with the functions you invoke.

As I understand it, your data is not yet stored in the base.  When/if 
this is the case, use whatever transcoding tool you find handy to 
re-encode your data before pushing it into the SQLite base, if needed.

For instance, the 'degree symbol' is {0xB0} ANSI (Latin1 codepage), is 
{0xC2 0xB0 } as UTF-8 and {0x00B0} as UTF-16.  But even if the ANSI 
(Latin1) charset between 0x80 and 0xFF map to corresponding Unicode 
codepoints, beware that they need to be UTF-8 encoded if you want them 
to display correctly using a UTF-8 tool.

OTOH you can as well choose to store ANSI (for instance) data, but you 
need to retrieve/display back data using the same encoding.  The catch 
is that non-Unicode (e.g. ANSI) tools are fading away, even in the Win* 
world.

J-C



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

Re: [sqlite] Degree character not displayed correctly.

[Ted Rolle]

On Mon, 26 Oct 2009 10:12:03 -0400
Pavel Ivanov  wrote:

> How do you insert it? How do you retrieve it? How do you display it?
> I bet the problem is in the first question, not in the last one.
> 
> Pavel

I didn't insert it.  I 'inherited' it from a (mercifully nameless)
predecessor.
I want to put this data into a database to make it easily accessible

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

Re: [sqlite] FTS3 - closed ticket 3950 - bug still exists in 3.6.19

[P Kishor]

On Mon, Oct 26, 2009 at 9:17 AM, Scott Hess  wrote:
> The bug was closed with "cannot reproduce".  So IMHO either the bug
> should be re-opened with the provided repro case, or a new bug should
> be opened with the provided repro case.

As listed in my earlier email, it can be reproduced very simply as follows --

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN
  ...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN
  ...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
sqlite> INSERT INTO Simple (Name) VALUES('one');
sqlite> INSERT INTO Simple (Name) VALUES('two');
sqlite> SELECT * FROM simple;
ID  Name
--  --
1   one
2   two
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
---
2
sqlite> DELETE FROM Simple WHERE (ID = 1);
sqlite> INSERT INTO Simple (Name) VALUES('three');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
---
4
sqlite>



> Then, if the team decides
> that this is works-as-designed, the team can close the bug with that
> resolution.
>
> When I brought this up on the list a year and a half ago, we didn't
> really come to a conclusion on how it should work.  I think there were
> arguments for both sides of the equation.  My personal preference
> would be for it to work like one would expect it to (the "interior"
> last-insert-id isn't exposed to "exterior" code, because it is none of
> that code's business).  But it needs to happen in a way which doesn't
> adversely impact performance.

Performance is important, but getting back what one expects
(integrity) is perhaps more important. If my subsequent actions are
dependent on the correct last_insert_rowid(), then the way it works
right now doesn't work.


>
> -scott
>
>
> On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns  wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> Wanadoo Hartwig wrote:
 Actually, if you replace the FTS3 table with any other type of table -
 or as many tables as you like and do also in these tables insert
 operations - the last row id of the original table is correctly
 returned. Therefore, I assume (I do not have any details) that there
 is a bug in the trigger mechanism itself.
>>
>> The point that keeps being missed is that FTS3 is not a regular table.  It
>> is a virtual table.  That means that the SQLite core calls it corresponding
>> to various SQL operations but behind the scenes it is up the virtual table
>> implementation what to do.  Read about them at:
>>
>>  http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>>
>> A virtual table implementation is free to do whatever it wants.  When asked
>> to insert one row, it could store 27 after converting all text to Klingon.
>> It could instead delete whatever you asked it to add.  It could do nothing.
>>  The FTS3 implementation happens to have 3 other tables and so on an insert
>> those are updated with extra information and you are seeing those after 
>> effects.
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.9 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iEYEARECAAYFAkrk/A4ACgkQmOOfHg372QTEWACggOjPYsHFzB00jNMcDkOmYQ5q
>> KCgAniNN8LAdKea5ZhHYO5SgrsG7qhdw
>> =R9aD
>> -END PGP SIGNATURE-
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 - closed ticket 3950 - bug still exists in 3.6.19

[Scott Hess]

The bug was closed with "cannot reproduce".  So IMHO either the bug
should be re-opened with the provided repro case, or a new bug should
be opened with the provided repro case.  Then, if the team decides
that this is works-as-designed, the team can close the bug with that
resolution.

When I brought this up on the list a year and a half ago, we didn't
really come to a conclusion on how it should work.  I think there were
arguments for both sides of the equation.  My personal preference
would be for it to work like one would expect it to (the "interior"
last-insert-id isn't exposed to "exterior" code, because it is none of
that code's business).  But it needs to happen in a way which doesn't
adversely impact performance.

-scott


On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Wanadoo Hartwig wrote:
>>> Actually, if you replace the FTS3 table with any other type of table -
>>> or as many tables as you like and do also in these tables insert
>>> operations - the last row id of the original table is correctly
>>> returned. Therefore, I assume (I do not have any details) that there
>>> is a bug in the trigger mechanism itself.
>
> The point that keeps being missed is that FTS3 is not a regular table.  It
> is a virtual table.  That means that the SQLite core calls it corresponding
> to various SQL operations but behind the scenes it is up the virtual table
> implementation what to do.  Read about them at:
>
>  http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>
> A virtual table implementation is free to do whatever it wants.  When asked
> to insert one row, it could store 27 after converting all text to Klingon.
> It could instead delete whatever you asked it to add.  It could do nothing.
>  The FTS3 implementation happens to have 3 other tables and so on an insert
> those are updated with extra information and you are seeing those after 
> effects.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkrk/A4ACgkQmOOfHg372QTEWACggOjPYsHFzB00jNMcDkOmYQ5q
> KCgAniNN8LAdKea5ZhHYO5SgrsG7qhdw
> =R9aD
> -END PGP SIGNATURE-
> ___
> 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] Degree character not displayed correctly.

[Pavel Ivanov]

How do you insert it? How do you retrieve it? How do you display it?
I bet the problem is in the first question, not in the last one.

Pavel

On Mon, Oct 26, 2009 at 10:04 AM, Ted Rolle  wrote:
> How can I get the degree character ° (0xB0) (as in 32 degrees Farenheit)
> to display correctly.
> My text editors (Vim and TextPad) and Claws-mailer display this
> character correctly.
> TextPad uses the 'ANSI' character set.
>
> Ted
> ___
> 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

[sqlite] Degree character not displayed correctly.

[Ted Rolle]

How can I get the degree character ° (0xB0) (as in 32 degrees Farenheit)
to display correctly.
My text editors (Vim and TextPad) and Claws-mailer display this
character correctly.
TextPad uses the 'ANSI' character set.

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

Re: [sqlite] counting items before a ScrollingCursor

[Sam Carleton]

On Mon, Oct 26, 2009 at 7:58 AM, Igor Tandetnik  wrote:


> and then in your host app check for negative values and treat them as zero.
> Or, if you insist on doing that in SQL:
>
> select (case when c > 0 then c else 0 end) from
> (SELECT COUNT(title) - 5 as c FROM tracks
>  WHERE singer='Madonna'
>   AND title<:firsttitle);


Igor,

You are always right on the mark, thank you!  The only minor change I will
make is to the 'else 0', change it to ' else 5 + c', getting the accurate
conceptual index of the first entry.

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

Re: [sqlite] counting items before a ScrollingCursor

[Igor Tandetnik]

Sam Carleton wrote:
> I can determine the number of title before lasttitle with this:
> 
> SELECT COUNT(title) FROM tracks
> WHERE singer='Madonna'
>   AND title<:lasttitle
> 
> I am having a problem doing this when scrolling backword.  Again here is the
> SQL from the Wiki to get the display set:
> 
> SELECT title FROM tracks
>WHERE singer='Madonna'
>  AND title<:firsttitle
>ORDER BY title DESC
>LIMIT 5;

Something like

SELECT COUNT(title) - 5 FROM tracks
 WHERE singer='Madonna'
   AND title<:firsttitle;

and then in your host app check for negative values and treat them as zero. Or, 
if you insist on doing that in SQL:

select (case when c > 0 then c else 0 end) from
(SELECT COUNT(title) - 5 as c FROM tracks
 WHERE singer='Madonna'
   AND title<:firsttitle);

Igor Tandetnik


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

Re: [sqlite] counting items before a ScrollingCursor

[Pavel Ivanov]

> SELECT MIN(title) FROM tracks
>WHERE singer='Madonna'
>  AND title<:firsttitle
>ORDER BY title DESC
>LIMIT 5;
>
> But this gave me the VERY first title.  It appears the MIN is executed
> BEFORE the LIMIT.

Yes, that's true. You may want to try this instead:

SELECT MIN(title) FROM
(SELECT title FROM tracks
   WHERE singer='Madonna'
 AND title<:firsttitle
   ORDER BY title DESC
   LIMIT 5);


Pavel

On Sun, Oct 25, 2009 at 11:04 PM, Sam Carleton
 wrote:
> I am working with a data table that can have rows removed and added at any
> point in time. I am following the SQLite Scrolling Cursor wiki
> pageon
> implementing paging.  The only thing I have left to figure out is how
> to
> determine how many items are before the first item, so the correct page can
> be determined.  Here is an example based on the wiki page:
>
> Assume the same Madonna example as the wiki page, here is how to get the
> next 5 titles:
>
> SELECT title FROM tracks
>  WHERE singer='Madonna'
>   AND title>:lasttitle
>  ORDER BY title
>  LIMIT 5;
>
> I can determine the number of title before lasttitle with this:
>
> SELECT COUNT(title) FROM tracks
>  WHERE singer='Madonna'
>   AND title<:lasttitle
>
> I am having a problem doing this when scrolling backword.  Again here is the
> SQL from the Wiki to get the display set:
>
> SELECT title FROM tracks
>    WHERE singer='Madonna'
>      AND title<:firsttitle
>    ORDER BY title DESC
>    LIMIT 5;
>
> My thought is to find the minimum title from the display set, then use that
> in the count select, so I started with this:
>
> SELECT MIN(title) FROM tracks
>    WHERE singer='Madonna'
>      AND title<:firsttitle
>    ORDER BY title DESC
>    LIMIT 5;
>
> But this gave me the VERY first title.  It appears the MIN is executed
> BEFORE the LIMIT.  How do I go about getting the minimum title to use in one
> compound select statement:
>
> SELECT COUNT(title) FROM tracks
>  WHERE singer='Madonna'
>   AND title<  [[Statement to get the minimum title]]
>
> Sam
> ___
> 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] SQLITE_BUSY

[Pavel Ivanov]

General suggestion to you, Mark, would be change your database engine,
use something else than SQLite.

Application that continuously writing to the database thousands of
rows (what size database can reach with it, I wonder?) from one
process and tries to read from another process is DOA with SQLite.
It's even written at http://www.sqlite.org/whentouse.html (see "High
Concurrency" part at the end). You can get some luck with such kind of
application combined with SQLite if you work from the single process
with shared cache turned on (and maybe even read_uncommitted would
also have to be turned on). But if you want to do it from different
processes you better do it with MySQL or alike.

Pavel

On Mon, Oct 26, 2009 at 4:21 AM, Mark Flipphi
 wrote:
> Hello,
>
> We have a sqlite database that is used to strore measurement data.
>
> One application is on a server and is continuous storing data to the
> database.
> We use :
> BEGIN
> INSERT
> INSERT
> 
> COMMIT
> The commit is called when there are more then 100.000 inserts or 1
> second  has elapsed.
>
> Now an other application is running on a desktop pc and opens the
> database from the server harddisk (shared drive)
> We need to be able to acces the data in the database, but the database
> is almost always locked.
> This application is reading the data and needs to be able to
> occasionally store some fields.
>
> I think it has something to do with the BEGIN that locks the database.
>
> Are there any suggestion on how to solve this ?
>
> Server is Windows 2008 R2, Desktop is Windows Vista
>
> With kind regards,
>
> Mark Flipphi
>
> ___
> 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

[sqlite] counting items before a ScrollingCursor

[Sam Carleton]

I am working with a data table that can have rows removed and added at any
point in time. I am following the SQLite Scrolling Cursor wiki
pageon
implementing paging.  The only thing I have left to figure out is how
to
determine how many items are before the first item, so the correct page can
be determined.  Here is an example based on the wiki page:

Assume the same Madonna example as the wiki page, here is how to get the
next 5 titles:

SELECT title FROM tracks
 WHERE singer='Madonna'
   AND title>:lasttitle
 ORDER BY title
 LIMIT 5;

I can determine the number of title before lasttitle with this:

SELECT COUNT(title) FROM tracks
 WHERE singer='Madonna'
   AND title<:lasttitle

I am having a problem doing this when scrolling backword.  Again here is the
SQL from the Wiki to get the display set:

SELECT title FROM tracks
WHERE singer='Madonna'
  AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;

My thought is to find the minimum title from the display set, then use that
in the count select, so I started with this:

SELECT MIN(title) FROM tracks
WHERE singer='Madonna'
  AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;

But this gave me the VERY first title.  It appears the MIN is executed
BEFORE the LIMIT.  How do I go about getting the minimum title to use in one
compound select statement:

SELECT COUNT(title) FROM tracks
 WHERE singer='Madonna'
   AND title<  [[Statement to get the minimum title]]

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

Re: [sqlite] SQLITE_BUSY

[Simon Slavin]

On 26 Oct 2009, at 8:21am, Mark Flipphi wrote:

> Now an other application is running on a desktop pc and opens the
> database from the server harddisk (shared drive)

Please verify for us that it's using a standard Windows shared  
folder.  This tells us how the sharing is achieved.

> We need to be able to acces the data in the database, but the database
> is almost always locked.
> This application is reading the data and needs to be able to
> occasionally store some fields.
>
> I think it has something to do with the BEGIN that locks the database.

Take a look at

http://www.sqlite.org/lang_transaction.html

and see which of the three BEGINs would suit you best for each of the  
two programs: the one that does the writing and the one that does  
mostly reading.

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

[sqlite] SQLITE_BUSY

[Mark Flipphi]

Hello,

We have a sqlite database that is used to strore measurement data.

One application is on a server and is continuous storing data to the 
database.
We use :
BEGIN
INSERT
INSERT

COMMIT
The commit is called when there are more then 100.000 inserts or 1 
second  has elapsed.

Now an other application is running on a desktop pc and opens the 
database from the server harddisk (shared drive)
We need to be able to acces the data in the database, but the database 
is almost always locked.
This application is reading the data and needs to be able to 
occasionally store some fields.

I think it has something to do with the BEGIN that locks the database.

Are there any suggestion on how to solve this ?

Server is Windows 2008 R2, Desktop is Windows Vista

With kind regards,

Mark Flipphi

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

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[SQL Maestro Group]

> SQLiteSpy (www.yunqa.de) is OK, but unless I missed the option, it
> won't let me copy the output of a SELECT into the clipboard so I can
> paste it elsewhere.
>
> Are there better alternatives?

SQLite Maestro allows you to copy query results to clipboard with ease: just
execute a query and press Ctrl+A, Ctrl+C.

It is also possible to export query results directly to MS Excel 97-2003, MS
Excel 2007, CSV/DSV/TSV, HTML, XML, MS Access, PDF, DBF, RTF, OpenDocument
Spreadsheet and some other formats. :-)

http://www.sqlmaestro.com/products/sqlite/maestro/

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