<commit_before><?php namespace Limoncello\Passport\Adaptors\MySql;

/**
 * Copyright 2015-2017 <EMAIL>
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DBALException;
use Limoncello\Passport\Contracts\Entities\DatabaseSchemeInterface;
use Limoncello\Passport\Traits\DatabaseSchemeMigrationTrait as BaseDatabaseSchemeMigrationTrait;

/**
 * @package Limoncello\Passport
 */
trait DatabaseSchemeMigrationTrait
{
    use BaseDatabaseSchemeMigrationTrait {
        BaseDatabaseSchemeMigrationTrait::createDatabaseScheme as baseCreateDatabaseScheme;
        BaseDatabaseSchemeMigrationTrait::removeDatabaseScheme as baseRemoveDatabaseScheme;
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @return void
     */
    protected function createDatabaseScheme(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $this->baseCreateDatabaseScheme($connection, $scheme);
        $this->createClientsView($connection, $scheme);
        $this->createTokensView($connection, $scheme);
        $this->createUsersView($connection, $scheme);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @return void
     */
    protected function removeDatabaseScheme(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $this->baseRemoveDatabaseScheme($connection, $scheme);
        $this->removeClientsView($connection, $scheme);
        $this->removeTokensView($connection, $scheme);
        $this->removeUsersView($connection, $scheme);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createTokensView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view                = $scheme->getTokensView();
        $tokens              = $scheme->getTokensTable();
        $intermediate        = $scheme->getTokensScopesTable();
        $tokensTokenId       = $scheme->getTokensIdentityColumn();
        $intermediateTokenId = $scheme->getTokensScopesTokenIdentityColumn();
        $intermediateScopeId = $scheme->getTokensScopesScopeIdentityColumn();
        $scopes              = $scheme->getTokensViewScopesColumn();

        $sql = <<< EOT
CREATE OR REPLACE VIEW {$view} AS
    SELECT
      t.*,
      GROUP_CONCAT(DISTINCT s.{$intermediateScopeId} SEPARATOR ' ') AS {$scopes}
    FROM {$tokens} AS t
      LEFT JOIN {$intermediate} AS s ON t.{$tokensTokenId} = s.{$intermediateTokenId}
    GROUP BY t.{$tokensTokenId};
EOT;
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeTokensView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getTokensView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createClientsView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view             = $scheme->getClientsView();
        $scopes           = $scheme->getClientsViewScopesColumn();
        $redirectUris     = $scheme->getClientsViewRedirectUrisColumn();
        $clientsScopes    = $scheme->getClientsScopesTable();
        $clientsUris      = $scheme->getRedirectUrisTable();
        $clients          = $scheme->getClientsTable();
        $clientsClientId  = $scheme->getClientsIdentityColumn();
        $clScopesClientId = $scheme->getClientsScopesClientIdentityColumn();
        $clUrisClientId   = $scheme->getRedirectUrisClientIdentityColumn();
        $urisValue        = $scheme->getRedirectUrisValueColumn();
        $scopesScopeId    = $scheme->getScopesIdentityColumn();
        $sql              = <<< EOT
CREATE VIEW {$view} AS
    SELECT
      c.*,
      GROUP_CONCAT(DISTINCT s.{$scopesScopeId} SEPARATOR ' ') AS {$scopes},
      GROUP_CONCAT(DISTINCT u.{$urisValue} SEPARATOR ' ')     AS {$redirectUris}
    FROM {$clients} AS c
      LEFT JOIN {$clientsScopes} AS s ON c.{$clientsClientId} = s.{$clScopesClientId}
      LEFT JOIN {$clientsUris}   AS u ON c.{$clientsClientId} = u.{$clUrisClientId}
    GROUP BY c.{$clientsClientId};
EOT;
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeClientsView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getClientsView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createUsersView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $users = $scheme->getUsersTable();
        if ($users !== null) {
            $view          = $scheme->getUsersView();
            $tokensValue   = $scheme->getTokensValueColumn();
            $tokensValueAt = $scheme->getTokensValueCreatedAtColumn();
            $tokensView    = $scheme->getTokensView();
            $tokensUserId  = $scheme->getTokensUserIdentityColumn();
            $usersUserId   = $scheme->getUsersIdentityColumn();

            $sql = <<< EOT
CREATE OR REPLACE VIEW {$view} AS
    SELECT
        t.$tokensValue, t.$tokensValueAt, u.*
    FROM {$tokensView} AS t
      LEFT JOIN {$users} AS u ON t.{$tokensUserId} = u.{$usersUserId};
EOT;
            $connection->exec($sql);
        }
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeUsersView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getUsersView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }
}<commit_msg>#39 Add scopes to tokens view.<commit_after><?php namespace Limoncello\Passport\Adaptors\MySql;

/**
 * Copyright 2015-2017 <EMAIL>
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DBALException;
use Limoncello\Passport\Contracts\Entities\DatabaseSchemeInterface;
use Limoncello\Passport\Traits\DatabaseSchemeMigrationTrait as BaseDatabaseSchemeMigrationTrait;

/**
 * @package Limoncello\Passport
 */
trait DatabaseSchemeMigrationTrait
{
    use BaseDatabaseSchemeMigrationTrait {
        BaseDatabaseSchemeMigrationTrait::createDatabaseScheme as baseCreateDatabaseScheme;
        BaseDatabaseSchemeMigrationTrait::removeDatabaseScheme as baseRemoveDatabaseScheme;
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @return void
     */
    protected function createDatabaseScheme(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $this->baseCreateDatabaseScheme($connection, $scheme);
        $this->createClientsView($connection, $scheme);
        $this->createTokensView($connection, $scheme);
        $this->createUsersView($connection, $scheme);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @return void
     */
    protected function removeDatabaseScheme(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $this->baseRemoveDatabaseScheme($connection, $scheme);
        $this->removeClientsView($connection, $scheme);
        $this->removeTokensView($connection, $scheme);
        $this->removeUsersView($connection, $scheme);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createTokensView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view                = $scheme->getTokensView();
        $tokens              = $scheme->getTokensTable();
        $intermediate        = $scheme->getTokensScopesTable();
        $tokensTokenId       = $scheme->getTokensIdentityColumn();
        $intermediateTokenId = $scheme->getTokensScopesTokenIdentityColumn();
        $intermediateScopeId = $scheme->getTokensScopesScopeIdentityColumn();
        $scopes              = $scheme->getTokensViewScopesColumn();

        $sql = <<< EOT
CREATE OR REPLACE VIEW {$view} AS
    SELECT
      t.*,
      GROUP_CONCAT(DISTINCT s.{$intermediateScopeId} SEPARATOR ' ') AS {$scopes}
    FROM {$tokens} AS t
      LEFT JOIN {$intermediate} AS s ON t.{$tokensTokenId} = s.{$intermediateTokenId}
    GROUP BY t.{$tokensTokenId};
EOT;
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeTokensView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getTokensView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createClientsView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view             = $scheme->getClientsView();
        $scopes           = $scheme->getClientsViewScopesColumn();
        $redirectUris     = $scheme->getClientsViewRedirectUrisColumn();
        $clientsScopes    = $scheme->getClientsScopesTable();
        $clientsUris      = $scheme->getRedirectUrisTable();
        $clients          = $scheme->getClientsTable();
        $clientsClientId  = $scheme->getClientsIdentityColumn();
        $clScopesClientId = $scheme->getClientsScopesClientIdentityColumn();
        $clUrisClientId   = $scheme->getRedirectUrisClientIdentityColumn();
        $urisValue        = $scheme->getRedirectUrisValueColumn();
        $scopesScopeId    = $scheme->getScopesIdentityColumn();
        $sql              = <<< EOT
CREATE VIEW {$view} AS
    SELECT
      c.*,
      GROUP_CONCAT(DISTINCT s.{$scopesScopeId} SEPARATOR ' ') AS {$scopes},
      GROUP_CONCAT(DISTINCT u.{$urisValue} SEPARATOR ' ')     AS {$redirectUris}
    FROM {$clients} AS c
      LEFT JOIN {$clientsScopes} AS s ON c.{$clientsClientId} = s.{$clScopesClientId}
      LEFT JOIN {$clientsUris}   AS u ON c.{$clientsClientId} = u.{$clUrisClientId}
    GROUP BY c.{$clientsClientId};
EOT;
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeClientsView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getClientsView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function createUsersView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $users = $scheme->getUsersTable();
        if ($users !== null) {
            $view          = $scheme->getUsersView();
            $tokensValue   = $scheme->getTokensValueColumn();
            $tokensValueAt = $scheme->getTokensValueCreatedAtColumn();
            $tokensScopes  = $scheme->getTokensViewScopesColumn();
            $tokensView    = $scheme->getTokensView();
            $tokensUserId  = $scheme->getTokensUserIdentityColumn();
            $usersUserId   = $scheme->getUsersIdentityColumn();

            $sql = <<< EOT
CREATE OR REPLACE VIEW {$view} AS
    SELECT
        t.$tokensValue, t.$tokensValueAt, t.$tokensScopes, u.*
    FROM {$tokensView} AS t
      LEFT JOIN {$users} AS u ON t.{$tokensUserId} = u.{$usersUserId};
EOT;
            $connection->exec($sql);
        }
    }

    /**
     * @param Connection              $connection
     * @param DatabaseSchemeInterface $scheme
     *
     * @throws DBALException
     *
     * @return void
     */
    protected function removeUsersView(Connection $connection, DatabaseSchemeInterface $scheme)
    {
        $view = $scheme->getUsersView();
        $sql  = "DROP VIEW IF EXISTS {$view}";
        $connection->exec($sql);
    }
}
{'fraction_non_alphanumeric': 0.11219650314999646, 'fraction_numerical': 0.0018404473702838535, 'mean_word_length': 2.6887728459530025, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 90, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3013150', 'n_tokens_mistral': 3905, 'n_tokens_neox': 3859, 'n_words': 862}