create_cdp_db.sql.template

/**_____________________________________________________________________________
 *
 *                                 OATS - INAF
 *  Osservatorio Astronomico di Tireste - Istituto Nazionale di Astrofisica
 *  Astronomical Observatory of Trieste - National Institute for Astrophysics
 * ____________________________________________________________________________
 *
 * Copyright (C) 20016  Istituto Nazionale di Astrofisica
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT 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
 * along with this program; if not, write to the Free Software Foundation, Inc., 
 * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 * _____________________________________________________________________________
 **/


/*
    DataBase creation and grant assignment
*/

CREATE DATABASE YOUR_DATABASE_NAME;

CREATE USER 'YOUR_DB_USER'@'localhost' identified by 'YOUR_DB_PASSWORD';

grant all on YOUR_DATABASE_NAME.* to 'YOUR_DATABASE_USER' identified by 'YOUR_DATABASE_USER_PASSWORD';

use YOUR_DATABASE_NAME;

/*
    Credential delegation service database
*/
CREATE TABLE x509_certificates
(
    canon_dn        VARCHAR(256) NOT NULL,
    exp_date        DATETIME     default CURRENT_TIMESTAMP,
    cert_chain      TEXT,
    private_key     BLOB,
    csr             TEXT,
    hash_dn         VARCHAR(256) NOT NULL,
    PRIMARY KEY (hash_dn)

) ENGINE=InnoDB; 


/*
DB versions support:
Since MariaDB 10.0.1, DATETIME columns also accept CURRENT_TIMESTAMP as the default value.
Since MySQL 5.6.X CURRENT_TIMESTAMP is supported.
For older versions it can be set as example:
lastModified      DATETIME          default NULL
and then a trigger can be used to set a current timestamp as default. Example:

DELIMITER ;;
CREATE TRIGGER `my_trigger` BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
    SET NEW.date_to_be_set = NOW();
END;;
DELIMITER ;
*/