Skip to content

14. Practice Exercise – version 6

Image

We have just implemented the following layered structure:

Image

The SGBD used in the examples was MySQL. In the “link” section, we noted that nothing in the class implementing the [dao] layer suggested that a specific SGBD was being used. We will now verify this by using another SGBD, the SGBD PostgreSQL. The layered architecture becomes as follows:

Image

14.1. Installation of the SGBD PostgreSQL

The SGBD and PostgreSQL distributions are available as of URL and [https://www.postgresql.org/download/] (May 2019). We will demonstrate the installation of version for 64-bit Windows:

Image

Image

  • Go to [1-4] and download the SGBD installer;

Launch the downloaded installer:

Image

  • in [6], specify an installation folder;

Image

  • In [8], option and [Stack Builder] are not needed for what we are doing here;
  • In [10], leave the value that is displayed;

Image

  • In [12-13], we have entered the password [root] here. This will be the administrator password for SGBD, which is called [postgres]. PostgreSQL also refers to it as the superuser;
  • in [15], leave the default value: this is the listening port for SGBD;

Image

  1. In [17], leave the default value;
  2. in [19], the installation configuration summary;

Image

Image

On Windows, SGBD PostgreSQL is installed as a Windows service that starts automatically. Most of the time, this is not desirable. We will modify this configuration. Type [services] into the Windows search bar:

Image

  • in [29], we see that the SGBD PostgreSQL service is set to automatic mode. We change this by accessing the properties of the [30] service:

Image

  • In [31-32], set the startup type to manual;
  • In [33], stop the service;

When you want to manually start SGBD, return to the [services] application, right-click on the [postgresql] service (34), and start it (35).

14.2. Enabling the PDO extension of SGBD PostgreSQL

We will modify the [php.ini] file that configures PHP (see link section):

Image

  • In [2], verify that the PDO extension of PostgreSQL is enabled. Once this is done, save the change and then restart Laragon to ensure that the change is applied. Next, check the configuration of PHP directly from Laragon [3-5].

14.3. Run PostgreSQL using the [pgAdmin] tool

Start the Windows service for SGBD PostgreSQL (see the link section). Then, in the same way you started the [services] tool, launch the [pgadmin] tool, which allows you to manage SGBD, PostgreSQL, and [1-3]:

Image

You may be prompted for the superuser password at some point. The superuser is named [postgres]. You set this password during the installation of SGBD. In this document, we assigned the password [root] to the superuser during installation.

  • In [4], [pgAdmin] is a web application;
  • in [5], the list of PostgreSQL servers detected by [pgAdmin], here 1;
  • in [6], the PostgreSQL server that we launched;
  • in [7], the databases of SGBD, here 1;
  • in [8], the [postgresql] database is managed by the superuser [postgres];

First, let’s create a user [admimpots] with the password [mdpimpots]:

Image

Image

  • in [17], we entered [mdpimpots];

Image

  • in [21], the code SQL that the tool [pgAdmin] will send to SGBD PostgreSQL. This is a way to learn the SQL language, which is proprietary to PostgreSQL;
  • In [22], after validation by the [Save] wizard, the user [admimpots] was created;

Now we create the database [dbimpots-2019]:

Image

Right-click on [23], then [24-25] to create a new database. In the [26] tab, we define the database name as [27] and its owner as [admimpots] [28].

Image

  • in [30], the database creation code SQL;
  • to [31]; after validation by the wizard [Save], the database [dbimpots-2019] is created;

Now, we will create the table [tbtranches] with the columns [id, limites, coeffr, coeffn]. A special feature of PostgreSQL is that column names are case-sensitive (upper/lowercase), which is not usually the case with other SGBD tables. Thus, with MySQL, the order [select limites, coeffR, coeffN from tbtranches] will work even if the actual columns in the table [tbtranches] are [LIMITES, COEFFR, COEFFN]. With PostgreSQL, the order SQL will not work. You could then write [select LIMITES, COEFFR, COEFFN from tbtranches], but it still won’t work, because PostgreSQL will execute the command [select limites, coeffr, coeffn from tbtranches]: by default, it converts column names to lowercase. To prevent this, you must write: [select "LIMITES", "COEFFR", "COEFFN" from tbtranches], i.e., you must enclose the column names in quotation marks. For these reasons, we will give the columns lowercase names. The names of database objects can be a source of incompatibility between SGBD, as certain names are reserved words in some SGBD but not in others.

We create the table [tbtranches]:

Image

  • use the [40] button to create columns;

Image

Image

  • after completing the creation wizard via [Save], the table [tbtranches] is created [52-53];

We need to tell SGBD to generate the primary key [id] itself when inserting a row into the table:

Image

  • In [56], we access the properties of the primary key [id];
  • in [59], we specify that the column is of type [Identity]. This will cause SGBD to generate the primary key values;

Image

  • In [62], the code SQL generated for this operation;

The table [tbtranches] is now ready.

We repeat the same operations to create the table [tbconstantes]. We specify the desired result:

Image

Image

Image

The [dbimpots-2019] database is now ready. We will populate it with data.

As we did with MySQL, it is possible to export the [dbimpots-2019] database to a SQL file. We can then import this SQL file to recreate the database if it has been lost or corrupted. Here, we will export only the database structure and not its data:

Image

Image

The generated file is as follows:


--
-- PostgreSQL database dump
--
 
-- Dumped from database version 11.2
-- Dumped by pg_dump version 11.2
 
-- Started on 2019-07-04 08:20:31
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
 
SET default_tablespace = '';
 
SET default_with_oids = false;
 
--
-- TOC entry 198 (class 1259 OID 16408)
-- Name: tbconstantes; Type: TABLE; Schema: public; Owner: postgres
--
 
CREATE TABLE public.tbconstantes (
    plafond_qf_demi_part double precision NOT NULL,
    id integer NOT NULL,
    plafond_revenus_celibataire_pour_reduction double precision NOT NULL,
    plafond_revenus_couple_pour_reduction double precision NOT NULL,
    valeur_reduc_demi_part double precision NOT NULL,
    plafond_decote_celibataire double precision NOT NULL,
    plafond_decote_couple double precision NOT NULL,
    plafond_impot_celibataire_pour_decote double precision NOT NULL,
    plafond_impot_couple_pour_decote double precision NOT NULL,
    abattement_dix_pourcent_max double precision NOT NULL,
    abattement_dix_pourcent_min double precision NOT NULL
);
 
 
ALTER TABLE public.tbconstantes OWNER TO postgres;
 
--
-- TOC entry 199 (class 1259 OID 16411)
-- Name: tbconstantes_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
 
ALTER TABLE public.tbconstantes ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.tbconstantes_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);
 
 
--
-- TOC entry 196 (class 1259 OID 16399)
-- Name: tbtranches; Type: TABLE; Schema: public; Owner: admimpots
--
 
CREATE TABLE public.tbtranches (
    limites double precision NOT NULL,
    id integer NOT NULL,
    coeffr double precision NOT NULL,
    coeffn double precision NOT NULL
);
 
 
ALTER TABLE public.tbtranches OWNER TO admimpots;
 
--
-- TOC entry 197 (class 1259 OID 16404)
-- Name: tbimpots_id_seq; Type: SEQUENCE; Schema: public; Owner: admimpots
--
 
ALTER TABLE public.tbtranches ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.tbimpots_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);
 
 
--
-- TOC entry 2694 (class 2606 OID 16429)
-- Name: tbconstantes tbconstantes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY public.tbconstantes
    ADD CONSTRAINT tbconstantes_pkey PRIMARY KEY (id);
 
 
--
-- TOC entry 2692 (class 2606 OID 16403)
-- Name: tbtranches tbimpots_pkey; Type: CONSTRAINT; Schema: public; Owner: admimpots
--
 
ALTER TABLE ONLY public.tbtranches
    ADD CONSTRAINT tbimpots_pkey PRIMARY KEY (id);
 
 
--
-- TOC entry 2821 (class 0 OID 0)
-- Dependencies: 198
-- Name: TABLE tbconstantes; Type: ACL; Schema: public; Owner: postgres
--
 
