Skip to content

Commit 1f4b653

Browse files
shubha-rajanshubha-rajan
authored
Add tests for Cloud SQL samples (GoogleCloudPlatform#4495)
* add integration tests for mysql * source Cloud SQL secrets in run_tests.sh * add integration tests to postgres sample * add tests for SQL server sample * fix typo in run_tests.sh * reformat files * create table before tests * fix linting * update license headers * rename files * use POJO instead of hashmap * move TemplateData class * move TemplateData class * move TemplateData class * add cleanup method to drop table * add uuid to table name * fix linting
1 parent d6ceebc commit 1f4b653

File tree

11 files changed

+565
-34
lines changed

11 files changed

+565
-34
lines changed

‎.kokoro/tests/run_tests.sh‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,8 @@ if [[ "$SCRIPT_DEBUG" != "true" ]]; then
7878
source "${KOKORO_GFILE_DIR}/firestore_secrets.txt"
7979
# spellcheck source=src/cts_v4_secrets.txt
8080
source "${KOKORO_GFILE_DIR}/cts_v4_secrets.txt"
81+
# shellcheck source=src/cloud_sql_secrets.txt
82+
source "${KOKORO_GFILE_DIR}/cloud_sql_secrets.txt"
8183

8284
# Activate service account
8385
gcloud auth activate-service-account \

‎cloud-sql/mysql/servlet/pom.xml‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,24 @@
7474
<artifactId>slf4j-simple</artifactId>
7575
<version>1.7.30</version>
7676
</dependency>
77+
<dependency>
78+
<groupId>org.mockito</groupId>
79+
<artifactId>mockito-core</artifactId>
80+
<version>3.6.28</version>
81+
<scope>test</scope>
82+
</dependency>
83+
<dependency>
84+
<groupId>junit</groupId>
85+
<artifactId>junit</artifactId>
86+
<version>4.13.1</version>
87+
<scope>test</scope>
88+
</dependency>
89+
<dependency>
90+
<groupId>com.google.truth</groupId>
91+
<artifactId>truth</artifactId>
92+
<version>1.1</version>
93+
<scope>test</scope>
94+
</dependency>
7795
</dependencies>
7896

7997
<build>

‎cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/IndexServlet.java‎

Lines changed: 35 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,8 @@
3737
import javax.servlet.http.HttpServletResponse;
3838
import javax.sql.DataSource;
3939

40+
41+
4042
@SuppressFBWarnings(
4143
value = {"SE_NO_SERIALVERSIONID", "WEM_WEAK_EXCEPTION_MESSAGING"},
4244
justification = "Not needed for IndexServlet, Exception adds context")
@@ -45,12 +47,20 @@ public class IndexServlet extends HttpServlet {
4547

4648
private static final Logger LOGGER = Logger.getLogger(IndexServlet.class.getName());
4749

48-
@Override
49-
public void doGet(HttpServletRequest req, HttpServletResponse resp)
50-
throws IOException, ServletException {
51-
// Extract the pool from the Servlet Context, reusing the one that was created
52-
// in the ContextListener when the application was started
53-
DataSource pool = (DataSource) req.getServletContext().getAttribute("my-pool");
50+
class TemplateData {
51+
52+
public int tabCount;
53+
public int spaceCount;
54+
public List<Vote> recentVotes;
55+
56+
public TemplateData(int tabCount, int spaceCount, List<Vote> recentVotes) {
57+
this.tabCount = tabCount;
58+
this.spaceCount = spaceCount;
59+
this.recentVotes = recentVotes;
60+
}
61+
}
62+
63+
public TemplateData getTemplateData(DataSource pool) throws ServletException {
5464

5565
int tabCount = 0;
5666
int spaceCount = 0;
@@ -59,7 +69,7 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
5969
// PreparedStatements are compiled by the database immediately and executed at a later date.
6070
// Most databases cache previously compiled queries, which improves efficiency.
6171
String stmt1 = "SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5";
62-
try (PreparedStatement voteStmt = conn.prepareStatement(stmt1); ) {
72+
try (PreparedStatement voteStmt = conn.prepareStatement(stmt1);) {
6373
// Execute the statement
6474
ResultSet voteResults = voteStmt.executeQuery();
6575
// Convert a ResultSet into Vote objects
@@ -73,7 +83,7 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
7383
// PreparedStatements can also be executed multiple times with different arguments. This can
7484
// improve efficiency, and project a query from being vulnerable to an SQL injection.
7585
String stmt2 = "SELECT COUNT(vote_id) FROM votes WHERE candidate=?";
76-
try (PreparedStatement voteCountStmt = conn.prepareStatement(stmt2); ) {
86+
try (PreparedStatement voteCountStmt = conn.prepareStatement(stmt2);) {
7787
voteCountStmt.setString(1, "TABS");
7888
ResultSet tabResult = voteCountStmt.executeQuery();
7989
if (tabResult.next()) { // Move to the first result
@@ -95,11 +105,24 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
95105
+ "steps in the README and try again.",
96106
ex);
97107
}
108+
TemplateData templateData = new TemplateData(tabCount, spaceCount, recentVotes);
109+
110+
return templateData;
111+
}
112+
113+
@Override
114+
public void doGet(HttpServletRequest req, HttpServletResponse resp)
115+
throws IOException, ServletException {
116+
// Extract the pool from the Servlet Context, reusing the one that was created
117+
// in the ContextListener when the application was started
118+
DataSource pool = (DataSource) req.getServletContext().getAttribute("my-pool");
119+
120+
TemplateData templateData = getTemplateData(pool);
98121

99122
// Add variables and render the page
100-
req.setAttribute("tabCount", tabCount);
101-
req.setAttribute("spaceCount", spaceCount);
102-
req.setAttribute("recentVotes", recentVotes);
123+
req.setAttribute("tabCount", templateData.tabCount);
124+
req.setAttribute("spaceCount", templateData.spaceCount);
125+
req.setAttribute("recentVotes", templateData.recentVotes);
103126
req.getRequestDispatcher("/index.jsp").forward(req, resp);
104127
}
105128

@@ -140,7 +163,7 @@ public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOEx
140163

141164
// PreparedStatements can be more efficient and project against injections.
142165
String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
143-
try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
166+
try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
144167
voteStmt.setTimestamp(1, now);
145168
voteStmt.setString(2, team);
146169

‎cloud-sql/mysql/servlet/src/test/java/com/TestIndexServletMysql.java‎

Lines changed: 132 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,132 @@
1+
/*
2+
* Copyright 2020 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.cloudsql;
18+
19+
import static com.google.common.truth.Truth.assertWithMessage;
20+
import static org.junit.Assert.assertNotNull;
21+
import static org.junit.Assert.assertTrue;
22+
import static org.mockito.Mockito.mock;
23+
import static org.mockito.Mockito.when;
24+
25+
import com.example.cloudsql.IndexServlet.TemplateData;
26+
import com.zaxxer.hikari.HikariConfig;
27+
import com.zaxxer.hikari.HikariDataSource;
28+
import java.io.PrintWriter;
29+
import java.io.StringWriter;
30+
import java.sql.Connection;
31+
import java.sql.PreparedStatement;
32+
import java.sql.SQLException;
33+
import java.util.Arrays;
34+
import java.util.List;
35+
import java.util.UUID;
36+
import javax.servlet.ServletContext;
37+
import javax.servlet.http.HttpServletRequest;
38+
import javax.servlet.http.HttpServletResponse;
39+
import javax.sql.DataSource;
40+
import org.junit.AfterClass;
41+
import org.junit.BeforeClass;
42+
import org.junit.Test;
43+
44+
45+
public class TestIndexServletMysql {
46+
47+
private static List<String> requiredEnvVars =
48+
Arrays.asList("MYSQL_USER", "MYSQL_PASS", "MYSQL_DB", "MYSQL_CONNECTION_NAME");
49+
50+
private static DataSource pool;
51+
private static String tableName;
52+
53+
public static void checkEnvVars() {
54+
// Check that required env vars are set
55+
requiredEnvVars.forEach((varName) -> {
56+
assertWithMessage(
57+
String.format("Environment variable '%s' must be set to perform these tests.", varName))
58+
.that(System.getenv(varName)).isNotEmpty();
59+
});
60+
}
61+
62+
private static void createTable(DataSource pool) throws SQLException {
63+
// Safely attempt to create the table schema.
64+
tableName = String.format("votes_%s", UUID.randomUUID().toString().replace("-", ""));
65+
try (Connection conn = pool.getConnection()) {
66+
String stmt =
67+
"CREATE TABLE IF NOT EXISTS "
68+
+ tableName
69+
+ " ( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL,"
70+
+ " candidate CHAR(6) NOT NULL,"
71+
+ " PRIMARY KEY (vote_id) );";
72+
try (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
73+
createTableStatement.execute();
74+
}
75+
}
76+
}
77+
78+
79+
@BeforeClass
80+
public static void createPool() throws SQLException {
81+
checkEnvVars();
82+
HikariConfig config = new HikariConfig();
83+
84+
config.setJdbcUrl(String.format("jdbc:mysql:///%s", System.getenv("MYSQL_DB")));
85+
config.setUsername(System.getenv("MYSQL_USER")); // e.g. "root", "mysql"
86+
config.setPassword(System.getenv("MYSQL_PASS")); // e.g. "my-password"
87+
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
88+
config.addDataSourceProperty("cloudSqlInstance", System.getenv("MYSQL_CONNECTION_NAME"));
89+
90+
pool = new HikariDataSource(config);
91+
createTable(pool);
92+
}
93+
94+
@AfterClass
95+
public static void dropTable() throws SQLException {
96+
try (Connection conn = pool.getConnection()) {
97+
String stmt = String.format("DROP TABLE %s;", tableName);
98+
try (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
99+
createTableStatement.execute();
100+
}
101+
}
102+
}
103+
104+
@Test
105+
public void testGetTemplateData() throws Exception {
106+
TemplateData templateData = new IndexServlet().getTemplateData(pool);
107+
108+
assertNotNull(templateData.tabCount);
109+
assertNotNull(templateData.spaceCount);
110+
assertNotNull(templateData.recentVotes);
111+
}
112+
113+
@Test
114+
public void testServletPost() throws Exception {
115+
HttpServletRequest request = mock(HttpServletRequest.class);
116+
HttpServletResponse response = mock(HttpServletResponse.class);
117+
ServletContext context = mock(ServletContext.class);
118+
119+
when(request.getServletContext()).thenReturn(context);
120+
when(context.getAttribute("my-pool")).thenReturn(pool);
121+
when(request.getParameter("team")).thenReturn("TABS");
122+
123+
StringWriter stringWriter = new StringWriter();
124+
PrintWriter writer = new PrintWriter(stringWriter);
125+
when(response.getWriter()).thenReturn(writer);
126+
127+
new IndexServlet().doPost(request, response);
128+
129+
writer.flush();
130+
assertTrue(stringWriter.toString().contains("Vote successfully cast for"));
131+
}
132+
}

‎cloud-sql/postgres/servlet/pom.xml‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,24 @@
6464
<artifactId>HikariCP</artifactId>
6565
<version>3.4.5</version>
6666
</dependency>
67+
<dependency>
68+
<groupId>org.mockito</groupId>
69+
<artifactId>mockito-core</artifactId>
70+
<version>3.6.28</version>
71+
<scope>test</scope>
72+
</dependency>
73+
<dependency>
74+
<groupId>junit</groupId>
75+
<artifactId>junit</artifactId>
76+
<version>4.13.1</version>
77+
<scope>test</scope>
78+
</dependency>
79+
<dependency>
80+
<groupId>com.google.truth</groupId>
81+
<artifactId>truth</artifactId>
82+
<version>1.1</version>
83+
<scope>test</scope>
84+
</dependency>
6785
</dependencies>
6886

6987
<build>

‎cloud-sql/postgres/servlet/src/main/java/com/example/cloudsql/ConnectionPoolContextListener.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,7 @@ private DataSource createConnectionPool() {
6666
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
6767
config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
6868

69+
6970
// The ipTypes argument can be used to specify a comma delimited list of preferred IP types
7071
// for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the
7172
// SocketFactory to connect with an instance's associated private IP.

‎cloud-sql/postgres/servlet/src/main/java/com/example/cloudsql/IndexServlet.java‎

Lines changed: 33 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -43,14 +43,22 @@
4343
@WebServlet(name = "Index", value = "")
4444
public class IndexServlet extends HttpServlet {
4545

46+
class TemplateData {
47+
48+
public int tabCount;
49+
public int spaceCount;
50+
public List<Vote> recentVotes;
51+
52+
public TemplateData(int tabCount, int spaceCount, List<Vote> recentVotes) {
53+
this.tabCount = tabCount;
54+
this.spaceCount = spaceCount;
55+
this.recentVotes = recentVotes;
56+
}
57+
}
58+
4659
private static final Logger LOGGER = Logger.getLogger(IndexServlet.class.getName());
4760

48-
@Override
49-
public void doGet(HttpServletRequest req, HttpServletResponse resp)
50-
throws IOException, ServletException {
51-
// Extract the pool from the Servlet Context, reusing the one that was created
52-
// in the ContextListener when the application was started
53-
DataSource pool = (DataSource) req.getServletContext().getAttribute("my-pool");
61+
public TemplateData getTemplateData(DataSource pool) throws ServletException {
5462

5563
int tabCount = 0;
5664
int spaceCount = 0;
@@ -59,7 +67,7 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
5967
// PreparedStatements are compiled by the database immediately and executed at a later date.
6068
// Most databases cache previously compiled queries, which improves efficiency.
6169
String stmt1 = "SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5";
62-
try (PreparedStatement voteStmt = conn.prepareStatement(stmt1); ) {
70+
try (PreparedStatement voteStmt = conn.prepareStatement(stmt1);) {
6371
// Execute the statement
6472
ResultSet voteResults = voteStmt.executeQuery();
6573
// Convert a ResultSet into Vote objects
@@ -73,7 +81,7 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
7381
// PreparedStatements can also be executed multiple times with different arguments. This can
7482
// improve efficiency, and project a query from being vulnerable to an SQL injection.
7583
String stmt2 = "SELECT COUNT(vote_id) FROM votes WHERE candidate=?";
76-
try (PreparedStatement voteCountStmt = conn.prepareStatement(stmt2); ) {
84+
try (PreparedStatement voteCountStmt = conn.prepareStatement(stmt2);) {
7785
voteCountStmt.setString(1, "TABS");
7886
ResultSet tabResult = voteCountStmt.executeQuery();
7987
if (tabResult.next()) { // Move to the first result
@@ -95,11 +103,24 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp)
95103
+ "steps in the README and try again.",
96104
ex);
97105
}
106+
TemplateData templateData = new TemplateData(tabCount, spaceCount, recentVotes);
107+
108+
return templateData;
109+
}
110+
111+
@Override
112+
public void doGet(HttpServletRequest req, HttpServletResponse resp)
113+
throws IOException, ServletException {
114+
// Extract the pool from the Servlet Context, reusing the one that was created
115+
// in the ContextListener when the application was started
116+
DataSource pool = (DataSource) req.getServletContext().getAttribute("my-pool");
117+
118+
TemplateData templateData = getTemplateData(pool);
98119

99120
// Add variables and render the page
100-
req.setAttribute("tabCount", tabCount);
101-
req.setAttribute("spaceCount", spaceCount);
102-
req.setAttribute("recentVotes", recentVotes);
121+
req.setAttribute("tabCount", templateData.tabCount);
122+
req.setAttribute("spaceCount", templateData.spaceCount);
123+
req.setAttribute("recentVotes", templateData.recentVotes);
103124
req.getRequestDispatcher("/index.jsp").forward(req, resp);
104125
}
105126

@@ -140,7 +161,7 @@ public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOEx
140161

141162
// PreparedStatements can be more efficient and project against injections.
142163
String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
143-
try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
164+
try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
144165
voteStmt.setTimestamp(1, now);
145166
voteStmt.setString(2, team);
146167

0 commit comments

Comments
 (0)