Re: Re[2]: [sqlite] How to get column description from table using SQL

[C.Peachment]

On Wed, 28 Jun 2006 12:40:52 +0600, blins wrote:

>>>  How to get column description from table using SQL

>CP> To get table definition for '':

>CP> select sql from sqlite_master where type='table' and name='';

>I need column definition.


Sqlite does not require that columns be typed so
they can hold anything. You need to read the table
definition to see what the designer expects will be
stored in each column.

Re: Re: [sqlite] Multibyte

[Anne . Kirchhellen]

> I am confused what are you trying to acheive by building a Unicode
> DLL?

I'm really confused to... ;-)

OK. The Facts:

I create SQLite-Release-DLL's, one Unicode, one MCBS
I create SQLite-Debug-Libs with symbolic informations, also 
Unicode and MCBS. For all this I use the Source-Version 3.3.5.

Now I create a Test-Application. Its a test for hold values in 
a COleVariant-List: Strings, Floats and Integers.

I build it like the following code in a for(a++)-Loop:
   tmp.Format(_T("COleVariant Insert: %i"), a);
   colevar1 = COleVariant(tmp);
   colevar2 = COleVariant(long(256 * a));
   colevar3 = COleVariant(double(123456 / double(a)));
(Remember... its only a test)

Later, i receive the values again to store they to DB:
   CString tmp = (TCHAR *) _bstr_t(&colevar1);
   int len = (int) tmp.GetLength() * sizeof(TCHAR);
   _sqlite3_bind_text(pRecordset, 1, tmp, len, NULL);

And even this one doesnt work. The stored Values are buggy in DB.

If I do the following directly without COleVariant, it work's right:
   tmp.Format(_T("COleVariant Insert: %i"), a);
   int len = (int) tmp.GetLength() * sizeof(TCHAR);
   _sqlite3_bind_text(pRecordset, 1, tmp, len, NULL);
And that is the thing, i cannot understand.

Now i found in the Docu "SQLITE_TRANSIENT". I test it like
this and all Lib's, Dll's and Test-Apps are working right:
   _sqlite3_bind_text(pRecordset, 1, tmp, len, SQLITE_TRANSIENT);

Is that ok..?... I'm a little bit unstable, what i have to do/think.

Best Greetings, Anne
-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

Re[2]: [sqlite] How to get column description from table using SQL

[blins]

Ïðèâåò C.Peachment,

Wednesday, June 28, 2006, 11:47:09 AM, you wrote:

CP> On Wed, 28 Jun 2006 10:29:47 +0600, blins wrote:


>>  How to get column description from table using SQL

CP> To get table definition for '':

CP> select sql from sqlite_master where type='table' and name='';

I need column definition.



-- 
?? ??, ? , ? ??.
Ñ íàèëó÷øèìè ïîæåëàíèÿìè,
 blinsmailto:[EMAIL PROTECTED]

Re: [sqlite] How to get column description from table using SQL

[C.Peachment]

On Wed, 28 Jun 2006 10:29:47 +0600, blins wrote:


>  How to get column description from table using SQL

To get table definition for '':

select sql from sqlite_master where type='table' and name='';

[sqlite] How to get column description from table using SQL

[blins]

Hi sqlite-users@sqlite.org,

  How to get column description from table using SQL

--
 А у нас как не было порядка, так и есть! 
С наилучшими пожеланиями,
 blins  mailto:[EMAIL PROTECTED]

Re: [sqlite] sqlite too slow for me?

[Cesar David Rodas Maldonado]

I dont know a lot about MySQL... but mysql is not so faster as you think...
I have in my computer the mysql 5 is good, but sqlite is so faster too!!!

but MySQL has a query cache  so i think the cache make it seems more
faster than sqlite...




On 6/27/06, Péter Szabó <[EMAIL PROTECTED]> wrote:


First, thank you all for the answers.

>UNIQUE(col1, col4, col5, col2),

Adding this would surely make the query run faster -- provided that
SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
col5) constraint to ensure the uniqueness of these three columns. So
instead I should have both

UNIQUE(col1, col4, col5),
UNIQUE(col1, col4, col5, col2),

but this would waste my disk space.

I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
MySQL answers my query in 0.02 seconds, and SQLite answers in more
than 28 seconds.

I guess that MySQL doesn't do any magic either (i.e. it operates in
O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
recognises that it should use the UNIQUE(col1, col4, col5) index,
while SQLite poorly chooses some other index, maybe the PRIMARY
KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
confirm that the SQLite is using the wrong index? Is it possible to
deterministically rewrite the query to force SQLite use the right
index?

Re: [sqlite] sqlite too slow for me?

[Péter Szabó]

First, thank you all for the answers.


   UNIQUE(col1, col4, col5, col2),


Adding this would surely make the query run faster -- provided that
SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
col5) constraint to ensure the uniqueness of these three columns. So
instead I should have both

 UNIQUE(col1, col4, col5),
 UNIQUE(col1, col4, col5, col2),

but this would waste my disk space.

I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
MySQL answers my query in 0.02 seconds, and SQLite answers in more
than 28 seconds.

I guess that MySQL doesn't do any magic either (i.e. it operates in
O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
recognises that it should use the UNIQUE(col1, col4, col5) index,
while SQLite poorly chooses some other index, maybe the PRIMARY
KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
confirm that the SQLite is using the wrong index? Is it possible to
deterministically rewrite the query to force SQLite use the right
index?

[sqlite] update or insert.

[Mario . Hebert]

My brain does not seem to be able to function properly today. I can think 
of many ways to do what I want to do but none of them I like.

I will describe the problem in the most symplistic form. 

I have two tables.  The first table, has a row which includes an 'id' 
pointing to a row of another table. 

create table config (profile_id integer, other_configs int );

The second table is defined as follows:

create table profile (id integer primary key autoincrement, name 
varchar(32) unique, magic_number int);

Every entry in 'profile' should be unique. I would like to be able to 
update (or insert if it does not exist) profile with a new 'magic_number' 
while keeping the same 'id' or updating the new 'id' in 'config' table.

