Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Arjen Markus]

Fred Williams wrote:
> 
> Well, since you put it that way.  May I go one step farther and request
> that this new Dynamic Type also adhere to "Bankers Rounding" commonly
> implemented as BCD in other so equipped databases.
> 
> For years I have spent countless hours testing  and sweating rounding
> and comparisons using "Float" (Real?) fields because (IMHO) the database
> designers were scientific mathematicians and the world of users were
> 99%^ business types.  So, spare me further anguish... :-)
> 

Hm, being an engineer (and not having too much experience with database,
I immediately admit), I have always thought it was the other way around:
administrative and financial people designing database systems :).

I rather like the idea Richard is trying to get across. Too often people
have been surprised by such awkward behaviour 5/2 becoming 2 or 5.1*5.1
turning out to be 26.00999 instead of 26.01 and the like.

As for banker's rounding: if I understand it correctly, there are at 
least two slightly different systems - an American and a European one.
That problem should be solved too, if you are going to introduce such
behaviour.

Regards,

Arjen

Re: [sqlite] basic question about sqlite

[Dan Kennedy]

> If I designed a sqlite database to be used by a bunch of independent
> applications, for example a bunch of CGI scripts each of which
> opened the database, did some processing, and exited, would
> that be (a) safe (b) effecient ?

I think lots of people have used that pattern successfully.



__ 
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Darren Duncan]

I can think of a very elegant solution to this whole ordeal, which is 
inspired by Perl's way of doing things:  Have *two* division 
operators which have different behaviour and which look different so 
you can tell what will happen where they are used, regardless of 
their operand data types.  Perl is loosely typed by default and so 
has elegant handling down to a fine art, having for example different 
operators for string and numerical comparisons, so you always know 
what will happen regardless of the operand data types.


So I propose for SQLite that any expression having '/' will cast both 
of its operands as reals and the result will be a real.


Also, any expression using instead 'DIV' will cast both of its 
operands as integers (truncating them if necessary, not rounding) and 
return an integer.


To go with that, 'MOD' will cast both operands as integers and return 
the integer modulus.


It works out visually, both 'word' operators use integers and the one 
'symbol' operator uses reals.


Sure there's a difference, and while this should help an implementer, 
it is useful to users because it describes *behaviour*.


On a different matter ...

At 9:59 PM -0500 11/1/05, [EMAIL PROTECTED] wrote:

John Stanton <[EMAIL PROTECTED]> wrote:


 Users love such a number system because it is natural and works like the
 Arithmetic they learned in Grade School.

 I find the idea of dividing two integers stored in binary form and
 getting a gratuitous conversion to floating point ugly and potentially
 very annoying.


I admit that it has been 4 decades since I was in grade school,
but back in the 60's we were taught that 5/2 is 2.5.  Are they
teaching something different now?  Or have I misunderstood the
comment?


Well, if you want to know ...

In the youngest grades, such as kindergarten and grade 1 etc, they 
only work with whole numbers, such as when dividing up apples or 
oranges, so 5/2 is "2 with 1 remainder".  Only in later grades do 
they start with fractional numbers, such as 5/2 is "2.5" or "2 and 
1/2".


So young children actually get it both ways depending on their ages.

-- Darren Duncan

Re: [sqlite] Re: Number of rows in a query result

[Eric Bohlman]

Edward Wilson wrote:

What I was trying to say was: with other db products the drivers (or something 
somewhere)
calculated the number of rows returned in a query automagicly.  I have never 
had to do anything
'extra' to get the number of rows in a query other than 
resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, 
I have always taken
for granted that the rowcount was 'apart of' the query returned from the 
database and not
something that I had to do 'extra' in addition to fetching the data to begin 
with.  I hope this
was clear.


I think you'll find that any interface involving a "resultset object" is 
really a wrapper that talks to the database at a fairly low level and 
actually retrieves all the selected rows into its internal memory, later 
parcelling them out in response to method calls.  Of course it can count 
the rows as it retrieves them and make the count available through a 
method or variable.  SQLite's native API actually corresponds to the 
low-level communication between the wrapper and database; the row 
counting would be done by the code that calls the API.  I believe that a 
while back drh strongly implied that most substantial code should be 
accessing SQLite via a wrapper rather than the "raw" API.

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Lindsay]

[EMAIL PROTECTED] wrote:

  I admit that it has been 4 decades since I was in grade school,
but back in the 60's we were taught that 5/2 is 2.5.  Are they
teaching something different now?  Or have I misunderstood the
comment?
  


Ah, but how does the 5 *feel* about being divided by 2 ? is the 2
oppressed by being under the 5 ? how is this the fault of the
patriarchy ? discuss

-- 

Lindsay

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

John Stanton <[EMAIL PROTECTED]> wrote:
> 
> Users love such a number system because it is natural and works like the 
> Arithmetic they learned in Grade School.
> 
> I find the idea of dividing two integers stored in binary form and 
> getting a gratuitous conversion to floating point ugly and potentially 
> very annoying.

I admit that it has been 4 decades since I was in grade school,
but back in the 60's we were taught that 5/2 is 2.5.  Are they
teaching something different now?  Or have I misunderstood the
comment?

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

Re: [sqlite] Thanks for making my product possible

[Edward Wilson]

Ditto - sqlite is pure beauty - thanks.

--- Clay Dowling <[EMAIL PROTECTED]> wrote:
> Amid all the wailing and gnashing of teeth I thought that I'd just say 
> thanks for making a great embeddable database that puts a very minimal 
> burden on the developer.  My product, at least, would never have seen 
[snip]






__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[John Stanton]

Rather than get caught in a mess of integers and floating point numbers 
and the consequent unpredictable results, how about having some typing, 
such as integers and floats and also a decimal fixed point type which 
looks like a text field and displays like one but which is much more 
compatible with the flexible typing of Sqlite.  Arithmetic on display 
format fields like that is not fast, but overall it is not bad when you 
take into account the absence of radix changes when you are inputting 
and outputting such numbers, often the most frequent activity on numbers 
in a general purpose database.


Users love such a number system because it is natural and works like the 
Arithmetic they learned in Grade School.  As I read the SQL spec it is 
not excluded.


I find the idea of dividing two integers stored in binary form and 
getting a gratuitous conversion to floating point ugly and potentially 
very annoying.

JS

Fred Williams wrote:

Ah the sticky wicket that is "Type less" :-)  We now have issues
evolving as a direct result of that feature in our cute little database.
We now seem to have by backing into it: Really Restricted Integer, Real,
DateTime (sort of), and Text.  BLOB and CLOB away at your own risk!

Is it time to officially declare/fully support some Types and clear the
air?

Fred



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 01, 2005 9:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2


"Preston Zaugg" <[EMAIL PROTECTED]> wrote:


As was discussed in the original
post this would be NON-STANDARD behavior.
The SQL-99 spec says that integer
math remains an integer.



The change I propose (and have now checked into CVS, btw,
though I might still back it out) does not violate this
specification.

Think of it this way:  SQLite supports only a single
numeric type which is REAL.  We call that type "numeric".
But the type represents what we normally think of as real
numbers.

We permit integer values to be read from and written to
the database as a convenience to the user.  And internally,
some values are sometimes kept as machine integers for
computational  and storage efficiency.  But that is only
an optimization. At the end of the day, there is only a
single numeric data type and that type is real.

An INTEGER PRIMARY KEY column seems like an exception to
this rule.  But perhaps not.  Think of an INTEGER PRIMARY
KEY column as holding a numeric value with restrictions.
It is as if we added to every INTEGER PRIMARY KEY named
"x" the following check constraint:

  CHECK( x >= -9223372036854775808
 AND x <= 923372036854775807
 AND x == round(x) )

So the values going in and out of an integer primary key
are still real values.  It just happens that their range
is restricted somewhat and they do not have a fractional
part.

If you look at things from this point of view, SQLite
does not support integer values.  And so we never have
to worry about integer division.

For complete consistency, I suppose we might want to
modify the built-in typeof() function to always return
"numeric" instead of "integer".  I wonder

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

[sqlite] basic question about sqlite

[Dave Dyer]

If I designed a sqlite database to be used by a bunch of independent
applications, for example a bunch of CGI scripts each of which
opened the database, did some processing, and exited, would
that be (a) safe (b) effecient ?

[sqlite] Thanks for making my product possible

[Clay Dowling]

Amid all the wailing and gnashing of teeth I thought that I'd just say 
thanks for making a great embeddable database that puts a very minimal 
burden on the developer.  My product, at least, would never have seen 
the light of day without SQLite.  I intend to continue using SQLite into 
the future, no matter what SELECT 5/2 returns in its result set.


Clay Dowling
--
CeaMuS, Simple Content Management
http://www.ceamus.com

