Merge branch 'master' into dbskel

application/config/dbskel.php

@@ -0,0 +1,28 @@
+<?php
+
+if (!defined('BASEPATH')) exit('No direct script access allowed');
+
+/*
+|--------------------------------------------------------------------------
+| Enable/Disable DBSkel procedure
+|--------------------------------------------------------------------------
+|
+| DBSkel is disabled by default for security reasons.
+| You should enable DBSkel whenever you intend to use DBSkel
+|
+*/
+$config['dbskel_enabled'] = false;
+
+/*
+|--------------------------------------------------------------------------
+| DBSkel mode
+|--------------------------------------------------------------------------
+|
+| This is used to set the dbskel mode:
+|	- dryrun: run without changing the database, useful for testing
+|	- new: build a new database or if database is already present creates only new objects
+|	- diff: like new, but it also remove object from database that are NOT present in configuration files
+|
+*/
+$config['dbskel_mode'] = 'dryrun';
+

application/controllers/system/DBSkel.php

@@ -0,0 +1,27 @@
+<?php
+
+if (!defined('BASEPATH')) exit('No direct script access allowed');
+
+class DBSkel extends CLI_Controller
+{
+	/**
+	 * Constructor
+	 */
+	public function __construct()
+	{
+		parent::__construct();
+
+		$this->load->library('DBSkelLib');
+	}
+
+	/**
+	 * Starts the DBSkel procedure
+	 */
+	public function start($step = null, $selectedDirectories = null)
+	{
+		// If the DBSkel procedure fails then exit with an error
+		// In this way it's possible to undestand from console what is the exit status of the procedure
+		$this->dbskellib->start($step, $selectedDirectories) === true ? exit(0) : exit(1);
+	}
+}
+

application/dbskel/fue/TBL_aktivitaet.php

@@ -0,0 +1,28 @@
+<?php
+
+$tableArray = array(
+	'tbl_aktivitaet' => array(
+		'aktivitaet_kurzbz' => array(
+			'comment' => 'I guess this is the PK',
+			'type' => 'character varying(16)',
+			'null' => false
+		),
+		'beschreibung' => array(
+			'comment' => 'none',
+			'type' => 'character varying(256)',
+			'null' => false,
+			'default' => "'Test string'"
+		),
+		'sort' => array(
+			'comment' => 'nope',
+			'type' => 'integer',
+			'default' => 1
+		),
+		'oe_kurzbz' => array(
+			'comment' => 'uhm',
+			'type' => 'character varying(32)'
+		)
+	),
+	'comment' => 'Timesheet SLA Activity'
+);
+

application/dbskel/fue/constraints.php

@@ -0,0 +1,9 @@
+<?php
+
+$constraintsArray = array(
+	'pk_tbl_aktivitaet' => 'ALTER TABLE fue.tbl_aktivitaet ADD CONSTRAINT pk_tbl_aktivitaet PRIMARY KEY (aktivitaet_kurzbz)',
+	'fk_projekt_oe' => 'ALTER TABLE fue.tbl_aktivitaet ADD CONSTRAINT fk_test FOREIGN KEY (oe_kurzbz) REFERENCES public.tbl_organisationseinheit (oe_kurzbz)',
+	'uk_beschreibung' => 'ALTER TABLE fue.tbl_aktivitaet ADD CONSTRAINT uk_beschreibung UNIQUE (beschreibung)',
+	'testchk' => 'ALTER TABLE fue.tbl_aktivitaet ADD CONSTRAINT testchk CHECK (sort > 0)'
+);
+

application/dbskel/fue/extra.sql

@@ -0,0 +1,2 @@
+SELECT 'Extra file' AS justatest;
+

application/dbskel/fue/functions.php

@@ -0,0 +1,4 @@
+<?php
+
+$functionsArray = array();
+

application/dbskel/fue/grants.sql

@@ -0,0 +1,25 @@
+-----------------------------------------------------
+-- Revokes all privileges from all granted users
+-----------------------------------------------------
+REVOKE ALL PRIVILEGES ON SCHEMA fue FROM vilesci;
+REVOKE ALL ON ALL TABLES IN SCHEMA fue FROM vilesci;
+REVOKE ALL ON ALL SEQUENCES IN SCHEMA fue FROM vilesci;
+REVOKE ALL ON ALL FUNCTIONS IN SCHEMA fue FROM vilesci;
+
+REVOKE ALL PRIVILEGES ON SCHEMA fue FROM web;
+REVOKE ALL ON ALL TABLES IN SCHEMA fue FROM web;
+REVOKE ALL ON ALL SEQUENCES IN SCHEMA fue FROM web;
+REVOKE ALL ON ALL FUNCTIONS IN SCHEMA fue FROM web;
+
+----------------------------------------------------------------------------------------------------
+-- Gives the desired privileges to the chosen users (with great power comes great responsibility!)
+----------------------------------------------------------------------------------------------------
+
+-- Schema privileges
+GRANT ALL ON SCHEMA fue TO vilesci;
+GRANT USAGE ON SCHEMA fue TO web;
+
+-- Sequences privileges
+GRANT SELECT,UPDATE ON SEQUENCE fue.seq_projekt_dokument_projekt_dokument_id TO vilesci;
+GRANT SELECT,UPDATE ON SEQUENCE fue.seq_projekt_dokument_projekt_dokument_id TO web;
+

