1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
| package java_projet;
public class GPAO_database {
static{
try{
java.sql.DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
java.sql.Statement statement = java.sql.DriverManager.getConnection("jdbc:derby:GPAO_database;create=true").createStatement();
statement.execute("create table Article(\n"
+ "reference varchar(30),\n"
+ "designation varchar(30) not null,\n"
+ "type_fabrication_achat varchar(30) not null,\n"
+ "unite_achat_stock varchar(30) not null,\n"
+ "delai_en_semaine integer not null,\n"
+ "prix_standard float,\n"
+ "lot_de_reapprovisionnement integer,\n"
+ "stock_mini integer,\n"
+ "stock_maxi integer,\n"
+ "pourcentage_de_perte float,\n"
+ "inventaire integer,\n"
+ "PF_ou_MP_ou_Piece_ou_SE char(2),\n"
+ "constraint Article_pk primary key(reference),\n"
+ "constraint Article_unique unique(designation),\n"
+ "constraint Article_check check(PF_ou_MP_ou_Piece_ou_SE = 'PF' or PF_ou_MP_ou_Piece_ou_SE = 'MP' or PF_ou_MP_ou_Piece_ou_SE = 'Pi' or PF_ou_MP_ou_Piece_ou_SE = 'SE'))");
statement.execute("create table Lien_de_nomenclature(\n"
//-- lien_de_nomenclature_id long,
+ "compose varchar(30),\n"
+ "composant varchar(30),\n"
+ "quantite_de_composition float not null,\n"
+ "constraint Lien_de_nomenclature_pk primary key(compose,composant),\n"
+ "constraint Lien_de_nomenclature_fk_1 foreign key(compose) references Article(reference) on delete cascade,\n"
+ "constraint Lien_de_nomenclature_fk_2 foreign key(composant) references Article(reference) on delete cascade)");
//-- constraint Lien_de_nomenclature_check check(compose <> composant)
statement.execute("create table Remplacement(\n"
+ "remplace_compose varchar(30),\n"
+ "remplace_composant varchar(30),\n"
+ "remplacant_compose varchar(30),\n"
+ "remplacant_composant varchar(30),\n"
+ "date_de_remplacement date not null,\n"
//-- constraint Remplacement_pk primary key(remplace_compose,remplace_composant,remplacant_compose,remplacant_composant),
+ "constraint Remplacement_fk_1 foreign key(remplace_compose,remplace_composant) references Lien_de_nomenclature(compose,composant) on delete cascade,\n"
+ "constraint Remplacement_fk_2 foreign key(remplacant_compose,remplacant_composant) references Lien_de_nomenclature(compose,composant) on delete cascade)");
//-- constraint Remplacement_check check(remplace <> remplacant)
statement.execute("create table Poste_de_charge(\n"
+ "numero_section integer,\n"
+ "numero_sous_section integer,\n"
+ "est_machine smallint,\n"
+ "designation varchar(30) not null,\n"
+ "taux_horaire_ou_forfait integer not null,\n"
+ "nombre_de_postes integer not null,\n"
+ "capacite_nominale integer not null,\n"
+ "type_taux_horaire_ou_forfait char(2) not null,\n"
+ "constraint Poste_de_charge_pk primary key(numero_section,numero_sous_section,est_machine),\n"
+ "constraint Poste_de_charge_check_1 check(est_machine = 0 or est_machine = 1),\n"
+ "constraint Poste_de_charge_check_2 check(type_taux_horaire_ou_forfait = 'TH' or type_taux_horaire_ou_forfait = 'F'))");
statement.execute("create table Operation(\n"
+ "gamme_de_fabrication varchar(30),\n"
+ "numero_operation integer,\n"
+ "machine_numero_section integer,\n"
+ "machine_numero_sous_section integer,\n"
+ "machine_est_machine smallint,\n"
+ "main_d_oeuvre_numero_section integer,\n"
+ "main_d_oeuvre_numero_sous_section integer,\n"
+ "main_d_oeuvre_est_machine smallint,\n"
+ "temps_preparation integer,\n"
+ "temps_execution integer,\n"
+ "temps_transfert integer not null,\n"
+ "libelle_operation varchar(30) not null,\n"
+ "constraint Operation_pk primary key(gamme_de_fabrication,numero_operation),\n"
+ "constraint Operation_fk1 foreign key(gamme_de_fabrication) references Article(reference) on delete cascade,\n"
+ "constraint Operation_fk2 foreign key(machine_numero_section,machine_numero_sous_section,machine_est_machine) references Poste_de_charge(numero_section,numero_sous_section,est_machine),\n"
+ "constraint Operation_fk3 foreign key(main_d_oeuvre_numero_section,main_d_oeuvre_numero_sous_section,main_d_oeuvre_est_machine) references Poste_de_charge(numero_section,numero_sous_section,est_machine),\n"
+ "constraint Operation_check_1 check(machine_est_machine = 1),\n"
+ "constraint Operation_check_2 check(main_d_oeuvre_est_machine = 0))");
statement.execute("create table Mouvement_de_stock(\n"
+ "reference varchar(30),\n"
+ "numero_magasin integer,\n"
+ "quantite integer,\n"
+ "periode date,\n"
+ "type char(6) not null,\n"
//-- constraint Mouvement_de_stock_pk primary key(reference,periode,type),
+ "constraint Mouvement_de_stock_fk foreign key(reference) references Article(reference),\n"
+ "constraint Mouvement_de_stock_check check(type = 'Entree' or type = 'Sortie'))");
statement.execute("insert into Article values('ROUE50','roue de camion','achat par lot','unite',6,1.5,500,500,2000,null,1850,'Pi')");
statement.execute("insert into Article values('ES000','essieu monte','fabr. par lot','unite',2,null,500,750,1500,null,null,'SE')");
statement.execute("insert into Article values('CH005','chassis monte','fabr. par lot','unite',1,null,300,null,900,null,null,'SE')");
statement.execute("insert into Article values('H000','conteneur bleu','fabr. par lot','unite',1,null,150,350,800,null,null,'SE')");
statement.execute("insert into Article values('H001','conteneur bleu special','fabr. a la commande','unite',1,null,150,350,null,null,null,'SE')");
statement.execute("insert into Article values('CD100','camion demenagement bleu','pf fabr. par lot','unite',2,null,200,null,600,null,null,'PF')");
statement.execute("insert into Lien_de_nomenclature values('ES000','ROUE50',2)");
statement.execute("insert into Lien_de_nomenclature values('CH005','ES000',2)");
statement.execute("insert into Lien_de_nomenclature values('CD100','CH005',1)");
statement.execute("insert into Lien_de_nomenclature values('CD100','H000',1)");
statement.execute("insert into Lien_de_nomenclature values('CD100','H001',1)");
statement.execute("insert into Remplacement values('CD100','H000','CD100','H001',DATE('2007-09-13'))");
statement.execute("insert into Poste_de_charge values(500,450,1,'Rectifieuse',80,1,39,'TH')");
statement.execute("insert into Poste_de_charge values(500,450,0,'Rectifieur',80,1,39,'TH')");
statement.execute("insert into Operation values('ES000',020,500,450,1,500,450,0,0.5,0.05,0.2,'Rectification')");
}catch (java.sql.SQLException sqle1){
System.err.println("'GPAO_database' probably already exists? " + sqle1.getMessage());
java.sql.Connection connection;
java.sql.Statement clean_up;
try {
connection = java.sql.DriverManager.getConnection("jdbc:derby:GPAO_database");
clean_up = connection.createStatement();
clean_up.execute("delete from GoCO");
clean_up.execute("delete from GonyPO");
clean_up.execute("delete from GoaVA");
clean_up.execute("delete from GonyVA");
connection.commit();
} catch (java.sql.SQLException sqle2) {
System.err.println("'GPAO_database' persistent error: " + sqle2.getMessage());
System.exit(-1);
}
}
}
private java.sql.Connection _connection;
//private java.sql.ResultSet _available_cards;
public GPAO_database() throws java.sql.SQLException {
_connection = java.sql.DriverManager.getConnection("jdbc:derby:GPAO_database");
_connection.setAutoCommit(false);
/*_available_cards = _connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY).executeQuery("SELECT DISTINCT PLASTIC_CARD.SERIAL_NUMBER, LOGICAL_CARD.CARD_NUMBER, CLIENT.BANK_CODE, CLIENT.NAME, CLIENT.CLIENT_PIN, LOGICAL_CARD.PASSWORD, LOGICAL_CARD.WITHDRAWAL_WEEKLY_LIMIT\n"
+ "FROM PLASTIC_CARD, CLIENT, LOGICAL_CARD\n"
+ "WHERE PLASTIC_CARD.CARD_NUMBER = LOGICAL_CARD.CARD_NUMBER\n"
+ "AND LOGICAL_CARD.CLIENT_PIN = CLIENT.CLIENT_PIN");
*/
}
public java.sql.Connection activate_connection() throws Exception {
if (_connection == null) {
throw new Exception("Fatal error: JDBC not initialized");
}
return _connection;
}
public void disactivate_connection() throws Exception {
if (_connection == null) {
throw new Exception("Fatal error: JDBC not initialized");
}
_connection.commit();
_connection.close();
} |
Partager