Re: [sqlite] Re: Number of rows in a query result

[Edward Wilson]

What I was trying to say was: with other db products the drivers (or something 
somewhere)
calculated the number of rows returned in a query automagicly.  I have never 
had to do anything
'extra' to get the number of rows in a query other than 
resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, 
I have always taken
for granted that the rowcount was 'apart of' the query returned from the 
database and not
something that I had to do 'extra' in addition to fetching the data to begin 
with.  I hope this
was clear.

-
ed

--- Puneet Kishor <[EMAIL PROTECTED]> wrote:

> 
> On Oct 31, 2005, at 7:54 PM, Edward Wilson wrote:
> 
> >> I simply count the number of elements in my record set
> >> thereby avoiding a double query to the database.
> >
> > Yes, exactly, I take for granted that the resultset is accumulated at 
> > the database level and not
> > at the application level.
> 
> sorry, I don't quite understand what you imply by the above. Obviously 
> this discussion stems from the fact that you can't take that for 
> granted, at least not without paying some cost for it. Because I don't 
> want to tie up the db doing double queries, I just do it in the 
> application.
> 
> 
> 
> >
> >
> > -
> > ed
> >
> > --- Puneet Kishor <[EMAIL PROTECTED]> wrote:
> >
> >>
> >> On Oct 28, 2005, at 7:20 PM, SRS wrote:
> >>
> >>> Edward Wilson wrote:
> >>>
>  The idea of issuing two selects is, well, a hack, and knowing how
>  many records one has in a
>  result-set is a powerful feature
> 
> >>>
> >>> Are you needing a progress bar for the search (ie the query?) Or some
> >>> action based on the result set?  If the later, get the result set as
> >>> your favorite container.. ask the container the size.  If its the
> >>> first then a "feature" won't help.  It still has to 'run' the query 
> >>> in
> >>> order to get the count.  It would be like me asking you to tell me 
> >>> how
> >>> many red Skittles are in a package before you open it. As for being a
> >>> 'hack' .. all your 'feature' would be is a pretty programming
> >>> interface around that hack.  As I said before, how can the database
> >>> know the number of items that will be returned without first 
> >>> searching
> >>> for them.
> >>>
> >>
> >> I think the problem is not so much (at least IMHO) that two queries
> >> have to be performed (that itself is a reasonable expectation), but
> >> that the COUNT(*) query is likely to be slow because of the full table
> >> scan. One option is to use an aftermarket solution... for example, in
> >> my Perl applications once I have queried the db for the columns based
> >> on my criteria, I simply count the number of elements in my record set
> >> thereby avoiding a double query to the database. Although, in reality,
> >> I personally don't mind the COUNT(*) option... none of my databases 
> >> are
> >> that large to merit worrying about this.
> >>
> >>
> >
> >
> >
> > 
> > __
> > Start your day with Yahoo! - Make it your home page!
> > http://www.yahoo.com/r/hs
> 
> 




__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

Re: [sqlite] Re: OCaml binding for SQLite 3

[Florian Weimer]

* Toby Allsopp:

> On 30 Oct 2005 at 03:14 NZST, Florian Weimer wrote:
>
>> Is it true that there isn't yet a binding to SQLite versoin 3 from
>> Objective Caml?
>>
>> (I only found one for version 2.)
>
> There seem to be a couple of different ocaml-sqlite3 bindings around:
>
> http://www.imada.sdu.dk/~bardur/personal/45-programs/ocaml-sqlite3/
> http://metamatix.org/~ocaml/ocaml_sqlite3.html
>
> I found these by typing "ocaml-sqlite3" into Google.  

I don't use Google anymore. 8-( Thanks for the pointer.

I completely forgot that monotone-viz also includes a binding
("mlsqlite").  Hmm, it seems that each one lacks something one of the
others offers.

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Joe Wilson]

The more I think about it, the proposed unified numeric model makes 
a lot of sense and should be the default with no pragmas or compile-time 
options for the old behavior. The dynamic manifest typing model of Sqlite 
practically begs to have uniform mathematical results given the 
dynamic nature of the operands:

  SQLite version 3.2.7
  Enter ".help" for instructions
  sqlite> create table foo(a);
  sqlite> insert into foo values(7);
  sqlite> insert into foo values(7.0);
  sqlite> select a/6 from foo;
  1
  1.17

If the columns of tables in Sqlite would strictly enforce types - 
this would be a different matter.  But since they do not (and will not), 
the proposed behavior is more logically self-consistent.




__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Darren Duncan]

Also, regarding the name change suggestions, I disagree.

As people have said, no database product is fully SQL standard 
compliant, and SQLite is no different in that regard.


So in the current environment, SQLite's name is *not* misleading, 
despite any deviations.


The name is a brand anyway, and brands transcend any meaningfulness.

It should stay the same.

-- Darren Duncan

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Darren Duncan]

To answer Richard's question directly:

I do not see the proposed change causing any hardship for me.

I happen to like static typing myself due to its ability to help 
prevent bad data from propagating, with explicit variadic data type 
for people that don't want to choose a more restrictive type, but 
SQLite already isn't statically typed, so this change won't make 
things any worse.


Under the circumstances, this change will actually be an improvement 
to useability as I see it.


We have round() or truncate() or CAST when we need integer division.

-- Darren Duncan

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Jay Sprenkle]

On 11/1/05, René Tegel <[EMAIL PROTECTED]> wrote:
> Jay Sprenkle wrote:
>
> >If you're going to become less compliant perhaps it would be less
> >misleading to remove the "SQL" from the project name. I'm not saying
> >either is a bad idea, just that the name shouldn't be misleading.
> >
> >
> I think this is kind of 'purist fetisjism'. Personally I like the
> pragmatic approach more: make thinks work the way you want it to work.
> Btw calling SQLite not worth the letters 'SQL' is imho just lame..


Hey Rene,

I think having my suggestion called "lame" and "purist fetishism"
was rude and uncalled for.

I have no problem with DRH's proposal either.  I didn't intend to
imply it's of less worth than SQL, merely that the name was misleading
if it wasn't going to be standards compliant.

How about this instead: Offer an "SQLite" and an "SQLite Classic".

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Joe Wilson]

I don't see any current integer behavior that cannot be emulated 
with the new unified numeric type, but you might have to document 
the behavior of mathematical operators under the new system. 
Is modulus an integer operation or a floating point operation, for 
example? Arguably, it could be either.

  sqlite> select 15.3 % 8;
  7.0
  sqlite> select 15.3 % 8.7;
  7.0
  sqlite> select 15.3 % -8.7;
  7.0
  sqlite> select 73 % 9.17;
  1.0
  sqlite> select -19.4 & 7;
  5
  sqlite> select substr("abcdefghijklmop", 63/8, 3.99);
  ghi

--- [EMAIL PROTECTED] wrote:
> Suppose SQLite were to merge "integer" and "real" into a single
> datatype "numeric" that always worked the same way.  Does
> anybody know of a (real) usage example where this would cause
> an actual hardship on developers?  Are there any examples of
> things that you can do with separate "real" and "integer"
> types that you cannot do with a unified "numeric" type?




__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Jackson, Douglas H]

I'd like to second Dennis' earlier remarks, and add some of my own.

 

One of my greatest hopes when I found SQLite (it's "value proposition")
was in having a great little database that would operate in such a
familiar way.  By "familiar", I mean that it should execute the SQL
language, and produce (for the same DDL, and the same DML) the same
result as other SQL implementations.  In short, I expected it to comply
with standards.

 

Using standards is a two-way street:

* I should be able to bring existing structures, and existing
code from SQL Server, or Informix, or PostgreSQL, and have it not only
run, but produce the same result.

* I also want to use SQLite to create and test code that I may
later take to one of these other platforms, and I'd like it to run the
same there as it has been running in SQLite.

The better it allows me to do both of the above, the more valuable it is
to me.


My friends and I joke that one of the greatest things about standards is
that everyone gets to choose their own. I'd call SQLite's manifest
typing one of "it's own".  It is one of SQLite's greatest strengths, as
well as it's greatest weakness.  Being able to informally type a field
is awesome, when I choose to use it.  But it is a weakness when it
influences a result unexpectedly (when it doesn't allow me to choose MY
own standard).

 

Most of my difficulty, and my greatest disappointments in using SQLite
to-date has been where I got unexpected results when it did not strictly
heed my formal DML instructions.  My specific challenges have been with
char(n)'s, but I think the learning applies equally to the discussion of
real/int/numeric.

 

Paradoxically, its greatest opportunity to grow and become more valuable
to me is in allowing me the flexibility to call upon it to behave more
strictly standard.

 

By "strictly standard", I mean:

* If I specify "INT", in my DML, I'd like it to behave exactly
as an INT in other implementations.  If it would like to abbreviate the
value for compactness of storage, that's great.  But I don't want it to
store, nor return anything that behaves differently from an int.

* Likewise FLOAT/REAL.  I really don't care if SQLite stores it
as a machine float, as an IEEE float, or as text.  But if the field is a
float, I don't want it to store, nor ever return anything that behaves
differently from a float.

* Likewise CHAR(n).  It should not store, nor ever return any
more than n characters.

 

In situations where informal typing applies well, I'd like to be able to
select this behavior explicitly.  For example, by defining a column as
VARIANT, or NUMERIC.

 

If the "power" of manifest typing is its ability to recognize a value
and properly convert it, and store it in a form as compact as it likes
-- can that power be leveraged to retrieve a value, no matter how
stored, and properly convert it back to behave exactly as the type it is
expected to be, rather than as the type it was coerced to for storage?

 

To sum it up:

I place more value in how the fields behave than in how they're stored.

I'd like more control, not less, in how they behave.

 

Doug

 

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 01, 2005 10:55 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

 



 

With the above clarification, I will restate the question:

Suppose SQLite were to merge "integer" and "real" into a single

datatype "numeric" that always worked the same way.  Does

anybody know of a (real) usage example where this would cause

an actual hardship on developers?  Are there any examples of

things that you can do with separate "real" and "integer"

types that you cannot do with a unified "numeric" type?

 

--

D. Richard Hipp <[EMAIL PROTECTED]>

 

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Dennis Cote]

