One of the two requirements of the parent task T151158 is to support creating a query against Quarry's own database. I'm splitting that requirement into its own task.
This should be done with a read-only user. The database is currently in Trove, so we can easily create a quarry_readonly user there.
I checked the db schema (pasted below) and I have some minor privacy concerns if we were to expose all the tables and columns, as it would leak some user ids, and it would make it possible to check which queries have been starred by which users.
A more conservative approach could be to create one or more redacted views, depending on which data people are interested in querying.
Please leave a comment below if you have a use case for this feature, specifying which tables/columns need to be exposed.
MariaDB [quarry]> SHOW TABLES; +------------------+ | Tables_in_quarry | +------------------+ | query | | query_revision | | query_run | | star | | user | | user_group | +------------------+ 6 rows in set (0.001 sec) | query | CREATE TABLE `query` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `title` varchar(1024) DEFAULT NULL, `latest_rev_id` int(10) unsigned DEFAULT NULL, `last_touched` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `parent_id` int(10) unsigned DEFAULT NULL, `published` smallint(6) NOT NULL DEFAULT 0, `description` text DEFAULT NULL, PRIMARY KEY (`id`), KEY `query_parent_id_index` (`parent_id`) ) ENGINE=InnoDB AUTO_INCREMENT=83811 DEFAULT CHARSET=utf8 | | query_revision | CREATE TABLE `query_revision` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` text DEFAULT NULL, `query_id` int(10) unsigned NOT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp(), `latest_run_id` int(10) unsigned DEFAULT NULL, `query_database` varchar(66) DEFAULT NULL, PRIMARY KEY (`id`), KEY `query_rev_query_id_index` (`query_id`), KEY `query_rev_query_database_index` (`query_database`) ) ENGINE=InnoDB AUTO_INCREMENT=900685 DEFAULT CHARSET=utf8 | | query_run | CREATE TABLE `query_run` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `query_rev_id` int(10) unsigned NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT 0, `timestamp` timestamp NOT NULL DEFAULT current_timestamp(), `task_id` varchar(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `extra_info` text CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `query_run_status_index` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=874191 DEFAULT CHARSET=utf8 | | star | CREATE TABLE `star` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `query_id` int(10) unsigned NOT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `star_user_query_index` (`user_id`,`query_id`), KEY `star_user_id_index` (`user_id`), KEY `star_query_id_index` (`query_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1876 DEFAULT CHARSET=latin1 | | user | CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `wiki_uid` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `wiki_uid` (`wiki_uid`), UNIQUE KEY `user_wiki_uid` (`wiki_uid`), UNIQUE KEY `username` (`username`), UNIQUE KEY `user_username_index` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=8925 DEFAULT CHARSET=latin1 | | user_group | CREATE TABLE `user_group` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `group_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), KEY `user_group_user_group_index` (`user_id`,`group_name`), KEY `user_group_user_id_index` (`user_id`), KEY `user_group_group_name_index` (`group_name`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |