diff options
Diffstat (limited to 'src/arrow/dev/benchmarking/ddl/2_00_views.sql')
| -rw-r--r-- | src/arrow/dev/benchmarking/ddl/2_00_views.sql | 324 |
1 files changed, 324 insertions, 0 deletions
diff --git a/src/arrow/dev/benchmarking/ddl/2_00_views.sql b/src/arrow/dev/benchmarking/ddl/2_00_views.sql new file mode 100644 index 000000000..cbd295e50 --- /dev/null +++ b/src/arrow/dev/benchmarking/ddl/2_00_views.sql @@ -0,0 +1,324 @@ +/* + 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. +*/ + +-- NOTE: +-- The function for documentation depends on view columns +-- being named exactly the same as in the table view. + +-- MACHINE_VIEW +CREATE OR REPLACE VIEW public.machine_view AS + SELECT + machine.machine_id + , mac_address + , machine_name + , memory_bytes + , cpu_actual_frequency_Hz + , os_name + , architecture_name + , kernel_name + , 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 + , gpu_information + , gpu_part_number + , gpu_product_name + , machine_other_attributes + FROM public.machine AS machine + JOIN public.cpu AS cpu ON machine.cpu_id = cpu.cpu_id + JOIN public.gpu AS gpu ON machine.gpu_id = gpu.gpu_id + JOIN public.os AS os ON machine.os_id = os.os_id; +COMMENT ON VIEW public.machine_view IS +E'The machine environment (CPU, GPU, OS) used for each benchmark run.\n\n' + '- "mac_address" is unique in the "machine" table\n' + '- "gpu_part_number" is unique in the "gpu" (graphics processing unit) table\n' + ' Empty string (''''), not null, is used for machines that won''t use the GPU\n' + '- "cpu_model_name" is unique in the "cpu" (central processing unit) table\n' + '- "os_name", "os_architecture_name", and "os_kernel_name"\n' + ' are unique in the "os" (operating system) table\n' + '- "machine_other_attributes" is a key-value store for any other relevant\n' + ' data, e.g. ''{"hard_disk_type": "solid state"}'''; + + +-- LANGUAGE_IMPLEMENTATION_VERSION_VIEW +CREATE OR REPLACE VIEW public.language_implementation_version_view AS + SELECT + lv.language_implementation_version_id + , bl.benchmark_language + , lv.language_implementation_version + FROM public.language_implementation_version AS lv + JOIN public.benchmark_language AS bl + ON lv.benchmark_language_id = bl.benchmark_language_id; + +-- ENVIRONMENT_VIEW +CREATE OR REPLACE VIEW public.environment_view AS + SELECT + env.environment_id + , benchmark_language + , language_implementation_version + , dependencies + FROM public.environment AS env + JOIN public.benchmark_language AS language + ON env.benchmark_language_id = language.benchmark_language_id + JOIN public.language_implementation_version AS version + ON env.language_implementation_version_id = version.language_implementation_version_id + JOIN public.dependencies AS deps + ON env.dependencies_id = deps.dependencies_id; +COMMENT ON VIEW public.environment_view IS +E'The build environment used for a reported benchmark run.\n' + '(Will be inferred from each "benchmark_run" if not explicitly added).\n\n' + '- Each entry is unique on\n' + ' ("benchmark_language", "language_implementation_version", "dependencies")\n' + '- "benchmark_language" is unique in the "benchmark_language" table\n' + '- "benchmark_language" plus "language_implementation_version" is unique in\n' + ' the "language_implementation_version" table\n' + '- "dependencies" is unique in the "dependencies" table'; + +-- UNIT_VIEW +CREATE OR REPLACE VIEW public.unit_view AS + SELECT + unit.unit_id + , units + , benchmark_type + , lessisbetter + FROM public.unit AS unit + JOIN public.benchmark_type AS bt + ON unit.benchmark_type_id = bt.benchmark_type_id; + +-- BENCHMARK_VIEW +CREATE OR REPLACE VIEW public.benchmark_view AS + SELECT + b.benchmark_id + , benchmark_name + , parameter_names + , benchmark_description + , benchmark_type + , units + , lessisbetter + , benchmark_version + , benchmark_language + FROM public.benchmark AS b + JOIN public.benchmark_language AS benchmark_language + ON b.benchmark_language_id = benchmark_language.benchmark_language_id + JOIN public.unit AS unit + ON b.unit_id = unit.unit_id + JOIN public.benchmark_type AS benchmark_type + ON unit.benchmark_type_id = benchmark_type.benchmark_type_id; +COMMENT ON VIEW public.benchmark_view IS +E'The details about a particular benchmark.\n\n' + '- "benchmark_name" is unique for a given "benchmark_language"\n' + '- Each entry is unique on\n' + ' ("benchmark_language", "benchmark_name", "benchmark_version")'; + +-- BENCHMARK_RUN_VIEW +CREATE OR REPLACE VIEW public.benchmark_run_view AS + SELECT + run.benchmark_run_id + -- benchmark_view (name, version, language only) + , benchmark_name + , benchmark_version + -- datum + , parameter_values + , value + , git_commit_timestamp + , git_hash + , val_min + , val_q1 + , val_q3 + , val_max + , std_dev + , n_obs + , run_timestamp + , run_metadata + , run_notes + -- machine_view (mac address only) + , mac_address + -- environment_view + , env.benchmark_language + , language_implementation_version + , dependencies + FROM public.benchmark_run AS run + JOIN public.benchmark_view AS benchmark + ON run.benchmark_id = benchmark.benchmark_id + JOIN public.machine_view AS machine + ON run.machine_id = machine.machine_id + JOIN public.environment_view AS env + ON run.environment_id = env.environment_id; +COMMENT ON VIEW public.benchmark_run_view IS +E'Each benchmark run.\n\n' + '- Each entry is unique on the machine, environment, benchmark,\n' + ' and git commit timestamp.'; + +-- FULL_BENCHMARK_RUN_VIEW +CREATE OR REPLACE VIEW public.full_benchmark_run_view AS + SELECT + run.benchmark_run_id + -- benchmark_view + , benchmark_name + , parameter_names + , benchmark_description + , benchmark_type + , units + , lessisbetter + , benchmark_version + -- datum + , parameter_values + , value + , git_commit_timestamp + , git_hash + , val_min + , val_q1 + , val_q3 + , val_max + , std_dev + , n_obs + , run_timestamp + , run_metadata + , run_notes + -- machine_view + , machine_name + , mac_address + , memory_bytes + , cpu_actual_frequency_Hz + , os_name + , architecture_name + , kernel_name + , 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 + , gpu_information + , gpu_part_number + , gpu_product_name + , machine_other_attributes + -- environment_view + , env.benchmark_language + , env.language_implementation_version + , dependencies + FROM public.benchmark_run AS run + JOIN public.benchmark_view AS benchmark + ON run.benchmark_id = benchmark.benchmark_id + JOIN public.machine_view AS machine + ON run.machine_id = machine.machine_id + JOIN public.environment_view AS env + ON run.environment_id = env.environment_id; + +-- SUMMARIZED_TABLES_VIEW +CREATE VIEW public.summarized_tables_view AS + WITH chosen AS ( + SELECT + cls.oid AS id + , cls.relname as tbl_name + FROM pg_catalog.pg_class AS cls + JOIN pg_catalog.pg_namespace AS ns ON cls.relnamespace = ns.oid + WHERE + cls.relkind = 'r' + AND ns.nspname = 'public' + ), all_constraints AS ( + SELECT + chosen.id AS tbl_id + , chosen.tbl_name + , unnest(conkey) AS col_id + , 'foreign key' AS col_constraint + FROM pg_catalog.pg_constraint + JOIN chosen ON chosen.id = conrelid + WHERE contype = 'f' + + UNION + + SELECT + chosen.id + , chosen.tbl_name + , unnest(indkey) + , 'unique' + FROM pg_catalog.pg_index i + JOIN chosen ON chosen.id = i.indrelid + WHERE i.indisunique AND NOT i.indisprimary + + UNION + + SELECT + chosen.id + , chosen.tbl_name + , unnest(indkey) + , 'primary key' + FROM pg_catalog.pg_index i + JOIN chosen on chosen.id = i.indrelid + WHERE i.indisprimary + ), gathered_constraints AS ( + SELECT + tbl_id + , tbl_name + , col_id + , string_agg(col_constraint, ', ' ORDER BY col_constraint) + AS col_constraint + FROM all_constraints + GROUP BY tbl_id, tbl_name, col_id + ) + SELECT + chosen.tbl_name AS table_name + , columns.attnum AS column_number + , columns.attname AS column_name + , typ.typname AS type_name + , CASE + WHEN columns.attnotnull + THEN 'not null' + ELSE '' + END AS nullable + , CASE + WHEN defaults.adsrc like 'nextval%' + THEN 'serial' + ELSE defaults.adsrc + END AS default_value + , CASE + WHEN gc.col_constraint = '' OR gc.col_constraint IS NULL + THEN cnstrnt.consrc + WHEN cnstrnt.consrc IS NULL + THEN gc.col_constraint + ELSE gc.col_constraint || ', ' || cnstrnt.consrc + END AS description + FROM pg_catalog.pg_attribute AS columns + JOIN chosen ON columns.attrelid = chosen.id + JOIN pg_catalog.pg_type AS typ + ON typ.oid = columns.atttypid + LEFT JOIN gathered_constraints AS gc + ON gc.col_id = columns.attnum + AND gc.tbl_id = columns.attrelid + LEFT JOIN pg_attrdef AS defaults + ON defaults.adrelid = chosen.id + AND defaults.adnum = columns.attnum + LEFT JOIN pg_catalog.pg_constraint AS cnstrnt + ON cnstrnt.conrelid = columns.attrelid + AND columns.attrelid = ANY(cnstrnt.conkey) + WHERE + columns.attnum > 0 + ORDER BY table_name, column_number; +COMMENT ON VIEW public.summarized_tables_view + IS 'A summary of all columns from all tables in the public schema, ' + ' identifying nullability, primary/foreign keys, and data type.'; |