Thoughts on "Love Your Database"

owner@postgresql.org] On Behalf Of Guyren Howe

I've long been frustrated with how most web developers I meet have no idea
how to use an SQL database properly. I think I'm going to write a book called
Love Your Database, aimed at web developers, that explains how to make their
apps better by leveraging the power of SQL in general, and Postgres in
particular.

I'm thinking of a section on features of SQL most folks don't know about
(CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
and window functions), but much of the book would be about how to do things
server side. Benchmarks showing how much faster this can be, but mostly
techniques — stored procedures/triggers/rules, views.

I asked a colleague about the advice I often hear stated but seldom
justified, that one shouldn't put business rules in the database. He offered
that server-side code can be hard to debug.

I'm sure many here would love to see such a book published, maybe some talks
on the topic given.

What might I cover that I haven't mentioned? What are the usual objections to
server-side code and how can they be met? When *are* they justified and what
should the criteria be to put code in Postgres? Any other thoughts? Any other
websites or books on the topic I might consult?

I'm a strong believer in putting the business code next to the data, not the wrong side of the object-relational divide. However, for many the challenge of writing and debugging SQL code is just too high! The SQL language on many servers can do everything but it's clunky, lacks modern language features, is hard to debug, often fragile and really hard to get right.

The only thing I can give you that might help is a cookbook on how to do it right: http://thehelsinkideclaration.blogspot.com.au/2009/03/window-on-data-applications.html.

Meanwhile I'm busy replacing SQL with Andl, which can do everything SQL gets right and avoids most of the things it gets wrong. Look out for an implementation on Postgres real soon now. See http://www.andl.org/2016/04/postgres-meet-andl/.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Good morning

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Guyren Howe
Sent: Mittwoch, 4. Mai 2016 06:11
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] Thoughts on "Love Your Database"

I've long been frustrated with how most web developers I meet have no idea how to use an SQL database properly. I
think I'm going to write a book called Love Your Database, aimed at web developers, that explains how to make their
apps better by leveraging the power of SQL in general, and Postgres in particular.

I'm thinking of a section on features of SQL most folks don't know about (CTEs are *way* to hell at the top of that
list, but also EXCEPT/INTERSECT and window functions), but much of the book would be about how to do things server
side. Benchmarks showing how much faster this can be, but mostly techniques — stored procedures/triggers/rules,
views.

I asked a colleague about the advice I often hear stated but seldom justified, that one shouldn't put business rules
in the database. He offered that server-side code can be hard to debug.

I'm sure many here would love to see such a book published, maybe some talks on the topic given.

What might I cover that I haven't mentioned? What are the usual objections to server-side code and how can they be
met? When *are* they justified and what should the criteria be to put code in Postgres? Any other thoughts? Any
other websites or books on the topic I might consult?

If you have a complex design or if the processes require the modification of various tables within a transaction you may probably prefer to expose functions as the application interface. Advantages of this approach:

- Hide complexity: You don't need to explain all the details, dependencies and implications to all web developers. Just make sure that your documentation is up-to-date for those who want to learn about it.
- Transactions are controlled by the database: You may have doubts if application developers do handle this correctly.
- Minimize the impact on application development: If changes to requirements force changes in the database, these would be transparent to the application. Even if the interface changes, that may mean only an additional argument to a function.
- Security: You can grant execute on (security definer) functions instead of granting privileges for each object. The latter can become quite complex.
- Separation of concerns: Application developers don't need to (but can if they want) learn SQL. They should focus instead on the presentation layer, which at the end is what customers see and sells.

Bye
Charles

TIA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I think one of the key questions is when to put logic in the database (and
this is controversial so it may be worth covering from a few angles). In
general my view is:

1. Data logic belongs in the database
2. Logic you can't roll back belongs somewhere else
3. A lot of stuff could go either place.

Another thing I would recommend is looking at SQL in a way that is similar
to map(), reduce(), and filter() (using python terms but you can find
similar in other languages).

Additionally some functional programming theory can go a long way in both
understanding database normalization and putting logic in the database.

