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();
} |
