class StringDatabaseStorage

class StringDatabaseStorage implements StringStorageInterface {

  /**
   * The database connection.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Additional database connection options to use in queries.
   *
   * @var array
   */
  protected $options = [];

  /**
   * Constructs a new StringDatabaseStorage class.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   A Database connection to use for reading and writing configuration data.
   * @param array $options
   *   (optional) Any additional database connection options to use in queries.
   */
  public function __construct(Connection $connection, array $options = []) {
    $this->connection = $connection;
    $this->options = $options;
  }

  /**
   * {@inheritdoc}
   */
  public function getStrings(array $conditions = [], array $options = []) {
    return $this
      ->dbStringLoad($conditions, $options, 'Drupal\\locale\\SourceString');
  }

  /**
   * {@inheritdoc}
   */
  public function getTranslations(array $conditions = [], array $options = []) {
    return $this
      ->dbStringLoad($conditions, [
      'translation' => TRUE,
    ] + $options, 'Drupal\\locale\\TranslationString');
  }

  /**
   * {@inheritdoc}
   */
  public function findString(array $conditions) {
    $values = $this
      ->dbStringSelect($conditions)
      ->execute()
      ->fetchAssoc();
    if (!empty($values)) {
      $string = new SourceString($values);
      $string
        ->setStorage($this);
      return $string;
    }
  }

  /**
   * {@inheritdoc}
   */
  public function findTranslation(array $conditions) {
    $values = $this
      ->dbStringSelect($conditions, [
      'translation' => TRUE,
    ])
      ->execute()
      ->fetchAssoc();
    if (!empty($values)) {
      $string = new TranslationString($values);
      $this
        ->checkVersion($string, \Drupal::VERSION);
      $string
        ->setStorage($this);
      return $string;
    }
  }

  /**
   * {@inheritdoc}
   */
  public function getLocations(array $conditions = []) {
    $query = $this->connection
      ->select('locales_location', 'l', $this->options)
      ->fields('l');
    foreach ($conditions as $field => $value) {

      // Cast scalars to array so we can consistently use an IN condition.
      $query
        ->condition('l.' . $field, (array) $value, 'IN');
    }
    return $query
      ->execute()
      ->fetchAll();
  }

  /**
   * {@inheritdoc}
   */
  public function countStrings() {
    return $this
      ->dbExecute("SELECT COUNT(*) FROM {locales_source}")
      ->fetchField();
  }

  /**
   * {@inheritdoc}
   */
  public function countTranslations() {
    return $this
      ->dbExecute("SELECT t.language, COUNT(*) AS translated FROM {locales_source} s INNER JOIN {locales_target} t ON s.lid = t.lid GROUP BY t.language")
      ->fetchAllKeyed();
  }

  /**
   * {@inheritdoc}
   */
  public function save($string) {
    if ($string
      ->isNew()) {
      $result = $this
        ->dbStringInsert($string);
      if ($string
        ->isSource() && $result) {

        // Only for source strings, we set the locale identifier.
        $string
          ->setId($result);
      }
      $string
        ->setStorage($this);
    }
    else {
      $this
        ->dbStringUpdate($string);
    }

    // Update locations if they come with the string.
    $this
      ->updateLocation($string);
    return $this;
  }

  /**
   * Update locations for string.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   */
  protected function updateLocation($string) {
    if ($locations = $string
      ->getLocations(TRUE)) {
      $created = FALSE;
      foreach ($locations as $type => $location) {
        foreach ($location as $name => $lid) {

          // Make sure that the name isn't longer than 255 characters.
          $name = substr($name, 0, 255);
          if (!$lid) {
            $this
              ->dbDelete('locales_location', [
              'sid' => $string
                ->getId(),
              'type' => $type,
              'name' => $name,
            ])
              ->execute();
          }
          elseif ($lid === TRUE) {

            // This is a new location to add, take care not to duplicate.
            $this->connection
              ->merge('locales_location', $this->options)
              ->keys([
              'sid' => $string
                ->getId(),
              'type' => $type,
              'name' => $name,
            ])
              ->fields([
              'version' => \Drupal::VERSION,
            ])
              ->execute();
            $created = TRUE;
          }

          // Loaded locations have 'lid' integer value, nor FALSE, nor TRUE.
        }
      }
      if ($created) {

        // As we've set a new location, check string version too.
        $this
          ->checkVersion($string, \Drupal::VERSION);
      }
    }
  }

