<commit_before>// Copyright 2015 clair authors
//
// 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.

package pgsql

import (
	"fmt"
	"strconv"
)

var queries map[string]string

func init() {
	queries = make(map[string]string)

	queries["l_vulnerability_affects_featureversion"] = `LOCK Vulnerability_Affects_FeatureVersion IN SHARE ROW EXCLUSIVE MODE`

	// keyvalue.go
	queries["u_keyvalue"] = `UPDATE KeyValue SET value = $1 WHERE key = $2`
	queries["i_keyvalue"] = `INSERT INTO KeyValue(key, value) VALUES($1, $2)`
	queries["s_keyvalue"] = `SELECT value FROM KeyValue WHERE key = $1`

	// namespace.go
	queries["soi_namespace"] = `
    WITH new_namespace AS (
      INSERT INTO Namespace(name)
      SELECT CAST($1 AS VARCHAR)
      WHERE NOT EXISTS (SELECT name FROM Namespace WHERE name = $1)
      RETURNING id
    )
    SELECT id FROM Namespace WHERE name = $1
    UNION
    SELECT id FROM new_namespace`

	queries["l_namespace"] = `SELECT id, name FROM Namespace`

	// feature.go
	queries["soi_feature"] = `
    WITH new_feature AS (
      INSERT INTO Feature(name, namespace_id)
      SELECT CAST($1 AS VARCHAR), CAST($2 AS INTEGER)
      WHERE NOT EXISTS (SELECT id FROM Feature WHERE name = $1 AND namespace_id = $2)
      RETURNING id
    )
    SELECT id FROM Feature WHERE name = $1 AND namespace_id = $2
    UNION
    SELECT id FROM new_feature`

	queries["soi_featureversion"] = `
    WITH new_featureversion AS (
      INSERT INTO FeatureVersion(feature_id, version)
      SELECT CAST($1 AS INTEGER), CAST($2 AS VARCHAR)
      WHERE NOT EXISTS (SELECT id FROM FeatureVersion WHERE feature_id = $1 AND version = $2)
      RETURNING id
    )
    SELECT 'exi', id FROM FeatureVersion WHERE feature_id = $1 AND version = $2
    UNION
    SELECT 'new', id FROM new_featureversion
  `

	queries["s_vulnerability_fixedin_feature"] = `
    SELECT id, vulnerability_id, version FROM Vulnerability_FixedIn_Feature
    WHERE feature_id = $1`

	queries["i_vulnerability_affects_featureversion"] = `
    INSERT INTO Vulnerability_Affects_FeatureVersion(vulnerability_id,
    featureversion_id, fixedin_id) VALUES($1, $2, $3)`

	// layer.go
	queries["s_layer"] = `
    SELECT l.id, l.name, l.engineversion, p.id, p.name, n.id, n.name
    FROM Layer l
      LEFT JOIN Layer p ON l.parent_id = p.id
      LEFT JOIN Namespace n ON l.namespace_id = n.id
    WHERE l.name = $1;`

	queries["s_layer_featureversion"] = `
		WITH RECURSIVE layer_tree(id, name, parent_id, depth, path, cycle) AS(
			SELECT l.id, l.name, l.parent_id, 1, ARRAY[l.id], false
			FROM Layer l
			WHERE l.id = $1
		UNION ALL
			SELECT l.id, l.name, l.parent_id, lt.depth + 1, path || l.id, l.id = ANY(path)
			FROM Layer l, layer_tree lt
			WHERE l.id = lt.parent_id
		)
		SELECT ldf.featureversion_id, ldf.modification, fn.id, fn.name, f.id, f.name, fv.id, fv.version, ltree.id, ltree.name
		FROM Layer_diff_FeatureVersion ldf
		JOIN (
			SELECT row_number() over (ORDER BY depth DESC), id, name FROM layer_tree
		) AS ltree (ordering, id, name) ON ldf.layer_id = ltree.id, FeatureVersion fv, Feature f, Namespace fn
		WHERE ldf.featureversion_id = fv.id AND fv.feature_id = f.id AND f.namespace_id = fn.id
		ORDER BY ltree.ordering`

	queries["s_featureversions_vulnerabilities"] = `
    SELECT vafv.featureversion_id, v.id, v.name, v.description, v.link, v.severity, v.metadata,
      vn.name, vfif.version
    FROM Vulnerability_Affects_FeatureVersion vafv, Vulnerability v,
         Namespace vn, Vulnerability_FixedIn_Feature vfif
    WHERE vafv.featureversion_id = ANY($1::integer[])
          AND vafv.vulnerability_id = v.id
          AND vafv.fixedin_id = vfif.id
          AND v.namespace_id = vn.id
          AND v.deleted_at IS NULL`

	queries["i_layer"] = `
    INSERT INTO Layer(name, engineversion, parent_id, namespace_id, created_at)
    VALUES($1, $2, $3, $4, CURRENT_TIMESTAMP)
    RETURNING id`

	queries["u_layer"] = `UPDATE LAYER SET engineversion = $2, namespace_id = $3 WHERE id = $1`

	queries["r_layer_diff_featureversion"] = `<commit_msg>database: modify join table in FindLayer to reduce cost by 3.5x<commit_after>// Copyright 2015 clair authors
//
// 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.

package pgsql

import (
	"fmt"
	"strconv"
)

var queries map[string]string

func init() {
	queries = make(map[string]string)

	queries["l_vulnerability_affects_featureversion"] = `LOCK Vulnerability_Affects_FeatureVersion IN SHARE ROW EXCLUSIVE MODE`

	// keyvalue.go
	queries["u_keyvalue"] = `UPDATE KeyValue SET value = $1 WHERE key = $2`
	queries["i_keyvalue"] = `INSERT INTO KeyValue(key, value) VALUES($1, $2)`
	queries["s_keyvalue"] = `SELECT value FROM KeyValue WHERE key = $1`

	// namespace.go
	queries["soi_namespace"] = `
    WITH new_namespace AS (
      INSERT INTO Namespace(name)
      SELECT CAST($1 AS VARCHAR)
      WHERE NOT EXISTS (SELECT name FROM Namespace WHERE name = $1)
      RETURNING id
    )
    SELECT id FROM Namespace WHERE name = $1
    UNION
    SELECT id FROM new_namespace`

	queries["l_namespace"] = `SELECT id, name FROM Namespace`

	// feature.go
	queries["soi_feature"] = `
    WITH new_feature AS (
      INSERT INTO Feature(name, namespace_id)
      SELECT CAST($1 AS VARCHAR), CAST($2 AS INTEGER)
      WHERE NOT EXISTS (SELECT id FROM Feature WHERE name = $1 AND namespace_id = $2)
      RETURNING id
    )
    SELECT id FROM Feature WHERE name = $1 AND namespace_id = $2
    UNION
    SELECT id FROM new_feature`

	queries["soi_featureversion"] = `
    WITH new_featureversion AS (
      INSERT INTO FeatureVersion(feature_id, version)
      SELECT CAST($1 AS INTEGER), CAST($2 AS VARCHAR)
      WHERE NOT EXISTS (SELECT id FROM FeatureVersion WHERE feature_id = $1 AND version = $2)
      RETURNING id
    )
    SELECT 'exi', id FROM FeatureVersion WHERE feature_id = $1 AND version = $2
    UNION
    SELECT 'new', id FROM new_featureversion
  `

	queries["s_vulnerability_fixedin_feature"] = `
    SELECT id, vulnerability_id, version FROM Vulnerability_FixedIn_Feature
    WHERE feature_id = $1`

	queries["i_vulnerability_affects_featureversion"] = `
    INSERT INTO Vulnerability_Affects_FeatureVersion(vulnerability_id,
    featureversion_id, fixedin_id) VALUES($1, $2, $3)`

	// layer.go
	queries["s_layer"] = `
    SELECT l.id, l.name, l.engineversion, p.id, p.name, n.id, n.name
    FROM Layer l
      LEFT JOIN Layer p ON l.parent_id = p.id
      LEFT JOIN Namespace n ON l.namespace_id = n.id
    WHERE l.name = $1;`

	queries["s_layer_featureversion"] = `
		WITH RECURSIVE layer_tree(id, name, parent_id, depth, path, cycle) AS(
			SELECT l.id, l.name, l.parent_id, 1, ARRAY[l.id], false
			FROM Layer l
			WHERE l.id = $1
		UNION ALL
			SELECT l.id, l.name, l.parent_id, lt.depth + 1, path || l.id, l.id = ANY(path)
			FROM Layer l, layer_tree lt
			WHERE l.id = lt.parent_id
		)
		SELECT ldf.featureversion_id, ldf.modification, fn.id, fn.name, f.id, f.name, fv.id, fv.version, ltree.id, ltree.name
		FROM Layer_diff_FeatureVersion ldf
		JOIN (
			SELECT row_number() over (ORDER BY depth DESC), id, name FROM layer_tree
		) AS ltree (ordering, id, name) ON ldf.layer_id = ltree.id, FeatureVersion fv, Feature f, Namespace fn
		WHERE ldf.featureversion_id = fv.id AND fv.feature_id = f.id AND f.namespace_id = fn.id
		ORDER BY ltree.ordering`

	queries["s_featureversions_vulnerabilities"] = `
    SELECT vafv.featureversion_id, v.id, v.name, v.description, v.link, v.severity, v.metadata,
      vn.name, vfif.version
    FROM Vulnerability_Affects_FeatureVersion vafv, Vulnerability v,
         Namespace vn, Vulnerability_FixedIn_Feature vfif
    WHERE vafv.featureversion_id = ANY($1::integer[])
          AND vfif.vulnerability_id = v.id
          AND vafv.fixedin_id = vfif.id
          AND v.namespace_id = vn.id
          AND v.deleted_at IS NULL`

	queries["i_layer"] = `
    INSERT INTO Layer(name, engineversion, parent_id, namespace_id, created_at)
    VALUES($1, $2, $3, $4, CURRENT_TIMESTAMP)
    RETURNING id`

	queries["u_layer"] = `UPDATE LAYER SET engineversion = $2, namespace_id = $3 WHERE id = $1`

	queries["r_layer_diff_featureversion"] = `
{'fraction_non_alphanumeric': 0.10120481927710843, 'fraction_numerical': 0.009857612267250822, 'mean_word_length': 3.5113636363636362, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, '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': '1971418', 'n_tokens_mistral': 3264, 'n_tokens_neox': 3028, 'n_words': 950}