-- ----------------------------------------------------------------------------
--                                                                           --
--                      GNADE  : GNu Ada Database Environment                --
--                                                                           --
--  Filename        : $Source: /cvsroot/gnade/gnade/samples/sample_db/gnade.mimer.sql,v $
--  Description     : Create the test database with the mimer db
--  Author          : Sune Falck 
--  Created On      : 6-Apr-2001
--  Last Modified By: $Author: merdmann $
--  Last Modified On: $Date: 2006/12/12 19:02:11 $
--  Status          : $State: Exp $
--
--  Copyright (C) 2000-2006
--
--  GNADE is copyrighted by the persons and institutions enumerated in the   --
--  AUTHORS file. This file is located in the root directory of the          --
--  GNADE distribution.                                                      --
--                                                                           --
--  GNADE is free software;  you can redistribute it  and/or modify it under --
--  terms of the  GNU General Public License as published  by the Free Soft- --
--  ware  Foundation;  either version 2,  or (at your option) any later ver- --
--  sion.  GNAT is distributed in the hope that it will be useful, but WITH- --
--  OUT ANY WARRANTY;  without even the  implied warranty of MERCHANTABILITY --
--  or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License --
--  for  more details.  You should have  received  a copy of the GNU General --
--  Public License  distributed with GNAT;  see file COPYING.  If not, write --
--  to  the Free Software Foundation,  59 Temple Place - Suite 330,  Boston, --
--  MA 02111-1307, USA.                                                      --
--                                                                           --
--  As a special exception,  if other files  instantiate  generics from      --
--  GNADE Ada units, or you link GNADE Ada units or libraries with other     --
--  files  to produce an executable, these  units or libraries do not by     --
--  itself cause the resulting  executable  to  be covered  by the  GNU      --
--  General  Public  License.  This exception does not however invalidate    --
--  any other reasons why  the executable file  might be covered by the      --
--  GNU Public License.                                                      --
--                                                                           --
--  GNADE is implemented to work with GNAT, the GNU Ada compiler.            --
--                                                                           --
-- ----------------------------------------------------------------------------         
--
--  Functional Description						     
--  ======================						     
--  This SQL Script generates the test data base as it is used by	     
--  the code in the ./samples directory. The demo user has username="gnade"  
--  and password="gnade".						     
--				                                              
--									     
--  Restrictions							     
--  ============							    
--  The script is for use with the Mimer database.		    
--									    
--  References								    
--  ==========								    
--
--  www.mimer.com
--								    
--  Prerequisites
--  =============
--
--  Install MIMER. Normally a test data base DEVELOP is created at 
--  the installation which can be used for our purpose
--
--  Login as root and create the databse and the user by:
--
--  make createuser
--
--  Run test programs
--
--  Changes compared to the postgres script.
--  ========================================
--
--  Removed quotation characters from table and column names
--  Changed int4 to INTEGER
--  Changed bool to CHARACTER (1)
--  Added DATE as qualifier to date constants in insert statements
--  Added database DEVELOP to the create table statements
--  Added REFERENCES and CHECK clauses and changed order
--  so that DEPARTMENTS is defined and loaded before EMPLOYEES
--
--  2001-04-06 Sune Falck
------------------------------------------------------------------------

--CREATE DATABANK DEVELOP
--       OF 10 PAGES
--       IN 'gnade'
--       WITH TRANS OPTION;            

CREATE TABLE DEPARTMENTS (
        DEPTNO   INTEGER       NOT NULL PRIMARY KEY,
        NAME     CHARACTER(20) NOT NULL,
        LOCATION CHARACTER(20)
) ;                                          

CREATE TABLE EMPLOYEES (
        EMPNO     INTEGER        NOT NULL PRIMARY KEY,
        NAME      CHARACTER (20) NOT NULL,
        FIRSTNAME CHARACTER (20),
        DEPTNO    INTEGER REFERENCES DEPARTMENTS (DEPTNO),
        SINCE     DATE,
        MANAGER	  INTEGER,
        JOB       CHARACTER (20),
        PROMOTION CHARACTER (1) CHECK (PROMOTION IN ('T', 'F')),
        SALARY	  DECIMAL (8,4)
) ;


