source: trunk/workflow/setup/default_records.inc.php @ 5726

Revision 5726, 13.8 KB checked in by viani, 13 years ago (diff)

Ticket #2531 - Correções de banco de dados no instalador do workflow
  • Property svn:executable set to *
Line 
1<?php
2  /***************************************************************************\
3  * eGroupWare - Workflow                                                     *
4  * http://www.egroupware.org                                                 *
5  * Written by:                                                               *
6  *  - Sidnei Augusto Drovetto Jr <drovetto@gmail.com>                        *
7  * ------------------------------------------------------------------------- *
8  *  This program is free software; you can redistribute it and/or modify it  *
9  *  under the terms of the GNU General Public License as published by the    *
10  *  Free Software Foundation; either version 2 of the License, or (at your   *
11  *  option) any later version.                                               *
12  \***************************************************************************/
13/* Default Workflow Database */
14
15/* extract the database connection parameters */
16if (!empty($GLOBALS['phpgw']->ADOdb->database))
17{
18        $workflowHostInfo = array(
19                'dbname' => $GLOBALS['phpgw']->ADOdb->database,
20                'host' => $GLOBALS['phpgw']->ADOdb->host,
21                'port' => $GLOBALS['phpgw']->ADOdb->port,
22                'user' => $GLOBALS['phpgw']->ADOdb->user,
23                'password' => $GLOBALS['phpgw']->ADOdb->password);
24}
25else
26{
27        $hostInfo = "dbname= host= password= port= user= " . $GLOBALS['phpgw']->ADOdb->host;
28        $hostInfo = explode(' ', $hostInfo);
29        $workflowHostInfo = array();
30        foreach ($hostInfo as $parameter)
31        {
32                $currentParameter = explode('=', $parameter);
33                $workflowHostInfo[$currentParameter[0]] = isset($currentParameter[1]) ? $currentParameter[1] : "";
34        }
35}
36
37if (($workflowHostInfo['password']{0} == "'") && ($workflowHostInfo['password']{strlen($workflowHostInfo['password'])-1} == "'"))
38        $workflowHostInfo['password'] = substr($workflowHostInfo['password'], 1, strlen($workflowHostInfo['password']) - 2);
39
40/* connect to the egroupware database */
41$GLOBALS['phpgw']->ADOdb->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], $workflowHostInfo['dbname']);
42
43/* create the workflow database */
44$GLOBALS['phpgw']->ADOdb->query("CREATE DATABASE workflow WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'LATIN1'");
45$GLOBALS['phpgw']->ADOdb->query("CREATE USER admin_workflow WITH PASSWORD 'admin_workflow' NOCREATEDB NOCREATEUSER VALID UNTIL 'infinity'");
46$GLOBALS['phpgw']->ADOdb->query("CREATE GROUP workflow");
47$GLOBALS['phpgw']->ADOdb->query("ALTER GROUP workflow ADD USER admin_workflow");
48$GLOBALS['phpgw']->ADOdb->query("GRANT workflow TO admin_workflow");
49
50/* pre-configure the module */
51$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'workflow_database_type', 'pgsql'));
52$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_type', 'pgsql'));
53$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_admin_password', 'admin_workflow'));
54$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_name', 'workflow'));
55$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_host', 'localhost'));
56$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_port', '5432'));
57$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'database_admin_user', 'admin_workflow'));
58$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'intranet_subnetworks', '10.0.0.0/8'));
59$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'log_type_file', 'True'));
60$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'log_type_firebug', 'True'));
61$GLOBALS['phpgw']->ADOdb->query("INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES(?, ?, ?)", array('workflow', 'log_level', '0'));
62
63/* connect to the new database */
64$workflowDB = $GLOBALS['phpgw']->ADOdb;
65if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
66{
67        /* create a sample application (Music CD Loan) */
68        $workflowDB->query("CREATE USER admin_cds WITH PASSWORD 'admin_cds' NOCREATEDB NOCREATEUSER VALID UNTIL 'infinity'");
69        $workflowDB->query("ALTER GROUP workflow ADD USER admin_cds");
70        $workflowDB->query("CREATE SCHEMA cds AUTHORIZATION admin_cds");
71        $workflowDB->query("GRANT ALL ON SCHEMA cds TO postgres");
72        $workflowDB->query("GRANT ALL ON SCHEMA cds TO admin_cds");
73        $workflowDB->query("CREATE TABLE cds.cdcollection(cdid int4 NOT NULL, title varchar(200), status varchar(40), usuario varchar(200), CONSTRAINT cdcollection_pkey PRIMARY KEY (cdid))");
74        $workflowDB->query("ALTER TABLE cds.cdcollection OWNER TO admin_cds");
75        $workflowDB->query("GRANT ALL ON TABLE cds.cdcollection TO postgres WITH GRANT OPTION");
76        $workflowDB->query("GRANT ALL ON TABLE cds.cdcollection TO admin_cds");
77        $workflowDB->query("COMMENT ON TABLE cds.cdcollection IS 'Exemplo de Empréstimo de CDs'");
78        $workflowDB->query("INSERT INTO cdcollection VALUES (1, 'Xuxa', 'disponivel', '')");
79        $workflowDB->query("INSERT INTO cdcollection VALUES (2, 'Roberto Carlos', 'disponivel', '')");
80        $workflowDB->query("INSERT INTO cdcollection VALUES (3, 'Pink Floyd', 'disponivel', '')");
81
82        /* create the new workflow tables */
83        $workflowDB->query('CREATE TABLE organizacao (organizacao_id serial NOT NULL, nome character varying(20) NOT NULL, descricao character varying(100) NOT NULL, url_imagem character varying(200), ativa character varying(1) NOT NULL, sitio CHARACTER VARYING(100), gidnumber_simplificado character varying(100))');
84        $workflowDB->query('CREATE TABLE area_status (area_status_id serial NOT NULL, organizacao_id integer NOT NULL, descricao character varying(50) NOT NULL, nivel integer NOT NULL)');
85        $workflowDB->query('CREATE TABLE centro_custo (organizacao_id integer NOT NULL, centro_custo_id serial NOT NULL, nm_centro_custo integer NOT NULL, grupo character varying(30), descricao character varying(100) NOT NULL)');
86        $workflowDB->query('CREATE TABLE localidade (organizacao_id integer NOT NULL, localidade_id serial NOT NULL, centro_custo_id integer, descricao character varying(50) NOT NULL, empresa CHARACTER VARYING(100), endereco CHARACTER VARYING(100), complemento CHARACTER VARYING(50), cep CHARACTER VARYING(9), bairro CHARACTER VARYING(30), cidade CHARACTER VARYING(50), uf CHARACTER(2))');
87        $workflowDB->query('CREATE TABLE funcionario (funcionario_id int4 NOT NULL, area_id integer NOT NULL, localidade_id integer NOT NULL, centro_custo_id integer, organizacao_id integer NOT NULL, funcionario_status_id integer NOT NULL, cargo_id int4, nivel int2, funcionario_categoria_id int4, titulo CHARACTER VARYING(30))');
88        $workflowDB->query('CREATE TABLE area (organizacao_id integer NOT NULL, area_id serial NOT NULL, area_status_id integer NOT NULL, superior_area_id integer, centro_custo_id integer, titular_funcionario_id int4, sigla character varying(20) NOT NULL, descricao character varying(100) NOT NULL, ativa character varying(1) NOT NULL, auxiliar_funcionario_id int4)');
89        $workflowDB->query('CREATE TABLE funcionario_status (funcionario_status_id serial NOT NULL, descricao character varying(50) NOT NULL, exibir character varying(1) NOT NULL, organizacao_id integer NOT NULL)');
90        $workflowDB->query('CREATE TABLE cargo (cargo_id serial NOT NULL, descricao character varying(150), organizacao_id int4)');
91        $workflowDB->query('CREATE TABLE funcionario_categoria (funcionario_categoria_id serial NOT NULL, descricao character varying(150), organizacao_id int4 NOT NULL)');
92        $workflowDB->query('CREATE TABLE telefone (telefone_id serial NOT NULL, descricao character varying(50) NOT NULL, numero character varying(50) NOT NULL, organizacao_id integer NOT NULL)');
93        $workflowDB->query('CREATE TABLE substituicao (substituicao_id serial NOT NULL, area_id integer NOT NULL, funcionario_id integer NOT NULL, data_inicio date NOT NULL, data_fim date NOT NULL)');
94
95
96        /* add the constraints */
97
98        /* primary keys */
99        $workflowDB->query('ALTER TABLE ONLY organizacao ADD CONSTRAINT organizacao_pkey PRIMARY KEY (organizacao_id)');
100        $workflowDB->query('ALTER TABLE ONLY area_status ADD CONSTRAINT areastatus_pkey PRIMARY KEY (area_status_id)');
101        $workflowDB->query('ALTER TABLE ONLY centro_custo ADD CONSTRAINT centrocusto_pkey PRIMARY KEY (centro_custo_id)');
102        $workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT localidade_pkey PRIMARY KEY (localidade_id)');
103        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT funcionario_pkey PRIMARY KEY (funcionario_id)');
104        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT area_pkey PRIMARY KEY (area_id)');
105        $workflowDB->query('ALTER TABLE ONLY funcionario_status ADD CONSTRAINT funcionario_status_pkey PRIMARY KEY (funcionario_status_id)');
106        $workflowDB->query('ALTER TABLE ONLY telefone ADD CONSTRAINT telefone_pkey PRIMARY KEY (telefone_id)');
107        $workflowDB->query('ALTER TABLE ONLY substituicao ADD CONSTRAINT substituicao_pkey PRIMARY KEY (substituicao_id)');
108        $workflowDB->query('ALTER TABLE ONLY cargo ADD CONSTRAINT cargo_pkey PRIMARY KEY (cargo_id)');
109        $workflowDB->query('ALTER TABLE ONLY funcionario_categoria ADD CONSTRAINT funcionario_categoria_pkey PRIMARY KEY (funcionario_categoria_id)');
110
111
112        /* foreign keys */
113        $workflowDB->query('ALTER TABLE ONLY funcionario_status ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
114        $workflowDB->query('ALTER TABLE ONLY area_status ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
115        $workflowDB->query('ALTER TABLE ONLY centro_custo ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
116        $workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT "$1" FOREIGN KEY (centro_custo_id) REFERENCES centro_custo(centro_custo_id)');
117        $workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT "$2" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
118        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$1" FOREIGN KEY (centro_custo_id) REFERENCES centro_custo(centro_custo_id)');
119        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$2" FOREIGN KEY (localidade_id) REFERENCES localidade(localidade_id)');
120        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$3" FOREIGN KEY (area_id) REFERENCES area(area_id)');
121        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$4" FOREIGN KEY (funcionario_status_id) REFERENCES funcionario_status(funcionario_status_id)');
122        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$5" FOREIGN KEY (cargo_id) REFERENCES cargo(cargo_id)');
123        $workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$6" FOREIGN KEY (funcionario_categoria_id) REFERENCES funcionario_categoria(funcionario_categoria_id)');
124        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$1" FOREIGN KEY (superior_area_id) REFERENCES area(area_id)');
125        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$2" FOREIGN KEY (centro_custo_id) REFERENCES centro_custo(centro_custo_id)');
126        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$3" FOREIGN KEY (titular_funcionario_id) REFERENCES funcionario(funcionario_id)');
127        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$4" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
128        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$5" FOREIGN KEY (area_status_id) REFERENCES area_status(area_status_id)');
129        $workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$6" FOREIGN KEY (auxiliar_funcionario_id) REFERENCES funcionario(funcionario_id)');
130        $workflowDB->query('ALTER TABLE ONLY telefone ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
131        $workflowDB->query('ALTER TABLE ONLY substituicao ADD CONSTRAINT "$1" FOREIGN KEY (area_id) REFERENCES area(area_id)');
132        $workflowDB->query('ALTER TABLE ONLY substituicao ADD CONSTRAINT "$2" FOREIGN KEY (funcionario_id) REFERENCES funcionario(funcionario_id)');
133        $workflowDB->query('ALTER TABLE ONLY cargo ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
134        $workflowDB->query('ALTER TABLE ONLY funcionario_categoria ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
135
136
137        /* set the permissions to the database objects */
138        $dbObjects = array('organizacao', 'area_status', 'centro_custo', 'localidade', 'funcionario', 'area', 'funcionario_status', 'telefone', 'substituicao', 'organizacao_organizacao_id_seq', 'area_status_area_status_id_seq', 'centro_custo_centro_custo_id_seq', 'localidade_localidade_id_seq', 'area_area_id_seq', 'funcionario_status_funcionario_status_id_seq', 'cargo', 'cargo_cargo_id_seq', 'funcionario_categoria', 'funcionario_categoria_funcionario_categoria_id_seq', 'telefone_telefone_id_seq', 'substituicao_substituicao_id_seq');
139
140        foreach ($dbObjects as $dbObject)
141        {
142                $workflowDB->query("GRANT ALL ON TABLE $dbObject TO admin_workflow");
143                $workflowDB->query("GRANT ALL ON TABLE $dbObject TO postgres");
144                $workflowDB->query("GRANT SELECT ON TABLE $dbObject TO public");
145        }
146}
147
148/* reconnect to the previous database */
149$GLOBALS['phpgw']->ADOdb->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], $workflowHostInfo['dbname']);
150
151?>
Note: See TracBrowser for help on using the repository browser.