=== Selfscovery Live Databases ===
Contributors: adlegiant
Tags: quiz, database, mysql, postgres, snowflake
Requires at least: 6.5
Tested up to: 7.1
Requires PHP: 7.4
Requires Plugins: selfscovery-quizzes
Stable tag: 1.0.0
License: GPLv2 or later
License URI: https://www.gnu.org/licenses/gpl-2.0.html

Lets Selfscovery quizzes built from a MySQL, MariaDB, Postgres or Snowflake table rank the whole table live.

== Description ==

An add-on for the Selfscovery Quizzes plugin. In the Selfscovery app, a quiz can be built from a database table, such as a parts catalogue or a list of properties. Published to WordPress, it shows the results saved with it. With this plugin, your site instead ranks the whole table each time a visitor finishes the quiz, so new and changed rows count straight away.

* Add a read-only login for each database under **Selfscovery → Live rankings**. Logins are stored encrypted with your site's security keys.
* MySQL 8.0.17 or newer and MariaDB 10.6 or newer, through PHP's mysqli (always present with WordPress); Postgres, through PHP's pgsql or pdo_pgsql extension if your host enables one; and Snowflake, through its SQL API, which needs only PHP's openssl extension.
* Visitors only send their answers and see the top results. If the database can't answer quickly, the quiz shows its saved results.

Needs the Selfscovery Quizzes plugin, version 0.9.0 or newer.

= How it works =

1. Install and activate Selfscovery Quizzes and this plugin.
2. Under **Selfscovery → Live rankings**, add a read-only login for the database, with the same database name as in the app. The page shows the SQL to create such a login.
3. In the app, turn on **Live results** in the quiz's Database settings and publish it to your site.

== Frequently Asked Questions ==

= How do live rankings stay safe? =

Visitors never send SQL. The ranking query is written by the app when an administrator publishes the quiz and kept on your site, out of public view; it must be a single SELECT whose placeholders are ones this plugin fills. A visitor's request carries only attribute names and weights, names the quiz doesn't use are dropped, and every value is passed to the database as a parameter. Your site signs in with a separate login that should only be able to SELECT, and sets the session to read-only too (Snowflake has no read-only sessions, so its role should only be able to read). Only administrators can add logins or publish quizzes that rank live, and only pages on your own site can ask for a ranking. Requests are rate limited, queries stop after six seconds, and if anything fails the quiz shows its saved results. Database errors are never shown to visitors. Because anyone can take a quiz, grant the login only the tables your quizzes use.

For the strongest protection of saved passwords, define `ADLEGIANT_LIVE_KEY` in wp-config.php with a long random value; otherwise they are encrypted with your site's security keys.

= How does Snowflake sign in? =

With a key pair your site makes itself. Save a Snowflake login under Selfscovery → Live rankings, then run the SQL that page shows once in a Snowsight worksheet. It creates a role that can only read the schema you name and a service user with no password. To retire the key, remove the login and drop the user in Snowflake.

== External services ==

This plugin connects only to databases an administrator adds under Selfscovery → Live rankings. Nothing is contacted until then, and nothing is sent to Selfscovery or Adlegiant.

= Your own MySQL, MariaDB or Postgres database =

When a visitor finishes a quiz that ranks live from a database table, your site signs in to the database you added, with the read-only login you saved, and runs one read-only query. It sends the attribute names and weights from the visitor's answers (as query parameters) and receives the top-ranked rows. It also runs a test query when you save or test the login. No information about the visitor is sent. The database is yours or your host's, so its terms are those of your own provider.

= Snowflake =

For a quiz that ranks live from a Snowflake table, your site calls the Snowflake SQL API at your account's address (`<account>.snowflakecomputing.com`). It sends a short-lived sign-in token made with the key your site created and a read-only query with the attribute names and weights from the visitor's answers, and receives the top-ranked rows. It also sends a test query when you save or test the login. No information about the visitor is sent. Snowflake is provided by Snowflake Inc.: [Terms of Service](https://www.snowflake.com/en/legal/terms-of-service/), [Privacy Notice](https://www.snowflake.com/en/legal/privacy-policy/).

== Changelog ==

= 1.0.0 =
* First release: database live rankings, formerly part of Selfscovery Quizzes 0.8 and earlier.

== Privacy ==

This plugin stores the database logins you add, encrypted, in your site's options. It does not store information about visitors. Deleting the plugin removes the saved logins.
