A thought about other open source projects

On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:

These projects need help to realise that adding Postgresql is not a
big
job, especially for those using JDBC which can already connect to all
DBs. It strikes me that if the project could write a few pages
gleaned
from other porting operations, then whenever a project like this is
found
they can be pointed to these pages and shown how easy it is to do.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

On Sunday 20 June 2010, Peter Eisentraut wrote:

On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:

These projects need help to realise that adding Postgresql is not a
big
job, especially for those using JDBC which can already connect to all
DBs. It strikes me that if the project could write a few pages
gleaned
from other porting operations, then whenever a project like this is
found
they can be pointed to these pages and shown how easy it is to do.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS

QL

Excellent, I had not realised this existed. I will point any projects I meet
which have not found Postrgesql goodness at this page. Thank you.

I don't support anyone has written a "how to write database agnostic
code" guide? That way its not a matter of porting, more a matter of
starting off right.

David

database agnostic code is theoretically a great idea. However, you lose
most of the advantages of the chosen database engine. For example, if
you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per database
(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

On Sunday 20 June 2010, Sim Zacks wrote:

database agnostic code is theoretically a great idea. However, you lose
most of the advantages of the chosen database engine. For example, if
you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per

database

(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

This is what I am trying to encourage. I am asking about the best
way to encourage it.

David

On 6/20/2010 12:08 PM, David Goodenough wrote:

On Sunday 20 June 2010, Peter Eisentraut wrote:

On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:

These projects need help to realise that adding Postgresql is not a
big
job, especially for those using JDBC which can already connect to

all

DBs. It strikes me that if the project could write a few pages
gleaned
from other porting operations, then whenever a project like this is
found
they can be pointed to these pages and shown how easy it is to

do.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_Postgr

eS

QL

Excellent, I had not realised this existed. I will point any projects I
meet which have not found Postrgesql goodness at this page. Thank

you.

David Goodenough wrote on 20.06.2010 11:08:

I don't support anyone has written a "how to write database agnostic
code" guide? That way its not a matter of porting, more a matter of
starting off right.

I don't believe in "database agnostic code".

In the end it basically means that the application will run equally slow on all platforms.

I'm not necessarily talking about syntax features/differences (e.g. hierarchical queries or other advanced features) but about behavioral features that stem from the way the engine works, e.g. due to different locking strategies or different optimizers.

Some engines don't like single large transactions, some don't like a lot of small transactions.
Then think about syntactically identical statements that will behave differently because each engine has different optimization strategies. Some engines are better with complex joins and subqueries some are better with several small queries. An index that might be used in one engine to speed up a select might be totally ignored by another.

Thomas

On Sun, 20 Jun 2010 12:52:22 +0200
Thomas Kellerer <spam_eater@gmx.net> wrote:

David Goodenough wrote on 20.06.2010 11:08:

I don't support anyone has written a "how to write database
agnostic code" guide? That way its not a matter of porting,
more a matter of starting off right.

I don't believe in "database agnostic code".

It depends on what you're after.

There are applications that really use a DB as a SQL interface to
the file system (sort of).
Coding non standard SQL for such kind of application (and there are
many out there) is just a different practice of BDSM.

Still even when you deal with application that can really take
advantage of the various special feature of some DB and their SQL
dialect... 80% of the written SQL could be standard.

Sometimes people write abstraction code to make it easier to write
in the only SQL dialect they know for a very small coding advantage
while they could write a better one that at least wouldn't make a
pain to post the application.

When thinking about portable code I generally find this documents
useful:
http://sql-info.de/postgresql/postgres-gotchas.html

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Sun, Jun 20, 2010 at 10:08:34AM +0100, David Goodenough wrote:

On Sunday 20 June 2010, Peter Eisentraut wrote:

On l�r, 2010-06-19 at 22:56 +0100, David Goodenough wrote:

These projects need help to realise that adding Postgresql is
not a big job, especially for those using JDBC which can already
connect to all DBs. It strikes me that if the project could
write a few pages gleaned from other porting operations, then
whenever a project like this is found they can be pointed to
these pages and shown how easy it is to do.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS

QL

Excellent, I had not realised this existed. I will point any
projects I meet which have not found Postrgesql goodness at this
page. Thank you.

I don't support anyone has written a "how to write database agnostic
code" guide?

I have. :)

http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html

That way its not a matter of porting, more a matter of starting off
right.

You're assuming that the goal of "database agnostic code" is
reasonable. I'd take a hard look at the trade-offs first. "Database
agnostic code" sounds like a reasonable idea until you've had to
maintain such code for a few years.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sunday 20 June 2010 12.52:22 Thomas Kellerer wrote:

I don't believe in "database agnostic code".

Using a db abstraction may be the right way to write "database agnostic
code".

I have quite a good impression of SQLAlchemy, for example: it is quite
generic, and it is still possible to write direct SQL for those few queries
where I know that I can create better queries than SQLAlchemy would.

YMMV, of course. When performance is an issue, optimized code for a certain
DB backend will probably beat abstractions like this every time. But if
you're just writing a small-ish application, the option to support other db
with minimal effort is quite nice.

cheers
-- vbi

--
Today is Sweetmorn, the 25th day of Confusion in the YOLD 3176

Sim Zacks wrote:

database agnostic code is theoretically a great idea. However, you lose
most of the advantages of the chosen database engine. For example, if
you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per

database

(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

David Goodenough wrote:

This is what I am trying to encourage. I am asking about the best
way to encourage it.

You want to encourage the use of databases that don't support relational
integrity?

Really?

I think that is a simply terrible idea.

--
Lew

David Goodenough wrote:

I don't support anyone has written a "how to write database agnostic
code" guide? That way its not a matter of porting, more a matter of
starting off right.

There is no real way to write "database[-]agnostic" SQL, although of course
middleware code can and should be.

SQL dialects cannot even agree on simple things like the syntax for VARCHAR
(VARCHAR2 in Oracle) or the semantics of TIMESTAMP, or what is legal in a
SELECT. As Sim Zacks said, "you lose most of the advantages of the chosen
database engine" if you write to the lowest common denominator, particularly
as such an LCD is nonexistent.

--
Lew

On Monday 21 June 2010, Lew wrote:

Sim Zacks wrote:

database agnostic code is theoretically a great idea. However, you

lose

most of the advantages of the chosen database engine. For

example, if

you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per

database

(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

David Goodenough wrote:

This is what I am trying to encourage. I am asking about the best
way to encourage it.

You want to encourage the use of databases that don't support

relational

integrity?

no, I want to encourage "The quickest way is to write code that will
work on any db but won't take advantage of db-specific features."

David

On Mon, Jun 21, 2010 at 08:35:02AM -0400, Lew wrote:

David Goodenough wrote:

I don't support anyone has written a "how to write database
agnostic code" guide? That way its not a matter of porting, more a
matter of starting off right.

There is no real way to write "database[-]agnostic" SQL, although of
course middleware code can and should be.

"Database-agnostic middleware" is not a practical or desirable goal
for the same reason that "database-agnostic SQL" isn't. The original
reasoning behind the radical experiment of "database-agnostic" was an
attempt to defend against the depredations of vendors of proprietary
RDBMSs, who tended to use strong-arm tactics any time they felt they
could get away with it.

As a strategy, "database-agnostic" has failed because the only two
(combinable) ways to implement it are enormously expensive even to
create, and super-linearly expensive to maintain. I've covered these
below:

http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html

Fortunately, another strategy whose effect is to defend against the
above-mentioned strong-arm tactics--making a wide selection of
non-proprietary RDBMSs--has succeeded. Just pick an RDBMS and max out
its capabilities.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

then you want your code to call stub functions (with DBSpecific stack parameters)
Insert

Update

Delete(-with-cascade)
Select

I ran into a problem recently where i wanted to LOCK table MySQL which of course is a no-op in MySQL so I carried the driver string as a stack param e.g.

public int Lock(String driver_string_stack_param)
if(driver_string_stack_param.equalsIgnoreCase("com.mysql.jdbc.Driver") ; //noop

else{

// do Lock Logic
}

hth
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

From: david.goodenough@btconnect.com
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] A thought about other open source projects
Date: Mon, 21 Jun 2010 16:14:10 +0100

On Monday 21 June 2010, Lew wrote:

Sim Zacks wrote:

database agnostic code is theoretically a great idea. However, you

lose

most of the advantages of the chosen database engine. For

example, if

you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per

database

(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

David Goodenough wrote:

This is what I am trying to encourage. I am asking about the best
way to encourage it.

You want to encourage the use of databases that don't support

relational

integrity?

no, I want to encourage "The quickest way is to write code that will
work on any db but won't take advantage of db-specific features."

David

Really?

I think that is a simply terrible idea.

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

_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&amp;ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4

On Mon, Jun 21, 2010 at 04:14:10PM +0100, David Goodenough wrote:

On Monday 21 June 2010, Lew wrote:

Sim Zacks wrote:

database agnostic code is theoretically a great idea. However, you

lose

most of the advantages of the chosen database engine. For

example, if

you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per

database

(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

David Goodenough wrote:

This is what I am trying to encourage. I am asking about the best
way to encourage it.

You want to encourage the use of databases that don't support relational
integrity?

no, I want to encourage "The quickest way is to write code that will
work on any db but won't take advantage of db-specific features."

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Exactly. The big issue here is that nobody's saying what kind of app
they want to write.

If it's a simple web content management system, the possibility of
having > 1 db being supported is better than if you need a
transactional real time securities trading system, it's a lot less
likely to work on > 1 db.

Scott Marlowe wrote:

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Exactly. The big issue here is that nobody's saying what kind of app
they want to write.

Or what sort of performance requirements are tied to that app.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.

On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote:

Scott Marlowe wrote:

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Exactly. The big issue here is that nobody's saying what kind of
app they want to write.

Or what sort of performance requirements are tied to that app.

It's not performance requirements that tend to tank such projects, but
the amount of maintenance involved. Extending the app gets
quadratically painful.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, 21 Jun 2010 11:27:20 -0700
David Fetter <david@fetter.org> wrote:

On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote:

Scott Marlowe wrote:

As with phrases like, "the quickest way to grill a unicorn
steak," that it can be stated in a few words does not make in
possible.

Exactly. The big issue here is that nobody's saying what kind
of app they want to write.

Or what sort of performance requirements are tied to that app.

It's not performance requirements that tend to tank such projects,
but the amount of maintenance involved. Extending the app gets
quadratically painful.

I perfectly agree. But maybe the number of hackers involved in a
project could grow faster if the project serves more purposes.

Reuse lower maintenance costs too.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

bnichols@ca.afilias.info (Brad Nicholson) writes:

Scott Marlowe wrote:

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Exactly. The big issue here is that nobody's saying what kind of app
they want to write.

Or what sort of performance requirements are tied to that app.

And that's only one form of "quality of service," too.

- Sometimes you need fast...

- Mighty frequently, "fast enough" is good enough.

- Sometimes you need excruciating correctness (to the point of needing
2PC!).

- Sometimes you need to not need to respond to all errors. (e.g. - in
an embedded application, there may never be a way for an
"administrator" to get at instances of the system - consider an
appliance like a router)

- Very frequently, making sure the system "isn't too fragile" is an
important aspect of QoS. [1]<http://www.databasejournal.com/features/db2/article.php/3888026&gt;

- I've really liked the recent comments about the philosophy behind
NoSQL, where it is suggested to be "postmodern" in the sense that:

- SQL traditionally has been about storing facts of some solidity,
and, in contrast...

- The postmodernist approach to philosophy, and, to a great extent,
NoSQL systems involves an acceptance of:
- The absence of objective truth
- Queries return opinions, not facts

In effect, this involves a more-or-less philosophical shift that is,
all the same, an expression of "quality of service."

- The vigorously expressed unfriendliness of the MythTV folk is somewhat
curious, and I'm not quite sure how to classify it. I don't think it's
any of the above.

[1]: <http://www.databasejournal.com/features/db2/article.php/3888026&gt;

[2]: <http://www.xaprb.com/blog/2010/06/12/postmodern-databases/&gt; -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://cbbrowne.com/info/internet.html "MS apparently now has a team dedicated to tracking problems with Linux and publicizing them. I guess eventually they'll figure out this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS apparently now has a team dedicated to tracking problems with
Linux and publicizing them. I guess eventually they'll figure out
this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>