Morfik Database Guide - Main Firebird SQL Statements

From Morfikwiki.com

This chapter will provide you with a general overview of the four main Statements in SQL language (Select, Insert, Update and Delete) and on how they are implemented in Firebird, Morfik’s built-in database. These are the Statements developers will be using, most of the time, when creating a Morfik, database driven, Web application.

The Database Connections topic of this guide has already presented a general overview on how to use SQL Statements from Morfik source code so this chapter will focus more on the Statements themselves then on explaining how to call the from within a Morfik application. This topic will, however, on the aspects of these Statements which can, normally, used from within a Morfik application leaving aside, for example, issues which relate to their usage in Stored Procedures or Triggers.

Contents 1 The Select Statement 1.1 Select Statement Syntax 1.1.1 ALL 1.1.2 DISTINCT 1.1.3 FIRST and SKIP 1.1.4 Columns Returned 1.1.5 FROM <table_or_view_or_procedure_or_select> 1.1.6 JOINS 1.1.7 INNER JOIN 1.1.8 OUTER JOIN 1.1.9 CROSS JOIN 1.1.10 WHERE 1.1.11 GROUP BY 1.1.12 HAVING 1.1.13 UNION [ALL | DISTINCT] 1.1.14 ORDER BY 1.2 The Insert Statement 1.3 The Update Statement 1.3.1 The SET Clause 1.3.2 The WHERE Clause 1.4 The Delete Statement 2 Wrapping it up 3 See Also

[edit] The Select Statement

The most known Statement in all the different variations of the SQL language is undoubtedly the Select Statement. This Statement, used in just about any relational database server available today, allows you to specify which information you want to retrieve from the database.

The Database Modeling topic, of this guide, presented the concept of Queries as objects which are a part of your project. This concept was then extensively used in creating the MorfikCMS sample application as described in the Visual Database Programming topic. All the queries mentioned and created to support the MorfikCMS application are based on the Select Statement. In fact, Select queries are the only ones which can be created visually inside the Morfik AppsBuilder Workspace.

Most of the Select Statements in this application are quite simple and only involve data retrieval from a single table. Such is the case of the GetSubSections query, for example. The following code snippet contains the SQL language code of the GetSubSections query.

SELECT ALL
    "Section"."Id", 
    "Section"."Title",
    "Section"."SubTitle", 
    "Section"."IsPublic",
    "Section"."ParentId"
FROM
    "Section"
WHERE
    "Section"."IsPublic" = 1 AND "Section"."ParentId" = :ParamParent

Select queries, however, can retrieve data from multiple columns. The following code snippet shows the SQL language code for the GetSectionArticles query.

SELECT ALL
    "Article"."Id",
    "Article"."Title",
    "Article"."Summary",
    "Article"."IsPublished",
    "Article"."DatePublished",
    "Article"."SectionId",
    "UserCatalog"."FullName"
FROM
    ("UserCatalog" INNER JOIN "Article" ON
    ("UserCatalog"."Id"="Article"."CreatedBy"))
WHERE
    "Article"."IsPublished" = 1 AND "Article"."SectionId" = :ParamId

These queries where created visually within the Morfik AppsBuilder Workspace using the Visual Query Designer. This obviously requires next to no knowledge of the SQL language.

There will be situations when you might want or need to hand code a query. In the MorfikCMS sample project a good example of this is the GetOneArticleCount query. This query was used to replace the GetOneArticle query. The difference between them is that while one actually specifies all the data to be retrieved from fields and tables, the other retrieves the results of a selectable stored procedure. Since it is not possible to visually create such a query in the Morfik Workspace, our only recourse is to hand code the Statement.

[edit] Select Statement Syntax

In order to better understand the power of the Select Statement, lets have a quick look at its syntax. The Select syntax as defined in the Firebird 2.0.1 release notes is the following:

<select statement> ::=
<select expression> [FOR UPDATE] [WITH LOCK]

<select expression> ::=
<query specification> [UNION [{ALL | DISTINCT}] <query specification>]

<query specification> ::=
SELECT [FIRST <value>] [SKIP <value>] <select list>
FROM <table expression list>
WHERE <search condition>
GROUP BY <group value list>
HAVING <group condition>
PLAN <plan item list>
ORDER BY <sort value list>
ROWS <value> [TO <value>]

