SET SQL DIALECT 3;

/*
CREATE DATABASE 'y:\ib.gdb\TEST1.FDB'
 USER 'sysdba' PASSWORD 'masterkey'  DEFAULT CHARACTER SET NONE;
*/



CREATE DOMAIN DTEST AS VARCHAR(100);

CREATE DOMAIN DTEST200 AS VARCHAR(200);

CREATE TABLE TEST(ID INTEGER NOT NULL,
IBE$TEST INTEGER,
TEST DTEST200,
TEST2 DTEST,
CLC_NUME COMPUTED BY ((SELECT NUME FROM TEST2 WHERE TEST.ID=TEST2.ID)),
CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM TEST2 WHERE TEST2.ID=TEST.ID)));

CREATE TABLE TEST2(ID INTEGER NOT NULL,
NUME VARCHAR(20),
VALOARE NUMERIC(18,4));

CREATE TABLE TEST3(ID INTEGER NOT NULL,
CLC_VALOARE COMPUTED BY ((SELECT VALOARE FROM GET_VALOARE(TEST3.ID))));

/* PK_TEST */
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);

/* PK_TEST2 */
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID);

/* PK_TEST3 */
ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);

/* RDB$INDEX_0 */
ALTER TABLE RDB$RELATIONS ADD CONSTRAINT RDB$INDEX_0 UNIQUE (RDB$RELATION_NAME);

/* RDB$INDEX_11 */
ALTER TABLE RDB$GENERATORS ADD CONSTRAINT RDB$INDEX_11 UNIQUE (RDB$GENERATOR_NAME);

/* RDB$INDEX_12 */
ALTER TABLE RDB$RELATION_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_12 UNIQUE (RDB$CONSTRAINT_NAME);

/* RDB$INDEX_13 */
ALTER TABLE RDB$REF_CONSTRAINTS ADD CONSTRAINT RDB$INDEX_13 UNIQUE (RDB$CONSTRAINT_NAME);

/* RDB$INDEX_15 */
ALTER TABLE RDB$RELATION_FIELDS ADD CONSTRAINT RDB$INDEX_15 UNIQUE (RDB$FIELD_NAME, RDB$RELATION_NAME);

/* RDB$INDEX_17 */
ALTER TABLE RDB$FILTERS ADD CONSTRAINT RDB$INDEX_17 UNIQUE (RDB$INPUT_SUB_TYPE, RDB$OUTPUT_SUB_TYPE);

/* RDB$INDEX_18 */
ALTER TABLE RDB$PROCEDURE_PARAMETERS ADD CONSTRAINT RDB$INDEX_18 UNIQUE (RDB$PACKAGE_NAME, RDB$PROCEDURE_NAME, RDB$PARAMETER_NAME);

/* RDB$INDEX_19 */
ALTER TABLE RDB$CHARACTER_SETS ADD CONSTRAINT RDB$INDEX_19 UNIQUE (RDB$CHARACTER_SET_NAME);

/* RDB$INDEX_2 */
ALTER TABLE RDB$FIELDS ADD CONSTRAINT RDB$INDEX_2 UNIQUE (RDB$FIELD_NAME);

/* RDB$INDEX_20 */
ALTER TABLE RDB$COLLATIONS ADD CONSTRAINT RDB$INDEX_20 UNIQUE (RDB$COLLATION_NAME);

/* RDB$INDEX_21 */
ALTER TABLE RDB$PROCEDURES ADD CONSTRAINT RDB$INDEX_21 UNIQUE (RDB$PACKAGE_NAME, RDB$PROCEDURE_NAME);

/* RDB$INDEX_22 */
ALTER TABLE RDB$PROCEDURES ADD CONSTRAINT RDB$INDEX_22 UNIQUE (RDB$PROCEDURE_ID);

/* RDB$INDEX_23 */
ALTER TABLE RDB$EXCEPTIONS ADD CONSTRAINT RDB$INDEX_23 UNIQUE (RDB$EXCEPTION_NAME);

