Article: Use the power of MySQL 5.1 features with MySQL Maestro!

Jan 29, 2007

Abstract

Table of contents

• Introduction
• Database structure
• Views
• Stored procedures and functions
• Partitions
• Events
• Conclusion and related links

Some time ago (November, 2005) we published an article about MySQL 5.0 features supported by MySQL Maestro 1.95. But since then the software have got many interface improvements and a series of server extensions have been implemented in the new MySQL versions.

MySQL Maestro is a powerful Windows GUI solution for MySQL server administration and database development. MySQL Maestro supports all the latest versions of MySQL, including MySQL 4.1.x/5.0.x/5.1.x.

MySQL has earned its reputation as an extremely easy to install, use and configure database. For all this independent benchmarks prove MySQL is both the pure performance and price/performance leader. Till recently MySQL has a quite poor means for ensuring data integrity and over these it couldn't be employed in large projects. Starting version 5.0, MySQL gives developers and DBAs tools to deliver and manage enterprise applications using ACID Transactions, Stored Procedures and Functions, Triggers, Views, etc. MySQL 5.1.x put at DBAs' disposal such new database objects as Events (MySQL 5.1.6+) and Table Partitions (MySQL 5.1.3+).

MySQL Maestro allows experienced users to feel all the power of potentialities inherent in new MySQL version. At the same time MySQL Maestro provides user with a comfortable graphical interface that makes work with MySQL Server easy and clear even for beginner.

Examples, given in this article are tested in the following environment: MySQL 5.1.14-beta installed under Windows XP, MySQL Maestro 6.12.

Let's consider a MySQL database with tables 'authors', 'publishers', and 'books' which have been created with the following SQL statements.

  CREATE TABLE `authors`(
    `author_id` int NOT NULL auto_increment,
    `author_name` varchar(100) NOT NULL,
    `author_phone` varchar(15),
    `author_address` varchar(100),
    `author_city` varchar(100),
    `author_country` varchar(100),
     PRIMARY KEY (`author_id`)
  ) ENGINE = InnoDB;

  CREATE TABLE `publishers`(
    `pub_id` int(11) NOT NULL auto_increment,
    `pub_name` varchar(100) NOT NULL,
     PRIMARY KEY (`pub_id`)
  ) ENGINE = InnoDB;

 CREATE TABLE `books`(
    `book_id` int(11) NOT NULL auto_increment,
    `book_price` float(9,2), 
    `book_name` varchar(100) NOT NULL,
    `author_id` int(11), 
    `pub_id` int(11),
    `book_note` varchar(255),
    `book_sell_amount` int(11),
    `book_bestseller` tinyint(4),
    PRIMARY KEY  (`book_id`),
    CONSTRAINT `author_id` 
         FOREIGN KEY (`author_id`)
         REFERENCES `authors`(`author_id`)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT, 
    CONSTRAINT `pub_id` 
         FOREIGN KEY (`pub_id`)
         REFERENCES `publishers` (`pub_id`)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT 
  ) ENGINE = InnoDB;

Let's also populate our tables with some data:

 INSERT INTO `authors`
   (`author_name`, `author_phone`, `author_address`, `author_city`, `author_country`) 
 VALUES 
    ('Paul DuBois', '4642968438', '201 West 103rd Street', 'Indianapolis,IN', 'USA'),
    ('Ewald Geshwinde', '12345677', '11/2 Breadalbane Street', 'Springfield', 'USA'),
    ('Jeremy D. Zawodny ', '435647322', '1901 Chief Osceola Rd.', 'Orlando', 'USA'),
    ('Michele E. Davis', '5470987', '801 Burlington', 'Downers Grove', 'USA'), 
    ('Larry Ullman', '43901234', 'Downtown 45', 'Oklahoma', 'USA'),
    ('Guy Harrison', '44467693873', '45 Str., 55A', 'London', 'GB'); 

 INSERT INTO `publishers`
    (`pub_name`)   
 VALUES
    ('New Riders'),
    ('SAMS Publishing'),
    ('Peachpit Press'),
    ('O`Reilly Media');   

 INSERT INTO `books`
  (`book_price`, `book_name`, `author_id`, `pub_id`, `book_sell_amount`, `book_bestseller`)     
 VALUES
  (32.90, 'MySQL', 1, 1, 500, 0),
  (29.50, 'PHP and PostgreSQL', 2, 2, 600, 0),
  (27.57, 'High Performance MySQL ', 3, 4, 200, 0),
  (20.64, 'PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide ', 5, 3, 500, 1),
  (34.49, 'MySQL Cookbook ', 1, 4, 300, 1), 
  (20.69, 'Learning PHP and MySQL ', 4, 4, 700, 0), 
  (31.04, 'MySQL Stored Procedure Programming', 6, 4, 400, 1);