[EMAIL PROTECTED] wrote:


Jay Sprenkle <[EMAIL PROTECTED]> wrote:
 


On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
   


All of the arguments against my proposed changes are
basically of the form that "this is not what the SQL standard
says".  They are theoretical arguments based on a world-view
that holds that strong-typing is good and that it is the duty
of database systems to enforce types.

I do not hold to that world view.  SQLite seeks a better way.
 


I think it's a bit misleading to call the library "SQL-Lite: if you're
going to redesign
and not be like SQL. What do you think about creating a separate
project for a fast
light database engine that's not "SQL Like"?
   



SQLite is very much SQL.  It only deviates from the standard
to fix bugs in the original design of the standard.
--
D. Richard Hipp <[EMAIL PROTECTED]>


 


Richard,

This particular point is devolving into a semantic argument, so I 
hesitate to continue down this road, but ... A language like SQL is 
defined by its standard (warts and all).


If you develop something that conforms to some but not all of the 
standard, especially if the non-conformities are by design, the best you 
can say is that it is close to the standard.


Further, I would suggest that the arguments against your change are more 
along the lines of "standards are good" rather than "strong typing is 
good". Many people have learned the hard way that using multiple 
implementations of something that are close to, but not quite standard, 
often leads to problems. The C language had this problem before ANSI 
standardization (and the release of conforming or nearly conforming 
compilers by almost all vendors). The same source often produced 
different results with different compilers. The use of language 
extensions (i.e. fixes to design omissions) is also often fraught with 
trouble.


If you set out to fix "bugs" in the design of a language, you are really 
designing a new language. This is why we have C# , C++, and D (or even C 
itself, which was a redesign of BCPL). They are all languages that were 
developed to fix bugs that their creators saw in the C language or one 
of its other derivatives.


The type of gratuitous non-conformance that you have proposed is almost 
certainly going to lead to interoperability problems between SQLite and 
other SQL implementations. It won't necessarily make SQLite itself 
better or worse than the others, simply different. That difference will 
then need to be allowed for by code that deals with different SQL 
implementations.


This is your project (and I thank you very much for your work on it) so 
it is ultimately your decision how SQLite will work. I just think that 
you should very carefully consider any changes that will lead to less 
conformance with the standard for the language that you are trying to 
implement.


Dennis Cote

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[René Tegel]

Jay Sprenkle wrote:


If you're going to become less compliant perhaps it would be less
misleading to remove the "SQL" from the project name. I'm not saying
either is a bad idea, just that the name shouldn't be misleading.
 

I think this is kind of 'purist fetisjism'. Personally I like the 
pragmatic approach more: make thinks work the way you want it to work. 
Btw calling SQLite not worth the letters 'SQL' is imho just lame..


If you'd be so kind, please name one (or more) SQL-based database 
engines that comply to the 'standard SQL' you are expecting to see.


If you allow me to take, for comparison, MySQL. It has ignored standards 
in favour of practical features ever since the project started. Still it 
is one of the most populair engines.


I am sure there are possible improvements regarding type affinity, 
dynamic columns etc which should all be taken into considuration. But as 
far as i can see Richard Hipp's proposal seems just fine, 
(backward)compatible and solves a couple of  issues and inconveniences.



best regards,

Rene

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Andrew Piskorski]

On Tue, Nov 01, 2005 at 01:45:04PM -0600, Jay Sprenkle wrote:

> > > I think it's a bit misleading to call the library "SQL-Lite: if
> > > you're going to redesign and not be like SQL. What do you think
> > > about creating a separate project for a fast light database
> > > engine that's not "SQL Like"?
> >
> > SQLite is very much SQL.  It only deviates from the standard
> > to fix bugs in the original design of the standard.
> 
> If you're going to become less compliant perhaps it would be less
> misleading to remove the "SQL" from the project name. I'm not saying
> either is a bad idea, just that the name shouldn't be misleading.

Your name "Jay" appears to derive from the Sanskrit "jaya", meaning
"victory".  I find this a bit misleading, as clearly anyone regularly
wasting time with suggestions like yours is unlikely to achieve
victory in anything.  I therefore suggest that you change your name
from "Jay" to something less misleading.

Just a thought.

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

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Peter Bierman]

At 1:54 PM -0500 11/1/05, [EMAIL PROTECTED] wrote:

Please let me restate the proposed change:  I desire to change
SQLite so that it no longer distinguishes between integer and
real.  The two concepts are merged into a unified "numeric"
type.



And because all number values are of the same type, they
need to always be treated in the same way.  A division should
return a result that includes the fractional part, if there
is one, regardless of the particular representation of the
operands.


I'm not a DB person by trade, I'm a programmer. I have grown to love 
SQLite for it's very 'C' like philosophy of making the underlying 
mechanisms transparent.


Part of that is the manifest typing, which lets me control exactly 
what's going into the database, and lets me optimize that for best 
efficiency.


My $0.02 is that combining INTEGER and REAL is the wrong direction. 
It takes information away, information that sqlite is perhaps unique 
in preserving.


It seems that the column affinity mechanism already holds the answer 
to this problem. If a column could have REAL affinity, then whatever 
data was stored in that column would have an opportunity to act 
according to the SQL standard, without losing metadata about the 
original data.


http://www.sqlite.org/datatype3.html
says:
A column that uses INTEGER affinity behaves in the same way as a 
column with NUMERIC affinity, except that if a real value with no 
floating point component (or text value that converts to such) is 
inserted it is converted to an integer and stored using the INTEGER 
storage class.


I propose a similar affinity be added called "REAL", that would 
behave the same as NUMERIC, except that INTEGERs would be converted 
to REAL.



Also, I'm surprised that the column affinity isn't applied when the 
data is read from the database vs inserted into the database, but 
that's a separate topic.


-pmb

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Jay Sprenkle]

> > I think it's a bit misleading to call the library "SQL-Lite: if you're
> > going to redesign
> > and not be like SQL. What do you think about creating a separate
> > project for a fast
> > light database engine that's not "SQL Like"?
>
> SQLite is very much SQL.  It only deviates from the standard
> to fix bugs in the original design of the standard.

If you're going to become less compliant perhaps it would be less
misleading to remove the "SQL" from the project name. I'm not saying
either is a bad idea, just that the name shouldn't be misleading.

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Paul G]

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, November 01, 2005 2:08 PM
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

> I think it's a bit misleading to call the library "SQL-Lite: if you're
> going to redesign
> and not be like SQL. What do you think about creating a separate
> project for a fast
> light database engine that's not "SQL Like"?

strict typing is not, to most people at least, a core feature of sql - the
syntax and semantics of operations (ie selects+joins etc) are. as drh
stated, strict typing was originally a performance
enhancement/implementation complexity reduction measure. the difference
between what sql prescribes and what sql does will be apparent to developers
at two points: a) schema definition and b) operation semantics expectation
formulation.

imo, the former is a non-issue, since pretty much each rdbms will have
significant differences there; sqlite would indeed probably be one of the
easiest one to adapt to in this regard due to the short list of possible
types.

the latter issue is the one that needs to be discussed and indeed the
one drh is trying to get at with his question. is asking developers to
change their mindset when working with sqlite and expect, say, a number with
a fraction back from a division of two integers too much? this question
should also be posed to the authors of language-specific bindings, since
this could be a hassle in strictly typed languages.

