1 24 package org.riotfamily.revolt.support; 25 26 import java.sql.Connection ; 27 import java.sql.DatabaseMetaData ; 28 import java.sql.ResultSet ; 29 import java.sql.SQLException ; 30 import java.util.ArrayList ; 31 import java.util.Iterator ; 32 33 import javax.sql.DataSource ; 34 35 import org.riotfamily.revolt.DatabaseOutOfSyncException; 36 import org.riotfamily.revolt.definition.Column; 37 import org.riotfamily.revolt.definition.Database; 38 import org.riotfamily.revolt.definition.Identifier; 39 import org.riotfamily.revolt.definition.Table; 40 import org.springframework.dao.DataAccessException; 41 import org.springframework.jdbc.core.ConnectionCallback; 42 import org.springframework.jdbc.core.JdbcTemplate; 43 import org.springframework.util.StringUtils; 44 45 49 public class DatabaseUtils { 50 51 54 public static String getUrl(DataSource dataSource) { 55 JdbcTemplate template = new JdbcTemplate(dataSource); 56 return (String ) template.execute(new ConnectionCallback() { 57 public Object doInConnection(Connection connection) 58 throws SQLException , DataAccessException { 59 60 DatabaseMetaData metaData = connection.getMetaData(); 61 return metaData.getURL(); 62 } 63 }); 64 } 65 66 69 public static boolean anyTablesExist(DataSource dataSource) { 70 JdbcTemplate template = new JdbcTemplate(dataSource); 71 Boolean result = (Boolean ) template.execute(new ConnectionCallback() { 72 public Object doInConnection(Connection connection) 73 throws SQLException , DataAccessException { 74 75 DatabaseMetaData metaData = connection.getMetaData(); 76 ResultSet rs = metaData.getTables(null, null, "_", null); 77 return Boolean.valueOf(rs.next()); 78 } 79 }); 80 return result.booleanValue(); 81 } 82 83 86 public static boolean tableExists(DataSource dataSource, 87 final Identifier table) { 88 89 JdbcTemplate template = new JdbcTemplate(dataSource); 90 Boolean result = (Boolean ) template.execute(new ConnectionCallback() { 91 public Object doInConnection(Connection connection) 92 throws SQLException , DataAccessException { 93 94 DatabaseMetaData metaData = connection.getMetaData(); 95 String pattern = getSearchPattern(metaData, table); 96 ResultSet rs = metaData.getTables(null, null, pattern, null); 97 return Boolean.valueOf(rs.next()); 98 } 99 }); 100 return result.booleanValue(); 101 } 102 103 106 public static void validate(DataSource dataSource, final Database model) 107 throws DatabaseOutOfSyncException { 108 109 JdbcTemplate template = new JdbcTemplate(dataSource); 110 template.execute(new ConnectionCallback() { 111 public Object doInConnection(Connection connection) 112 throws SQLException , DataAccessException { 113 114 DatabaseMetaData metaData = connection.getMetaData(); 115 validate(metaData, model); 116 return null; 117 } 118 }); 119 } 120 121 private static String getSearchPattern(DatabaseMetaData metaData, 122 Identifier identifier) throws SQLException { 123 124 String escape = metaData.getSearchStringEscape(); 125 String pattern = identifier.getName().replaceAll("_", escape + "_"); 126 127 if (identifier.isQuoted()) { 128 if (metaData.storesUpperCaseQuotedIdentifiers()) { 129 pattern = pattern.toUpperCase(); 130 } 131 else if (metaData.storesLowerCaseQuotedIdentifiers()) { 132 pattern = pattern.toLowerCase(); 133 } 134 } 135 else { 136 if (metaData.storesUpperCaseIdentifiers()) { 137 pattern = pattern.toUpperCase(); 138 } 139 else if (metaData.storesLowerCaseIdentifiers()) { 140 pattern = pattern.toLowerCase(); 141 } 142 } 143 return pattern; 144 } 145 146 private static void validate(DatabaseMetaData metaData, Database model) 147 throws SQLException , DatabaseOutOfSyncException { 148 149 Iterator it = model.getTables().iterator(); 150 while (it.hasNext()) { 151 Table table = (Table) it.next(); 152 String pattern = getSearchPattern(metaData, table); 153 ResultSet rs = metaData.getColumns(null, null, pattern, "%"); 154 ArrayList columns = new ArrayList (); 155 while (rs.next()) { 156 columns.add(new Column(rs.getString("COLUMN_NAME"))); 157 } 158 if (columns.isEmpty()) { 159 throw new DatabaseOutOfSyncException("Table " 160 + table.getName() + " does not exist"); 161 } 162 if (!columns.containsAll(table.getColumns())) { 163 ArrayList missing = new ArrayList (table.getColumns()); 164 missing.removeAll(columns); 165 throw new DatabaseOutOfSyncException("Table "+ table.getName() 166 + " does not have the following column(s): " 167 + StringUtils.collectionToCommaDelimitedString(missing)); 168 } 169 if (!table.getColumns().containsAll(columns)) { 170 ArrayList redundant = new ArrayList (columns); 171 redundant.removeAll(table.getColumns()); 172 throw new DatabaseOutOfSyncException("Table "+ table.getName() 173 + " has additionally the following unspecified column(s): " 174 + StringUtils.collectionToCommaDelimitedString(redundant)); 175 } 176 } 177 } 178 } 179 | Popular Tags |