A little bit more about SQLAlternatives to MySQLDifferences between DBMSDifferences (continued)ViewsCreating a view with MySQLDeleting a viewChanging the view definitionSome properties of a view…TriggersTriggers (continued)Slide 13Slide 14Slide 15WebsitesA little bit more about SQLAlternatives to MySQLViews/TriggersBy Loïs DesplatAlternatives to MySQLPostgreSQL – It has more features than many other DBMS including MySQL and is completely free thanks to its BSD license.SQLite – Small library, meant to be embedded inside an application without many features but surprisingly compliant to the SQL standard (source under the public domain!)Differences between DBMSEven though all three of the mentioned DBMS (MySQL, PostgreSQL, SQLite) have a strong adherence to the standard, there are some differences.Even though you might have chosen one DBMS, be aware of the limitations and differences of the other DBMS so that if you have to switch, you will have designed your program to easily switch to another DBMS.Differences (continued)Be aware when you use special features of one DBMS, you will be practically locked to that DBMS and it might be very hard to switch to another DBMS.Thankfully, most DBMS based on SQL do try very hard to adhere to the standard and the differences are almost always very small.ViewsA view is a subset of a table. You can use it to retrieve and update data or even delete rows.You create a view from attributes/tuples of other tables and from there you can do almost everything that you can do with a table.Creating a view with MySQLCREATE TABLE t (qty INT, price INT);INSERT INTO t VALUES(3, 50);CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;qty price value3 50 150Deleting a viewVery simple commandDROP VIEW v;Changing the view definitionALTER VIEW v AS SELECT qty, price FROM t;Same as CREATE VIEW. It actually DROPS the view and then creates it again. It is a shortcut.Some properties of a viewWhen you update the tuples inside the view, they are updated in the table where you took them from.So let’s say some crazy CEO decided to triple the price.UPDATE v SET price=price*3;qty price value3 150 150…Did you see that?Our value attribute didn’t get updated when the price went up.Thankfully, triggers are at the rescue or are they?Actually they won’t help us here, but we’ll see why later!TriggersA trigger is an object in a database that is associated with a table and is activated when a particular event occurs in the table.Unfortunately, triggers can only be associated with a permanent table and not with a temporary one or a view (at least in MySQL)Triggers (continued)Let’s assume that our view v is now a permanent table so that we can associate a trigger with it.CREATE TRIGGER updatevalue AFTER UPDATE ON v FOR EACH ROW BEGINUPDATE v SET value=price*qty;ENDTriggers (continued)So there you go, we can now have our value attribute updated every single time that the table changes.The previous command did not look very efficient to me (what if you updated only one tuple.. You don’t need to go through all the rows).It appears that for most uses of a trigger you will need to go through all the rows though.TriggersYou can also remove a trigger.DROP TRIGGER updatevalue;Other DBMS like Oracle and Postgres seem to have a more advanced trigger implementation.All the features shown in this presentation are only present since the release of MySQL 5.0.10Other DBMS have had these features for a very long time including Oracle and Postgres.SQLite mentioned earlier is, at its name implies, light. Therefore it has limited support for Triggers and Views. For example views are read-only in sqlite.WebsitesMySQL: http://www.mysql.comPostgreSQL: http://www.postgresql.orgSQLite:
View Full Document
Unlocking...