2 * ============LICENSE_START=======================================================
3 * ONAP : ccsdk features
4 * ================================================================================
5 * Copyright (C) 2020 highstreet technologies GmbH Intellectual Property.
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END=========================================================
22 package org.onap.ccsdk.features.sdnr.wt.dataprovider.dblib.test;
24 import static org.junit.Assert.assertEquals;
25 import static org.junit.Assert.assertTrue;
26 import static org.junit.Assert.fail;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.text.ParseException;
30 import java.util.concurrent.TimeUnit;
31 import org.junit.AfterClass;
32 import org.junit.BeforeClass;
33 import org.junit.Test;
34 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntryList;
35 import org.onap.ccsdk.features.sdnr.wt.common.database.data.DatabaseVersion;
36 import org.onap.ccsdk.features.sdnr.wt.common.database.data.IndicesEntryList;
37 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.SqlDBClient;
38 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.data.SqlDBDataProvider;
39 import org.onap.ccsdk.features.sdnr.wt.dataprovider.dblib.test.util.MariaDBTestBase;
40 import ch.vorburger.exec.ManagedProcessException;
42 public class TestCRUDMariaDB {
45 private static final String TABLE1_NAME = "table1";
46 private static final String TABLE2_NAME = "table2-v6";
47 private static final String TABLE3_NAME = "table3-v6";
48 private static final String VIEW2_NAME = "table2";
49 private static final String VIEW3_NAME = "table3";
50 private static final String TABLE1_MAPPING = "col1 INT PRIMARY KEY, col2 NVARCHAR(30), col3 BOOLEAN";
51 private static final String TABLE2_MAPPING = "col1 INT PRIMARY KEY, col2 NVARCHAR(30), col3 BOOLEAN";
52 private static final String TABLE3_MAPPING = "col1 INT PRIMARY KEY, col2 NVARCHAR(30), col3 BOOLEAN";
53 private static final String DELETE_ALL_FORMAT = "DELETE FROM `%s`";
54 private static final String READ_ALL_FORMAT = "SELECT * FROM `%s`";
55 private static final String TABLE1_INSERT_ENTRY_FORMAT =
56 "INSERT INTO `" + TABLE1_NAME + "` (col1, col2, col3) VALUES(%d,'%s',%d);";
57 private static final String TABLE1_UPDATE_ENTRY_FORMAT =
58 "UPDATE `" + TABLE1_NAME + "` SET col2='%s', col3=%d WHERE col1=%d;";
59 private static final String TABLE1_DELETE_ENTRY_FORMAT = "DELETE FROM `" + TABLE1_NAME + "` WHERE col1=%d;";
60 private static String DBNAME = null;
62 private static MariaDBTestBase testBase;
63 private static SqlDBDataProvider dbProvider;
64 private static SqlDBClient dbClient;
67 public static void init() throws Exception {
69 testBase = new MariaDBTestBase();
70 dbProvider = testBase.getDbProvider();
71 dbProvider.waitForDatabaseReady(30, TimeUnit.SECONDS);
72 dbClient = testBase.createRawClient();
73 DBNAME = testBase.getDBName();
77 public static void close() {
80 } catch (ManagedProcessException e) {
91 success = dbClient.createTable(TABLE1_NAME, TABLE1_MAPPING);
92 assertTrue("failed to create table", success);
95 success = dbClient.delete(String.format(DELETE_ALL_FORMAT, TABLE1_NAME));
96 } catch (SQLException e) {
99 assertTrue("failed to clear table", success);
101 data = dbClient.read(String.format(READ_ALL_FORMAT, TABLE1_NAME));
103 assertEquals(0, countRows(data));
104 } catch (SQLException e) {
106 fail("unable to read size");
108 try { data.close(); } catch (SQLException ignore) { }
112 success = dbClient.write(String.format(TABLE1_INSERT_ENTRY_FORMAT, 10, "hello", 0));
113 } catch (SQLException e) {
116 assertTrue("failed to write data", success);
118 data = dbClient.read(String.format(READ_ALL_FORMAT, TABLE1_NAME));
120 assertEquals(1, countRows(data));
121 assertTrue(data.next());
122 assertEquals(10, data.getInt(1));
123 assertEquals("hello", data.getString(2));
124 assertEquals(false, data.getBoolean(3));
125 } catch (SQLException e) {
127 fail("unable to verify write");
129 try { data.close(); } catch (SQLException ignore) { }
133 success = dbClient.update(String.format(TABLE1_UPDATE_ENTRY_FORMAT, "hello2", 1, 10));
134 } catch (SQLException e) {
137 assertTrue("failed to update data", success);
139 data = dbClient.read(String.format(READ_ALL_FORMAT, TABLE1_NAME));
141 assertEquals(1, countRows(data));
142 assertTrue(data.next());
143 assertEquals(10, data.getInt(1));
144 assertEquals("hello2", data.getString(2));
145 assertEquals(true, data.getBoolean(3));
146 } catch (SQLException e) {
148 fail("unable to verify write");
150 try { data.close(); } catch (SQLException ignore) { }
154 success = dbClient.delete(String.format(TABLE1_DELETE_ENTRY_FORMAT, 10));
155 } catch (SQLException e) {
158 assertTrue("failed to delete data", success);
160 data = dbClient.read(String.format(READ_ALL_FORMAT, TABLE1_NAME));
162 assertEquals(0, data.getFetchSize());
163 } catch (SQLException e) {
165 fail("unable to verify delete. size>0");
167 try { data.close(); } catch (SQLException ignore) { }
171 public void testDBVersion() {
172 DatabaseVersion version = null;
174 version = dbClient.readActualVersion();
175 } catch (SQLException | ParseException e) {
177 fail(e.getMessage());
179 assertTrue(version.getMajor() >= 10);
183 public void testTableStuff() {
186 //create Tables/Views
187 success = dbClient.createTable(TABLE2_NAME, TABLE2_MAPPING);
189 success = dbClient.createTable(TABLE3_NAME, TABLE3_MAPPING);
192 success = dbClient.createView(TABLE2_NAME, VIEW2_NAME);
194 success = dbClient.createView(TABLE3_NAME, VIEW3_NAME);
196 } catch (SQLException e) {
198 fail(e.getMessage());
201 IndicesEntryList tables = dbClient.readTables();
202 assertTrue(tables.stream().filter(t -> t.getName().equals(TABLE2_NAME)).count() == 1);
203 assertTrue(tables.stream().filter(t -> t.getName().equals(TABLE3_NAME)).count() == 1);
204 AliasesEntryList views = dbClient.readViews(DBNAME);
205 assertTrue(views.stream().filter(t -> t.getIndex().equals(TABLE2_NAME) && t.getAlias().equals(VIEW2_NAME))
207 assertTrue(views.stream().filter(t -> t.getIndex().equals(TABLE3_NAME) && t.getAlias().equals(VIEW3_NAME))
210 //delete Tables/Views
212 success = dbClient.deleteView(VIEW2_NAME);
214 success = dbClient.deleteView(VIEW3_NAME);
216 success = dbClient.deleteTable(TABLE2_NAME);
218 success = dbClient.deleteTable(TABLE3_NAME);
220 } catch (SQLException e) {
222 fail(e.getMessage());
225 tables = dbClient.readTables();
226 assertTrue(tables.stream().filter(t->t.getName().equals(TABLE2_NAME)).count()==0);
227 assertTrue(tables.stream().filter(t->t.getName().equals(TABLE3_NAME)).count()==0);
228 views = dbClient.readViews(DBNAME);
229 assertEquals(0,views.size());
232 public static int countRows(ResultSet data) throws SQLException {
234 while (data.next()) {