regardless, sqlite's fudging of type handling does not make it not look like
sql, although it's pretty obvious it's noncompliant in those respects. this
is not a bad thing - following braindead standards faithfully is a braindead
design. (disclaimer: assuming that the deviation is less braindead).
besides, if you want to get into linguistic interpretations, 'sqlite' could
be interpreted both as 'lightweight sql rdbms' and as 'rdbms conforming to a
reduced, lightweight subset of sql'.

imo, provided that there is a prominenent 'You are standing on a chair and
wearing a noose around your neck!' notice given to folks, i don't see this
as a problem.

-p

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Clay Dowling]

Jay Sprenkle said:
> On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> All of the arguments against my proposed changes are
>> basically of the form that "this is not what the SQL standard
>> says".  They are theoretical arguments based on a world-view
>> that holds that strong-typing is good and that it is the duty
>> of database systems to enforce types.
>>
>> I do not hold to that world view.  SQLite seeks a better way.
>
> I think it's a bit misleading to call the library "SQL-Lite: if you're
> going to redesign
> and not be like SQL. What do you think about creating a separate
> project for a fast
> light database engine that's not "SQL Like"?

I have an even better proposal:

When you're writing your code, use tools appropriate to the job.  For
instance, languages like C, Pascal and BASIC are really good and handling
arithmetic.  Likewise, SQL and database engines are really good at data
storage.  So when you need to divide 5 by 2, it probably makes a lot of
sense to do that in C/Pascal/BASIC than in SQL.  And sure, there's cases
where you can't avoid it.  But usually you can.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > All of the arguments against my proposed changes are
> > basically of the form that "this is not what the SQL standard
> > says".  They are theoretical arguments based on a world-view
> > that holds that strong-typing is good and that it is the duty
> > of database systems to enforce types.
> >
> > I do not hold to that world view.  SQLite seeks a better way.
> 
> I think it's a bit misleading to call the library "SQL-Lite: if you're
> going to redesign
> and not be like SQL. What do you think about creating a separate
> project for a fast
> light database engine that's not "SQL Like"?

SQLite is very much SQL.  It only deviates from the standard
to fix bugs in the original design of the standard.
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Fred Williams]

Well, since you put it that way.  May I go one step farther and request
that this new Dynamic Type also adhere to "Bankers Rounding" commonly
implemented as BCD in other so equipped databases.

For years I have spent countless hours testing  and sweating rounding
and comparisons using "Float" (Real?) fields because (IMHO) the database
designers were scientific mathematicians and the world of users were
99%^ business types.  So, spare me further anguish... :-)

Fred

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 12:55 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
...
> Please let me restate the proposed change:  I desire to change
> SQLite so that it no longer distinguishes between integer and
> real.  The two concepts are merged into a unified "numeric"
> type.  Just as the same string can be represented as either
> UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number
> be represented as integer or real.  But regardless of the
> particular representation chosen at any particular moment,
> the "type" of the data is always "numeric".  There is no
> "integer".  There is no "real".  There is only "numeric".
> And because all number values are of the same type, they
> need to always be treated in the same way.  A division should
> return a result that includes the fractional part, if there
> is one, regardless of the particular representation of the
> operands.
>
> This a simplification designed to make your life easier.  No
> longer are there different rules to apply depending on whether
> a value or a column is "integer" or "real".  Everything always
> works the same way.  The goal is to have no special cases.
>
...
>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Jay Sprenkle]

On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> All of the arguments against my proposed changes are
> basically of the form that "this is not what the SQL standard
> says".  They are theoretical arguments based on a world-view
> that holds that strong-typing is good and that it is the duty
> of database systems to enforce types.
>
> I do not hold to that world view.  SQLite seeks a better way.

I think it's a bit misleading to call the library "SQL-Lite: if you're
going to redesign
and not be like SQL. What do you think about creating a separate
project for a fast
light database engine that's not "SQL Like"?

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Preston Z]

There is an ongoing debate on this subject at Lambda the Ultimate. One
approach that is sound is to introduce a new static type, called
DYNAMIC, that permits any value. Summarizing and seconding Dennis
Cote's suggestion, perhaps columns that have no type declared, or that
are declared DYNAMIC, behave as SQLite does today, and columns that
have static declarations behave as if they are statically typed.


I like that suggestion, but I am sure that this is beyond the scope of the 
question.


Truth be told, manifest typing has never been a “selling point” (if I can 
use that term for free software) for me, rather it has been something to be 
aware of / work around. I know at one point there was talk of a “strict” 
affinity mode for sqlite (at least I saw something that indicated that 
somewhere on the website: http://www.sqlite.org/datatype3.html bullet point 
6.) I personally would love to see a strict affinity mode db with the 
inclusion of a dynamically typed column that can still be used.



BTW: a dynamically typed column is actually included in MSSQL 2005.


ALso is there a "roadmap" type document that talks about the future of 
sqlite?


--Preston

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

All of the arguments against my proposed changes are
basically of the form that "this is not what the SQL standard
says".  They are theoretical arguments based on a world-view
that holds that strong-typing is good and that it is the duty
of database systems to enforce types.

I do not hold to that world view.  SQLite seeks a better way.
You may or may not agree that the SQLite way is better (I
happen to think that it is, of course) but that is beside
the point.  What I want to know is whether or not my proposed
changes will cause serious hardship to any current or future
SQLite users.

Please let me restate the proposed change:  I desire to change
SQLite so that it no longer distinguishes between integer and
real.  The two concepts are merged into a unified "numeric" 
type.  Just as the same string can be represented as either
UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number
be represented as integer or real.  But regardless of the
particular representation chosen at any particular moment,
the "type" of the data is always "numeric".  There is no
"integer".  There is no "real".  There is only "numeric".
And because all number values are of the same type, they
need to always be treated in the same way.  A division should
return a result that includes the fractional part, if there 
is one, regardless of the particular representation of the
operands.

This a simplification designed to make your life easier.  No
longer are there different rules to apply depending on whether
a value or a column is "integer" or "real".  Everything always
works the same way.  The goal is to have no special cases.

I'm trying do all this while at the same time maintaining
reasonable compatibility with other SQL database engines.
SQLite will never be 100% compatible with other database
engines.  But on the other hand, I dare say you cannot find
any two other SQL database engines that are 100% compatible
with each other.  Most claim SQL compatibility, but you can
always find a corner case where two different engines will
give divergent results.  So even though SQLite is not 100%
compatible, neither is any other database engine.

With the above clarification, I will restate the question:
Suppose SQLite were to merge "integer" and "real" into a single
datatype "numeric" that always worked the same way.  Does
anybody know of a (real) usage example where this would cause
an actual hardship on developers?  Are there any examples of
things that you can do with separate "real" and "integer"
types that you cannot do with a unified "numeric" type?

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

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Hamid Benhocine]

Dennis Cote wrote:




The second issue is demonstrated by the last last three statements.

 CREATE TABLE t (a REAL, b REAL);
 INSERT INTO t VALUES (5, 2);
 SELECT a / b FROM t;

Here he has explicitly declared the columns a and b to be of type 
real. He then stores integer literal values into those columns. This 
is where the problem occurs, not during the division in the select 
statement.


SQL is a typed language. SQLite was originally an untyped 
implementation of SQL. In version 3 SQLite was changed to introduce 
stronger data typing, while still trying to maintain compatibility 
with its previous untyped versions. It does this very well in most 
cases. SQLite uses manifest typing, where each data value has its own 
associated data type whereas the standard assumes each column has a 
data type. In a few cases SQLite bumps into areas where this 
implementation produces non-standard behavior. This is one of them.




To produce standard behavior, integer values stored into columns of 
type real should be converted to real values.



This is actualy the problem we have,
even we take care of inserting /or importing data doing the right 
bindings (to fellow the example above)

and it's diffult to care of everything ..

  rc = sqlite3_prepare(db, "insert into t values (?,?);", -1, , 0);
  if (rc == SQLITE_OK && stmt != NULL ) {
sqlite3_bind_double(stmt, 1, 5);
sqlite3_bind_double(stmt, 2, 2);
sqlite3_step(stmt);
rc = sqlite3_reset(stmt);
  }
  rc = sqlite3_finalize(stmt);
sqlite3> select a,b,(a/b) from t
5.0|2.0|2.5 Ok.


when a user issue an update command (i have no control on this command)
on the two columns with

sqlite3> update set a= 5, b =2;
sqliote3> select a,b,(a/b) from t;
5|2|2  which is not correct.

But i don't see a problem when explictly doing 5/2 gives 2. AS 5 and 2 
are integers.


regards
hamid

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Fred Williams]

