<group name="root">
<group name="Performing Administrative Tasks">

<group name="Security">
  <snippet name="Create Role"><![CDATA[CREATE ROLE ROLE01;
  
GRANT ROLE01 TO USER01;]]></snippet>
  <snippet name="Drop Role"><![CDATA[DROP ROLE ROLE01;]]></snippet>
  <snippet name="Grant"><![CDATA[GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE TABLE01 TO ROLE01;]]></snippet>
  <snippet name="Revoke"><![CDATA[REVOKE SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE TABLE01 FROM ROLE01;]]></snippet>
</group>
</group>

<group name="Show Statements">
  <snippet name="Show tables"><![CDATA[SELECT 
  R.RDB$RELATION_NAME AS name, 
  R.RDB$OWNER_NAME AS owner, 
  D.RDB$DESCRIPTION AS description
FROM RDB$RELATIONS R 
LEFT OUTER JOIN RDB$RELATIONS D ON 
  D.RDB$RELATION_NAME = R.RDB$RELATION_NAME 
WHERE
  (R.RDB$VIEW_SOURCE is NULL) AND
  (R.RDB$SYSTEM_FLAG = 0)]]></snippet>
  <snippet name="Show procedures"><![CDATA[SELECT 
  P.RDB$PROCEDURE_NAME AS name, 
  P.RDB$OWNER_NAME AS owner, 
  P.RDB$DESCRIPTION AS description, 
  P.RDB$PROCEDURE_SOURCE AS proc_source 
FROM RDB$PROCEDURES P 
WHERE
  ((P.RDB$SYSTEM_FLAG = 0) OR
   (P.RDB$SYSTEM_FLAG IS NULL))]]></snippet>  
  <snippet name="Show roles"><![CDATA[SELECT 
  R.RDB$ROLE_NAME AS name, 
  R.RDB$OWNER_NAME AS owner 
FROM RDB$ROLES R;]]></snippet>
</group>


<group name="SQL Syntax">
<group name="DML Statements">
  <snippet name="Delete"><![CDATA[DELETE 
  FROM TABLE1
  WHERE COLUMN1 = VALUE1;]]></snippet>
  <snippet name="Insert"><![CDATA[INSERT INTO TABLE1(
  COLUMN1, COLUMN2)
  VALUES (VALUE1, VALUE2);]]></snippet>
  <snippet name="Insert with Select Clause"><![CDATA[INSERT 
  INTO TABLE1(COLUMN11, COLUMN12)
  SELECT COLUMN21, COLUMN22 FROM TABLE2;]]></snippet>
  <snippet name="Truncate"><![CDATA[DELETE FROM TABLE1;]]></snippet>
  <snippet name="Update"><![CDATA[UPDATE TABLE1
  SET COLUMN1 = VALUE1
  WHERE COLUMN2 = VALUE2;]]></snippet>
</group>

<group name="Functions">

<group name="Aggregate Functions">
  <snippet name="Avg"><![CDATA[AVG(DISTINCT 1)]]></snippet>
  <snippet name="Count"><![CDATA[COUNT(*)]]></snippet>
  <snippet name="Sum"><![CDATA[SUM(1+1)]]></snippet>
</group>
<group name="Cast Functions">
  <snippet name="Cast to date"><![CDATA[CAST('2000-01-01' AS DATE)]]></snippet>
  <snippet name="Cast to timestamp"><![CDATA[CAST('2000-01-01' AS TIMESTAMP)]]></snippet>
</group>
<group name="Date and Time Functions">
  <snippet name="Date_add"><![CDATA[DATEADD(DAY, 31, CAST('2007-01-02' as DATE))]]></snippet>
  <snippet name="CURRENT_DATE"><![CDATA[CURRENT_DATE]]></snippet>
  <snippet name="DateDiff"><![CDATA[DATEDIFF(MONTH, CURRENT_DATE, DATE '2007-01-02')]]></snippet>
</group>
<group name="Numeric Functions">
  <snippet name="Abs"><![CDATA[ABS(1)]]></snippet>
  <snippet name="Cos"><![CDATA[COS(1)]]></snippet>
  <snippet name="Log"><![CDATA[LOG(1, 1)]]></snippet>
  <snippet name="Power"><![CDATA[POWER(1, 1)]]></snippet>
</group>
<group name="String Functions">
  <snippet name="ASCII_CHAR"><![CDATA[ASCII_VAL('a')]]></snippet>
  <snippet name="ASCII_CHAR"><![CDATA[ASCII_CHAR(35)]]></snippet>
  <snippet name="Char_length"><![CDATA[CHAR_LENGTH('xxxxx')]]></snippet>
  <snippet name="Lower"><![CDATA[LOWER('XXXXX')]]></snippet>
  <snippet name="Substring"><![CDATA[SUBSTRING('xxxxx', 1, 2)]]></snippet>
  <snippet name="Upper"><![CDATA[UPPER('xxxxx')]]></snippet>

</group>

</group>

