Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Fri, Jun 29, 2012 at 8:33 PM, Jim Morris  wrote:

> Are there significant improvements in speed for existing SQL?
>

In my _simple_ tests (which run _almost_ the same ops through mysql5,
sqlite3, and now sqlite4), i'm seeing _huge_ boosts in speed in v4 (25x
over v3!) BUT:

a) this is not necessarily indicative of "what will be" because v4 will
certainly see significant changes before it stabilizes. i'm also using a
variety of flags just to get it to build, and can't say with certainty what
is enabled/disabled at the moment (not all valid combinations build right
now). e.g. i might have syncing turned off in the v4 build (but certainly
don't in my v3 build).

b) my tests which use auto-increment/last-row-id are disabled for v4 (it
doesn't yet do those), so those tests inherently have a few fewer calls
into the db (but not enough fewer to account for a 25x speed increase).


In other words, what i'm seeing might just be a fluke of nature.



> How does the compiled size compare with SQLite3?


The sized on my machine can't be directly compared because i've only got v4
building as a static lib, but the current static lib sizes on x64 Linux:

libsqlite3.a 5214256
libsqlite4.a 3753496

but again, that is not necessarily any indication of what it will look like
in 13 hours or 6 months. Nor does it give any hint about what the different
will mean for clients linked to it.

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

Re: [sqlite] SQLite4 (don't scream)

[Jim Morris]

Are there significant improvements in speed for existing SQL?

How does the compiled size compare with SQLite3?


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

Re: [sqlite] SQLite4 (don't scream)

[Petite Abeille]

On Jun 28, 2012, at 5:57 PM, Simon Slavin wrote:

> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not 
> going away."

Argh… but more seriously… would that new incarnation provide proper error 
messages for constraint violations?  

Providing a proper data dictionary would be nice as well :))

Supporting merge statement and with clause would be the cherry on the top.

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

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 4:09 PM, Alexey Pechnikov wrote:

> Will be covering indices on views available as replacement of materialized
> views?
>

No.  I don't know how to do that.


>
> 2012/6/28 Simon Slavin 
>
> > First, the important bit:
> >
> > "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
> not
> > going away."
> >
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLite4 (don't scream)

[Alexey Pechnikov]

Will be covering indices on views available as replacement of materialized
views?

2012/6/28 Simon Slavin 

> First, the important bit:
>
> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not
> going away."
>
> Now the URL:
>
> 
>
> Just thought some people might enjoy reading and thinking about it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Simon Slavin]

On 28 Jun 2012, at 6:04pm, Roger Binns  wrote:

> http://news.ycombinator.com/item?id=4168645

LOL.  Those Hacker News guys are hardcore.  Make some of my mailing lists look 
almost civil.

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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:09 PM, E. Timothy Uy  wrote:

> That's kind of cool, but how about $twentyseven!
>

$c1...$c27 (c==column)?

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

Re: [sqlite] SQLite4 (don't scream)

[E. Timothy Uy]

That's kind of cool, but how about $twentyseven!


> On Thu, Jun 28, 2012 at 12:57 PM, Robert Myers 
> wrote:
>
> > Aghhh (just kidding)
> >
> > One request for a change - make bind and column start with the same
> index.
> > I always have to look up which one is zero based and which one is one
> based.
> >
>
> Right now, porting an app from SQLite3 to SQLite4 is mostly a global
> search/replace of "sqlite3"->"sqlite4".  (Maybe a little more than
> thatbut not
> much.)  Changing the index base for sqlite4_bind would really
> complicate matters.
>
> Recommend you not use sqlite4_bind() directly, but rather us named
> parameters (ex: "@one", ":two", "$three") with
> sqlite4_bind_parameter_index():
>
>sqlite4_bind_int(pStmt, sqlite4_bind_parameter_index(pStmt, "$three"),
> 3);
>
> That way, you never have to worry about miscounting the "?" parameters in
> your query and being off-by-one in your bindings.
>
>
> >
> > Rob
> >
> >
> > On 6/28/2012 10:57 AM, Simon Slavin wrote:
> >
> >> First, the important bit:
> >>
> >> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
> >> not going away."
> >>
> >> Now the URL:
> >>
> >>  http://www.sqlite.org/src4/doc/trunk/www/design.wiki>
> >> >
> >>
> >> Just thought some people might enjoy reading and thinking about it.
> >>
> >> Simon.
> >> __**_
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
> >>
> >
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 12:57 PM, Robert Myers  wrote:

> Aghhh (just kidding)
>
> One request for a change - make bind and column start with the same index.
> I always have to look up which one is zero based and which one is one based.
>

Right now, porting an app from SQLite3 to SQLite4 is mostly a global
search/replace of "sqlite3"->"sqlite4".  (Maybe a little more than
thatbut not
much.)  Changing the index base for sqlite4_bind would really
complicate matters.

Recommend you not use sqlite4_bind() directly, but rather us named
parameters (ex: "@one", ":two", "$three") with
sqlite4_bind_parameter_index():

sqlite4_bind_int(pStmt, sqlite4_bind_parameter_index(pStmt, "$three"),
3);

That way, you never have to worry about miscounting the "?" parameters in
your query and being off-by-one in your bindings.


>
> Rob
>
>
> On 6/28/2012 10:57 AM, Simon Slavin wrote:
>
>> First, the important bit:
>>
>> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
>> not going away."
>>
>> Now the URL:
>>
>> 
>> >
>>
>> Just thought some people might enjoy reading and thinking about it.
>>
>> Simon.
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] SQLite4 (don't scream)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/12 08:57, Simon Slavin wrote:
> Just thought some people might enjoy reading and thinking about it.

There has also been discussion on Hacker News and Reddit:

 http://news.ycombinator.com/item?id=4168645

 http://www.reddit.com/r/programming/comments/vp9uh/sqlite4_the_design/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/sjp0ACgkQmOOfHg372QR7HACghy3WA1rXUlXtgUkJMncEGFPa
SwsAnA3T1tpMN9QsdXqUq7W/4961Dgmo
=JzxV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Ryan Johnson]

On 28/06/2012 12:30 PM, Cory Nelson wrote:

On Thu, Jun 28, 2012 at 11:20 AM, Stephan Beal wrote:


On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
wrote:


Now the URL:



Just thought some people might enjoy reading and thinking about it.


FWIW, my 0.02 Euros regarding this line:

"SQLite4 makes use of standard data types such as size_t, int64_t,
uint64_t,
and others."


size_t does not have a specified size and causes all sorts of grief in
porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
fixed-size integers defined in inttypes.h, and not size_t. There is of
course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
there are free drop-in replacements available here:
http://code.google.com/p/msinttypes/


stdint was made available in VC++ 2010, though inttypes is still missing.
Probably not an issue -- I'm not sure how a public API would need inttypes
anyway. Also, perhaps you are seeing size_t be misused. A blanket "please
don't use" is nonsense.
I tend to agree with Simon on this. size_t is only useful when 
expressing the amount of memory something might involve, when 
constrained only by the size of the current machine's address space. 
Major examples would include sizeof(), malloc(), and strlen(). C++ 
std::vector::size() is an anti-pattern, since the number of elements in 
a vector is not a number of bytes. However, size_t isn't particularly 
helpful even when used "correctly." Any 32-bit portable code can safely 
use uint32_t everywhere: it's equivalent to size_t on 32-bits, and it 
will silently convert to size_t whenever needed on a 64-bit machine; if 
the code is 64-bit only -- most likely to allow for allocations larger 
than 4GB -- then uint64_t can be used everywhere instead.


$0.02
Ryan


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

Re: [sqlite] SQLite4 (don't scream)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/12 09:20, Stephan Beal wrote:
> size_t does not have a specified size and causes all sorts of grief in 
> porting i/o-based APIs between 32/64 bits,

I have been one of the people complaining loudest about not using types
like size_t.  However the complaints are not directed to the internals of
SQLite where it is absolutely appropriate to use an explicitly sized type
for cases where a particular size is needed.

The area where it does matter is in various APIs such as sqlite3_bind_text
which takes an int for the length of the string.  This should be
size_t/ssize_t not int.  In all the open source code I looked at at the
time, they were all written as though size_t was used so there was
arbitrary truncation going on.  Various attacks were even possible due to
the discrepancy in how sizes were measured between the rest of the program
and SQLite.  They have been somewhat mitigated since then, but I'm still
sure that a dedicated cracker could figure out ways of exploiting this.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/sjjEACgkQmOOfHg372QTPwACggVC6nosFsJb2caXff1DEhkLh
/zwAoNtgC/bMDnj9yXa6EkulX5QsizOR
=PcBR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:57 PM, Robert Myers  wrote:

> One request for a change - make bind and column start with the same index.
> I always have to look up which one is zero based and which one is

one based.
>

That particular convention comes not from sqlite3, but from SQL. i have no
idea why, but every SQL API i've ever used does it that way. Doing it
different in sqlite4 would cause all kinds of porting grief, IMO.

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

Re: [sqlite] SQLite4 (don't scream)

[Robert Myers]

Aghhh (just kidding)

One request for a change - make bind and column start with the same 
index. I always have to look up which one is zero based and which one is 
one based.


Rob

On 6/28/2012 10:57 AM, Simon Slavin wrote:

First, the important bit:

"SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going 
away."

Now the URL:



Just thought some people might enjoy reading and thinking about it.

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



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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:52 PM, Stephan Beal  wrote:

> fully specialized). _Which_ fixed-size int type it collides with is
> platform-dependent and leads to convoluted workarounds like this:
>
>
> http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1207
>

And here's even one which was added explicitly to work around a
platform-dependent collision of long long and sqlite3_int64:

http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1270

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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:46 PM, Stephan Beal  wrote:

> 64-bits. size_t has at least the following drawbacks compared to using a
> fixed-size integer type:
>

- In C++ size_t is often problematic in template specializations because in
invariably collides with one of the other integer types (which might also
be fully specialized). _Which_ fixed-size int type it collides with is
platform-dependent and leads to convoluted workarounds like this:

http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1207

(that particular ugly hack is for long vs long-long, but the same applies
to size_t)

Sticking with fixed-size integers simply bypasses a whole range of
downstream unsightliness, and a solution which bypasses problems by its
very nature (as opposed to requiring extra effort to work around them) is
always the winning solution in my book.

So i don't mind at all making the blanket statement, "avoid size_t in new
APIs."

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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:30 PM, Cory Nelson  wrote:

> Probably not an issue -- I'm not sure how a public API would need inttypes
> anyway.

Also, perhaps you are seeing size_t be misused. A blanket "please
> don't use" is nonsense.
>

Hi, Cory! That's what i thought to until i started porting my i/o-centric
APIs to 64-bits. size_t has at least the following drawbacks compared to
using a fixed-size integer type:

- No standard size, so it is not terribly useful in platform-independent
file formats.

- There are no standard printf()/scanf() specifiers for it, which means
those funcs cannot be used with size_t or ifdefs or casts are needed to
handle them portably. All of the fixed-size integer types have portable
scanf/printf specifiers except that int8_t and scanf has a caveat due to
the compiler upgrading a (int8_t*) to a wider pointer (at least on gcc,
where it warns if you try to pass (int8_t*) to sscanf and friends).

- Structs which contain size_t members cannot have guaranteed sizes, so
they cannot be portably serialized without writing routines to do the
numeric conversion and check the value's range on the target platform. This
adds complication to any sort of deserialization involving size_t.

The way i "solve" this in my libraries is to have a config option for
however many bits the lib supports, e.g. 16-64, and then define a
lib-specific type, my_lib_size_t, aliasing the appropriate fixed-size type.
This is essentially what sqlite does with sqlite3_int64 and friends, though
i don't know if sqlite guarantees _minimum_ or _absolute_ integer lengths.

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

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 12:20 PM, Stephan Beal wrote:

> On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
> wrote:
>
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
>
> FWIW, my 0.02 Euros regarding this line:
>
> "SQLite4 makes use of standard data types such as size_t, int64_t,
> uint64_t,
> and others."
>

Since I wrote that, I've gone back into the code and changes a lot of those
into sqlite4_int64, etc.  Keep in mind that not just the code, but the
documentation is also a work in progress :-)



>
>
> size_t does not have a specified size and causes all sorts of grief in
> porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
> fixed-size integers defined in inttypes.h, and not size_t. There is of
> course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
> there are free drop-in replacements available here:
> http://code.google.com/p/msinttypes/
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLite4 (don't scream)

[Cory Nelson]

On Thu, Jun 28, 2012 at 11:20 AM, Stephan Beal wrote:

> On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
> wrote:
>
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
>
> FWIW, my 0.02 Euros regarding this line:
>
> "SQLite4 makes use of standard data types such as size_t, int64_t,
> uint64_t,
> and others."
>
>
> size_t does not have a specified size and causes all sorts of grief in
> porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
> fixed-size integers defined in inttypes.h, and not size_t. There is of
> course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
> there are free drop-in replacements available here:
> http://code.google.com/p/msinttypes/


stdint was made available in VC++ 2010, though inttypes is still missing.
Probably not an issue -- I'm not sure how a public API would need inttypes
anyway. Also, perhaps you are seeing size_t be misused. A blanket "please
don't use" is nonsense.

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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin  wrote:

> Now the URL:
>
> 
>
> Just thought some people might enjoy reading and thinking about it.
>

FWIW, my 0.02 Euros regarding this line:

"SQLite4 makes use of standard data types such as size_t, int64_t, uint64_t,
and others."


size_t does not have a specified size and causes all sorts of grief in
porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
fixed-size integers defined in inttypes.h, and not size_t. There is of
course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
there are free drop-in replacements available here:
http://code.google.com/p/msinttypes/


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

[sqlite] SQLite4 (don't scream)

[Simon Slavin]

First, the important bit:

"SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not 
going away."

Now the URL:



Just thought some people might enjoy reading and thinking about it.

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