--
-- FFIGEN SQL Script Dump Created: 
-- FFIGEN Utility 
--
--
-- Drop table statements for FFIGEN tables
--

DROP TABLE FFIGEN_FUNCTIONARGS;
DROP TABLE FFIGEN_FUNCTION;
DROP TABLE FFIGEN_LIBRARY;
DROP TABLE FFIGEN_TYPEDEFS;
DROP TABLE FFIGEN_TYPESETS;
DROP TABLE FFIGEN_PREFERENCES;
DROP SEQUENCE FFIGEN_PACKAGE_SEQ;
DROP SEQUENCE FFIGEN_FUNCTION_SEQ;
DROP SEQUENCE FFIGEN_DATATYPE_SEQ;
DROP SEQUENCE FFIGEN_TYPESET_SEQ;

--
-- Create table & Comment statements for FFIGEN tables
--

CREATE SEQUENCE FFIGEN_PACKAGE_SEQ INCREMENT BY 1 START WITH 2 MINVALUE 2 NOCYCLE NOCACHE;
CREATE SEQUENCE FFIGEN_FUNCTION_SEQ INCREMENT BY 1 START WITH 2 MINVALUE 2 NOCYCLE NOCACHE;
CREATE SEQUENCE FFIGEN_DATATYPE_SEQ INCREMENT BY 1 START WITH 37 MINVALUE 37 NOCYCLE NOCACHE;
CREATE SEQUENCE FFIGEN_TYPESET_SEQ INCREMENT BY 1 START WITH 4 MINVALUE 4 NOCYCLE NOCACHE;
CREATE TABLE FFIGEN_TYPEDEFS ( DATATYPE_ID  NUMBER NOT NULL, SET_ID  NUMBER NOT NULL ,TYPE_NAME  VARCHAR2(30) NOT NULL,FORCE_POINTER VARCHAR2(1) NOT NULL, BASETYPE NUMBER, FFI_CONSTANT  VARCHAR2(30) NOT NULL ,PLSQL_DATATYPE  VARCHAR2(30) NOT NULL);
COMMENT ON TABLE FFIGEN_TYPEDEFS IS 'FFIGEN_TYPEDEFS contains the individual datatypes in each set of types. Each type must map back to a PL/SQL datatype';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.SET_ID IS 'The Type Set that this datatype is in ';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.TYPE_NAME IS 'The description for the datatype as will be displayed in List boxes';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.FORCE_POINTER IS 'Is this datatype itself a Pointer (Y/N)';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.FFI_CONSTANT IS 'The ORA_FFI datatype that this datatype maps to';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.PLSQL_DATATYPE IS 'The PL/SQL datatype that this datatype maps to';
COMMENT ON COLUMN FFIGEN_TYPEDEFS.DATATYPE_ID IS 'Unique Identifier for each datatype generated from FFIGEN_DATATYPE_SEQ';
CREATE TABLE FFIGEN_LIBRARY (PACKAGE_NAME  VARCHAR2(30) NOT NULL ,NAME  VARCHAR2(30) NOT NULL ,PATH  VARCHAR2(300) ,DESCRIPTION  VARCHAR2(2000) ,PACKAGE_ID  NUMBER NOT NULL );
COMMENT ON TABLE FFIGEN_LIBRARY IS 'FFIGEN_LIBRARY defines all the Shared Libraries (.DLL and .SO) ';
COMMENT ON COLUMN FFIGEN_LIBRARY.PACKAGE_NAME IS 'Name that will be used to identify the PL/SQL Code package';
COMMENT ON COLUMN FFIGEN_LIBRARY.NAME IS 'Actual name of the Sharable file (e.g. .DLL or .SO file)';
COMMENT ON COLUMN FFIGEN_LIBRARY.PATH IS 'Location of the sharable Library ';
COMMENT ON COLUMN FFIGEN_LIBRARY.DESCRIPTION IS 'Free Text description';
COMMENT ON COLUMN FFIGEN_LIBRARY.PACKAGE_ID IS 'Unique id for each Library';
CREATE TABLE FFIGEN_FUNCTIONARGS (FUNCTION_ID  NUMBER NOT NULL ,POSITION  NUMBER NOT NULL ,ARGUMENT_NAME  VARCHAR2(30) NOT NULL ,DATATYPE  NUMBER NOT NULL ,POINTER  VARCHAR2(1) NOT NULL ,READ_ONLY  VARCHAR2(1) NOT NULL ,PREALLOC_LENGTH  NUMBER, DEFAULT_VALUE VARCHAR2(2000),  INTERNALISE VARCHAR2(1) );
COMMENT ON TABLE FFIGEN_FUNCTIONARGS IS 'FFIGEN_FUNCTIONARGS defines all the arguments to a particular function in a Shared Library';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.FUNCTION_ID IS 'Link back to the function';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.POSITION IS 'Position of this Arg in the argument list for the function';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.ARGUMENT_NAME IS 'Name of this argument in the 3GL specification';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.DATATYPE IS 'Type for this Arg';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.POINTER IS 'Is this a Pointer to the specified datatype, either Y or N';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.READ_ONLY IS 'Should this be read-only, valid values Y and N';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.PREALLOC_LENGTH IS 'For VARCHAR2 datatypes how much space should be allocated';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.DEFAULT_VALUE IS 'Default value for this parameter to be used in generated PL/SQL declarations';
COMMENT ON COLUMN FFIGEN_FUNCTIONARGS.INTERNALISE IS 'Should this parameter be supressed from the public declaration of the package?';
CREATE TABLE FFIGEN_FUNCTION (PACKAGE_ID  NUMBER NOT NULL ,FUNCTION_NAME  VARCHAR2(30) NOT NULL  ,CALLTYPE  VARCHAR2(1) NOT NULL ,RETURNTYPE  NUMBER(30,0) NOT NULL ,RETURN_AS_POINTER  CHAR(1) NOT NULL ,FUNCTION_ID  NUMBER NOT NULL, FUNCTION_COMMENT VARCHAR2(2000) );
COMMENT ON TABLE FFIGEN_FUNCTION IS 'FFIGEN_FUNCTION defines each of the Functions within a Shared library';
COMMENT ON COLUMN FFIGEN_FUNCTION.PACKAGE_ID IS 'Links to the shared library that this function is in';
COMMENT ON COLUMN FFIGEN_FUNCTION.FUNCTION_NAME IS 'Name of this function in the 3GL code';
COMMENT ON COLUMN FFIGEN_FUNCTION.CALLTYPE IS 'C or Pascal Calling Standard Values C and P respectivly';
COMMENT ON COLUMN FFIGEN_FUNCTION.RETURNTYPE IS 'Datatype that the function returns';
COMMENT ON COLUMN FFIGEN_FUNCTION.RETURN_AS_POINTER IS 'Is the return value a Pointer to the Specified datatype?';
COMMENT ON COLUMN FFIGEN_FUNCTION.FUNCTION_ID IS 'The unique Id for this function, generated from FFIGEN_FUNCTION_SEQ';
COMMENT ON COLUMN FFIGEN_FUNCTION.FUNCTION_COMMENT IS 'Description of what the function does.';
CREATE TABLE FFIGEN_TYPESETS (SET_ID  NUMBER NOT NULL ,SET_DESCRIPTION  VARCHAR2(60) NOT NULL ,SET_ENABLED VARCHAR2(1) NOT NULL,SET_COMMENT  VARCHAR2(2000) );
COMMENT ON TABLE FFIGEN_TYPESETS IS 'FFIGEN_TYPESETS is a table which defines logical groups of datatypes, eg) Standard C datatypes or Windows types etc.    ';
COMMENT ON COLUMN FFIGEN_TYPESETS.SET_ID IS 'Unique numerical identifier for the Type Set';
COMMENT ON COLUMN FFIGEN_TYPESETS.SET_ENABLED IS 'Specifies that this set of datatypes should be made available in the Poplists in FFIGEN';
COMMENT ON COLUMN FFIGEN_TYPESETS.SET_DESCRIPTION IS 'Description of the set ';
COMMENT ON COLUMN FFIGEN_TYPESETS.SET_COMMENT IS 'Free text column to further qualify the description';
CREATE TABLE FFIGEN_PREFERENCES (USERID  VARCHAR2(30) NOT NULL ,PREFERENCE  NUMBER(6,0) NOT NULL ,VALUE  VARCHAR2(60) NOT NULL );
COMMENT ON TABLE FFIGEN_PREFERENCES IS 'FFIGEN_PREFERENCES holds information specific to the settings in the FFIGEN application';
ALTER TABLE FFIGEN_TYPEDEFS ADD CONSTRAINT FFI_TD_PK PRIMARY KEY   (DATATYPE_ID);
ALTER TABLE FFIGEN_LIBRARY ADD CONSTRAINT FFI_LIB_PK PRIMARY KEY   (PACKAGE_ID);
ALTER TABLE FFIGEN_FUNCTION ADD CONSTRAINT FFI_FN_PK PRIMARY KEY   (FUNCTION_ID);
ALTER TABLE FFIGEN_TYPESETS ADD CONSTRAINT FFI_TS_PK PRIMARY KEY   (SET_ID);
ALTER TABLE FFIGEN_FUNCTIONARGS ADD CONSTRAINT FFI_FA_CK_RO CHECK (read_only in ('Y','N')) ADD CONSTRAINT FFI_FA_CK_PTR CHECK (pointer in ('Y','N'));
ALTER TABLE FFIGEN_FUNCTION ADD CONSTRAINT FFIGEN_FN_CK_CALLTYPE CHECK ( calltype in ('C','P')) ADD CONSTRAINT FFIGEN_FN_CK_PTR CHECK (return_as_pointer in ('Y','N'));
ALTER TABLE FFIGEN_TYPEDEFS ADD CONSTRAINT FFI_TYPEDEFS_TYPESET_FK FOREIGN KEY   (SET_ID) REFERENCES FFIGEN_TYPESETS  (SET_ID);
ALTER TABLE FFIGEN_FUNCTIONARGS ADD CONSTRAINT FFI_FUNCTIONARG_FUNCTION_FK FOREIGN KEY (FUNCTION_ID) REFERENCES FFIGEN_FUNCTION  (FUNCTION_ID) ADD CONSTRAINT FFI_FUNCTIONARG_TYPEDEF_FK FOREIGN KEY   (DATATYPE) REFERENCES FFIGEN_TYPEDEFS  (DATATYPE_ID);
ALTER TABLE FFIGEN_FUNCTION  ADD CONSTRAINT FFI_FUNCTION_LIBRARY_FK FOREIGN KEY   (PACKAGE_ID) REFERENCES FFIGEN_LIBRARY  (PACKAGE_ID) ADD CONSTRAINT FFI_FUNCTION_TYPEDEF_FK FOREIGN KEY (RETURNTYPE) REFERENCES FFIGEN_TYPEDEFS (DATATYPE_ID);
ALTER TABLE FFIGEN_PREFERENCES ADD CONSTRAINT FFI_PRF_PK PRIMARY KEY (USERID  ,PREFERENCE);
ALTER TABLE FFIGEN_TYPESETS ADD CONSTRAINT FFI_TS_CHK_EN CHECK (set_enabled in ('Y','N'));

