| /* |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| 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. |
| */ |
| |
| |
| -- PROJECT_DETAILS |
| CREATE TYPE public.type_project_details AS ( |
| project_name text |
| , project_url text |
| , repo_url text |
| ); |
| |
| CREATE OR REPLACE FUNCTION public.project_details() |
| RETURNS public.type_project_details AS |
| $$ |
| SELECT project_name, project_url, repo_url |
| FROM public.project |
| ORDER BY last_changed DESC |
| LIMIT 1 |
| $$ |
| LANGUAGE sql STABLE; |
| COMMENT ON FUNCTION public.project_details() |
| IS 'Get the current project name, url, and repo url.'; |
| |
| |
| -------------------------- GET-OR-SET FUNCTIONS -------------------------- |
| -- The following functions have the naming convention "get_<tablename>_id". |
| -- All of them attempt to SELECT the desired row given the column |
| -- values, and if it does not exist will INSERT it. |
| -- |
| -- When functions are overloaded with fewer columns, it is to allow |
| -- selection only, given columns that comprise a unique index. |
| |
| -- GET_CPU_ID |
| CREATE OR REPLACE FUNCTION public.get_cpu_id( |
| cpu_model_name citext |
| , cpu_core_count integer |
| , cpu_thread_count integer |
| , cpu_frequency_max_Hz bigint |
| , cpu_frequency_min_Hz bigint |
| , cpu_L1d_cache_bytes integer |
| , cpu_L1i_cache_bytes integer |
| , cpu_L2_cache_bytes integer |
| , cpu_L3_cache_bytes integer |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT cpu_id INTO result FROM public.cpu AS cpu |
| WHERE cpu.cpu_model_name = $1 |
| AND cpu.cpu_core_count = $2 |
| AND cpu.cpu_thread_count = $3 |
| AND cpu.cpu_frequency_max_Hz = $4 |
| AND cpu.cpu_frequency_min_Hz = $5 |
| AND cpu.cpu_L1d_cache_bytes = $6 |
| AND cpu.cpu_L1i_cache_bytes = $7 |
| AND cpu.cpu_L2_cache_bytes = $8 |
| AND cpu.cpu_L3_cache_bytes = $9; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.cpu( |
| cpu_model_name |
| , cpu_core_count |
| , cpu_thread_count |
| , cpu_frequency_max_Hz |
| , cpu_frequency_min_Hz |
| , cpu_L1d_cache_bytes |
| , cpu_L1i_cache_bytes |
| , cpu_L2_cache_bytes |
| , cpu_L3_cache_bytes |
| ) |
| VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) |
| RETURNING cpu_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_cpu_id( |
| citext |
| , integer |
| , integer |
| , bigint -- cpu_frequency_max_Hz |
| , bigint -- cpu_frequency_min_Hz |
| , integer |
| , integer |
| , integer |
| , integer |
| ) |
| IS 'Insert or select CPU data, returning "cpu.cpu_id".'; |
| |
| -- GET_GPU_ID |
| CREATE OR REPLACE FUNCTION public.get_gpu_id( |
| gpu_information citext DEFAULT NULL |
| , gpu_part_number citext DEFAULT NULL |
| , gpu_product_name citext DEFAULT NULL |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT gpu_id INTO result FROM public.gpu AS gpu |
| WHERE |
| gpu.gpu_information = COALESCE($1, '') |
| AND gpu.gpu_part_number = COALESCE($2, '') |
| AND gpu.gpu_product_name = COALESCE($3, ''); |
| |
| IF result IS NULL THEN |
| INSERT INTO public.gpu( |
| gpu_information |
| , gpu_part_number |
| , gpu_product_name |
| ) |
| VALUES (COALESCE($1, ''), COALESCE($2, ''), COALESCE($3, '')) |
| RETURNING gpu_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_gpu_id(citext, citext, citext) |
| IS 'Insert or select GPU data, returning "gpu.gpu_id".'; |
| |
| -- GET_OS_ID |
| CREATE OR REPLACE FUNCTION public.get_os_id( |
| os_name citext |
| , architecture_name citext |
| , kernel_name citext DEFAULT '' |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT os_id INTO result FROM public.os AS os |
| WHERE os.os_name = $1 |
| AND os.architecture_name = $2 |
| AND os.kernel_name = COALESCE($3, ''); |
| |
| IF result is NULL THEN |
| INSERT INTO public.os(os_name, architecture_name, kernel_name) |
| VALUES ($1, $2, COALESCE($3, '')) |
| RETURNING os_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_os_id(citext, citext, citext) |
| IS 'Insert or select OS data, returning "os.os_id".'; |
| |
| -- GET_MACHINE_ID (full signature) |
| CREATE OR REPLACE FUNCTION public.get_machine_id( |
| mac_address macaddr |
| , machine_name citext |
| , memory_bytes bigint |
| , cpu_actual_frequency_Hz bigint |
| -- os |
| , os_name citext |
| , architecture_name citext |
| , kernel_name citext |
| -- cpu |
| , cpu_model_name citext |
| , cpu_core_count integer |
| , cpu_thread_count integer |
| , cpu_frequency_max_Hz bigint |
| , cpu_frequency_min_Hz bigint |
| , L1d_cache_bytes integer |
| , L1i_cache_bytes integer |
| , L2_cache_bytes integer |
| , L3_cache_bytes integer |
| -- gpu |
| , gpu_information citext DEFAULT '' |
| , gpu_part_number citext DEFAULT NULL |
| , gpu_product_name citext DEFAULT NULL |
| -- nullable machine attributes |
| , machine_other_attributes jsonb DEFAULT NULL |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| found_cpu_id integer; |
| found_gpu_id integer; |
| found_os_id integer; |
| result integer; |
| BEGIN |
| -- Can't bypass looking up all the values because of unique constraint. |
| SELECT public.get_cpu_id( |
| cpu_model_name |
| , cpu_core_count |
| , cpu_thread_count |
| , cpu_frequency_max_Hz |
| , cpu_frequency_min_Hz |
| , L1d_cache_bytes |
| , L1i_cache_bytes |
| , L2_cache_bytes |
| , L3_cache_bytes |
| ) INTO found_cpu_id; |
| |
| SELECT public.get_gpu_id( |
| gpu_information |
| , gpu_part_number |
| , gpu_product_name |
| ) INTO found_gpu_id; |
| |
| SELECT public.get_os_id( |
| os_name |
| , architecture_name |
| , kernel_name |
| ) INTO found_os_id; |
| |
| SELECT machine_id INTO result FROM public.machine AS m |
| WHERE m.os_id = found_os_id |
| AND m.cpu_id = found_cpu_id |
| AND m.gpu_id = found_gpu_id |
| AND m.mac_address = $1 |
| AND m.machine_name = $2 |
| AND m.memory_bytes = $3 |
| AND m.cpu_actual_frequency_Hz = $4; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.machine( |
| os_id |
| , cpu_id |
| , gpu_id |
| , mac_address |
| , machine_name |
| , memory_bytes |
| , cpu_actual_frequency_Hz |
| , machine_other_attributes |
| ) |
| VALUES (found_os_id, found_cpu_id, found_gpu_id, $1, $2, $3, $4, $20) |
| RETURNING machine_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_machine_id( |
| macaddr |
| , citext |
| , bigint -- memory_bytes |
| , bigint -- cpu_frequency_actual_Hz |
| -- os |
| , citext |
| , citext |
| , citext |
| -- cpu |
| , citext |
| , integer |
| , integer |
| , bigint -- cpu_frequency_max_Hz |
| , bigint -- cpu_frequency_min_Hz |
| , integer |
| , integer |
| , integer |
| , integer |
| -- gpu |
| , citext |
| , citext |
| , citext |
| -- nullable machine attributes |
| , jsonb |
| ) |
| IS 'Insert or select machine data, returning "machine.machine_id".'; |
| |
| -- GET_MACHINE_ID (given unique mac_address) |
| CREATE OR REPLACE FUNCTION public.get_machine_id(mac_address macaddr) |
| RETURNS integer AS |
| $$ |
| SELECT machine_id FROM public.machine AS m |
| WHERE m.mac_address = $1; |
| $$ |
| LANGUAGE sql STABLE; |
| COMMENT ON FUNCTION public.get_machine_id(macaddr) |
| IS 'Select machine_id given its mac address, returning "machine.machine_id".'; |
| |
| -- GET_BENCHMARK_LANGUAGE_ID |
| CREATE OR REPLACE FUNCTION public.get_benchmark_language_id(language citext) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT benchmark_language_id INTO result |
| FROM public.benchmark_language AS bl |
| WHERE bl.benchmark_language = language; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.benchmark_language(benchmark_language) |
| VALUES (language) |
| RETURNING benchmark_language_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_benchmark_language_id(citext) |
| IS 'Insert or select benchmark_language returning ' |
| '"benchmark_language.benchmark_language_id".'; |
| |
| -- GET_LANGUAGE_IMPLEMENTATION_VERSION_ID |
| CREATE OR REPLACE FUNCTION public.get_language_implementation_version_id( |
| language citext |
| , language_implementation_version citext DEFAULT '' |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| language_id integer; |
| result integer; |
| BEGIN |
| SELECT public.get_benchmark_language_id($1) INTO language_id; |
| |
| SELECT language_implementation_version_id INTO result FROM public.language_implementation_version AS lv |
| WHERE lv.benchmark_language_id = language_id |
| AND lv.language_implementation_version = COALESCE($2, ''); |
| |
| IF result IS NULL THEN |
| INSERT INTO |
| public.language_implementation_version(benchmark_language_id, language_implementation_version) |
| VALUES (language_id, COALESCE($2, '')) |
| RETURNING language_implementation_version_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_language_implementation_version_id(citext, citext) |
| IS 'Insert or select language and version data, ' |
| 'returning "language_implementation_version.language_implementation_version_id".'; |
| |
| CREATE OR REPLACE FUNCTION public.get_language_implementation_version_id( |
| -- overload for when language_id is known |
| language_id integer |
| , language_implementation_version citext DEFAULT '' |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT language_implementation_version_id INTO result FROM public.language_implementation_version AS lv |
| WHERE lv.benchmark_language_id = language_id |
| AND lv.language_implementation_version = COALESCE($2, ''); |
| |
| IF result IS NULL THEN |
| INSERT INTO |
| public.language_implementation_version(benchmark_language_id, language_implementation_version) |
| VALUES (language_id, COALESCE($2, '')) |
| RETURNING language_implementation_version_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| |
| -- GET_LANGUAGE_DEPENDENCY_LOOKUP_ID |
| CREATE OR REPLACE FUNCTION public.get_dependencies_id( |
| dependencies jsonb DEFAULT '{}'::jsonb |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT dependencies_id INTO result |
| FROM public.dependencies AS ldl |
| WHERE ldl.dependencies = COALESCE($1, '{}'::jsonb); |
| |
| IF result IS NULL THEN |
| INSERT INTO |
| public.dependencies(dependencies) |
| VALUES (COALESCE($1, '{}'::jsonb)) |
| RETURNING dependencies_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_dependencies_id(jsonb) |
| IS 'Insert or select dependencies, returning "dependencies.dependencies_id".'; |
| |
| -- GET_ENVIRONMENT_ID |
| CREATE OR REPLACE FUNCTION public.get_environment_id( |
| language citext, |
| language_implementation_version citext DEFAULT '', |
| dependencies jsonb DEFAULT '{}'::jsonb |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| found_language_id integer; |
| found_version_id integer; |
| found_dependencies_id integer; |
| result integer; |
| BEGIN |
| SELECT public.get_benchmark_language_id($1) INTO found_language_id; |
| SELECT |
| public.get_language_implementation_version_id(found_language_id, $2) |
| INTO found_version_id; |
| SELECT |
| public.get_dependencies_id ($3) |
| INTO found_dependencies_id; |
| |
| SELECT environment_id INTO result FROM public.environment AS e |
| WHERE e.benchmark_language_id = found_language_id |
| AND e.language_implementation_version_id = found_version_id |
| AND e.dependencies_id = found_dependencies_id; |
| |
| IF result IS NULL THEN |
| INSERT INTO |
| public.environment( |
| benchmark_language_id |
| , language_implementation_version_id |
| , dependencies_id |
| ) |
| VALUES (found_language_id, found_version_id, found_dependencies_id) |
| RETURNING environment_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_environment_id(citext, citext, jsonb) |
| IS 'Insert or select language, language version, and dependencies, ' |
| 'returning "environment.environment_id".'; |
| |
| -- GET_BENCHMARK_TYPE_ID (full signature) |
| CREATE OR REPLACE FUNCTION public.get_benchmark_type_id( |
| benchmark_type citext |
| , lessisbetter boolean |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT benchmark_type_id INTO result FROM public.benchmark_type AS bt |
| WHERE bt.benchmark_type = $1 |
| AND bt.lessisbetter = $2; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.benchmark_type(benchmark_type, lessisbetter) |
| VALUES($1, $2) |
| RETURNING benchmark_type_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_benchmark_type_id(citext, boolean) |
| IS 'Insert or select benchmark type and lessisbetter, ' |
| 'returning "benchmark_type.benchmark_type_id".'; |
| |
| -- GET_BENCHMARK_TYPE_ID (given unique benchmark_type string only) |
| CREATE OR REPLACE FUNCTION public.get_benchmark_type_id( |
| benchmark_type citext |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| result integer; |
| BEGIN |
| SELECT benchmark_type_id INTO result FROM public.benchmark_type AS bt |
| WHERE bt.benchmark_type = $1; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_benchmark_type_id(citext) |
| IS 'Select benchmark_type_id given benchmark type (e.g. ''time''), ' |
| 'returning "benchmark_type.benchmark_type_id".'; |
| |
| -- GET_UNIT_ID (full signature) |
| CREATE OR REPLACE FUNCTION public.get_unit_id( |
| benchmark_type citext |
| , units citext |
| , lessisbetter boolean DEFAULT NULL |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| found_benchmark_type_id integer; |
| result integer; |
| BEGIN |
| |
| IF ($3 IS NOT NULL) -- if lessisbetter is not null |
| THEN |
| SELECT public.get_benchmark_type_id($1, $3) |
| INTO found_benchmark_type_id; |
| ELSE |
| SELECT public.get_benchmark_type_id($1) |
| INTO found_benchmark_type_id; |
| END IF; |
| |
| SELECT unit_id INTO result FROM public.unit AS u |
| WHERE u.benchmark_type_id = found_benchmark_type_id |
| AND u.units = $2; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.unit(benchmark_type_id, units) |
| VALUES(found_benchmark_type_id, $2) |
| RETURNING unit_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_unit_id(citext, citext, boolean) |
| IS 'Insert or select benchmark type (e.g. ''time''), ' |
| 'units string (e.g. ''miliseconds''), ' |
| 'and "lessisbetter" (true if smaller benchmark values are better), ' |
| 'returning "unit.unit_id".'; |
| |
| -- GET_UNIT_ID (given unique units string only) |
| CREATE OR REPLACE FUNCTION public.get_unit_id(units citext) |
| RETURNS integer AS |
| $$ |
| SELECT unit_id FROM public.unit AS u |
| WHERE u.units = units; |
| $$ |
| LANGUAGE sql STABLE; |
| COMMENT ON FUNCTION public.get_unit_id(citext) |
| IS 'Select unit_id given unit name, returning "unit.unit_id".'; |
| |
| -- GET_BENCHMARK_ID (full signature) |
| CREATE OR REPLACE FUNCTION public.get_benchmark_id( |
| benchmark_language citext |
| , benchmark_name citext |
| , parameter_names text[] |
| , benchmark_description text |
| , benchmark_version citext |
| , benchmark_type citext |
| , units citext |
| , lessisbetter boolean |
| ) |
| RETURNS integer AS |
| $$ |
| DECLARE |
| found_benchmark_language_id integer; |
| found_unit_id integer; |
| result integer; |
| BEGIN |
| SELECT public.get_benchmark_language_id( |
| benchmark_language |
| ) INTO found_benchmark_language_id; |
| |
| SELECT public.get_unit_id( |
| benchmark_type |
| , units |
| , lessisbetter |
| ) INTO found_unit_id; |
| |
| SELECT benchmark_id INTO result FROM public.benchmark AS b |
| WHERE b.benchmark_language_id = found_benchmark_language_id |
| AND b.benchmark_name = $2 |
| -- handle nullable "parameter_names" |
| AND b.parameter_names IS NOT DISTINCT FROM $3 |
| AND b.benchmark_description = $4 |
| AND b.benchmark_version = $5 |
| AND b.unit_id = found_unit_id; |
| |
| IF result IS NULL THEN |
| INSERT INTO public.benchmark( |
| benchmark_language_id |
| , benchmark_name |
| , parameter_names |
| , benchmark_description |
| , benchmark_version |
| , unit_id |
| ) |
| VALUES (found_benchmark_language_id, $2, $3, $4, $5, found_unit_id) |
| RETURNING benchmark_id INTO result; |
| END IF; |
| |
| RETURN result; |
| END |
| $$ |
| LANGUAGE plpgsql; |
| COMMENT ON FUNCTION public.get_benchmark_id( |
| citext |
| , citext |
| , text[] |
| , text |
| , citext |
| , citext |
| , citext |
| , boolean |
| ) |
| IS 'Insert/select benchmark given data, returning "benchmark.benchmark_id".'; |
| |
| -- GET_BENCHMARK_ID (by unique columns) |
| CREATE OR REPLACE FUNCTION public.get_benchmark_id( |
| benchmark_language citext |
| , benchmark_name citext |
| , benchmark_version citext |
| ) |
| RETURNS integer AS |
| $$ |
| WITH language AS ( |
| SELECT public.get_benchmark_language_id(benchmark_language) AS id |
| ) |
| SELECT b.benchmark_id |
| FROM public.benchmark AS b |
| JOIN language ON b.benchmark_language_id = language.id |
| WHERE b.benchmark_name = benchmark_name |
| AND benchmark_version = benchmark_version |
| $$ |
| LANGUAGE sql STABLE; |
| COMMENT ON FUNCTION public.get_benchmark_id(citext, citext, citext) |
| IS 'Select existing benchmark given unique columns, ' |
| 'returning "benchmark.benchmark_id".'; |
