/*
The projects table is used to store the name projects.
@authorised='USER;ADMIN'
@filter='PROJECT_ID,project_name'
@indexes='project_name,project_start'
@prefix='project'
@save='one,two,three'
@order='project_name, project_code; project_code'
*/
table projects
{
ORG_ID Key
PROJECT_ID Primary Key
project_type Text
project_name Text
project_code Text[20]
project_start Date
project_end Date
project_approved Bool
project_priority Int
}
The premise of SPGEN is that an SQL table definition can be used to generate key stored procedures that provide the majority of the code needed for a web application.
DROP TABLE projects;
CREATE TABLE projects
(
ORG_ID INT NOT NULL,
PROJECT_ID INT AUTO_INCREMENT,
project_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
project_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
project_deleted TIMESTAMP DEFAULT 0,
project_type TEXT NOT NULL,
project_name TEXT,
project_code CHAR(99) DEFAULT '',
PRIMARY KEY (PROJECT_ID)
)
COMMENT 'Authorised=USER;ADMIN',
COMMENT 'Filter=PROJECT_ID;project_name',
COMMENT 'Save=project_name;project_code',
COMMENT 'OrderBy=project_name;project_code;project_name, project_code';
Notes:
- Mandatory foreign keys are placed at the top of the table definition and give identified as "NOT NULL".
- The primary key should come after the mandatory foreign keys.
- After the primary key should be a creation datetime that is initialised once when a tuple is created.\
- After the creation datetime should be a timestamp that is modified ON UPDATE.
- After the modification timestamp should come any fields relevant for that table.
- Fields that only exist in this table should be share a common prefix that is the prefix of the primary key.
- If a field should only be stored into when the tuple is initially created it should be marked as "NOT NULL".
- One or more structured textual comments are added to the table to provide additional information to guide the generation of procedures:
- Each comment has the form 'keyvalue|...', where each keyvalue is:
- keyvalue: {key=value;...}.
- e.g., COMMENT 'Authorisation=USER;ADMIN|Generate=QUERY;REPLACE;SAVE'
There are three general purpose procedure constructions that are useful for manipulating database tables, these:
- Retrieve information from the table.
- Create or update all of the data in a table
- Update specific fields in a table
These procedures should use the following names, in Title case.
- {table name} - query results set from table, e.g., "Projects"
- {table name}_Replace - replace tuple into table, e.g., "Projects_Replace"
- {table name}_Save - update single field of tuple of table, e.g., "Projects_Save"
Query
DROP PROCEDURE Projects;
DELIMITER //
CREATE PROCEDURE Projects
(
$Sid TEXT,
$apikey TEXT,
$ORG_ID INT,
$PROJECT_ID INT,
$project_name TEXT,
$limit INT,
$offset INT,
$order TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL CheckLimitOffset( $limit, $offset );
CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ORG_ID, @email, @USER, @GROUP_ID, @idtype, @role );
IF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Projects';
ELSEIF NOT( @role LIKE '%QUERY%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Projects';
ELSEIF "project_name" = $order OR "" = $order THEN
SELECT *
FROM projects
WHERE ORG_ID = $ORG_ID
AND project_deleted = 0
AND (0 = $PROJECT_ID OR PROJECT_ID = $PROJECT_ID )
AND ('' = $project_name OR project_name = $project_name)
ORDER BY project_name
LIMIT $limit
OFFSET $offset;
ELSEIF "project_code" = $order THEN
SELECT *
FROM projects
WHERE ORG_ID = $ORG_ID
AND project_deleted = 0
AND (0 = $PROJECT_ID OR PROJECT_ID = $PROJECT_ID )
AND ('' = $project_name OR project_name = $project_name)
ORDER BY project_code
LIMIT $limit
OFFSET $offset;
ELSEIF "project_name, project_code" = $order THEN
SELECT *
FROM projects
WHERE ORG_ID = $ORG_ID
AND project_deleted = 0
AND (0 = $PROJECT_ID OR PROJECT_ID = $PROJECT_ID )
AND ('' = $project_name OR project_name = $project_name)
ORDER BY project_name, project_code
LIMIT $limit
OFFSET $offset;
END IF;
END
//
DELIMITER ;
Notes
- The parameters passed into the QUERY procedure include a 'sessionid', an 'apikey', and any fields indicated in the tables 'Filter' specification, as well as 'limit' and 'offset'.
- The SQL SECURITY mode is set as definer, which means that when it is executed it has the privileges of the account the defined it (usually root).
- A COMMENT of 'EXPORT' is added, which will allow the procedure to be callable through a Web API Bridge.
- The procedure assumes the use of the Base Schema set of base tables, views, functions, and procedures. The procedure Base_Users_Authorise_Sessionid_Or_Apikey authorises further action by verifying the validity of either the sessionid or apikey. @email, @USER, @GROUP, and @idtype are OUT variables that can be used to determine if further action is authorised.
- The generated query uses the following construction that allows for a Filter variable to only be activive if it is passed
- WHERE ("" = project_name OR project_name = $project_name)
- Note: in the future it may be required that ignored filter variables must be null, in which case the following would be used:
- WHERE (ISNULL(project_name) OR project_name = $project_name)
Replace
DROP PROCEDURE Projects_Replace;
DELIMITER //
CREATE PROCEDURE Projects_Replace
(
$Sid TEXT,
$apikey TEXT,
$ORG_ID INT,
$PROJECT_ID INT,
$project_type TEXT,
$project_name TEXT,
$project_code TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ORG_ID, @email, @USER, @GROUP_ID, @idtype, @role );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Projects_Replace';
ELSEIF NOT( @role LIKE '%REPLACE%' )THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Projects_Replace';
ELSE
IF NOT $PROJECT_ID THEN
REPLACE INTO projects
( GROUP_ID, ORG_ID, PROJECT_ID, project_created, project_type )
VALUES
( @GROUP_ID, $ORG_ID, $PROJECT_ID, NOW(), $project_Type );
SET $PROJECT_ID = LAST_INSERT_ID();
END IF;
UPDATE projects
SET
project_name = $project_name,
project_code = $project_code
WHERE ORG_ID = $ORG_ID
AND PROJECT_ID = $PROJECT_ID
AND project_deleted = 0;
DO LAST_INSERT_ID( $PROJECT_ID );
END IF;
END
//
DELIMITER ;
Notes
- The parameters passed into the REPLACE procedure include a sessionid, an apikey, any foreign keys, the primary key, and any content fields.
- The SQL SECURITY mode is set as definer, which means that when it is executed it has the privileges of the account the defined it (usually root).
- A COMMENT of 'EXPORT' is added, which will allow the procedure to be callable through a Web API Bridge.
- The procedure assumes the use of the Base Schema set of base tables, views, functions, and procedures. The procedure Base_Users_Authorise_Sessionid_Or_Apikey authorises further action by verifying the validity of either the sessionid or apikey. @email, @USER, @GROUP, and @idtype are OUT variables that can be used to determine if further action is authorised.
- If a PROJECT_ID is not passed, the REPLACE ?? is used to insert a new tuple into the database. The REPLACE statement only needs to store FOREIGN KEYS, the creation timestamp, and any other fields that should not be able to be changed in the future.
- If a PROJECT_ID was passed, then an UPDATE statement is performed to update any modifiable fields.
- Finally, the PROJECT_ID is stored into the LAST_INSERT_ID so that the caller can be confident of receiving a valid ID from LAST_INSERT_ID.
Save
DROP PROCEDURE Projects_Save;
DELIMITER //
CREATE PROCEDURE Projects_Save
(
$Sid TEXT,
$apikey TEXT,
$ORG_ID INT,
$PROJECT_ID INT,
$name TEXT,
$value TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ORG_ID, @email, @USER, @GROUP, @idtype, @role );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Projects_Save';
ELSEIF NOT( @role LIKE '%SAVE%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Projects_Save';
ELSE
CASE $name
WHEN 'project_name' THEN UPDATE projects SET project_name = $value WHERE ORG_ID = $ORG_ID AND PROJECT_ID = $PROJECT_ID AND project_deleted = 0;
WHEN 'project_code' THEN UPDATE projects SET project_code = $value WHERE ORG_ID = $ORG_ID AND PROJECT_ID = $PROJECT_ID AND project_deleted = 0;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CASE_OPTION IN Projects_Save';
END CASE;
END IF;
END
//
DELIMITER ;
Notes
Delete
DROP PROCEDURE Projects_Delete;
DELIMITER //
CREATE PROCEDURE Projects_Delete
(
$Sid TEXT,
$apikey TEXT,
$ORG_ID INT,
$PROJECT_ID INT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_Apikey_For_Org( $Sid, $apikey, $ORG_ID, @email, @USER, @GROUP, @idtype, @role );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( @idtype LIKE '%USER%' OR @idtype LIKE '%ADMIN%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Projects_Delete';
ELSEIF NOT( @role LIKE '%DELETE%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_ROLE IN Projects_Delete';
ELSE
UPDATE projects
SET project_deleted = NOW()
WHERE ORG_ID = $ORG_ID
AND PROJECT_ID = $PROJECT_ID
AND project_deleted = 0;
END IF;
END
//
DELIMITER ;
Notes
Test stub
DROP PROCEDURE CheckLimitOffset;
DELIMITER //
CREATE PROCEDURE CheckLimitOffset
(
INOUT
$limit INT,
INOUT
$offset INT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
IF 0 = $limit OR ISNULL( $limit ) THEN
SET $limit = 4294967295;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Authorise_Sessionid_Or_Apikey;
DELIMITER //
CREATE PROCEDURE Base_Users_Authorise_Sessionid_Or_Apikey
(
$Sid TEXT,
$apikey TEXT,
INOUT
$email TEXT,
INOUT
$USER INT,
INOUT
$GROUP_ID INT,
INOUT
$idtype TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
SET $email = "example";
SET $USER = 1;
SET $GROUP_ID = 1;
SET $idtype = "USER";
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Authorise_Sessionid_Or_APIKey;
DELIMITER //
CREATE PROCEDURE Base_Users_Authorise_Sessionid_Or_APIKey
(
$Sid CHAR(64),
$apikey CHAR(64),
OUT $email TEXT,
OUT $USER INT,
OUT $idtype TEXT
)
SQL SECURITY INVOKER
BEGIN
SET $email = "";
SET $USER = 0;
SET $idtype = "";
IF "" != $Sid THEN
CALL Base_Users_Authorise_Sessionid( $Sid, $email, $USER, $idtype );
ELSE
SELECT
email,
USER_OWNER,
type
INTO
$email,
$USER,
$idtype
FROM base_apikeys
LEFT JOIN view_base_users ON (USER_OWNER=USER)
WHERE apikey=$apikey;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Authorise_Sessionid_Or_APIKey_For_Org;
DELIMITER //
CREATE PROCEDURE Base_Users_Authorise_Sessionid_Or_APIKey_For_Org
(
$Sid CHAR(64),
$apikey CHAR(64),
INOUT
$ORG_ID INT,
OUT $email TEXT,
OUT $USER INT,
OUT $idtype TEXT,
OUT $org_user_role TEXT
)
SQL SECURITY INVOKER
BEGIN
SET $org_user_role = "";
CALL Base_Users_Authorise_Sessionid_Or_Apikey( $Sid, $apikey, $email, $USER, $idtype );
IF "ADMIN" = $idtype THEN
SET $org_user_role = "QUERY.REPLACE.SAVE.DELETE";
ELSE
SELECT org_user_role INTO $org_user_role
FROM base_organisations_users
WHERE ORG_ID = $ORG_ID
AND ORG_USER_ID = $USER;
IF "" = $org_user_role THEN
SET $ORG_ID = 0;
END IF;
END IF;
END
//
DELIMITER ;
Example Usage
CALL Projects_Replace( '', '', 1, 0, 'Live', 'Example Project', 'EXP' ); CALL Projects_Replace( '', '', 1, 0, 'Live', 'Example Project 2', 'EXP' ); CALL Projects_Replace( '', '', 1, 0, 'Live', 'Example Project 3', 'EXP' ); CALL Projects ( '', '', 0, 'Example Project', 0, 0, '' ); CALL Projects_Save ( '', '', 1, 'project_name', 'Example Project 1' ); CALL Projects ( '', '', 0, 'Example Project', 0, 0, '' ); CALL Projects ( '', '', 0, '', 0, 0, '' );