Ibuildings Blogs
Jobs at Ibuildings
Are you passionate about PHP and do you thrive in a culture that values teamwork? Then check out our vacancies.
Tag cloud
ajax architecture atk best practices blogs book browser caching cms communication communities community conference development documentation dpc dpc2008 drempelsvrij e-commerce enterprise framework google ibuildings javascript klant marketing migration mysql oop open source performance php php4 php5 phpfreakz project project management provincie release release management rss scalability seminar social networks symfony teamwork test uk unittest usability web 2.0 webdesign webshop webstandaarden xml zend zend platform zend studio zendcon zoeken
Subscribe to this blog
Home 
Ibuildings Blogs
Ibuildings BlogsHarrie's blog
 |
ing. Harrie Verveer Software Engineer |
 |
Tuesday, June 19. 2007Yesterday, Carsten Pedersen and Roland Bouman of MySQL AB visited us to tell us a bit about the MySQL certification program. Although I've been using MySQL for several years they told me of some features I simply never used before. Of course I had heard of 'stored procedures' before, but I just did not use them at all. One of the reasons is that all of our products are database independent and stored procedures are not. In other words: writing a MySQL stored procedure for ATK would mean all people using ATK on an Oracle or Postgres database would suddenly get fatal errors. In the training they explained what stored procedures and functions are and I got curious. "Okay, so I can use a programming language sort of thing in MySQL. So how powerfull is this?". Driving back I suddenly realised I would do a really stupid and most probably useless experiment: to try to build a website entirely in MySQL, leaving the PHP part out? With that in mind I couldn't really do anything else than boot my computer once I got home, and keep hacking it until my application was finished. Of course there is no such thing as a website without a decent template parser. So let's build a nice little function to do this. Not really Smarty or anything like that, just a function that does some string-replace calls and that's it. Considering the philosophy of extreme programming that's rather good - we can always add more functionality to the template parser if we want to, but right now just let us keep it simple. We have three tables. Obviously one table to keep the pages. For each page we define what template to use (so you can use different templates for different pages!). Finally we need a table with template variables so we can assign these variables to the template. The database structure has to look something like this: CODE: mysql> desc pages; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | pagename | varchar(255) | NO | PRI | | | | templateid | int(11) | NO | PRI | | | | sort | int(11) | NO | | | | +------------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> desc templates; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | html | text | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> desc tplvars; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | pagename | varchar(255) | NO | PRI | | | | varname | varchar(255) | NO | PRI | | | | value | text | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Ok, so we have our datastructure for parsing templates. Let's put some test data in it and create a stored procedure to parse templates! Basically we got 2 functions, one to parse templates and one to parse pages. I had to hack around a bit to get the thing working, but with a little imagination you could say it is parsing templates  CODE: DELIMITER | # # Parses the given templatestring with the vars in the tplvars table # CREATE FUNCTION PARSETEMPLATE(page VARCHAR(255), tplstring TEXT) RETURNS TEXT BEGIN DECLARE done INT DEFAULT 0; DECLARE vname VARCHAR(255); DECLARE vvalue, returntext TEXT; DECLARE cur1 CURSOR FOR SELECT tplvars.varname,tplvars.value FROM tplvars WHERE tplvars.pagename = page; # Borrowed the line below (and the loop following) from dev.mysql.com DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET returntext = tplstring; OPEN cur1; # Loop through each variable and replace it in the raw template (with %%..%% around # it, because when you parse a template you just got to have percentage-signs =D) REPEAT FETCH cur1 INTO vname,vvalue; SET returntext = REPLACE(returntext,CONCAT('%%',CONCAT(vname,'%%')),vvalue); UNTIL done END REPEAT; RETURN returntext; END| # # Parses the given page and returns the wanted URL # CREATE FUNCTION PARSEPAGE (page VARCHAR(255)) RETURNS TEXT BEGIN DECLARE tplid INT; DECLARE mytemplate TEXT; DECLARE mytemplate2 TEXT; # Get the template HTML (raw unparsed template) SELECT templateid INTO tplid FROM pages WHERE pagename = page; SELECT html INTO mytemplate FROM templates WHERE id = tplid; # Stuff the template into the parsetemplate-function to get it parsed SET mytemplate = PARSETEMPLATE(page,mytemplate); RETURN mytemplate; END| How to use this? SELECT PARSEPAGE('pagename') takes the template, replaces the variables stored in the variable table and returns the parsed HTML. Hooray! This is about what I was looking for. So what are we missing to create a really amazing enterprise website? That's right! A menu structure. At this moment our application will only have a one-level menustructure, which is fine for the application I'm developing. Of course the challenge is to have a different lay-out for the currently selected menuitem. Enough for the brainstorm session, let's see if we can generate a proper menu! CODE: # # Gets menu based on the available pages # CREATE FUNCTION GETMENU (currentpage VARCHAR(255)) RETURNS TEXT BEGIN DECLARE done INT DEFAULT 0; DECLARE pagecode,pagetitle VARCHAR(255); DECLARE returntext TEXT; DECLARE cur1 CURSOR FOR SELECT DISTINCT tplvars.pagename,tplvars.value FROM tplvars LEFT JOIN pages ON (pages.pagename = tplvars.pagename) WHERE tplvars.varname = 'title' ORDER BY pages.sort; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET returntext = '<ul>'; OPEN cur1; REPEAT FETCH cur1 INTO pagecode,pagetitle; IF NOT done THEN SET returntext = CONCAT(returntext,'<li>'); SET returntext = CONCAT(returntext,'<a href="index.php?page='); SET returntext = CONCAT(returntext,pagecode); SET returntext = CONCAT(returntext,'">'); IF pagecode = currentpage THEN SET returntext = CONCAT(returntext,'<b>'); END IF; SET returntext = CONCAT(returntext,pagetitle); IF pagecode = currentpage THEN SET returntext = CONCAT(returntext,'</b>'); END IF; SET returntext = CONCAT(returntext,'</a></li>'); END IF; UNTIL done END REPEAT; SET returntext = CONCAT(returntext,'</ul>'); RETURN returntext; END| Now we get a nice little menu as well! Last thing we have to do is add the menu to our template while we are parsing it: CODE: # Add the menu to our page SET returntext = REPLACE(returntext,'%%menu%%',GETMENU(page)); And we're done! I've managed to break down the PHP app. to six lines of code, which is still more than I intended to. It's too bad there doesn't seem to be a way to let apache redirect requests to mysql directly and use the commandline parameters in MySQL. However, I'm happy Created a website in six lines of code! Ok PHP that is, and about 80 lines of SQL of course. The PHP lines are still there to connect to the database, retrieve the current page from the $_REQUEST vars and do the PARSEPAGE('pagename') call. Maybe we could make this entirely php-less using a shellscript like: CODE: #!/bin/sh mysql < PARSEPAGE($1) and than connecting that to port 80 using mysqlclient or something. Haven't tried that yet, but theoretically we got ourselves a website with MySQL only. Now how cool is that? You can see the working result at: http://harrie.demo.ibuildings.nl/mysqlwebsite/ Or download the sourcecode: http://harrie.demo.ibuildings.nl/mysqlwebsite/source.tar.gz Sources used: http://dev.mysql.com/ http://www.databasejournal.com/features/mysql/article.php/3569846 Comments
Display comments as
(Linear | Threaded)
Nice, but imho it's not a target to get rid of PHP. The target must be to create a safe application the user is looking for. And depending on the programmers skills, you could use PHP, .NET of stored procedures.
And don't forget future maintenance, many PHP-programmers have never seen nor heard about stored procedures. When you have a pool of DBA's and a system that won't have many changes, stored procedures could be a good choise. Otherwise I would stay with plain SQL. By the way, I use a lot off stored procedures in PostgreSQL, works great!
Well off course it was never mend to be a example of how an application should work - it's just a stupid experiment: me finding out if it's possible. Obviously you don't want this to be a real application for the reasons you mentioned and more (no flexibility, no object oriented programming, not modular etc.). I think I didn't make that clear enough in the article so I thought let's add an extra comment to explain
Cool!
One minor nitpick: your menu should just as well try to use a template of some sort. For a 0.0.1 version like this, we can see where this could be heading. Apart from that: this example shows several usefull techniques (and makes me want to use stored procedures just because it is possible). |
MySQL ofrece muchas funciones que los desarrolladores pasan por alto en muchas ocasiones. Estas funciones son lo suficientemente ricas como para crear toda una aplicación web únicamente utilizando el motor de la base de datos.
Tracked: Jun 26, 20:46