Blog2Blog Maak je eigen Blog2Blog | Gratis je eigen blog c.q weblog op internet
peekorama.nl
peekorama.nl

Stored Procedures and Backups

As a teaser!

To give this blog a meaning i will discuss an issue we came across while making extensive use of stored procedures.

As some of you might know a normal backup from a database will use normal sql entries the moment you want to restore the database at a backup location. Seems sufficient for the normal things you want to do with your database.

But what happens if you filled your database with loads of stored functions, relying havely on stored procedures you wrote earlier.

There is no way mysql will let you restore a stored procedure if it "needs" another function. So basically, you need to be sure that all children are inserted before you insert the parent procedure. Too bad mysql uses the alfabet to sort their queries.

We came up with a relative easy solution, we needed to insert the queries in a functional order. So if stored procedure [a] uses stored procdures [b] and [c], we have to inserrt [b] and [c] before we can insert [a].

Our solution uses a php script which loads the sql queries from the backup.sql..then recursively inserts all queries by looking for related queries.

So e.g. if SP(Stored Procedure) [a] uses [b] and [c], we go and check if [b] and [c] have any related SP's. If SP[b] uses for instance SP[x], we check SP[x] for any more children. If not, we can insert this query and cut of the recursion, if so we keep on searching till we find a SP without any children.

This will provide us with a solid backup.sql which would be parsed by the sql restore function. Mind you that this method fails if you build a loop in your stored functions ( e.g. SP[a] has child [b] and [b] has child [a]).

I might provide some more details about this php script if i got time for it.

So for now, teaser out




23:10 - 28/1/2008 - comments {0} - post comment

First entry

Welcome at my new blog!

This will be the first entry just to fill the page. I hope to inform readers about my projects as a webdesigner and programmer currently working for http://www.wannahaves.com.

The plan is to fill the site with more exciting projects and i'm working on a flash cartoon which will be published as soon as i'm satisfied with the results.

In the meantime, you are free to contact me at rwpeek[at]students[dot]cs[dot]uu[dot]nl if you think you can provide me with some freelancing work, or if you are just interested in what i do.






22:44 - 28/1/2008 - comments {0} - post comment

Description
A short blog about my portfolio as a php, java, flash programmer

Home
User Profile
Archives

Active Projects
Wannahaves
Boretti
Dafusion

Links
Zeger Knops

Recent Entries
- Stored Procedures and Backups
- First entry