pgsql_statements.config

%% -*- mode:erlang, erlang-indent-level: 4;indent-tabs-mode: nil -*-
%% Copyright Chef Software, Inc. All Rights Reserved.
%%
%% This file is provided to you 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.
%%


{ping, <<"SELECT 'pong' as ping LIMIT 1">>}.

{stats,
 <<"SELECT *"
   " FROM"
   "   ( SELECT SUM(seq_scan) as seq_scan,"
   "       SUM(seq_tup_read) as seq_tup_read,"
   "       SUM(idx_scan) as idx_scan,"
   "       SUM(idx_tup_fetch) as idx_tup_fetch,"
   "       SUM(n_tup_ins) as n_tup_ins,"
   "       SUM(n_tup_upd) as n_tup_upd,"
   "       SUM(n_tup_del) as n_tup_del,"
   "       SUM(n_live_tup) as n_live_tup,"
   "       SUM(n_dead_tup) as n_dead_tup"
   "     FROM pg_stat_all_tables ) stats1,"
   "   ( SELECT SUM(heap_blks_read) as heap_blks_read,"
   "       SUM(heap_blks_hit) as heap_blks_hit,"
   "       SUM(idx_blks_read) as idx_blks_read,"
   "       SUM(idx_blks_hit) as idx_blks_hit,"
   "       SUM(toast_blks_read) as toast_blks_read,"
   "       SUM(toast_blks_hit) as toast_blks_hit,"
   "       SUM(tidx_blks_read) as tidx_blks_read,"
   "       SUM(tidx_blks_hit) as tidx_blks_hit"
   "     FROM pg_statio_all_tables ) stats2,"
   "   ( SELECT COUNT(*) as n_active_conns"
   "     FROM pg_stat_activity as pga"
   "     WHERE pga.state = 'active') stats3,"
   "   ( SELECT COUNT(*) as n_conns"
   "     FROM pg_stat_activity) stats4">>}.

%% Query to count the number of nodes for license checks
{count_nodes, <<"SELECT COUNT(*) FROM nodes INNER JOIN orgs ON nodes.org_id = orgs.id;">>}.


{find_node_by_orgid_name,
 <<"SELECT id, authz_id, org_id, name, environment, last_updated_by,"
   " created_at, updated_at, serialized_object FROM nodes WHERE (org_id = $1"
   " AND name = $2) LIMIT 1">>}.

{insert_node,
 <<"INSERT INTO nodes (id, authz_id, org_id, name, environment,"
   " policy_name, policy_group,"
   " last_updated_by, created_at, updated_at, serialized_object ) VALUES"
   " ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)">>}.

{update_node_by_id,
 <<"UPDATE nodes SET environment= $1, policy_name= $2, policy_group= $3, "
   "last_updated_by= $4, updated_at= $5, "
   "serialized_object= $6 WHERE id= $7">>}.

{delete_node_by_id, <<"DELETE FROM nodes WHERE id= $1">>}.

{list_nodes_for_org, <<"SELECT name FROM nodes WHERE org_id= $1">>}.
{list_env_nodes_for_org,
 <<"SELECT name FROM nodes WHERE org_id= $1 AND environment = $2">>}.

{list_node_ids_names_for_org,
 <<"SELECT id, name FROM nodes WHERE org_id = $1">>}.

{bulk_get_node_authz_ids,
 <<"SELECT id, authz_id FROM nodes WHERE id = ANY($1)">>}.

%% bulk_get_nodes query used for fetching nodes returned from
%% search; only returns the gzip JSON data.  If a node has been
%% deleted, you may get back fewer than X nodes and you won't know
%% which ids were not found without parsing and inspecting what did
%% come back. Parameter $1 must be an ARRAY of ids.
{bulk_get_nodes, <<"SELECT serialized_object FROM nodes WHERE id = ANY($1)">>}.

%% role queries