What is the simplest way (and most efficient) to achieve this ? 

Regards,
Mario Hebert
Legerity

Re: [sqlite] Multibyte

[Roberto]

I am confused what are you trying to acheive by building a Unicode DLL?

On 27/06/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Hi to all

I have created my own 2 SQLite-DLL's, one as a Unicode-DLL and the
other one as Multibyte-DLL.

If I create a Test-App with Unicode selected and import the
Unicode-DLL it works fine. If I do the same, but have Multibyte
select und import the MBCS-DLL, some crazy things happens:
Some in the DB stored (by Test-App) Fields are confused.
Not all... only some...  That means, all Fields who are
temporarly stored in a COleVariant are buggy after they written
to DB.

The same MBCS-Test-App works fine, if I import the Unicode-DLL.
Now I'm really confused.  ;-)

How do it right? Is it ok, if I ever use a Unicode-DLL?

Many thanks and best greetings from Germany
Anne

--


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

[sqlite] unsubscribing from sqlite

[Darren Duncan]

At 5:53 PM + 6/27/06, prabhu kumaravelu wrote:
i want to unsubscribe. I have been email from sqlite and fill up my 
inbox please unsubcribe this


The instructions to get yourself off are in the header of every email:

List-Post: 
List-Help: 
List-Unsubscribe: 
List-Subscribe: 

-- Darren Duncan

Re: [sqlite] Database design questions

[prabhu kumaravelu]

i want to unsubscribe. I have been email from sqlite and fill up my inbox 
please unsubcribe this





From: Darren Duncan <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Database design questions
Date: Tue, 27 Jun 2006 10:33:03 -0700

At 10:17 AM -0700 6/27/06, Bud Beacham wrote:

I am using SQLite to add a database engine to a product I hope
to sell.  One thing that is desirable is to allow the customers
to enhance the database for their specific needs.  For example,
they could add new tables to reflect their environment, and then
design queries that look at both the standard and added tables.

However, since this all new to me I have two questions.

If I offer an upgrade version with new tables how difficult will
it be for the user to merge their database into the latest
version?  I will insure that any customer table names will not
conflict with my table names.

Would it make more sense to have the customers create a new
database and then design their queries to deal with two
databases?


Keeping everything that belongs together in a single disk file is usually 
simpler for users.  Within that constraint, the simplest way to incorporate 
both your own tables plus arbitrary user-defined ones is for you to rename 
the user-defined table names in a reversable fashion, to guarantee their 
uniqueness from yours.  The simplest way to do this is simply add a common 
name prefix to all user-defined names behind the scenes, but hide that 
implementation detail from the user; eg, prepend "udt_" (user defined 
table) to each name the user picks, and make sure that none of your 
application defined tables start with that.  FYI, this practice simulates 
multiple name-spaces / schemas in a single disk file, which SQLite doesn't 
do natively. -- Darren Duncan


_
How good are you in a Formula One car? Play now 
http://server1.msn.co.in/sp05/tataracing/onlinegame.asp

[sqlite] Re: ORDER BY clause with column values not case-sensitive

[Igor Tandetnik]

Dennis Cote <[EMAIL PROTECTED]> wrote:

Srikanth wrote:

Is there a way to order the results of a select statement using the
values of a column name, with the values in that column insensitive?


Add COLLATE NOCASE to the column definition of the country column in
your table.


... or directly in the ORDER BY clause, without modifying the table 
schema:


select * from tableName
order by fieldName collate NOCASE;

Igor Tandetnik 

Re: [sqlite] Database design questions

[Jay Sprenkle]

On 6/27/06, Bud Beacham <[EMAIL PROTECTED]> wrote:

I am using SQLite to add a database engine to a product I hope
to sell.  One thing that is desirable is to allow the customers
to enhance the database for their specific needs.  For example,
they could add new tables to reflect their environment, and then
design queries that look at both the standard and added tables.


I've tried this myself. I've found unless
your customers are technically savvy they won't use it.
Your program is a tool. They want to push button X and get result Y.
They don't care how it works and don't want to know.

If they are technically savvy be prepared for service calls
when they change or remove something your code relies on.

The perfect example was when I let the users choose the
colors of their screen and text. I get a call and the
user says "I set the letters to black and the screen to black, how do
I fix it?"  ;)
The next version was changed to use color palettes and not
let the user pick individual colors. They liked it better and I
didn't get calls.

Give them a screen with fields from the database. Let them enter
their search string into the field they want to search on and generate the
queries yourself.

Re: [sqlite] Database design questions

[Darren Duncan]

At 10:17 AM -0700 6/27/06, Bud Beacham wrote:

I am using SQLite to add a database engine to a product I hope
to sell.  One thing that is desirable is to allow the customers
to enhance the database for their specific needs.  For example,
they could add new tables to reflect their environment, and then
design queries that look at both the standard and added tables.

However, since this all new to me I have two questions.

If I offer an upgrade version with new tables how difficult will
it be for the user to merge their database into the latest
version?  I will insure that any customer table names will not
conflict with my table names.

Would it make more sense to have the customers create a new
database and then design their queries to deal with two
databases?


Keeping everything that belongs together in a single disk file is 
usually simpler for users.  Within that constraint, the simplest way 
to incorporate both your own tables plus arbitrary user-defined ones 
is for you to rename the user-defined table names in a reversable 
fashion, to guarantee their uniqueness from yours.  The simplest way 
to do this is simply add a common name prefix to all user-defined 
names behind the scenes, but hide that implementation detail from the 
user; eg, prepend "udt_" (user defined table) to each name the user 
picks, and make sure that none of your application defined tables 
start with that.  FYI, this practice simulates multiple name-spaces / 
schemas in a single disk file, which SQLite doesn't do natively. -- 
Darren Duncan

Re: [sqlite] Database design questions

[Brian Johnson]

Bud Beacham ([EMAIL PROTECTED]) wrote:
>
> If I offer an upgrade version with new tables how difficult will
> it be for the user to merge their database into the latest
> version?  I will insure that any customer table names will not
> conflict with my table names.
>
> Would it make more sense to have the customers create a new
> database and then design their queries to deal with two
> databases?
>