Thank you for your insight into the state of SQLite "Typing."  I think
this further exposes the need to establish our level of conformance to a
pretty much pervasive database standard (SQL.)  I'm not certain that IBM
was the initiator of the term "Gray Area" but they certainly have
greatly increased it's influence at least in the world of computers.

In every IBM manual I ever used there was always the Standard, white
back grounded "Standard" text and a very large portion of "Gray Area,"
gray background text, commonly referred to as "Enhancements" or
"Extensions."

If one avoided the gray areas, theoretically the exercises undertaken
were "Platform independent."

Perhaps if we intend to venture beyond the "Standard" (SQL) it might be
time to put a little prominent "Gray" on the Wiki Pages.  And, as IBM
did/does allow one to error in his/her own choice of death by
"Enhancement."

Personally I always have thought the int -> int = int can be an extreme
pain in the ...  But, I know about it and have adapted.  The same can be
said for "Dynamic Typing."  I just say make that text very "Gray."  And
if possible "User Controlled" optional.

>From my standpoint, I was merrily cruising along with the "old" SQLite
2.8.x implementation.  But the switch to 3,x,x has been at best,
"eventful."  Seems with each new day I either find something old
obscurely broken or yet another way to shoot myself in the foot with a
3.x.x "Enhancement." :-)  Ah, life on the "bleeding edge"!

BTW.  On the Windose machine, footprint (.dll size): 2.8.x, 219K; 3.x.x,
245k (today)  equals +36k,  If you had yet another 36K what could you
do?  As modern PDA's now seem to have a minimum of 8M or so.  And these
kind of enhancement requests just keep coming.

Fred

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 10:53 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> "Fred Williams" <[EMAIL PROTECTED]> wrote:
> > Ah the sticky wicket that is "Type less" :-)  We now have issues
> > evolving as a direct result of that feature in our cute
> little database.
> > We now seem to have by backing into it: Really Restricted
> Integer, Real,
> > DateTime (sort of), and Text.  BLOB and CLOB away at your own risk!
> >
> > Is it time to officially declare/fully support some Types
> and clear the
> > air?
> >
>
> SQLite is not "type-less".  It uses manifest or dynamic typing instead
> of static-typing which is what most other SQL database engines use
> (and the SQL standard specifies).  Manifest or dynamic typing is a
> superset of static typing.  The use of static typing in the
> SQL standard
> is a bug in that standard, in my view, than unnecessarily complicates
> and restricts what you can do with SQL.  The original SQL standard
> specifies static typing so that implementations can use fixed-size
> records.  Static typing is an artifact of the implementation showing
> through into the interface.  Static typing in SQL is designed not to
> help the users of SQL databases, but rather to help the implementors
> of SQL database engines.
>
> SQLite is the only SQL database engine that I am aware of that offers
> dynamic typing.  This is not going to change.
>
> The difficult faced by SQLite is not how to deal with a dynamically
> typed language (that's easy) but how to deal with a dynamic typing
> in a way that is backwards compatible with the (broken) static typing
> behavior of SQL.  That is much harder.  But it is achievable, I think.
>
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Fred Williams]

In Delphi we have the Type: "Variant," which is pretty much "Type-less"
for OOP purposes.

> -Original Message-
> From: Doug Currie [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 11:25 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> Tuesday, November 1, 2005, 11:53:11 AM, [EMAIL PROTECTED] wrote:
>
> > Static typing in SQL is designed not to help the users of SQL
> > databases, but rather to help the implementors of SQL database
> > engines.
>
> There are uses for static typing. Types in the SQL context can be used
> as a kind of integrity constraint. If my design calls for a
> measurement, say, in a REAL column, perhaps I don't want rows with
> text (e.g., "large") in this column. Types is a way to enforce this.
>
> > The difficult faced by SQLite is not how to deal with a dynamically
> > typed language (that's easy) but how to deal with a dynamic typing
> > in a way that is backwards compatible with the (broken)
> static typing
> > behavior of SQL.  That is much harder.  But it is
> achievable, I think.
>
> There is an ongoing debate on this subject at Lambda the Ultimate. One
> approach that is sound is to introduce a new static type, called
> DYNAMIC, that permits any value. Summarizing and seconding Dennis
> Cote's suggestion, perhaps columns that have no type declared, or that
> are declared DYNAMIC, behave as SQLite does today, and columns that
> have static declarations behave as if they are statically typed.
>
> Regards,
>
> e
>
> --
> Doug Currie
> Londonderry, NH
>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Doug Currie]

Tuesday, November 1, 2005, 11:53:11 AM, [EMAIL PROTECTED] wrote:

> Static typing in SQL is designed not to help the users of SQL
> databases, but rather to help the implementors of SQL database
> engines.

There are uses for static typing. Types in the SQL context can be used
as a kind of integrity constraint. If my design calls for a
measurement, say, in a REAL column, perhaps I don't want rows with
text (e.g., "large") in this column. Types is a way to enforce this.

> The difficult faced by SQLite is not how to deal with a dynamically
> typed language (that's easy) but how to deal with a dynamic typing
> in a way that is backwards compatible with the (broken) static typing
> behavior of SQL.  That is much harder.  But it is achievable, I think.

There is an ongoing debate on this subject at Lambda the Ultimate. One
approach that is sound is to introduce a new static type, called
DYNAMIC, that permits any value. Summarizing and seconding Dennis
Cote's suggestion, perhaps columns that have no type declared, or that
are declared DYNAMIC, behave as SQLite does today, and columns that
have static declarations behave as if they are statically typed.

Regards,

e

-- 
Doug Currie
Londonderry, NH

Re: [sqlite] Page size problem

[drh]

"Anton Kuznetsov" <[EMAIL PROTECTED]> wrote:
> Hello!
> 
> Did anybody try to create an SQLite3 database with a custom page_size (e.g.
> 8192) and fill it with data of more than 1Gb? As for me I didn't
> manage (using tclsqlite-3.2.7). It says "database disk image is malformed".
> 

I just testing the script shown below.  It generates a 2 GiB database
that seems to work fine.

PRAGMA page_size=8192;
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 
123456789 123456789 123456789 123456789 ');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

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

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> I've found a potential problem - 
> round() is not a good substitute for the old integer truncation.
> 
> Consider the previous Sqlite3 behavior:
> 
>   sqlite> select 15/8;
>   1
>   sqlite> select round(15.0/8.0);
>   2
> 
> Can you recommend or provide a new function that performs correct
> integer trunction?
> 

I think the current code in CVS allows you to do this with

   CAST( 15.0/8.0 AS integer )

But I admit I need to test that case more thoroughly.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

"Fred Williams" <[EMAIL PROTECTED]> wrote:
> Ah the sticky wicket that is "Type less" :-)  We now have issues
> evolving as a direct result of that feature in our cute little database.
> We now seem to have by backing into it: Really Restricted Integer, Real,
> DateTime (sort of), and Text.  BLOB and CLOB away at your own risk!
> 
> Is it time to officially declare/fully support some Types and clear the
> air?
> 

SQLite is not "type-less".  It uses manifest or dynamic typing instead
of static-typing which is what most other SQL database engines use
(and the SQL standard specifies).  Manifest or dynamic typing is a
superset of static typing.  The use of static typing in the SQL standard
is a bug in that standard, in my view, than unnecessarily complicates
and restricts what you can do with SQL.  The original SQL standard
specifies static typing so that implementations can use fixed-size
records.  Static typing is an artifact of the implementation showing
through into the interface.  Static typing in SQL is designed not to
help the users of SQL databases, but rather to help the implementors 
of SQL database engines.

SQLite is the only SQL database engine that I am aware of that offers
dynamic typing.  This is not going to change.

The difficult faced by SQLite is not how to deal with a dynamically
typed language (that's easy) but how to deal with a dynamic typing
in a way that is backwards compatible with the (broken) static typing
behavior of SQL.  That is much harder.  But it is achievable, I think.


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

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Dennis Cote]

Preston Zaugg wrote:



I would NOT be in favor of this change. As was discussed in the 
original post this would be NON-STANDARD behavior. The SQL-99 spec 
says that integer math remains an integer.


The only time I would like an integer to return a "real" result is if 
that integer is stored in a field of type “real”, then all operations 
on that number should return a "real" result.


I understand the reasons for "int"s being stored as an "int" in a 
"real" column, but the change I would prefer is for it to act as a 
"real" if stored in a "real" column, otherwise it should act as it 
currently does.




I agree with Preston. SQLite should follow the SQL standard whenever 
possible.


Ralf brought up two separate but related issues in his original post:


In risk of asking the obvious, I wonder if the following division should be 
considered correct:

 | Query | Result Value | Result Type| OK?
---
1 | SELECT 5 / 2; | 2| SQLITE_INTEGER | No?
2 | SELECT 5.0 / 2;   | 2.5  | SQLITE_FLOAT   | Yes
3 | SELECT 5 / 2.0;   | 2.5  | SQLITE_FLOAT   | Yes
4 | SELECT 5.0 / 2.0; | 2.5  | SQLITE_FLOAT   | Yes

The query in question is Query 1. Is the returned integer result correct or 
should it not better return the 2.5 float value instead?

I understand that this probably boils down to integer arithmetics, but the 
decimals dropping can cause severe rounding errors if SQLite stores an integer 
number without further warning like in:

 CREATE TABLE t (a REAL, b REAL);
 INSERT INTO t VALUES (5, 2);

Then the query

 SELECT a / b FROM t;

returns wrong results, even though both colums are declared as REAL.

In my opinion, any division which can not be represented as an integer should 
return a float value.

 

The first set of select statements are doing arithmetic using literal 
constant values. Each of these has a type, either real or integer. 
SQLite is doing the arithmetic using these values according to the 
standard. It produces an real (or approximate result) if either argument 
is real, and an integer (or exact) result if both arguments are integer. 
The semantics of arithmetic are different in many scripting languages, 
but those languages are following a different standard. The SQL standard 
specifies how this should be done and SQLite is doing it that way now. 
It shouldn't be changed.


The second issue is demonstrated by the last last three statements.

 CREATE TABLE t (a REAL, b REAL);
 INSERT INTO t VALUES (5, 2);
 SELECT a / b FROM t;

Here he has explicitly declared the columns a and b to be of type real. 
He then stores integer literal values into those columns. This is where 
the problem occurs, not during the division in the select statement.


SQL is a typed language. SQLite was originally an untyped implementation 
of SQL. In version 3 SQLite was changed to introduce stronger data 
typing, while still trying to maintain compatibility with its previous 
untyped versions. It does this very well in most cases. SQLite uses 
manifest typing, where each data value has its own associated data type 
whereas the standard assumes each column has a data type. In a few cases 
SQLite bumps into areas where this implementation produces non-standard 
behavior. This is one of them.


To produce standard behavior, integer values stored into columns of type 
real should be converted to real values.


If SQLite did this, then the select statement would be doing arithmetic 
on two real values and it would produce a real result. This is what the 
user expects because he explicitly said that these columns should 
contain real values. Currently SQLite is giving more weight to the fact 
that he didn't put a decimal point on the literal values than it is 
giving to the fact that he explicitly said the columns will hold real data.


This change would only affect columns which are explicitly typed as 
real. Other columns that are untyped would continue to be able to hold 
any type of value as they do now. In all likelihood, any users that are 
explicitly declaring the data type of a column will be intending to use 
it to hold data of that type, and will only insert data of that type (or 
values hey expect to be converted to that type as in this case). Others 
who are using the typeless feature of SQLite will probably not declared 
a column data type and will get the same behavior they have now.


Similar arguments can be applied to values inserted into columns that 
are declared to be type integer. SQLite should probably convert real 
valued data inserted into integer columns into integer values. This 
would ensure that the sum of an integer column is always an integer for 
example.


It would also eliminate the situation we have now where real values 
stored into integer columns are accepted without complaint except when 
the integer column is also a primary key. In this case we get a 
"datatype mismatch" error 

[sqlite] Page size problem

[Anton Kuznetsov]

Hello!

Did anybody try to create an SQLite3 database with a custom page_size (e.g.
8192) and fill it with data of more than 1Gb? As for me I didn't
manage (using tclsqlite-3.2.7). It says "database disk image is malformed".

Thanks.
Anton. 

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Joe Wilson]