<group name="SQL Queries">
  <snippet name="Select"><![CDATA[SELECT
  * 
FROM
  TEST.TABLE1;]]></snippet>
  <snippet name="Select with Aliases"><![CDATA[SELECT
  T1.COLUMN1, T2.COLUMN2 
FROM
  TABLE1 T1, TABLE2 T2
WHERE
  T1.COLUMN3 = T2.COLUMN4;]]></snippet>
  <snippet name="Group"><![CDATA[SELECT
  COLUMN1, COUNT(COLUMN2)
FROM
  TABLE1
GROUP BY
  COLUMN1;]]></snippet>
  <snippet name="Join"><![CDATA[SELECT TABLE1.COLUMN1, TABLE2.COLUMN2
 FROM TABLE1 JOIN TABLE2
 ON TABLE1.JOIN_COLUMN1 = TABLE2.JOIN_COLUMN2;]]></snippet>
  <snippet name="Left Join"><![CDATA[SELECT TABLE1.COLUMN1, TABLE2.COLUMN2
 FROM TABLE1 LEFT OUTER JOIN TABLE2
 ON TABLE1.JOIN_COLUMN1 = TABLE2.JOIN_COLUMN2;]]></snippet>
  <snippet name="Like"><![CDATA[SELECT 
  *
FROM
  TABLE1
WHERE
  COLUMN1 LIKE '%a%';]]></snippet>
  <snippet name="Right Join"><![CDATA[SELECT TABLE1.COLUMN1, TABLE2.COLUMN2
 FROM TABLE1 RIGHT OUTER JOIN TABLE2
 ON TABLE1.JOIN_COLUMN1 = TABLE2.JOIN_COLUMN2;]]></snippet>
  <snippet name="Union"><![CDATA[SELECT
  COLUMN1
FROM
  TABLE1
UNION
  SELECT
    COLUMN2
  FROM
    TABLE2;]]></snippet>
  <snippet name="Where"><![CDATA[SELECT
  *
FROM
  TABLE1
WHERE
  COLUMN1 > 10
ORDER BY COLUMN1;]]></snippet>


</group>

</group>


<group name="Stored Procedure Syntax">

<group name="Control Structures">
  <snippet name="Begin-end"><![CDATA[BEGIN
  
END;
]]></snippet>
  <snippet name="Case"><![CDATA[CASE A
  WHEN  THEN  
  ELSE 
END;
]]></snippet>
  <snippet name="If-Then"><![CDATA[IF true THEN
    ;
]]></snippet>
  <snippet name="If-Then-Else"><![CDATA[IF true THEN 
  ;
ELSE
  ;
]]></snippet>
  <snippet name="While"><![CDATA[WHILE true DO 
  ;]]></snippet>
</group>
<group name="Cursors">
  <snippet name="Close Cursor"><![CDATA[CLOSE CURSOR1;]]></snippet>
  <snippet name="Declare Cursor"><![CDATA[DECLARE CURSOR1 CURSOR FOR
  (SELECT TABLE1.COLUMN1 
FROM
  TEST.TABLE1);]]></snippet>
  <snippet name="Fetch Cursor"><![CDATA[FETCH CURSOR1 
  INTO :VARIABLE1]]></snippet>
  <snippet name="Open Cursor"><![CDATA[OPEN CURSOR1;]]></snippet>
</group>

</group>

<group name="Working With Schema Objects">
<group name="Stored Procedures">

  <snippet name="Alter Procedure"><![CDATA[SET TERM ^ ;

ALTER PROCEDURE PROCEDURE01 (
 PARAMETER01 INTEGER)
AS 
BEGIN
  /* New procedure text */
END^

SET TERM ; ^

COMMENT ON PROCEDURE PROCEDURE01
  IS 'Comment text';]]></snippet>
  <snippet name="Drop Procedure"><![CDATA[DROP PROCEDURE PROCEDURE01;]]></snippet>
  <snippet name="Create Procedure"><![CDATA[SET TERM ^ ;

RECREATE PROCEDURE PROCEDURE01 (
 PARAMETER01 INTEGER)
AS 
BEGIN
  /* Procedure text */
END^

SET TERM ; ^]]></snippet>
</group>
<group name="Tables">
  <snippet name="Alter Table"><![CDATA[ALTER TABLE TABLE01
  ADD FIELD03 INTEGER;]]></snippet>
  <snippet name="Create Table"><![CDATA[CREATE TABLE TABLE01 (
  FIELD01  INTEGER NOT NULL PRIMARY KEY,
  FIELD02  INTEGER
);]]></snippet>
  <snippet name="Drop Table"><![CDATA[DROP TABLE TABLE01;]]></snippet>
  <snippet name="Add autoincrement"><![CDATA[
CREATE GENERATOR GEN_TABLE01_ID;

SET TERM ^ ;

CREATE TRIGGER TABLE01_BI FOR TABLE01
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.FIELD01 IS NULL) THEN
  NEW.FIELD01 = GEN_ID(GEN_TABLE01_ID,1);
END^

SET TERM ; ^]]></snippet>
</group>
<group name="Triggers">
  <snippet name="Create Trigger"><![CDATA[SET TERM ^ ;

CREATE TRIGGER TRIGGER01 FOR TABLE01
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
  /* Trigger text */
END^

SET TERM ; ^
]]></snippet>
  <snippet name="Drop Trigger"><![CDATA[DROP TRIGGER TRIGGER01;]]></snippet>
</group>
<group name="Views">
  <snippet name="Alter View"><![CDATA[DROP VIEW VIEW01;

CREATE VIEW VIEW01
(
  FIELD01,
  FIELD02
)
AS
SELECT 
  FIELD01,
  FIELD02
FROM TABLE01
WHERE FIELD01 = 1;]]></snippet>
  <snippet name="Create View"><![CDATA[CREATE VIEW VIEW01
AS
SELECT 
  FIELD01,
  FIELD02
FROM TABLE01]]></snippet>
  <snippet name="Drop View"><![CDATA[DROP VIEW VIEW01;]]></snippet>
</group>
</group>
</group>