The difficulty to maintain a link to your changign tables will totally depend
on what data is linked and how much you change them between versions.  ie
nobody can answer that question

I would say that it would be easier for everyone to manage if the customer's
data was in a separate database file.

[sqlite] Database design questions

[Bud Beacham]

I am using SQLite to add a database engine to a product I hope
to sell.  One thing that is desirable is to allow the customers
to enhance the database for their specific needs.  For example,
they could add new tables to reflect their environment, and then
design queries that look at both the standard and added tables.

However, since this all new to me I have two questions.

If I offer an upgrade version with new tables how difficult will
it be for the user to merge their database into the latest
version?  I will insure that any customer table names will not
conflict with my table names.

Would it make more sense to have the customers create a new 
database and then design their queries to deal with two
databases?

[sqlite] Icon for sqlite?

[Edwin Knoppert]

My tool needs an sqlite icon, is there one?
I see a logo only.

:)

Re: [sqlite] ORDER BY clause with column values not case-sensitive

[Srikanth]

Dennis,
It works. Thanks a lot.
Srikanth.

On 6/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote:

Srikanth wrote:
> Is there a way to order the results of a select statement using the
> values of a column name, with the values in that column insensitive?
> E.g., If the column has the following values: Zambia, italy,Iceland,
> then a regular "ORDER BY tablename.country DESC" would result in:
>
> italy
> Zambia
> Iceland
>
> I want my output to be:
> Zambia
> italy
> Iceland.
>
Srikanth,

Add COLLATE NOCASE to the column definition of the country column in
your table.

CREATE TABLE name (
...
countrytext collate nocase,
...
);

HTH
Dennis Cote

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Question remains if it isn't better to go to a new major version on such 
changes.

Forward compatibility is assumed by users imo.



- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, June 27, 2006 1:08 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


Eugene Wee <[EMAIL PROTECTED]> wrote:

Hi,

[EMAIL PROTECTED] wrote:
> The new file format stores boolean values (the integers 0 and 1)
> more efficiently - requiring only 1 bytes of disk space instead of
> 2.  There are no other changes.
>
>
> Let me reemphasize that the new file format has caused so much
> grief that I will likely revert to the older format with 3.4.0.
> That is to say, databases created by 3.4.0 will be readable by
> 3.2.8.  3.4.0 will be able to read and write both the old and the
> new formats, of course.  And you will still be able to use the new
> format using a pragma or a compile-time option.  It just won't be
> the default any more.
>
> I have learned my lesson.  Do not enhance the file format without
> a very good reason.  Saving one byte of space when storing booleans
> is not a sufficiently good reason...

I think that reverting back is not the solution. At the moment,
the news about the change in file format is several entries down
the news and changes list. People may also not be aware that newer
SQLite versions are backwards compatible but not forwards compatible.
If the documentation was clearer on the file format, its changes
and compatibility, an enhancement of the file format may not
cause so much confusion.



You know what - the new file format supports an additional feature
that I completely forgot about: descending indices.  So I suppose
it was worth going to the new format after all.  I put in the
change back in December of last year and had completely forgotten
about the descending index addition.  But it is coming back to
me now.  I added descending indices and said to myself, as long
as I am having to change the file format, I might as well enhance
the boolean value representation too.  But then I completely forgot
about the descending index change.  Silly me



P.S.: any news on when 3.4.0 will be out? :D



I still have not made a final decision on whether it will be
3.3.7 or 3.4.0.  There is no incompatibility so 3.3.7 would
technically be correct.  But there are a lot of enhancements
so I was thinking of going to 3.4.0 just to emphasize the
magnitude of the change.

I'm thinking end of July or early August.

Re: [sqlite] ORDER BY clause with column values not case-sensitive

[Dennis Cote]

Srikanth wrote:

Is there a way to order the results of a select statement using the
values of a column name, with the values in that column insensitive?
E.g., If the column has the following values: Zambia, italy,Iceland,
then a regular "ORDER BY tablename.country DESC" would result in:

italy
Zambia
Iceland

I want my output to be:
Zambia
italy
Iceland.


Srikanth,

Add COLLATE NOCASE to the column definition of the country column in 
your table.


CREATE TABLE name (
   ...
   countrytext collate nocase,
   ...
);

HTH
Dennis Cote

Re: [sqlite] Huge performance difference between SQLite on Windows XP and Windows 2000

[Christian Smith]

Lukáš Neumann uttered:


Hello,

I am using SQLite DLL version 3.2.1 to access a single file database. I 
use sqlite3_exec() to call this simple query:


BEGIN; INSERT INTO Messages (IDMessage, Body) VALUES (1054, 'Test'); 
COMMIT;


When the application runs under Windows XP, the query takes unnoticable 
amout of time (10ms or so), but when I run the very same application 
under clean installation of Windows 2000, the query may take 150ms or 
more. The database size is same on both systems.



How many times did you reproduce the test? Is this test representitive of 
your actual work patterns?


You don't want to benchmark individual INSERTS, then draw conclusions on 
the relative performance of two similar systems based on the miniscule 
results.


Note, also, that WinXP is a later version of Windows than Win2000. It may 
contain performance enhancements that earn't present in the earlier 
version, though I guess the overall performance should be roughly similar.





Could you suggest any way how I can improve the performance under 
Windows 2000?


Thanks in advance
Lukas Neumann




Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] ORDER BY clause with column values not case-sensitive

[Srikanth]

Hi,
Is there a way to order the results of a select statement using the
values of a column name, with the values in that column insensitive?
E.g., If the column has the following values: Zambia, italy,Iceland,
then a regular "ORDER BY tablename.country DESC" would result in:

italy
Zambia
Iceland

I want my output to be:
Zambia
italy
Iceland.

Thanks.
Srikanth.

Re: [sqlite] use of index in ORDER BY clause

[Dennis Cote]

Jens Miltner wrote:

I have a schema similar to this:

CREATE TABLE foo (id integer primary key, name text);
CREATE TABLE bar (id integer primary key, foo_id integer, something 
text);