  /**
   * Checks whether the string version matches a given version, fix it if not.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   * @param string $version
   *   Drupal version to check against.
   */
  protected function checkVersion($string, $version) {
    if ($string
      ->getId() && $string
      ->getVersion() != $version) {
      $string
        ->setVersion($version);
      $this->connection
        ->update('locales_source', $this->options)
        ->condition('lid', $string
        ->getId())
        ->fields([
        'version' => $version,
      ])
        ->execute();
    }
  }

  /**
   * {@inheritdoc}
   */
  public function delete($string) {
    if ($keys = $this
      ->dbStringKeys($string)) {
      $this
        ->dbDelete('locales_target', $keys)
        ->execute();
      if ($string
        ->isSource()) {
        $this
          ->dbDelete('locales_source', $keys)
          ->execute();
        $this
          ->dbDelete('locales_location', $keys)
          ->execute();
        $string
          ->setId(NULL);
      }
    }
    else {
      throw new StringStorageException('The string cannot be deleted because it lacks some key fields: ' . $string
        ->getString());
    }
    return $this;
  }

  /**
   * {@inheritdoc}
   */
  public function deleteStrings($conditions) {
    $lids = $this
      ->dbStringSelect($conditions, [
      'fields' => [
        'lid',
      ],
    ])
      ->execute()
      ->fetchCol();
    if ($lids) {
      $this
        ->dbDelete('locales_target', [
        'lid' => $lids,
      ])
        ->execute();
      $this
        ->dbDelete('locales_source', [
        'lid' => $lids,
      ])
        ->execute();
      $this
        ->dbDelete('locales_location', [
        'sid' => $lids,
      ])
        ->execute();
    }
  }

  /**
   * {@inheritdoc}
   */
  public function deleteTranslations($conditions) {
    $this
      ->dbDelete('locales_target', $conditions)
      ->execute();
  }

  /**
   * {@inheritdoc}
   */
  public function createString($values = []) {
    return new SourceString($values + [
      'storage' => $this,
    ]);
  }

  /**
   * {@inheritdoc}
   */
  public function createTranslation($values = []) {
    return new TranslationString($values + [
      'storage' => $this,
      'is_new' => TRUE,
    ]);
  }

  /**
   * Gets table alias for field.
   *
   * @param string $field
   *   One of the field names of the locales_source, locates_location,
   *   locales_target tables to find the table alias for.
   *
   * @return string
   *   One of the following values:
   *   - 's' for "source", "context", "version" (locales_source table fields).
   *   - 'l' for "type", "name" (locales_location table fields)
   *   - 't' for "language", "translation", "customized" (locales_target
   *     table fields)
   */
  protected function dbFieldTable($field) {
    if (in_array($field, [
      'language',
      'translation',
      'customized',
    ])) {
      return 't';
    }
    elseif (in_array($field, [
      'type',
      'name',
    ])) {
      return 'l';
    }
    else {
      return 's';
    }
  }

  /**
   * Gets table name for storing string object.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   *
   * @return string
   *   The table name.
   */
  protected function dbStringTable($string) {
    if ($string
      ->isSource()) {
      return 'locales_source';
    }
    elseif ($string
      ->isTranslation()) {
      return 'locales_target';
    }
  }

  /**
   * Gets keys values that are in a database table.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   *
   * @return array
   *   Array with key fields if the string has all keys, or empty array if not.
   */
  protected function dbStringKeys($string) {
    if ($string
      ->isSource()) {
      $keys = [
        'lid',
      ];
    }
    elseif ($string
      ->isTranslation()) {
      $keys = [
        'lid',
        'language',
      ];
    }
    if (!empty($keys) && ($values = $string
      ->getValues($keys)) && count($keys) == count($values)) {
      return $values;
    }
    else {
      return [];
    }
  }