/* RDB$INDEX_24 */
ALTER TABLE RDB$EXCEPTIONS ADD CONSTRAINT RDB$INDEX_24 UNIQUE (RDB$EXCEPTION_NUMBER);

/* RDB$INDEX_25 */
ALTER TABLE RDB$CHARACTER_SETS ADD CONSTRAINT RDB$INDEX_25 UNIQUE (RDB$CHARACTER_SET_ID);

/* RDB$INDEX_26 */
ALTER TABLE RDB$COLLATIONS ADD CONSTRAINT RDB$INDEX_26 UNIQUE (RDB$COLLATION_ID, RDB$CHARACTER_SET_ID);

/* RDB$INDEX_32 */
ALTER TABLE RDB$TRANSACTIONS ADD CONSTRAINT RDB$INDEX_32 UNIQUE (RDB$TRANSACTION_ID);

/* RDB$INDEX_39 */
ALTER TABLE RDB$ROLES ADD CONSTRAINT RDB$INDEX_39 UNIQUE (RDB$ROLE_NAME);

/* RDB$INDEX_44 */
ALTER TABLE RDB$BACKUP_HISTORY ADD CONSTRAINT RDB$INDEX_44 UNIQUE (RDB$BACKUP_LEVEL, RDB$BACKUP_ID);

/* RDB$INDEX_45 */
ALTER TABLE RDB$FILTERS ADD CONSTRAINT RDB$INDEX_45 UNIQUE (RDB$FUNCTION_NAME);

/* RDB$INDEX_46 */
ALTER TABLE RDB$GENERATORS ADD CONSTRAINT RDB$INDEX_46 UNIQUE (RDB$GENERATOR_ID);

/* RDB$INDEX_47 */
ALTER TABLE RDB$PACKAGES ADD CONSTRAINT RDB$INDEX_47 UNIQUE (RDB$PACKAGE_NAME);

/* RDB$INDEX_5 */
ALTER TABLE RDB$INDICES ADD CONSTRAINT RDB$INDEX_5 UNIQUE (RDB$INDEX_NAME);

/* RDB$INDEX_53 */
ALTER TABLE RDB$FUNCTIONS ADD CONSTRAINT RDB$INDEX_53 UNIQUE (RDB$FUNCTION_ID);

/* RDB$INDEX_7 */
ALTER TABLE RDB$SECURITY_CLASSES ADD CONSTRAINT RDB$INDEX_7 UNIQUE (RDB$SECURITY_CLASS);

/* RDB$INDEX_8 */
ALTER TABLE RDB$TRIGGERS ADD CONSTRAINT RDB$INDEX_8 UNIQUE (RDB$TRIGGER_NAME);

/* RDB$INDEX_9 */
ALTER TABLE RDB$FUNCTIONS ADD CONSTRAINT RDB$INDEX_9 UNIQUE (RDB$PACKAGE_NAME, RDB$FUNCTION_NAME);



SET TERM ^ ;

CREATE PROCEDURE GET_VALOARE(ID INTEGER)
 RETURNS(VALOARE VARCHAR(10))
 AS
BEGIN
  FOR SELECT T.CLC_VALOARE
      FROM TEST T
      WHERE T.ID = :ID
      INTO :VALOARE
  DO
    SUSPEND;
END
^

CREATE PROCEDURE TEST_DATE_ADD(A DATE)
 RETURNS(B DATE)
 AS
BEGIN
  /*  */
  B = DATEADD(3 MONTH TO :A);
  SUSPEND;
END
^

CREATE PROCEDURE TEST_POSITION(A VARCHAR(100))
 RETURNS(B INTEGER)
 AS
BEGIN
  /*  */
  B = POSITION(';', :A);
  SUSPEND;
END
^

SET TERM ; ^