CREATE INDEX bar_idx on bar(foo_id, something);


When I run a query like

SELECT
foo.id AS foo_id,
bar.id AS bar_id
FROM
bar
LEFT JOIN
foo ON foo.id=bar.foo_id
ORDER BY
bar.something
;

sqlite will only use the primary key index of foo
(as the output of 'explain query plan' shows:

0|0|TABLE bar
1|1|TABLE foo USING PRIMARY KEY

)


If I try to disable the foo primary key index by prefixing foo.id with 
a '+' sign, no index will be used.


I'd have expected the bar_idx index to be used for the ORDER BY 
clause? Or is this some unreasonable assumption?

Is there a way to enforce this?

Thanks,




Jens,

Your query is not using any index to do the order by clause, it is 
sorting the intermediate results.


There is no need to join the foo table in your query since the foo_id is 
available in the bar table. The following is equivalent:


   SELECT
   foo_id,
   bar.id AS bar_id
   FROM
   bar
   ORDER BY
   bar.something
   ;

It still does a sort as shown by the explain output

   sqlite> .explain on
   sqlite> explain SELECT
  ...> foo_id,
  ...> bar.id AS bar_id
  ...> FROM
  ...> bar
  ...> ORDER BY
  ...> bar.something
  ...> ;
   addr  opcode  p1  p2  p3
     --  --  --  
-

   0 OpenVirtual 1   3   keyinfo(1,BINARY)
   1 Goto0   28
   2 Integer 0   0
   3 OpenRead0   3
   4 SetNumColumns   0   3
   5 Rewind  0   15
   6 Column  0   1
   7 Rowid   0   0
   8 MakeRecord  2   0
   9 Column  0   2
   10Sequence1   0
   11Pull2   0
   12MakeRecord  3   0
   13IdxInsert   1   0
   14Next0   6
   15Close   0   0
   16OpenPseudo  2   0
   17SetNumColumns   2   2
   18Sort1   26
   19Integer 1   0
   20Column  1   2
   21Insert  2   0
   22Column  2   0
   23Column  2   1
   24Callback2   0
   25Next1   19
   26Close   2   0
   27Halt0   0
   28Transaction 0   0
   29VerifyCookie0   3
   30Goto0   2
   31Noop0   0
   sqlite> .explain off
   sqlite> explain query plan SELECT
  ...> foo_id,
  ...> bar.id AS bar_id
  ...> FROM
  ...> bar
  ...> ORDER BY
  ...> bar.something
  ...> ;
   0|0|TABLE bar
   sqlite>

To use the index to do the oder by the index must start with the columns 
used to order the output. If you create an index on the bar.something 
column then it will be used to scan the bar table in order rather than 
from start to finish by primary key id.


   sqlite> create index bar_someting on bar(something);
   sqlite> .explain on
   sqlite> explain SELECT
  ...> foo_id,
  ...> bar.id AS bar_id
  ...> FROM
  ...> bar
  ...> ORDER BY
  ...> bar.something
  ...> ;
   addr  opcode  p1  p2  p3
     --  --  --  
-

   0 Noop0   0
   1 Goto0   19
   2 Integer 0   0
   3 OpenRead0   3
   4 SetNumColumns   0   3
   5 Integer 0   0
   6 OpenRead2   5   keyinfo(1,BINARY)
   7 Rewind  2   16
   8 RowKey  2   0
   9 IdxIsNull   0   15
   10IdxRowid2   0
   11MoveGe  0   0
   12Column  0   1
   13Rowid   0   0
   14Callback2   0
   15Next2   8
   16Close   0   0
   17Close   2   0
   18Halt0   0
   19Transaction 0   0
   20VerifyCookie0   4
   21Goto0   2
   22Noop0   0
   sqlite> .explain off
   sqlite>
   sqlite> explain query plan SELECT
  ...> foo_id,
  ...> bar.id AS bar_id
  ...> FROM
  ...> bar
  ...> ORDER BY
  ...> bar.something
  ...> ;
   0|0|TABLE bar WITH INDEX bar_someting
   sqlite>


HTH
Dennis Cote

Re: [sqlite] use of index in ORDER BY clause

[Jay Sprenkle]

On 6/27/06, Jens Miltner <[EMAIL PROTECTED]> wrote:

When I run a query like

SELECT
foo.id AS foo_id,
bar.id AS bar_id
FROM
bar
LEFT JOIN
foo ON foo.id=bar.foo_id
ORDER BY
bar.something



I think it's using the join first to determine what goes in the output set,
then sorting the output set using the ORDER BY.
That seems to be correct to me. It might not be optimal
but that would be extremely difficult to determine before executing
the query.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] sqlite3_free()

[Christian Smith]

Andrew Piskorski uttered:


On Tue, Jun 27, 2006 at 04:14:37PM +0100, Christian Smith wrote:


Anyway, it's not difficult to provide thread local storage. HP-UX's
netdb.h functions (gethostbyname etc.) are fully re-entrant despite
returning 'static' data, for example. Other UNIXs got hamstrung with
various getXbyY_r implementations, with horrible semantics.


Well yes, the *_r functions are often pretty ugly to use.  But they
work great if what you want to do is build your own thread local
storage version on top!

I've always assumed there's some good reason for the existence and use
of *_r functions rather than equivalent thread local storage versions,
although I've never been sure just what it is.



Mainly because the _r functions were hacked by lazy types who couldn't be 
bothered to use TLS (or TLS wasn't available). The _r functions weren't 
particularly well thought out, leaving the client to allocate the storage 
(arguably good) without telling the client how big the storage has to 
actually be (definitely bad). It is this type of implementation issue that 
should be completely hidden from the client, hence my preferred use of TLS 
for 'static' buffers managed by the API.


Grr, I'm definitely sounding like I'm ranting now:)


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] use of index in ORDER BY clause

[Jens Miltner]

I have a schema similar to this:

CREATE TABLE foo (id integer primary key, name text);
CREATE TABLE bar (id integer primary key, foo_id integer, something  
text);


CREATE INDEX bar_idx on bar(foo_id, something);


When I run a query like

