Constraints, Triggers, and ORM

Check

Two types:
                Attribute-based, Tuple-based

Attribute based checks

Put a constraint on the value of a particular attribute.

CHECK( <condition> ) must be added to the declaration for the attribute.

The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.

For example:

CREATE TABLE SALE(
	SALE_ID	INT PRIMARY KEY,
	PROD_ID	VARCHAR(20)	CHECK ( PROD_ID IN
			(SELECT PROD_ID FROM PRODUCT)),
	qty	INT CHECK ( qty > 20 )
);

How is the above different from a foreign key?

The kind of conditions to enforce? Check is clearly more broad

The timing/actions of enforcing a check?

An attribute-based check is checked only when a value for that attribute is inserted or updated.
                Example: CHECK (qty > 10) checks every new qty and rejects it if it is less than 10
                Example: CHECK (prod_ID IN (SELECT prod_ID FROM PRODUCT)) not checked if a prod_ID is deleted from PRODUCT (unlike foreign-keys).

Tuple-based checks

CHECK ( <condition> ) may be added as another element of a schema definition.

The condition may refer to any attribute of the relation, but any other attributes or relations require a subquery.

Checked on insert or update only.

Example: We only sell sweaters in bulk:

CREATE TABLE Sale (
		SALE_ID	INT PRIMARY KEY,
		PROD_ID	INT,
		qty		INT,
		CONSTRAINT sweaterQtyConstraint
		CHECK (PROD_ID = 1035 AND
					QTY > 100)
	);

Why do we need tuple level check?

We can do attribute-based check, why tuple level?

Allows checks based on other values in the tuple

Assertion

These are database-schema elements, like relations or views.

Defined By:

CREATE ASSERTION <name>
			CHECK ( <condition> );

Condition may refer to any relation or attribute in the database schema.

Unfortunately – Attribute- and tuple-based checks have limited capabilities.

  1. Assertions are sufficiently general for most constraint applications, but they are hard to implement efficiently.
  2. The DBMS must have real intelligence to avoid checking assertions that couldn’t possibly have been violated.

Instead we use:

Triggers

A trigger allows the user to specify when the check occurs.

Like an assertion, a trigger has a general-purpose condition and also can perform any sequence of SQL database modifications.

Event-Condition-Action

Another name for “trigger” is ECA rule, or event-condition-action rule.

Event :  typically a type of database modification, e.g., “insert on Sells.”

Condition : Any SQL boolean-valued expression.

Action : Any SQL statements.

There are many details to learn about triggers.

Here is an example to set the stage.

Instead of using a foreign-key constraint and rejecting insertions into SALE with unknown PRODUCTS, a trigger can add that product to PRODUCT, with a NULL description.

CREATE TRIGGER ProdTrig
	AFTER INSERT ON SALE 
	REFERENCING NEW ROW AS NewTuple
	FOR EACH ROW
	WHEN (NewTuple.prod_id NOT IN
		(SELECT prod_ID FROM PRODUCT))
	INSERT INTO PRODUCT(prod_ID, PROD_DESC,…)
		VALUES(NewTuple.prod_ID, null, …);

Trigger Options

The event

AFTER can be BEFORE.
                Also, INSTEAD OF, if the relation is a view.
                                A great way to execute view modifications: have triggers translate them to appropriate modifications on the base tables.

INSERT can be DELETE or UPDATE.
                And UPDATE can be UPDATE … ON a particular attribute.

For each row

Triggers are either row-level or statement-level.

FOR EACH ROW indicates row-level; its absence indicates statement-level.

Row level triggers are executed once for each modified tuple.

Statement-level triggers execute once for an SQL statement, regardless of how many tuples are modified.

Referencing

Tuple-Level

INSERT implies a new row.

DELETE implies an old row.

UPDATE implies both.

Statement-level
                Always have OLD/NEW table for statement level.

Refer to these by
                [NEW OLD][ROW TABLE] AS <name>

The condition

Any boolean-valued condition is appropriate.

It is evaluated before or after the triggering event, depending on whether BEFORE or AFTER is used in the event.

Access the new/old tuple or set of tuples through the names declared in the REFERENCING clause. (or fixed by “OLD”, “NEW” in MySQL.)

The Action

There can be more than one SQL statement in the action.
                Surround by BEGIN . . . END if there is more than one.

But queries make no sense in an action, so we are really limited to modifications.

Triggers vs Views

Generally, it is impossible to modify a view, because it doesn’t exist. BAD EXAMPLE ALERT

INSERT INTO can_visit VALUES (‘Weninger’, ‘Jefferson’)

But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.

CREATE TRIGGER ViewTrig
	INSTEAD OF INSERT ON can_visit
	REFERENCING NEW ROW AS n
	FOR EACH ROW
	BEGIN
		INSERT INTO Salesperson VALUES(n.salpersname, …);
	END;

Object Relational Mapping

Taken from: https://stackoverflow.com/questions/1279613/what-is-an-orm-how-does-it-work-and-how-should-i-use-one

Introduction

Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. When talking about ORM, most people are referring to a library that implements the Object-Relational Mapping technique, hence the phrase “an ORM”.

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don’t use SQL anymore; you interact directly with an object in the same language you’re using.

For example, here is a completely imaginary case with a pseudo language:

You have a book class, you want to retrieve all the books of which the author is “Linus”. Manually, you would do something like that:

sale_list = new List();
sql = "SELECT sale_id FROM sale WHERE qty > 100";
data = query(sql); 
while (row = data.next())
{
     sale = new Sale();
     sale.setQuantity(row.get('qty');
     sale_list.add(sale);
}

With an ORM library, it would look like this:

sale_list = SaleModel.query(qty>100);

The mechanical part is taken care of automatically via the ORM library.

Pros and Cons

Using ORM saves a lot of time because:

  • DRY: You write your data model in only one place, and it’s easier to update, maintain, and reuse the code.
  • A lot of stuff is done automatically, from database handling to I18N.
  • It forces you to write MVC code, which, in the end, makes your code a little cleaner.
  • You don’t have to write poorly-formed SQL (most Web programmers really suck at it, because SQL is treated like a “sub” language, when in reality it’s a very powerful and complex one).
  • Sanitizing; using prepared statements or transactions are as easy as calling a method.

Using an ORM library is more flexible because:

  • It fits in your natural way of coding (it’s your language!).
  • It abstracts the DB system, so you can change it whenever you want.
  • The model is weakly bound to the rest of the application, so you can change it or use it anywhere else.
  • It lets you use OOP goodness like data inheritance without a headache.

But ORM can be a pain:

  • You have to learn it, and ORM libraries are not lightweight tools;
  • You have to set it up. Same problem.
  • Performance is OK for usual queries, but a SQL master will always do better with his own SQL for big projects.
  • It abstracts the DB. While it’s OK if you know what’s happening behind the scene, it’s a trap for new programmers that can write very greedy statements, like a heavy hit in a for loop.