connection = PoolManager.getInstance().getConnection();
connection.setAutoCommit(false);
// Création des tables :
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_COLLECTIVITE (TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, COL_LIBELLE varchar(100) not null, primary key (TID_CODE, COL_CLEF)) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_FONCTIONNALITE (FCT_CODE varchar(20) not null, FCT_LIBELLE varchar(100), primary key (FCT_CODE)) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_COL (UTI_ID int not null, TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, primary key (TID_CODE, UTI_ID, COL_CLEF), index AUTH_HAB_COL_UTI_FK(UTI_ID), index AUTH_HAB_COL_COL_FK(TID_CODE, COL_CLEF)) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_DOC (UTI_ID int not null, DOC_CODE varchar(100) not null, primary key (UTI_ID, DOC_CODE), index AUTH_HAB_DOC_UTI_FK(UTI_ID)) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PRF_FCT (PRF_CODE varchar(20) not null, FCT_CODE varchar(20) not null, primary key (PRF_CODE, FCT_CODE), index AUTH_PRF_FCT_PRF_FK(PRF_CODE), index AUTH_PRF_FCT_FCT_FK(FCT_CODE) ) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PROFIL (PRF_CODE varchar(20) not null, PRF_LIBELLE varchar(100) not null, primary key (PRF_CODE)) ENGINE MYISAM");
- count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = schema() and upper(table_name) = 'AUTH_UTILISATEUR'", new ColToLongRowMapper()));
- boolean tableUtilisateurExist = count > 0;
- if (! tableUtilisateurExist) {
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTILISATEUR (UTI_ID int not null AUTO_INCREMENT, UTI_LOGIN varchar(50) not null, UTI_PASSWORD varchar(50), UTI_PRENOM varchar(50), UTI_NOM varchar(50), UTI_ACTIF int not null, UTI_ALL_FONC int not null, UTI_ALL_DOC int not null, UTI_ALL_COL int not null, primary key (UTI_ID), index AUTH_UTI_LOGIN_IDX(UTI_LOGIN)) ENGINE MYISAM");
- JdbcUtils.executeUpdate(connection, "ALTER TABLE AUTH_UTILISATEUR AUTO_INCREMENT = 10");
- }
- JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTI_PRF( UTI_ID int not null, PRF_CODE varchar(20) not null, primary key (UTI_ID, PRF_CODE), index AUTH_UTI_PRF_UTI_FK(UTI_ID), index AUTH_UTI_PRF_PRF_FK(PRF_CODE)) ENGINE MYISAM");
- // Création des fonctionnalités si besoin :
- long fonctionnaliteCount = (Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_FONCTIONNALITE", new ColToLongRowMapper());
- if (fonctionnaliteCount <= 0) {
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("CONNECT").add("Connexion").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("SEARCH").add("Recherche").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("BROWSE").add("Navigation").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("IMPORT").add("Import").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("EXPORT").add("Export").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("CLEAN").add("Suppression").toPStmtBinder());
- JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
- new SimplePStmtBinderBuilder().add("USER_ADMIN").add("Administration utilisateurs").toPStmtBinder());
+ if(connection.getMetaData().getURL().toLowerCase().contains(":mysql:")) {
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_COLLECTIVITE (TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, COL_LIBELLE varchar(100) not null, primary key (TID_CODE, COL_CLEF)) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_FONCTIONNALITE (FCT_CODE varchar(20) not null, FCT_LIBELLE varchar(100), primary key (FCT_CODE)) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_COL (UTI_ID int not null, TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, primary key (TID_CODE, UTI_ID, COL_CLEF), index AUTH_HAB_COL_UTI_FK(UTI_ID), index AUTH_HAB_COL_COL_FK(TID_CODE, COL_CLEF)) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_DOC (UTI_ID int not null, DOC_CODE varchar(100) not null, primary key (UTI_ID, DOC_CODE), index AUTH_HAB_DOC_UTI_FK(UTI_ID)) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PRF_FCT (PRF_CODE varchar(20) not null, FCT_CODE varchar(20) not null, primary key (PRF_CODE, FCT_CODE), index AUTH_PRF_FCT_PRF_FK(PRF_CODE), index AUTH_PRF_FCT_FCT_FK(FCT_CODE) ) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PROFIL (PRF_CODE varchar(20) not null, PRF_LIBELLE varchar(100) not null, primary key (PRF_CODE)) ENGINE MYISAM");
+ count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = schema() and upper(table_name) = 'AUTH_UTILISATEUR'", new ColToLongRowMapper()));
+ boolean tableUtilisateurExist = count > 0;
+ if (! tableUtilisateurExist) {
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTILISATEUR (UTI_ID int not null AUTO_INCREMENT, UTI_LOGIN varchar(50) not null, UTI_PASSWORD varchar(50), UTI_PRENOM varchar(50), UTI_NOM varchar(50), UTI_ACTIF int not null, UTI_ALL_FONC int not null, UTI_ALL_DOC int not null, UTI_ALL_COL int not null, primary key (UTI_ID), index AUTH_UTI_LOGIN_IDX(UTI_LOGIN)) ENGINE MYISAM");
+ JdbcUtils.executeUpdate(connection, "ALTER TABLE AUTH_UTILISATEUR AUTO_INCREMENT = 10");
+ }
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTI_PRF( UTI_ID int not null, PRF_CODE varchar(20) not null, primary key (UTI_ID, PRF_CODE), index AUTH_UTI_PRF_UTI_FK(UTI_ID), index AUTH_UTI_PRF_PRF_FK(PRF_CODE)) ENGINE MYISAM");
+ // Création des fonctionnalités si besoin :
+ long fonctionnaliteCount = (Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_FONCTIONNALITE", new ColToLongRowMapper());
+ if (fonctionnaliteCount <= 0) {
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("CONNECT").add("Connexion").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("SEARCH").add("Recherche").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("BROWSE").add("Navigation").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("IMPORT").add("Import").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("EXPORT").add("Export").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("CLEAN").add("Suppression").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("USER_ADMIN").add("Administration utilisateurs").toPStmtBinder());
+ }
+ count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_UTILISATEUR", new ColToLongRowMapper()));
+ connection.commit();
+ } else {
+ // ORACLE
+ if(!oracleIsTableExists("AUTH_COLLECTIVITE",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_COLLECTIVITE (TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, COL_LIBELLE varchar(100) not null, primary key (TID_CODE, COL_CLEF))");
+ if(!oracleIsTableExists("AUTH_FONCTIONNALITE",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_FONCTIONNALITE (FCT_CODE varchar(20) not null, FCT_LIBELLE varchar(100), primary key (FCT_CODE))");
+ if(!oracleIsTableExists("AUTH_HAB_COL",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_HAB_COL (UTI_ID int not null, TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, constraint pk_AUTH_HAB_COL primary key (TID_CODE, UTI_ID, COL_CLEF))");
+ if(!oracleIsTableExists("AUTH_HAB_DOC",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_HAB_DOC (UTI_ID int not null, DOC_CODE varchar(100) not null, constraint pk_AUTH_HAB_DOC primary key (UTI_ID, DOC_CODE))");
+ if(!oracleIsTableExists("AUTH_PRF_FCT",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_PRF_FCT (PRF_CODE varchar(20) not null, FCT_CODE varchar(20) not null, constraint pk_AUTH_PRF_FCT primary key (PRF_CODE, FCT_CODE))");
+ if(!oracleIsTableExists("AUTH_PROFIL",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_PROFIL (PRF_CODE varchar(20) not null, PRF_LIBELLE varchar(100) not null, constraint pk_AUTH_PROFIL primary key (PRF_CODE))");
+// count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = schema() and upper(table_name) = 'AUTH_UTILISATEUR'", new ColToLongRowMapper()));
+ boolean tableUtilisateurExist = oracleIsTableExists("AUTH_UTILISATEUR",connection);
+ if (! tableUtilisateurExist) {
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_UTILISATEUR (UTI_ID int not null, UTI_LOGIN varchar(50) not null, UTI_PASSWORD varchar(50), UTI_PRENOM varchar(50), UTI_NOM varchar(50), UTI_ACTIF int not null, UTI_ALL_FONC int not null, UTI_ALL_DOC int not null, UTI_ALL_COL int not null, constraint pk_AUTH_UTILISATEUR primary key (UTI_ID))");
+ }
+ if(!oracleIsTableExists("AUTH_UTI_PRF",connection))
+ JdbcUtils.executeUpdate(connection, "CREATE TABLE AUTH_UTI_PRF( UTI_ID int not null, PRF_CODE varchar(20) not null, constraint pk_AUTH_UTI_PRF primary key (UTI_ID, PRF_CODE))");
+ // Création des fonctionnalités si besoin :
+ long fonctionnaliteCount = (Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_FONCTIONNALITE", new ColToLongRowMapper());
+ if (fonctionnaliteCount <= 0) {
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("CONNECT").add("Connexion").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("SEARCH").add("Recherche").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("BROWSE").add("Navigation").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("IMPORT").add("Import").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("EXPORT").add("Export").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("CLEAN").add("Suppression").toPStmtBinder());
+ JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
+ new SimplePStmtBinderBuilder().add("USER_ADMIN").add("Administration utilisateurs").toPStmtBinder());
+ }
+ count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_UTILISATEUR", new ColToLongRowMapper()));
+ connection.commit();
}
- count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_UTILISATEUR", new ColToLongRowMapper()));
- connection.commit();
} finally {
PoolManager.getInstance().releaseConnection(connection);
}
new RowMapper.ColToStringRowMapper(),
new SimplePStmtBinderBuilder().add(login).add(Crypter.cryptPassword(password)).add(1).toPStmtBinder()) != null;
}
+
+ static private boolean oracleIsTableExists(String tableName, Connection con) throws SQLException {
+ boolean ret = false;
+ PreparedStatement ps = con.prepareStatement("SELECT 1 FROM TAB WHERE TABTYPE='TABLE' AND TNAME=?");
+ ps.setString(1,tableName);
+ ResultSet rs = ps.executeQuery();
+ if(rs.next()) {
+ ret = true;
+ }
+ rs.close();
+ ps.close();
+ return ret;
+ }
}