On Wed, May 4, 2016 at 6:11 AM, Guyren Howe <guyren@gmail.com> wrote:

I've long been frustrated with how most web developers I meet have no idea
how to use an SQL database properly. I think I'm going to write a book
called Love Your Database, aimed at web developers, that explains how to
make their apps better by leveraging the power of SQL in general, and
Postgres in particular.

I'm thinking of a section on features of SQL most folks don't know about
(CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
and window functions), but much of the book would be about how to do things
server side. Benchmarks showing how much faster this can be, but mostly
techniques — stored procedures/triggers/rules, views.

I asked a colleague about the advice I often hear stated but seldom
justified, that one shouldn't put business rules in the database. He
offered that server-side code can be hard to debug.

I'm sure many here would love to see such a book published, maybe some
talks on the topic given.

What might I cover that I haven't mentioned? What are the usual objections
to server-side code and how can they be met? When *are* they justified and
what should the criteria be to put code in Postgres? Any other thoughts?
Any other websites or books on the topic I might consult?

TIA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more

On Tue, 3 May 2016 23:11:06 -0500
Guyren Howe <guyren@gmail.com> wrote:

I've long been frustrated with how most web developers I meet have no idea how to use an SQL database properly. I think I'm going to write a book called Love Your Database, aimed at web developers, that explains how to make their apps better by leveraging the power of SQL in general, and Postgres in particular.

I'm thinking of a section on features of SQL most folks don't know about (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT and window functions), but much of the book would be about how to do things server side. Benchmarks showing how much faster this can be, but mostly techniques ? stored procedures/triggers/rules, views.

I asked a colleague about the advice I often hear stated but seldom justified, that one shouldn't put business rules in the database. He offered that server-side code can be hard to debug.

I'm sure many here would love to see such a book published, maybe some talks on the topic given.

What might I cover that I haven't mentioned? What are the usual objections to server-side code and how can they be met? When *are* they justified and what should the criteria be to put code in Postgres? Any other thoughts? Any other websites or books on the topic I might consult?

Not a specific topic, but as a general theme, a lot of developers don't
seem to think it's useful for them to know SQL, and therefore don't
bother trying -- or even actively resist learning.

So if the overall theme is "knowing this makes things better", I would
buy multiple copies of the book an mysteriously leave it on various
developer's desks.

--
Bill Moran

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 4 May 2016 at 06:46, dandl <david@andl.org> wrote:

I'm a strong believer in putting the business code next to the data, not the wrong
side of the object-relational divide. However, for many the challenge of writing and
debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug. I'm afraid
that the problem seems to me to be that your peg is rather too square.

Meanwhile I'm busy replacing SQL with Andl, which can do everything SQL gets
right and avoids most of the things it gets wrong. Look out for an implementation
on Postgres real soon now. See http://www.andl.org/2016/04/postgres-meet-andl/.

Please, can you stop spamming every marginally-related topic in the
list with this? I'm sure that anyone who's interested in this will
have seen it in the thread you created that was actually marked with
it in the subject.

Geoff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A few observations

On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

On 4 May 2016 at 06:46, dandl <david@andl.org> wrote:

I'm a strong believer in putting the business code next to the data, not

the wrong

side of the object-relational divide. However, for many the challenge of

writing and

debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug. I'm afraid
that the problem seems to me to be that your peg is rather too square.

I actually agree with dandl on this. Folks can write SQL but often aren't
really comfortable using it as core application logic.

I.e. one often sees code that retrieves a bunch of records from the db,
loops through them, and transforms the data as part of the OLTP workflow.
It is obviously much better of one can think about SQL as business logic
but this is not that often.

I.e. people think the peg is square but indeed it is round.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more

On 4 May 2016 at 13:13, Chris Travers <chris.travers@gmail.com> wrote:

A few observations

On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless <pgsqladmin@geoff.dj>
wrote:

On 4 May 2016 at 06:46, dandl <david@andl.org> wrote:

I'm a strong believer in putting the business code next to the data,