That's all. Now we're ready to investigate the new features.

View is an alternative way to retrieve data from one or more tables. View is something like a filter that passes data from tables before user can see them. When user creates a view, data from tables are not duplicated. All view's data are stored in tables from which the view is created. Work with views is the same as work with ordinary tables.

Views provide developers with easy in use way to protect data. Besides views can be effective when database structure is changed (e.g. some table is divided into several) and user prefers working in accustomed to him/her style.

Views advantages:

• Focusing: views allow user to concentrate on interesting data or on task he needs to solve. Data which don't concern to this user can be kept back. For example, a user needs to have deal with a list of bestsellers. It would be convenient to create the following view:

   

  CREATE VIEW `bestsellers`(
     `author_name`,
     `book_name`,
     `book_price`,
     `pub_name`)
  AS
  SELECT       
     `authors`.`author_name` AS `author_name`,
     `books`.`book_name` AS `book_name`,  
     `books`.`book_price` AS `book_price`,
     `publishers`.`pub_name` AS `pub_name`
  FROM
      ((`books` 
      left join `authors` on `books`.`author_id` = `authors`.`author_id`) 
      left join `publishers` on `books`.`pub_id` = `publishers`.`pub_id`)
  WHERE     
      (`books`.`book_bestseller` = 1);     
  

  
  
• Easy data manipulating: views facilitate not only data perception but work with them. Frequently used joins, projections and selects are specified for views. This allow user not to indicate all conditions every time when next operation is executed. For example, the following queries return the same result:
  
  

  Without view:	With view:
  SELECT `books`.`book_id` AS `book_id`, `books`.`book_price` AS `book_price`, `books`.`book_name` AS `book_name`, `authors`.`author_name` AS `author_name` FROM ((`books` left join `authors` on `books`.`author_id` = `authors`.`author_id`) left join `publishers` on `books`.`pub_id` = `publishers`.`pub_id`) WHERE (`books`.`book_bestseller` = 1) ORDER BY author_name;	SELECT* FROM bestsellers ORDER BY author_name;

   

  
  

  
  Picture 1. SQL Editor.

   

  
  

• Security: users can request and modify only information that they can see. The rest part of database is remaining concealed and inaccessible. With the help of GRANT and REVOKE it is possible to permit every user access to certain database. Objects and views are among them. It is easy and at the same time effective mechanism to provide security protection. Defining different views and setting different rights to them it is possible to grant object permissions just for certain subsets of data.

Use of views becomes much easier with help of MySQL Maestro's View Editor. This editor allows editing the existing view definition and other properties.

Picture 2. View Editor.

Creation of a new view can be done with the MySQL Maestro's Create View Wizard in a very obvious way.

Picture 3. Create View Wizard.

Picture 4. Filter Builder dialog.

In order to create a view, user needs to get permission to invoke the CREATE VIEW command from database owner and have corresponding access to all tables and views that are used in defining that view. Applying MySQL Maestro's User Editor, DBAs can easily set privileges.

Picture 5. User Editor.

Stored procedures are the set of SQL queries that are kept in the server. Once they are written they can be implemented repeatedly.

Stored procedures and functions advantages:

• Performance improvement: stored procedures and functions considerably increase capacity, efficacy and flexibility of SQL. The reason of this is that stored procedures are compiled beforehand. During first procedure execution server's SQL query processor is analyzing procedure and prepare execution plan, which is kept in system table. Later on, procedure is performed according to this plan. Since the main part of query processing has been done then stored procedures are implemented in a moment.

• Reuse: some algorithm can be realized in stored procedure and then implemented many times. Besides it can be used in common by many clients/applications (even in various languages).

• Security: stored procedures allow providing more data security degree. Some user can be permitted to invoke procedure. At the same time he/she can't directly modify data that user don't have access through this procedure. For example a seller in bookshop has rights to update information in table BOOK. When a book is bought he brings in changes in table BOOK. At the same time information about author's earnings needs to be changed in table AUTHOR to which seller don't have rights. This can be easily organized using stored procedures.

• Decrease of network cost: using stored procedures more of the work is done on the server side and less is done on the client/application side. Thus less information needs to be sent between server and client.

To simplify stored procedures and functions management use MySQL Maestro's Stored Procedure Editor. This editor allows you to execute the existing stored procedure or edit its definition (procedure name, parameter list, procedure body, etc.). It opens when you create a new procedure or edit the existing one. Since MySQL Maestro 6.12 the editor stores parameters history for procedure execution so you can choose a set of already used values instead of entering them manually.

Picture 6. Stored Procedure Editor.