SELECT
foo.id AS foo_id,
bar.id AS bar_id
FROM
bar
LEFT JOIN
foo ON foo.id=bar.foo_id
ORDER BY
bar.something
;

sqlite will only use the primary key index of foo
(as the output of 'explain query plan' shows:

0|0|TABLE bar
1|1|TABLE foo USING PRIMARY KEY

)


If I try to disable the foo primary key index by prefixing foo.id  
with a '+' sign, no index will be used.


I'd have expected the bar_idx index to be used for the ORDER BY  
clause? Or is this some unreasonable assumption?

Is there a way to enforce this?

Thanks,

Re: [sqlite] sqlite3_free()

[Andrew Piskorski]

On Tue, Jun 27, 2006 at 04:14:37PM +0100, Christian Smith wrote:

> Anyway, it's not difficult to provide thread local storage. HP-UX's 
> netdb.h functions (gethostbyname etc.) are fully re-entrant despite 
> returning 'static' data, for example. Other UNIXs got hamstrung with 
> various getXbyY_r implementations, with horrible semantics.

Well yes, the *_r functions are often pretty ugly to use.  But they
work great if what you want to do is build your own thread local
storage version on top!

I've always assumed there's some good reason for the existence and use
of *_r functions rather than equivalent thread local storage versions,
although I've never been sure just what it is.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] sqlite3_free()

[Christian Smith]

Dennis Cote uttered:


Christian Smith wrote:


Yes, of course, Windows sticks it's oar in again. Going back to the 
previous DLL discussion, this alone is surely confirmation of why the 
Windows DLL system sucks.


This really has nothing to do with the Windows DLL system. It is simply the 
case that the main application and the SQLite library may be compiled with 
different compilers that use different runtime libraries and therefore 
implement different memory heaps. You can't in general expect memory that was 
allocated from one heap by one runtime library (used by SQLite) to be 
correctly released to another heap maintained by a another runtime library 
used by the application.


Under *nix it is more common, but not required, for applications to link to 
one common runtime library.



Under UNIX it is more common because UNIX provides a runtime system by 
default. Windows programs all ship with their own runtime due to sloppy 
engineering on MS's part. It harks back to the days when each DLL had it's 
own local data segment under Win16. Implementation details from 20 years 
ago biting us in the bum even when the Win32 API doesn't have segments!





For reference (well, for my reference at least) I believe that returned 
memory should be considered static to the database connection, with 
subsequent invocations overwriting the previous contents. That way, all 
management would be internal to the API, and if the client wants a copy, he 
should copy it before the next invocation. This is especially true of such 
things as error strings.


Ack! No! This leads to non-reentrant code. This is the kind of problem that 
the standard asctime() API has. It is much better for the caller to provide 
the memory buffer, or have the library dynamically allocate the buffer and 
pass it back to the caller. In this case you never have to worry about some 
other thread calling the function before your thread has completed its copy.



Static is probably the wrong word. The string is local to the database 
connection, which shouldn't be used by more than one thread without proper 
synchronisation.



Anyway, it's not difficult to provide thread local storage. HP-UX's 
netdb.h functions (gethostbyname etc.) are fully re-entrant despite 
returning 'static' data, for example. Other UNIXs got hamstrung with 
various getXbyY_r implementations, with horrible semantics.





Dennis Cote




Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] Huge performance difference between SQLite on Windows XP and Windows 2000

[Lukáš Neumann]

Hello,

I am using SQLite DLL version 3.2.1 to access a single file database. I use 
sqlite3_exec() to call this simple query: 

BEGIN; INSERT INTO Messages (IDMessage, Body) VALUES (1054, 'Test'); COMMIT;

When the application runs under Windows XP, the query takes unnoticable amout 
of time (10ms or so), but when I run the very same application under clean 
installation of Windows 2000, the query may take 150ms or more. The database 
size is same on both systems.

Could you suggest any way how I can improve the performance under Windows 2000?

Thanks in advance
Lukas Neumann

Re: [sqlite] subtracting days from two dates

[Dennis Cote]

mithin wrote:

Hi,

I want to find if the difference between the days of two dates is less than
or greater than some number. The year is not in the picture and it can be
anything. I gave the following query but it is not working.

SELECT * FROM date_details WHERE (date(date_details.value,%j) -
date('now',%j) < 30)

Can someone pls help me?
Thanks a lot.
  

Try this:

   SELECT * FROM date_details 
   WHERE abs(strftime(date_details.value,'%j') -
	strftime('now','%j')) < 30 



HTH
Dennis Cote

Re: [sqlite] Hardcopy docs?

[Christian Smith]

Bernie Cosell uttered:


On 26 Jun 2006 at 14:14, [EMAIL PROTECTED] wrote:


"Bernie Cosell" <[EMAIL PROTECTED]> wrote:

I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the
sqlite3 docs available in any sort of reasonably-printable format?



http://www.apress.com/book/bookDisplay.html?bID=10130


Hmmm... Since I don't really need 450+ pages of info, but just something
akin to a crib sheet of the SQL that sqlite supports (which I'd guess
would end up at about 20 pages if it weren't so difficultly-embedded in
html files), the option of a fifty dollar book isn't really very useful
to me. [BTW: no knock on Mike or on the worth of the book.  But: I'm not
a beginner [either with Perl, SQL or SQLite] nor do I care hardly at all
about SQLite's internals... I just need to know the SQL SQLite
implements, and so virtually all of Mike's book is really not going to be
of any interest or use to me... I'm just the wrong audience for it].



Try this perhaps?

http://www.tdb.uu.se/~jan/html2ps.html


Just tried (in www directory):

$ tclsh lang.tcl | html2ps > lang.ps

Mostly useable output. A few formatting issues, but will do as a 
reference.


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] sqlite3_free()

[Dennis Cote]

Christian Smith wrote:


Yes, of course, Windows sticks it's oar in again. Going back to the 
previous DLL discussion, this alone is surely confirmation of why the 
Windows DLL system sucks.


This really has nothing to do with the Windows DLL system. It is simply 
the case that the main application and the SQLite library may be 
compiled with different compilers that use different runtime libraries 
and therefore implement different memory heaps. You can't in general 
expect memory that was allocated from one heap by one runtime library 
(used by SQLite) to be correctly released to another heap maintained by 
a another runtime library used by the application.


Under *nix it is more common, but not required, for applications to link 
to one common runtime library.


For reference (well, for my reference at least) I believe that 
returned memory should be considered static to the database 
connection, with subsequent invocations overwriting the previous 
contents. That way, all management would be internal to the API, and 
if the client wants a copy, he should copy it before the next 
invocation. This is especially true of such things as error strings.


Ack! No! This leads to non-reentrant code. This is the kind of problem 
that the standard asctime() API has. It is much better for the caller to 
provide the memory buffer, or have the library dynamically allocate the 
buffer and pass it back to the caller. In this case you never have to 
worry about some other thread calling the function before your thread 
has completed its copy.


Dennis Cote

Re: [sqlite] subtracting days from two dates

[Jay Sprenkle]

On 6/27/06, mithin <[EMAIL PROTECTED]> wrote:


Hi,

I want to find if the difference between the days of two dates is less than
or greater than some number. The year is not in the picture and it can be
anything. I gave the following query but it is not working.

SELECT * FROM date_details WHERE (date(date_details.value,%j) -
date('now',%j) < 30)

Can someone pls help me?



From the wiki:


http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Compute the number of seconds between two dates:
 SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400;

maybe this:

SELECT * FROM date_details
WHERE julianday(value) - julianday('now') < 30






--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] subtracting days from two dates

[mithin]

Hi,

I want to find if the difference between the days of two dates is less than
or greater than some number. The year is not in the picture and it can be
anything. I gave the following query but it is not working.

SELECT * FROM date_details WHERE (date(date_details.value,%j) -
date('now',%j) < 30)

Can someone pls help me?
Thanks a lot.
-- 
View this message in context: 
http://www.nabble.com/subtracting-days-from-two-dates-tf1855577.html#a5066813
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Hardcopy docs?

[Dennis Cote]

Bernie Cosell wrote:



Hmmm... Since I don't really need 450+ pages of info, but just something 
akin to a crib sheet of the SQL that sqlite supports (which I'd guess 
would end up at about 20 pages if it weren't so difficultly-embedded in 
html files), the option of a fifty dollar book isn't really very useful 
to me. [BTW: no knock on Mike or on the worth of the book.  But: I'm not 
a beginner [either with Perl, SQL or SQLite] nor do I care hardly at all 
about SQLite's internals... I just need to know the SQL SQLite 
implements, and so virtually all of Mike's book is really not going to be 
of any interest or use to me... I'm just the wrong audience for it].


I guess I'll work harder at getting something readable out of the 
'lang.html' pages...  It looks like that if I cobble up a little script 
to take the pages linked out of lang.html and just delete the  and 
 blocks at the start of each page it'll get me just the reference 
info, and after that a pass through HTMLDOC and I suspect I'll have 
something more in line of what I was hoping for...  I'll report back on 
how it goes in case anyone cares...   tnx!




  

Bernie,

If you download the source you can build local copies of the HTML files 
that document SQLite. They are slightly different than those on the 
website, all the related documentation pages are linked into one file. 
Simply do a:


./configure --with-tcl=/path/to/your/tcl/
make doc

I then open the file index.html in Firefox. It looks the same as the 
website, so click documentation and then SQL Syntax. The current page 
has all the SQL syntax documentation on one HTML page. I simply print 
the whole thing.


I, too, am a hardcopy guy. I keep the resulting 26 page document "SQL as 
Understood By SQLite" on my desk beside my computer.


HTH
Dennis Cote

Re: [sqlite] sqlite3_free()

[Christian Smith]

[EMAIL PROTECTED] uttered:


Christian Smith <[EMAIL PROTECTED]> wrote:


My own personal opinion on these coding style issues is if the API
requires special handling of cleanup, then the API should do the cleanup.
Returning an allocated string that requires special cleanup results in a
potentially generic operation now being special cased by the API client.



If all the world was Unix, this would work great.  But sadly,
it is not.  We also have to support windows.  See

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

The sqlite_freemem() API is an old SQLite version 2 API that was
added to work around the fact that memory allocated using malloc()
in a DLL cannot be passed to free() in the main program.



Yes, of course, Windows sticks it's oar in again. Going back to the 
previous DLL discussion, this alone is surely confirmation of why the 
Windows DLL system sucks.


My previous rant was really that, just a rant. Given the previous 
interface, you must maintain compatibility, and breaking the old use of 
free() should be acceptable. My own code is not affected, as I already 
used sqlite_freemem (stuck with 2.x for the moment.)


For reference (well, for my reference at least) I believe that returned 
memory should be considered static to the database connection, with 
subsequent invocations overwriting the previous contents. That way, all 
management would be internal to the API, and if the client wants a copy, 
he should copy it before the next invocation. This is especially true of 
such things as error strings.





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



Christian


--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] 2PC / two-phase commit?

[Gerhard Häring]

Eino Mäkitalo wrote:
I am playing with zodb storages and there is some kind of 2pc support. I 
dont know if I really need it
to implement my storage (for fun) zodb over sqlite but at least I was 
thinking possibility to not make those temporary changes programmatic 
way but to use sqlite transactions as they are. [...]


I am pretty confident ZODB storage does not need to support 2PC. Also 
not all storage backends support all features (like versioning, undo, ...).


If you need additional example material, you can look at APE and the 
historical OracleStorage for ancient ZODB releases. See also here for an 
overview of storage backends: 
http://cvs.zope.org/*checkout*/ZODB3/Doc/storages.html?rev=1.10


APE is here: http://hathawaymix.org/Software/Ape

If you can get it to work at all, it should be trivial to add SQLite 
support for it using pysqlite, as it already has support for MySQL, 
PostgreSQL and others.


-- Gerhard

[sqlite] parsing explain output

[Jay Sprenkle]

It occurs to me a program to read explain output and provide suggesions in
english would be a useful thing. Has anyone already done anything
along this line?

