[autotest] Setup chromeos_lab_servers database
This CL adds necessary script to create a chromeos_lab_servers database when
setting up Autotest.
This CL also changes the database_connection module so that any database
connection created by a given configure section will be force to use the
database credential defined in AUTOTEST_WEB (the AFE database).
design doc:
https://docs.google.com/a/google.com/document/d/1fqeXZElMkx5lVEh8xSdNMDn88LE6xJLkU-5vc3iVmOw/edit#
BUG=chromium:424700
TEST=locally run:
./site_utils/setup_dev_autotest.sh -p autotest -a /usr/local/autotest/
./database/migrate.py sync 0 -d AUTOTEST_SERVER_DB
./database/migrate.py sync 1 -d AUTOTEST_SERVER_DB
Confirm the database and tables are created for version 1 and deleted for version 0.
DEPLOY=migrate_server_db
Change-Id: Iea923b2bd5eca81689ac888322f7ae66e6938f10
Reviewed-on: https://chromium-review.googlesource.com/225971
Tested-by: Dan Shi <dshi@chromium.org>
Reviewed-by: Prashanth B <beeps@chromium.org>
Commit-Queue: Dan Shi <dshi@chromium.org>
diff --git a/database/database_connection.py b/database/database_connection.py
index 8012eae..90b43e5 100644
--- a/database/database_connection.py
+++ b/database/database_connection.py
@@ -175,23 +175,61 @@
self._read_options()
- def _get_option(self, name, provided_value):
+ def _get_option(self, name, provided_value, use_afe_setting=False):
+ """Get value of given option from global config.
+
+ @param name: Name of the config.
+ @param provided_value: Value being provided to override the one from
+ global config.
+ @param use_afe_setting: Force to use the settings in AFE, default is
+ False.
+ """
+ # TODO(dshi): This function returns the option value depends on multiple
+ # conditions. The value of `provided_value` has highest priority, then
+ # the code checks if use_afe_setting is True, if that's the case, force
+ # to use settings in AUTOTEST_WEB. At last the value is retrieved from
+ # specified global config section.
+ # The logic is too complicated for a generic function named like
+ # _get_option. Ideally we want to make it clear from caller that it
+ # wants to get database credential from one of the 3 ways:
+ # 1. Use the credential from given config section
+ # 2. Use the credential from AUTOTEST_WEB section
+ # 3. Use the credential provided by caller.
if provided_value is not None:
return provided_value
- if self.global_config_section:
+ section = ('AUTOTEST_WEB' if use_afe_setting else
+ self.global_config_section)
+ if section:
global_config_name = _GLOBAL_CONFIG_NAMES.get(name, name)
return global_config.global_config.get_config_value(
- self.global_config_section, global_config_name)
+ section, global_config_name)
+
return getattr(self, name, None)
def _read_options(self, db_type=None, host=None, username=None,
password=None, db_name=None):
- self.db_type = self._get_option('db_type', db_type)
- self.host = self._get_option('host', host)
- self.username = self._get_option('username', username)
- self.password = self._get_option('password', password)
+ """Read database information from global config.
+
+ Unless any parameter is specified a value, the connection will use
+ database name from given configure section (self.global_config_section),
+ and database credential from AFE database settings (AUTOTEST_WEB).
+
+ @param db_type: database type, default to None.
+ @param host: database hostname, default to None.
+ @param username: user name for database connection, default to None.
+ @param password: database password, default to None.
+ @param db_name: database name, default to None.
+ """
self.db_name = self._get_option('db_name', db_name)
+ use_afe_setting = not bool(db_type or host or username or password)
+
+ # Database credential can be provided by the caller, as passed in from
+ # function connect.
+ self.db_type = self._get_option('db_type', db_type, use_afe_setting)
+ self.host = self._get_option('host', host, use_afe_setting)
+ self.username = self._get_option('username', username, use_afe_setting)
+ self.password = self._get_option('password', password, use_afe_setting)
def _get_backend(self, db_type):
diff --git a/database/migrate.py b/database/migrate.py
index eca776b..be99aea 100755
--- a/database/migrate.py
+++ b/database/migrate.py
@@ -10,8 +10,10 @@
_AUTODIR = os.path.join(os.path.dirname(__file__), '..')
_MIGRATIONS_DIRS = {
- 'AUTOTEST_WEB' : os.path.join(_AUTODIR, 'frontend', 'migrations'),
- 'TKO' : os.path.join(_AUTODIR, 'tko', 'migrations'),
+ 'AUTOTEST_WEB': os.path.join(_AUTODIR, 'frontend', 'migrations'),
+ 'TKO': os.path.join(_AUTODIR, 'tko', 'migrations'),
+ 'AUTOTEST_SERVER_DB': os.path.join(_AUTODIR, 'database',
+ 'server_db_migrations'),
}
_DEFAULT_MIGRATIONS_DIR = 'migrations' # use CWD
@@ -262,6 +264,8 @@
def _migrate_from_base(self):
+ """Initialize the AFE database.
+ """
self.confirm_initialization()
migration_script = utils.read_file(
diff --git a/database/server_db_migrations/001_initial_db.py b/database/server_db_migrations/001_initial_db.py
new file mode 100644
index 0000000..7c05673
--- /dev/null
+++ b/database/server_db_migrations/001_initial_db.py
@@ -0,0 +1,105 @@
+def migrate_up(manager):
+ """migrate up the database schema.
+
+ @param manager: Instance of MigrationManager.
+ """
+ manager.execute_script(INIT_DB_SQL)
+ manager.execute_script(INIT_VERSION_SQL)
+
+
+def migrate_down(manager):
+ """migrate down the database schema.
+
+ @param manager: Instance of MigrationManager.
+ """
+ manager.execute_script(DROP_DB_SQL)
+
+
+INIT_VERSION_SQL = """\
+INSERT INTO migrate_info VALUES (0)
+"""
+
+INIT_DB_SQL = """\
+-- MySQL dump 10.13 Distrib 5.5.40, for debian-linux-gnu (x86_64)
+--
+-- Host: localhost Database: chromeos_lab_servers
+-- ------------------------------------------------------
+-- Server version 5.5.40-0ubuntu0.14.04.1
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `migrate_info`
+--
+
+DROP TABLE IF EXISTS `migrate_info`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `migrate_info` (
+ `version` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `server_roles`
+--
+
+DROP TABLE IF EXISTS `server_roles`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `server_roles` (
+ `id` int(11) NOT NULL auto_increment,
+ `server_id` int(11) DEFAULT NULL,
+ `role` varchar(128) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `fk_server_roles_server_idx` (`server_id`),
+ CONSTRAINT `fk_server_roles_server` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `servers`
+--
+
+DROP TABLE IF EXISTS `servers`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `servers` (
+ `id` int(11) NOT NULL auto_increment,
+ `hostname` varchar(128) NOT NULL,
+ `cname` varchar(128) DEFAULT NULL,
+ `status` varchar(128) DEFAULT NULL,
+ `date_created` datetime DEFAULT NULL,
+ `date_modified` datetime DEFAULT NULL,
+ `note` text,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `hostname_UNIQUE` (`hostname`),
+ UNIQUE KEY `cname_UNIQUE` (`cname`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2014-10-28 12:08:40
+"""
+
+DROP_DB_SQL = """\
+DROP TABLE IF EXISTS `server_roles`;
+DROP TABLE IF EXISTS `servers`;
+"""
diff --git a/database/server_db_migrations/__init__.py b/database/server_db_migrations/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/database/server_db_migrations/__init__.py
diff --git a/global_config.ini b/global_config.ini
index e56b661..52cdeba 100644
--- a/global_config.ini
+++ b/global_config.ini
@@ -43,6 +43,9 @@
global_db_min_retry_delay:
global_db_max_retry_delay:
+[AUTOTEST_SERVER_DB]
+database: chromeos_lab_servers
+
[SHARD]
# If this is not None, the instance is considered a shard.
# The value should be the hostname of the local shard.
@@ -102,6 +105,10 @@
# Time in hours to wait before giving up on crash collection.
crash_collection_hours_to_wait: 0.001
+# If True, use autotest_server_db to verify the host before running services
+# like scheduler, host-scheduler and suite-scheduler.
+use_server_db: False
+
[SCHEDULER]
die_on_orphans: False
enable_scheduler: True
diff --git a/site_utils/setup_dev_autotest.sh b/site_utils/setup_dev_autotest.sh
index 8b7b11a..f015746 100755
--- a/site_utils/setup_dev_autotest.sh
+++ b/site_utils/setup_dev_autotest.sh
@@ -174,37 +174,47 @@
fi
echo -e "Done!\n"
-echo "Setting up Database: chromeos_autotest_db in MySQL..."
-if mysql -u root -e ';' 2> /dev/null ; then
- PASSWD_STRING=
-elif mysql -u root -p"${PASSWD}" -e ';' 2> /dev/null ; then
- PASSWD_STRING="-p${PASSWD}"
-else
- PASSWD_STRING="-p"
-fi
+# Check if database exists, clobber existing database with user consent.
+#
+# Arguments: Name of the database
+check_database()
+{
+ local db_name=$1
+ echo "Setting up Database: $db_name in MySQL..."
+ if mysql -u root -e ';' 2> /dev/null ; then
+ PASSWD_STRING=
+ elif mysql -u root -p"${PASSWD}" -e ';' 2> /dev/null ; then
+ PASSWD_STRING="-p${PASSWD}"
+ else
+ PASSWD_STRING="-p"
+ fi
-if ! mysqladmin ping ; then
- sudo service mysql start
-fi
+ if ! mysqladmin ping ; then
+ sudo service mysql start
+ fi
-CLOBBERDB='y'
-EXISTING_DATABASE=$(mysql -u root "${PASSWD_STRING}" -e "SELECT SCHEMA_NAME \
-FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'chromeos_autotest_db'")
+ local clobberdb='y'
+ local existing_database=$(mysql -u root "${PASSWD_STRING}" -e "SELECT \
+ SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$db_name'")
-if [ -n "${EXISTING_DATABASE}" ]; then
- get_y_or_n CLOBBERDB "Clobber existing shadow config? [Y/n]: " "n"
-fi
+ if [ -n "${existing_database}" ]; then
+ get_y_or_n clobberdb "Clobber existing MySQL database? [Y/n]: " "n"
+ fi
-SQL_COMMAND="drop database if exists chromeos_autotest_db; \
-create database chromeos_autotest_db; \
-grant all privileges on chromeos_autotest_db.* TO \
-'chromeosqa-admin'@'localhost' identified by '${PASSWD}'; \
-FLUSH PRIVILEGES;"
+ local sql_command="drop database if exists $db_name; \
+ create database $db_name; \
+ grant all privileges on $db_name.* TO \
+ 'chromeosqa-admin'@'localhost' identified by '${PASSWD}'; \
+ FLUSH PRIVILEGES;"
-if [[ "${CLOBBERDB}" = 'y' ]]; then
- mysql -u root "${PASSWD_STRING}" -e "${SQL_COMMAND}"
-fi
-echo -e "Done!\n"
+ if [[ "${clobberdb}" = 'y' ]]; then
+ mysql -u root "${PASSWD_STRING}" -e "${sql_command}"
+ fi
+ echo -e "Done!\n"
+}
+
+check_database 'chromeos_autotest_db'
+check_database 'chromeos_lab_servers'
AT_DIR=/usr/local/autotest
echo -n "Bind-mounting your autotest dir at ${AT_DIR}..."
@@ -246,6 +256,10 @@
"${AT_DIR}"/utils/test_importer.py
echo -e "Done!\n"
+echo "Initializing chromeos_lab_servers mysql DB..."
+"${AT_DIR}"/database/migrate.py sync -f -d AUTOTEST_SERVER_DB
+echo -e "Done!\n"
+
echo "Configuring apache to run the autotest web interface..."
if [ ! -d /etc/apache2/run ]; then
sudo mkdir /etc/apache2/run