{find_role_by_orgid_name,
 <<"SELECT id, authz_id, org_id, name, last_updated_by,"
   " created_at, updated_at, serialized_object FROM roles WHERE (org_id = $1"
   " AND name = $2) LIMIT 1">>}.

{insert_role,
 <<"INSERT INTO roles (id, authz_id, org_id, name,"
   " last_updated_by, created_at, updated_at, serialized_object ) VALUES"
   " ($1, $2, $3, $4, $5, $6, $7, $8)">>}.

{update_role_by_id,
 <<"UPDATE roles SET last_updated_by= $1, updated_at= $2, "
   "serialized_object= $3 WHERE id= $4">>}.

{delete_role_by_id, <<"DELETE FROM roles WHERE id= $1">>}.

{list_roles_for_org, <<"SELECT name FROM roles WHERE org_id= $1">>}.

{list_role_ids_names_for_org,
 <<"SELECT id, name FROM roles WHERE org_id = $1">>}.

{bulk_get_role_authz_ids,
 <<"SELECT id, authz_id FROM roles WHERE id = ANY($1)">>}.

%% bulk_get_roles query used for fetching roles returned from
%% search only returns the gzip JSON data.  If a role has been
%% deleted, you may get back fewer than X roles and you won't know
%% which ids were not found without parsing and inspecting what did
%% come back.
{bulk_get_roles, <<"SELECT serialized_object FROM roles WHERE id = ANY($1)">>}.

%% data_bag queries

{find_data_bag_by_orgid_name,
 <<"SELECT id, authz_id, org_id, name, last_updated_by,"
   " created_at, updated_at FROM data_bags WHERE (org_id = $1"
   " AND name = $2) LIMIT 1">>}.

{insert_data_bag,
 <<"INSERT INTO data_bags (id, authz_id, org_id, name, "
   " last_updated_by, created_at, updated_at) VALUES"
   " ($1, $2, $3, $4, $5, $6, $7)">>}.

{update_data_bag_by_id,
 <<"UPDATE data_bags SET last_updated_by= $1, updated_at= $2 WHERE id= $3">>}.

{delete_data_bag_by_id, <<"DELETE FROM data_bags WHERE id= $1">>}.

{list_data_bags_for_org, <<"SELECT name FROM data_bags WHERE org_id= $1">>}.

%% We don't bulk_get data_bags, but only data_bag_items.

%% data_bag_item queries

{find_data_bag_item_by_orgid_name,
 <<"SELECT id, org_id, data_bag_name, item_name, last_updated_by,"
   " created_at, updated_at, serialized_object FROM data_bag_items WHERE (org_id = $1"
   " AND data_bag_name = $2 AND item_name = $3) LIMIT 1">>}.

{find_data_bag_item_id_by_orgid_name,
 <<"SELECT id FROM data_bag_items WHERE "
   "(org_id = $1 AND data_bag_name = $2)">>}.

{insert_data_bag_item,
 <<"INSERT INTO data_bag_items (id,org_id, data_bag_name, item_name,"
   " last_updated_by, created_at, updated_at, serialized_object ) VALUES"
   " ($1, $2, $3, $4, $5, $6, $7, $8)">>}.

{update_data_bag_item_by_id,
 <<"UPDATE data_bag_items SET last_updated_by= $1, updated_at= $2, "
   "serialized_object= $3 WHERE id= $4">>}.

{delete_data_bag_item_by_id, <<"DELETE FROM data_bag_items WHERE id= $1">>}.

{list_data_bag_items_for_data_bag, <<"SELECT item_name FROM data_bag_items "
                                     "WHERE (org_id= $1 AND data_bag_name= $2)">>}.

{list_data_bag_item_ids_names_for_org,
 <<"SELECT id, item_name FROM data_bag_items WHERE org_id = $1 AND data_bag_name = $2">>}.

%% bulk_get_data_bag_items query used for fetching data_bag_items
%% returned from search only returns the gzip JSON data.  If a
%% data_bag_item has been deleted, you may get back fewer than X
%% data_bag_items and you won't know which ids were not found without
%% parsing and inspecting what did come back.
{bulk_get_data_bag_items, <<"SELECT serialized_object FROM data_bag_items WHERE id = ANY($1)">>}.

%% environment queries

{find_environment_by_orgid_name,
 <<"SELECT id, authz_id, org_id, name, last_updated_by,"
   " created_at, updated_at, serialized_object FROM environments WHERE (org_id = $1"
   " AND name = $2) LIMIT 1">>}.

{insert_environment,
 <<"INSERT INTO environments (id, authz_id, org_id, name,"
   " last_updated_by, created_at, updated_at, serialized_object ) VALUES"
   " ($1, $2, $3, $4, $5, $6, $7, $8)">>}.

{update_environment_by_id,
 <<"UPDATE environments SET last_updated_by= $1, updated_at= $2, "
   "name= $3, serialized_object= $4 WHERE id= $5">>}.

{delete_environment_by_id, <<"DELETE FROM environments WHERE id= $1">>}.

{list_environments_for_org, <<"SELECT name FROM environments WHERE org_id= $1">>}.

{list_environment_ids_names_for_org,
 <<"SELECT id, name FROM environments WHERE org_id = $1">>}.

{bulk_get_environment_authz_ids,
 <<"SELECT id, authz_id FROM environments WHERE id = ANY($1)">>}.

%% bulk_get_environments query used for fetching environments returned
%% from search only returns the gzip JSON data.  If a environment has
%% been deleted, you may get back fewer than X environments and you
%% won't know which ids were not found without parsing and inspecting
%% what did come back.
{bulk_get_environments, <<"SELECT serialized_object FROM environments WHERE id = ANY($1)">>}.

%% client queries


