Skip to content

Commit 3ca7807

Browse files
craiglabenzcraiglabenz
authored
Added tests to Cloud SQL samples (GoogleCloudPlatform#4977)
1 parent da297ef commit 3ca7807

12 files changed

Lines changed: 1118 additions & 75 deletions

File tree

‎.gitignore‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ __pycache__
1414
managed_vms/django_tutorial/static/*
1515
**/migrations/*
1616
lib
17+
testing/test-env-local.sh
1718
testing/resources/test-env.sh
1819
testing/resources/service-account.json
1920
testing/resources/client-secrets.json
@@ -27,3 +28,4 @@ credentials.dat
2728
.DS_store
2829
env/
2930
.idea
31+
.env*

‎.kokoro/tests/run_tests.sh‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -108,11 +108,15 @@ export DATALABELING_ENDPOINT="test-datalabeling.sandbox.googleapis.com:443"
108108
# Run Cloud SQL proxy (background process exit when script does)
109109
wget --quiet https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 \
110110
-O ${HOME}/cloud_sql_proxy && chmod +x ${HOME}/cloud_sql_proxy
111-
${HOME}/cloud_sql_proxy -instances="${MYSQL_INSTANCE}"=tcp:3306 &>> \
111+
${HOME}/cloud_sql_proxy -instances="${MYSQL_INSTANCE}"=tcp:3306,"${MYSQL_INSTANCE}" -dir "${HOME}" &>> \
112112
${HOME}/cloud_sql_proxy.log &
113-
${HOME}/cloud_sql_proxy -instances="${POSTGRES_INSTANCE}"=tcp:5432 &>> \
113+
echo -e "\Cloud SQL proxy started for MySQL."
114+
${HOME}/cloud_sql_proxy -instances="${POSTGRES_INSTANCE}"=tcp:5432,"${POSTGRES_INSTANCE}" -dir "${HOME}" &>> \
114115
${HOME}/cloud_sql_proxy-postgres.log &
115-
echo -e "\nCloud SQL proxy started."
116+
echo -e "\Cloud SQL proxy started for Postgres."
117+
${HOME}/cloud_sql_proxy -instances="${SQLSERVER_INSTANCE}"=tcp:1433 &>> \
118+
${HOME}/cloud_sql_proxy-sqlserver.log &
119+
echo -e "\Cloud SQL proxy started for SQL Server."
116120

117121
echo -e "\n******************** TESTING PROJECTS ********************"
118122
# Switch to 'fail at end' to allow all tests to complete before exiting.

‎cloud-sql/mysql/sqlalchemy/connection_test.py‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
from contextlib import contextmanager
2+
import logging
3+
import os
4+
from typing import Dict
5+
import uuid
6+
7+
import pymysql
8+
import pytest
9+
10+
import main
11+
12+
13+
logger = logging.getLogger()
14+
15+
16+
@pytest.mark.usefixtures("tcp_db_connection")
17+
def test_tcp_connection(tcp_db_connection):
18+
assert tcp_db_connection is not None
19+
20+
21+
@pytest.mark.usefixtures("unix_db_connection")
22+
def test_unix_connection(unix_db_connection):
23+
assert unix_db_connection is not None
24+
25+
26+
@pytest.mark.usefixtures("tcp_db_connection")
27+
def test_get(tcp_db_connection):
28+
main.create_tables()
29+
context = main.get_index_context()
30+
assert isinstance(context, dict)
31+
assert len(context.get("recent_votes")) >= 0
32+
assert context.get("tab_count") >= 0
33+
assert context.get("space_count") >= 0
34+
35+
36+
env_map = {
37+
"MYSQL_USER": "DB_USER",
38+
"MYSQL_PASSWORD": "DB_PASS",
39+
"MYSQL_DATABASE": "DB_NAME",
40+
"MYSQL_INSTANCE": "CLOUD_SQL_CONNECTION_NAME",
41+
}
42+
43+
44+
@pytest.fixture(scope="module")
45+
def tcp_db_connection():
46+
tcp_env_map = {key: value for key, value in env_map.items()}
47+
tcp_env_map["MYSQL_HOST"] = "DB_HOST"
48+
49+
with mapped_env_variables(tcp_env_map):
50+
yield from _common_setup()
51+
52+
53+
@pytest.fixture(scope="module")
54+
def unix_db_connection():
55+
with mapped_env_variables(env_map):
56+
yield from _common_setup()
57+
58+
59+
def _common_setup():
60+
try:
61+
pool = main.init_connection_engine()
62+
except pymysql.err.OperationalError as e:
63+
logger.warning(
64+
"Could not connect to the production database. "
65+
"If running tests locally, is the cloud_sql_proxy currently running?"
66+
)
67+
raise e
68+
69+
table_name: str = uuid.uuid4().hex
70+
71+
with pool.connect() as conn:
72+
conn.execute(
73+
f"CREATE TABLE IF NOT EXISTS `{table_name}`"
74+
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
75+
"candidate CHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
76+
)
77+
78+
yield pool
79+
80+
with pool.connect() as conn:
81+
conn.execute(f"DROP TABLE IF EXISTS `{table_name}`")
82+
83+
84+
@contextmanager
85+
def mapped_env_variables(env_map: Dict):
86+
"""Copies values in the environment to other values, also in
87+
the environment.
88+
89+
In `env_map`, keys are source environment variables and values
90+
are destination environment variables.
91+
"""
92+
for key, value in env_map.items():
93+
os.environ[value] = os.environ[key]
94+
95+
try:
96+
yield
97+
finally:
98+
for variable_name in env_map.values():
99+
if os.environ.get(variable_name):
100+
del os.environ[variable_name]

‎cloud-sql/mysql/sqlalchemy/main.py‎

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -35,22 +35,26 @@ def init_connection_engine():
3535
# The total number of concurrent connections for your application will be
3636
# a total of pool_size and max_overflow.
3737
# [END cloud_sql_mysql_sqlalchemy_limit]
38+
3839
# [START cloud_sql_mysql_sqlalchemy_backoff]
3940
# SQLAlchemy automatically uses delays between failed connection attempts,
4041
# but provides no arguments for configuration.
4142
# [END cloud_sql_mysql_sqlalchemy_backoff]
43+
4244
# [START cloud_sql_mysql_sqlalchemy_timeout]
4345
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
4446
# new connection from the pool. After the specified amount of time, an
4547
# exception will be thrown.
4648
"pool_timeout": 30, # 30 seconds
4749
# [END cloud_sql_mysql_sqlalchemy_timeout]
50+
4851
# [START cloud_sql_mysql_sqlalchemy_lifetime]
4952
# 'pool_recycle' is the maximum number of seconds a connection can persist.
5053
# Connections that live longer than the specified amount of time will be
5154
# reestablished
5255
"pool_recycle": 1800, # 30 minutes
5356
# [END cloud_sql_mysql_sqlalchemy_lifetime]
57+
5458
}
5559

5660
if os.environ.get("DB_HOST"):
@@ -84,10 +88,7 @@ def init_tcp_connection_engine(db_config):
8488
port=db_port, # e.g. 3306
8589
database=db_name, # e.g. "my-database-name"
8690
),
87-
# ... Specify additional properties here.
88-
# [END cloud_sql_mysql_sqlalchemy_create_tcp]
8991
**db_config
90-
# [START cloud_sql_mysql_sqlalchemy_create_tcp]
9192
)
9293
# [END cloud_sql_mysql_sqlalchemy_create_tcp]
9394

@@ -119,24 +120,24 @@ def init_unix_connection_engine(db_config):
119120
cloud_sql_connection_name) # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
120121
}
121122
),
122-
# ... Specify additional properties here.
123-
124-
# [END cloud_sql_mysql_sqlalchemy_create_socket]
125123
**db_config
126-
# [START cloud_sql_mysql_sqlalchemy_create_socket]
127124
)
128125
# [END cloud_sql_mysql_sqlalchemy_create_socket]
129126

130127
return pool
131128

132129

133-
# The SQLAlchemy engine will help manage interactions, including automatically
134-
# managing a pool of connections to your database
135-
db = init_connection_engine()
130+
# This global variable is declared with a value of `None`, instead of calling
131+
# `init_connection_engine()` immediately, to simplify testing. In general, it
132+
# is safe to initialize your database connection pool when your script starts
133+
# -- there is no need to wait for the first request.
134+
db = None
136135

137136

138137
@app.before_first_request
139138
def create_tables():
139+
global db
140+
db = db or init_connection_engine()
140141
# Create tables (if they don't already exist)
141142
with db.connect() as conn:
142143
conn.execute(
@@ -148,6 +149,11 @@ def create_tables():
148149

149150
@app.route("/", methods=["GET"])
150151
def index():
152+
context = get_index_context()
153+
return render_template('index.html', **context)
154+
155+
156+
def get_index_context():
151157
votes = []
152158
with db.connect() as conn:
153159
# Execute the query and fetch all results
@@ -167,10 +173,11 @@ def index():
167173
# Count number of votes for spaces
168174
space_result = conn.execute(stmt, candidate="SPACES").fetchone()
169175
space_count = space_result[0]
170-
171-
return render_template(
172-
"index.html", recent_votes=votes, tab_count=tab_count, space_count=space_count
173-
)
176+
return {
177+
'recent_votes': votes,
178+
'space_count': space_count,
179+
'tab_count': tab_count,
180+
}
174181

175182

176183
@app.route("/", methods=["POST"])

0 commit comments

Comments
 (0)