Creating procedures is becoming simple with MySQL Maestro's Create Procedure Wizard that guides you through the process of creating a new stored procedure.

Picture 7. Create Stored Procedure Wizard.

Stored procedures are very convenient when user needs to develop some complex algorithm and call it in requisite time. But sometimes some acts should be done under certain conditions. In these cases triggers are used. Trigger is a stored procedure that is associated with a table and that activates when a user tries to insert, update or delete data into/from table.

Triggers advantages:

• Referential integrity provision: user can use triggers for ensuring referential integrity in database. Referential integrity defined in the DELETE FROM statement is rather weak as it can't solve some problems. Cascade data change in interrelated tables is among them. Triggers also allow to support more complicated constrains compared to rules, allow compare modified data and realize some actions as the result of this comparison.

• Automation: triggers are invoked automatically. They are executed in any case if an event occurs.

• "If, then"-analysis: triggers can fulfill simple "if, then"- analysis. Trigger can compare table state before and later data modification and act basing this comparison.

  Example:

  DELIMITER |
  CREATE TRIGGER `upd_film`
    AFTER UPDATE
    ON `film`
    FOR EACH ROW
  BEGIN
      IF (old.title != new.title) or (old.description != new.description)
      THEN
          UPDATE film_text
              SET title=new.title,
                  description=new.description,
                  film_id=new.film_id
          WHERE film_id=old.film_id;
      END IF;
    END
    |
  
  DELIMITER ;
  
  

• Business space determination: triggers are very important in automation of business process. Triggers can be used to send manager information about deficient of goods on warehouse and importance of replenishment in case if total amount of goods is less then it is important.

   

Work with triggers can be simplified with the help of MySQL Maestro's Trigger Editor. This editor allows you to set up trigger properties when you add or edit a trigger. Editing triggers isn't standard for MySQL because there is no ALTER TRIGGER statement, but it is implemented in MySQL Maestro.

Picture 8. Trigger Editor.

Creation of your own database triggers is clear with Create Trigger Wizard. Just follow the easy wizard steps like the following.

Picture 9. Create Trigger Wizard.

Table partitioning allows you to distribute portions of individual tables across a filesystem according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

Partitioned tables advantages:

• Increased performance: during scan operations, the MySQL optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution. For example, a million row table may be broken up into ten different partitions in range style so that each partition contains 100,000 rows. If a query is issued that only needs data from one of the partitions, and a table scan operation is necessary, only 100,000 rows will be accessed instead of a million.

• Simplified data management: partitioning allows a DBA to have more control over how data is managed inside of the database. By intelligently creating partitions, a DBA can simplify how certain data operations are performed. For example, a DBA can drop specific partitions in a partitioned table while the remaining partitions remain intact (as opposed to crafting a fragmentation-producing mass delete operation for the whole table).

• Data safe keeping: all maintenance functions and a number of other operations can be performed on partitions (rather than acting on a full table), including: ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION, ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, and REPAIR PARTITION.

For your convenience MySQL Maestro provides to create a partitioned table in a few mouse clicks. The Create Table Wizard supports all MySQL partitions types. Just select the type on the first wizard step and add all the partitions you need on the adding table subitems step.

Picture 10. Partitioned table creation.

MySQL Events are tasks that run according to a beforehand defined schedule. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time.

Events advantages:

• Rutine actions automation: a user may schedule daily, weekly or other kind of scheduled operations on the defined database, and they will be performed without his/her intervention.

  Example:

  DELIMITER |
  
  CREATE EVENT e_daily
      ON SCHEDULE
        EVERY 1 DAY
      COMMENT 'Saves total number of sessions then clears the table each day.'
      DO
        BEGIN
          INSERT INTO site_activity.totals (when, total)
            SELECT CURRENT_TIMESTAMP, COUNT(*) 
              FROM site_activity.sessions;
          DELETE FROM site_activity.sessions;
        END |
  
  DELIMITER ;

• Data checking flexibility: system procedures for checking the consistency of the data and the system health could be run automatically at regular intervals.

You can enable/disable an existing event with MySQL Maestro's Event Editor. Moreover the editor provides to set the event definition and other properties such as event schedule.

Picture 9. Event Editor.

For your convenient schedule setting with MySQL Maestro, it was implemented Schedule Properties window. The window is available during the event creation and from the Event Editor.

Picture 10. Event Schedule Properties.

New-appeared MySQL's features (Views, Stored Procedures, Triggers, Partitions, and Events) are making MySQL 5.x rather powerful database server that can be used even in large enterprise products. MySQL Maestro has all the tools for developing easy and effective applications starting from small to industrial.

Related links:

Back to news archive