<table expression> ::=
<table name> | <joined table> | <derived table>

<joined table> ::=
{<cross join> | <qualified join>}

<cross join> ::=
<table expression> CROSS JOIN <table expression>
<qualified join> ::=
<table expression> [{INNER | {LEFT | RIGHT | FULL} [OUTER]}] JOIN <table expression>
ON <join condition>

<derived table> ::=
'(' <select expression> ')'

In summary form:

SELECT [FIRST (n)] [SKIP(n)] [ALL | DISTINCT] 
<column_list> [, [column_name] | expression | constant] AS alias_name]
FROM <table_or_procedure_or_view_or_select>
[{[[INNER] | [{LEFT | RIGHT | FULL} [OUTER]] JOIN }] 
   <table_or_procedure_or_view_or_select> ON <join_conditions> [{JOIN…]]
[WHERE <search_conditions>]
[GROUP BY <grouped_column_list>]
[HAVING <search_condition>]
[UNION <select_expression> [ALL]]
[PLAN <plan_expression>]
[ORDER BY <column_list> [[ASC] | DESC]]
[ROWS <expression1 [TO <expression2>]]
[FOR UPDATE [OF column1 [, column2…]] [WITH LOCK]]

It is important to note that the clauses in square brackets are optional and that the order in which they appear in the Statement is significant.

We will proceed to proceed to have a quick look at the more frequently used clauses of the Select Statement.

[edit] ALL

The inclusion of the ALL clause means that the Select Statement should return all the rows (just the values for the specified columns) which where found. This is the default behavior for the Select Statement so its inclusion is customarily neglected.

[edit] DISTINCT

The DISTINCT clause removes duplicate rows which might have been retrieved by the Select Statement. This means that any row having the same values for the columns listed in the Select Statement will be condensed into a single row in the result set.

A good example of the usage of this Statement would be to select the list of countries from which visitors to a website come from. The following code snippet consists of an alphabetically sorted list of countries present in the table. No duplicates will exist, no matter how many rows the table contains with the same value in the "Country" column.

 SELECT DISTINCT "Country" FROM "Visitor" ORDER BY "Country" 

[edit] FIRST and SKIP

The FIRST clause specifies how many rows should be returned from the Select Statement, while the SKIP clause informs how many rows should be ignored before starting to return rows.

 SELECT FIRST 5 "Id", "Country", FROM "Visitor" ORDER BY "Country" 

The previous Statement would return the first five Countries and Ids from the Visitor table.

 SELECT FIRST 5 SKIP 5 "Id", "Country", FROM "Visitor" ORDER BY "Country" 

The second Statement would return the "next" five rows, discarding the first five, considering the sorting based on the Country column.

One of the most common uses of these clauses is to paginate the data returned from a Select Statement. It should be quite rare to need to use these clauses when working in a Morfik application as the Morfik Framework provides automatic paging of the data returned by the data sources associated with Forms.

[edit] Columns Returned

When creating a Select statement the user must specify which columns should be returned, from the rows that match the requested criteria. If an "*" (asterisk) is specified all columns will be returned. The Select statement can not only specify existing columns from the specified tables but, also, calculated columns which are created on the fly from existing columns. The following are some examples of Select statements:

 SELECT "Id", "Title" FROM "Article" WHERE "SectionId" = 5 

This statement returns the Id and Title of all rows in the Article table which have the column SectionId with a value of 5.

 SELECT "Id", "Title" FROM "Article" WHERE "SectionId" = 5 ORDER BY "Title" 

This second statement will retrieve the same rows as the first one but this time they will be ordered by the content of the Title column.

 SELECT "FirstName" || ' ' || "LastName" AS "FullName" FROM "UserCatalog" 

The above statement returns as a column called FullName, the concatenated value of the FirstName and LastName columns with a space added in between them for good measure.

 SELECT "FirstName" || ' ' || "LastName" AS "FullName" FROM "UserCatalog" ORDER BY "FullName" 

The inclusion of the ORDER BY clause in the above statement shows that calculated columns can also be used for sorting.

 SELECT COUNT(*) AS "Total" FROM "UserCatalog" 

The COUNT function, used in the previous statement is used to count how many rows conform to the specified criteria. Since in this example no criteria were specified it returns the number of rows in the UserCatalog table.

 SELECT MAX("ViewCount") FROM "Article" 

This simple statement returns the highest value for the ViewCount column in table Article.

SELECT "Id", "Title", (SELECT COUNT(*) FROM "Article" A 
WHERE A."SectionId" = S."Id") AS NUM_ARTICLES FROM "Section" S

This example uses a sub-query to calculate a new column. In this example the select statement is returning the Id and Title of each row in the Section table, as well as the number of articles in the Article table which are related to each section. This example also shows the use of table aliases (in this case A and S).

Note
	The user should always involve identifiers such as table names and column names with double quotes unless the usage of all-caps identifiers is considered acceptable. Firebird requires that all identifiers conform to one of these two standards so identifiers are either all-caps or enclosed in double quotes.

As a user becomes more familiar with the SQL statements used by the built-in database he/she will naturally be able to create more complex statements, sometimes accomplishing complex tasks with very little code.

[edit] FROM <table_or_view_or_procedure_or_select>

The FROM clause in the Select statement is used to identify in which tables the desired data is stored. If data is being recovered from more than one table the FROM clause will contain a list of tables.

In addition to common tables the developer can specify as the origin of the data views, stored procedures and even other Select statements. The following code snippet shows a Select statement which retrieves data from a selectable stored procedure, for example:

 SELECT * FROM "Article_SEL"(3) 

In creating the FROM clause of Select statements developers are allowed to declare aliases for the tables. It is common practice to create short aliases in order to reduce the amount of typing required to create more complex statements. The following example shows the declaration and usage of two table aliases (A and S).

SELECT "Id", "Title", (SELECT COUNT(*) FROM "Article" A 
WHERE A."SectionId" = S."Id") AS NUM_ARTICLES FROM "Section" S

Note

The version of Firebird that is used as Morfik’s built-in database engine does not support the mixed usage of table names and aliases when qualifying column names. The following code, for example, will generate and error. SELECT U.ID, USERCATALOG.USERNAME FROM USERCATALOG U The error will be due to the fact that this statement is qualifying columns using both the tables name (USERCATALOG) and its alias (U).

[edit] JOINS

The JOIN clause specifies the relationship between tables in a Select statement allowing the retrieval of columns from different tables in a single result set.

Firebird supports several different types of JOINs: INNER JOIN, OUTER JOINs (LEFT, RIGHT, FULL) e CROSSJOIN. The JOIN clause is normally paired with an ON clause. Together these two clauses determine the relationship between tables. The information retrieved from each table is called a stream.

 SELECT S."Id", S."Title", A."Title" FROM "Section" S JOIN "Article" A ON S."Id" = A."SectionId" 

[edit] INNER JOIN

The INNER JOIN or exclusive join can be represented by omitting the INNER clause as in the previous example. That essentially means that this is the default JOIN behavior and that the previous example could have been written as follows.

 SELECT S."Id", S."Title", A."Title" FROM "Section" S INNER JOIN "Article" A ON S."Id" = A."SectionId" 

This kind of join will only return information on Sections that do have Articles associated with them. This means that any sections which do not have any articles will be excluded from the result set.

[edit] OUTER JOIN

An OUTER JOIN returns rows even when the condition is not satisfied. In this case the columns belonging to the stream where there was no related data will be returned as NULL.

It is important to note that an OUTER JOIN is composed of two streams (right and left). When multiple JOINs are used in a single Select statement the right stream of a JOIN can be the left stream of another one.

There are three types of OUTER JOINs: LEFT, RIGHT and FULL. In each case, once one of these clauses has been specified the OUTER clause itself can be omitted from the Select statement.

The LEFT OUTER JOIN returns all the rows of the left stream, regardless of the specified condition being met.

SELECT S."Id", S."Title", A."Title" FROM "Section" S LEFT OUTER JOIN 
"Article" A ON S."Id" = A."SectionId"

The RIGHT OUTER JOIN, on the contrary, returns all the rows of the right stream, regardless of the specified condition being met.

SELECT S."Id", S."Title", A."Title" FROM "Section" S RIGHT OUTER JOIN 
"Article" A ON S."Id" = A."SectionId"

Finally a FULL OUTER JOIN returns all the rows from both streams, once again, regardless of the specified condition being met.

SELECT S."Id", S."Title", A."Title" FROM "Section" S FULL OUTER JOIN 
"Article" A ON S."Id" = A."SectionId"

[edit] CROSS JOIN

A CROSS JOIN returns the Cartesian product of the related tables. It is important to use this feature with caution as its result set might be overly large and therefore cause application performance degradation.

Have in mind that the returning result set of a CROSS JOIN between two tables with 1,000 rows, each, will hold 1,000,000 rows. The following code snippet shows the syntax for the use of a CROSS JOIN clause.

 SELECT A.NAME, B.ID FROM TABLEA A CROSS JOIN TABLEB B 

[edit] WHERE

The WHERE clause enables developers to specify criteria which must be met in order for a row to be part of the returning result set of a Select statement. The following code snippet, for example, uses the WHERE clause to specify that only rows of the Article table with the column SectionId holding the value of five should be returned.

 SELECT "Id", "Title" FROM "Article" WHERE "SectionId" = 5 

When specifying criteria for the WHERE clause a number of different operators can be used. Table 1 shows a list of operators and a brief description of how they work and are used in composing Select statements.

---

Table 1 – Comparison Operators
Property	Value
=	Equality
<>, !=, ~=, ^=	Non Equality (Different)
>	Greater Than
<	Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To
!>, ~>, ^>	Not Greater Than (essentially the same as Less Than or Equal To)
!<, ~<, ^<	Not Less Than (essentially the same as Greater Than or Equal To)
BETWEEN AND	Used to specify a range within which a value must fall. SELECT ID, FULLNAME FROM USER WHERE ID BETWEEN 1 AND 10;
CONTAINING	Used to match a substring within a column. SELECT ID, FULLNAME FROM USER WHERE NAME CONTAINING ’PAUL’ This statement will return rows where the FULLNAME column contains the substring ’PAUL’.
IN	Used to verify if a column matches any of the values contained in a list. SELECT ID, TITLE FROM ARTICLE WHERE SECTIONID IN (1,2,3) This operator allows up to 1,500 values to be listed.
STARTING WITH	Used to match a substring at the start of a column. SELECT ID, FULLNAME FROM USER WHERE NAME STARTING WITH ’PAUL’ The above example, contrary to that of the CONTAINING operator will not return a row with name such as ’JEAN-PAUL’ since it does not Start with ’PAUL’.
LIKE	Used to match substrings with the usage of wildcard characters. (% and _) The ’%’ character indicates any number of characters while the ’_’ indicates a single character.

[edit] GROUP BY

The GROUP BY clause specifies by which columns a result set will be grouped. Use of this clause is mandatory whenever an aggregate functions is used (COUNT, SUM, MAX, MIN, etc) in any of the selected columns.

SELECT U.COUNTRY, COUNT(*) AS USERCOUNT FROM USERCATALOG U GROUP BY
U.COUNTRY ORDER BY USERCOUNT DESC

[edit] HAVING

The HAVING clause works in a similar manner to the WHERE clause but works with conditions based on aggregates. The following code snippet shows a variant of the previous example, but in this we are restricting the result set to only those countries which have less than 10 users.

SELECT U.COUNTRY, COUNT(*) AS USERCOUNT FROM USERCATALOG U GROUP BY
U.COUNTRY HAVING COUNT(*) < 10 ORDER BY USERCOUNT DESC

[edit] UNION [ALL | DISTINCT]

The UNION clause allows us to bring data from multiple select statements into one single result set. The selects involved in a UNION operation can all reference the same table(s) or entirely different ones in which case Firebird will automatically try to cast the distinct row types in a single compatible type.

SELECT U.FULLNAME FROM USER U
UNION
SELECT S.FULLNAME FROM SUPERUSER S

Note

Note that the standard behavior for a UNION operation is to exclude duplicate entries so if there is a name which is present in both the USER and SUPERUSER tables of the previous example, it will appear only once. If the desired result is that "all" rows from both Select statements be returned, the UNION clause must be substituted by the UNION ALL clause. Since the default behavior is to suppress duplicates the UNION clause is actually a synonym to the UNION DISTINCT clause.

[edit] ORDER BY

The ORDER BY clause allows the developer to specify how the rows returned in the result set of a Select statement will be sorted. The sorting can be either ASCENDING (ASC – default) or DESCENDING (DESC).

 SELECT "Id", "Title" FROM "Article" WHERE "SectionId" = 5 ORDER BY "Title" ASC 

The previous example returns all rows of the Article table where the column SectionId is equal to 5, in ascending alphabetical order of values for the Title column.

[edit] The Insert Statement

The Insert statement is used for adding new data into a single table. It is important to note that the SQL language does not allow for the insertion of data (rows) into more than one table per Insert statement.

The Insert Statement can take two forms. The first form allows for the insertion of a single row whose column values are specified in the VALUES clause. The second form allows for the insertion of multiple rows at once, the column values of which will originate from a Select statement.

 INSERT INTO USER(ID, FIRSTNAME, LASTNAME) VALUES (100, 'JON', 'DOE') 

The previous sample shows a row with three columns being inserted into the User table. If we suppose that the ID column will actually be an AutoNumber field/column we can dispense with its presence in our code and be confident that it will receive the appropriate value automatically.]

 INSERT INTO USER(FIRSTNAME, LASTNAME) VALUES ('JON', 'DOE')

