Stored Objects in Database Architectures

Stored Objects 

Stored objects in MySQL usually refer to either stored procedures or stored functions. The purpose of such stored functions is to store them inside of the server until they need to complete certain work and be “called to work”, so to say. Stored objects (also sometimes called “stored routines”) are usually created using the CREATE PROCEDURE and CREATE FUNCTION statements, then called using the CALL statement. To create a procedure or a function, we should run queries that look like this: 

As you can see from this example, creating procedures looks quite a bit different from running an ordinary query. That’s because of a couple of key reasons: 

• - Procedures must have a column that they take input from (defined after the IN clause) and the column that they put the data into (defined after the OUT clause.) 
•  
• - A procedure must have a beginning (see BEGIN) and the end (see END.) The query that is run when the procedure gets called (in bold) is in between the two statements. 

Once your procedure is set up, call it when needed by using the CALL statement like this: 

CALL demo_procedure(‘Demo’, @demo); 

The call takes two parameters because we’ve defined two parameters – a “demo” column with a VARCHAR value, and a “demo_out” column with an integer-based value. The text after “@” defines what variable to call after a SELECT statement to invoke the procedure, so the whole process looks something like this: 

1. 1. Create a procedure with the query you need to be run (see example above.) 

1. 2. CALL the procedure also defining the parameter you will use to use to see the results with a SELECT query (see example above.) 

1. 3. SELECT the procedure using SELECT @procedure_name. It’s as easy as that – by doing that you will enable MySQL to do the work you wanted it to to when creating the procedure. 

Stored objects in the architecture of other database management systems (think Oracle, MongoDB, TimescaleDB, and the like) might be called a little bit differently than they’re called in MySQL, but the core of them is the same.) 

What can Stored Objects Do? 

The purpose of stored objects is to ease the job of developers and database administrators by them specifying the work that they need the database to do for them. For example, if you find yourself constantly needing to insert the row count from a table A into a table B like in the example we’ve given you a while ago, they’re golden. 

You can also use stored functions, triggers, events or views – all of these are objects accomplishing different goals. For example, functions act much like functions that are built into MySQL by default, triggers is a trigger that is associated with a table in the sense that it runs when certain operations on the table in question are performed. MySQL also allows you to create events to invoke them according to a certain schedule as well. 

Here’s how to create a trigger: 

Triggers can be run before or after a certain INSERT, UPDATE, or DELETE statement runs (insert these statements into the place of *statement here*), and they can take effect on either each row (see example above), or each statement that is run (replace as you see fit.) 

A basic trigger would probably look something around these lines (note that we have to specify a delimiter to tell MySQL where our trigger actually ends after beginning): 

You get the point – triggers “trigger” specific queries that can be run on a specific time. They can be used to set specific values for specific columns if conditions are met (see example above), but should we need a different caliber of stored objects – say, those that run on certain schedule, they’re off limits – instead, we should be looking at events. To run event queries, first enable the event scheduler by running a query like SET GLOBAL event scheduler = ON; and then create an event for you to use. Event queries look something along these lines: 

CREATE EVENT [IF NOT EXISTS] demo_event ON SCHEDULE AT [time_here, CURRENT_TIMESTAMP is also an option] DO 

INSERT INTO events(event, ran_at) VALUES (‘an Event for Tuesday’, NOW()); 

It’s pretty self-explanatory: we name the event demo_event, then make it run on schedule at a specified time and specify its body after the DO keyword. 

We can also set up recurring events like so: 

This event would be scheduled to execute every 15 minutes and stop executing after an hour. So, we would have 4 such events in a given hour. 

Going Beyond Stored Objects 

Stored objects have their place in any database architecture – as you’ve learned from the examples we’ve provided, they can be scheduled to run at any specific time, be triggered before or after certain statements are run, etc. However, if you really want to improve your database performance, the usage of stored objects alone won’t be enough – you would still have to keep a very close eye on your database performance, availability, and security, and that’s not the job of stored objects, no matter on what kind of database architecture we are running them on. 

That’s the job of database management tools – not all tools are made equal, of course, but if you find yourself searching for one, keep in mind that a good database management tool should offer at least three out of these options defined below: 

• - It should help you make your databases more available and monitor their availability at any given moment. 
•  
• - It should help you keep an eye on your database clusters and their performance from within the database by providing you useful charts and advice on how to improve the database performance. 
•  
• - It should provide actionable advice on how to better secure your database clusters to keep them safe from data breaches both today and in the future. 
•  
• - It should advise you when your databases are running out of hard drive space and either upscale the disks automatically or at least tell you which disks are running out of space and how to act on the issue. 

Good database management solutions do much more than just that though – they usually support multiple flavors of database management software, reliable providers are usually being used every day by reputable vendors and known companies, and they’re usually accompanied with 24/7 support by phone or email (or both.) 

Isn’t it better to see your database architecture in the form of charts (see below), instead of observing metrics via the CLI? 

Image 1 - Charts Provided by dbWatch 

Though stored objects definitely have their place, with good database management tools, the hassle of creating and managing them will jump out of the window. If you find yourself searching for a database management tool, keep in mind that the majority of the good ones will allow you to test the software risk-free for free for 30 days. dbWatch Control Center is one of them, so if you’re wondering whether dbWatch Control Center is the choice you should go with when monitoring your database clusters, make sure to give it a shot today, and until next time! 

-------------------

Other blogs:

Common issues between Database Management Solutions and PHP Developers

Enterprise Backups won't save you

-------------------

About the Author:

Lukas Vileikis is an ethical hacker and a frequent conference speaker.

Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.

He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.