Re: [sqlite] SQLite 2013 retrospective

[big stone]

Whish list for 2014 =
. a simplified implementation of "Common Table Expressions",
. amazing SQlite4 performance when in ":memory:".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 2013 retrospective

[Nico Williams]

On Tue, Dec 31, 2013 at 10:37 AM, Richard Hipp  wrote:
> New tested and supported extensions added to the source tree:
>
>2.  The transitive_closure virtual table:
> www.sqlite.org/src/artifact/6360243

Funny you should mention this.  I've written transitive closure
computations using pure SQL (in SQLite3) several times now.  For my
most recent specific needs this virtual table wouldn't quite have been
a natural fit, but it's still interesting to see your implementation.

FWIW, I do a simple loop over INSERT OR IGNORE INTO SELECT parent ID,
childen's children IDs, repeatedly up to the max path length
iterations or until the table inserted to stops growing.  Since I need
to pre-compute all transitive closures in my data set and I need to
persist and efficiently-update the result, this approach works well
enough and permits subsequent O(1) computations... with the caveat
that the pre-computation is expensive: worst-case *bound* with this
approach is roughly O(N^2 log N), but for the actual -trusted- dataset
it's much, much less.  I've even resorted to doing the loop up to max.
depth times via a recursive trigger, so it's all pure SQLite3.

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

Re: [sqlite] SQLite 2013 retrospective

[Constantine Yannakopoulos]

If I may:

On Thu, Jan 2, 2014 at 12:59 AM, Petite Abeille wrote:

> Wish list for the new year :))
>
> - information schema [1]
> - merge statement [2]
> - with clause [3][4]
> - analytic functions [5][6]
>

- More informative error messages for FK violations. I consider this the
only unworkaroundable flaw of SQLite.

A happy new year to all!

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

Re: [sqlite] SQLite 2013 retrospective

[Petite Abeille]

On Dec 31, 2013, at 5:37 PM, Richard Hipp  wrote:

> In addition to the above, there are countless new test cases and minor
> feature and performance enhancements.

Thank you so much for for all the hard work :)

> Our goal is to maintain this aggressive pace of innovation and enhancement
> in SQLite throughout 2014 and beyond.

Wish list for the new year :))

- information schema [1]
- merge statement [2]
- with clause [3][4]
- analytic functions [5][6]

> Happy New Year to all.

Happy New Year!

[1] http://en.wikipedia.org/wiki/Information_schema
[2] http://en.wikipedia.org/wiki/Merge_(SQL)
[3] http://www.oracle-base.com/articles/misc/with-clause.php
[4] http://www.postgresql.org/docs/9.3/static/queries-with.html
[5] http://www.oracle-base.com/articles/misc/analytic-functions.php
[6] http://www.postgresql.org/docs/9.3/static/tutorial-window.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 2013 retrospective