Thanks!

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] sqlite and in memory databases

[Jay Sprenkle]

Yes, several others on this forum have done exactly that.
I believe the forum is archived somewhere, but I don't know where.
It might provide some leads.

On 6/26/06, james osburn <[EMAIL PROTECTED]> wrote:

i am looking for a embeddable sql database that i can use to manipulate
memory stored in battery back sram. would sqllite be cable of doing this?

thanks
j osburn






--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] sqlite3_free()

[drh]

Christian Smith <[EMAIL PROTECTED]> wrote:
> 
> My own personal opinion on these coding style issues is if the API 
> requires special handling of cleanup, then the API should do the cleanup. 
> Returning an allocated string that requires special cleanup results in a 
> potentially generic operation now being special cased by the API client.
> 

If all the world was Unix, this would work great.  But sadly,
it is not.  We also have to support windows.  See

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

The sqlite_freemem() API is an old SQLite version 2 API that was
added to work around the fact that memory allocated using malloc()
in a DLL cannot be passed to free() in the main program.

So I do have to provides sqlite3_free() at least, if for no
other purpose than to support windows users.  And notice too
that if you want to your code to be portable to windows, then
you have to use sqlite3_free() to release memory coming back
out of sqlite3_mprintf() or sqlite3_exec().  There appears to
be no way to avoid this due to the limitations of windows DLLs.

The other thing is that since version 3.3.0, SQLite has allowed
an implementation to define its own malloc/free implementation
by overloading the sqlite3OsMalloc() and sqlite3OsFree() interfaces
on the OS-layer.  This is sometimes a useful thing to do.  The
sqlite3_malloc() and sqlite3_free() APIs provide a portable way
to get the application-preferred memory allocator.

The default implementation of sqlite3OsMalloc() is not compatible
with malloc(), however, since it needs to be extended to support
sqlite3AllocationSize() (a capability that is tragically missing
from the standard unix-style malloc()).  

I could continue to provide an sqlite3_free() that is compatible
with free() on unix systems and then provide another set of
routines, sqlite3_osfree(), sqlite3_osmalloc(), sqlite3_osrealloc(),
etc., that provide access to the OS-layer memory allocator.  But
then we would have two separate memory allocation systems which
seems even more confusing than requiring the use of sqlite3_free().
And, notice also that the added complication only allows you to
avoid using sqlite3_free() on Unix platforms - it is of no help
on windows.

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

Re: [sqlite] 2PC / two-phase commit?

[Eino Mäkitalo]

I am playing with zodb storages and there is some kind of 2pc support. I 
dont know if I really need it
to implement my storage (for fun) zodb over sqlite but at least I was 
thinking possibility to not make those temporary changes programmatic 
way but to use sqlite transactions as they are.


According to ZODB storage api manual  (imcomplete and hard to 
understand) I should not "write data immediately" because "if tranaction 
is aborted you should to be prepared to undo". Obviously easiest way to 
implement this kind of behaviour is orginal transactions of DB.


If there are no 2PC in sqlite for this purpose I can implement features 
I need but then I am programming  logic which I'd like to get from 
database. ZODB needs are quite simple but documentation sucks. My goal 
is to be able to imlement as clean as possible zodb over sqlite.


Eino

[EMAIL PROTECTED] kirjoitti:

=?ISO-8859-1?Q?Eino_M=E4kitalo?= <[EMAIL PROTECTED]> wrote:
  

Does sqlite support two-phase commit (2PC)?
Found some hints about it only with virtual tables.




SQLite uses a 2-phase commit internally when it is
making changes to two or more ATTACHed database files.
But that is probably not what you are asking.  What
is it that you really want to know?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

  

Re: [sqlite] 2PC / two-phase commit?

[drh]

=?ISO-8859-1?Q?Eino_M=E4kitalo?= <[EMAIL PROTECTED]> wrote:
> Does sqlite support two-phase commit (2PC)?
> Found some hints about it only with virtual tables.
> 

SQLite uses a 2-phase commit internally when it is
making changes to two or more ATTACHed database files.
But that is probably not what you are asking.  What
is it that you really want to know?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Multibyte

[Anne . Kirchhellen]

Hi to all

I have created my own 2 SQLite-DLL's, one as a Unicode-DLL and the
other one as Multibyte-DLL.

If I create a Test-App with Unicode selected and import the 
Unicode-DLL it works fine. If I do the same, but have Multibyte
select und import the MBCS-DLL, some crazy things happens:
Some in the DB stored (by Test-App) Fields are confused. 
Not all... only some...  That means, all Fields who are 
temporarly stored in a COleVariant are buggy after they written
to DB.

The same MBCS-Test-App works fine, if I import the Unicode-DLL. 
Now I'm really confused.  ;-)

How do it right? Is it ok, if I ever use a Unicode-DLL?

Many thanks and best greetings from Germany
Anne

-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

Re: [sqlite] sqlite3_free()

[Christian Smith]

[EMAIL PROTECTED] uttered:


Two SQLite APIs, sqlite3_exec() and sqlite3_mprintf(), return
strings in memory obtained from a malloc-like memory allocator.
The documentation has always said that you need to use sqlite3_free()
in order to free those strings.  But, as it happens, it has
until now worked to call plain old free().

But that might change.  In the latest code in CVS, if you
disregard the documentation and use free() in place of
sqlite3_free(), it will likely lead to a segfault.  It
might still work depending on how you compile.  But a
segfault is the more likely outcome.

So correct code should continue to work fine.  But broken
code that happened to work before might now really break.



My own personal opinion on these coding style issues is if the API 
requires special handling of cleanup, then the API should do the cleanup. 
Returning an allocated string that requires special cleanup results in a 
potentially generic operation now being special cased by the API client.


While it's too late to change now, this puts the client in the unenviable 
position of needed to copy the string anyway if the string is required 
elsewhere in the client that may not be aware of the special SQLite API 
requirements.





I'm hoping that this change will not have too much adverse
impact.  If you think this change might cause excessive
hardship, please let me know (before the next release!) and
we will consider using (suboptimal) alternatives that allow
the older broken code to continue functioning.  If I do not
hear a sufficiently large outcry, the new code will appear
in the next release.



