Native SQL Queries

As of version 1.9, AMGA supports the native SQL query, which greatly eases the work needed to port existing SQL-based database applications to the Grid using AMGA and complements the existing metadata query language in AMGA. This feature also makes full use of the advanced security features of AMGA, namely schema ACLs and access through the GSI.

The followings are currently supported native SQL queries.

• SQL-92 Entry Level <direct_data_statements>
  • SELECT, INSERT, UPDATE, and DELETE
• Part of SQL-92 Intermediate Level
  • OUTER JOIN
• Not defined in the SQL Standard
  • LIMIT and OFFSET

IMPORTANT NOTE: All the keywords in native SQL queries should be provided in capital lettersr. However table names, aliases, and column names can be either of capital or small.

Table names are referred to as references to directories. And reference to an attribute can take one of the forms as follows:

  <attribute>
  <directory>:<attribute>
  <directory>.<attribute>

Special attribute such as FILE, OWNER, PERMISSIONS, GROUP_RIGHT refer to the properties of an entry. For detail, refer metadata_quries .

SQL-92 Entry Level <direct_data_statements>

AMGA supports <direct_data_statements> in the SQL-92 entry level that is supported by most DBMS such as PostgreSQL, Oracle, MySQL, SQLite and etc. The SQL-92 Entry Level <direct_data_statements> includes 4 basic statements: SELECT, INSERT, UPDATE, and DELETE. The brief syntax of supported statements is as follows.

• SELECT [ALL | DISTINCT] select_list from_clause [where_clause] [group_by_clause] [having_clause] [order_by_clause] [;]

• INSERT INTO table [column_list] VALUES ( { value_list | query_specification } ) [;]

• UPDATE table SET assignment_list [where_clause] [;]

• DELETE FROM table [where_clause] [;]

Examples are as follows.

  - Query> SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM='P2' ORDER BY 2 ASC

  - Query> SELECT SUM(HOURS),AVG(HOURS),MIN(HOURS),MAX(HOURS) FROM WORKS 
    WHERE EMPNUM='E1';

  - Query> SELECT * FROM STAFF FIRST1, STAFF SECOND2 
    WHERE FIRST1.CITY = SECOND2.CITY AND FIRST1.EMPNUM < SECOND2.EMPNUM

  - Query> INSERT INTO WORKS VALUES ('P22','E22',NULL);

  - Query> UPDATE STAFF SET GRADE = NULL 
    WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';

  - Query> DELETE FROM WORKS WHERE PNUM='P18';

The exact syntax of SQL-92 entry level is described in the following site.

     - http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt. 

The output of SELECT statement prints attribute names followed by attributes values. Other statements return how many rows have been changed as a result.

Possible Errors are:

• 4 Permission denied : You need proper permission on the table
• 88 SQL Parsing Error : SQL syntax is wrong

LIMIT, OFFSET and JOIN

LIMIT, OFFSET and JOIN are supported in AMGA even though they are not defined in the SQL-92 Entry Level, because there were many requirements from user communities. With LIMIT and OFFSET, it is possible to specify a range of rows to return. The SQL JOIN combines records from two tables, resulting in a new, temporary table, sometimes called a "joined table".

Examples are as follows.

  - Query> SELECT * FROM WORKS RIGHT OUTER JOIN STAFF USING (EMPNUM) 
    ORDER BY WORKS.EMPNUM LIMIT 1 OFFSET 2

Current Limitation

• The native SQL queries do not provide means to modify ACLs on tables or entries, which can be set properly by other AMGA operations.

---