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 - post comment
A short blog about my portfolio as a php, java, flash programmer
- Stored Procedures and Backups
- First entry