not the wrong

side of the object-relational divide. However, for many the challenge

of writing and

debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug. I'm afraid
that the problem seems to me to be that your peg is rather too square.

I actually agree with dandl on this. Folks can write SQL but often aren't
really comfortable using it as core application logic.

I.e. one often sees code that retrieves a bunch of records from the db,
loops through them, and transforms the data as part of the OLTP workflow.
It is obviously much better of one can think about SQL as business logic
but this is not that often.

I.e. people think the peg is square but indeed it is round.

From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually the
management said "no, that's not doable, as we will have trouble with
finding good sql programmers later", and we were still writing all the
logic outside the database.

--
regards Szymon Lipiński

owner@postgresql.org] On Behalf Of Geoff Winkless

I'm a strong believer in putting the business code next to the data,
not the wrong side of the object-relational divide. However, for many
the challenge of writing and debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like weasel-
words to me. In my experience, a wide range of people, from beginners to
experts, find SQL easy to write and debug. I'm afraid that the problem seems
to me to be that your peg is rather too square.

Then I think you've seriously misunderstood. Most people can indeed learn to write basic SQL queries, but those are (obviously) not what I'm talking about.

To write the business logic of a significant application entirely in SQL requires PLSQL (or in other dialects, whatever passes for SQL/PSM). It means writing an entire data access layer as a set of stored procedures, with a substantial set of special functions, types, triggers and so on. No beginner and few experts have the skills required to do that in SQL, and then debug that code on the server. The plain aim of Andl is make this task far, far easier so that indeed a beginner can do it.

Meanwhile I'm busy replacing SQL with Andl, which can do everything
SQL gets right and avoids most of the things it gets wrong. Look out
for an implementation on Postgres real soon now. See

http://www.andl.org/2016/04/postgres-meet-andl/.

Please, can you stop spamming every marginally-related topic in the list with
this? I'm sure that anyone who's interested in this will have seen it in the
thread you created that was actually marked with it in the subject.

The man asked a question and I gave him two links that provide specific parts of my answer. I think you would learn something from reading them, but perhaps the simplest solution is that you just ignore my posts in future.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 4 May 2016 at 06:11, Guyren Howe <guyren@gmail.com> wrote:

I've long been frustrated with how most web developers I meet have no idea
how to use an SQL database properly. I think I'm going to write a book
called Love Your Database, aimed at web developers, that explains how to
make their apps better by leveraging the power of SQL in general, and
Postgres in particular.

I'm thinking of a section on features of SQL most folks don't know about
(CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
and window functions), but much of the book would be about how to do things
server side. Benchmarks showing how much faster this can be, but mostly
techniques — stored procedures/triggers/rules, views.

I asked a colleague about the advice I often hear stated but seldom
justified, that one shouldn't put business rules in the database. He
offered that server-side code can be hard to debug.

I'm sure many here would love to see such a book published, maybe some
talks on the topic given.

What might I cover that I haven't mentioned? What are the usual objections
to server-side code and how can they be met? When *are* they justified and
what should the criteria be to put code in Postgres? Any other thoughts?
Any other websites or books on the topic I might consult?

TIA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
at my previous jobs I was working with many programmers, and almost none of
them understood SQL. The problem was even deeper. They didn't want to learn
it. When I was working among java programmers, I noticed that they hated
SQL, but there was no problem with learning HQL, which in fact is quite
similar. I really have no idea why it is like that.

I was thinking about such a book too, I have even started writing that, but
the whole set of ideas was always either too large or was a copy of what
can be found in other, general programming books. And if programmers don't
want to read the other books, and learn basics of SQL from there, then the
whole idea seemed useless to me. Of course the other reason of not writing
the book was lack of time, as writing a good book requires enormous amount
of work.

On the other hand I think that the huge problem with programmers and SQL is
changing the mindset. A standard programmer usually has a problem with
thinking in sets. Instead she usually thinks in terms of loops, and objects.

