From: Colm MacCárthaigh (colmmacc at domain redbrick.dcu.ie)
Date: Sat 14 Sep 2002 - 12:19:29 IST
On Sat, Sep 14, 2002 at 03:47:52AM +0100, kevin lyda wrote:
> On Sat, Sep 14, 2002 at 02:29:40AM +0100, Colm MacCarthaigh wrote:
> > On Sat, Sep 14, 2002 at 01:40:42AM +0100, kevin lyda wrote:
> > It's not a totally bad comparison from some perspectives. They are
> > both essentially convientent interfaces to convetional filesystem
> > DB hash as opposed to an actual DBMS.
>
> again, mysql supports different table types. yes, it does do things
> in a unix way by taking advantage of the underlying fs (a db is a dir;
> a table is a collection of files).
>
> > That could be done in shell using tdb. Sure, mysql can store and index
> > records, that's no biggie :) That makes it a database, but not
> > a managment system :)
>
> actually, yes, i agree. for the portion that generates invoices/reports
> a db like mysql does make some things easier.
Oh there are many benifits to using a daemonised database system that
understands SQL, definitely. And if all your doing is storing and
queries mySQL is o.k., have to say I still prefer postgresql even
for these basics. Last I checked, MySQL didnt even suport ident
for auth, something which makes postgresql great for web applications,
because I don't need to store a password any more. And if am
using sensitive data, Postgresql has TLS and SSL these days ...
things like that :)
> > MySQL suits a hell of a lot of tasks, this is one of them. Postgresql
> > would do it better in my opinion, but there's no harm in using MySQL.
>
> ah yes, but postgres needed that nightly downtime back when this
> was first written (and downtime is unacceptable). and it still needs
> to be maintained. our mysql installation was out of the box, untuned
> and unmaintained for a long period of time initially.
PostgreSQL needs to be configured, it doesnt really need to be
maintained, certainly no more than mySQL. I use postgresql heavily
and it takes care of itself for me, so I'm not sure what you mean
by that. I don't generally moan about having to configure things,
it's kind of expected.
> > > i'm not quite certain what enterprise class is. does that sound like
> > > it yet?
> > Not really. A more realistic enterprise application is a live
> > registration system, or real time data gathering from *diverse*
> > sources, where triggered commits and data verification at the
> > fundamental level, which may be coming from a diverse range of
> > sources is very important.
> >
> > Real DBMS's give you the ability to trigger and use SPROCs to
> > take care of sanity checking your data. What if tomorrow the
>
> ah, see, here you lose me. i'm not a db programmer. i use db's to
> query and store data; i use libraries to auth/verify data i receive.
> we do get data from a variety of sources, but all the db connections are
> handled by a single module.
WHy replicate the data collection and aggregation features of a DBMS?
A DBMS is sitting , ready and waiting, highly tuned to accept and
handle data efficiently. Why would you want to put everythign through
another input system? If you'd have used Postgresql you'll probably find
you could have gotten away with a small object library and had
postgresql call the functions on triggers. Surely that module may become
a bottleneck, and won't it have to be maintained ?
Not that it isnt a valid architecture, but I have to say I prefer
SPROCs, or even a mixture of both.
> the idea behind this is that we can change
> the underlying db or the db config. for instance if at a later date we
> use mysql mirroring for redundancy/performance then all i need to do is
> change a dozen or so lines of code (most of which relate to parsing the
> application config file).
>
> and all the data types we use are simple - numbers or chars. postgress
> has oodles of data types, as does oracle and so on. however doing that
> locks me into a db.
I don't really follow the logic here. Are you sayign it's bad
to use Postgresql for the above just because it has more than
you need ? I've never found speed to be an issue, I find
postgresql is faster, it has everything mysql has only better,
so why not use it ? Just out of curiousity.
> > people who developed the above are asked to make their system
> > available via the web ? And then via call up phone service,
> > and then via real human operators ?
>
> for those types of interfaces, then we'd need to write a payment collection
> service. but once we did then we'd make calls to the database library.
So basically your DBMS is mySQL + this library. There has to be
replication there. I know I'd prefer to just be able to fire off
SQL to a DBMS and have it take care of things for me.
> > For example, something as basic as this, can you even
> > make MySQL generate a failure if you try to insert a non
> > RFC822 compliant e-mail address into a certain field ?
>
> again i've seen too many occasions when stored procedures and tricks
> like that cause problem in terms of database migration or upgrades.
> the simple solution is to write your application so that the database
> storage is handled by a library and is abstracted as small as possible.
I totally agree, which is why PostgreSQL has the ability to
load precompiled object code (ie ... a library) and use it
as sproc's, this is the approach I use. It means all of your
code is rational and re-usable further out if you want
(like I said, users should get nice error reports), but that
you still have your data checking done as close and as
well as possible. SQL functions are pretty portable these
days, and will do some queit advanced things. Being tied
to Postgresql would not concern me because my SPROC's are
either standard SQL, or C libraries which I can re-integrate.
> the main guts of the application contain no sql, just calls to the
> database module with the data required. at a later date if we change the
> underlying db (changing the table layout, the data types, a different db,
> or whatever) it will only require a change to a single module.
But where would you get the new data from ? Surely you'd have
to change the API of your module ? Unless it's already recieving
data it doesnt bother storing, which is a bit odd. If you're
making API changes, changing SQL is just as easy/hard accross
systems. If you're using the module to gather data from another
data source or whatever ... then you're replicating a feature
of a real DBMS ... triggers!
> > What about if you'd like to store the last, say 100
> > credit card numbers used in a round-robin database, which
> > are checked when cancelations are requested, and then
> > drop any CC #'s for security reasons ?
>
> there are better ways to do that. in that scenario i'd store an md5
> hash of the cc# and then when a cancelation came in i'd just hash the
> incoming cc# and check it against the db. that solution scales better
> and is more secure.
>
> > For a single input system, the arguments arnt queit as
> > effective, but when you start seeing multiple inputs
> > you start saving time and money :)
>
> but that's the reason not to use stored procedures and more exotic
> db features. the original poster is getting fleeced for their ingress
> license. they want to switch to another db to save money. since they
> don't use stored procedures or exotic data types (iirc) they can pretty
> easily do that.
True, but if they'd used a well-designed open-source DBMS like
PostgreSQL they'd have found that a) this wouldnt have happened,
b) their SQL would probably be slightly more portable c)
any sproc's they did use would be available to them in simple
libraries or SQL functions.
I don't want to seem zealous about mySQL being useless, it's
not useless, but it is crap. I guess I just prefer to have
my modularity wrapped up by something reliable I know wil
take care of it, and you prefer to have your modularity
spread out in a more serial fashion. There are advantages
to both, it's true that it's much easier to change the
DB backend in the above certainly, but to me it's not that
big a deal. Like with anything, for the sake of usability
and efficiency, I'm prepared to have to customise.
-- colmmacc at domain redbrick.dcu.ie PubKey: colmmacc+pgp at domain redbrick.dcu.ie Web: http://devnull.redbrick.dcu.ie/
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:18:52 GMT