---  Testdata 

insert into departments 
   values( 1001, 'Sales',		'Berlin' );
insert into departments 
   values( 1000, 'Developement',	'Stuttgart' );
insert into departments 
   values( 1002, 'CM and Production',	'Bonn' );
insert into departments 
   values( 1003, 'Support',		'Chicago' );
insert into departments 
   values( 2000, 'Masterminds',		null );      


insert into employees values 
(  1, 'Erdmann',	'Michael',	1001,	DATE'1999-12-31',	 2, 'Coding Monkey', 'T', 200.1234 );
insert into employees values 
(  2, 'Massalski',	'Marina',	1000,	DATE'2000-01-09', null, 'Supervisor',    'F', 200.00 );
insert into employees values 
(  3, 'Tully',		'Noel',		1001,	DATE'1959-01-23',	 2, 'Supervisor',    'T', 200.00 );
insert into employees values 
(  4, 'Pfeifer',	'Juergen',	1001,	DATE'2001-01-01',	 2, 'Designer',	     'T', 200.00 );
insert into employees values 
(  5, 'Smith',		'John',		1000,	DATE'1980-06-11',	 3, 'Coding Monkey', 'T', 250.00 );
insert into employees values 
(  6, 'Smith',		'John',		1001,	DATE'1972-06-11',	 3, 'Designer',	     'T', 820.00 );
insert into employees values 
( 50, 'Simpson',	'Bud',		1003,	DATE'1980-06-11',	 3, 'Clerk',	     'F',  20.00 );
insert into employees values 
( 51, 'Bundy',		'Al',		1003,	DATE'1980-06-11',	 3, 'Clerk',	     'F',  10.00 );
insert into employees values 
( 52, 'Bundy',		'Peggy',	1003,	DATE'1988-06-15',	 3,  null,	     'F', -10.00 );
insert into employees values 
( 53, 'Simpson',	'Kelly',	1003,	DATE'1995-09-11',	 3, 'Secretary',     'F',  20.00 );
insert into employees values 
(500, 'Rutherford',	'Ernest',	2000,	DATE'1930-05-30',	 3, 'Genius',	     'T',1200.00 );
insert into employees values 
(501, 'Fermi',		null,		2000,	DATE'1940-07-29',	 3, 'Genius',	     'T',1200.00 );
insert into employees values 
(502, 'Einstein',	'Albert',	2000,	DATE'1941-04-30',	 2, 'Genius',	     'T',1200.00 );
insert into employees values 
(503, 'Fermi',		null,		2000,	DATE'1020-08-15',	 2, 'Genius',	     'T',1200.00 );  

CREATE TABLE LOCATIONS (
        NAME     CHARACTER(20) NOT NULL PRIMARY KEY,
        CITY     VARCHAR(40),
        STREET   VARCHAR(80),
        NO       CHARACTER(5),
        ZIPCODE  INT
) ;

insert into locations
   values( 'Berlin', 'Berlin','Siemensdamm', '50a', 10243 );                  
insert into locations
   values( 'Bonn', 'Bonn','Oberkasselerstrasse', '8', 47112 );          
insert into locations
   values( 'Stuttgart', 'Stuttgart','Lorenzweg', '1', 60001 );          
insert into locations
   values( 'Chicage', 'Chicago','Bundy Stree', '9996', 70001 );          

CREATE TABLE ATTACHMENTS (
	ID	 INT NOT NULL PRIMARY KEY,
        USED     INT,
        CREATION DATE,
	TITLE    VARCHAR(256),
	DATA     VARBINARY(10000)
) ;

-- ACL Information. This may have to be changed if a different DBCS is used
REVOKE ALL on employees from PUBLIC;
REVOKE ALL on departments from PUBLIC;
REVOKE ALL on locations from PUBLIC;
REVOKE ALL on ATTACHMENTS from PUBLIC;
GRANT ALL on employees to PUBLIC;
GRANT ALL on departments to PUBLIC;
GRANT ALL on locations to PUBLIC;
GRANT ALL on ATTACHMENTS to PUBLIC;
                                         