When I was giving talks about what not to do in databases, people were
either not interested or everything was a surprise for them, even for quite
experienced programmers. However after such a talk, or a training, people
were still not interested in knowing more, but they were happily learning
about programming.

I think it would be great to have a book like that, and I think it should
not be only about Postgres. But here is the problem I had with this
concept: to describe all the things to a normal programmer, assuming she
will be interested, it will need to be quite a huge and complicated book.

Or maybe this book should be about something else, start from an ORM, and
show how to translate it to much better SQL, as ORMs are the things
programmers usually understand, and they really don't bother that using
them can be a bad idea.

--
regards Szymon Lipiński

On Wed, May 4, 2016 at 7:55 AM, Szymon Lipiński <mabewlun@gmail.com> wrote:
​<snip>​

Hi,
at my previous jobs I was working with many programmers, and almost none
of them understood SQL. The problem was even deeper. They didn't want to
learn it. When I was working among java programmers, I noticed that they
hated SQL, but there was no problem with learning HQL, which in fact is
quite similar. I really have no idea why it is like that.

I was thinking about such a book too, I have even started writing that,
but the whole set of ideas was always either too large or was a copy of
what can be found in other, general programming books. And if programmers
don't want to read the other books, and learn basics of SQL from there,
then the whole idea seemed useless to me. Of course the other reason of not
writing the book was lack of time, as writing a good book requires enormous
amount of work.

On the other hand I think that the huge problem with programmers and SQL
is changing the mindset. A standard programmer usually has a problem with
thinking in sets. Instead she usually thinks in terms of loops, and objects.

​Very true. What _really_ was of help to me was the fact that I had learned
APL in college. APL is an vector / array oriented language with operators
which work on entire data structures, rather than individual array
elements. E.g. to sum all the number in a vector is simply "+/vector".

When I was giving talks about what not to do in databases, people were
either not interested or everything was a surprise for them, even for quite
experienced programmers. However after such a talk, or a training, people
were still not interested in knowing more, but they were happily learning
about programming.

I think it would be great to have a book like that, and I think it should
not be only about Postgres. But here is the problem I had with this
concept: to describe all the things to a normal programmer, assuming she
will be interested, it will need to be quite a huge and complicated book.

Or maybe this book should be about something else, start from an ORM, and
show how to translate it to much better SQL, as ORMs are the things
programmers usually understand, and they really don't bother that using
them can be a bad idea.

--
regards Szymon Lipiński

​What I have heard of are shops in which the application programmers simply
DO NOT CODE any SQL. They only use stored procedures which they view as
subroutine calls. The stored procedures are written by the DBAs and other
SQL experts. That group has hard control over the enterprise data. If the
programmers need something, they become like end-user in that they simply
describe what data they need and why. The programmers basically know enough
to be able to ask for something. Completely unlike back in the days of
indexed (DBM basically) files​.

--
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

On 4 May 2016 at 13:36, Szymon Lipiński <mabewlun@gmail.com> wrote:

On 4 May 2016 at 13:13, Chris Travers <chris.travers@gmail.com> wrote:

A few observations

On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless <pgsqladmin@geoff.dj>
wrote:

On 4 May 2016 at 06:46, dandl <david@andl.org> wrote:

I'm a strong believer in putting the business code next to the data,

not the wrong

side of the object-relational divide. However, for many the challenge

of writing and

debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug. I'm afraid
that the problem seems to me to be that your peg is rather too square.

I actually agree with dandl on this. Folks can write SQL but often
aren't really comfortable using it as core application logic.

I.e. one often sees code that retrieves a bunch of records from the db,
loops through them, and transforms the data as part of the OLTP workflow.
It is obviously much better of one can think about SQL as business logic
but this is not that often.

I.e. people think the peg is square but indeed it is round.

From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually the
management said "no, that's not doable, as we will have trouble with
finding good sql programmers later", and we were still writing all the
logic outside the database.

Yeah. The classic "We have a bunch of scissors, so use them instead of
screwdrivers. Oh, and by the way, please redesign our screws, to make them
more scissor compatible." approach. :)
The real shame, when they buy an expensive, feature-rich DBMS, run it on a
kick ass hardware and then using it as a glorified file cabinet.

