private static void updateMultiBase() {
// Calcul automatique du ht des saisies de vente avec facture
System.err.println("Start");
// on recupere les differentes bases
ComptaPropsConfiguration instance = ComptaPropsConfiguration.create();
Configuration.setInstance(instance);
SQLBase base = Configuration.getInstance().getBase();
SQLTable tableBase = base.getTable("SOCIETE_COMMON");
SQLSelect sel = new SQLSelect(base, false);
sel.addSelect(tableBase.getField("DATABASE_NAME"));
List listBasesNX =
(List)
Configuration.getInstance()
.getBase()
.getDataSource()
.execute(sel.asString(), new ArrayListHandler());
// for (int i = 0; i < listBasesNX.size(); i++) {
// Object[] tmp = (Object[]) listBasesNX.get(i);
//
// setOrdreComptePCE(tmp[0].toString());
// }
reOrderCompteID("Default");
System.err.println("End");
}
public static void fixUserCommon(int base) {
if (Configuration.getInstance() == null) {
Configuration.setInstance(ComptaPropsConfiguration.create());
}
Configuration instance = Configuration.getInstance();
SQLTable tableSociete = Configuration.getInstance().getBase().getTable("SOCIETE_COMMON");
String baseName = tableSociete.getRow(base).getString("DATABASE_NAME");
instance.getBase().getDBSystemRoot().getRootsToMap().clear();
try {
Set<String> s = new HashSet<String>();
s.add(baseName);
instance.getBase().fetchTables(s);
} catch (SQLException e) {
throw new IllegalStateException("could not access societe base", e);
}
System.err.println("baseName" + baseName);
instance.getSystemRoot().prependToRootPath("Common");
instance.getSystemRoot().prependToRootPath(baseName);
SQLSchema baseSQL = instance.getBase().getSchema(baseName);
DatabaseMetaData dbMetaDataSociete;
try {
dbMetaDataSociete = baseSQL.getBase().getDataSource().getConnection().getMetaData();
String[] type = new String[1];
type[0] = "TABLE";
ResultSet rs = dbMetaDataSociete.getTables("", baseSQL.getName(), "%", null);
System.err.println("Start " + rs.getFetchSize());
int i = 0;
while (rs.next()) {
if (rs.getString("TABLE_TYPE") != null
&& rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
// System.err.println("FIND TABLE");
// baseSQL.getTableNames();
final SQLTable table = baseSQL.getTable(rs.getString("TABLE_NAME"));
Set<SQLField> s = table.getFields();
for (SQLField field : s) {
if (field.getName().equalsIgnoreCase("ID_USER_COMMON_CREATE")
|| field.getName().equalsIgnoreCase("ID_USER_COMMON_MODIFY")) {
Object o = field.getDefaultValue();
if (o == null || (o instanceof Integer && ((Integer) o) == 0)) {
System.err.println("Bad default on " + field);
baseSQL
.getBase()
.execute(
"ALTER TABLE \""
+ field.getTable().getSchema().getName()
+ "\".\""
+ field.getTable().getName()
+ "\" ALTER COLUMN \""
+ field.getName()
+ "\" SET DEFAULT 1;");
baseSQL
.getBase()
.execute(
"UPDATE \""
+ field.getTable().getSchema().getName()
+ "\".\""
+ field.getTable().getName()
+ "\" SET \""
+ field.getName()
+ "\"=1 WHERE \""
+ field.getName()
+ "\"=0 OR \""
+ field.getName()
+ "\" IS NULL;");
}
}
}
}
// System.err.println(i++ + " " + rs.getString("TABLE_TYPE"));
}
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Affiche si il y a des différences entre les tables de base et baseDefault
*
* @param base
* @param baseDefault
*/
public static void compareDB(int base, int baseDefault) {
try {
if (Configuration.getInstance() == null) {
Configuration.setInstance(ComptaPropsConfiguration.create());
}
Configuration instance = Configuration.getInstance();
SQLTable tableSociete = Configuration.getInstance().getBase().getTable("SOCIETE_COMMON");
String baseName = tableSociete.getRow(base).getString("DATABASE_NAME");
String baseDefaultName = tableSociete.getRow(baseDefault).getString("DATABASE_NAME");
instance.getBase().getDBSystemRoot().getRootsToMap().clear();
try {
Set<String> s = new HashSet<String>();
s.add(baseName);
s.add(baseDefaultName);
instance.getBase().fetchTables(s);
} catch (SQLException e) {
throw new IllegalStateException("could not access societe base", e);
}
// instance.getBase().getDBRoot(baseName);
// instance.getBase().getDBRoot(baseDefaultName);
System.err.println("baseName" + baseName);
System.err.println("baseDefault" + baseDefaultName);
instance.getSystemRoot().prependToRootPath("Common");
instance.getSystemRoot().prependToRootPath(baseName);
instance.getSystemRoot().prependToRootPath(baseDefaultName);
SQLSchema baseSQL = instance.getBase().getSchema(baseName);
SQLSchema baseSQLDefault = instance.getBase().getSchema(baseDefaultName);
DatabaseMetaData dbMetaDataSociete =
baseSQL.getBase().getDataSource().getConnection().getMetaData();
DatabaseMetaData dbMetaDataSocieteDefault =
baseSQLDefault.getBase().getDataSource().getConnection().getMetaData();
Map<String, Map<String, SQLField>> mapTableSociete =
new HashMap<String, Map<String, SQLField>>();
Map<String, Map<String, SQLField>> mapTableSocieteDefault =
new HashMap<String, Map<String, SQLField>>();
ResultSet rs = dbMetaDataSociete.getTables("", baseSQL.getName(), "%", null);
System.err.println("Start");
while (rs.next()) {
// System.err.println(rs.getString("TABLE_NAME") + ", TYPE ::" +
// rs.getString("TABLE_TYPE"));
if (rs.getString("TABLE_TYPE") != null
&& rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
Map<String, SQLField> m = new HashMap<String, SQLField>();
baseSQL.getTableNames();
Set<SQLField> s = baseSQL.getTable(rs.getString("TABLE_NAME")).getFields();
for (SQLField field : s) {
m.put(field.getName(), field);
}
mapTableSociete.put(rs.getString("TABLE_NAME"), m);
}
}
rs.close();
rs = dbMetaDataSocieteDefault.getTables("", baseSQLDefault.getName(), "%", null);
while (rs.next()) {
// System.err.println(rs.getString("TABLE_NAME") + ", TYPE ::" +
// rs.getString("TABLE_TYPE"));
if (rs.getString("TABLE_TYPE") != null
&& rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
Map<String, SQLField> m = new HashMap<String, SQLField>();
Set<SQLField> s = baseSQLDefault.getTable(rs.getString("TABLE_NAME")).getFields();
for (SQLField field : s) {
m.put(field.getName(), field);
}
mapTableSocieteDefault.put(rs.getString("TABLE_NAME"), m);
}
}
rs.close();
System.err.println("Test 1 " + mapTableSociete.keySet().size());
// On verifie que toutes les tables de la societe sont contenues dans la base default
for (String tableName : mapTableSociete.keySet()) {
if (!mapTableSocieteDefault.containsKey(tableName)) {
System.err.println(
"!! **** La table " + tableName + " n'est pas dans la base " + baseDefault);
} else {
Map<String, SQLField> mSoc = mapTableSociete.get(tableName);
Map<String, SQLField> mDef = mapTableSocieteDefault.get(tableName);
if (mSoc.keySet().containsAll(mDef.keySet())) {
if (mSoc.keySet().size() == mDef.keySet().size()) {
System.err.println("Table " + tableName + " --- OK");
compareTypeField(mSoc, mDef);
} else {
if (mSoc.keySet().size() > mDef.keySet().size()) {
for (String fieldName : mDef.keySet()) {
mSoc.remove(fieldName);
}
System.err.println("!! **** Difference Table " + tableName);
System.err.println(
tableSociete.getRow(baseDefault).getString("DATABASE_NAME")
+ " Set Column "
+ mSoc);
System.err.println(
getAlterTable(
mSoc,
tableSociete.getRow(baseDefault).getString("DATABASE_NAME"),
tableName));
} else {
}
}
} else {
// System.err.println("!! **** Difference Table " + tableName);
// System.err.println(tableSociete.getRow(base).getString("DATABASE_NAME") +
// " Set Column " + mapTableSociete.get(tableName));
// System.err.println(tableSociete.getRow(baseDefault).getString("DATABASE_NAME")
// + " Set Column " + mapTableSocieteDefault.get(tableName));
for (String fieldName : mSoc.keySet()) {
mDef.remove(fieldName);
}
System.err.println("!! **** Difference Table " + tableName);
System.err.println(
tableSociete.getRow(base).getString("DATABASE_NAME") + " Set Column " + mDef);
System.err.println(
getAlterTable(
mDef, tableSociete.getRow(base).getString("DATABASE_NAME"), tableName));
}
}
}
System.err.println("Test 2 " + mapTableSocieteDefault.keySet().size());
// On verifie que toutes les tables de la base default sont contenues dans la base
// societe
for (Iterator i = mapTableSocieteDefault.keySet().iterator(); i.hasNext(); ) {
Object tableName = i.next();
if (!mapTableSociete.containsKey(tableName)) {
System.err.println(
"!! **** La table " + tableName + " n'est pas dans la base " + baseDefault);
}
}
SQLSchema schem = instance.getBase().getSchema("Common");
} catch (SQLException e) {
e.printStackTrace();
}
}