I've found a potential problem - 
round() is not a good substitute for the old integer truncation.

Consider the previous Sqlite3 behavior:

  sqlite> select 15/8;
  1
  sqlite> select round(15.0/8.0);
  2

Can you recommend or provide a new function that performs correct integer 
trunction?

--- [EMAIL PROTECTED] wrote:

> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > 
> > How do intend to treat 5/2 if passed to an Sqlite function expecting
> > an integer argument? 
> 
> Exactly the same thing that happens now if you pass 2.5
> into that same function: it truncates the value to an
> integer 2.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 




__ 
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Re: [sqlite] disabling journalling of the database - side affects?

[Christian Smith]

On Tue, 1 Nov 2005, Gerry Snyder wrote:

>Allan, Mark wrote:
>>
>>
>>  We do believe however that journaling the database for us
>> is of no benefit as our filing system is 100% fail safe and will
>> return to the last flushed state of the file on power loss.
>
>Mark,
>
>I am probably in over my head, as usual, but how do you recover if power
>goes down during the write process for an update; that is, if some of an
>update is written to the file and some of it is not?
>
>I guess it must be a journalling fs. Double journalling would indeed be
>inefficient.
>
>Maybe it might be easier to turn off the fs journalling than that in sqlite?


FLASH-based filesystems often have an append-only log behaviour, in order
to give wear levelling by spreading writes across all cells evenly.
Without this wear levelling, the FLASH device would suffer premature
failure as some cells would be written more often than others.

Info on JFFS can be found here.
http://sourceware.org/jffs2/

>
>Gerry
>

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

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Jay Sprenkle]

On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I am proposing to make the changes outlined below in SQLite
> version 3.3.0 and I am wondering if these changes will cause
> any severe hardship.

Stay with the SQL standard, if that's not clear follow what other languages do..

int operator int -> int result
int operator float -> float operator float -> float result

etc.

Just my opinion FWIW.

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Fred Williams]

Ah the sticky wicket that is "Type less" :-)  We now have issues
evolving as a direct result of that feature in our cute little database.
We now seem to have by backing into it: Really Restricted Integer, Real,
DateTime (sort of), and Text.  BLOB and CLOB away at your own risk!

Is it time to officially declare/fully support some Types and clear the
air?

Fred

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 9:43 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> "Preston Zaugg" <[EMAIL PROTECTED]> wrote:
> > As was discussed in the original
> > post this would be NON-STANDARD behavior.
> > The SQL-99 spec says that integer
> > math remains an integer.
> >
>
> The change I propose (and have now checked into CVS, btw,
> though I might still back it out) does not violate this
> specification.
>
> Think of it this way:  SQLite supports only a single
> numeric type which is REAL.  We call that type "numeric".
> But the type represents what we normally think of as real
> numbers.
>
> We permit integer values to be read from and written to
> the database as a convenience to the user.  And internally,
> some values are sometimes kept as machine integers for
> computational  and storage efficiency.  But that is only
> an optimization. At the end of the day, there is only a
> single numeric data type and that type is real.
>
> An INTEGER PRIMARY KEY column seems like an exception to
> this rule.  But perhaps not.  Think of an INTEGER PRIMARY
> KEY column as holding a numeric value with restrictions.
> It is as if we added to every INTEGER PRIMARY KEY named
> "x" the following check constraint:
>
>CHECK( x >= -9223372036854775808
>   AND x <= 923372036854775807
>   AND x == round(x) )
>
> So the values going in and out of an integer primary key
> are still real values.  It just happens that their range
> is restricted somewhat and they do not have a fractional
> part.
>
> If you look at things from this point of view, SQLite
> does not support integer values.  And so we never have
> to worry about integer division.
>
> For complete consistency, I suppose we might want to
> modify the built-in typeof() function to always return
> "numeric" instead of "integer".  I wonder
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> How do intend to treat 5/2 if passed to an Sqlite function expecting
> an integer argument? 

Exactly the same thing that happens now if you pass 2.5
into that same function: it truncates the value to an
integer 2.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

"Preston Zaugg" <[EMAIL PROTECTED]> wrote:
> As was discussed in the original 
> post this would be NON-STANDARD behavior. 
> The SQL-99 spec says that integer 
> math remains an integer.
> 

The change I propose (and have now checked into CVS, btw,
though I might still back it out) does not violate this
specification.

Think of it this way:  SQLite supports only a single
numeric type which is REAL.  We call that type "numeric".
But the type represents what we normally think of as real
numbers.

We permit integer values to be read from and written to
the database as a convenience to the user.  And internally,
some values are sometimes kept as machine integers for 
computational  and storage efficiency.  But that is only 
an optimization. At the end of the day, there is only a 
single numeric data type and that type is real.

An INTEGER PRIMARY KEY column seems like an exception to
this rule.  But perhaps not.  Think of an INTEGER PRIMARY
KEY column as holding a numeric value with restrictions.
It is as if we added to every INTEGER PRIMARY KEY named
"x" the following check constraint:

   CHECK( x >= -9223372036854775808
  AND x <= 923372036854775807
  AND x == round(x) )