application/dbskel/fue/schema.sql

@@ -0,0 +1,6 @@
+-- Create the schema if not exists
+CREATE SCHEMA IF NOT EXISTS fue;
+
+-- Comment schema
+COMMENT ON SCHEMA fue IS 'Projectmanagement';
+

application/dbskel/fue/sequences.php

@@ -0,0 +1,47 @@
+<?php
+
+$sequencesArray = array(
+	'seq_projekt_dokument_projekt_dokument_id' =>
+		'CREATE SEQUENCE fue.seq_projekt_dokument_projekt_dokument_id
+			START WITH 1
+			INCREMENT BY 1
+			NO MINVALUE
+			NO MAXVALUE
+			CACHE 1;',
+	'seq_projekt_ressource_projekt_ressource_id' =>
+		'CREATE SEQUENCE seq_projekt_ressource_projekt_ressource_id
+			START WITH 1
+			INCREMENT BY 1
+			NO MAXVALUE
+			NO MINVALUE
+			CACHE 1;',
+	'seq_projektphase_projektphase_id' =>
+		'CREATE SEQUENCE seq_projektphase_projektphase_id
+			START WITH 1
+			INCREMENT BY 1
+			NO MAXVALUE
+			NO MINVALUE
+			CACHE 1;',
+	'seq_projekttask_projekttask_id' =>
+		'CREATE SEQUENCE seq_projekttask_projekttask_id
+			START WITH 1
+			INCREMENT BY 1
+			NO MAXVALUE
+			NO MINVALUE
+			CACHE 1;',
+	'seq_ressource_ressource_id' =>
+		'CREATE SEQUENCE seq_ressource_ressource_id
+			START WITH 1
+			INCREMENT BY 1
+			NO MAXVALUE
+			NO MINVALUE
+			CACHE 1;',
+	'tbl_projekt_projekt_id_seq' =>
+		'CREATE SEQUENCE fue.tbl_projekt_projekt_id_seq
+			START WITH 1
+			INCREMENT BY 1
+			NO MAXVALUE
+			NO MINVALUE
+			CACHE 1;'
+);
+

application/dbskel/fue/views.php

@@ -0,0 +1,51 @@
+<?php
+
+$viewsArray = array(
+	'vw_projektressourcen' =>
+		'CREATE OR REPLACE VIEW fue.vw_projektressourcen (
+					projekt_ressource_id,
+					projekt_kurzbz,
+					projektphase_id,
+					projektphase,
+					typ,
+					ressource_id,
+					ressource,
+					funktion_kurzbz,
+					start,
+					ende,
+					oe_kurzbz,
+					projektbudget,
+					aufwandstyp_kurzbz,
+					projektphase_fk,
+					phasenbudget,
+					personentage,
+					nummer,
+					titel,
+					aufwand
+				)
+					AS
+						SELECT tpr.projekt_ressource_id,
+							COALESCE(tpr.projekt_kurzbz, tpp.projekt_kurzbz) AS projekt_kurzbz,
+							tpr.projektphase_id,
+							tpp.bezeichnung AS projektphase,
+							COALESCE(tpp.typ, \'Projekt\'::character varying) AS typ,
+							tpr.ressource_id,
+							tr.bezeichnung AS ressource,
+							tpr.funktion_kurzbz,
+							COALESCE(tpp.start, tp.beginn) AS start,
+							COALESCE(tpp.ende, tp.ende) AS ende,
+							tp.oe_kurzbz,
+							tp.budget AS projektbudget,
+							tp.aufwandstyp_kurzbz,
+							tpp.projektphase_fk,
+							tpp.budget AS phasenbudget,
+							tpp.personentage,
+							tp.nummer,
+							tp.titel,
+							tpr.aufwand
+						FROM fue.tbl_projekt_ressource tpr
+							JOIN fue.tbl_ressource tr USING (ressource_id)
+							LEFT JOIN fue.tbl_projekt tp USING (projekt_kurzbz)
+							LEFT JOIN fue.tbl_projektphase tpp USING (projektphase_id);'
+);
+

application/dbskel/index.html

@@ -0,0 +1,11 @@
+<!DOCTYPE html>
+<html>
+<head>
+	<title>403 Forbidden</title>
+</head>
+<body>
+
+<p>Directory access is forbidden.</p>
+
+</body>
+</html>