  /**
   * Loads multiple string objects.
   *
   * @param array $conditions
   *   Any of the conditions used by dbStringSelect().
   * @param array $options
   *   Any of the options used by dbStringSelect().
   * @param string $class
   *   Class name to use for fetching returned objects.
   *
   * @return \Drupal\locale\StringInterface[]
   *   Array of objects of the class requested.
   */
  protected function dbStringLoad(array $conditions, array $options, $class) {
    $strings = [];
    $result = $this
      ->dbStringSelect($conditions, $options)
      ->execute();
    foreach ($result as $item) {

      /** @var \Drupal\locale\StringInterface $string */
      $string = new $class($item);
      $string
        ->setStorage($this);
      $strings[] = $string;
    }
    return $strings;
  }

  /**
   * Builds a SELECT query with multiple conditions and fields.
   *
   * The query uses both 'locales_source' and 'locales_target' tables.
   * Note that by default, as we are selecting both translated and untranslated
   * strings target field's conditions will be modified to match NULL rows too.
   *
   * @param array $conditions
   *   An associative array with field => value conditions that may include
   *   NULL values. If a language condition is included it will be used for
   *   joining the 'locales_target' table.
   * @param array $options
   *   An associative array of additional options. It may contain any of the
   *   options used by Drupal\locale\StringStorageInterface::getStrings() and
   *   these additional ones:
   *   - 'translation', Whether to include translation fields too. Defaults to
   *     FALSE.
   *
   * @return \Drupal\Core\Database\Query\Select
   *   Query object with all the tables, fields and conditions.
   */
  protected function dbStringSelect(array $conditions, array $options = []) {

    // Start building the query with source table and check whether we need to
    // join the target table too.
    $query = $this->connection
      ->select('locales_source', 's', $this->options)
      ->fields('s');

    // Figure out how to join and translate some options into conditions.
    if (isset($conditions['translated'])) {

      // This is a meta-condition we need to translate into simple ones.
      if ($conditions['translated']) {

        // Select only translated strings.
        $join = 'innerJoin';
      }
      else {

        // Select only untranslated strings.
        $join = 'leftJoin';
        $conditions['translation'] = NULL;
      }
      unset($conditions['translated']);
    }
    else {
      $join = !empty($options['translation']) ? 'leftJoin' : FALSE;
    }
    if ($join) {
      if (isset($conditions['language'])) {

        // If we've got a language condition, we use it for the join.
        $query
          ->{$join}('locales_target', 't', "t.lid = s.lid AND t.language = :langcode", [
          ':langcode' => $conditions['language'],
        ]);
        unset($conditions['language']);
      }
      else {

        // Since we don't have a language, join with locale id only.
        $query
          ->{$join}('locales_target', 't', "t.lid = s.lid");
      }
      if (!empty($options['translation'])) {

        // We cannot just add all fields because 'lid' may get null values.
        $query
          ->fields('t', [
          'language',
          'translation',
          'customized',
        ]);
      }
    }

    // If we have conditions for location's type or name, then we need the
    // location table, for which we add a subquery. We cast any scalar value to
    // array so we can consistently use IN conditions.
    if (isset($conditions['type']) || isset($conditions['name'])) {
      $subquery = $this->connection
        ->select('locales_location', 'l', $this->options)
        ->fields('l', [
        'sid',
      ]);
      foreach ([
        'type',
        'name',
      ] as $field) {
        if (isset($conditions[$field])) {
          $subquery
            ->condition('l.' . $field, (array) $conditions[$field], 'IN');
          unset($conditions[$field]);
        }
      }
      $query
        ->condition('s.lid', $subquery, 'IN');
    }

    // Add conditions for both tables.
    foreach ($conditions as $field => $value) {
      $table_alias = $this
        ->dbFieldTable($field);
      $field_alias = $table_alias . '.' . $field;
      if (is_null($value)) {
        $query
          ->isNull($field_alias);
      }
      elseif ($table_alias == 't' && $join === 'leftJoin') {

        // Conditions for target fields when doing an outer join only make
        // sense if we add also OR field IS NULL.
        $query
          ->condition((new Condition('OR'))
          ->condition($field_alias, (array) $value, 'IN')
          ->isNull($field_alias));
      }
      else {
        $query
          ->condition($field_alias, (array) $value, 'IN');
      }
    }

    // Process other options, string filter, query limit, etc.
    if (!empty($options['filters'])) {
      if (count($options['filters']) > 1) {
        $filter = new Condition('OR');
        $query
          ->condition($filter);
      }
      else {

        // If we have a single filter, just add it to the query.
        $filter = $query;
      }
      foreach ($options['filters'] as $field => $string) {
        $filter
          ->condition($this
          ->dbFieldTable($field) . '.' . $field, '%' . $this->connection
          ->escapeLike($string) . '%', 'LIKE');
      }
    }
    if (!empty($options['pager limit'])) {
      $query = $query
        ->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender')
        ->limit($options['pager limit']);
    }
    return $query;
  }

