Integrating and taking advantage of model views

PHP
1

A couple of weeks ago I shot off a thread which featured a formatting sql function and there was a little debate on how formatting of that nature should go into the php view code. I consulted with my coworker and while he wasn’t opposed to placing it in the database he prefered for deployment reasons to have the code in a smarty function and moved it over.

But it got me to thinking about database roles in general and this morning I had one of those weird thought dreams where I saw a computer screen going over views in the database system. I woke out of it and took some notes.

Part of the problem with the larger more specific queries is they get placed in a block of code where PHP is doing other things and getting too them can be a pain. In thinking on this I thought of whether or not to place major selects of the system into views. Formatting makes sense here as well since a sql view is likely to correspond to only one view in the overall system, and even if not they are both re-usable but can be dodged. Change the format of the data in a view and you only affect the parts of the program looking at that view.

The framework I’m working on has some queries with fairly complex joins. By creating views I can get the logic of those joins out of PHP and into the database. It’s sort of like having an ERM, but without the overyhead of PHP code to build it dynamically.

Still playing with it. Thoughts?

2

I consulted with my coworker and while he wasn’t opposed to placing it in the database he prefered for deployment reasons to have the code in a smarty function and moved it over.

Smarty has persisted the illusion that templates are the place to perform formatting. I believe the best place for such behavior is the model.

Input is (translated) validated before use and formatted before return from the model.

The benefits include:

  1. Keeping with fat model, skinny controller
  2. Removes anything but presentation logic from view
  3. Centralizes the logic making changes enmasse more likely

Consider formatting in the template, what happens when you wish to show the same listings in a RSS feed? Now the RSS template/view has to format phone numbers the same way. What about REST API? Someone queries the API and they get the phone number back in a format that uses periods instead of hyphens?

If your system is truly enterprise and internationally-aware and you allow individuals to specify the format of phone numbers according to locale (providing a default) you want that configuration applied universally across the board - now you have to pass that config value around to the various views your application might support. If it’s done in the model layer, regardless of what view (Smarty XHTML, RSS, REST, CLI, etc) is required the format is always the same. The same principle applied to input and conversion of high level phone numbers or whatever into a universal form.

SQL might seem appealing, because it’s essentially a low level component of the model and first and last to send and receive input, but if you do this in the SQL layer, how do you deal with things like file listings? Their timestamps are returned and now you have to write some PHP code to output those dates according to locale and everything again anyways.

For those reasons I prefer to keep the model active in handling this responsibility.

Cheers,
Alex

3

But SQL is a discreet language is it not? And further, the SQL code is entirely model and should be considered a part of the model.

Just as javascript is also a discreet language, but it’s actions are part of the view code. View code isn’t just the templates - it’s the code that governs the users interaction with the data. The model isn’t just the data but the code that accesses it, is it not?

Creating table views segues well with this.

File listings, while a business logic concern, aren’t part of the database section of the model. The data model means all parts of the model be it file system, db or cache. So that example is a bit of a red herring since you can’t write a SQL query to work with file listings.

4

And exactly that’s the problem.
You bind your whole app to exactly this one datasource which is not replaceable.
Just think of replacing a MySQL-DB with a SQLite - no chance to get the data the same way so you have to reimplement all the code for formatting.
Database says it all, it’s not Formatbase.

5

CREATE VIEW syntax is pretty universal to RDMS systems these days. If you switch to a non-relational you’ll have to reformat the data anyway. I consider it a supremely stupid idea to impose artificial limitations to a project. One such artificial limitation is to try to force the database system to be compatible with every possible RDMS out there - especially in light of the fact that this is under the control of the programmer and vendor. I have not in all my time programming seen a switch of RDMS systems that didn’t also accompany major shifts in languages and technology that would have made any of the preparation your calling for entirely moot.

I have enough headaches in my life dealing with multiple browser agents, looking at all the neat features the newer ones have, then ignoring them entirely because my sites have to work on IE 7 - the lowest common denominator. I refuse to do the same on the server side where I do get to control the technology in use.

6

Seen any webservice having views? No?
So it’s still not the datasource that should do formatting.
And switching of datasources is very common.

But do it the way you like, I’m out…

7

But SQL is a discreet language is it not? And further, the SQL code is entirely model and should be considered a part of the model.

Not sure I understand what you mean by SQL being discreet, but I do agree it’s “part” of the model - although I tend to see it as the low level layer - DAL perhaps.