Regards,
Sándor

On 4 May 2016 at 12:36, Szymon Lipiński <mabewlun@gmail.com> wrote:

From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually the
management said "no, that's not doable, as we will have trouble with finding
good sql programmers later", and we were still writing all the logic outside
the database.

"Finding good programmers later" will always be hard. There are less
of them about than Human Resources would like to believe.

Putting your logic in a different layer or in a different language
over the top of SQL doesn't mean you won't need good programmers
later; quite the reverse, because now you need programmers who are
both strong in SQL _and_ good enough to understand the layer you've
added on top.

On the other hand, if you're planning on putting _some_ of your logic
into the database, then I probably see where they're coming from. If
you give full database access to application developers (rather than
providing them with stored procedures that perform the tasks for them)
then they will be surprised as hell when the database does something
they weren't expecting because of some business rules that are in a
fourth-level trigger somewhere.

The sensible way is to do it as John wrote - to restrict access rights
to everyone except admin to calling functions only. That way the
functions are written by the people who are paid to understand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that pure model, though. :)

Geoff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

​<snip>

The sensible way is to do it as John wrote - to restrict access rights
to everyone except admin to calling functions only. That way the
functions are written by the people who are paid to understand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that pure model, though. :)

​Allowing PHBs direct access t​o company data is a nasty thing. They become
like some users who "know Excel". They are now just as knowledgeable as
someone who's been doing this for years. I've actually heard one say
something akin to: "Damn it, I can write Excel formulas. I know very well
that an new function on the web site could be written in less than a day,
if you'd just get off you a$$ and do it."

Geoff

--
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

On Wed, May 4, 2016 at 9:25 AM, John McKown <john.archie.mckown@gmail.com>
wrote:

On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless <pgsqladmin@geoff.dj>
wrote:

​<snip>

The sensible way is to do it as John wrote - to restrict access rights
to everyone except admin to calling functions only. That way the
functions are written by the people who are paid to understand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that pure model, though. :)

​Allowing PHBs direct access t​o company data is a nasty thing. They
become like some users who "know Excel". They are now just as knowledgeable
as someone who's been doing this for years. I've actually heard one say
something akin to: "Damn it, I can write Excel formulas. I know very well
that an new function on the web site could be written in less than a day,
if you'd just get off you a$$ and do it."

Geoff

--
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

What might I cover that I haven't mentioned?

Well, I'm pretty sure that one of the reasons Web Developers do not use SQL
is because they do not know what is in the database. Perhaps a sections
that teaches them how to list the tables and columns
from the INFORMATION_SCHEMA would be a good start.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Hi,

Le 04/05/2016 13:36, Szymon Lipiński a écrit :

On 4 May 2016 at 13:13, Chris Travers <chris.travers@gmail.com
<mailto:chris.travers@gmail.com>> wrote:
A few observations

On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless <pgsqladmin@geoff.dj
<mailto:pgsqladmin@geoff.dj>> wrote:

On 4 May 2016 at 06:46, dandl <david@andl.org
<mailto:david@andl.org>> wrote:

I'm a strong believer in putting the business code next to the data, not the wrong
side of the object-relational divide. However, for many the challenge of writing and
debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug.

Yes, I agree. SQL is just crystal-clear to write, read and understand. I
found out that debugging is usually not a common exercise in SQL,
because the language is so trivial.

...

From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually
the management said "no, that's not doable, as we will have trouble with
finding good sql programmers later",

Shocking! Apart from very few languages I know, SQL is by far more
productive and efficient, for many-many tasks.

and we were still writing all the logic outside the database.

I used to implement the logic outside the database, like you mention,
*but* I was writing plain SQL. Only when I had specific needs, then I
would switch to another language which would just get the results from a
well-polished plain SQL query, process, and feed back things into the
database (with another well-polished SQL, of course) or just throw the
results out somewhere else (file, screen, picture, whatever). No ORM or
any complication.