GRANT ALL ON TABLE public.tbconstantes TO admimpots;
 
 
-- Completed on 2019-07-04 08:20:32
 
--
-- PostgreSQL database dump complete
--

14.4. Filling the [tbtranches] table

We have already done this work with SGBD and MySQL in the link section. We simply need to modify the [database.json] file, which describes the database:

Image

The [database.json] file becomes the following:


{
    "dsn": "pgsql:host=localhost;dbname=dbimpots-2019",
    "id": "admimpots",
    "pwd": "mdpimpots",
    "tableTranches": "public.tbtranches",
    "colLimites": "limites",
    "colCoeffR": "coeffr",
    "colCoeffN": "coeffn",
    "tableConstantes": "public.tbconstantes",
    "colPlafondQfDemiPart": "plafond_qf_demi_part",
    "colPlafondRevenusCelibatairePourReduction": "plafond_revenus_celibataire_pour_reduction",
    "colPlafondRevenusCouplePourReduction": "plafond_revenus_couple_pour_reduction",
    "colValeurReducDemiPart": "valeur_reduc_demi_part",
    "colPlafondDecoteCelibataire": "plafond_decote_celibataire",
    "colPlafondDecoteCouple": "plafond_decote_couple",
    "colPlafondImpotCelibatairePourDecote": "plafond_impot_celibataire_pour_decote",
    "colPlafondImpotCouplePourDecote": "plafond_impot_couple_pour_decote",
    "colAbattementDixPourcentMax": "abattement_dix_pourcent_max",
    "colAbattementDixPourcentMin": "abattement_dix_pourcent_min"
}
  • line 2: the DSN has changed; [pgsql] indicates that we are dealing with the Postgres SGBD;
  • lines 5 and 9: the table names have been prefixed with the name of the schema to which they belong, [public]. This was not strictly necessary since [public] is the default schema when no schema is specified in the table name;
  • lines 6–8, 10–19: the column names have changed;

The script [MainTransferAdminDataFromJsonFile2PostgresDatabase.php] for populating the database [dbimpots-2019] is as follows:


<?php
 
// strict adherence to declared types of function parameters
declare (strict_types=1);
 
// namespace
namespace Application;
 
// error handling by PHP
// ini_set("display_errors", "0");
// interface and class inclusion
require_once __DIR__ . "/../../version-05/Entities/BaseEntity.php";
require_once __DIR__ . "/../../version-05/Entities/TaxAdminData.php";
require_once __DIR__ . "/../../version-05/Entities/TaxPayerData.php";
require_once __DIR__ . "/../../version-05/Entities/Database.php";
require_once __DIR__ . "/../../version-05/Entities/ExceptionImpots.php";
require_once __DIR__ . "/../../version-05/Utilities/Utilitaires.php";
require_once __DIR__ . "/../../version-05/Dao/InterfaceDao.php";
require_once __DIR__ . "/../../version-05/Dao/TraitDao.php";
require_once __DIR__ . "/../../version-05/Dao/InterfaceDao4TransferAdminData2Database.php";
require_once __DIR__ . "/../../version-05/Dao/DaoTransferAdminDataFromJsonFile2Database.php";
//
// definition of constants
const DATABASE_CONFIG_FILENAME = "../Data/database.json";
const TAXADMINDATA_FILENAME = "../Data/taxadmindata.json";
 