So the values going in and out of an integer primary key
are still real values.  It just happens that their range
is restricted somewhat and they do not have a fractional
part.

If you look at things from this point of view, SQLite
does not support integer values.  And so we never have
to worry about integer division.

For complete consistency, I suppose we might want to
modify the built-in typeof() function to always return
"numeric" instead of "integer".  I wonder

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

RE: [sqlite] disabling journalling of the database - side affects?

[Allan, Mark]

Gerry,

We are using a flash filesystem.

The changes are made to a separate area of the flash, when the file is flushed 
then the descriptor blocks are updated to point to the new block of flash. If 
the power is lost before the file is flushed then the descriptor is not updated 
and therefore the file is still in the same state as it was prior to any of the 
writes. The file is only changed when it is flushed or closed.

We believe based on this that we do not need to journal the database file. 
Indeed we had no problems with 3.2.1 albeit as we were benfiting from flushes 
that where not supposed to be happening.

Regards

Mark


> -Original Message-
> From: Gerry Snyder [mailto:[EMAIL PROTECTED]
> Sent: 01 November 2005 14:58
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] disabling journalling of the database - side
> affects?
> 
> 
> Allan, Mark wrote:
> > 
> > 
> >  We do believe however that journaling the database for us 
> > is of no benefit as our filing system is 100% fail safe and will 
> > return to the last flushed state of the file on power loss. 
> 
> Mark,
> 
> I am probably in over my head, as usual, but how do you 
> recover if power 
> goes down during the write process for an update; that is, if 
> some of an 
> update is written to the file and some of it is not?
> 
> I guess it must be a journalling fs. Double journalling would 
> indeed be 
> inefficient.
> 
> Maybe it might be easier to turn off the fs journalling than 
> that in sqlite?
> 
> Gerry
> 
> -- 
> --
> Gerry Snyder
> American Iris Society Director, Symposium Chair
> in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
> 
> 
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Fred Williams]

What happened to the old: Integer arithmetic produces integer results
rule?  I thought that was either a "Standard"  or at least a very old
artifact.  Is it not how most Language math functions work?

I like the Pragma idea on this one.

> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 9:10 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> Although all my Sqlite3 databases depend on integer division
> truncation
> and would break with your proposed change I agree that 5/2
> should equal
> 2.5 in order to be more consistant with other databases. I
> can migrate
> my databases to use round(). But might it be possible to create a
> backwards compatibilty pragma to preserve the old integer division
> truncation behavior? Or perhaps a compile-time option?
>
> How do intend to treat 5/2 if passed to an Sqlite function expecting
> an integer argument?  An error? 2? 3? I would vote that it would be
> treated as 2 in such a case.
>
> --- [EMAIL PROTECTED] wrote:
>
> > I am proposing to make the changes outlined below in SQLite
> > version 3.3.0 and I am wondering if these changes will cause
> > any severe hardship.
> >
> > Two changes working together:
> >
> >   (1) Floating point values are *always* converted into
> >   integers if it is possible to do so without loss
> >   of information.
> >
> >   (2) Division of two integers returns a floating point
> >   value if necessary to preserve the fractional part
> >   of the result.
> >
> > The effect of change (1) is to combine the integer affinity
> > and the numeric affinity column types into a single type.
> > The new type is called numeric affinity, but it works like
> > integer affinity.  Change (2) resolves Ralf Junker's
> > division paradox.
> >
> > The only code that I can think of that this change might
> > break is cases where the user is depending on the division
> > of two integers returning an integer result.  Such code
> > will need to be modified to use the "round()" function
> > to obtain the same result.  I am thinking that such code
> > should be very uncommon and that this change will have
> > minimal impact.  Nevertheless, the impact is non-zero so
> > I will increment the minor version number as part of this
> > change.
> >
> > If you can think of any other adverse impact that this
> > change might have, please let me know.
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
>
>
>
>
> __
> Yahoo! FareChase: Search multiple travel sites in one click.
> http://farechase.yahoo.com

RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Preston Zaugg]

For what it is worth…

I would NOT be in favor of this change. As was discussed in the original 
post this would be NON-STANDARD behavior. The SQL-99 spec says that integer 
math remains an integer.


The only time I would like an integer to return a "real" result is if that 
integer is stored in a field of type “real”, then all operations on that 
number should return a "real" result.


I understand the reasons for "int"s being stored as an "int" in a "real" 
column, but the change I would prefer is for it to act as a "real" if stored 
in a "real" column, otherwise it should act as it currently does.


Thanks for asking for opinions before making the change
Preston




From: [EMAIL PROTECTED]
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: [sqlite] Proposed 3.3.0 changes.  Was: 5/2==2
Date: Tue, 01 Nov 2005 08:59:53 -0500

I am proposing to make the changes outlined below in SQLite
version 3.3.0 and I am wondering if these changes will cause
any severe hardship.

Two changes working together:

  (1) Floating point values are *always* converted into
  integers if it is possible to do so without loss
  of information.

  (2) Division of two integers returns a floating point
  value if necessary to preserve the fractional part
  of the result.

The effect of change (1) is to combine the integer affinity
and the numeric affinity column types into a single type.
The new type is called numeric affinity, but it works like
integer affinity.  Change (2) resolves Ralf Junker's
division paradox.

The only code that I can think of that this change might
break is cases where the user is depending on the division
of two integers returning an integer result.  Such code
will need to be modified to use the "round()" function
to obtain the same result.  I am thinking that such code
should be very uncommon and that this change will have
minimal impact.  Nevertheless, the impact is non-zero so
I will increment the minor version number as part of this
change.

If you can think of any other adverse impact that this
change might have, please let me know.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Joe Wilson]

Although all my Sqlite3 databases depend on integer division truncation
and would break with your proposed change I agree that 5/2 should equal 
2.5 in order to be more consistant with other databases. I can migrate 
my databases to use round(). But might it be possible to create a 
backwards compatibilty pragma to preserve the old integer division 
truncation behavior? Or perhaps a compile-time option? 

How do intend to treat 5/2 if passed to an Sqlite function expecting
an integer argument?  An error? 2? 3? I would vote that it would be 
treated as 2 in such a case.

--- [EMAIL PROTECTED] wrote:

> I am proposing to make the changes outlined below in SQLite
> version 3.3.0 and I am wondering if these changes will cause
> any severe hardship.
> 
> Two changes working together:
> 
>   (1) Floating point values are *always* converted into 
>   integers if it is possible to do so without loss
>   of information.
> 
>   (2) Division of two integers returns a floating point
>   value if necessary to preserve the fractional part
>   of the result.
> 
> The effect of change (1) is to combine the integer affinity
> and the numeric affinity column types into a single type.
> The new type is called numeric affinity, but it works like
> integer affinity.  Change (2) resolves Ralf Junker's
> division paradox.
> 
> The only code that I can think of that this change might
> break is cases where the user is depending on the division
> of two integers returning an integer result.  Such code
> will need to be modified to use the "round()" function
> to obtain the same result.  I am thinking that such code
> should be very uncommon and that this change will have
> minimal impact.  Nevertheless, the impact is non-zero so
> I will increment the minor version number as part of this
> change.
> 
> If you can think of any other adverse impact that this
> change might have, please let me know.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 




__ 
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Re: [sqlite] disabling journalling of the database - side affects?

[Gerry Snyder]

Allan, Mark wrote:



 We do believe however that journaling the database for us 
is of no benefit as our filing system is 100% fail safe and will 
return to the last flushed state of the file on power loss. 


Mark,

I am probably in over my head, as usual, but how do you recover if power 
goes down during the write process for an update; that is, if some of an 
update is written to the file and some of it is not?


I guess it must be a journalling fs. Double journalling would indeed be 
inefficient.


Maybe it might be easier to turn off the fs journalling than that in sqlite?

Gerry

--
--
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

[sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[drh]

I am proposing to make the changes outlined below in SQLite
version 3.3.0 and I am wondering if these changes will cause
any severe hardship.

Two changes working together:

  (1) Floating point values are *always* converted into 
  integers if it is possible to do so without loss
  of information.

  (2) Division of two integers returns a floating point
  value if necessary to preserve the fractional part
  of the result.

The effect of change (1) is to combine the integer affinity
and the numeric affinity column types into a single type.
The new type is called numeric affinity, but it works like
integer affinity.  Change (2) resolves Ralf Junker's
division paradox.

The only code that I can think of that this change might
break is cases where the user is depending on the division
of two integers returning an integer result.  Such code
will need to be modified to use the "round()" function
to obtain the same result.  I am thinking that such code
should be very uncommon and that this change will have
minimal impact.  Nevertheless, the impact is non-zero so
I will increment the minor version number as part of this
change.

If you can think of any other adverse impact that this
change might have, please let me know.
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] disabling journalling of the database - side affects?