And I find SQL fairly easy to debug and maintain, no need for fancy
tools: an editor and a console (psql or equivalent) and you're up and going!

Nowadays, things got quite different, and I tend to stuff more and more
logic inside the database. Which is often merely converting SQL queries
into views...

But it comes with a counterpart: the more you put logic inside your
DBMS, the more dependent you become. As far as I'm concerned, I recently
decided to just stick to PostgreSQL forever! (or almost)

À+
Pierre

PS: sorry for the double-reply, Szymon: I forgot *again* to hit
Shift-Ctrl-R instead of Ctrl-R, shame on me...
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1967@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le 04/05/2016 15:25, John McKown a écrit :

On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless <pgsqladmin@geoff.dj
<mailto:pgsqladmin@geoff.dj>>wrote:

​<snip>

The sensible way is to do it as John wrote - to restrict access rights
to everyone except admin to calling functions only. That way the
functions are written by the people who are paid to understand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that pure model, though. :)

​Allowing PHBs direct access t​o company data is a nasty thing.

Sorry, what is a PHB? Our friend google didn't help me much on this matter.

They become like some users who "know Excel". They are now just as
knowledgeable as someone who's been doing this for years. I've actually
heard one say something akin to: "Damn it, I can write Excel formulas. I
know very well that an new function on the web site could be written in
less than a day, if you'd just get off you a$$ and do it."

Hm. Sounds familiar...
I usually call "excelitis" a sort of mental disease related to a use and
abuse of Excel, up to the point where one cannot imagine data which is
*not* in a table-like array. And they think that they do Relational
Database Management... In the 1990's, I met many-many deeply sick
persons. I had been infected for a while, I must confess.

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1967@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le 04/05/2016 13:36, dandl a écrit :
...

Then I think you've seriously misunderstood. Most people can
indeed learn to write basic SQL queries, but those are
(obviously) not what I'm talking about.

To write the business logic of a significant application
entirely in SQL requires PLSQL (or in other dialects, whatever
passes for SQL/PSM). It means writing an entire data access
layer as a set of stored procedures, with a substantial set of
special functions, types, triggers and so on. No beginner and
few experts have the skills required to do that in SQL, and then
debug that code on the server.

All right, I understand better now. I think I also totally missed your
point, sorry...
I'll give a look at andl.

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1967@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

First, you hit them over the head with a copy of "SQL Antipatterns:
Avoiding the Pitfalls of Database Programming". It is a tad out of date and
tends to use PHP and MySQL for the main examples but does also address
different solutions available in PostgreSQL, Oracle. MS SQL server, etc.
while pointing out the risks of various common foot-guns and providing
alternatives.

Or point them to this recent Linux Journal article by Reuven Lerner (who is
occasionally seen on these lists):
http://www.linuxjournal.com/content/use-your-database

Developers often have a pre-Gallileo world view that they and whatever app
they are coding is the center of the universe and databases, networks,
storage and the rest all revolve around them existing only to support their
app.

But ultimately the church of the developer gets forced into the modern era
and finds that the data is at the center and the apps that allow input,
maintenance, extraction and analysis all revolve around those core crown
jewels. Then, *gasp*, there are other people and apps touching "your" data.
Are they all validating the data the way you do? Protecting it? Retrieving
it efficiently? Only then does the real value of the database come into
focus.

Cheers,
Steve

On Tue, May 3, 2016 at 9:11 PM, Guyren Howe <guyren@gmail.com> wrote:

A reason to consider may be portability. What happens if I want to let my
customer chose their data store or I just don't want to put all my eggs in
one basket.Technically there are truths but you cannot ignore the business
side either. If a we can exceed our performance requirements and keep
things generic/portable this is the best of both worlds.I think this is the
main reason people separate the business logic from the database. How many
of you have ported databases between platforms? Or had multiple types of
data stores in the same company?

On Wed, May 4, 2016 at 12:11 AM, Guyren Howe <guyren@gmail.com> wrote: