'Lite' Databases (Re: sqlite3 and dates)

[rurpy at yahoo.com]

On 02/19/2015 02:23 PM, Mario Figueiredo wrote:
> On Thu, 19 Feb 2015 12:26:04 -0800 (PST), rurpy at yahoo.com wrote:
>>
>> I'll point out that five people in this thread (by my
>> count) have said that Postgresql requires a significant
>> amount of work to setup and use.  Only you and Steven claim
>> the opposite.
> 
> Well, I claim the opposite too.
> 
>> Or I could have tried as you
>> and Steven suggest to "somehow" package Postgresql in my
>> app installer.  That would have been an even bigger cost
>> in my time with an uncertain outcome.
> 
> I don't see how. You said it was just a simple application this
> postgres database was serving. So I must assume you aren't talking of
> a complex setup that database synchronization or other features that
> force a manual setup.

Correct.

> For your purposes, you could just basically backup your data folder
> and package postgres with the same configuration files you have in
> your current computer.

What do you mean by "data folder"?  The directory in which 
Postgresql keeps its database data?  If so you're wrong.  That 
data is at a minimum architecture dependent.  It is also private  
to Postgresql and one would be ill advised to use that as a 
distribution format.  There is also no need to since installing
Postgresql and loading the initial data from some standard format 
is supported and will produce the same results. 

Or are you talking about some sort of data (csv files, sql files
of insert statements, etc) that you use to initially load tables
in your database.  That will be the same whether you are using
Postgresql or Sqlite so I don't see your point. 

>> None of those
>> costs would have been necessary at all had I developed
>> a self-contained Sqlite app.
> 
> The cost would have probably been much higher, depending on your
> project. SQLite would have forced you to move all your business logic
> into your code, greatly increasing code maintenance, your application
> extensibility and its ability to more easily adapt to new business
> requirements.

How so?  nobody's claimed that Sqlite is a replacement for 
Postgresql in large scale, high concurrency "heavy-duty" 
applications.  Ethan Furman posted a list of things that 
Sqlite does well from the Sqlite website.  Here is a link 
if you missed it:

  http://www.sqlite.org/whentouse.html

We are talking about using Postgresql as a backend for 
applications that fit on that list, ie applications like 
the applications already using Sqlite :-) 

>From previous posts I am guessing that what you are saying is 
that business logic should be implemented in stored procedures 
and because Sqlite does not offer stored procedures you can't 
implement business logic in Sqlite.

First, recall that we are not talking about a multi-tiered set 
of applications with a middleware layer and mutiuser backend.
We've established already that is the domain of servers like
Postgresql.

While I tend to agree with the idea that business logic 
should be in the database, I'm sure you're aware that that 
is not a universally held opinion and it is certainly not 
a universally implemented one in the domain of applications
we're talking about.  It is easy to find plenty of applications 
that implement all or part of the business logic in the app.  

Given Sqlite's architecture I'm not even sure you can say
it doesn't have stored procedures.  Since programmatic 
access is through the (python-)API you can write a module 
with all the business logic and decree that that all higher
level functions access the database through that module.
How is that effectively different than a set of stored
procedures in a client-server database?

And I can see a justification for not even going that far 
in some cases.  We are talking about small scale applications 
where the database can be considered an "implementation 
detail" of the application but it is easier to implement 
the application by taking advantage of the capabilities of 
a SQL relational database than trying to implement those 
storage details in some ad-hoc way.

> Conversely, if none of this is true concerning your particular
> project, then you just chose the wrong tool. Postgres was overkill for
> your particular needs and it was a mistake to think you need it to
> function just as a shelve on steroids.

Bingo!  You're catching on.  :-)  Again I remind you that no one
has said that Sqlite is a universal replacement for Postgresql

>> Finally keep in mind that if you develop your app using
>> Sqlite, it is likely to be far easier to migrate to
>> a heavy-duty backend like Postgresql later should you
>> need to than to go in the other direction when you find
>> out you didn't really need Postgresql after all and the
>> cost turned out to be higher than you expected.
> 
> Completely not true! For the reasons mentioned above. You are
> concentrating too much on the RDBMS aspects and completely forgetting
> about the implications in your codebase.
> 
> Whether you move from a non distributed model to a client-server
> model, or the other way around, your code will suffer major changes.

> And even if you decide to keep the business logic in the client layer
> (which is a mistake) 

Whether it is a mistake or not is not does not have a simple 
yes/no answer.   See above.

> when moving from SQLite to a client-server RDBMS,
> you will still have to deal with a whole new set of issues regarding
> the very nature of concurrent access that will essentially force you
> to scrap much of your previous code.

How so?  We've already determined that Sqlite provides
concurrent access (albeit on a limited scale).  So you've
already had to deal with it in the way you've designed 
the database.

I think you are saying that if you are moving to Postresql
that you will want to restructure your code to take advantage
of Postgreql's greater capabilities.  Sure, but your exiting
existing Sqlite design will still work while you do that
restructure incrementally.  The code will need changes to
adapt from Sqlite to Postgresql but that should be tractable.
The converse (moving from Postgreql back to Sqlite is much 
harder because now you have reimplement those nice features 
of Postgreql that you took advantage of when writing your 
application but that are hard to port to Sqlite.  So your 
app is broken until you do all that work.

>> It is bad advise to recommend using Postgresql without
>> regard to the developer's actual needs.
> 
> Naturally. But I must say postgres isn't the bad cat you painted in
> your post. It's much, much easier to distribute, deploy and manage
> than you are suggesting.

If you think I've been painting Postgresql as a "bad cat" 
I don't think you've been paying attention.  

  On 02/19/2015 01:26 PM, rurpy wrote:
  > I've used Postgresql for a number of small to medium size 
  > projects for work and personal use.  I too think it is an 
  > amazing piece of work for free  software.

What I have said is that there are many use cases for a SQL
relational database that don't need the features of Postgresql
and for which Sqlite is a lighter-weight, less costly alternative
and hence the better choice.  

Saying that something is not appropriate for use in every
possible environment is not the same as saying it's a "bad cat".

> For most systems where performance and database synchronization aren't
> a requirement, it can be entirely automated, I know, because that's
> how we had it set up on three schools where we sold our integrated
> management system. I haven't had a maintenance request call in 8
> months.

I never said it can't be done.  I claim it can't be done
anywhere as near as easily as you can with Sqlite
*if you don't require the capabilities of Postgresql*. 
How long did it take to develop your automated installer
and over how wide a variety of environments does it work?
Three sites, all institutional, is not what I'd call a
a wide variety.  They all have someone (perhaps defacto)
in the role of systems or network administrator?

> It's only under critical requirements that postgres necessitates a
> baby sitter. And those projects don't suffer from lack of competent
> administrators.

And you (or your users) handle database backups and Postgresql 
upgrades how?