OmniSciDB  c07336695a
OmniSciDatabaseMetaDataTest.java
Go to the documentation of this file.
1 package com.omnisci.jdbc;
2 
3 import static org.junit.Assert.*;
4 
5 import org.junit.After;
6 import org.junit.Before;
7 import org.junit.Test;
8 
9 import java.sql.*;
10 import java.util.ArrayList;
11 import java.util.HashMap;
12 import java.util.Properties;
13 
15  // Property_loader loads the values from 'connection.properties in resources
16  static Properties PROPERTIES = new Property_loader("metadata_test.properties");
17 
18  static final ArrayList<String> default_tables = new ArrayList<String>() {
19  {
20  add(PROPERTIES.getProperty("base_t1"));
21  add(PROPERTIES.getProperty("base_t2"));
22  add(PROPERTIES.getProperty("base_t3"));
23  }
24  };
25 
26  static final ArrayList<String> default_perms = new ArrayList<String>() {
27  {
28  add("CREATE");
29  add("DROP");
30  add("SELECT");
31  add("INSERT");
32  add("UPDATE");
33  add("DELETE");
34  add("TRUNCATE");
35  add("ALTER");
36  }
37  };
38 
39  static final String super_user = PROPERTIES.getProperty("default_super_user");
40  static final String super_password = PROPERTIES.getProperty("default_user_password");
41 
42  static final String user1 = PROPERTIES.getProperty("user1");
43  static final String user1_password = PROPERTIES.getProperty("user1_password");
44  static final String user_url = PROPERTIES.getProperty("user_db_connection_url");
45  static final String user_table1 = PROPERTIES.getProperty("tst_table1");
46  static final String user_table2 = PROPERTIES.getProperty("tst_table2");
47 
48  private Connection m_super_conn = null;
49 
50  @Before
51  public void setUp() throws Exception {
52  // 1. Connect to the default db to create the actual db used for the tests
53  Connection l_conn = DriverManager.getConnection(
54  PROPERTIES.getProperty("default_db_connection_url"),
55  super_user,
57  Statement st = l_conn.createStatement();
58  // 2. Create base db and connect. Shouldn't need to reconnect to
59  // the default db again.
60  run_command(st, PROPERTIES.getProperty("drop_base_db"));
61  run_command(st, PROPERTIES.getProperty("create_base_db"));
62  st.close();
63  l_conn.close();
64 
65  m_super_conn = DriverManager.getConnection(
66  PROPERTIES.getProperty("base_db_connection_url"), super_user, super_password);
67  }
68 
69  @After
70  public void tearDown() throws Exception {
71  m_super_conn.close();
72  // When we drop a DB all its connections are invalidated, so we do it from
73  // a connection to another DB
74  Connection default_conn = DriverManager.getConnection(
75  PROPERTIES.getProperty("default_db_connection_url"),
76  super_user,
78  Statement st = default_conn.createStatement();
79  run_command(st, PROPERTIES.getProperty("drop_base_db"));
80  st.close();
81  default_conn.close();
82  }
83 
84  private void run_command(Statement st, String cmd) throws SQLException {
85  try {
86  st.executeUpdate(cmd);
87  } catch (SQLException sE) {
88  // Intention is to ignore simple object does not exist
89  // errors on drop commands
90  if (0 != sE.getErrorCode()) {
91  System.out.println("run_command ERROR");
92  System.out.println(sE.toString());
93  throw(sE);
94  }
95  }
96  }
97 
98  private void set_user2() throws Exception {
99  Connection x_conn = DriverManager.getConnection(user_url, super_user, super_password);
100  Statement st = x_conn.createStatement();
101  try {
102  run_command(st, PROPERTIES.getProperty("drop_user2"));
103  run_command(st, PROPERTIES.getProperty("create_user2"));
104  run_command(st, PROPERTIES.getProperty("grant_user2_db_access"));
105  run_command(st, PROPERTIES.getProperty("grant_user2_tble_select"));
106  } catch (SQLException sE) {
107  System.out.println("set_user2 ERROR");
108  System.out.println(sE.toString());
109  throw(sE);
110  } finally {
111  st.close();
112  x_conn.close();
113  }
114  }
115 
116  private void set_user1(boolean extra_table) throws Exception {
117  Statement st = m_super_conn.createStatement();
118  Connection conn = null;
119  try {
120  run_command(st, PROPERTIES.getProperty("drop_user1"));
121  run_command(st, PROPERTIES.getProperty("drop_user_db"));
122 
123  run_command(st, PROPERTIES.getProperty("create_user1"));
124  run_command(st, PROPERTIES.getProperty("create_user_db"));
125  st.close();
126 
127  conn = DriverManager.getConnection(user_url, user1, user1_password);
128  st = conn.createStatement();
129  run_command(st, PROPERTIES.getProperty("drop_tst_table1"));
130  run_command(st, PROPERTIES.getProperty("create_tst_table1"));
131  if (extra_table == true) {
132  run_command(st, PROPERTIES.getProperty("drop_tst_table2"));
133  run_command(st, PROPERTIES.getProperty("create_tst_table2"));
134  }
135  } catch (SQLException sE) {
136  System.out.println("set_user1 ERROR");
137  System.out.println(sE.toString());
138  throw(sE);
139  } finally {
140  st.close();
141  if (conn != null) conn.close();
142  }
143  }
144 
145  private void set_omnisci() throws Exception {
146  Statement st = m_super_conn.createStatement();
147  try {
148  run_command(st, PROPERTIES.getProperty("drop_base_table1"));
149  run_command(st, PROPERTIES.getProperty("create_base_table1"));
150  run_command(st, PROPERTIES.getProperty("drop_base_table2"));
151  run_command(st, PROPERTIES.getProperty("create_base_table2"));
152  run_command(st, PROPERTIES.getProperty("drop_base_table3"));
153  run_command(st, PROPERTIES.getProperty("create_base_table3"));
154  } catch (SQLException sE) {
155  System.out.println("set_user1 ERROR");
156  System.out.println(sE.toString());
157  throw(sE);
158  } finally {
159  st.close();
160  }
161  }
162 
163  // These object can be in different db. Need to pass in
164  // conn to make sure we use the correct db.
165  private void drop_setup() throws Exception {
166  Statement st = m_super_conn.createStatement();
167  try {
168  // Drop the lot even of they aren't there to make the code easier
169  run_command(st, PROPERTIES.getProperty("drop_tst_table1"));
170  run_command(st, PROPERTIES.getProperty("drop_tst_table2"));
171  run_command(st, PROPERTIES.getProperty("drop_user1"));
172  run_command(st, PROPERTIES.getProperty("drop_user2"));
173  run_command(st, PROPERTIES.getProperty("drop_user_db"));
174  } catch (SQLException sE) {
175  System.out.println("drop_setup ERROR");
176  System.out.println(sE.toString());
177  throw(sE);
178  } finally {
179  st.close();
180  }
181  }
182 
183  @Test
184  public void tst01_get_meta_data() throws Exception {
185  DatabaseMetaData dM = m_super_conn.getMetaData();
186  assertEquals(super_user, dM.getUserName());
187  assertNotEquals(null, dM.getDatabaseProductVersion());
188  }
189 
190  class QueryStruct {
191  public String D;
192  public String S;
193  public String T;
194  public int result_count;
195  }
196 
197  @Test
198  public void tst02_omnisci_table() throws Exception {
199  // Get all of the tables in the base_db as super user
200  set_omnisci();
201  QueryStruct qS = new QueryStruct() {
202  {
203  D = "%";
204  S = "%";
205  T = "%";
206  result_count = 24;
207  }
208  };
209  test_permissons(m_super_conn, qS, default_tables);
210  drop_setup();
211  }
212 
213  @Test
214  public void tst03_omnisci_table() throws Exception {
215  // Get one specfic table in the base_db as super user
216  set_omnisci();
217  QueryStruct qS = new QueryStruct() {
218  {
219  D = "%";
220  S = "%";
221  T = PROPERTIES.getProperty("base_t3");
222  result_count = 8;
223  }
224  };
225  ArrayList<String> possible_tables = default_tables;
226  possible_tables.add(user_table1);
227  test_permissons(m_super_conn, qS, possible_tables);
228  drop_setup();
229  }
230 
231  @Test
232  public void tst04_omnisci_table() throws Exception {
233  // Get a specfic table in the base_db as super user with a wild card search
234  set_omnisci();
235  QueryStruct qS = new QueryStruct() {
236  {
237  D = PROPERTIES.getProperty("default_db");
238  S = "%";
239  T = PROPERTIES.getProperty("base_table_ptrn");
240  result_count = 8;
241  }
242  };
243  ArrayList<String> possible_tables = default_tables;
244  possible_tables.add(user_table1);
245  test_permissons(m_super_conn, qS, possible_tables);
246  drop_setup();
247  }
248 
249  @Test
250  public void tst05_user_table() throws Exception {
251  // Get the only table in the user_db as user1 using a wild card
252  boolean extra_table = false;
253  set_user1(extra_table); // create database and a single test table
254  QueryStruct qS = new QueryStruct() {
255  {
256  D = "%";
257  S = "%";
258  T = "%";
259  result_count = 8;
260  }
261  };
262  Connection conn = DriverManager.getConnection(user_url, user1, user1_password);
263  ArrayList<String> possible_tables = default_tables;
264  possible_tables.add(user_table1);
265  test_permissons(conn, qS, possible_tables);
266  conn.close(); // close connection
267  drop_setup(); // drop user1 and tables
268  }
269 
270  @Test
271  public void tst06_user_table() throws Exception {
272  // Get the only table by name in the user_db as user1
273  boolean extra_table = false;
274  set_user1(extra_table); // create database and a single test table
275  QueryStruct qS = new QueryStruct() {
276  {
277  D = "%";
278  S = "%";
279  T = user_table1;
280  result_count = 8;
281  }
282  };
283  Connection conn = DriverManager.getConnection(user_url, user1, user1_password);
284  ArrayList<String> possible_tables = default_tables;
285  possible_tables.add(user_table1);
286  test_permissons(conn, qS, possible_tables);
287  conn.close(); // close connection
288  drop_setup(); // drop user1 and tables
289  }
290 
291  @Test
292  public void tst07_user_table() throws Exception {
293  // Get the only table in the user_db as user1 using null for table name
294  boolean extra_table = false;
295  set_user1(extra_table); // create database and a single test table
296  QueryStruct qS = new QueryStruct() {
297  {
298  D = "%";
299  S = "%";
300  T = null;
301  result_count = 8;
302  }
303  };
304  Connection conn = DriverManager.getConnection(user_url, user1, user1_password);
305  ArrayList<String> possible_tables = default_tables;
306  possible_tables.add(user_table1);
307  test_permissons(conn, qS, possible_tables);
308  conn.close(); // close connection
309  drop_setup(); // drop user1 and tables
310  }
311 
312  @Test
313  public void tst08_user_table() throws Exception {
314  // Get the two table in the user_db as user1 using a wild card
315  boolean extra_table = true;
316  set_user1(extra_table); // create database and a single test table
317  QueryStruct qS = new QueryStruct() {
318  {
319  D = "%";
320  S = "%";
321  T = PROPERTIES.getProperty("table_ptrn");
322  result_count = 16;
323  }
324  };
325  Connection conn = DriverManager.getConnection(user_url, user1, user1_password);
326  ArrayList<String> possible_tables = default_tables;
327  possible_tables.add(user_table1);
328  possible_tables.add(user_table2); // add extra table to reference
329  test_permissons(conn, qS, possible_tables);
330  conn.close(); // close connection
331  drop_setup(); // drop user1 and tables
332  }
333 
334  @Test
335  public void tst09_user_table() throws Exception {
336  // Get the two table in the user_db as user1 using a wild card
337  boolean extra_table = true;
338  set_user1(extra_table); // create database and a single test table
339  QueryStruct qS = new QueryStruct() {
340  {
341  D = PROPERTIES.getProperty("user_db");
342  S = "%";
343  T = PROPERTIES.getProperty("table_ptrn");
344  result_count = 16;
345  }
346  };
347  ArrayList<String> possible_tables = new ArrayList<String>() {
348  { add(user_table1); }
349  { add(user_table2); }
350  };
351  Connection conn = DriverManager.getConnection(user_url, user1, user1_password);
352  test_permissons(conn, qS, possible_tables);
353  conn.close(); // close connection
354  drop_setup(); // drop user1 and tables
355  }
356 
357  @Test
358  public void tst10_omnisci_table() throws Exception {
359  // Get the two table in the user_db as super user using a wild card
360  boolean extra_table = true;
361  set_user1(extra_table); // create database and a single test table
362  QueryStruct qS = new QueryStruct() {
363  {
364  D = PROPERTIES.getProperty("user_db");
365  S = "%";
366  T = PROPERTIES.getProperty("table_ptrn");
367  result_count = 32; // rows returned are 2 tables * 8 permissions * 2 users; super
368  // user and user1
369  }
370  };
371  ArrayList<String> possible_tables = new ArrayList<String>() {
372  { add(user_table1); }
373  { add(user_table2); }
374  };
375  Connection conn = DriverManager.getConnection(user_url, super_user, super_password);
376  test_permissons(conn, qS, possible_tables);
377  conn.close(); // close connection
378  drop_setup(); // drop user1 and tables
379  }
380 
381  @Test
382  public void tst11_user2_table() throws Exception {
383  // Get a single table in the user_db as user2 user using a wild card
384  // user2 only has select access on a single table
385  boolean extra_table = true;
386  set_user1(extra_table); // create database and a single test table
387  set_user2(); // create database and a single test table
388  QueryStruct qS = new QueryStruct() {
389  {
390  D = PROPERTIES.getProperty("user_db");
391  S = "%";
392  T = PROPERTIES.getProperty("table_ptrn");
393  result_count = 1; // rows returned are 1 tables * 1 permissions * 1 users; user2
394  }
395  };
396  ArrayList<String> possible_tables = new ArrayList<String>() {
397  { add(user_table1); }
398  };
399 
400  Connection conn = DriverManager.getConnection(user_url,
401  PROPERTIES.getProperty("user2"),
402  PROPERTIES.getProperty("user2_password"));
403 
404  test_permissons(conn, qS, possible_tables);
405  conn.close(); // close connection
406  drop_setup(); // drop user1 and tables
407  }
408 
409  private void test_permissons(
410  Connection conn, QueryStruct qt, ArrayList<String> possible_tables)
411  throws Exception {
412  ArrayList<HashMap<String, String>> rows = new ArrayList<HashMap<String, String>>();
413 
414  getTablePrivileges(conn, qt, rows);
415  assertEquals(qt.result_count, rows.size());
416 
417  HashMap<String, Integer> record_count_accumulator = new HashMap<String, Integer>();
418  for (HashMap<String, String> record : rows) {
419  String table_name = record.get("TABLE_NAME");
420 
421  assertTrue(possible_tables.contains(table_name));
422  String privilege = record.get("PRIVILEGE");
423  String grantee = record.get("GRANTEE");
424  assertTrue(default_perms.contains(privilege));
425  // Count all records for a table_name + privilege + grantee.
426  // Should only be one each
427  String key = table_name + privilege + grantee;
428  // insert zero if new record and alway increment
429  record_count_accumulator.put(
430  key, record_count_accumulator.getOrDefault(key, 0) + 1);
431  }
432  // Since there are the correct number of perms returned
433  // and each perm is only listed once this should mean all the type
434  // of perms are present
435  for (Integer count : record_count_accumulator.values()) {
436  // Check each instance only orrurs once.
437  assertEquals(1, count.intValue());
438  }
439 
440  rows.clear();
441  }
442 
443  public void getTablePrivileges(
444  Connection conn, QueryStruct qt, ArrayList<HashMap<String, String>> rows)
445  throws Exception {
446  {
447  ResultSet privileges = conn.getMetaData().getTablePrivileges(qt.D, qt.S, qt.T);
448  assertEquals(7, privileges.getMetaData().getColumnCount());
449 
450  while (privileges.next()) {
451  HashMap<String, String> record = new HashMap<String, String>();
452  record.put("TABLE_CAT", privileges.getString("TABLE_CAT"));
453  record.put("TABLE_SCHEM", privileges.getString("TABLE_SCHEM"));
454  record.put("TABLE_NAME", privileges.getString("TABLE_NAME"));
455  record.put("PRIVILEGE", privileges.getString("PRIVILEGE"));
456  record.put("GRANTOR", privileges.getString("GRANTOR"));
457  record.put("GRANTEE", privileges.getString("GRANTEE"));
458  record.put("IS_GRANTABLE", privileges.getString("IS_GRANTABLE"));
459  rows.add(record);
460  }
461  }
462  }
463 
464  /*
465  * @Test public void allProceduresAreCallable() { }
466  *
467  * @Test public void allTablesAreSelectable() { }
468  *
469  * @Test public void getURL() { }
470  *
471  * @Test public void getUserName() { }
472  *
473  * @Test public void isReadOnly() { }
474  *
475  * @Test public void nullsAreSortedHigh() { }
476  *
477  * @Test public void nullsAreSortedLow() { }
478  *
479  * @Test public void nullsAreSortedAtStart() { }
480  *
481  * @Test public void nullsAreSortedAtEnd() { }
482  *
483  * @Test public void getDatabaseProductName() { }
484  *
485  * @Test public void getDriverName() { }
486  *
487  * @Test public void getDriverVersion() { }
488  *
489  * @Test public void getDriverMajorVersion() { }
490  *
491  * @Test public void getDriverMinorVersion() { }
492  *
493  * @Test public void getNumericFunctions() { }
494  *
495  * @Test public void getStringFunctions() { }
496  *
497  * @Test public void getSystemFunctions() { }
498  *
499  * @Test public void getTimeDateFunctions() { }
500  *
501  * @Test public void getSearchStringEscape() { }
502  *
503  * @Test public void getSchemaTerm() { }
504  *
505  * @Test public void getProcedureTerm() { }
506  *
507  * @Test public void getCatalogTerm() { }
508  *
509  * @Test public void getTables() { }
510  *
511  * @Test public void getSchemas() { }
512  *
513  * @Test public void getCatalogs() { }
514  *
515  * @Test public void getTableTypes() { }
516  *
517  * @Test public void getColumns() { }
518  *
519  */
520 }
void getTablePrivileges(Connection conn, QueryStruct qt, ArrayList< HashMap< String, String >> rows)
void test_permissons(Connection conn, QueryStruct qt, ArrayList< String > possible_tables)