Revision 46994
Added by Claudio Atzori almost 7 years ago
modules/cnr-enabling-database-service/branches/openaire/deploy.info | ||
---|---|---|
1 |
{"type_source": "SVN", "goal": "package -U -T 4C source:jar", "url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet45/modules/cnr-enabling-database-service/trunk/", "deploy_repository": "dnet45-snapshots", "version": "4", "mail": "sandro.labruzzo@isti.cnr.it,michele.artini@isti.cnr.it, claudio.atzori@isti.cnr.it, alessia.bardi@isti.cnr.it", "deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet45-snapshots", "name": "cnr-enabling-database-service"} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/utils/DatabaseUtilsTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.utils; |
|
2 |
|
|
3 |
import java.io.StringReader; |
|
4 |
import java.sql.Date; |
|
5 |
import java.util.Arrays; |
|
6 |
import java.util.HashMap; |
|
7 |
import java.util.List; |
|
8 |
import java.util.Map; |
|
9 |
import javax.sql.DataSource; |
|
10 |
|
|
11 |
import com.google.common.collect.Lists; |
|
12 |
import com.google.common.collect.Maps; |
|
13 |
import eu.dnetlib.enabling.database.DataSourceFactory; |
|
14 |
import eu.dnetlib.enabling.database.TransactionTemplateFactory; |
|
15 |
import eu.dnetlib.enabling.database.objects.DnetDatabase; |
|
16 |
import org.apache.velocity.app.VelocityEngine; |
|
17 |
import org.dom4j.Document; |
|
18 |
import org.dom4j.DocumentHelper; |
|
19 |
import org.dom4j.Element; |
|
20 |
import org.dom4j.io.SAXReader; |
|
21 |
import org.junit.Before; |
|
22 |
import org.junit.Ignore; |
|
23 |
import org.junit.Test; |
|
24 |
import org.junit.runner.RunWith; |
|
25 |
import org.mockito.Mock; |
|
26 |
import org.mockito.runners.MockitoJUnitRunner; |
|
27 |
import org.springframework.jdbc.core.JdbcTemplate; |
|
28 |
import org.springframework.transaction.support.TransactionCallback; |
|
29 |
import org.springframework.transaction.support.TransactionTemplate; |
|
30 |
|
|
31 |
import static org.junit.Assert.*; |
|
32 |
import static org.mockito.Matchers.any; |
|
33 |
import static org.mockito.Matchers.anyObject; |
|
34 |
import static org.mockito.Matchers.anyString; |
|
35 |
import static org.mockito.Mockito.*; |
|
36 |
|
|
37 |
@RunWith(MockitoJUnitRunner.class) |
|
38 |
public class DatabaseUtilsTest { |
|
39 |
|
|
40 |
private static final String DB = "THE_DB"; |
|
41 |
private static final String TABLE = "THE_TABLE"; |
|
42 |
private static final String QUERY = "SELECT age FROM persons"; |
|
43 |
private static final String RESOURCE_ID = "id1234"; |
|
44 |
private static final String DB_PREFIX = "THE_"; |
|
45 |
public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_"; |
|
46 |
private static final String DEFAULT_DB = "postgres"; |
|
47 |
|
|
48 |
// Class Under Test |
|
49 |
private DatabaseUtils dbUtils; |
|
50 |
|
|
51 |
private final List<Map<String, Object>> RESULTS = Lists.newArrayList(); |
|
52 |
|
|
53 |
private Map<String, Object> MAP; |
|
54 |
|
|
55 |
private final DnetDatabase DNET_DB = new DnetDatabase(); |
|
56 |
|
|
57 |
@Mock |
|
58 |
private JdbcTemplateFactory jdbcTemplateFactory; |
|
59 |
@Mock |
|
60 |
private JdbcTemplate jdbcTemplate; |
|
61 |
@Mock |
|
62 |
private DataSource dataSource; |
|
63 |
@Mock |
|
64 |
private VelocityEngine velocityEngine; |
|
65 |
@Mock |
|
66 |
private DataSourceFactory dataSourceFactory; |
|
67 |
@Mock |
|
68 |
private TransactionTemplateFactory transactionTemplateFactory; |
|
69 |
@Mock |
|
70 |
private TransactionTemplate transactionTemplate; |
|
71 |
|
|
72 |
@SuppressWarnings("unchecked") |
|
73 |
@Before |
|
74 |
public void setUp() throws Exception { |
|
75 |
dbUtils = new DatabaseUtils(); |
|
76 |
|
|
77 |
DNET_DB.setDbName(DB); |
|
78 |
|
|
79 |
MAP = new HashMap<String, Object>(); |
|
80 |
MAP.put("k1", "v1"); |
|
81 |
MAP.put("k2", "v2"); |
|
82 |
MAP.put("k3", "v3"); |
|
83 |
|
|
84 |
dbUtils.setJdbcTemplateFactory(jdbcTemplateFactory); |
|
85 |
dbUtils.setDataSourceFactory(dataSourceFactory); |
|
86 |
dbUtils.setTransactionTemplateFactory(transactionTemplateFactory); |
|
87 |
dbUtils.setVelocityEngine(velocityEngine); |
|
88 |
dbUtils.setDbPrefix(DB_PREFIX); |
|
89 |
dbUtils.setNumbersOfRecordsForTransaction(20); |
|
90 |
|
|
91 |
when(jdbcTemplateFactory.createJdbcTemplate(dataSource)).thenReturn(jdbcTemplate); |
|
92 |
when(jdbcTemplateFactory.createJdbcTemplate(DB)).thenReturn(jdbcTemplate); |
|
93 |
|
|
94 |
when(jdbcTemplate.queryForList(QUERY, Integer.class)).thenReturn(Arrays.asList(1, 2, 3, 4)); |
|
95 |
|
|
96 |
for (int i = 0; i < 4; i++) { |
|
97 |
RESULTS.add(MAP); |
|
98 |
} |
|
99 |
|
|
100 |
when(jdbcTemplate.queryForList(QUERY)).thenReturn(RESULTS); |
|
101 |
when(jdbcTemplate.queryForList("SELECT * FROM " + TABLE)).thenReturn(Arrays.asList(MAP, MAP, MAP)); |
|
102 |
|
|
103 |
when(jdbcTemplate.queryForList("SELECT * FROM information_schema.columns WHERE table_name = ?", new Object[] { TABLE })).thenReturn( |
|
104 |
Arrays.asList(MAP, MAP, MAP)); |
|
105 |
//when(jdbcTemplate.queryForList("SELECT profileid FROM dnet_tables WHERE table_name='THE_TABLE' AND database_db_name='THE_DB'", String.class)) |
|
106 |
// .thenReturn(Arrays.asList("1234")); |
|
107 |
|
|
108 |
when(dataSourceFactory.createDataSource(anyString())).thenReturn(dataSource); |
|
109 |
//when(jdbcTemplate.getDataSource()).thenReturn(dataSource); |
|
110 |
|
|
111 |
when(transactionTemplateFactory.createTransactionTemplate(dataSource)).thenReturn(transactionTemplate); |
|
112 |
|
|
113 |
} |
|
114 |
|
|
115 |
@Test |
|
116 |
public void testListCommonDBTables() throws Exception { |
|
117 |
dbUtils.listCommonDBTables(DB); |
|
118 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
119 |
verify(jdbcTemplate).queryForList( |
|
120 |
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type != 'VIEW' AND table_name NOT LIKE '%_log'", |
|
121 |
String.class); |
|
122 |
} |
|
123 |
|
|
124 |
@Test |
|
125 |
public void testListCommonDBViews() throws Exception { |
|
126 |
dbUtils.listCommonDBViews(DB); |
|
127 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
128 |
verify(jdbcTemplate).queryForList( |
|
129 |
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'VIEW' AND table_name NOT LIKE '%_log'", |
|
130 |
String.class); |
|
131 |
} |
|
132 |
|
|
133 |
@Ignore |
|
134 |
@Test |
|
135 |
public void testListAllDatabases() throws Exception { |
|
136 |
dbUtils.listAllDatabases(); |
|
137 |
verify(jdbcTemplateFactory).createJdbcTemplate(DEFAULT_DB); |
|
138 |
verify(jdbcTemplate).queryForList("SELECT datname FROM pg_database WHERE datname LIKE '" + DB_PREFIX + "%'", String.class); |
|
139 |
} |
|
140 |
|
|
141 |
@Test |
|
142 |
public void testGetTypedListFromSql() throws Exception { |
|
143 |
List<Integer> list = dbUtils.getTypedListFromSql(DB, QUERY, Integer.class); |
|
144 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
145 |
verify(jdbcTemplate).queryForList(QUERY, Integer.class); |
|
146 |
assertEquals(4, list.size()); |
|
147 |
} |
|
148 |
|
|
149 |
@Test |
|
150 |
public void testGetSimpleListFromSql() throws Exception { |
|
151 |
List<String> list = dbUtils.getSimpleListFromSql(DB, QUERY); |
|
152 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
153 |
verify(jdbcTemplate).queryForList(QUERY); |
|
154 |
assertEquals(4, list.size()); |
|
155 |
} |
|
156 |
|
|
157 |
@Test |
|
158 |
public void testExecuteSql() throws Exception { |
|
159 |
dbUtils.executeSql(DB, QUERY, null); |
|
160 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
161 |
verify(jdbcTemplate).update(QUERY); |
|
162 |
} |
|
163 |
|
|
164 |
@Test |
|
165 |
public void testDescribeTable() throws Exception { |
|
166 |
List<Map<?, ?>> res = dbUtils.describeTable(DB, TABLE); |
|
167 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
168 |
assertEquals(3, res.size()); |
|
169 |
assertEquals(3, res.get(0).size()); |
|
170 |
assertEquals(3, res.get(1).size()); |
|
171 |
assertEquals(3, res.get(2).size()); |
|
172 |
} |
|
173 |
|
|
174 |
@Test |
|
175 |
public void testDumpTableAsXML() throws Exception { |
|
176 |
String xml = dbUtils.dumpTableAsXML(DB, TABLE); |
|
177 |
|
|
178 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
179 |
assertNotNull(xml); |
|
180 |
assertTrue(xml.contains(TABLE)); |
|
181 |
assertTrue(xml.contains(DB)); |
|
182 |
assertTrue(xml.contains("ROW")); |
|
183 |
} |
|
184 |
|
|
185 |
@Test |
|
186 |
public void testDumpTableAsList() throws Exception { |
|
187 |
List<Document> list = dbUtils.dumpTableAsList(DB, TABLE); |
|
188 |
|
|
189 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
190 |
verify(jdbcTemplate).queryForList("SELECT * FROM " + TABLE); |
|
191 |
assertTrue(list.size() > 0); |
|
192 |
} |
|
193 |
|
|
194 |
@Test |
|
195 |
public void testGetRowByResourceId() throws Exception { |
|
196 |
dbUtils.getRowByResourceId(DB, TABLE, RESOURCE_ID); |
|
197 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
198 |
verify(jdbcTemplate).queryForMap("SELECT * FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID }); |
|
199 |
} |
|
200 |
|
|
201 |
@SuppressWarnings({ "unchecked", "rawtypes" }) |
|
202 |
@Test |
|
203 |
public void testImportFromIterable() throws Exception { |
|
204 |
List<String> iterable = Lists.newArrayList("<ROWS><ROW table='t1' /></ROWS>", "<ROWS><ROW table='t2' /></ROWS>"); |
|
205 |
|
|
206 |
GenericRow row1 = new GenericRow("1", TABLE, MAP, true); |
|
207 |
GenericRow row2 = new GenericRow("2", TABLE, MAP, false);; |
|
208 |
|
|
209 |
when(transactionTemplate.execute((TransactionCallback) anyObject())).thenReturn(Lists.newArrayList(row1, row2, row1, row2)); |
|
210 |
|
|
211 |
dbUtils.importFromIterable(DB, iterable); |
|
212 |
|
|
213 |
verify(jdbcTemplateFactory).createJdbcTemplate(dataSource); |
|
214 |
verify(transactionTemplateFactory).createTransactionTemplate(dataSource); |
|
215 |
|
|
216 |
verify(transactionTemplate, times(1)).execute((TransactionCallback) anyObject()); |
|
217 |
} |
|
218 |
|
|
219 |
@Test |
|
220 |
public void testDeleteRowByResourceId() throws Exception { |
|
221 |
dbUtils.deleteRowByResourceId(DB, TABLE, RESOURCE_ID); |
|
222 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
223 |
verify(jdbcTemplate).update("DELETE FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID }); |
|
224 |
} |
|
225 |
|
|
226 |
@Test |
|
227 |
public void testClearTable() throws Exception { |
|
228 |
dbUtils.clearTable(DB, TABLE); |
|
229 |
verify(jdbcTemplateFactory).createJdbcTemplate(DB); |
|
230 |
verify(jdbcTemplate).update("DELETE FROM " + TABLE); |
|
231 |
} |
|
232 |
|
|
233 |
@Ignore |
|
234 |
@SuppressWarnings("unchecked") |
|
235 |
@Test |
|
236 |
public void testPrepareManagementOfTable() throws Exception { |
|
237 |
when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) anyObject())).thenReturn(1); |
|
238 |
|
|
239 |
dbUtils.prepareManagementOfTable(DB, TABLE); |
|
240 |
verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB); |
|
241 |
verify(jdbcTemplate, atLeastOnce()).queryForObject(anyString(), Integer.class, (Object[]) anyObject()); |
|
242 |
verify(jdbcTemplate, never()).update(anyString()); |
|
243 |
} |
|
244 |
|
|
245 |
@Ignore |
|
246 |
@SuppressWarnings("unchecked") |
|
247 |
@Test |
|
248 |
public void testPrepareManagementOfTable_2() throws Exception { |
|
249 |
when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) anyObject())).thenReturn(0); |
|
250 |
|
|
251 |
dbUtils.prepareManagementOfTable(DB, TABLE); |
|
252 |
verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB); |
|
253 |
verify(jdbcTemplate, atLeastOnce()).queryForObject(anyString(), Integer.class, (Object[]) anyObject()); |
|
254 |
verify(jdbcTemplate, atLeastOnce()).update(anyString()); |
|
255 |
} |
|
256 |
|
|
257 |
@Ignore |
|
258 |
@SuppressWarnings("unchecked") |
|
259 |
@Test |
|
260 |
public void testClearManagementOfTable() throws Exception { |
|
261 |
when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) anyObject())).thenReturn(0); |
|
262 |
|
|
263 |
dbUtils.removeManagementOfTable(DB, TABLE); |
|
264 |
verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB); |
|
265 |
|
|
266 |
verify(jdbcTemplate, never()).update(anyString()); |
|
267 |
} |
|
268 |
|
|
269 |
@Ignore |
|
270 |
@Test |
|
271 |
public void testClearManagementOfTable_2() throws Exception { |
|
272 |
when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) anyObject())).thenReturn(1); |
|
273 |
dbUtils.removeManagementOfTable(DB, TABLE); |
|
274 |
verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB); |
|
275 |
|
|
276 |
verify(jdbcTemplate, atLeastOnce()).update(anyString()); |
|
277 |
} |
|
278 |
|
|
279 |
@Test |
|
280 |
public void testParseDate() throws Exception { |
|
281 |
Date d = dbUtils.parseDate("31-05-2011", "dd-MM-yyyy"); |
|
282 |
assertEquals("2011-05-31", d.toString()); |
|
283 |
} |
|
284 |
|
|
285 |
@Test |
|
286 |
public void testEscape() throws Exception { |
|
287 |
Map<String, String> m = Maps.newHashMap(); |
|
288 |
|
|
289 |
m.put("url", "http://www.google.com/set?a=val1&b=val2"); |
|
290 |
m.put("title", "My favourite movie title"); |
|
291 |
|
|
292 |
Document doc = dbUtils.rowToDocument(m); |
|
293 |
assertNotNull(doc); |
|
294 |
|
|
295 |
Document doc2 = DocumentHelper.createDocument(); |
|
296 |
Element elem = doc2.addElement("a"); |
|
297 |
elem.setText(doc.asXML()); |
|
298 |
doc2.setRootElement(elem); |
|
299 |
|
|
300 |
String tempXML = doc2.asXML(); |
|
301 |
|
|
302 |
SAXReader reader = new SAXReader(); |
|
303 |
Document doc3 = reader.read(new StringReader(tempXML)); |
|
304 |
|
|
305 |
System.out.println(doc.asXML());; |
|
306 |
System.out.println(doc3.getRootElement().getText());; |
|
307 |
|
|
308 |
assertEquals(doc.asXML(), doc3.getRootElement().getText()); |
|
309 |
|
|
310 |
} |
|
311 |
|
|
312 |
@Test |
|
313 |
public void testParseBoolean() throws Exception { |
|
314 |
assertEquals(dbUtils.parseBoolean("TRUE"), true); |
|
315 |
assertEquals(dbUtils.parseBoolean("true"), true); |
|
316 |
assertEquals(dbUtils.parseBoolean("T"), true); |
|
317 |
assertEquals(dbUtils.parseBoolean("Yes"), true); |
|
318 |
assertEquals(dbUtils.parseBoolean("false"), false); |
|
319 |
assertEquals(dbUtils.parseBoolean("F"), false); |
|
320 |
assertEquals(dbUtils.parseBoolean("No"), false); |
|
321 |
assertEquals(dbUtils.parseBoolean("N/A"), false); |
|
322 |
assertEquals(dbUtils.parseBoolean("XYZ"), false); |
|
323 |
} |
|
324 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/utils/JdbcTemplateFactoryTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.utils; |
|
2 |
|
|
3 |
import static org.junit.Assert.assertEquals; |
|
4 |
import static org.mockito.Mockito.verify; |
|
5 |
import static org.mockito.Mockito.when; |
|
6 |
|
|
7 |
import javax.sql.DataSource; |
|
8 |
|
|
9 |
import org.junit.Before; |
|
10 |
import org.junit.Test; |
|
11 |
import org.junit.runner.RunWith; |
|
12 |
import org.mockito.Mock; |
|
13 |
import org.mockito.runners.MockitoJUnitRunner; |
|
14 |
import org.springframework.jdbc.core.JdbcTemplate; |
|
15 |
|
|
16 |
import eu.dnetlib.enabling.database.DataSourceFactory; |
|
17 |
|
|
18 |
@RunWith(MockitoJUnitRunner.class) |
|
19 |
public class JdbcTemplateFactoryTest { |
|
20 |
|
|
21 |
private static final String VALID_DB = "db01"; |
|
22 |
|
|
23 |
// Class under test |
|
24 |
private JdbcTemplateFactory factory; |
|
25 |
|
|
26 |
@Mock |
|
27 |
private DataSourceFactory dataSourceFactory; |
|
28 |
|
|
29 |
@Mock |
|
30 |
private DataSource dataSource; |
|
31 |
|
|
32 |
@Before |
|
33 |
public void setUp() throws Exception { |
|
34 |
factory = new JdbcTemplateFactory(); |
|
35 |
factory.setDataSourceFactory(dataSourceFactory); |
|
36 |
when(dataSourceFactory.createDataSource(VALID_DB)).thenReturn(dataSource); |
|
37 |
} |
|
38 |
|
|
39 |
@Test |
|
40 |
public void testCreateJdbcTemplate() { |
|
41 |
JdbcTemplate t = factory.createJdbcTemplate(VALID_DB); |
|
42 |
verify(dataSourceFactory).createDataSource(VALID_DB); |
|
43 |
assertEquals(dataSource, t.getDataSource()); |
|
44 |
} |
|
45 |
|
|
46 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/DataSourceFactoryImplTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database; |
|
2 |
|
|
3 |
import static org.junit.Assert.assertNotNull; |
|
4 |
|
|
5 |
import java.sql.SQLException; |
|
6 |
|
|
7 |
import javax.sql.DataSource; |
|
8 |
|
|
9 |
import org.junit.Before; |
|
10 |
import org.junit.Ignore; |
|
11 |
import org.junit.Test; |
|
12 |
import org.junit.runner.RunWith; |
|
13 |
import org.mockito.runners.MockitoJUnitRunner; |
|
14 |
|
|
15 |
@RunWith(MockitoJUnitRunner.class) |
|
16 |
@Ignore |
|
17 |
public class DataSourceFactoryImplTest { |
|
18 |
|
|
19 |
// Class under test |
|
20 |
private DataSourceFactoryImpl factory; |
|
21 |
|
|
22 |
@Before |
|
23 |
public void setUp() throws Exception { |
|
24 |
factory = new DataSourceFactoryImpl(); |
|
25 |
factory.setBaseUrl("jdbc:postgresql://localhost:5432"); |
|
26 |
factory.setDriverClassName("org.postgresql.Driver"); |
|
27 |
factory.setUsername("dnet"); |
|
28 |
factory.setPassword("dnetPwd"); |
|
29 |
} |
|
30 |
|
|
31 |
@Test |
|
32 |
public void testCreateDataSource_1() throws SQLException { |
|
33 |
DataSource ds = factory.createDataSource(""); |
|
34 |
assertNotNull(ds); |
|
35 |
assertNotNull(ds.getConnection()); |
|
36 |
} |
|
37 |
|
|
38 |
@Test |
|
39 |
public void testCreateDataSource_2() throws SQLException { |
|
40 |
DataSource ds = factory.createDataSource("postgres"); |
|
41 |
assertNotNull(ds); |
|
42 |
assertNotNull(ds.getConnection()); |
|
43 |
} |
|
44 |
|
|
45 |
@Test(expected = SQLException.class) |
|
46 |
public void testCreateDataSource_error() throws SQLException { |
|
47 |
DataSource ds = factory.createDataSource("_INVALID_DATABASE_"); |
|
48 |
assertNotNull(ds); |
|
49 |
assertNotNull(ds.getConnection()); |
|
50 |
} |
|
51 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/DatabaseServiceImplTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database; |
|
2 |
|
|
3 |
import java.util.Date; |
|
4 |
|
|
5 |
import eu.dnetlib.enabling.database.rmi.DatabaseException; |
|
6 |
import org.junit.Before; |
|
7 |
import org.junit.Test; |
|
8 |
import org.junit.runner.RunWith; |
|
9 |
import org.mockito.Mock; |
|
10 |
import org.mockito.runners.MockitoJUnitRunner; |
|
11 |
|
|
12 |
import static org.mockito.Mockito.verify; |
|
13 |
|
|
14 |
@RunWith(MockitoJUnitRunner.class) |
|
15 |
public class DatabaseServiceImplTest { |
|
16 |
|
|
17 |
private static final String DBNAME = "THE_DB"; |
|
18 |
private static final String TABLENAME = "THE_TABLE"; |
|
19 |
|
|
20 |
// Class under test |
|
21 |
private DatabaseServiceImpl impl; |
|
22 |
@Mock |
|
23 |
private DatabaseServiceCore core; |
|
24 |
|
|
25 |
@Before |
|
26 |
public void setUp() throws Exception { |
|
27 |
impl = new DatabaseServiceImpl(); |
|
28 |
impl.setCore(core); |
|
29 |
} |
|
30 |
|
|
31 |
@Test |
|
32 |
public void testDumpTable() throws DatabaseException { |
|
33 |
impl.dumpTable(DBNAME, TABLENAME); |
|
34 |
verify(core).generateResultSet(DBNAME, TABLENAME, null); |
|
35 |
} |
|
36 |
|
|
37 |
@Test |
|
38 |
public void testDumpTableAndLogs() throws DatabaseException { |
|
39 |
Date d1 = new Date(100000); |
|
40 |
Date d2 = new Date(200000); |
|
41 |
impl.dumpTableAndLogs(DBNAME, TABLENAME, d1, d2); |
|
42 |
verify(core).generateResultSet(DBNAME, TABLENAME, d1, d2); |
|
43 |
} |
|
44 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/resultset/SQLResultSetListenerTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.resultset; |
|
2 |
|
|
3 |
import static org.junit.Assert.assertEquals; |
|
4 |
import static org.mockito.Matchers.anyMap; |
|
5 |
import static org.mockito.Matchers.anyString; |
|
6 |
import static org.mockito.Matchers.eq; |
|
7 |
import static org.mockito.Mockito.times; |
|
8 |
import static org.mockito.Mockito.verify; |
|
9 |
import static org.mockito.Mockito.when; |
|
10 |
|
|
11 |
import java.util.List; |
|
12 |
|
|
13 |
import org.dom4j.Document; |
|
14 |
import org.junit.Before; |
|
15 |
import org.junit.Test; |
|
16 |
import org.junit.runner.RunWith; |
|
17 |
import org.mockito.Mock; |
|
18 |
import org.mockito.runners.MockitoJUnitRunner; |
|
19 |
import org.springframework.jdbc.support.rowset.SqlRowSet; |
|
20 |
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData; |
|
21 |
|
|
22 |
import eu.dnetlib.enabling.database.rmi.DatabaseException; |
|
23 |
import eu.dnetlib.enabling.database.utils.DatabaseUtils; |
|
24 |
|
|
25 |
@RunWith(MockitoJUnitRunner.class) |
|
26 |
public class SQLResultSetListenerTest { |
|
27 |
|
|
28 |
// Class under test |
|
29 |
private SQLResultSetListener listener; |
|
30 |
|
|
31 |
private static final int SIZE = 123; |
|
32 |
private static final int FROM = 10; |
|
33 |
private static final int TO = 12; |
|
34 |
private static final String DB = "THE_DB"; |
|
35 |
private static final String SQL = "SELECT * FROM test"; |
|
36 |
private static final String SQL_FROM_TO = SQL + " OFFSET " + (FROM - 1); |
|
37 |
private static final String XML = "<AAAA />"; |
|
38 |
|
|
39 |
@Mock |
|
40 |
private DatabaseUtils dbUtils; |
|
41 |
|
|
42 |
@Mock |
|
43 |
private Document simpleResponseDoc; |
|
44 |
@Mock |
|
45 |
private SqlRowSet ROWSET; |
|
46 |
@Mock |
|
47 |
private SqlRowSetMetaData ROWMETADATA; |
|
48 |
|
|
49 |
@Before |
|
50 |
public void setUp() throws Exception { |
|
51 |
listener = new SQLResultSetListener(); |
|
52 |
listener.setDb(DB); |
|
53 |
listener.setDbUtils(dbUtils); |
|
54 |
listener.setSql(SQL); |
|
55 |
|
|
56 |
when(dbUtils.executeSql(eq(DB), anyString(), eq(Integer.class))).thenReturn(SIZE); |
|
57 |
when(dbUtils.executeSql(DB, SQL, SqlRowSet.class)).thenReturn(ROWSET); |
|
58 |
when(dbUtils.executeSql(DB, SQL_FROM_TO, SqlRowSet.class)).thenReturn(ROWSET); |
|
59 |
when(ROWSET.next()).thenReturn(true); |
|
60 |
when(ROWSET.getMetaData()).thenReturn(ROWMETADATA); |
|
61 |
when(ROWMETADATA.getColumnNames()).thenReturn(new String[] { "col1,col2,col3" }); |
|
62 |
when(ROWSET.getObject(anyString())).thenReturn("val_XXX"); |
|
63 |
when(dbUtils.rowToDocument(anyMap())).thenReturn(simpleResponseDoc); |
|
64 |
when(simpleResponseDoc.asXML()).thenReturn(XML); |
|
65 |
} |
|
66 |
|
|
67 |
@Test |
|
68 |
public void testGetResult_1() throws DatabaseException { |
|
69 |
listener.getResult(1, FROM - 1); |
|
70 |
listener.getResult(FROM, TO); |
|
71 |
|
|
72 |
verify(dbUtils).executeSql(DB, SQL, SqlRowSet.class); |
|
73 |
verify(ROWSET, times(TO + 2)).next(); |
|
74 |
verify(ROWSET, times(1)).previous(); |
|
75 |
} |
|
76 |
|
|
77 |
@Test |
|
78 |
public void testGetResult_3() throws DatabaseException { |
|
79 |
List<String> response = listener.getResult(FROM, TO); |
|
80 |
|
|
81 |
verify(dbUtils).executeSql(DB, SQL_FROM_TO, SqlRowSet.class); |
|
82 |
|
|
83 |
assertEquals(3, response.size()); |
|
84 |
for (String s : response) { |
|
85 |
assertEquals(XML, s); |
|
86 |
} |
|
87 |
} |
|
88 |
|
|
89 |
@Test(expected = IllegalArgumentException.class) |
|
90 |
public void testGetResult_err1() { |
|
91 |
listener.getResult(0, 0); |
|
92 |
} |
|
93 |
|
|
94 |
@Test(expected = IllegalArgumentException.class) |
|
95 |
public void testGetResult_err2() { |
|
96 |
listener.getResult(-4, 2); |
|
97 |
} |
|
98 |
|
|
99 |
@Test(expected = IllegalArgumentException.class) |
|
100 |
public void testGetResult_err3() { |
|
101 |
listener.getResult(1, -1); |
|
102 |
} |
|
103 |
|
|
104 |
@Test(expected = IllegalArgumentException.class) |
|
105 |
public void testGetResult_err4() { |
|
106 |
listener.getResult(10, 4); |
|
107 |
} |
|
108 |
|
|
109 |
@Test |
|
110 |
public void testGetSize() { |
|
111 |
assertEquals(SIZE, listener.getSize()); |
|
112 |
} |
|
113 |
|
|
114 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/resultset/SQLResultSetListenerFactoryTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.resultset; |
|
2 |
|
|
3 |
import static org.junit.Assert.assertEquals; |
|
4 |
import static org.junit.Assert.assertNotNull; |
|
5 |
import static org.mockito.Matchers.anyMap; |
|
6 |
import static org.mockito.Matchers.eq; |
|
7 |
import static org.mockito.Mockito.when; |
|
8 |
|
|
9 |
import java.util.Date; |
|
10 |
import java.util.Map; |
|
11 |
import java.util.concurrent.BlockingQueue; |
|
12 |
|
|
13 |
import org.junit.Before; |
|
14 |
import org.junit.Test; |
|
15 |
import org.junit.runner.RunWith; |
|
16 |
import org.mockito.Mock; |
|
17 |
import org.mockito.runners.MockitoJUnitRunner; |
|
18 |
|
|
19 |
import eu.dnetlib.enabling.database.rmi.DatabaseException; |
|
20 |
import eu.dnetlib.enabling.database.utils.DatabaseUtils; |
|
21 |
import eu.dnetlib.enabling.resultset.ResultSetListener; |
|
22 |
|
|
23 |
@RunWith(MockitoJUnitRunner.class) |
|
24 |
public class SQLResultSetListenerFactoryTest { |
|
25 |
|
|
26 |
// Class under test. |
|
27 |
private SQLResultSetListenerFactory factory; |
|
28 |
|
|
29 |
private static final String DB = "THE_DB"; |
|
30 |
private static final String TABLE = "THE_TABLE"; |
|
31 |
|
|
32 |
@Mock |
|
33 |
private DatabaseUtils dbUtils; |
|
34 |
|
|
35 |
@Mock |
|
36 |
private BlockingQueue<Map<String, Object>> queue; |
|
37 |
|
|
38 |
@Mock |
|
39 |
private Date fromDATE; |
|
40 |
@Mock |
|
41 |
private Date untilDATE; |
|
42 |
|
|
43 |
@Before |
|
44 |
public void setUp() throws Exception { |
|
45 |
factory = new SQLResultSetListenerFactory(); |
|
46 |
factory.setDbUtils(dbUtils); |
|
47 |
} |
|
48 |
|
|
49 |
@Test |
|
50 |
public void testCreateSQLListener() throws DatabaseException { |
|
51 |
String query = "SELECT * FROM test "; |
|
52 |
String countquery = "SELECT 100"; |
|
53 |
|
|
54 |
when(dbUtils.executeSql(DB, countquery, Integer.class)).thenReturn(100); |
|
55 |
when(dbUtils.executeSql(DB, query, BlockingQueue.class)).thenReturn(queue); |
|
56 |
|
|
57 |
ResultSetListener listener = factory.createSQLListenerWithSize(DB, query, countquery); |
|
58 |
|
|
59 |
assertNotNull(listener); |
|
60 |
} |
|
61 |
|
|
62 |
@Test |
|
63 |
public void testCreateTableListener() { |
|
64 |
SQLResultSetListener listener = (SQLResultSetListener) factory.createTableListener(DB, TABLE); |
|
65 |
|
|
66 |
assertEquals(listener.getSql(), "SELECT * FROM " + TABLE); |
|
67 |
assertEquals(dbUtils, listener.getDbUtils()); |
|
68 |
assertEquals(DB, listener.getDb()); |
|
69 |
} |
|
70 |
|
|
71 |
@Test |
|
72 |
public void testCreateCondTableListener() { |
|
73 |
String cond = "title='XYZ'"; |
|
74 |
|
|
75 |
SQLResultSetListener listener = (SQLResultSetListener) factory.createCondTableListener(DB, TABLE, cond); |
|
76 |
|
|
77 |
assertEquals(listener.getSql(), "SELECT * FROM " + TABLE + " WHERE " + cond); |
|
78 |
assertEquals(dbUtils, listener.getDbUtils()); |
|
79 |
assertEquals(DB, listener.getDb()); |
|
80 |
} |
|
81 |
|
|
82 |
@SuppressWarnings("unchecked") |
|
83 |
@Test |
|
84 |
public void testCreateLoggedTableListener() { |
|
85 |
String sql = "SELECT * FROM ..."; |
|
86 |
|
|
87 |
when(dbUtils.getSQLFromTemplate(eq("loggedTable"), eq(DB), eq(TABLE), anyMap())).thenReturn(sql); |
|
88 |
|
|
89 |
SQLResultSetListener listener = (SQLResultSetListener) factory.createLoggedTableListener(DB, TABLE, fromDATE, untilDATE); |
|
90 |
|
|
91 |
assertEquals(listener.getSql(), sql); |
|
92 |
assertEquals(dbUtils, listener.getDbUtils()); |
|
93 |
assertEquals(DB, listener.getDb()); |
|
94 |
} |
|
95 |
|
|
96 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/test/java/eu/dnetlib/enabling/database/DatabaseServiceCoreTest.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database; |
|
2 |
|
|
3 |
import static org.junit.Assert.assertEquals; |
|
4 |
import static org.junit.Assert.assertFalse; |
|
5 |
import static org.junit.Assert.assertTrue; |
|
6 |
import static org.mockito.Mockito.verify; |
|
7 |
import static org.mockito.Mockito.when; |
|
8 |
|
|
9 |
import java.util.Arrays; |
|
10 |
import java.util.Date; |
|
11 |
import java.util.HashMap; |
|
12 |
import java.util.List; |
|
13 |
import java.util.Map; |
|
14 |
|
|
15 |
import org.junit.Before; |
|
16 |
import org.junit.Ignore; |
|
17 |
import org.junit.Test; |
|
18 |
import org.junit.runner.RunWith; |
|
19 |
import org.mockito.Mock; |
|
20 |
import org.mockito.runners.MockitoJUnitRunner; |
|
21 |
|
|
22 |
import eu.dnetlib.enabling.database.objects.DnetDatabase; |
|
23 |
import eu.dnetlib.enabling.database.resultset.SQLResultSetListenerFactory; |
|
24 |
import eu.dnetlib.enabling.database.utils.DatabaseUtils; |
|
25 |
import eu.dnetlib.enabling.database.utils.GenericRow; |
|
26 |
import eu.dnetlib.enabling.resultset.ResultSetFactory; |
|
27 |
import eu.dnetlib.enabling.resultset.ResultSetListener; |
|
28 |
|
|
29 |
@RunWith(MockitoJUnitRunner.class) |
|
30 |
public class DatabaseServiceCoreTest { |
|
31 |
|
|
32 |
private static final String DB_NAME = "cinema"; |
|
33 |
private static final String TABLE_NAME = "films"; |
|
34 |
private static final String TABLE_WHERE = "year=1980"; |
|
35 |
private static final Date TABLE_FROM = new Date(100000); |
|
36 |
private static final Date TABLE_UNTIL = new Date(200000); |
|
37 |
private static final String SQL = "SELECT * from films"; |
|
38 |
|
|
39 |
// Class under test |
|
40 |
private DatabaseServiceCore core; |
|
41 |
|
|
42 |
@Mock |
|
43 |
private DatabaseUtils dbUtils; |
|
44 |
|
|
45 |
@Mock |
|
46 |
private ResultSetFactory resultSetFactory; |
|
47 |
@Mock |
|
48 |
private SQLResultSetListenerFactory resultSetListenerFactory; |
|
49 |
@Mock |
|
50 |
private ResultSetListener listener_where; |
|
51 |
@Mock |
|
52 |
private ResultSetListener listener_from_until; |
|
53 |
@Mock |
|
54 |
private ResultSetListener listener_sql; |
|
55 |
@Mock |
|
56 |
private GenericRow ROW; |
|
57 |
|
|
58 |
private final Map<String, Object> ROW_FIELDS = new HashMap<String, Object>(); |
|
59 |
|
|
60 |
@Before |
|
61 |
public void setUp() throws Exception { |
|
62 |
core = new DatabaseServiceCore(); |
|
63 |
core.setDbUtils(dbUtils); |
|
64 |
|
|
65 |
core.setResultSetFactory(resultSetFactory); |
|
66 |
core.setResultSetListenerFactory(resultSetListenerFactory); |
|
67 |
|
|
68 |
ROW_FIELDS.put("name", "Pippo"); |
|
69 |
ROW_FIELDS.put("age", 36); |
|
70 |
|
|
71 |
when(dbUtils.listCommonDBTables(DB_NAME)).thenReturn(Arrays.asList("T1", "T2", "T3"));; |
|
72 |
|
|
73 |
when(resultSetListenerFactory.createCondTableListener(DB_NAME, TABLE_NAME, TABLE_WHERE)).thenReturn(listener_where); |
|
74 |
when(resultSetListenerFactory.createLoggedTableListener(DB_NAME, TABLE_NAME, TABLE_FROM, TABLE_UNTIL)).thenReturn(listener_from_until); |
|
75 |
when(resultSetListenerFactory.createSQLListener(DB_NAME, SQL)).thenReturn(listener_sql); |
|
76 |
|
|
77 |
//when(ROW.getTable()).thenReturn(TABLE_NAME); |
|
78 |
//when(ROW.getFields()).thenReturn(ROW_FIELDS); |
|
79 |
} |
|
80 |
|
|
81 |
@Ignore |
|
82 |
@Test |
|
83 |
public void testListDatabases() throws Exception { |
|
84 |
List<DnetDatabase> list = core.listDatabases(); |
|
85 |
verify(dbUtils).listAllDatabases(); |
|
86 |
assertEquals("List size invalid", 5, list.size()); |
|
87 |
|
|
88 |
for (DnetDatabase db : list) { |
|
89 |
if (db.getDbName().startsWith("M")) { |
|
90 |
assertTrue("Db should be managed", db.isManaged()); |
|
91 |
} else { |
|
92 |
assertFalse("Db should be not managed", db.isManaged()); |
|
93 |
} |
|
94 |
} |
|
95 |
|
|
96 |
} |
|
97 |
|
|
98 |
@Test |
|
99 |
public void testChangeDatabaseStatus_manage() throws Exception { |
|
100 |
core.changeDatabaseStatus(DB_NAME, true); |
|
101 |
verify(dbUtils).listCommonDBTables(DB_NAME); |
|
102 |
verify(dbUtils).setManaged(DB_NAME, true); |
|
103 |
} |
|
104 |
|
|
105 |
@Test |
|
106 |
public void testChangeDatabaseStatus_unmanage() throws Exception { |
|
107 |
core.changeDatabaseStatus(DB_NAME, false); |
|
108 |
verify(dbUtils).listCommonDBTables(DB_NAME); |
|
109 |
verify(dbUtils).setManaged(DB_NAME, false); |
|
110 |
} |
|
111 |
|
|
112 |
@Test |
|
113 |
public void testGenerateResultSet_1() throws Exception { |
|
114 |
core.generateResultSet(DB_NAME, SQL); |
|
115 |
verify(resultSetFactory).createResultSet(listener_sql); |
|
116 |
} |
|
117 |
|
|
118 |
@Test |
|
119 |
public void testGenerateResultSet_2() throws Exception { |
|
120 |
core.generateResultSet(DB_NAME, TABLE_NAME, TABLE_WHERE); |
|
121 |
verify(resultSetFactory).createResultSet(listener_where); |
|
122 |
} |
|
123 |
|
|
124 |
@Test |
|
125 |
public void testGenerateResultSet_3() throws Exception { |
|
126 |
core.generateResultSet(DB_NAME, TABLE_NAME, TABLE_FROM, TABLE_UNTIL); |
|
127 |
verify(resultSetFactory).createResultSet(listener_from_until); |
|
128 |
} |
|
129 |
|
|
130 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/main/java/eu/dnetlib/enabling/database/utils/GenericRow.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.utils; |
|
2 |
|
|
3 |
import java.util.Map; |
|
4 |
|
|
5 |
public class GenericRow { |
|
6 |
private String datasourceId; |
|
7 |
private String table; |
|
8 |
private Map<String, Object> fields; |
|
9 |
private boolean toDelete = false; |
|
10 |
|
|
11 |
public GenericRow(final String datasourceId, String table, Map<String, Object> fields, boolean toDelete) { |
|
12 |
this.datasourceId = datasourceId; |
|
13 |
this.table = table; |
|
14 |
this.fields = fields; |
|
15 |
this.toDelete = toDelete; |
|
16 |
} |
|
17 |
|
|
18 |
public String getDatasourceId() { |
|
19 |
return datasourceId; |
|
20 |
} |
|
21 |
|
|
22 |
public void setDatasourceId(final String datasourceId) { |
|
23 |
this.datasourceId = datasourceId; |
|
24 |
} |
|
25 |
|
|
26 |
public String getTable() { |
|
27 |
return table; |
|
28 |
} |
|
29 |
|
|
30 |
public void setTable(String table) { |
|
31 |
this.table = table; |
|
32 |
} |
|
33 |
|
|
34 |
public Map<String, Object> getFields() { |
|
35 |
return fields; |
|
36 |
} |
|
37 |
|
|
38 |
public void setFields(Map<String, Object> fields) { |
|
39 |
this.fields = fields; |
|
40 |
} |
|
41 |
|
|
42 |
public boolean isToDelete() { |
|
43 |
return toDelete; |
|
44 |
} |
|
45 |
|
|
46 |
public void setToDelete(boolean toDelete) { |
|
47 |
this.toDelete = toDelete; |
|
48 |
} |
|
49 |
|
|
50 |
} |
modules/cnr-enabling-database-service/branches/openaire/src/main/java/eu/dnetlib/enabling/database/utils/DatabaseUtils.java | ||
---|---|---|
1 |
package eu.dnetlib.enabling.database.utils; |
|
2 |
|
|
3 |
import java.io.StringReader; |
|
4 |
import java.sql.Array; |
|
5 |
import java.sql.Date; |
|
6 |
import java.sql.ResultSetMetaData; |
|
7 |
import java.text.ParseException; |
|
8 |
import java.text.SimpleDateFormat; |
|
9 |
import java.util.*; |
|
10 |
import java.util.concurrent.ArrayBlockingQueue; |
|
11 |
import java.util.concurrent.BlockingQueue; |
|
12 |
import java.util.concurrent.Executors; |
|
13 |
import java.util.concurrent.TimeUnit; |
|
14 |
import java.util.concurrent.atomic.AtomicReference; |
|
15 |
import java.util.regex.Pattern; |
|
16 |
import javax.sql.DataSource; |
|
17 |
|
|
18 |
import com.google.common.collect.Lists; |
|
19 |
import com.google.common.collect.Queues; |
|
20 |
import com.google.common.collect.Sets; |
|
21 |
import eu.dnetlib.enabling.database.DataSourceFactory; |
|
22 |
import eu.dnetlib.enabling.database.TransactionTemplateFactory; |
|
23 |
import eu.dnetlib.enabling.database.objects.DnetDatabase; |
|
24 |
import eu.dnetlib.enabling.database.rmi.DatabaseException; |
|
25 |
import eu.dnetlib.miscutils.datetime.DateUtils; |
|
26 |
import eu.dnetlib.miscutils.functional.string.Sanitizer; |
|
27 |
import org.apache.commons.lang.BooleanUtils; |
|
28 |
import org.apache.commons.lang.StringUtils; |
|
29 |
import org.apache.commons.logging.Log; |
|
30 |
import org.apache.commons.logging.LogFactory; |
|
31 |
import org.apache.velocity.app.VelocityEngine; |
|
32 |
import org.dom4j.Document; |
|
33 |
import org.dom4j.DocumentHelper; |
|
34 |
import org.dom4j.Element; |
|
35 |
import org.dom4j.Node; |
|
36 |
import org.dom4j.io.SAXReader; |
|
37 |
import org.joda.time.DateTime; |
|
38 |
import org.joda.time.format.ISODateTimeFormat; |
|
39 |
import org.springframework.beans.factory.annotation.Required; |
|
40 |
import org.springframework.dao.DataAccessException; |
|
41 |
import org.springframework.jdbc.core.JdbcTemplate; |
|
42 |
import org.springframework.jdbc.support.rowset.SqlRowSet; |
|
43 |
import org.springframework.transaction.support.TransactionTemplate; |
|
44 |
import org.springframework.ui.velocity.VelocityEngineUtils; |
|
45 |
|
|
46 |
public class DatabaseUtils { |
|
47 |
|
|
48 |
public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_"; |
|
49 |
public static final int BLOCKING_QUEUE_TIMEOUT = 300; |
|
50 |
private static final String SQL_DATE_FORMAT = "yyyy-MM-dd"; |
|
51 |
private static final Log log = LogFactory.getLog(DatabaseUtils.class); // NOPMD by marko on 11/24/08 5:02 PM |
|
52 |
private static final int BLOCKING_QUEUE_SIZE = 200; |
|
53 |
private static final Set<String> TRUE_VALUES = Sets.newHashSet("true", "t", "yes", "y", "vero", "v"); |
|
54 |
private static final Set<String> FALSE_VALUES = Sets.newHashSet("false", "f", "no", "n", "falso"); |
|
55 |
private DataSourceFactory dataSourceFactory; |
|
56 |
private JdbcTemplateFactory jdbcTemplateFactory; |
|
57 |
private TransactionTemplateFactory transactionTemplateFactory; |
|
58 |
private String defaultDB; |
|
59 |
private VelocityEngine velocityEngine; |
|
60 |
private String dbPrefix; |
|
61 |
private int numbersOfRecordsForTransaction; |
|
62 |
|
|
63 |
public List<String> listCommonDBTables(final String database) throws DatabaseException { |
|
64 |
String query = |
|
65 |
"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type != 'VIEW' " |
|
66 |
+ "AND table_name NOT LIKE '%_log'"; |
|
67 |
return getTypedListFromSql(database, query, String.class); |
|
68 |
} |
|
69 |
|
|
70 |
public List<String> listCommonDBViews(final String database) throws DatabaseException { |
|
71 |
String query = |
|
72 |
"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type = 'VIEW' " |
|
73 |
+ "AND table_name NOT LIKE '%_log'"; |
|
74 |
return getTypedListFromSql(database, query, String.class); |
|
75 |
} |
|
76 |
|
|
77 |
public List<DnetDatabase> listAllDatabases() throws DatabaseException { |
|
78 |
final String query = |
|
79 |
"SELECT d.datname AS db, COALESCE(dsc.description,'')='isManaged' AS managed FROM pg_database d LEFT OUTER JOIN pg_shdescription dsc ON (d.oid = dsc.objoid) WHERE d.datname LIKE '" |
|
80 |
+ dbPrefix + "%' ORDER BY d.datname DESC"; |
|
81 |
final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(defaultDB); |
|
82 |
|
|
83 |
final List<DnetDatabase> list = Lists.newArrayList(); |
|
84 |
for (Map<String, Object> map : jdbcTemplate.queryForList(query)) { |
|
85 |
list.add(new DnetDatabase(map.get("db").toString(), Boolean.parseBoolean(map.get("managed").toString()))); |
|
86 |
} |
|
87 |
return list; |
|
88 |
|
|
89 |
} |
|
90 |
|
|
91 |
public <T> List<T> getTypedListFromSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException { |
|
92 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName); |
|
93 |
|
|
94 |
try { |
|
95 |
List<T> list = new ArrayList<T>(); |
|
96 |
for (Object obj : jdbcTemplate.queryForList(query, clazz)) { |
|
97 |
list.add(clazz.cast(obj)); |
|
98 |
} |
|
99 |
return list; |
|
100 |
} catch (DataAccessException e) { |
|
101 |
throw new DatabaseException(e); |
|
102 |
} |
|
103 |
} |
|
104 |
|
|
105 |
public List<String> getSimpleListFromSql(final String dbName, final String query) throws DatabaseException { |
|
106 |
|
|
107 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName); |
|
108 |
|
|
109 |
try { |
|
110 |
List<String> list = new ArrayList<>(); |
|
111 |
for (Object obj : jdbcTemplate.queryForList(query)) { |
|
112 |
list.add(obj.toString()); |
|
113 |
} |
|
114 |
return list; |
|
115 |
} catch (DataAccessException e) { |
|
116 |
throw new DatabaseException(e); |
|
117 |
} |
|
118 |
} |
|
119 |
|
|
120 |
public void executeSql(final String db, final String query) throws DatabaseException { |
|
121 |
executeSql(db, query, Void.class); |
|
122 |
} |
|
123 |
|
|
124 |
@SuppressWarnings("unchecked") |
|
125 |
public <T> T executeSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException { |
|
126 |
|
|
127 |
final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName); |
|
128 |
try { |
|
129 |
if (clazz == Integer.class) return (T) jdbcTemplate.queryForObject(query, Integer.class); |
|
130 |
else if (clazz == List.class) return (T) jdbcTemplate.queryForList(query); |
|
131 |
else if (clazz == Map.class) return (T) jdbcTemplate.queryForMap(query); |
|
132 |
else if (clazz == SqlRowSet.class) return (T) jdbcTemplate.queryForRowSet(query); |
|
133 |
else if (clazz == BlockingQueue.class) { |
|
134 |
log.debug("Creating Queue"); |
|
135 |
|
|
136 |
final ArrayBlockingQueue<Map<String, Object>> q = Queues.newArrayBlockingQueue(BLOCKING_QUEUE_SIZE); |
|
137 |
|
|
138 |
Executors.newSingleThreadExecutor().submit(() -> { |
|
139 |
try { |
|
140 |
jdbcTemplate.query(query, rs -> { |
|
141 |
|
|
142 |
ResultSetMetaData md = rs.getMetaData(); |
|
143 |
Map<String, Object> row = new HashMap<>(); |
|
144 |
for (int i = 1; i <= md.getColumnCount(); i++) { |
|
145 |
row.put(md.getColumnName(i), rs.getObject(i)); |
|
146 |
} |
|
147 |
try { |
|
148 |
if (!rs.isClosed() && !q.offer(row, BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS)) { |
|
149 |
log.warn("The consumer doesn't consume my queue, I stop"); |
|
150 |
rs.close(); |
|
151 |
return; |
|
152 |
} |
|
153 |
log.debug("Putted element in queue"); |
|
154 |
} catch (InterruptedException e) { |
|
155 |
log.error("Error putting element in queue"); |
|
156 |
throw new RuntimeException(e); |
|
157 |
} |
|
158 |
}); |
|
159 |
} catch (Throwable e) { |
|
160 |
log.error("Exception executing SQL", e); |
|
161 |
throw new RuntimeException(e); |
|
162 |
} |
|
163 |
try { |
|
164 |
// An empty Map indicates the end of the resultset |
|
165 |
q.offer(new HashMap<>(), BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS); |
|
166 |
} catch (InterruptedException e) { |
|
167 |
log.error("Error putting LAST element in queue"); |
|
168 |
throw new RuntimeException(e); |
|
169 |
} |
|
170 |
log.debug(" -- End of Sql Resultset"); |
|
171 |
}); |
|
172 |
|
|
173 |
log.debug("Returned Queue"); |
|
174 |
|
|
175 |
return (T) q; |
|
176 |
} else { |
|
177 |
jdbcTemplate.update(query); |
|
178 |
return null; |
|
179 |
} |
|
180 |
} catch (Throwable e) { |
|
181 |
throw new DatabaseException(e); |
|
182 |
} |
|
183 |
} |
|
184 |
|
|
185 |
public boolean contains(final String db, final String table, final String column, final String value) throws DatabaseException { |
|
186 |
String query = ""; |
|
187 |
try { |
|
188 |
verifyParameters(db, table, column); |
|
189 |
query = "SELECT " + column + " FROM " + table + " WHERE " + column + " = '" + value + "'"; |
|
190 |
List<String> res = getSimpleListFromSql(db, query); |
|
191 |
return res != null && res.size() > 0; |
|
192 |
} catch (Throwable e) { |
|
193 |
throw new DatabaseException("Error performing SQL: " + query, e); |
|
194 |
} |
|
195 |
} |
|
196 |
|
|
197 |
public List<Map<?, ?>> describeTable(final String database, final String table) throws DatabaseException { |
|
198 |
verifyParameters(database, table); |
|
199 |
|
|
200 |
try { |
|
201 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
202 |
List<Map<?, ?>> response = new ArrayList<>(); |
|
203 |
String query = "SELECT * FROM information_schema.columns WHERE table_name = ?"; |
|
204 |
|
|
205 |
for (Object o : jdbcTemplate.queryForList(query, new Object[] { table })) { |
|
206 |
if (o instanceof Map<?, ?>) { |
|
207 |
response.add((Map<?, ?>) o); |
|
208 |
} |
|
209 |
} |
|
210 |
return response; |
|
211 |
} catch (DataAccessException e) { |
|
212 |
throw new DatabaseException(e); |
|
213 |
} |
|
214 |
} |
|
215 |
|
|
216 |
public String dumpTableAsXML(final String db, final String t) throws DatabaseException { |
|
217 |
return dumpTableAsDoc(db, t).asXML(); |
|
218 |
} |
|
219 |
|
|
220 |
public Document dumpTableAsDoc(final String db, final String t) throws DatabaseException { |
|
221 |
Document doc = DocumentHelper.createDocument(); |
|
222 |
|
|
223 |
Element root = doc.addElement("DB_TABLE"); |
|
224 |
Element head = root.addElement("HEADER"); |
|
225 |
|
|
226 |
head.addElement("DATABASE").addAttribute("value", db); |
|
227 |
head.addElement("TABLE").addAttribute("value", t); |
|
228 |
head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601()); |
|
229 |
|
|
230 |
Element body = root.addElement("BODY"); |
|
231 |
for (Document d : dumpTableAsList(db, t)) { |
|
232 |
body.add(d.getRootElement()); |
|
233 |
} |
|
234 |
return doc; |
|
235 |
} |
|
236 |
|
|
237 |
public List<Document> dumpTableAsList(final String db, final String t) throws DatabaseException { |
|
238 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(db); |
|
239 |
|
|
240 |
List<Document> list = new ArrayList<Document>(); |
|
241 |
for (Object o : jdbcTemplate.queryForList("SELECT * FROM " + t)) { |
|
242 |
if (o instanceof Map<?, ?>) { |
|
243 |
list.add(rowToDocument((Map<?, ?>) o)); |
|
244 |
} |
|
245 |
} |
|
246 |
return list; |
|
247 |
} |
|
248 |
|
|
249 |
public Document rowToDocument(final Map<?, ?> map) throws DatabaseException { |
|
250 |
Document doc = DocumentHelper.createDocument(); |
|
251 |
|
|
252 |
Element row = doc.addElement("ROW"); |
|
253 |
for (Map.Entry<?, ?> entry : map.entrySet()) { |
|
254 |
Element col = row.addElement("FIELD"); |
|
255 |
col.addAttribute("name", "" + entry.getKey()); |
|
256 |
addValue(col, entry.getValue()); |
|
257 |
} |
|
258 |
return doc; |
|
259 |
} |
|
260 |
|
|
261 |
public Document getRowByResourceId(final String database, final String table, final String resourceId) throws DatabaseException { |
|
262 |
verifyParameters(database, table); |
|
263 |
|
|
264 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
265 |
String query = "SELECT * FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?"; |
|
266 |
|
|
267 |
Map<?, ?> map = jdbcTemplate.queryForMap(query, resourceId); |
|
268 |
Document doc = DocumentHelper.createDocument(); |
|
269 |
|
|
270 |
Element root = doc.addElement("DB_RECORD"); |
|
271 |
Element head = root.addElement("HEADER"); |
|
272 |
head.addElement("RESOURCE_IDENTIFIER").addAttribute("value", resourceId); |
|
273 |
head.addElement("DATABASE").addAttribute("value", database); |
|
274 |
head.addElement("TABLE").addAttribute("value", table); |
|
275 |
head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601()); |
|
276 |
|
|
277 |
Element body = root.addElement("BODY"); |
|
278 |
|
|
279 |
Element row = body.addElement("ROW"); |
|
280 |
|
|
281 |
for (Map.Entry<?, ?> entry : map.entrySet()) { |
|
282 |
Element col = row.addElement("FIELD"); |
|
283 |
col.addAttribute("name", "" + entry.getKey()); |
|
284 |
addValue(col, entry.getValue()); |
|
285 |
} |
|
286 |
|
|
287 |
return doc; |
|
288 |
} |
|
289 |
|
|
290 |
private void addValue(final Element elem, final Object value) throws DatabaseException { |
|
291 |
if (value instanceof Array) { |
|
292 |
try { |
|
293 |
for (Object o : (Object[]) ((Array) value).getArray()) { |
|
294 |
addValue(elem.addElement("ITEM"), o); |
|
295 |
} |
|
296 |
} catch (Exception e) { |
|
297 |
throw new DatabaseException("Error procsessing a Array", e); |
|
298 |
} |
|
299 |
} else if (value != null) { |
|
300 |
elem.addText(Sanitizer.sanitize(value.toString())); |
|
301 |
} else { |
|
302 |
elem.addAttribute("isNull", "true"); |
|
303 |
} |
|
304 |
} |
|
305 |
|
|
306 |
private void verifyParameters(final String... params) throws DatabaseException { |
|
307 |
Pattern pattern = Pattern.compile("\\w{1,128}"); |
|
308 |
|
|
309 |
for (String p : params) { |
|
310 |
log.debug("TESTING SQL PARAM:" + p); |
|
311 |
if (p == null) throw new DatabaseException("Parameter is null"); |
|
312 |
else if (!pattern.matcher(p).matches()) throw new DatabaseException("Parameter [" + p + "] contains an invalid character"); |
|
313 |
else { |
|
314 |
log.debug("TEST OK"); |
|
315 |
} |
|
316 |
} |
|
317 |
} |
|
318 |
|
|
319 |
public void importFromIterable(final String db, final Iterable<String> iterable) throws DatabaseException { |
|
320 |
verifyParameters(db); |
|
321 |
|
|
322 |
final DataSource dataSource = dataSourceFactory.createDataSource(db); |
|
323 |
final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dataSource); |
|
324 |
final TransactionTemplate transactionTemplate = transactionTemplateFactory.createTransactionTemplate(dataSource); |
|
325 |
|
|
326 |
int counterTotal = 0; |
|
327 |
|
|
328 |
long start = DateUtils.now(); |
|
329 |
|
|
330 |
final List<GenericRow> rows = new ArrayList<>(); |
|
331 |
for (String s : iterable) { |
|
332 |
rows.addAll(obtainListOfRows(s)); |
|
333 |
if (rows.size() > numbersOfRecordsForTransaction) { |
|
334 |
counterTotal += rows.size(); |
|
335 |
importTransaction(jdbcTemplate, transactionTemplate, rows); |
|
336 |
rows.clear(); |
|
337 |
} |
|
338 |
} |
|
339 |
counterTotal += rows.size(); |
|
340 |
importTransaction(jdbcTemplate, transactionTemplate, rows); |
|
341 |
|
|
342 |
long end = DateUtils.now(); |
|
343 |
|
|
344 |
log.info("**********************************************************"); |
|
345 |
log.info("Processed " + counterTotal + " rows in " + ((end - start) / 1000) + " seconds"); |
|
346 |
log.info("**********************************************************"); |
|
347 |
} |
|
348 |
|
|
349 |
private void importTransaction(final JdbcTemplate jdbcTemplate, final TransactionTemplate transactionTemplate, List<GenericRow> rows) |
|
350 |
throws DatabaseException { |
|
351 |
if (rows != null && rows.size() > 0) { |
|
352 |
importTransactionInternal(jdbcTemplate, transactionTemplate, rows); |
|
353 |
} |
|
354 |
} |
|
355 |
|
|
356 |
private List<GenericRow> importTransactionInternal(final JdbcTemplate jdbcTemplate, |
|
357 |
final TransactionTemplate transactionTemplate, |
|
358 |
final List<GenericRow> rows) throws DatabaseException { |
|
359 |
|
|
360 |
final AtomicReference<DatabaseException> error = new AtomicReference<>(); |
|
361 |
|
|
362 |
try { |
|
363 |
return transactionTemplate.execute(status -> { |
|
364 |
final List<GenericRow> ok = Lists.newArrayList(); |
|
365 |
try { |
|
366 |
for (GenericRow row : rows) { |
|
367 |
if (row.isToDelete()) { |
|
368 |
deleteRow(jdbcTemplate, row); |
|
369 |
} else { |
|
370 |
addOrUpdateRow(jdbcTemplate, row); |
|
371 |
} |
|
372 |
ok.add(row); |
|
373 |
} |
|
374 |
} catch (DatabaseException e) { |
|
375 |
log.warn("Transaction failed", e); |
|
376 |
status.setRollbackOnly(); |
|
377 |
error.set(e); |
|
378 |
} |
|
379 |
return ok; |
|
380 |
}); |
|
381 |
} finally { |
|
382 |
if (error.get() != null) { |
|
383 |
throw error.get(); |
|
384 |
} |
|
385 |
} |
|
386 |
} |
|
387 |
|
|
388 |
protected void addOrUpdateRow(final JdbcTemplate jdbcTemplate, final GenericRow row) throws DatabaseException { |
|
389 |
try { |
|
390 |
|
|
391 |
if (log.isDebugEnabled()) { |
|
392 |
log.debug("Adding or updating element to table " + row.getTable()); |
|
393 |
} |
|
394 |
verifyParameters(row.getTable()); |
|
395 |
verifyParameters(row.getFields().keySet().toArray(new String[row.getFields().size()])); |
|
396 |
|
|
397 |
final List<Map<String, Object>> res = |
|
398 |
jdbcTemplate.queryForList("SELECT 1 FROM datasources d WHERE d.id = ? and d.managed is true", row.getDatasourceId()); |
|
399 |
|
|
400 |
if (res != null && !res.isEmpty()) { |
|
401 |
log.debug(String.format("avoiding to update managed datasource %s", row.getDatasourceId())); |
|
402 |
return; |
|
403 |
} |
|
404 |
|
|
405 |
String fields = ""; |
|
406 |
String values = ""; |
|
407 |
List<Object> list = new ArrayList<>(); |
|
408 |
|
|
409 |
for (Map.Entry<String, Object> e : row.getFields().entrySet()) { |
|
410 |
if (!fields.isEmpty()) { |
|
411 |
fields += ","; |
|
412 |
} |
|
413 |
fields += e.getKey(); |
|
414 |
if (!values.isEmpty()) { |
|
415 |
values += ","; |
|
416 |
} |
|
417 |
values += "?"; |
|
418 |
list.add(e.getValue()); |
|
419 |
} |
|
420 |
|
|
421 |
int count = 0; |
|
422 |
if (row.getFields().containsKey(DNET_RESOURCE_ID_FIELD)) { |
|
423 |
List<Object> list2 = new ArrayList<>(); |
|
424 |
list2.addAll(list); |
|
425 |
list2.add(row.getFields().get(DNET_RESOURCE_ID_FIELD)); |
|
426 |
|
|
427 |
count = jdbcTemplate.update(String.format("UPDATE %s SET (%s) = (%s) WHERE %s = ?", row.getTable(), fields, values, DNET_RESOURCE_ID_FIELD), list2.toArray()); |
|
428 |
} |
|
429 |
if (count == 0) { |
|
430 |
jdbcTemplate.update(String.format("INSERT INTO %s (%s) VALUES (%s)", row.getTable(), fields, values), list.toArray()); |
|
431 |
} |
|
432 |
} catch (final Exception e) { |
|
433 |
throw new DatabaseException("Error adding or updating record", e); |
|
434 |
} |
|
435 |
} |
|
436 |
|
|
437 |
protected void deleteRow(final JdbcTemplate jdbcTemplate, final GenericRow row) throws DatabaseException { |
|
438 |
if (log.isDebugEnabled()) { |
|
439 |
log.debug("Deleting element from table " + row.getTable()); |
|
440 |
} |
|
441 |
verifyParameters(row.getTable()); |
|
442 |
verifyParameters(row.getFields().keySet().toArray(new String[row.getFields().size()])); |
|
443 |
|
|
444 |
List<Object> list = new ArrayList<>(); |
|
445 |
|
|
446 |
String where = ""; |
|
447 |
|
|
448 |
for (Map.Entry<String, Object> e : row.getFields().entrySet()) { |
|
449 |
if (!where.isEmpty()) { |
|
450 |
where += " AND "; |
|
451 |
} |
|
452 |
where += e.getKey() + "=?"; |
|
453 |
list.add(e.getValue()); |
|
454 |
} |
|
455 |
|
|
456 |
if (where.isEmpty()) throw new DatabaseException("Delete condition is empty"); |
|
457 |
int n = jdbcTemplate.update("DELETE FROM " + row.getTable() + " WHERE " + where, list.toArray()); |
|
458 |
|
|
459 |
if (log.isDebugEnabled()) { |
|
460 |
log.debug("Number of Deleted records: " + n); |
|
461 |
} |
|
462 |
} |
|
463 |
|
|
464 |
public void deleteRowByResourceId(final String database, final String table, final String resourceIdentifier) throws DatabaseException { |
|
465 |
verifyParameters(database, table, resourceIdentifier); |
|
466 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
467 |
jdbcTemplate.update("DELETE FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?", resourceIdentifier); |
|
468 |
} |
|
469 |
|
|
470 |
public void clearTable(final String database, final String table) throws DatabaseException { |
|
471 |
verifyParameters(database, table); |
|
472 |
|
|
473 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
474 |
jdbcTemplate.update("DELETE FROM " + table); |
|
475 |
} |
|
476 |
|
|
477 |
public void prepareManagementOfTable(final String database, final String table) throws DatabaseException { |
|
478 |
verifyParameters(database, table); |
|
479 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
480 |
|
|
481 |
if (!isManagedTable(jdbcTemplate, table)) { |
|
482 |
jdbcTemplate.update(getSQLFromTemplate("manageTable", database, table, null)); |
|
483 |
log.info("Added management of table " + table); |
|
484 |
} |
|
485 |
} |
|
486 |
|
|
487 |
public void removeManagementOfTable(final String database, final String table) throws DatabaseException { |
|
488 |
verifyParameters(database, table); |
|
489 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
490 |
|
|
491 |
if (isManagedTable(jdbcTemplate, table)) { |
|
492 |
jdbcTemplate.update(getSQLFromTemplate("unmanageTable", database, table, null)); |
|
493 |
log.info("Removed management of table " + table); |
|
494 |
} |
|
495 |
} |
|
496 |
|
|
497 |
public boolean isManagedTable(final String database, final String table) throws DatabaseException { |
|
498 |
verifyParameters(database, table); |
|
499 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
500 |
return isManagedTable(jdbcTemplate, table); |
|
501 |
} |
|
502 |
|
|
503 |
private boolean isManagedTable(final JdbcTemplate jdbcTemplate, final String table) { |
|
504 |
return jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.columns WHERE table_name = ? AND column_name = ?", Integer.class, |
|
505 |
table, DNET_RESOURCE_ID_FIELD) == 1; |
|
506 |
} |
|
507 |
|
|
508 |
public String getDefaultDnetIdentifier(final String database, final String table) throws DatabaseException { |
|
509 |
verifyParameters(database, table); |
|
510 |
JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database); |
|
511 |
if (isManagedTable(jdbcTemplate, table)) return jdbcTemplate.queryForObject( |
|
512 |
"SELECT column_default FROM information_schema.columns WHERE table_name = ? AND column_name = ?", new Object[] { |
|
513 |
table, DNET_RESOURCE_ID_FIELD }, String.class); |
|
514 |
return ""; |
|
515 |
} |
|
516 |
|
|
517 |
public void reassignDefaultDnetIdentifiers(final String db) throws DatabaseException { |
|
518 |
for (String t : listCommonDBTables(db)) { |
|
519 |
reassignDefaultDnetIdentifiers(db, t); |
|
520 |
} |
|
521 |
} |
|
522 |
|
|
523 |
public void reassignDefaultDnetIdentifiers(final String db, final String t) throws DatabaseException { |
|
524 |
if (!isManagedTable(db, t)) return; |
|
525 |
|
|
526 |
SqlRowSet rows = |
|
527 |
executeSql(db, "SELECT pg_attribute.attname as pkey FROM pg_index, pg_class, pg_attribute " + "WHERE pg_class.oid = '" + t + "'::regclass " |
|
528 |
+ "AND indrelid = pg_class.oid " + "AND pg_attribute.attrelid = pg_class.oid " |
|
529 |
+ "AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary " + "ORDER BY pkey", SqlRowSet.class); |
|
530 |
|
|
531 |
String defaultValue = ""; |
|
532 |
while (rows.next()) { |
|
533 |
if (!defaultValue.isEmpty()) { |
|
534 |
defaultValue += "||'@@'||"; |
|
535 |
} |
|
536 |
defaultValue += rows.getString("pkey"); |
|
537 |
} |
|
538 |
executeSql(db, "UPDATE " + t + " SET " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + " = " + defaultValue); |
|
539 |
log.info("Reassigned dnetId for table " + t); |
|
540 |
} |
|
541 |
|
|
542 |
public String getSQLFromTemplate(final String sqlTemplate, final String db, final String table, Map<String, Object> map) { |
|
543 |
if (map == null) { |
|
544 |
map = new HashMap<>(); |
|
545 |
} |
|
546 |
|
|
547 |
map.put("mainDB", defaultDB); |
|
548 |
map.put("db", db); |
|
549 |
map.put("table", table); |
|
550 |
map.put("idField", DNET_RESOURCE_ID_FIELD); |
|
551 |
|
|
552 |
return VelocityEngineUtils.mergeTemplateIntoString(velocityEngine, "eu/dnetlib/enabling/database/velocity/" + sqlTemplate + ".sql.vm", "UTF-8", map); |
|
553 |
} |
|
554 |
|
|
555 |
public List<GenericRow> obtainListOfRows(final String xml) throws DatabaseException { |
|
556 |
try { |
|
557 |
final Document doc = new SAXReader().read(new StringReader(xml)); |
|
558 |
|
|
559 |
final String datasourceId = doc.valueOf("//ROWS/ROW[@table='datasources']/FIELD[@name='id']/text()"); |
|
560 |
if (StringUtils.isBlank(datasourceId)) { |
|
561 |
throw new DatabaseException("datasource Id is missing, check your mappings!"); |
|
562 |
} |
|
563 |
|
|
564 |
final List<GenericRow> list = Lists.newArrayList(); |
|
565 |
|
|
566 |
for (Object or : doc.selectNodes("//ROW")) { |
|
567 |
Element row = (Element) or; |
|
568 |
|
|
569 |
String table = row.valueOf("@table"); |
|
570 |
|
|
571 |
if ((table == null) || table.isEmpty()) throw new DatabaseException("Attribute table is missing in XSLT"); |
|
572 |
|
|
573 |
boolean toDelete = "deleted".equals(row.valueOf("@status")); |
|
574 |
|
|
575 |
Map<String, Object> fields = new HashMap<String, Object>(); |
|
576 |
|
|
577 |
for (Object of : row.selectNodes("./FIELD")) { |
|
578 |
Node node = (Node) of; |
|
579 |
String key = node.valueOf("@name"); |
|
580 |
String type = node.valueOf("@type"); |
|
581 |
String format = node.valueOf("@format"); |
|
582 |
String valueS = node.getText().trim(); |
|
583 |
|
|
584 |
if ((key != null) && !key.isEmpty()) { |
|
585 |
Object value = valueS; |
|
586 |
if (type != null) { |
|
587 |
|
|
588 |
try { |
|
589 |
// probably an empty string in a typed field means null |
|
590 |
if ("".equals(valueS)) { |
|
591 |
value = null; |
|
592 |
} else if (type.equals("int")) { |
|
593 |
value = Integer.parseInt(valueS); |
|
594 |
} else if (type.equals("float")) { |
Also available in: Unified diff
openaire branch, coupling the dbService to the openaire datasource management