Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #85771 > unrolled thread

Re: sqlite3 and dates

Back to article view | Back to comp.lang.python

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 18:05 +1100
    Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 12:11 +0100
      Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 22:21 +1100
        Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 12:57 +0100
          Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 23:14 +1100
            Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 14:13 +0100
            Not sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:53 +0200
          Re: sqlite3 and dates Adam Funk <a24061@ducksburg.com> - 2015-02-19 13:18 +0000
        Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 14:17 -0800
          Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-19 09:37 +1100
            Not sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:54 +0200
            Re: sqlite3 and dates Adam Funk <a24061@ducksburg.com> - 2015-02-19 13:21 +0000
          Re: sqlite3 and dates Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 14:52 -0800
          'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 15:32 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 01:08 +0100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 11:42 +1100
                Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 13:13 +1100
                  Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 03:43 +0000
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 08:49 +0100
                  Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:10 -0800
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 18:07 +1100
                      Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 18:23 +1100
                        Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 12:26 -0800
                          Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-20 07:47 +1100
                            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 20:20 -0800
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-02-20 09:16 -0500
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Sibylle Koczian <nulla.epistola@web.de> - 2015-02-21 11:44 +0100
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-02-21 12:54 -0500
                          Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 22:23 +0100
                            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 20:27 -0800
                      Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 12:20 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:05 -0800
                Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 08:21 -0600
              Re: 'Lite' Databases (Re: sqlite3 and dates) Gregory Ewing <greg.ewing@canterbury.ac.nz> - 2015-02-19 18:22 +1300
                Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 08:33 +0100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 05:32 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 08:17 -0600
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 15:04 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-20 02:19 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 10:03 -0600
                Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 11:45 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 11:03 +1100
            Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-20 13:17 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-20 21:44 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Ethan Furman <ethan@stoneleaf.us> - 2015-02-20 14:10 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-21 12:24 +1100
              Re: 'Lite' Databases Ben Finney <ben+python@benfinney.id.au> - 2015-02-21 14:13 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-20 15:31 -0600
              Re: 'Lite' Databases Chris Angelico <rosuav@gmail.com> - 2015-02-21 16:39 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Ned Deily <nad@acm.org> - 2015-02-20 22:22 -0800
                Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-20 22:42 -0800
                  Re: 'Lite' Databases (Re: sqlite3 and dates) Ned Deily <nad@acm.org> - 2015-02-21 00:17 -0800
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-21 00:32 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) "Eric S. Johansson" <esj@harvee.org> - 2015-02-21 14:27 -0500
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 19:33 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 15:01 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 15:09 +1100
            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:26 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 18:23 +1100
          When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 20:15 -0800
            Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 06:59 +0200
              Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 21:07 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 20:29 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 15:36 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 20:57 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 16:16 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 21:26 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 21:37 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 13:17 -0800
        Re: sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:48 +0200
          Re: sqlite3 and dates Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 03:34 +0000
      Re: sqlite3 and dates Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 07:14 +1100
        Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 14:13 -0800
          Re: sqlite3 and dates Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 10:07 +1100
            Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 20:08 -0800

Page 3 of 4 — ← Prev page 1 2 [3] 4  Next page →

#85925 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/19/2015 09:03 AM, Tim Chase wrote:
> On 2015-02-19 15:04, Mark Lawrence wrote:
>> On 19/02/2015 14:17, Tim Chase wrote:
>>>>> Parameterized queries is just a pet peeve of mine that I wish to
>>>>> include here. SQLite misses it and I miss the fact SQLite misses
>>>>> it. The less SQL one needs to write in their code, the happier
>>>>> one should be.
>>>>
>>>> Instead, use the DB-API's parameter substitution. Put ? as a
>>>> placeholder wherever you want to use a value, and then provide a
>>>> tuple of values as the second argument to the cursor's execute()
>>>> method. (Other database modules may use a different placeholder,
>>>> such as %s or :1.) For example:..."
>>>
>>> I think Mario was referring to what other back ends call prepared
>>> statements.
>>
>> Is this
>> https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany
>> an equivalent?
> 
> Depends on whether sqlite3.Cursor.executemany() uses
> 
> https://www.sqlite.org/c3ref/stmt.html
> 
> under the hood.

So it seems that Sqlite does have prepared statements -- they 
are just accessible from the api and not from SQL.

Regarding Mark's question I would say that a more significant
difference is that executemany() requires you to know all 
the bind parameter values at the time the statement is 
executed.

A prepared statement does not require you to know any of 
the bind parameter values when you prepare the statement -- 
they are supplied to the prepared statement at any later 
time or times when you execute the prepared statement.

[toc] | [prev] | [next] | [standalone]

#85835 — Re: 'Lite' Databases (Re: sqlite3 and dates)

memilanuk <memilanuk@gmail.com> writes:

> Okay... this might be a question with a blindingly obvious answer, but
> I haven't seen any recommendations otherwise so I'll ask anyway ;)
>
> Is there anything *good* that sits in between the two extremes of
> SQLite and PostgreSQL?

What do you need a RDBMS to do, and what do you not need?

The answers to those questions vary hugely between different people (and
most people probably don't think too deeply about them). They will
determine what “good” means for your case.

> Is there nothing that amounts to a 'PostgreSQLite'?

PostgreSQL itself fits that mould quite well; it is quite capable of
serving a small footprint while still offering full concurrency.

I don't know of a free-software concurrent RDBMS which can be considered
lighter than that. (No, MySQL doesn't count; its concurrency is
*unreliable* and it commonly loses data silently. Don't use MySQL.)

But perhaps you don't need concurrency? Only you can tell us.

-- 
 \      “It's up to the masses to distribute [music] however they want |
  `\    … The laws don't matter at that point. People sharing music in |
_o__)        their bedrooms is the new radio.” —Neil Young, 2008-05-06 |
Ben Finney

[toc] | [prev] | [next] | [standalone]

#85993 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Ben Finney <ben+python@benfinney.id.au> writes:
> I don't know of a free-software concurrent RDBMS which can be considered
> lighter than that. (No, MySQL doesn't count; its concurrency is
> *unreliable* and it commonly loses data silently. Don't use MySQL.)

I thought they fixed MySQL transactions years ago, with the InnoDB
engine.  For some reason it's not the default, so you have to turn it on
explicitly: is there more to it than that?

For stuff like browser bookmarks or other typical embedded database
purposes, I don't see why SQL or relations are needed.  Berkeley DB is a
transactional key-value store that's been around for decades and is way
simpler than SQLite, and there's other things like that too.

SQLite always seemed bloated (from the embedded NoSQL point of view) and
fragile to me, and the vendor plays an annoying anti-forking trick,
which is that the code is released but the developers' test suite is
secret and proprietary (can be licensed from them for big bucks).  So if
you want to make your own version of SQLite you have to either pay for
the test suite, or have much less reliability assurance for your patched
version than the vendor has for their version.  Add that Sqlite is
written in C (think of naked whirling razor blades) and you've got a
pretty serious disincentive against modification.

[toc] | [prev] | [next] | [standalone]

#85994 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 20/02/2015 21:17, Paul Rubin wrote:
> Ben Finney <ben+python@benfinney.id.au> writes:
>> I don't know of a free-software concurrent RDBMS which can be considered
>> lighter than that. (No, MySQL doesn't count; its concurrency is
>> *unreliable* and it commonly loses data silently. Don't use MySQL.)
>
> I thought they fixed MySQL transactions years ago, with the InnoDB
> engine.  For some reason it's not the default, so you have to turn it on
> explicitly: is there more to it than that?
>
> For stuff like browser bookmarks or other typical embedded database
> purposes, I don't see why SQL or relations are needed.  Berkeley DB is a
> transactional key-value store that's been around for decades and is way
> simpler than SQLite, and there's other things like that too.

I thought I recognised the name, so a quick search and found it was 
deprecated in Python 2.6, removed from 3.0.  Supported bindings 
available here https://www.jcea.es/programacion/pybsddb.htm for anybody 
who's interested.

>
> SQLite always seemed bloated (from the embedded NoSQL point of view) and
> fragile to me, and the vendor plays an annoying anti-forking trick,
> which is that the code is released but the developers' test suite is
> secret and proprietary (can be licensed from them for big bucks).  So if
> you want to make your own version of SQLite you have to either pay for
> the test suite, or have much less reliability assurance for your patched
> version than the vendor has for their version.  Add that Sqlite is
> written in C (think of naked whirling razor blades) and you've got a
> pretty serious disincentive against modification.
>

Thanks for the above.  I've been meaning to take a look at how SQLite is 
tested for months if not years.  That saves me the trouble :)

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [next] | [standalone]

#85995 — Re: 'Lite' Databases (Re: sqlite3 and dates)

[Multipart message — attachments visible in raw view] — view raw

On 02/20/2015 01:17 PM, Paul Rubin wrote:

> SQLite always seemed bloated (from the embedded NoSQL point of view) and
> fragile to me, and the vendor plays an annoying anti-forking trick,
> which is that the code is released but the developers' test suite is
> secret and proprietary (can be licensed from them for big bucks). 

Wow, really?  I had just started playing with it, but I don't think I'll bother now.

--
~Ethan~

[toc] | [prev] | [next] | [standalone]

#86005 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Sat, Feb 21, 2015 at 8:17 AM, Paul Rubin <no.email@nospam.invalid> wrote:
> Ben Finney <ben+python@benfinney.id.au> writes:
>> I don't know of a free-software concurrent RDBMS which can be considered
>> lighter than that. (No, MySQL doesn't count; its concurrency is
>> *unreliable* and it commonly loses data silently. Don't use MySQL.)
>
> I thought they fixed MySQL transactions years ago, with the InnoDB
> engine.  For some reason it's not the default, so you have to turn it on
> explicitly: is there more to it than that?

Even if you use InnoDB for all of _your_ tables, the system catalog
tables will all be MyISAM. So it's possible to lose critical metadata.

ChrisA

[toc] | [prev] | [next] | [standalone]

#86009 — Re: 'Lite' Databases

Chris Angelico <rosuav@gmail.com> writes:

> Even if you use InnoDB for all of _your_ tables, the system catalog
> tables will all be MyISAM. So it's possible to lose critical metadata.

In addition, MySQL silently [0] loses data in many common situations.

    <URL:https://dev.mysql.com/doc/refman/5.6/en/silent-column-changes.html>
    <URL:http://www.davidpashley.com/2009/02/15/silently-truncated/>
    <URL:http://effectivemysql.com/downloads/MySQLIdiosyncrasiesThatBITE.pdf>

That it loses data so *by default* is what makes MySQL strongly
anti-recommended. Having an insane default, with an obscure specific
toggle switch to turn off only that particular insanity, should not
significantly improve one's estimation of MySQL.

That it has a great many such insane defaults, that one must discover
one at a time, and many of them result in silent data loss, should be
enough to swear off MySQL forever.


[0] There are warnings created for many of these data losses. Warnings
    are inadequate in MySQL, because they are ignored by default, do not
    interrupt the operation, require specific action to retrieve, and
    become inaccessible immediately after the operation. This counts as
    “silent” in my assessment.

-- 
 \      “I don't know half of you half as well as I should like, and I |
  `\   like less than half of you half as well as you deserve.” —Bilbo |
_o__)                                                          Baggins |
Ben Finney

[toc] | [prev] | [next] | [standalone]

#86011 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 2015-02-20 13:17, Paul Rubin wrote:
> For stuff like browser bookmarks or other typical embedded database
> purposes, I don't see why SQL or relations are needed.  Berkeley DB
> is a transactional key-value store that's been around for decades
> and is way simpler than SQLite, and there's other things like that
> too.

Well, for Steven's purposes, both fail ungracefully on network
shares.  From my understanding, BDB fails even harder (whereas the
sqlite doesn't so much fail as keep a locked write transaction locked
if it fails at the wrong time, so you have to force an unlock)

And all said, sqlite doesn't add that much overhead compared to the
many other libraries I've used that aren't nearly so well-tested.

-tkc

[toc] | [prev] | [next] | [standalone]

#86014 — Re: 'Lite' Databases

On Sat, Feb 21, 2015 at 2:13 PM, Ben Finney <ben+python@benfinney.id.au> wrote:
> In addition, MySQL silently [0] loses data in many common situations.
>
>     <URL:https://dev.mysql.com/doc/refman/5.6/en/silent-column-changes.html>
>     <URL:http://www.davidpashley.com/2009/02/15/silently-truncated/>
>     <URL:http://effectivemysql.com/downloads/MySQLIdiosyncrasiesThatBITE.pdf>
>

Right, I'd forgotten about silent truncations. Though to be fair, the
first link refers to some changes which aren't a problem:

> Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way.

The requirement that a PK consist of non-nullable columns is part of
other databases, too, and having the words "PRIMARY KEY" imply "NOT
NULL PRIMARY KEY" is fine IMO. Back when I used DB2 version 5, I used
to use "ID SMALLINT NOT NULL PRIMARY KEY" at the beginning of most of
my table definitions, and all this "silent change" does is allow you
to abbreviate that.

> Trailing spaces are automatically deleted from ENUM and SET member values when the table is created.

Which means they're built on top of CHAR columns, not VARCHAR. Not a
huge deal IMO, though definitely something to be aware of.

Some of the other issues are more concerning, but these two don't
bother me, anyway.

The other two links, though? Definitely problems. Silent truncation of
data is a pest, and it gets even worse than that. I tried to put
together a test-case to see if I could do the classic "break two
UTF-16 strings and construct a brand new character out of them" trick,
but found something even worse.

# -*- encoding: UTF-8 -*-
from __future__ import print_function
import MySQLdb
# If you don't explicitly say charset="UTF8", you get some default that you may
# not be able to trust. On my test box, it gave me Latin-1. Plus, you can't say
# charset="UTF-8" - you have to omit the hyphen. Not good, but not a gotcha as
# you get an immediate exception.
con = MySQLdb.connect("localhost","demo","demo","demodb",charset="UTF8")
cur = con.cursor()
# Minor nastiness: This produces a warning if the table doesn't exist.
# So automatically displaying warnings will produce annoying noise.
cur.execute("drop table if exists nasty")
# Don't forget, "utf8" doesn't mean UTF-8... no, you have to say "utf8mb4"!
cur.execute("create table nasty (id smallint primary key, payload
char(8) charset utf8mb4 not null)")
strings = (u"English: safe", u"ελληνικά: safe", u"\U0001F4A9: accurate")
cur.execute("insert into nasty values (1, %s), (2, %s), (3, %s)", strings)
cur.execute("select payload from nasty order by id")
print("Input\t\tOutput")
for input, output in zip(strings, cur):
    print(input,output[0],sep="\t")

Your terminal may or may not be able to display U+1F4A9, but it's an
accurate description of MySQL's handling of astral characters in this
demo. I don't even know what's going on here. Tested on MySQL 5.5 on
Debian Wheezy.

ChrisA

[toc] | [prev] | [next] | [standalone]

#86017 — Re: 'Lite' Databases (Re: sqlite3 and dates)

In article <54E7B0DA.7060303@stoneleaf.us>,
 Ethan Furman <ethan@stoneleaf.us> wrote:

> On 02/20/2015 01:17 PM, Paul Rubin wrote:
> 
> > SQLite always seemed bloated (from the embedded NoSQL point of view) and
> > fragile to me, and the vendor plays an annoying anti-forking trick,
> > which is that the code is released but the developers' test suite is
> > secret and proprietary (can be licensed from them for big bucks). 
> Wow, really?  I had just started playing with it, but I don't think I'll 
> bother now.

SQLite is one of the most widely-used, best-documented, best-tested, and 
well-respected software packages in the world.  It is used all over by 
the place on many different platforms.  The code, documentation, and 
some of the tests are in the public domain, freely usable and forkable 
by all (though I don't know why anyone would want to fork it).  It is 
true that part of the test suite is only released free to SQLite 
consortium members.  I imagine that is done as an incentive to help 
finance the on-going development and maintenance of SQLite.

http://en.wikipedia.org/wiki/SQLite
https://www.sqlite.org/about.html
https://www.sqlite.org/testing.html
https://www.sqlite.org/copyright.html

-- 
 Ned Deily,
 nad@acm.org

[toc] | [prev] | [next] | [standalone]

#86019 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Ned Deily <nad@acm.org> writes:
> (though I don't know why anyone would want to fork it).  

Same reason lots of people have forked Postgres.  Or you might just want
to customize it.

> I imagine that is done as an incentive to help
> finance the on-going development and maintenance of SQLite.

It's a pretty unusual and annoying trick that other projects have not
felt they had to resort to.

> https://www.sqlite.org/testing.html

Thanks, that's the page I remember.  The TH3 test suite is the
interesting one and you can't get it without paying a lot of $$$$$.  I
guess there is some semantic quibble possible about whether you pay for
the test suite, or (as they put it) pay for Consortium membership and
then (as a member) get the test suite for free.

Do you know the situation with the SQL Logic Test (SLT) also mentioned
on that page?

[toc] | [prev] | [next] | [standalone]

#86021 — Re: 'Lite' Databases (Re: sqlite3 and dates)

In article <871tljepea.fsf@jester.gateway.pace.com>,
 Paul Rubin <no.email@nospam.invalid> wrote:

> Ned Deily <nad@acm.org> writes:
> > (though I don't know why anyone would want to fork it).  
> 
> Same reason lots of people have forked Postgres.  Or you might just want
> to customize it.

Well, for whatever reason one might have, one can: it's public domain 
software.

> > I imagine that is done as an incentive to help
> > finance the on-going development and maintenance of SQLite. 
> It's a pretty unusual and annoying trick that other projects have not
> felt they had to resort to.
> 
> > https://www.sqlite.org/testing.html
> 
> Thanks, that's the page I remember.  The TH3 test suite is the
> interesting one and you can't get it without paying a lot of $$$$$.  I
> guess there is some semantic quibble possible about whether you pay for
> the test suite, or (as they put it) pay for Consortium membership and
> then (as a member) get the test suite for free.

I don't have any special knowledge of the history or current status of 
the Consortium but it's not difficult to find information and blog posts 
about it.  It seems like this was an approach Richard Hipp and major 
users of SQLite took to ensure a sustaining funding model for the 
project while ensuring its independence.  It strikes me as a very 
reasonable and modest constraint given the immense good that the SQLite 
project has done for so many other projects over the years.  Many other 
less important projects have foundered for lack of sustained funding.

https://www.sqlite.org/consortium.html
https://blog.lizardwrangler.com/2008/02/27/the-sqlite-consortium/
 
> Do you know the situation with the SQL Logic Test (SLT) also mentioned
> on that page?

No

-- 
 Ned Deily,
 nad@acm.org

[toc] | [prev] | [next] | [standalone]

#86022 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Ned Deily <nad@acm.org> writes:
>> Same reason lots of people have forked Postgres.  Or you might just want
>> to customize it.
> Well, for whatever reason one might have, one can: it's public domain 
> software.

Yes, but unlike with most FOSS software, your version has much lower
quality assurance than the "official" version because you don't have the
big test suite.  Even if you don't fork or change the code at all, but
you just port it to a new platform or compiler, you really should run
the full set of tests, but you can't.

> It seems like [the SQLite consortium] was an approach Richard Hipp
> and major users of SQLite took to ensure a sustaining funding model
> for the project while ensuring its independence.

The main benefit of the consortium seems to be very close an intense
support from the SQLite core developers, including customization and
porting services.  Consortium membership apparently starts at $75K a
year so I doubt anyone joins just to get the test suite.  There is some
mention of separate licenses just for the test suite but I didn't see a
price tag and I wonder if that generates significant revenue compared to
the consortium.

> given the immense good that the SQLite project has done for so many
> other projects over the years.

Maybe I should look into it more.  I've never particularly felt the need
for it since I've either used client/server databases or else simpler
embedded databases like bsddb or even flat files.  I actually think
Macid/Happstack-state is brilliant but I haven't used it yet.

> Many other less important projects have foundered for lack of
> sustained funding.

Other FOSS databases like Postgres, MySQL, MongoDB, Riak, Cassandra,
etc. all seem to be doing fine.  GNAT Ada (GCC-based Ada compiler and
surrounding ecosystem) is split into free and proprietary components and
you have to pay pretty big to get the proprietary parts, but the free
parts aren't hobbled in any way (like missing tests) AFAIK.

[toc] | [prev] | [next] | [standalone]

#86062 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/21/2015 01:22 AM, Ned Deily wrote:
> SQLite is one of the most widely-used, best-documented, best-tested, 
> and well-respected software packages in the world. 
yes but is still sql.  there are a couple of small scale not-sql 
databases that look interesting.  problem with them is that the creator 
seem to subscribe to the build-and-toss-into-the-wild school of development.

http://buzhug.sourceforge.net/
http://www.pydblite.net/en/index.html

both are useful, both could use multi-writer support, and both need some 
love from the python world.

--- eric

[toc] | [prev] | [next] | [standalone]

#85846 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/18/2015 04:03 PM, Ben Finney wrote:

>> Is there anything *good* that sits in between the two extremes of
>> SQLite and PostgreSQL?
>
> What do you need a RDBMS to do, and what do you not need?
>
> The answers to those questions vary hugely between different people (and
> most people probably don't think too deeply about them). They will
> determine what “good” means for your case.
>
>> Is there nothing that amounts to a 'PostgreSQLite'?
>
> PostgreSQL itself fits that mould quite well; it is quite capable of
> serving a small footprint while still offering full concurrency.
>
> I don't know of a free-software concurrent RDBMS which can be considered
> lighter than that. (No, MySQL doesn't count; its concurrency is
> *unreliable* and it commonly loses data silently. Don't use MySQL.)
>
> But perhaps you don't need concurrency? Only you can tell us.
>

At this point... I don't think concurrency is going to be a major 
requirement for what I have in mind.  For one project, only a few people 
will be writing to the DB, and only by a stroke of luck would it be at 
the same time, and it would be very unlikely that they would be 
modifying the same record at the same time due to physical constraints.

For the other... there may be anywhere from 1-10 (maybe more, but 
doubtful) entering data (creating new records for competitors, or 
entering existing competitors in a tournament).  I have a hard time 
picturing that few people stressing a modern computer system enough to 
where SQLite couldn't keep up (thinking web-based interface using Flask 
or something similar).  In the latter case, one of the over-arching 
priorities is that it be easily distributable, as in that people with 
relatively little knowledge of a database be able to set it up and run it.

-- 
Shiny!  Let's be bad guys.

Reach me @ memilanuk (at) gmail dot com

[toc] | [prev] | [next] | [standalone]

#85849 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Thu, Feb 19, 2015 at 2:33 PM, memilanuk <memilanuk@gmail.com> wrote:
> At this point... I don't think concurrency is going to be a major
> requirement for what I have in mind.  For one project, only a few people
> will be writing to the DB, and only by a stroke of luck would it be at the
> same time, and it would be very unlikely that they would be modifying the
> same record at the same time due to physical constraints.
>
> For the other... there may be anywhere from 1-10 (maybe more, but doubtful)
> entering data (creating new records for competitors, or entering existing
> competitors in a tournament).  I have a hard time picturing that few people
> stressing a modern computer system enough to where SQLite couldn't keep up
> (thinking web-based interface using Flask or something similar).  In the
> latter case, one of the over-arching priorities is that it be easily
> distributable, as in that people with relatively little knowledge of a
> database be able to set it up and run it.

Both of these need concurrency. You may not need _heavy_ concurrency,
but you certainly do need to cope adequately with multiple
simultaneous users. Your first case is a perfect example of why you
need a database rather than flat files; in fact, you want the
granularity of record-level locking rather than table-level. Alas,
SQLite3 does not actually offer this (in fact, I'm not sure it even
offers table-level locking); once any process begins writing to the
database, all others are locked out (even for reading) until it
finishes. That's fine if you (a) don't write very often, and (b) don't
write very much, but the fact that you're trying to modify different
records doesn't help you here. It does with full-scale database
systems, where you actually do have record-level locking, but not with
SQLite3.

Your second case definitely demands concurrency. I've seen tournaments
for various games where database-level write locking would be a
critical problem, and that with only a couple hundred players and a
handful of people keying in data. Of course, it depends how much
effort it takes to key that in. If the humans have to enter extensive
reports on the tournament results, they'll spend most of their time
doing that; but if their job is to quickly say "X beat Y 2-1" and then
get back results saying "X plays Z next, Y gets a bye", then you need
your database to react quickly, even if three other people are
entering results. So it's a huge question of human versus computer
workload... but once again, chances are you need record-level locking.

It may very well turn out that SQLite3 is entirely capable of the job.
But it's certainly not proven by your above statements, and I would
start by assuming PostgreSQL by default.

ChrisA

[toc] | [prev] | [next] | [standalone]

#85852 — Re: 'Lite' Databases (Re: sqlite3 and dates)

memilanuk <memilanuk@gmail.com> writes:

> At this point... I don't think concurrency is going to be a major
> requirement for what I have in mind.

What's the difference betwen a “requirement” and a “major requirement”?

If you want networked access, you need concurrent access and access
permissions, etc.

SQLite does not have concurrent access. Once you require concurrent
access, you need something more complex, like PostgreSQL.

> I have a hard time picturing that few people stressing a modern
> computer system enough to where SQLite couldn't keep up (thinking
> web-based interface using Flask or something similar). In the latter
> case, one of the over-arching priorities is that it be easily
> distributable, as in that people with relatively little knowledge of a
> database be able to set it up and run it.

Set it up where? Are you hoping that a network-accessible service can be
set up without knowledge of the specific concurrent authenticated
networked access is needed in each installation?

-- 
 \         “Broken promises don't upset me. I just think, why did they |
  `\                                         believe me?” —Jack Handey |
_o__)                                                                  |
Ben Finney

[toc] | [prev] | [next] | [standalone]

#85855 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/18/2015 09:09 PM, Ben Finney wrote
> memilanuk <memilanuk@gmail.com> writes:
>[...]
> If you want networked access, you need concurrent access and access
> permissions, etc.

Sqlite has concurrent access.  It doesn't have concurrent 
access that will support a large number of writers or high 
volume of writes.

As for access permissions, it is common, even with Postgresql
to do all database access through a single Postgresql user 
and to implement authorization and access permission in the 
application.

> SQLite does not have concurrent access. Once you require concurrent
> access, you need something more complex, like PostgreSQL.

Please read https://www.sqlite.org/faq.html#q5

>[...]

[toc] | [prev] | [next] | [standalone]

#85873 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Ben Finney wrote:

> What's the difference betwen a “requirement” and a “major requirement”?

"I require a gold-plated Mercedes, if I can have it for less than $30,000. 
My major requirement is for some sort of personal transport."


*wink*


-- 
Steve

[toc] | [prev] | [next] | [standalone]

#85854 — When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)]

[Multipart message — attachments visible in raw view] — view raw

At the risk of using actual data, I looked this up at http://www.sqlite.org/whentouse.html:


Checklist For Choosing The Right Database Engine

 * Is the data separated from the application by a network? → choose client/server

    Relational database engines act as a bandwidth-reducing data filter. So it is best to keep the database engine and
the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the
network, only the lower-bandwidth application-to-engine link.

    But SQLite is built into the application. So if the data is on a separate device from the application, it is
required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence,
it is usually better to select a client/server database engine when the data is on a separate device from the application.

 * Many concurrent writers? → choose client/server

    If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take
turns) then it is best to select a database engine that supports that capability, which always means a client/server
database engine.

    SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes
milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people
suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to
coordinate access, can usually handle far more write concurrency than SQLite ever will.

 * Big data? → choose client/server

    If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you
should select a solution other than SQLite. SQLite supports databases up to 140 terabytes in size, assuming you can find
a disk drive and filesystem that will support 140-terabyte files. Even so, when the size of the content looks like it
might creep into the terabyte range, it would be good to consider a centralized client/server database.

 * Otherwise → choose SQLite!

    For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a
better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple.
SQLite "just works".

[toc] | [prev] | [next] | [standalone]

Page 3 of 4 — ← Prev page 1 2 [3] 4  Next page →

Back to top | Article view | comp.lang.python

csiph-web