--
-- Seed data insert statements for FFIGEN tables
--

insert into ffigen_typesets values (0,'C Native Datatypes','Y','This set of datatypes represents the basic C datatypes that ORA_FFI can work with.  All other datatype sets have to resolve to this set of datatypes (or pointers to them)');
insert into ffigen_typesets values (1,'Common MS Windows Datatypes','Y', 'This set of datatypes represents the datatypes commonly found in MS/Windows programs and maps them to their Native C equivalents');
insert into ffigen_typesets values (2,'User Defined Datatypes','N', 'For your own use');
insert into ffigen_typedefs values (1,0,'void','N',NULL,'C_DVOID','ORA_FFI.POINTERTYPE');
insert into ffigen_typedefs values (2,0,'char','N',NULL,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (3,0,'short int','N',NULL,'C_SHORT','PLS_INTEGER');
insert into ffigen_typedefs values (4,0,'int','N',NULL,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (5,0,'long int','N',NULL,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (6,0,'float','N',NULL,'C_FLOAT','NUMBER');
insert into ffigen_typedefs values (7,0,'double','N',NULL,'C_DOUBLE','NUMBER');
insert into ffigen_typedefs values (8,1,'ULONG','N',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (9,1,'USHORT','N',3,'C_SHORT','PLS_INTEGER');
insert into ffigen_typedefs values (10,1,'DWORD','N',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (11,1,'BOOL','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (12,1,'BYTE','N',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (13,1,'WORD','N',3,'C_SHORT','PLS_INTEGER');
insert into ffigen_typedefs values (14,1,'FLOAT','N',6,'C_FLOAT','NUMBER');
insert into ffigen_typedefs values (15,1,'INT','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (16,1,'UINT','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (17,1,'WPARAM','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (18,1,'LPARAM','N',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (19,1,'LRESULT','N',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (20,1,'HFILE','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (21,1,'HFILE','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (22,1,'HINSTANCE','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (23,1,'HMODULE','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (24,1,'HWND','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_typedefs values (25,1,'LPSTR','Y',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (26,1,'LPCSTR','Y',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (27,1,'PCSTR','Y',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (28,1,'CHAR','N',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (29,1,'PSZ','Y',2,'C_CHAR','VARCHAR2');
insert into ffigen_typedefs values (30,1,'PWORD','Y',3,'C_SHORT','PLS_INTEGER');
insert into ffigen_typedefs values (31,1,'LPWORD','Y',3,'C_SHORT','PLS_INTEGER');
insert into ffigen_typedefs values (32,1,'LPLONG','Y',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (33,1,'LONG','N',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (34,1,'PDWORD','Y',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (35,1,'LPDWORD','Y',5,'C_LONG','PLS_INTEGER');
insert into ffigen_typedefs values (36,1,'HDC','N',4,'C_INT','PLS_INTEGER');
insert into ffigen_library (package_id,package_name,name,path,description) values (FFIGEN_PACKAGE_SEQ.NEXTVAL,'kernel32','kernel32.dll','','Some sample functions from the Windows API.');
insert into ffigen_function (function_id,package_id,function_name,calltype,returntype,return_as_pointer,function_comment) values (FFIGEN_FUNCTION_SEQ.NEXTVAL,FFIGEN_PACKAGE_SEQ.CURRVAL,'GetCurrentDirectoryA','C',10,'N','Sets the ''Working Directory'' for the current application.'||chr(10)||''||chr(10)||'Usage:'||chr(10)||'GetCurrentDirectoryA gets the Working directory of the current process.'||chr(10)||'The Function returns a string into the the specified buffer (WorkingDirectory) which must be pre-defined to the length indictated in the BufferLength variable.'||chr(10)||'The return code is the number of bytes written into the buffer.  Thus a Zero return code means that the call was not sucessful.'||chr(10)||''||chr(10)||'Usage:'||chr(10)||''||chr(10)||'declare'||chr(10)||'  BufferLength	pls_integer := 256;'||chr(10)||'  DirBuffer		varchar2(256);'||chr(10)||'  ReturnCode	pls_integer;'||chr(10)||'begin '||chr(10)||'   -- Get the Working Directory'||chr(10)||'   ReturnCode :=  kernel32.GetCurrentDirectoryA(BufferLength,DirBuffer);'||chr(10)||'   if Returncode = 0 then '||chr(10)||'      Message(''Unable to get Working Directory'');'||chr(10)||'   else'||chr(10)||'      Message(''Working Directory = ''||DirBuffer;'||chr(10)||'   end if;'||chr(10)||'end;');
insert into ffigen_functionargs (function_id,position,argument_name,datatype,pointer,read_only,prealloc_length,default_value,internalise) values (FFIGEN_FUNCTION_SEQ.CURRVAL,1,'BufferLength',10,'N','Y',NULL,'','');
insert into ffigen_functionargs (function_id,position,argument_name,datatype,pointer,read_only,prealloc_length,default_value,internalise) values (FFIGEN_FUNCTION_SEQ.CURRVAL,2,'WorkingDirectory',25,'Y','N',256,'','');
insert into ffigen_function (function_id,package_id,function_name,calltype,returntype,return_as_pointer,function_comment) values (FFIGEN_FUNCTION_SEQ.NEXTVAL,FFIGEN_PACKAGE_SEQ.CURRVAL,'SetCurrentDirectoryA','C',10,'N','Sets the ''Working Directory'' for the current application.'||chr(10)||'The Return Code from the function is a C boolean where TRUE=1 and FALSE=0.'||chr(10)||''||chr(10)||'Usage:'||chr(10)||''||chr(10)||'declare'||chr(10)||'  ReturnCode	pls_integer;'||chr(10)||'begin '||chr(10)||'   -- Set the Working Dir to \temp'||chr(10)||'   ReturnCode :=  kernel32.SetCurrentDirectoryA(''c:\temp'');'||chr(10)||'   if Returncode <>1 then '||chr(10)||'      Message(''Unable to set Working Directory'');'||chr(10)||'   end if;'||chr(10)||'end;');
insert into ffigen_functionargs (function_id,position,argument_name,datatype,pointer,read_only,prealloc_length,default_value,internalise) values (FFIGEN_FUNCTION_SEQ.CURRVAL,1,'NewWorkingDirectory',26,'Y','Y',256,'','');

--
-- This section of the DDL file provides the structure of the tables used by FFIGEN Version 1.0
-- If you want to edit the FFIGEN FMB file You'll need to ensure that these tables
-- are created as well so that you can compile
--

create table ffi$typedef(id number,nativetype  varchar2(30),nativeconst varchar2(30),plsqltype varchar2(30),primary key (id));
create table ffi$library(package_name varchar2(30),name varchar2(30),path varchar2(100),description varchar2(2000),primary key (package_name));
create table ffi$function(name varchar2(30),calltype VARCHAR2(1) CHECK ( calltype in ('C','P')),returntype number,package_name VARCHAR2(30),primary key (package_name,name),foreign key (package_name) references ffi$library  on delete cascade);
create table ffi$functionArgs(fnName varchar2(30),name varchar2(30),position number,datatype number,prealloc_length number,read_only varchar2(1),package_name varchar2(30),primary key (package_name,fnName,name),foreign key (package_name,fnName) references ffi$function on delete cascade);

--
-- End of FFIGEN SQL file.
--
