persistence/sql/migrations/20211019000001000001_merge_authentication_request_tables.sqlite.up.sql
-- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen
INSERT INTO hydra_oauth2_flow (
state,
login_challenge,
requested_scope,
login_verifier,
login_csrf,
subject,
request_url,
login_skip,
client_id,
requested_at,
login_initialized_at,
oidc_context,
login_session_id,
requested_at_audience,
login_remember,
login_remember_for,
login_error,
acr,
login_authenticated_at,
login_was_used,
forced_subject_identifier,
context,
amr,
consent_challenge_id,
consent_verifier,
consent_skip,
consent_csrf,
granted_scope,
consent_remember,
consent_remember_for,
consent_error,
session_access_token,
session_id_token,
consent_was_used,
granted_at_audience,
consent_handled_at
) SELECT
case
when hydra_oauth2_authentication_request_handled.error IS NOT NULL then 128
when hydra_oauth2_consent_request_handled.error IS NOT NULL then 129
when hydra_oauth2_consent_request_handled.was_used = true then 6
when hydra_oauth2_consent_request_handled.challenge IS NOT NULL then 5
when hydra_oauth2_consent_request.challenge IS NOT NULL then 4
when hydra_oauth2_authentication_request_handled.was_used = true then 3
when hydra_oauth2_authentication_request_handled.challenge IS NOT NULL then 2
else 1
end,
hydra_oauth2_authentication_request.challenge,
hydra_oauth2_authentication_request.requested_scope,
hydra_oauth2_authentication_request.verifier,
hydra_oauth2_authentication_request.csrf,
hydra_oauth2_authentication_request.subject,
hydra_oauth2_authentication_request.request_url,
hydra_oauth2_authentication_request.skip,
hydra_oauth2_authentication_request.client_id,
hydra_oauth2_authentication_request.requested_at,
hydra_oauth2_authentication_request.authenticated_at,
coalesce(hydra_oauth2_authentication_request.oidc_context, '{}'),
hydra_oauth2_authentication_request.login_session_id,
hydra_oauth2_authentication_request.requested_at_audience,
coalesce(hydra_oauth2_authentication_request_handled.remember, false),
coalesce(hydra_oauth2_authentication_request_handled.remember_for, 0),
hydra_oauth2_authentication_request_handled.error,
coalesce(hydra_oauth2_authentication_request_handled.acr, ''),
hydra_oauth2_authentication_request_handled.authenticated_at,
coalesce(hydra_oauth2_authentication_request_handled.was_used, false),
coalesce(hydra_oauth2_consent_request.forced_subject_identifier, hydra_oauth2_authentication_request_handled.forced_subject_identifier, ''),
coalesce(hydra_oauth2_authentication_request_handled.context, '{}'),
coalesce(hydra_oauth2_authentication_request_handled.amr, ''),
hydra_oauth2_consent_request.challenge,
hydra_oauth2_consent_request.verifier,
coalesce(hydra_oauth2_consent_request.skip, false),
hydra_oauth2_consent_request.csrf,
coalesce(hydra_oauth2_consent_request_handled.granted_scope, '[]'),
coalesce(hydra_oauth2_consent_request_handled.remember, false),
coalesce(hydra_oauth2_consent_request_handled.remember_for, 0),
hydra_oauth2_consent_request_handled.error,
coalesce(hydra_oauth2_consent_request_handled.session_access_token, '{}'),
coalesce(hydra_oauth2_consent_request_handled.session_id_token, '{}'),
coalesce(hydra_oauth2_consent_request_handled.was_used, false),
coalesce(hydra_oauth2_consent_request_handled.granted_at_audience, '[]'),
hydra_oauth2_consent_request_handled.handled_at
FROM hydra_oauth2_authentication_request
LEFT JOIN hydra_oauth2_authentication_request_handled
ON hydra_oauth2_authentication_request.challenge = hydra_oauth2_authentication_request_handled.challenge
LEFT JOIN hydra_oauth2_consent_request
ON hydra_oauth2_authentication_request.challenge = hydra_oauth2_consent_request.login_challenge
LEFT JOIN hydra_oauth2_consent_request_handled
ON hydra_oauth2_consent_request.challenge = hydra_oauth2_consent_request_handled.challenge;
UPDATE hydra_oauth2_access AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);
UPDATE hydra_oauth2_code AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);
UPDATE hydra_oauth2_oidc AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);
UPDATE hydra_oauth2_refresh AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);
UPDATE hydra_oauth2_pkce AS t1
SET challenge_id = NULL
WHERE challenge_id IS NOT NULL
AND NOT exists(SELECT NULL FROM hydra_oauth2_flow t2 WHERE t1.challenge_id = t2.consent_challenge_id);