Just as javascript is also a discreet language, but it’s actions are part of the view code. View code isn’t just the templates - it’s the code that governs the users interaction with the data. The model isn’t just the data but the code that accesses it, is it not?

By discreet do you mean distinct, as well? I disagree that JavaScript is part of the view. JavaScript is client side, and you can easily implement business logic or controller code in javascript, such as one might in a heavily RIA environment such as ExtJS.

File listings, while a business logic concern, aren’t part of the database section of the model. The data model means all parts of the model be it file system, db or cache. So that example is a bit of a red herring since you can’t write a SQL query to work with file listings.

Agreed. File operations are just another example of a lower level DAL, just as a web service might be considered one as well. The model API would encapsulate/wrap those DAL functions and provide the required business logic, formatting, conversion, calcluations, etc. IMO anyways. :slight_smile:

I consider it a supremely stupid idea to impose artificial limitations to a project. One such artificial limitation is to try to force the database system to be compatible with every possible RDMS out there -

Why? In the enterprise or off the shelf software market, supporting multiple RDBMS is a very real demand. Taking care to write generic SQL whilst using a simplified table data gateway class makes the task far easier. I simply re-create the tables in MSSQL and change a config option and voila - the program just went from working with MySQL to MSSQL. Also this decision is usually made at install - it’s very rare someone switches the RDBMS after installation - in fact I have yet to deal with that - it’s always been an install time requirement.

I have not in all my time programming seen a switch of RDMS systems that didn’t also accompany major shifts in languages and technology that would have made any of the preparation your calling for entirely moot.

It does happen, I assure you. When it was announced MySQL had been acquired by Sun then Oracle, there was a lot of buzz and action on switching to MSSQL. Again though, it’s not so much about switching as it is having that option available during initial install. People will turn down your application based on what RDBMS it supports, trust me. If you are dealing with ASP developers who need your solution but are not comfortable with MySQL and trust more in MSSQL - they will request that. Thats a big market to turn a blind eye on.

I have enough headaches in my life dealing with multiple browser agents, looking at all the neat features the newer ones have, then ignoring them entirely because my sites have to work on IE 7 - the lowest common denominator. I refuse to do the same on the server side where I do get to control the technology in use.

Well thats your personal choice I guess. Flexibility and customizability are two of the most important selling points for any software system, IMO.

Cheers,
Alex

8

I’m writing code in PHP - how does ASP even enter into it?

9

It doesn’t, really. All I meant was that there are many times when a enterprise needs a knowledge management suite of software that happens to only be “well done” or available in say PHP. Traditionally the business may be a supporter of MSSQL and associated technologies. They would more comfortable running your software off a MSSQL install as opposed to MySQL so having that as an option at the time of install is not a bad thing.

Cheers,
Alex

10

Ah.

Well, for the record, I wasn’t proposing something so radical as to only be workable in MySQL. Creating views is supported by MSSQL, Postogre and Oracle. They aren’t supported by SQL-Lite, but I can accept that since I’m building a system that is fully ACID compliant (SQLLite isn’t, or at least wasn’t), with the ability to do transactions, rollbacks, views, and so on - many of these features MSSQL has been able to do for years and it’s MySQL doing the catch up.

I wasn’t looking for a RDMS debate. I was looking for insight into the use of model views in a database.

11

The problem with views is that it makes assumptions on the data. Specifically if you need to deal with i18n or l10n you have a problem because you can only supply one format for the view.
You could solve this by modifying the view if you switch to another locale, but that doesn’t allow for multiple users using different locales at the same time (unless you alter the view every time someone from a different locale views your site hack the crap out of it, maybe).

If you put this logic in the model you don’t have that problem, since the model can still check what locale the user is using and act accordingly.

12

So the prevailing opinion of this board then is the authors of MySQL, MSSQL, PostOgre and Oracle are morons for providing a CREATE VIEW system because it should never be used. Right?

That’s what I’m reading.

And Scallio - making assumptions about data is mandatory at some level. VARCHAR, BIGINT, CHAR(x), DATE, INT – those ALL make assumptions about data.

I made the mistake of bringing of formatting because everyone is putting on blinders to everything else views can do. They can recast data types in the database. I’ve been using them for this to provide some layer of sanity in a database originally built by a moron who inconsistency swaps from storing money as integers that have to be divided by 100, or as decimals - and rather than rack my brain trying to remember what when where with the stupidity I use a view (recoding the fields is a non-starter because mountains of existing code expect the harebrained formatting).

