Jim wrote:

> The big ones that Postgres offers are rollback transactions
> and stored procedures.

Thanks, Jim.

Just for the record, MySQL does support rollback transactions already.
You're right about stored procedures though ... they won't be in MySQL
until version 5.0.

Jeffrey

"Thomas Ronayne" <trona@REMOVETHISameritech.net> wrote in message
news:6aEwd.116$Y8.12@newssvr17.news.prodigy.com...
> Murphy wrote:
>
> >I am currently evaluating our LAN requirments as we move towards a new
> >enterprise.
> >We require db & front end and a dynamic web site.
> >
> >Until recently I would have developed using:
> >MS SQL Server
> >IIS
> >ASP.NET
> >MS Access front end
> >MS Exchange
> >
> >I find thr thought of a Linux solution appealing because of the $ savings
> >and the challenge although I am confident of my abilities in the MS
World.
> >
> >What thoughts does the group have on running Linux based solutions for
the
> >above ?
> >Apache ==> Web Server
> >MySQL ==> db Server
> >PHP ==> Dyanmic website
> >
> >However I am unsure of the db front end and the mail server solution ?
> >
> >Would I still us MS Access, can it connect to MySQL ? is it complex to
mix
> >MS & linux solutions as I know how well Access & SQL Server work
together.
> >
> >Thanks
> >
> >Murphy
> >
> A LAMP solution -- Linux, Apache, MySQL, PHP -- would be a good idea...
> depending.

Was just about to ask what a LAMP solution was until I read it again...
>
> You're going to have to do some work -- moving from Access to an SQL

Our current db is SQL Server and we simply use Access as a front end
product, not as a db itself.

> data base management system may not be trivial; not in terms of
> performance (almost any relational data base management system [RDBMS]
> will vastly out perform Access), but more in terms of design. When you
> migrate to or do a redesign of an RDBMS, you really need to pay
> attention to the basics. If you've done a good job of developing your
> schema and you've got an efficient data base design now, migrating the
> schema and your tables content to, say, MySQL will not be difficult. On
> the other hand, if you've got a mess, well, you've got a mess that
> you'll have to clean up before you can really do much of anything. My
> experience has been that most (not all by any means, but most) Access
> implementations leave a great deal to be desired in terms of efficient
> RDBMS design and require redesign work (frequently a lot of redesign
> work) before a successful migration can be carried out. One basic
> requirement is that you speak SQL fluently or you'll get nowhere quickly
> -- if you don't have a good grasp of efficient table design and query
> performance, you'll have a great deal of trouble no matter what RDBMS
> "engine" you decide to go with.

Table design is excellent, normalised well etc.

>
> There is nothing "wrong" with MySQL. It's fast, it's efficient, and it
> does everything you would expect from an RDBMS. MySQL works well with
> PHP (either 3.x, 4.x, or 5.x) and PHP works well with HTML and Apache
> (and all of them work well with UNIX/Linux). What none of them are is
> "click and drool," which seems to be a tremendous down side in the MS
> world. The fact is, you need to give serious thought to your data base
> design without caring what the engines are in front of that design:
> stick to standard HTML (which means you build web pages that do not rely
> on, heaven help you and your customers, Active-X), stick to ANSI and ISO
> standard SQL, and figure out what to do with PHP in between the web page
> and the data base (PHP is just a query - response interface between HTML
> and SQL, not an end-all, be-all like Access tries [and fails] to be).