[Niall O'Reilly]

At Tue, 31 Dec 2013 11:37:05 -0500,
Richard Hipp wrote:
> 
> In addition to the above, there are countless new test cases and minor
> feature and performance enhancements.
> 
> Our goal is to maintain this aggressive pace of innovation and enhancement
> in SQLite throughout 2014 and beyond.

Congratulations on a busy and productive year!
 
> Happy New Year to all.

The same to you, and to everyone on the list ...

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

Re: [sqlite] SQLite 2013 retrospective

[Ryan Finnesey]

Wanted to thank the team for all there great work and wish everyone a Happy New 
Year! And all the best in 2014.

Cheers
Ryan


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, December 31, 2013 11:37 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite 2013 retrospective

Here is a quick summary of the changes and enhancements to SQLite during 2013.  
The comparison is between trunk versions,
http://www.sqlite.org/src/info/a611c75 versus
http://www.sqlite.org/src/info/cc72c5aec7

The amalgamation source file grew in side from 137619 lines to 145010 lines, or 
4868834 bytes to 5125216 bytes.  Excluding comments the size grew from 82156 
lines to 87537 lines.  That's between 5% to 7% growth in source code, depending 
on how you measure.  Approximately one source code line out of every five 
changed during 2013 for a 20% code churn.

The compiled binary (gcc 4.8.1 with -Os on x64) grew from 412365 to 433963 
bytes, or about 5%.

Running a typical mix of SQL statements (as implemented by the 
http://www.sqlite.org/src/artifact/7130d2cb?ln test program), SQLite uses 15.5% 
fewer CPU operations to do the same task as it did one year ago, as measured by 
valgrind.  Real-time performance is about 12% faster according to that same 
benchmark running on Ubuntu 13.10, x64.

There were 737 trunk check-ins during 2013, or about two check-ins per day.  
There were an additional 924 check-ins on branches, for a total of
1661 check-ins.  416 files were changed in some way or another.  24216 lines 
were inserted and 12744 lines deleted, according to diffstat.  The above is for 
the main source tree only.  There are many, many other enhancements to the 
documentation and test suites.

The following releases occurred during 2013:

2013-01-09:   3.7.15.2
2013-03-18:   3.7.16
2013-03-29:   3.7.16.1
2013-04-12:   3.7.16.2
2013-05-20:   3.7.17
2013-08-26:   3.8.0
2013-08-29:   3.8.0.1
2013-09-03:   3.8.0.2
2013-10-17:   3.8.1
2013-12-06:   3.8.2

Major new features added in 2013 include:

   1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
   2.  The next-generation query planner:
http://www.sqlite.org/queryplanner-ng.html
   3.  Partial indices: http://www.sqlite.org/partialindex.html
   4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

New PRAGMAs:

   1.  PRAGMA foreign_key_check;
   2.  PRAGMA application_id;
   3.  PRAGMA defer_foreign_keys;
   4.  PRAGMA cache_spill;
   5.  PRAGMA query_only;
   6.  PRAGMA soft_heap_limit;

New SQL functions:

   1.  printf()  -- to appear in version 3.8.3
   2.  unlikely()
   3.  likelihood()
   4.  unicode()
   5.  char()

New C-language APIs:

   1.  sqlite3_cancel_auto_extension()
   2.  sqlite3_strglob()

New tested and supported extensions added to the source tree:

   1.  The approximate_match virtual table:
www.sqlite.org/src/artifact/678056a
   2.  The transitive_closure virtual table:
www.sqlite.org/src/artifact/6360243
   3.  The ieee754() SQL function:
http://www.sqlite.org/src/artifact/b03621672
   4.  The next_char() SQL function: www.sqlite.org/src/artifact/35c8b8ba
   5.  The percentile() SQL function: www.sqlite.org/src/artifact/bcbee3c
   6.  The regexp() SQL function:
http://www.sqlite.org/src/artifact/af92cdaa5
   7.  The rot13() SQL function: http://www.sqlite.org/src/artifact/1ac6f95f
   8.  The spellfix1 virtual table: http://www.sqlite.org/spellfix1.html
   9.  The tointeger() and toreal() SQL functions:
www.sqlite.org/src/artifact/4a167594
   10. The wholenumber virtual table: www.sqlite.org/src/artifact/784b1254

In addition to the above, there are countless new test cases and minor feature 
and performance enhancements.

Our goal is to maintain this aggressive pace of innovation and enhancement in 
SQLite throughout 2014 and beyond.

Happy New Year to all.
--
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] SQLite 2013 retrospective

[Simon Slavin]

On 31 Dec 2013, at 7:36pm, jose isaias cabrera  wrote:

> It would be nice to know also if you have any major idea(s) for enhancements 
> for sqlite3 in 2014.

You might consider SQLite4 to be major ideas for SQLite3



though as the page says, the two will coexist since there is no backward 
compatibility and the billion or so installations of SQLite3 aren't going to go 
away any time soon.

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

Re: [sqlite] SQLite 2013 retrospective

[jose isaias cabrera]

Richard Hipp wrote...



Here is a quick summary of the changes and enhancements to SQLite during
2013.  The comparison is between trunk versions,

[clip]
Thanks so much for this info.


Our goal is to maintain this aggressive pace of innovation and enhancement
in SQLite throughout 2014 and beyond.
It would be nice to know also if you have any major idea(s) for enhancements 
for sqlite3 in 2014.


Thanks so much for this great tool!

Happy 2014 to all.

josé 


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

Re: [sqlite] SQLite 2013 retrospective

[Jan Nijtmans]

2013/12/31 Richard Hipp :
> Here is a quick summary of the changes and enhancements to SQLite during
> 2013.  The comparison is between trunk versions,
> http://www.sqlite.org/src/info/a611c75 versus
> http://www.sqlite.org/src/info/cc72c5aec7
...
> Major new features added in 2013 include:
>
>1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
>2.  The next-generation query planner:
> http://www.sqlite.org/queryplanner-ng.html
>3.  Partial indices: http://www.sqlite.org/partialindex.html
>4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

Also worth to mention:

New VFS's:
   1.  win32-longpath
   2.  unix-cygwin

(Ok. Ok, the latter is not in the officlal SQLite sources
yet, but it's available as official Cygwin package and
I hope to be able to contribute this in 2014.)

> Happy New Year to all.

Seconded:   Happy New Year to all.

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

[sqlite] SQLite 2013 retrospective

[Richard Hipp]

Here is a quick summary of the changes and enhancements to SQLite during
2013.  The comparison is between trunk versions,
http://www.sqlite.org/src/info/a611c75 versus
http://www.sqlite.org/src/info/cc72c5aec7

The amalgamation source file grew in side from 137619 lines to 145010
lines, or 4868834 bytes to 5125216 bytes.  Excluding comments the size grew
from 82156 lines to 87537 lines.  That's between 5% to 7% growth in source
code, depending on how you measure.  Approximately one source code line out
of every five changed during 2013 for a 20% code churn.

The compiled binary (gcc 4.8.1 with -Os on x64) grew from 412365 to 433963
bytes, or about 5%.

Running a typical mix of SQL statements (as implemented by the
http://www.sqlite.org/src/artifact/7130d2cb?ln test program), SQLite uses
15.5% fewer CPU operations to do the same task as it did one year ago, as
measured by valgrind.  Real-time performance is about 12% faster according
to that same benchmark running on Ubuntu 13.10, x64.

There were 737 trunk check-ins during 2013, or about two check-ins per
day.  There were an additional 924 check-ins on branches, for a total of
1661 check-ins.  416 files were changed in some way or another.  24216
lines were inserted and 12744 lines deleted, according to diffstat.  The
above is for the main source tree only.  There are many, many other
enhancements to the documentation and test suites.

The following releases occurred during 2013:

2013-01-09:   3.7.15.2
2013-03-18:   3.7.16
2013-03-29:   3.7.16.1
2013-04-12:   3.7.16.2
2013-05-20:   3.7.17
2013-08-26:   3.8.0
2013-08-29:   3.8.0.1
2013-09-03:   3.8.0.2
2013-10-17:   3.8.1
2013-12-06:   3.8.2

Major new features added in 2013 include:

   1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
   2.  The next-generation query planner:
http://www.sqlite.org/queryplanner-ng.html
   3.  Partial indices: http://www.sqlite.org/partialindex.html
   4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

New PRAGMAs:

   1.  PRAGMA foreign_key_check;
   2.  PRAGMA application_id;
   3.  PRAGMA defer_foreign_keys;
   4.  PRAGMA cache_spill;
   5.  PRAGMA query_only;
   6.  PRAGMA soft_heap_limit;

New SQL functions:

   1.  printf()  -- to appear in version 3.8.3
   2.  unlikely()
   3.  likelihood()
   4.  unicode()
   5.  char()

New C-language APIs:

   1.  sqlite3_cancel_auto_extension()
   2.  sqlite3_strglob()

New tested and supported extensions added to the source tree:

   1.  The approximate_match virtual table:
www.sqlite.org/src/artifact/678056a
   2.  The transitive_closure virtual table:
www.sqlite.org/src/artifact/6360243
   3.  The ieee754() SQL function:
http://www.sqlite.org/src/artifact/b03621672
   4.  The next_char() SQL function: www.sqlite.org/src/artifact/35c8b8ba
   5.  The percentile() SQL function: www.sqlite.org/src/artifact/bcbee3c
   6.  The regexp() SQL function:
http://www.sqlite.org/src/artifact/af92cdaa5
   7.  The rot13() SQL function: http://www.sqlite.org/src/artifact/1ac6f95f
   8.  The spellfix1 virtual table: http://www.sqlite.org/spellfix1.html
   9.  The tointeger() and toreal() SQL functions:
www.sqlite.org/src/artifact/4a167594
   10. The wholenumber virtual table: www.sqlite.org/src/artifact/784b1254

In addition to the above, there are countless new test cases and minor
feature and performance enhancements.

Our goal is to maintain this aggressive pace of innovation and enhancement
in SQLite throughout 2014 and beyond.

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