  /**
   * Creates a database record for a string object.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   *
   * @return bool|int
   *   If the operation failed, returns FALSE.
   *   If it succeeded returns the last insert ID of the query, if one exists.
   *
   * @throws \Drupal\locale\StringStorageException
   *   If the string is not suitable for this storage, an exception is thrown.
   */
  protected function dbStringInsert($string) {
    if ($string
      ->isSource()) {
      $string
        ->setValues([
        'context' => '',
        'version' => 'none',
      ], FALSE);
      $fields = $string
        ->getValues([
        'source',
        'context',
        'version',
      ]);
    }
    elseif ($string
      ->isTranslation()) {
      $string
        ->setValues([
        'customized' => 0,
      ], FALSE);
      $fields = $string
        ->getValues([
        'lid',
        'language',
        'translation',
        'customized',
      ]);
    }
    if (!empty($fields)) {
      return $this->connection
        ->insert($this
        ->dbStringTable($string), $this->options)
        ->fields($fields)
        ->execute();
    }
    else {
      throw new StringStorageException('The string cannot be saved: ' . $string
        ->getString());
    }
  }

  /**
   * Updates string object in the database.
   *
   * @param \Drupal\locale\StringInterface $string
   *   The string object.
   *
   * @return bool|int
   *   If the record update failed, returns FALSE. If it succeeded, returns
   *   SAVED_NEW or SAVED_UPDATED.
   *
   * @throws \Drupal\locale\StringStorageException
   *   If the string is not suitable for this storage, an exception is thrown.
   */
  protected function dbStringUpdate($string) {
    if ($string
      ->isSource()) {
      $values = $string
        ->getValues([
        'source',
        'context',
        'version',
      ]);
    }
    elseif ($string
      ->isTranslation()) {
      $values = $string
        ->getValues([
        'translation',
        'customized',
      ]);
    }
    if (!empty($values) && ($keys = $this
      ->dbStringKeys($string))) {
      return $this->connection
        ->merge($this
        ->dbStringTable($string), $this->options)
        ->keys($keys)
        ->fields($values)
        ->execute();
    }
    else {
      throw new StringStorageException('The string cannot be updated: ' . $string
        ->getString());
    }
  }

  /**
   * Creates delete query.
   *
   * @param string $table
   *   The table name.
   * @param array $keys
   *   Array with object keys indexed by field name.
   *
   * @return \Drupal\Core\Database\Query\Delete
   *   Returns a new Delete object for the injected database connection.
   */
  protected function dbDelete($table, $keys) {
    $query = $this->connection
      ->delete($table, $this->options);
    foreach ($keys as $field => $value) {
      $query
        ->condition($field, $value);
    }
    return $query;
  }

  /**
   * Executes an arbitrary SELECT query string with the injected options.
   */
  protected function dbExecute($query, array $args = []) {
    return $this->connection
      ->query($query, $args, $this->options);
  }

}