How is free() sub-optimal? IMHO, malloc/free is not something an API 
should be trying to optimise other than internally and opaquely to the 
API client. You want to block allocate buffers? Fine, do it in SQLite, but 
exporting this to the API is the implementation showing through.


If the client wants to do memory checking, then the developer should link 
against instrumented malloc/free like valgrind or ElectricFence.


As to the actual change, I guess this is trying to optimise the realloc 
case in the future, perhaps? Is this truly a bottleneck? Otherwise, the 
current CVS implementation doesn't add anything.


If this seems like a rant, I'm sorry. I just hate the practice of 
overriding malloc/free because it makes API specific a generic case. 
Memory allocation is something the original C standard library got mostly 
right.





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



Christian


--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] 2PC / two-phase commit?

[Eino Mäkitalo]

Does sqlite support two-phase commit (2PC)?
Found some hints about it only with virtual tables.

Eino

[sqlite] sqlite3_free()

[drh]

Two SQLite APIs, sqlite3_exec() and sqlite3_mprintf(), return
strings in memory obtained from a malloc-like memory allocator.
The documentation has always said that you need to use sqlite3_free()
in order to free those strings.  But, as it happens, it has
until now worked to call plain old free().

But that might change.  In the latest code in CVS, if you 
disregard the documentation and use free() in place of 
sqlite3_free(), it will likely lead to a segfault.  It
might still work depending on how you compile.  But a
segfault is the more likely outcome.

So correct code should continue to work fine.  But broken
code that happened to work before might now really break.

I'm hoping that this change will not have too much adverse
impact.  If you think this change might cause excessive
hardship, please let me know (before the next release!) and
we will consider using (suboptimal) alternatives that allow
the older broken code to continue functioning.  If I do not
hear a sufficiently large outcry, the new code will appear 
in the next release.

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

Re: [sqlite] v3.2.1 and current differences!

[drh]

Eugene Wee <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> [EMAIL PROTECTED] wrote:
> > The new file format stores boolean values (the integers 0 and 1)
> > more efficiently - requiring only 1 bytes of disk space instead of
> > 2.  There are no other changes.
> > 
> > 
> > Let me reemphasize that the new file format has caused so much
> > grief that I will likely revert to the older format with 3.4.0.
> > That is to say, databases created by 3.4.0 will be readable by
> > 3.2.8.  3.4.0 will be able to read and write both the old and the
> > new formats, of course.  And you will still be able to use the new
> > format using a pragma or a compile-time option.  It just won't be
> > the default any more.
> > 
> > I have learned my lesson.  Do not enhance the file format without
> > a very good reason.  Saving one byte of space when storing booleans
> > is not a sufficiently good reason...
> 
> I think that reverting back is not the solution. At the moment, 
> the news about the change in file format is several entries down 
> the news and changes list. People may also not be aware that newer 
> SQLite versions are backwards compatible but not forwards compatible.
> If the documentation was clearer on the file format, its changes 
> and compatibility, an enhancement of the file format may not 
> cause so much confusion.
> 

You know what - the new file format supports an additional feature
that I completely forgot about: descending indices.  So I suppose
it was worth going to the new format after all.  I put in the
change back in December of last year and had completely forgotten
about the descending index addition.  But it is coming back to
me now.  I added descending indices and said to myself, as long 
as I am having to change the file format, I might as well enhance
the boolean value representation too.  But then I completely forgot
about the descending index change.  Silly me

> 
> P.S.: any news on when 3.4.0 will be out? :D
> 

I still have not made a final decision on whether it will be
3.3.7 or 3.4.0.  There is no incompatibility so 3.3.7 would
technically be correct.  But there are a lot of enhancements
so I was thinking of going to 3.4.0 just to emphasize the 
magnitude of the change.

I'm thinking end of July or early August.

Re: [sqlite] v3.2.1 and current differences!

[Eugene Wee]

Hi,

[EMAIL PROTECTED] wrote:

The new file format stores boolean values (the integers 0 and 1)
more efficiently - requiring only 1 bytes of disk space instead of
2.  There are no other changes.


Let me reemphasize that the new file format has caused so much
grief that I will likely revert to the older format with 3.4.0.
That is to say, databases created by 3.4.0 will be readable by
3.2.8.  3.4.0 will be able to read and write both the old and the
new formats, of course.  And you will still be able to use the new
format using a pragma or a compile-time option.  It just won't be
the default any more.

I have learned my lesson.  Do not enhance the file format without
a very good reason.  Saving one byte of space when storing booleans
is not a sufficiently good reason...


I think that reverting back is not the solution. At the moment, the news about 
the change in file format is several entries down the news and changes list. 
People may also not be aware that newer SQLite versions are backwards compatible 
but not forwards compatible. If the documentation was clearer on the file 
format, its changes and compatibility, an enhancement of the file format may not 
cause so much confusion.


Regards,
Eugene Wee

P.S.: any news on when 3.4.0 will be out? :D

Re: [sqlite] Hardcopy docs?

[Dan Kennedy]

> I guess I'll work harder at getting something readable out of the 
> 'lang.html' pages...  It looks like that if I cobble up a little script 
> to take the pages linked out of lang.html and just delete the  and 
>  blocks at the start of each page it'll get me just the reference 
> info, and after that a pass through HTMLDOC and I suspect I'll have 
> something more in line of what I was hoping for...  I'll report back on 
> how it goes in case anyone cares...   tnx!

You can generate a single file containing all the stuff linked by 
lang.html without too much difficulty. You'll need the lang.tcl and
common.tcl files from the www/ directory of the source distro. Try:

mkdir /tmp/sqldocs
cd /tmp/sqldocs
# ... copy common.tcl and lang.tcl to /tmp/sqldocs ...
tclsh ./lang.tcl > sql.html

Then simply launch hv3[1] and view the results. You'll still have to 
figure out how to print the html of course. 

[1] http://tkhtml.tcl.tk/hv3.html http://tkhtml.tcl.tk/hv3.html";>hv3

Dan.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com