GRANT EXECUTE ON PROCEDURE GET_VALOARE TO ION;
GRANT EXECUTE ON PROCEDURE GET_VALOARE TO REPL;
GRANT RDB$ADMIN TO ION;
GRANT SELECT ON MON$ATTACHMENTS TO PUBLIC;
GRANT SELECT ON MON$CALL_STACK TO PUBLIC;
GRANT SELECT ON MON$CONTEXT_VARIABLES TO PUBLIC;
GRANT SELECT ON MON$DATABASE TO PUBLIC;
GRANT SELECT ON MON$IO_STATS TO PUBLIC;
GRANT SELECT ON MON$MEMORY_USAGE TO PUBLIC;
GRANT SELECT ON MON$RECORD_STATS TO PUBLIC;
GRANT SELECT ON MON$STATEMENTS TO PUBLIC;
GRANT SELECT ON MON$TABLE_STATS TO PUBLIC;
GRANT SELECT ON MON$TRANSACTIONS TO PUBLIC;
GRANT SELECT ON RDB$AUTH_MAPPING TO PUBLIC;
GRANT SELECT ON RDB$BACKUP_HISTORY TO PUBLIC;
GRANT SELECT ON RDB$CHARACTER_SETS TO PUBLIC;
GRANT SELECT ON RDB$CHECK_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$COLLATIONS TO PUBLIC;
GRANT SELECT ON RDB$DATABASE TO PUBLIC;
GRANT SELECT ON RDB$DB_CREATORS TO PUBLIC;
GRANT SELECT ON RDB$DEPENDENCIES TO PUBLIC;
GRANT SELECT ON RDB$EXCEPTIONS TO PUBLIC;
GRANT SELECT ON RDB$FIELD_DIMENSIONS TO PUBLIC;
GRANT SELECT ON RDB$FIELDS TO PUBLIC;
GRANT SELECT ON RDB$FILES TO PUBLIC;
GRANT SELECT ON RDB$FILTERS TO PUBLIC;
GRANT SELECT ON RDB$FORMATS TO PUBLIC;
GRANT SELECT ON RDB$FUNCTION_ARGUMENTS TO PUBLIC;
GRANT SELECT ON RDB$FUNCTIONS TO PUBLIC;
GRANT SELECT ON RDB$GENERATORS TO PUBLIC;
GRANT SELECT ON RDB$INDEX_SEGMENTS TO PUBLIC;
GRANT SELECT ON RDB$INDICES TO PUBLIC;
GRANT SELECT ON RDB$LOG_FILES TO PUBLIC;
GRANT SELECT ON RDB$PACKAGES TO PUBLIC;
GRANT SELECT ON RDB$PAGES TO PUBLIC;
GRANT SELECT ON RDB$PROCEDURE_PARAMETERS TO PUBLIC;
GRANT SELECT ON RDB$PROCEDURES TO PUBLIC;
GRANT SELECT ON RDB$REF_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_FIELDS TO PUBLIC;
GRANT SELECT ON RDB$RELATIONS TO PUBLIC;
GRANT SELECT ON RDB$ROLES TO PUBLIC;
GRANT SELECT ON RDB$SECURITY_CLASSES TO PUBLIC;
GRANT SELECT ON RDB$TRANSACTIONS TO PUBLIC;
GRANT SELECT ON RDB$TRIGGER_MESSAGES TO PUBLIC;
GRANT SELECT ON RDB$TRIGGERS TO PUBLIC;
GRANT SELECT ON RDB$TYPES TO PUBLIC;
GRANT SELECT ON RDB$USER_PRIVILEGES TO PUBLIC;
GRANT SELECT ON RDB$VIEW_RELATIONS TO PUBLIC;
GRANT SELECT ON SEC$DB_CREATORS TO PUBLIC;
GRANT SELECT ON SEC$GLOBAL_AUTH_MAPPING TO PUBLIC;
GRANT SELECT ON SEC$USER_ATTRIBUTES TO PUBLIC;
GRANT SELECT ON SEC$USERS TO PUBLIC;