//
try {
  // creation of the [dao] layer
  $dao = new DaoTransferAdminDataFromJsonFile2Database(DATABASE_CONFIG_FILENAME, TAXADMINDATA_FILENAME);
  // data transfer to the database
  $dao->transferAdminData2Database();
} catch (ExceptionImpots $ex) {
  // error is displayed
  print "L'erreur suivante s'est produite : " . utf8_encode($ex->getMessage()) . "\n";
}
// end
print "Terminé\n";
exit;

Comments

Only lines 12–21, which load the files required to run the application, change. They change because the value [__DIR__] changes: it now refers to the folder [version-07/Main].

When this script is run, the following result is obtained in the [tbtranches] table:

Image

  1. Right-click on [1], then on [2-3];
  2. In [4], the tax bracket data is present;

We repeat the same process for the [tbconstantes] constant table:

Image

Image

Image

Note that the Laragon application does not need to be running to execute the script: neither the Apache server nor SGBD or MySQL is required. We only need SGBD and PostgreSQL, for which we have started the Windows service.

14.5. Tax calculation

Image

The [dao] (3) and [métier] (2) layers have already been written. We have already written the main script for SGBD and MySQL in the linked section. We simply need to take the [MainCalculateImpotsWithTaxAdminDataInMySQLDatabase.php] script and adapt it for SGBD and PostgreSQL. It is now called [MainCalculateImpotsWithTaxAdminDataInPostgresDatabase.php]:

Image

The [MainCalculateImpotsWithTaxAdminDataInPostgresDatabase.php] script is as follows:


<?php
 
// strict adherence to declared types of function parameters
declare (strict_types=1);
 
// namespace
namespace Application;
 
// error handling by PHP
//ini_set("display_errors", "0");
// interface and class inclusion
require_once __DIR__ . "/../../version-05/Entities/BaseEntity.php";
require_once __DIR__ . "/../../version-05/Entities/TaxAdminData.php";
require_once __DIR__ . "/../../version-05/Entities/TaxPayerData.php";
require_once __DIR__ . "/../../version-05/Entities/Database.php";
require_once __DIR__ . "/../../version-05/Entities/ExceptionImpots.php";
require_once __DIR__ . "/../../version-05/Utilities/Utilitaires.php";
require_once __DIR__ . "/../../version-05/Dao/InterfaceDao.php";
require_once __DIR__ . "/../../version-05/Dao/TraitDao.php";
require_once __DIR__ . "/../../version-05/Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
require_once __DIR__ . "/../../version-05/Métier/InterfaceMetier.php";
require_once __DIR__ . "/../../version-05/Métier/Metier.php";
//
// definition of constants
const DATABASE_CONFIG_FILENAME = "../Data/database.json";
const TAXADMINDATA_FILENAME = "../Data/taxadmindata.json";
const RESULTS_FILENAME = "../Data/resultats.json";
const ERRORS_FILENAME = "../Data/errors.json";
const TAXPAYERSDATA_FILENAME = "../Data/taxpayersdata.json";
 
try {
  // creation of the [dao] layer
  $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
  // creation of the [business] layer
  $métier = new Metier($dao);
  // tax calculation in batch mode
  $métier->executeBatchImpots(TAXPAYERSDATA_FILENAME, RESULTS_FILENAME, ERRORS_FILENAME);
} catch (ExceptionImpots $ex) {
  // error is displayed
  print "Une erreur s'est produite : " . utf8_encode($ex->getMessage()) . "\n";
}
// end
print "Terminé\n";
exit;

Comments

Only lines 12–22, which load the files required to run the application, change. They change because the value [__DIR__] changes: it now refers to the folder [version-07/Main].

Execution Results

The same as those obtained in previous versions.

14.6. [Codeception] Tests

As with previous versions, we validate this version with [Codeception] tests:

Image

14.6.1. [dao] layer test

The [DaoTest.php] test is as follows:


<?php
 
// strict adherence to declared types of function parameters
declare (strict_types=1);
 
// namespace
namespace Application;
 
// root directories
define("ROOT", "C:/Data/st-2019/dev/php7/poly/scripts-console/impots/version-06");
define("VENDOR", "C:/myprograms/laragon-lite/www/vendor");
 
