Database convert or init

1 files changed, 83 insertions, 0 deletions

diff --git a/app-from-scratch.sh b/app-from-scratch.sh
index 4d09168..9d6ed70 100644
--- a/app-from-scratch.sh
+++ b/app-from-scratch.sh
@@ -125,3 +125,86 @@ editor config/bootstrap.php config/app.php
 editor config/app_credentials.php # choose real DB credentials now
 git add config/bootstrap.php config/app.php ../app-from-scratch.sh
 git commit -m "Config : database, salt, inflections, datetime format"
+# 7bf701764ff5661d053244b263cfe0be423faef5
+
+
+# database init
+sudo mariadb --defaults-extra-file=/etc/mysql/debian.cnf <<"EOT"
+CREATE DATABASE IF NOT EXISTS fai_gestion;
+CREATE DATABASE IF NOT EXISTS fai_gestion_exports;
+CREATE USER IF NOT EXISTS 'fai_gestion'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXXXXX';
+GRANT ALL PRIVILEGES ON  fai_gestion.* TO 'fai_gestion'@'localhost';
+FLUSH PRIVILEGES;
+EOT
+
+# database schema modifications
+# base on branch master (chd_gestion) 5709f164402a76dfd0e6ef098a45c73f976b48cd
+# Fixup list for ~/chd_gestion/schema/schema-mysql.sql
+# - sed -e 's/DEFINER=`gestion`@`localhost`/DEFINER=`fai_gestion`@`localhost`/g'
+# - change v_relais SQL SECURITY INVOKER to SECURITY DEFINER=`fai_gestion`@`localhost`
+# - move all views (v_*) in fai_gestion_exports because cake bake do bad things with them
+# - DROP TABLE tmp_mac_seen;
+# - DROP VIEW v_tmp_diff_seen_mac;
+# - ALTER TABLE adherents DROP num_adt_ttn;
+# - ALTER TABLE villes DROP tel_contact;
+# - ALTER TABLE equipement_modeles CHANGE modele title VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
+# - ALTER TABLE equipement_modeles ADD INDEX(title);
+# - DROP TRIGGER equipements_insert;
+# - DROP TRIGGER equipements_update;
+
+cat ~/chd_gestion/schema/schema-mysql.sql | sudo mariadb fai_gestion
+cat <<"EOT"
+CREATE TABLE lien_filaire (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ equipement_interface_id_1 int(11) NOT NULL,
+ port_1 char(15) NOT NULL,
+ equipement_interface_id_2 int(11) NOT NULL,
+ port_2 char(15) NOT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY equipement_interface_id_1 (equipement_interface_id_1,port_1,equipement_interface_id_2,port_2),
+ KEY equipement_interface_id_2 (equipement_interface_id_2),
+ CONSTRAINT lien_filaire_ibfk_1 FOREIGN KEY (equipement_interface_id_1) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE,
+ CONSTRAINT lien_filaire_ibfk_2 FOREIGN KEY (equipement_interface_id_2) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE
+);
+
+CREATE TABLE lien_radio (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ title varchar(64) NOT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY title (title)
+);
+
+CREATE TABLE lien_tunnel (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ endpoint_ip_1 varchar(39) NOT NULL,
+ endpoint_port_1 int(5) NOT NULL,
+ endpoint_ip_2 varchar(39) NOT NULL,
+ endpoint_port_2 int(5) NOT NULL,
+ equipement_interface_id_1 int(11) NOT NULL,
+ equipement_interface_id_2 int(11) NOT NULL,
+ tuntype varchar(15) NOT NULL DEFAULT 'tun',
+ daemon_options varchar(255) DEFAULT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY equipement_interface_id_1 (equipement_interface_id_1,equipement_interface_id_2),
+ UNIQUE KEY endpoint_ip_1 (endpoint_ip_1,endpoint_port_1,endpoint_ip_2,endpoint_port_2),
+ KEY equipement_interface_id_2 (equipement_interface_id_2),
+ CONSTRAINT lien_tunnel_ibfk_1 FOREIGN KEY (equipement_interface_id_1) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE,
+ CONSTRAINT lien_tunnel_ibfk_2 FOREIGN KEY (equipement_interface_id_2) REFERENCES equipement_interfaces (id) ON UPDATE CASCADE
+);
+EOT
+cat ~/chd_gestion/schema/views-mysql.sql | sudo mariadb fai_gestion_exports
+cat ~/chd_gestion/schema/enum-tables-data-mysql.sql | sudo mariadb fai_gestion
+(
+	echo 'INSERT INTO ippubliques (ip4, ip6, secteur_id) VALUES'
+	for i in $(seq 1 254)
+	do
+		[ $i -ne 254 ] && sep=',' || sep=';'
+		printf "('185.131.43.%i', '2a03:a0a1:1:%x::', '1')$sep\n" $i $i
+	done 
+) | sudo mariadb fai_gestion
+
+# Convert equipements.uplink tree to (equipement_interfaces, lien_cable, lien_radio, lien_tunnel)
+# - ALTER TABLE equipements DROP uplink_id;
+# - ALTER TABLE equipements CHANGE ipmgmt_id ip_management VARCHAR(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
+# - import PROCEDURE refresh_network_elt(IN root INT)
+# - TODO : work on fai_gestion_exports permissions