% DEPRECATED as of APIv1
{find_client_by_orgid_name_v0,
 <<"SELECT c.id, authz_id, org_id, name, validator, admin, k.public_key, k.key_version pubkey_version,
           c.last_updated_by, c.created_at, c.updated_at
      FROM clients c
 LEFT JOIN keys k ON c.id = k.id AND key_name = 'default'
     WHERE org_id = $1 AND name = $2
     LIMIT 1">>}.

{find_client_by_orgid_name,
 <<"SELECT c.id, authz_id, org_id, name, validator, c.last_updated_by, c.created_at, c.updated_at
      FROM clients c
     WHERE org_id = $1 AND name = $2
     LIMIT 1">>}.

% DEPRECATED as of APIv1
{insert_client_v0,
 <<"INSERT INTO clients ( id, authz_id, org_id, name, validator, admin, public_key,"
   "    pubkey_version, last_updated_by, created_at, updated_at )"
   "  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)">>}.

{insert_client,
 <<"INSERT INTO clients ( id, authz_id, org_id, name, validator, last_updated_by, created_at, updated_at, pubkey_version, admin )"
   "  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0, false)">>}.

% DEPRECATED as of APIv1
{update_client_by_id_v0,
 <<"UPDATE clients SET last_updated_by = $1, updated_at = $2, "
   "       name = $3, public_key = $4, pubkey_version = $5, "
   "       validator = $6, admin = $7  WHERE id = $8">>}.

{update_client_by_id,
 <<"UPDATE clients SET last_updated_by = $1, updated_at = $2, "
   "       name = $3, validator = $4 WHERE id = $5">>}.

{delete_client_by_id, <<"DELETE FROM clients WHERE id= $1">>}.

{list_clients_for_org, <<"SELECT name FROM clients WHERE org_id= $1">>}.

{list_client_ids_names_for_org,
 <<"SELECT id, name FROM clients WHERE org_id = $1">>}.

{bulk_get_client_authz_ids,
 <<"SELECT id, authz_id FROM clients WHERE id = ANY($1)">>}.

%% bulk_get_clients query used for fetching clients returned from
%% search returns the minimum fields needed to construct a client
%% search response since there is no serialized object.  Note for
%% compatibility search on clientname in ruby we return the name
%% twice, once as 'name' and once as 'clientname'
%%
%% If a client has been deleted, you may get back fewer than X clients
%% and you won't know which ids were not found without parsing and
%% inspecting what did come back.
%% DEPRECATED as of APIv1
{bulk_get_clients_v0,
 <<"SELECT c.id, authz_id, org_id, name, validator, admin, k.public_key, k.key_version pubkey_version,
           c.last_updated_by, c.created_at, c.updated_at
      FROM clients c
 LEFT JOIN keys k ON c.id = k.id AND key_name = 'default'
     WHERE c.id = ANY($1)">>}.

%% replace bulk_get_clients
{bulk_get_clients,
 <<"SELECT c.id, authz_id, org_id, name, validator, c.last_updated_by, c.created_at, c.updated_at
      FROM clients c
     WHERE c.id = ANY($1)">>}.

%% cookbook queries
{find_cookbook_by_orgid_name,
 <<"SELECT authz_id, org_id, name FROM cookbooks WHERE (org_id = $1 AND name = $2) LIMIT 1">>}.

{insert_cookbook,
 <<"INSERT INTO cookbooks (authz_id, org_id, name) VALUES ($1, $2, $3)">>}.

{delete_cookbook_by_orgid_name, <<"DELETE FROM cookbooks WHERE (org_id = $1 AND name = $2)">>}.

{bulk_get_cookbook_authz_ids,
 <<"SELECT id, authz_id FROM cookbooks WHERE id = ANY($1)">>}.

%% cookbook version queries
{find_cookbook_version_by_orgid_name_version,
 <<"SELECT v.id,  v.major, v.minor, v.patch, v.frozen, v.meta_attributes,
    v.meta_deps, v.meta_long_desc, v.metadata, v.serialized_object, v.last_updated_by,
    v.created_at, v.updated_at, c.authz_id, c.org_id, c.name FROM cookbooks AS c, cookbook_versions AS v
    WHERE (v.cookbook_id = c.id AND c.org_id = $1 AND c.name = $2
    AND v.major = $3 AND v.minor = $4 AND v.patch = $5) LIMIT 1">>}.

{find_latest_cookbook_version_by_orgid_name,
<<"SELECT v.id, v.major, v.minor, v.patch, v.frozen, v.meta_attributes,
   v.meta_deps, v.meta_long_desc, v.metadata, v.serialized_object, v.last_updated_by,
   v.created_at, v.updated_at, c.authz_id, c.org_id, c.name FROM cookbooks AS c, cookbook_versions  AS v
   WHERE (v.cookbook_id = c.id AND c.org_id = $1 AND c.name = $2)
   ORDER BY v.major DESC, v.minor DESC, v.patch DESC LIMIT 1">>}.


{insert_cookbook_version,
 <<"INSERT INTO cookbook_versions (id, major, minor, patch, frozen, "
   "meta_attributes, meta_deps, meta_long_desc, metadata, serialized_object, "
   "last_updated_by, created_at, updated_at, cookbook_id) VALUES ($1, $2, $3, $4, "
   "$5, $6, $7, $8, $9, $10, $11, $12, $13, "
   "(SELECT id FROM cookbooks WHERE org_id = $14 AND name = $15))">>}.

{update_cookbook_version,
 <<"UPDATE cookbook_versions"
   " SET frozen = $1, meta_attributes = $2, meta_deps = $3, meta_long_desc = $4,"
   "   metadata = $5, serialized_object = $6, last_updated_by = $7, updated_at = $8"
   " WHERE id = $9">>}.

{delete_cookbook_version_by_id, <<"DELETE FROM cookbook_versions WHERE id = $1">>}.

{insert_cookbook_version_checksum,
 <<"INSERT INTO cookbook_version_checksums (checksum, org_id, cookbook_version_id) "
   "VALUES ($1, $2, (SELECT cookbook_versions.id FROM cookbooks, cookbook_versions "
   "                 WHERE cookbooks.id = cookbook_versions.cookbook_id AND "
   "                       cookbooks.name = $3 AND "
   "                       cookbooks.org_id = $4 AND "
   "                       major = $5 AND minor = $6 AND patch = $7))">>}.

{delete_cookbook_version_checksum, <<"DELETE FROM cookbook_version_checksums WHERE checksum = $1 AND org_id = $2 AND cookbook_version_id = $3">>}.

{list_cookbook_versions_by_orgid,
  <<"SELECT cookbooks.name, major, minor, patch FROM cookbooks, cookbook_versions "
    "WHERE (org_id = $1 AND cookbooks.id = cookbook_versions.cookbook_id) "
    "ORDER BY name, major DESC, minor DESC, patch DESC">>}.

{list_cookbook_versions_by_orgid_cookbook_name,
  <<"SELECT cookbooks.name, major, minor, patch FROM cookbooks, cookbook_versions "
    "WHERE (org_id = $1 AND cookbooks.name = $2 AND cookbooks.id = cookbook_versions.cookbook_id) "
    "ORDER BY name, major DESC, minor DESC, patch DESC">>}.

%% 'serialized_object' contains the recipes
{fetch_latest_cookbook_recipes_by_orgid,
 <<"SELECT name, serialized_object
    FROM cookbook_versions_by_rank
    WHERE org_id = $1
      AND rank = 1
    ORDER BY name ASC">>}.

{fetch_recent_cookbook_versions_by_orgid_and_num,
 <<"SELECT name, version
   FROM cookbook_versions_by_rank
   WHERE org_id = $1
     AND rank <= $2
   ORDER BY name ASC, major DESC, minor DESC, patch DESC">>}.

%% Though this query could utilize the `cookbook_versions_by_rank'
%% view, we use a slightly different view here that does not use the
%% `rank' window function.  We don't actually need
%% `rank' here, so we shouldn't need to compute it.
{fetch_all_cookbook_versions_by_orgid,
 <<"SELECT name, version
   FROM joined_cookbook_version
   WHERE org_id = $1
   ORDER BY name ASC, major DESC, minor DESC, patch DESC">>}.

%% Used for getting all the versions of a cookbook, as filtered
%% through an environment's constraints
{fetch_all_cookbook_versions_by_orgid_cookbook,
 <<"SELECT name, version
   FROM joined_cookbook_version
   WHERE org_id = $1
     AND name = $2
   ORDER BY major DESC, minor DESC, patch DESC">>}.

%% Used in implementation of environments/ENVIRONMENT/recipes endpoint.
%%
%% We do NOT require these results to be ordered, as the results are
%% being dumped into a dict anyway.
%%
%% We do not use cookbook_versions_by_rank for this, because
%% ranking is unnecessary.
{fetch_all_cookbook_version_ids_by_orgid,
 <<"SELECT name, version, id
    FROM joined_cookbook_version
    WHERE org_id = $1">>}.

%% Used in implementation of environemnts/ENVNAME/cookbook_versions endpoint.
%%
%% Depends on the cbv type existing in the schema
%%
%% This query uses two CTEs (inputs and min_cve) to force the query planner
%% to first join cookbook_versions (min, maj, patch) with cookbooks (id, name)
%% before filtering with inputs.
%%
%% Without the min_cv CTE, the query planner would choose to join inputs with
%% cookbook_versions before filtering by name. In the worst case scenario (all
%% cookbooks have the same version number), the inputs and cookbook_versions
%% join behaved like a cartesian product of all the inputs and the cookbook versions.
{bulk_fetch_minimal_cookbook_versions,
 <<"WITH inputs AS ( SELECT * FROM unnest($2::cbv[]) ),
         min_cv AS ( SELECT v.frozen, v.meta_deps, v.metadata, v.serialized_object,
                            v.major, v.minor, v.patch, c.authz_id, c.org_id, c.name
                       FROM cookbooks as c,
                            cookbook_versions as v
                      WHERE c.org_id = $1
                        AND c.id = v.cookbook_id )
  SELECT min_cv.frozen, min_cv.meta_deps, min_cv.metadata, min_cv.serialized_object,
         min_cv.authz_id, min_cv.org_id, min_cv.name
    FROM inputs,
         min_cv
   WHERE inputs.name = min_cv.name
     AND inputs.major = min_cv.major
     AND inputs.minor = min_cv.minor
     AND inputs.patch = min_cv.patch">>}.

%% Used in implementation of environments/ENVIRONMENT/recipes endpoint
%%
%% This query shouldn't use the `cookbook_versions_by_rank' view, even
%% though the information is present and we wouldn't need the JOIN
%% clause, because it will compute ranks unnecessarily; we are just
%% interested in individual cookbook versions here.
{bulk_get_cbv_serialized_object,
 <<"SELECT name, serialized_object
    FROM joined_cookbook_version
    WHERE id = ANY($1)">>}.

{fetch_all_cookbook_version_dependencies_by_orgid,
 <<"SELECT name, major || '.' || minor || '.' || patch AS version, dependencies
    FROM cookbook_version_dependencies
    WHERE org_id = $1
    ORDER BY name, major DESC, minor DESC, patch DESC">>}.

{list_cookbook_checksums_by_orgid_cookbook_versionid,
  <<"SELECT checksum FROM cookbook_versions, cookbook_version_checksums "
    "WHERE (cookbook_version_checksums.org_id = $1 AND cookbook_versions.id = $2) "
    "AND (cookbook_version_checksums.cookbook_version_id = cookbook_versions.id)">>}.

{delete_cookbook_checksums_by_orgid_cookbook_versionid,
  <<"DELETE FROM cookbook_version_checksums "
    "WHERE org_id = $1 AND cookbook_version_id = $2">>}.

%% Sandbox Queries
{find_sandbox_by_id,
<<"SELECT sc.org_id, sc.sandbox_id, sc.checksum, c.checksum IS NOT NULL AS uploaded, sc.created_at
   FROM sandboxed_checksums AS sc
   LEFT JOIN checksums AS c
          ON sc.org_id = c.org_id
         AND sc.checksum = c.checksum
   WHERE sc.org_id = $1
     AND sc.sandbox_id = $2">>}.

{delete_sandbox_by_id,
 <<"DELETE FROM sandboxed_checksums WHERE sandbox_id = $1">>}.

{insert_checksum,
  <<"INSERT INTO checksums(org_id, checksum)
     VALUES ($1, $2)">>}.

{delete_checksum_by_id,
  <<"DELETE FROM checksums where org_id = $1 AND checksum = $2">>}.

{insert_sandboxed_checksum,
 <<"INSERT INTO sandboxed_checksums(org_id, sandbox_id, checksum, created_at)
    VALUES ($1, $2, $3, $4)">>}.

{non_uploaded_checksums,
<<"SELECT sc.checksum
   FROM sandboxed_checksums AS sc
   LEFT JOIN checksums AS c
          ON sc.org_id = c.org_id
         AND sc.checksum = c.checksum
   WHERE sc.org_id = $1
     AND sc.sandbox_id = $2
     AND c.checksum IS NULL">>}.

{list_keys_for_actor, <<"SELECT key_name, (expires_at <= CURRENT_TIMESTAMP at time zone 'utc')
                          FROM keys WHERE id= $1">>}.
{find_key_by_id_and_name, <<"SELECT id, key_name, public_key, key_version, expires_at,
                                    last_updated_by, created_at, updated_at
                               FROM keys
                              WHERE (id = $1 AND key_name = $2)">>}.
{insert_key_for_actor, <<"INSERT INTO keys (id, key_name, public_key, key_version,
                                            expires_at, last_updated_by, created_at,
                                            updated_at)
                          VALUES ($1, $2, $3, $4, $5, $6, $7, $8)">>}.
{delete_key_by_id_and_name, <<"DELETE FROM keys WHERE id = $1 AND key_name = $2">>}.
{update_key_by_id_and_name, <<"UPDATE keys SET key_name = $3, public_key = $4, key_version = $5,
                                               expires_at = $6, last_updated_by = $7,
                                               updated_at = $8
                                WHERE id = $1 AND key_name = $2">>}.

% Couple of notes:
% 1. Order by type DESC presently puts users ahead of clients.
%    Consumers of this query may depend on that order. In the future
%    we may wish to tune with heuristics to increase the likelihood of
%    matching early, but consumers may have to be changed to
%    accomodate such changes.
% 2. expires_at is 'timestamp without timezone', which means storage as UTC.  We force
%    current_timestamp to UTC to ensure a valid comparison.
{fetch_requestors_by_name,
 <<"SELECT id, org_id, name, authz_id, type, key_name, public_key, key_version
      FROM keys_by_name
     WHERE (org_id = $1 OR org_id = 'global')
       AND name = $2
       AND expires_at > CURRENT_TIMESTAMP at time zone 'utc'
     ORDER BY type DESC">>}.

%% User-related
%%
{insert_user,
  <<"SELECT add_user($1, $2, $3, $4, 'this_is_not_a_key', 0, $5, $6, $7, $8, $9, $10, $11, $12, $13, false)">>}.

{insert_user_v0,
  <<"SELECT add_user($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, false)">>}.

{find_user_by_username_v0,
  <<"SELECT u.id, authz_id, username, email, k.public_key, k.key_version pubkey_version,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
 LEFT JOIN keys k ON u.id = k.id AND key_name = 'default'
     WHERE username = $1">>}.

{find_user_by_username,
  <<"SELECT u.id, authz_id, username, email,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
     WHERE username = $1">>}.

{find_user_by_external_authentication_uid_v0,
  <<"SELECT u.id, authz_id, username, email, k.public_key, k.key_version pubkey_version,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
 LEFT JOIN keys k ON u.id = k.id AND key_name = 'default'
     WHERE lower(external_authentication_uid) = lower($1)">>}.

{find_user_by_external_authentication_uid,
  <<"SELECT u.id, authz_id, username, email,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
     WHERE lower(external_authentication_uid) = lower($1)">>}.

{find_user_by_sensitive_external_authentication_uid_v0,
  <<"SELECT u.id, authz_id, username, email, k.public_key, k.key_version pubkey_version,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
 LEFT JOIN keys k ON u.id = k.id AND key_name = 'default'
     WHERE external_authentication_uid = $1">>}.

{find_user_by_sensitive_external_authentication_uid,
  <<"SELECT u.id, authz_id, username, email,
      hashed_password, salt, hash_type, u.last_updated_by, u.created_at,
      u.updated_at, external_authentication_uid,
      recovery_authentication_enabled, serialized_object
      FROM users u
     WHERE external_authentication_uid = $1">>}.

{delete_user_by_id,
  <<"DELETE FROM users WHERE id = $1">>}.

{list_users_verbose, <<"SELECT username, email, serialized_object FROM users">>}.

{list_users, <<"SELECT username FROM users">>}.

%% This is supported by a functional index on lower(email)
{list_users_by_email, <<"SELECT username FROM users WHERE lower(email) = lower($1)">>}.

{list_users_by_ext_auth_uid, <<"SELECT username FROM users WHERE external_authentication_uid = $1">>}.

{update_user_by_id_v0,
<<"SELECT update_user($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, false, $13)">>}.

{list_common_orgs_for_users,
 <<"SELECT o.name as name, o.full_name as full_name, o.id as guid"
   "  FROM (SELECT * FROM org_user_associations WHERE user_id = $1) AS a,"
   "       (SELECT * FROM org_user_associations WHERE user_id = $2) AS b,"
   "       orgs o"
   " WHERE a.org_id = o.id"
   "   AND a.org_id = b.org_id">>
}.

{update_user_by_id,
<<"SELECT update_user(0, 'this_is_not_a_key', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, false, $11)">>}.

{value_for_key, <<"SELECT value FROM kvpair WHERE key = $1">>}.