In case of inserting multiple rows at once, we need to use another syntax which allows us to specify through a Select statement which values will be inserted. The following code snippet exemplifies this syntax by retrieving all rows from the SuperUser table that have the column FirstName starting with the letter A and inserting them into the User table.

INSERT INTO USER(FIRSTNAME, LASTNAME) SELECT FIRSTNAME, LASTNAME 
FROM SUPERUSER WHERE FIRSTNAME STARTING WITH 'A'

[edit] The Update Statement

The Update statement is used to change information already stored in the rows of a table. As with the Insert Statement, the Update Statement cannot affect rows in more than one table at a time.

Though the Update statement can be used in the context of a cursor operation or of a search, in this chapter we will focus exclusively on the later as it is the only way which makes sense for usage from within a Morfik XApp.

 UPDATE USERCATALOG SET FIRSTNAME = 'Mark', LASTNAME = 'Harris' WHERE ID = 5 

This example shows a sample Update statement that sets two columns (FIRSTNAME and LASTNAME) of a row of the USERCATALOG table which has the ID column with the value of 5.

Update statements identify the rows which they will affect in the same manner as a Select statement identifies which rows it will return.

[edit] The SET Clause

The SET clause allows the developer to specify a list of coma separated list of value assignments to columns as shown in the following code snippet.

 UPDATE USERCATALOG SET FIRSTNAME = 'Mark', LASTNAME = 'Harris', PWD = 'SECRET', EMAIL = 'mark.harris@companyname.com' WHERE ID = 5 