Views can be locked to permission sets. Consider a public view of a table that has social security numbers. You can have a view of that table ommitting the social security field that is used by the user account that accesses it. I’m not sure that it’s a good idea to pass this filtering from PHP to SQL but it’s possible.

But to hear the posts in this thread the implication is that database programmers are stupid for putting formatting functions into SQL and creating view functionality in the first place. I’d rather know why they are there and what they might be used for.

13

I wasn’t looking for a RDMS debate. I was looking for insight into the use of model views in a database.

OK well then, I’m a advocate of VIEWS :stuck_out_tongue:

14

How do you use them?

I’m a MySQL veteran working with systems that until about 6 months ago where running 4.1.x We’ve hopped over to 5.1 and I’m playing catch up and getting ready to do a massive system upgrade.

15

But to hear the posts in this thread the implication is that database programmers are stupid for putting formatting functions into SQL and creating view functionality in the first place. I’d rather know why they are there and what they might be used for.

Database programmers are not stupid just DBA’s. Formatting is arguably a model function and possibly can be done in SQL but for the reasons I listed before I prefer not to use SQL for such a thing.

Database centric developers tend to want to put as much as posisble in teh DB layer whether it be stored procs, views, triggers, etc.

It’s a classic example of “just because you can doesn’t mean you should”. The argument that disparate systems (Java, C++, Mobile, Web) can all use a common interface and avoid duplication of business logic by storing it in the RDBMS is antiquated IMO. RDBMS have been around for a long time, far longer than RESTful API’s but they seem to be the common place now, to implement business logic, in a middle tier technology such as PHP.

Thin clients still but the heavy lifting is done by scripting languages instead of RDBMS, not for speed, but for easier maintenance and other benefits.

Anyways I’m back on topic…I use VIEWS strictly for the purpose of emulating tables or rather JOINing multiple tables.

Cheers,
Alex

16

Ok. What I’m starting to use them for is transition. The current project I’m working on has a very poorly written database. One key problem is the design was done piecemeal and without planning. One painful example is that currency is handled either by integer or by decimal data depending on table…

Eventually the database needs to be redesigned and the data moved around to a proper normalized structure - but in the interim I’ve started using views to at least stay sane.

17

Ok. What I’m starting to use them for is transition. The current project I’m working on has a very poorly written database. One key problem is the design was done piecemeal and without planning. One painful example is that currency is handled either by integer or by decimal data depending on table…

I also have used VIEWS as a way to provide backwards compatability with legacy systems. For example we have an Application suite built on Access and I’m gradually rebuilding the entire thing as a series of extJS/PHP based applications.

The old tables have a naming convention and schema totally different from how I would design them. Withouth much consideration fo backwards compatability until near the end of one project, I realized the situation I got myself into. Instead of rewriting the thousands of lines of Access code to use my tables (READ-ONLY in this one instance) I simply provided VIEWS to emulate the original tables but they all just essentially acted like adapters to the newly designed/optimized table schema. No code changes to Access needed and are easily dropped when the next phase is complete.

I will have to use triggers for the next phase of the port, because these tables are read and written to, but hopefully it should work.

Cheers,
Alex

18

Wrong.
A VIEW in general has nothing to do with formatting.
The prevailing opinion seems to be to not do real formatting (like “format numbers with 3 decimals and red color”) in SQL as this is not portable and has to be re-done at every datasource and thus is part of the model.

19

in SQL as this is not portable and has to be re-done at every datasource and thus is part of the model.

I would tend to agree and this is a great way of describing the problem with relying on data source. However this only stands if you believe/subscribe to theory that formatting is the models responsibility. Smarty advocates formatting be done in the template layer - personally I choose the model but it’s debatable i suppose.

Cheers,
Alex

20

I prefer view helpers (template engine, Zend_View, etc.) … but whether or not formatting belongs in the view really depends on what type of formatting. There are instances where you need to format data in certain ways as required by the view, but there are also instances where the data is useless unless it’s formatted… What if different views require different formats of data?

Either way, I think database views are a a viable option if database portability is not a concern. It gives you a fairly centralized area to do it, though I’d still prefer to do it in the application code rather than database code.

Cheers