// interface and class inclusion
require_once ROOT . "/../version-05/Entities/BaseEntity.php";
require_once ROOT . "/../version-05/Entities/TaxAdminData.php";
require_once ROOT . "/../version-05/Entities/TaxPayerData.php";
require_once ROOT . "/../version-05/Entities/Database.php";
require_once ROOT . "/../version-05/Entities/ExceptionImpots.php";
require_once ROOT . "/../version-05/Utilities/Utilitaires.php";
require_once ROOT . "/../version-05/Dao/InterfaceDao.php";
require_once ROOT . "/../version-05/Dao/TraitDao.php";
require_once ROOT . "/../version-05/Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
 
// third-party libraries
require_once VENDOR . "/autoload.php";
 
// definition of constants
const DATABASE_CONFIG_FILENAME = ROOT ."../Data/database.json";
 
class DaoTest extends \Codeception\Test\Unit {
  // TaxAdminData
  private $taxAdminData;
 
  public function __construct() {
    parent::__construct();
    // creation of the [dao] layer
    $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
    $this->taxAdminData = $dao->getTaxAdminData();
  }
 
  // tests
  public function testTaxAdminData() {
    …
  }
 
}

Comments

  1. lines 9–28: definition of the test environment. We use the same environment, without the [métier] layer, as the one used by the main script [MainCalculateImpotsWithTaxAdminDataInPostgresDatabase] described in the linked section;
  2. lines 34–39: construction of the [dao] layer;
  3. line 38: the [$this→taxAdminData] attribute contains the data to be tested;
  4. lines 42–44: the [testTaxAdminData] method is the one described in the linked section;

The test results are as follows:

Image

14.6.2. Test of layer [métier]

The [MetierTest.php] test is as follows:


<?php
 
// strict adherence to declared types of function parameters
declare (strict_types=1);
 
// namespace
namespace Application;
 
// root directories
define("ROOT", "C:/Data/st-2019/dev/php7/poly/scripts-console/impots/version-06");
define("VENDOR", "C:/myprograms/laragon-lite/www/vendor");
 
// interface and class inclusion
require_once ROOT . "/../version-05/Entities/BaseEntity.php";
require_once ROOT . "/../version-05/Entities/TaxAdminData.php";
require_once ROOT . "/../version-05/Entities/TaxPayerData.php";
require_once ROOT . "/../version-05/Entities/Database.php";
require_once ROOT . "/../version-05/Entities/ExceptionImpots.php";
require_once ROOT . "/../version-05/Utilities/Utilitaires.php";
require_once ROOT . "/../version-05/Dao/InterfaceDao.php";
require_once ROOT . "/../version-05/Dao/TraitDao.php";
require_once ROOT . "/../version-05/Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
require_once ROOT . "/../version-05/Métier/InterfaceMetier.php";
require_once ROOT . "/../version-05/Métier/Metier.php";
// third-party libraries
require_once VENDOR . "/autoload.php";
// definition of constants
const DATABASE_CONFIG_FILENAME = ROOT . "../Data/database.json";
 
class MetierTest extends \Codeception\Test\Unit {
  // business layer
  private $métier;
 
  public function __construct() {
    parent::__construct();
    // creation of the [dao] layer
    $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
    // creation of the [business] layer
    $this->métier = new Metier($dao);
  }
 
  // tests
  public function test1() {
    …
  }
--------------------------------------------------------------------
  public function test11() {
    …
  }
 
}

Comments

  • lines 9–28: definition of the test environment. We use the same one as the main script [MainCalculateImpotsWithTaxAdminDataInPostgresDatabase] described in the linked section;
  • lines 34–40: creation of the layers [dao] and [métier];
  • line 39: the [$this→métier] attribute references the [métier] layer
  • lines 43–49: the [test1, test2…, test11] methods are those described in the link section;

The test results are as follows:

Image

https://stahe.github.io
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.