Is it advised to avoid stored procedures in SQL Server then so as to ease
the transition to another DBMS later, this would mean the SQL would need to
be coded into the PHP/ASP... I have read that MS is pushing towards a 2 tier
model as opposed to 3 tier.
>
> When you say "mail," what do you mean? For your internal users -- look
> hard at Mozilla or Firefox and Thunderbird. For mass mailings -- Linux
> comes with it, sendmail works just fine (and interfaces with PHP pretty
> easily).
>
> When you talk about "dynamic website" (if I understand what you mean by
> that) go look at Amazon.com and Ebay.com -- they're both standard HTML
> in front of PHP in front of an RDBMS (no, not MySQL in Amazon's case,
> but you can't afford a stripped-down custom Oracle like they can). Look
> at the source for their web pages (Ctrl-U in Mozilla or Firefox) and see
> some of what they do. Get familiar with style sheets while you're at it.
>
> One thing you do want to do, if you're going to bite the bullet, is
> avoid like the plague any "vendor extensions;" i.e., if it's not ANSI or
> ISO standard, don't do it (because it will come back to haunt you and
> your customers). If you haven't learned that lesson in the MS world,
> learn it well before you migrate to the UNIX or Linux world. One thing
> you should avoid in an RDBMS is stored procedures and triggers -- these
> look like they're convenient (a trigger event causes a stored procedure
> to execute), but they can be the death of you in a dynamic data base
> with a lot of hits coming from a web server (read up on them and test,
> test, test before you ever think about implementing these things); the
> sizzle is a lot better than the steak.
>
> You can get to MySQL (and most other RDBMS') with ODBC drivers -- if you
> like pain and agony and enjoy reinventing the wheel periodically, that
> is. Basically, go one way or go the other but don't try to mix the two
> -- in the real world, trying to front an RDBMS with MS stuff is not a
> good idea. Build your HTML and PHP applications to the way you want them
> and forget about fooling around with MS, you'll be happier.

What about for the internal interface to the db data ? I understand using
PHP etc to create the Website but would you suggest creating even the admin
interface as a web app too ?

>
> Bear in mind that the UNIX and Linux philosophy is that a program or
> utility does one thing and does it well -- shoot for that and you'll
> have a system that will be much, much easier to maintain and improve.
> Your goal in life is no telephone calls at three in the morning; a LAMP
> implementation might cost you a little in the beginning but it will pay
> off in uninterrupted sleep.

Thanks for your input here, greatly appreciated.

Murphy

Murphy wrote:

>"Thomas Ronayne" <trona@REMOVETHISameritech.net> wrote in message
>news:6aEwd.116$Y8.12@newssvr17.news.prodigy.com.. .
>
>
>Was just about to ask what a LAMP solution was until I read it again...
>
>
Yeah, don't you just love insider acronyms?

> <>
> Our current db is SQL Server and we simply use Access as a front end
> product, not as a db itself.

OK, you're half way there, then.

> <>
> Table design is excellent, normalised well etc.

The next step is to go off and read
http://dev.mysql.com/tech-resources/...microsoft.html
(it's an excellent discussion of the differences and recommends tools
and techniques). You'll have more or less fun doing this depending on
how many "features" of SQL Server you used, but it's not all that bad
and you'll be better off in the long run.

Grab a box, install Slackware 10.0 on it and go for the gold -- a full
install includes Apache, MySQL and PHP. There is a learning curve, not
terribly steep, but a curve nonetheless. When you set the box up, give
some thought to how big your data base is going to be and either make
/var big enough for the data base and all the other stuff that's in /var
or make /var/lib/mysql a mounted partition and be sure to give it plenty
of space (that's where your data base goes).

You need to do some (minor) configuration of Apache and PHP (your server
address, administrator e-mail, stuff like that), you need to do a
one-time initialization of MySQL, you need to set up users and
permissions in MySQL, you need to get comfortable. By default, Apache
and PHP are configured to interface with each other and with MySQL so
you don't have to rebuild anything to get at your data base with a
browser. When you build the box, go off to
http://www.slackware.com/pb/browse.p...tches/packages, get all
of them and do "upgradepkg *.tgz" in the directory where you downloaded
the patches.

Basically you need a schema for the data base and delimited unload files
for the tables and that's pretty much it -- except, of course, for the
gotcha's. The less "features" you used in SQL Server, the less gotcha's
you'll have to deal with.

>Is it advised to avoid stored procedures in SQL Server then so as to ease
>the transition to another DBMS later, this would mean the SQL would need to
>be coded into the PHP/ASP... I have read that MS is pushing towards a 2 tier
>model as opposed to 3 tier.
>
>
It's advised to never, ever use triggers and stored procedures (and
functions), period. One possible exception is a cascading delete (a
delete in one table cascades through others where the data are joined),
but even that should be avoided if possible on an active data base.
Stored procedures are, after all, a query and when executed will read
every row in a table over and over again. In a small table, say a couple
of million rows, that's not too big a deal but start thinking about what
happens when you've got 10 - 100 million or more rows and you've got a
real problem on your hands. Stored procedures, functions and triggers
look good on paper but in practice will bite you in the hiney if you
don't watch out (and they're almost impossible to debug if you have a
problem). Been there, did that, got the T-shirt and everything. Keep it
simple.

> <>What about for the internal interface to the db data ? I understand
> using
> PHP etc to create the Website but would you suggest creating even the
> admin
> interface as a web app too ?

Yep. Take a look at http://dev.mysql.com/downloads, specifically at
MySQL Administrator and MySQL Query Browser. You can be old fashioned
and use MySQL interactively with an Xterm (which works just fine), or
you can go for the GUI (aka "click and drool"). Bear in mind that PHP is
the interface between HTML and your data base -- that's its job. You can
execute PHP programs stand-alone (with the command line; e.g., php
prog.php) as well as from a web page. It's an extremely useful tool.

> <>Thanks for your input here, greatly appreciated.

No problem, hope some of this helps.

> <>
> Murphy
>
>