[Allan, Mark]

Thanks for your detailed reply. Would you mind answering my follow up queries?

> The ability to disable journaling is not a supported behaviour of
> SQLite.  If you can get it to work, that's great.  But if not, that
> is not considered a bug.

Ok so the ability to not be able to disable journaling is not a bug. However 
would you consider implementing this as a new feature in a future revision of 
SQLite? I am unsure how many other users would find the disabling of the 
journal file a useful feature? I know here we would really appreciate this. We 
have seen information on the web indicating that turning off journaling would 
help performance, See section 3.3 at 
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html. Although it 
does not state a mechanism for doing so. So maybe there are other users that 
would appeciate this as well as us?

Indeed not journaling the file did help performance a great deal, but of course 
we have to prioritise data integrity over speed. We do believe however that 
journaling the database for us is of no benefit as our filing system is 100% 
fail safe and will return to the last flushed state of the file on power loss. 

Ideally we want to be able to use the latest versions of SQLite as they are 
released and as such don't want to stay with 3.2.1 especially as we may have 
been inadvertently benefiting from what was actually a bug anyhow.

Regards

Mark

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 01 November 2005 11:49
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] disabling journalling of the database - side
> affects?
> 
> 
> "Allan, Mark" <[EMAIL PROTECTED]> wrote:
> > I definately have a problem when I disable journalling on 
> version 3.2.7 
> > of SQLite. I dont see the same problem with 3.2.1 of SQlite.
> > I disable the journalling of the database file as described in my 
> > earlier email.
> > With version 3.2.7 this causes SQlite to not sync the database file 
> > after some updates (i.e. sqlite3OsSync() is not called). This means 
> > that if power is lost before the database file is explicitly closed 
> > we are losing those changes.
> > 
> > I realise that the purpose of the journal file is to allow 
> the database
> > to return to its last valid state if power is lost during an update,
> > however we believe that in our system we will not need this as our
> > flash filing system is 100% power safe. i.e. it will return to the
> > last flushed state of the file on power loss. So we believe that not
> > only is the journalling of the database time consuming it is also
> > unnecessary for us, which is a little annoying.
> > 
> > If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot
> > disable the journal file safely by the mechanism described in my
> > previous email then can somebody please indicate how I can disable
> > journalling of the database safely.
> > 
> 
> The ability to disable journalling is not a supported behavior of
> SQLite.  If you can get it to work, that's great.  But if not, that
> is not considered a bug.
> 
> The omitJournal flag on sqlite3BtreeFactory() is used for transient
> tables that are never rolled back and which we do not care about if
> there is a program crash or power failure.  The system should never
> call sqlite3OsSync() on such files because sqlite3OsSync() is an
> expensive operation (on most platforms) and for a transient file
> it does not accomplish anything useful.  If version 3.2.1 was calling
> sqlite3OsSync() on unjournalled files, then that was a performance
> bug.  I have no specific memory of fixing that problem in 3.2.7, but
> a lot of little problems were fixed in between those two releases,
> so it seems plausible that this was one of them.
> 
> So my best guess is that if your journal-less use of SQLite worked
> with version 3.2.1 then that was due to a bug in 3.2.1 that has
> since been fixed - not a bug that has been introduced.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re: [sqlite] disabling journalling of the database - side affects?

[drh]

"Allan, Mark" <[EMAIL PROTECTED]> wrote:
> I definately have a problem when I disable journalling on version 3.2.7 
> of SQLite. I dont see the same problem with 3.2.1 of SQlite.
> I disable the journalling of the database file as described in my 
> earlier email.
> With version 3.2.7 this causes SQlite to not sync the database file 
> after some updates (i.e. sqlite3OsSync() is not called). This means 
> that if power is lost before the database file is explicitly closed 
> we are losing those changes.
> 
> I realise that the purpose of the journal file is to allow the database
> to return to its last valid state if power is lost during an update,
> however we believe that in our system we will not need this as our
> flash filing system is 100% power safe. i.e. it will return to the
> last flushed state of the file on power loss. So we believe that not
> only is the journalling of the database time consuming it is also
> unnecessary for us, which is a little annoying.
> 
> If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot
> disable the journal file safely by the mechanism described in my
> previous email then can somebody please indicate how I can disable
> journalling of the database safely.
> 

The ability to disable journalling is not a supported behavior of
SQLite.  If you can get it to work, that's great.  But if not, that
is not considered a bug.

The omitJournal flag on sqlite3BtreeFactory() is used for transient
tables that are never rolled back and which we do not care about if
there is a program crash or power failure.  The system should never
call sqlite3OsSync() on such files because sqlite3OsSync() is an
expensive operation (on most platforms) and for a transient file
it does not accomplish anything useful.  If version 3.2.1 was calling
sqlite3OsSync() on unjournalled files, then that was a performance
bug.  I have no specific memory of fixing that problem in 3.2.7, but
a lot of little problems were fixed in between those two releases,
so it seems plausible that this was one of them.

So my best guess is that if your journal-less use of SQLite worked
with version 3.2.1 then that was due to a bug in 3.2.1 that has
since been fixed - not a bug that has been introduced.

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

RE: [sqlite] disabling journalling of the database - side affects?

[Allan, Mark]

I definately have a problem when I disable journalling on version 3.2.7 of 
SQLite. I dont see the same problem with 3.2.1 of SQlite.
I disable the journalling of the database file as described in my earlier email.
With version 3.2.7 this causes SQlite to not sync the database file after some 
updates (i.e. sqlite3OsSync() is not called). This means that if power is lost 
before the database file is explicitly closed we are losing those changes.

I realise that the purpose of the journal file is to allow the database to 
return to its last valid state if power is lost during an update, however we 
believe that in our system we will not need this as our flash filing system is 
100% power safe. i.e. it will return to the last flushed state of the file on 
power loss. So we believe that not only is the journalling of the database time 
consuming it is also unnecessary for us, which is a little annoying.

If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot disable the 
journal file safely by the mechanism described in my previous email then can 
somebody please indicate how I can disable journalling of the database safely.

Regards

Mark


> -Original Message-
> From: Allan, Mark [mailto:[EMAIL PROTECTED]
> Sent: 01 November 2005 09:52
> To: sqlite-users@sqlite.org
> Subject: [sqlite] disabling journalling of the database - 
> side affects?
> 
> 
> 
> Can anybody tell me if I will suffer any sideaffects if I 
> disable the journalling of the database file with version 3.2.7?
> 
> We have changed the SQlite code locally to omit the 
> journalling of the database file by supplying 1 as the value 
> for omitJournal to calls to the function sqlite3BtreeFactory. 
> We have done this as we do not want the performance overhead 
> of doubling the amount of writes we make as we are using an 
> NOR flash filing system and this is not particularly quick.
> 
> Can anybody help me?
> 
> Thanks 
> 
> 
> Mark
> 
> 
> DISCLAIMER:
> This information and any attachments contained in this email 
> message is intended only for the use of the individual or 
> entity to which it is addressed and may contain information 
> that is privileged, confidential, and exempt from disclosure 
> under applicable law.  If the reader of this message is not 
> the intended recipient, or the employee or agent responsible 
> for delivering the message to the intended recipient, you are 
> hereby notified that any dissemination, distribution, 
> forwarding, or copying of this communication is strictly 
> prohibited.  If you have received this communication in 
> error, please notify the sender immediately by return email, 
> and delete the original message immediately.
> 
> 
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re: [sqlite] Error code 25

[Arjen Markus]

Hannes Ricklefs wrote:
> 
> Hi,
> 
> does anyone know what error code 25 stands for I can't find it in the
> documentation! It occurs in relation to binding values to a prepared
> statement.
> 
> THanks,
> Hannes

You can find all codes in the sqlite.h include file:

#define SQLITE_RANGE   25   /* 2nd parameter to sqlite3_bind out of
range */ 

(I posted a message about one or two error codes missing from the online
docs last week - this is one of them).

Regards,

Arjen

[sqlite] Error code 25

[Hannes Ricklefs]

Hi,

does anyone know what error code 25 stands for I can't find it in the
documentation! It occurs in relation to binding values to a prepared
statement.

THanks,
Hannes

[sqlite] disabling journalling of the database - side affects?

[Allan, Mark]

Can anybody tell me if I will suffer any sideaffects if I disable the 
journalling of the database file with version 3.2.7?

We have changed the SQlite code locally to omit the journalling of the database 
file by supplying 1 as the value for omitJournal to calls to the function 
sqlite3BtreeFactory. We have done this as we do not want the performance 
overhead of doubling the amount of writes we make as we are using an NOR flash 
filing system and this is not particularly quick.

Can anybody help me?

Thanks 


Mark


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.