echo "drop database Vulcain;" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain
echo "create database Vulcain;" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR
echo "source ~baumert/SAUVEGARDE/MDB/dump_vulcain.sql;" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "load data local infile '~baumert/SAUVEGARDE/MDB/MDB_LOCALISATION.txt' into table
MDB_LOCALISATION
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
local_id,
local_serveur,
local_alias,
local_adresse_ip,
local_domaine,
local_type,local_os);
" | /env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "load data local infile '~baumert/SAUVEGARDE/MDB/MDB_FILE_SYSTEM.txt' into table
MDB_FILE_SYSTEM
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
file_id,
file_nom,
file_taille_giga,
file_espace_occupe);
" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "load data local infile '~baumert/SAUVEGARDE/MDB/MDB_SGBD.txt' into table
MDB_SGBD
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
sgbd_id,
sgbd_nom);
" | /env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "load data local infile '~baumert/SAUVEGARDE/MDB/MDB_SERVEUR_DB.txt' into table
MDB_SERVEUR_DB
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
srv_id,
srv_nom,
srv_sgbd_id,
srv_localisation_id,
srv_port,
srv_version,
srv_date_maj,
srv_serveur_db);
" | /env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

source ~baumert/SQL/reload_vulcain.sql

echo "insert into
MDB_SOURCE (
src_nom_serveur,
src_nom_dat,
src_nom_pro)
select
srv_nom,
dat_nom,
ifnull(dat_owner, 'dindon')
from MDB_SERVEUR_DB,MDB_DATABASE
where srv_id=dat_serveur_db_id;
" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
1,
\"BACKUP\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id in (1,2,3,4)
AND srv_nom != \"SERVEUR2\"
AND srv_nom != \"SERVEUR3\"
AND srv_localisation_id != \"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
1,
\"LOG\"
FROM MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id=1
AND srv_nom!=\"SERVEUR1\"
AND srv_nom != \"SERVEUR2\"
AND srv_nom != \"SERVEUR3\"
AND srv_localisation_id!=\"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
2,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE srv_sgbd_id=1
AND srv_nom!=\"SERVEUR1\"
AND srv_nom!=\"SERVEUR2\"
AND srv_nom!=\"SERVEUR3\"
AND srv_nom!=\"SERVEUR4\"
AND MDB_SERVEUR_DB.srv_id=MDB_DATABASE.dat_serveur_db_id;
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
3,
\"DATA\"
FROM MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id=3
AND srv_localisation_id!=\"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
3,
\"LOG\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id=2
AND srv_localisation_id != \"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
4,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_localisation_id!=\"4\"
AND srv_sgbd_id=\"2\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
5,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE srv_nom!=\"MONGO_SERVEUR1\"
AND srv_sgbd_id=\"4\"
AND srv_id=dat_serveur_db_id;
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
6,
\"BACKUP\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id = srv_id
AND srv_nom in (\"SERVEUR2\",\"SERVEUR3\");
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
6,
\"LOG\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id = srv_id
AND srv_nom in (\"SERVEUR2\",\"SERVEUR3\");
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
7,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id AND srv_nom=\"SERVEUR2\" AND dat_nom not in (
\"GROSSE_BASE1\",
\"lab_GROSSE_BASE1\",
\"GROSSE_BASE1_AGL\",
\"GROSSE_BASE2_AGL\",
\"GROSSE_BASE3_ZR\",
\"lab_GROSSE_BASE4\",
\"lab_GROSSE_BASE5\",
\"lab_GROSSE_BASE3\",
\"lab_GROSSE_BASE6 \",
\"lab_GROSSE_BASE7\");
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
8,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id AND srv_nom=\"SERVEUR2\" AND dat_nom in (
\"lab_GROSSE_BASE1\",
\"lab_GROSSE_BASE4\",
\"lab_GROSSE_BASE5\",
\"lab_GROSSE_BASE3\",
\"lab_GROSSE_BASE6 \",
\"lab_GROSSE_BASE7\");
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
9,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id in (1,2,3,4)
AND srv_localisation_id=\"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
10,
\"BACKUP\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id in (1,3,4)
AND srv_localisation_id = \"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
10,
\"LOG\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id
AND srv_sgbd_id=1
AND srv_localisation_id = \"4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
11,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_nom!=\"lab_GROSSE_BASE1\" AND dat_serveur_db_id=srv_id AND srv_nom=\"SERVEUR3\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
12,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id=srv_id AND srv_nom=\"SERVEUR2\" AND dat_nom in (
\"GROSSE_BASE1_AGL\",
\"GROSSE_BASE3_ZR\",
\"GROSSE_BASE2_AGL\");
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "insert into REL_DATABASE_FILE_SYSTEM
select
dat_id,
srv_id,
13,
\"DATA\"
from MDB_DATABASE,MDB_SERVEUR_DB
WHERE dat_serveur_db_id = srv_id
AND srv_nom=\"SERVEUR4\";
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "SELECT
srv_nom,
file_nom,
rel_type,
count(*) AS \"Nombre de Bases\"
FROM
MDB_SERVEUR_DB,
REL_DATABASE_FILE_SYSTEM,
MDB_FILE_SYSTEM
WHERE
srv_id=rel_file_database_serveur_db_id
AND file_id=rel_file_system_id
GROUP BY srv_nom,file_nom,rel_type
ORDER BY srv_nom,file_nom;
" |/env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain

echo "select
srv_nom,
srv_id
from MDB_SERVEUR_DB
where srv_sgbd_id=1;
" | /env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain | while read srvname srvid;
do
echo "select concat('
update MDB_DATABASE set dat_date_creation=',
'\"',
min(create_time),
'\"',
' where dat_nom=',
'\"',
table_schema,
'\"',
' AND dat_serveur_db_id=',
'\"$srvid\";')
from tables where create_time is not null group by table_schema;
" | /env/test/osef/mysql/msql -N -uutilisateur -s$srvname information_schema ;
done > ~baumert/SQL/create.sql
echo "source ~baumert/SQL/create.sql
" | /env/test/osef/mysql/msql -f -uutilisateur -sMONSERVEUR Vulcain 2>> ~baumert/LOG/importations.log ;

echo "select
srv_nom,
srv_id
from MDB_SERVEUR_DB
where srv_sgbd_id=2;
" | /env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain | while read srvname srvid ; do echo "SET NOCOUNT ON
GO
sp_helpdb

go" | /env/test/osef/sybase/sql -uutilisateur -b -n -S$srvname > ~baumert/SQL/sauvowner$srvname.sql ; awk -F' ' -v Quote="'" '{print "update MDB_DATABASE set dat_owner=" Quote $4 Quote "where dat_nom=" Quote $1 Quote " AND dat_serveur_db_id='$srvid';"}' ~baumert/SQL/sauvowner$srvname.sql > ~baumert/SQL/owner$srvname.sql ;

echo "source ~baumert/SQL/owner$srvname.sql;
" | /env/test/osef/mysql/msql -f -uutilisateur -sMONSERVEUR Vulcain 2>> ~baumert/LOG/importations.log ;
done

export PGPASSWORD=Password ; echo "select srv_nom, srv_id, srv_port from MDB_SERVEUR_DB where srv_sgbd_id=3;" | /env/test/osef/mysql/msql -N -uutilisateur -sMONSERVEUR Vulcain | while read srvname srvid port; do echo "\\l" | psql -t -uutilisateur -h$srvname postgres -p$port| sed 's/ //g' > ~baumert/SQL/sauvownerpsql$srvname.sql;

awk -F'|' -v Quote="'" '{print "update MDB_DATABASE set dat_owner=" Quote $2 Quote "where dat_nom=" Quote $1 Quote " AND dat_serveur_db_id='$srvid';"}' ~baumert/SQL/sauvownerpsql$srvname.sql > ~baumert/SQL/ownerpsql$srvname.sql ;

echo "source ~baumert/SQL/ownerpsql$srvname.sql;
" | /env/test/osef/mysql/msql -f -uutilisateur -sMONSERVEUR Vulcain 2>> ~baumert/LOG/importations.log ;
done

echo "select distinct dat_nom from MDB_DATABASE, REL_USERS_DATABASE where rel_u_dat_id=dat_id and rel_u_users_id=10000;" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain |while read datnom ; do echo "update MDB_DATABASE SET dat_owner='vmeyer' where dat_nom='$datnom';" ; done |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "UPDATE MDB_DATABASE SET dat_owner='utilisateur1' where dat_serveur_db_id='14';" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain

echo "UPDATE MDB_DATABASE SET dat_owner='utilisateur1' where dat_serveur_db_id='13';" |/env/test/osef/mysql/msql -uutilisateur -sMONSERVEUR Vulcain