Note that in this example four columns have their values set. Any columns which are not specified in an Update statement will retain their original values.

[edit] The WHERE Clause

The WHERE clause of an Update statement works in exactly the same manner as that of the same clause of the Select statement.

The previous examples of usage of the Update statement have all included the usage of the WHERE clause.

[edit] The Delete Statement

The Delete statement is the most simple of the four main SQL statements which are covered in this chapter. Its sole purpose is to remove rows from a table. As with the Insert and Update statements the Delete statement can have an optional WHERE clause which, again, works exactly as the one in the Select Statement.

The following code snippet shows the usage of the Delete statement to remove all rows from the USERCATALOG table which have a value o zero in the ISACTIVE column.

 DELETE FROM USERCATALOG WHERE ISACTIVE = 0 

Note
	Great care must be taken when working with both the Update and Delete statements as a missing or incorrectly formulated WHERE clause condition might end up affecting a large number of rows in the table.

[edit] Wrapping it up

In this chapter we have reviewed the basics of the four most used statement of the SQL Language, in a Morfik Application. There are innumerable other statements and clauses in the SQL language standard and in PSQL, Firebird’s procedure and Trigger language. What was shown here should give developers the basic knowledge to start trying out new uses of these SQL statements.

[edit] See Also

• Morfik Database Programming
• Database Modeling
• Visual Database Programming
• Database Connections
• Editing and Searching
• Using Stored Procedures
• System Objects in a Database