CODEWITHSUNDEEP
mysql
MattBelanger
MattBelanger

Reputation: 5350

MySQL - Views Alternative

I've developed an application at working using MySQL 5, that uses Views to access the major pieces of data. It turns out that our production server uses MySQL 4, which does not have Views included.

Does anybody have a quick and dirty way to deal with this that doesn't involve rewriting all my code?

Upvotes: 2

Views: 3740

Answers (4)

derobert
derobert

Reputation: 51137

The quick and very dirty way that comes to mind is to subclass DBI and re-write the SQL there. Depends on what you're using views for, of course, and if you mean MySQL 4.0 (does not have subqueries) or MySQL 4.1 (does have subqueries).

If you're on 4.1, you can turn:

CREATE VIEW foo AS
  SELECT a, b, c FROM real_table WHERE fooable = 1;

SELECT * FROM foo;

into

SELECT v1.* FROM (
  SELECT a, b, c FROM real_table WHERE fooable = 1
) v1;

At least, the latter syntax works in 5.0.x, I think it should in 4.1.x as well.

If you're on 4.0... well, it won't be as easy.

Upvotes: 2

Chris Kloberdanz
Chris Kloberdanz

Reputation: 4536

Ouch. Aside from a DeLorean and a flux capacitor or upgrading the server I don't know of any easy way to get around this issue. A lot of change seems necessary.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562270

This certainly points out the importance of using the same technology in your development and production environments!

Workarounds involving triggers or stored procedures won't work, because these are also not supported on MySQL 4.x.

Your options at this point:

  • Rewrite application code to duplicate data in denormalized tables, designed to match your views.

  • Upgrade your production database to MySQL 5.0. If you're talking about a hosting provider, then contact that provider and ask if they have an option for MySQL 5.0, otherwise you need to relocate to a provider who does.

I'd recommend the latter path, it'll be far less work than writing code to manage duplicate data.

Note that MySQL 4.1 was released as production software over four years ago. Active support for this release ended in 2006. Extended support for MySQL 4.1 ends 2009-12-31. See http://www.mysql.com/about/legal/lifecycle/

Upvotes: 3

Anne Porosoff
Anne Porosoff

Reputation: 1941

Unfortunately, without upgrading to MySQL 5, probably not.

Upvotes: 0

Related Questions