ceph/src/arrow/dev/benchmarking/ddl/3_00_functions_helpers.sql

   1 /*
   2   Licensed to the Apache Software Foundation (ASF) under one
   3   or more contributor license agreements.  See the NOTICE file
   4   distributed with this work for additional information
   5   regarding copyright ownership.  The ASF licenses this file
   6   to you under the Apache License, Version 2.0 (the
   7   "License"); you may not use this file except in compliance
   8   with the License.  You may obtain a copy of the License at
   9
  10     http://www.apache.org/licenses/LICENSE-2.0
  11
  12   Unless required by applicable law or agreed to in writing,
  13   software distributed under the License is distributed on an
  14   "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  15   KIND, either express or implied.  See the License for the
  16   specific language governing permissions and limitations
  17   under the License.
  18 */
  19
  20
  21 -- PROJECT_DETAILS
  22 CREATE TYPE public.type_project_details AS (
  23   project_name text
  24   , project_url text
  25   , repo_url text
  26 );
  27
  28 CREATE OR REPLACE FUNCTION public.project_details()
  29 RETURNS public.type_project_details AS
  30 $$
  31   SELECT project_name, project_url, repo_url
  32   FROM public.project
  33   ORDER BY last_changed DESC
  34   LIMIT 1
  35 $$
  36 LANGUAGE sql STABLE;
  37 COMMENT ON FUNCTION public.project_details()
  38 IS 'Get the current project name, url, and repo url.';
  39
  40
  41 -------------------------- GET-OR-SET FUNCTIONS --------------------------
  42 --  The following functions have the naming convention "get_<tablename>_id".
  43 --  All of them attempt to SELECT the desired row given the column
  44 --  values, and if it does not exist will INSERT it.
  45 --
  46 --  When functions are overloaded with fewer columns, it is to allow
  47 --  selection only, given columns that comprise a unique index.
  48
  49 -- GET_CPU_ID
  50 CREATE OR REPLACE FUNCTION public.get_cpu_id(
  51   cpu_model_name citext
  52   , cpu_core_count integer
  53   , cpu_thread_count integer
  54   , cpu_frequency_max_Hz bigint
  55   , cpu_frequency_min_Hz bigint
  56   , cpu_L1d_cache_bytes integer
  57   , cpu_L1i_cache_bytes integer
  58   , cpu_L2_cache_bytes integer
  59   , cpu_L3_cache_bytes integer
  60 )
  61 RETURNS integer AS
  62 $$
  63   DECLARE
  64     result integer;
  65   BEGIN
  66     SELECT cpu_id INTO result FROM public.cpu AS cpu
  67     WHERE cpu.cpu_model_name = $1
  68       AND cpu.cpu_core_count = $2
  69       AND cpu.cpu_thread_count = $3
  70       AND cpu.cpu_frequency_max_Hz = $4
  71       AND cpu.cpu_frequency_min_Hz = $5
  72       AND cpu.cpu_L1d_cache_bytes = $6
  73       AND cpu.cpu_L1i_cache_bytes = $7
  74       AND cpu.cpu_L2_cache_bytes = $8
  75       AND cpu.cpu_L3_cache_bytes = $9;
  76
  77     IF result IS NULL THEN
  78       INSERT INTO public.cpu(
  79         cpu_model_name
  80         , cpu_core_count
  81         , cpu_thread_count
  82         , cpu_frequency_max_Hz
  83         , cpu_frequency_min_Hz
  84         , cpu_L1d_cache_bytes
  85         , cpu_L1i_cache_bytes
  86         , cpu_L2_cache_bytes
  87         , cpu_L3_cache_bytes
  88       )
  89       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
  90       RETURNING cpu_id INTO result;
  91     END IF;
  92
  93     RETURN result;
  94   END
  95 $$
  96 LANGUAGE plpgsql;
  97 COMMENT ON FUNCTION public.get_cpu_id(
  98   citext
  99   , integer
 100   , integer
 101   , bigint -- cpu_frequency_max_Hz
 102   , bigint -- cpu_frequency_min_Hz
 103   , integer
 104   , integer
 105   , integer
 106   , integer
 107 )
 108 IS 'Insert or select CPU data, returning "cpu.cpu_id".';
 109
 110 -- GET_GPU_ID
 111 CREATE OR REPLACE FUNCTION public.get_gpu_id(
 112   gpu_information citext DEFAULT NULL
 113   , gpu_part_number citext DEFAULT NULL
 114   , gpu_product_name citext DEFAULT NULL
 115 )
 116 RETURNS integer AS
 117 $$
 118   DECLARE
 119     result integer;
 120   BEGIN
 121     SELECT gpu_id INTO result FROM public.gpu AS gpu
 122     WHERE
 123       gpu.gpu_information = COALESCE($1, '')
 124       AND gpu.gpu_part_number = COALESCE($2, '')
 125       AND gpu.gpu_product_name = COALESCE($3, '');
 126
 127     IF result IS NULL THEN
 128       INSERT INTO public.gpu(
 129         gpu_information
 130         , gpu_part_number
 131         , gpu_product_name
 132       )
 133       VALUES (COALESCE($1, ''), COALESCE($2, ''), COALESCE($3, ''))
 134       RETURNING gpu_id INTO result;
 135     END IF;
 136
 137     RETURN result;
 138   END
 139 $$
 140 LANGUAGE plpgsql;
 141 COMMENT ON FUNCTION public.get_gpu_id(citext, citext, citext)
 142 IS 'Insert or select GPU data, returning "gpu.gpu_id".';
 143
 144 -- GET_OS_ID
 145 CREATE OR REPLACE FUNCTION public.get_os_id(
 146   os_name citext
 147   , architecture_name citext
 148   , kernel_name citext DEFAULT ''
 149 )
 150 RETURNS integer AS
 151 $$
 152   DECLARE
 153     result integer;
 154   BEGIN
 155     SELECT os_id INTO result FROM public.os AS os
 156     WHERE os.os_name = $1
 157       AND os.architecture_name = $2
 158       AND os.kernel_name = COALESCE($3, '');
 159
 160     IF result is NULL THEN
 161       INSERT INTO public.os(os_name, architecture_name, kernel_name)
 162       VALUES ($1, $2, COALESCE($3, ''))
 163       RETURNING os_id INTO result;
 164     END IF;
 165
 166     RETURN result;
 167   END
 168 $$
 169 LANGUAGE plpgsql;
 170 COMMENT ON FUNCTION public.get_os_id(citext, citext, citext)
 171 IS 'Insert or select OS data, returning "os.os_id".';
 172
 173 -- GET_MACHINE_ID (full signature)
 174 CREATE OR REPLACE FUNCTION public.get_machine_id(
 175   mac_address macaddr
 176   , machine_name citext
 177   , memory_bytes bigint
 178   , cpu_actual_frequency_Hz bigint
 179   -- os
 180   , os_name citext
 181   , architecture_name citext
 182   , kernel_name citext
 183   -- cpu
 184   , cpu_model_name citext
 185   , cpu_core_count integer
 186   , cpu_thread_count integer
 187   , cpu_frequency_max_Hz bigint
 188   , cpu_frequency_min_Hz bigint
 189   , L1d_cache_bytes integer
 190   , L1i_cache_bytes integer
 191   , L2_cache_bytes integer
 192   , L3_cache_bytes integer
 193   -- gpu
 194   , gpu_information citext DEFAULT ''
 195   , gpu_part_number citext DEFAULT NULL
 196   , gpu_product_name citext DEFAULT NULL
 197   -- nullable machine attributes
 198   , machine_other_attributes jsonb DEFAULT NULL
 199 )
 200 RETURNS integer AS
 201 $$
 202   DECLARE
 203     found_cpu_id integer;
 204     found_gpu_id integer;
 205     found_os_id integer;
 206     result integer;
 207   BEGIN
 208     -- Can't bypass looking up all the values because of unique constraint.
 209     SELECT public.get_cpu_id(
 210       cpu_model_name
 211       , cpu_core_count
 212       , cpu_thread_count
 213       , cpu_frequency_max_Hz
 214       , cpu_frequency_min_Hz
 215       , L1d_cache_bytes
 216       , L1i_cache_bytes
 217       , L2_cache_bytes
 218       , L3_cache_bytes
 219     ) INTO found_cpu_id;
 220
 221     SELECT public.get_gpu_id(
 222       gpu_information
 223       , gpu_part_number
 224       , gpu_product_name
 225     ) INTO found_gpu_id;
 226
 227     SELECT public.get_os_id(
 228       os_name
 229       , architecture_name
 230       , kernel_name
 231     ) INTO found_os_id;
 232
 233     SELECT machine_id INTO result FROM public.machine AS m
 234     WHERE m.os_id = found_os_id
 235       AND m.cpu_id = found_cpu_id
 236       AND m.gpu_id = found_gpu_id
 237       AND m.mac_address = $1
 238       AND m.machine_name = $2
 239       AND m.memory_bytes = $3
 240       AND m.cpu_actual_frequency_Hz = $4;
 241
 242     IF result IS NULL THEN
 243       INSERT INTO public.machine(
 244         os_id
 245         , cpu_id
 246         , gpu_id
 247         , mac_address
 248         , machine_name
 249         , memory_bytes
 250         , cpu_actual_frequency_Hz
 251         , machine_other_attributes
 252       )
 253       VALUES (found_os_id, found_cpu_id, found_gpu_id, $1, $2, $3, $4, $20)
 254       RETURNING machine_id INTO result;
 255     END IF;
 256
 257     RETURN result;
 258   END
 259 $$
 260 LANGUAGE plpgsql;
 261 COMMENT ON FUNCTION public.get_machine_id(
 262   macaddr
 263   , citext
 264   , bigint  -- memory_bytes
 265   , bigint  -- cpu_frequency_actual_Hz
 266   -- os
 267   , citext
 268   , citext
 269   , citext
 270   -- cpu
 271   , citext
 272   , integer
 273   , integer
 274   , bigint  -- cpu_frequency_max_Hz
 275   , bigint  -- cpu_frequency_min_Hz
 276   , integer
 277   , integer
 278   , integer
 279   , integer
 280   -- gpu
 281   , citext
 282   , citext
 283   , citext
 284   -- nullable machine attributes
 285   , jsonb
 286 )
 287 IS 'Insert or select machine data, returning "machine.machine_id".';
 288
 289 -- GET_MACHINE_ID (given unique mac_address)
 290 CREATE OR REPLACE FUNCTION public.get_machine_id(mac_address macaddr)
 291 RETURNS integer AS
 292 $$
 293   SELECT machine_id FROM public.machine AS m
 294   WHERE m.mac_address = $1;
 295 $$
 296 LANGUAGE sql STABLE;
 297 COMMENT ON FUNCTION public.get_machine_id(macaddr)
 298 IS 'Select machine_id given its mac address, returning "machine.machine_id".';
 299
 300 -- GET_BENCHMARK_LANGUAGE_ID
 301 CREATE OR REPLACE FUNCTION public.get_benchmark_language_id(language citext)
 302 RETURNS integer AS
 303 $$
 304   DECLARE
 305     result integer;
 306   BEGIN
 307     SELECT benchmark_language_id INTO result
 308     FROM public.benchmark_language AS bl
 309     WHERE bl.benchmark_language = language;
 310
 311     IF result IS NULL THEN
 312       INSERT INTO public.benchmark_language(benchmark_language)
 313       VALUES (language)
 314       RETURNING benchmark_language_id INTO result;
 315     END IF;
 316
 317     RETURN result;
 318   END
 319 $$
 320 LANGUAGE plpgsql;
 321 COMMENT ON FUNCTION public.get_benchmark_language_id(citext)
 322 IS 'Insert or select benchmark_language returning '
 323    '"benchmark_language.benchmark_language_id".';
 324
 325 -- GET_LANGUAGE_IMPLEMENTATION_VERSION_ID
 326 CREATE OR REPLACE FUNCTION public.get_language_implementation_version_id(
 327   language citext
 328   , language_implementation_version citext DEFAULT ''
 329 )
 330 RETURNS integer AS
 331 $$
 332   DECLARE
 333     language_id integer;
 334     result integer;
 335   BEGIN
 336     SELECT public.get_benchmark_language_id($1) INTO language_id;
 337
 338     SELECT language_implementation_version_id INTO result FROM public.language_implementation_version AS lv
 339     WHERE lv.benchmark_language_id = language_id
 340       AND lv.language_implementation_version = COALESCE($2, '');
 341
 342     IF result IS NULL THEN
 343       INSERT INTO
 344         public.language_implementation_version(benchmark_language_id, language_implementation_version)
 345       VALUES (language_id, COALESCE($2, ''))
 346       RETURNING language_implementation_version_id INTO result;
 347     END IF;
 348
 349     RETURN result;
 350   END
 351 $$
 352 LANGUAGE plpgsql;
 353 COMMENT ON FUNCTION public.get_language_implementation_version_id(citext, citext)
 354 IS 'Insert or select language and version data, '
 355       'returning "language_implementation_version.language_implementation_version_id".';
 356
 357 CREATE OR REPLACE FUNCTION public.get_language_implementation_version_id(
 358   -- overload for when language_id is known
 359   language_id integer
 360   , language_implementation_version citext DEFAULT ''
 361 )
 362 RETURNS integer AS
 363 $$
 364   DECLARE
 365     result integer;
 366   BEGIN
 367     SELECT language_implementation_version_id INTO result FROM public.language_implementation_version AS lv
 368     WHERE lv.benchmark_language_id = language_id
 369       AND lv.language_implementation_version = COALESCE($2, '');
 370
 371     IF result IS NULL THEN
 372       INSERT INTO
 373         public.language_implementation_version(benchmark_language_id, language_implementation_version)
 374       VALUES (language_id, COALESCE($2, ''))
 375       RETURNING language_implementation_version_id INTO result;
 376     END IF;
 377
 378     RETURN result;
 379   END
 380 $$
 381 LANGUAGE plpgsql;
 382
 383 -- GET_LANGUAGE_DEPENDENCY_LOOKUP_ID
 384 CREATE OR REPLACE FUNCTION public.get_dependencies_id(
 385   dependencies jsonb DEFAULT '{}'::jsonb
 386 )
 387 RETURNS integer AS
 388 $$
 389   DECLARE
 390     result integer;
 391   BEGIN
 392     SELECT dependencies_id INTO result
 393     FROM public.dependencies AS ldl
 394     WHERE ldl.dependencies = COALESCE($1, '{}'::jsonb);
 395
 396     IF result IS NULL THEN
 397       INSERT INTO
 398         public.dependencies(dependencies)
 399       VALUES (COALESCE($1, '{}'::jsonb))
 400       RETURNING dependencies_id INTO result;
 401     END IF;
 402
 403     RETURN result;
 404   END
 405 $$
 406 LANGUAGE plpgsql;
 407 COMMENT ON FUNCTION public.get_dependencies_id(jsonb)
 408 IS 'Insert or select dependencies, returning "dependencies.dependencies_id".';
 409
 410 -- GET_ENVIRONMENT_ID
 411 CREATE OR REPLACE FUNCTION public.get_environment_id(
 412   language citext,
 413   language_implementation_version citext DEFAULT '',
 414   dependencies jsonb DEFAULT '{}'::jsonb
 415 )
 416 RETURNS integer AS
 417 $$
 418   DECLARE
 419     found_language_id integer;
 420     found_version_id integer;
 421     found_dependencies_id integer;
 422     result integer;
 423   BEGIN
 424     SELECT public.get_benchmark_language_id($1) INTO found_language_id;
 425     SELECT
 426       public.get_language_implementation_version_id(found_language_id, $2)
 427       INTO found_version_id;
 428     SELECT
 429       public.get_dependencies_id ($3)
 430       INTO found_dependencies_id;
 431
 432     SELECT environment_id INTO result FROM public.environment AS e
 433     WHERE e.benchmark_language_id = found_language_id
 434       AND e.language_implementation_version_id = found_version_id
 435       AND e.dependencies_id = found_dependencies_id;
 436
 437     IF result IS NULL THEN
 438       INSERT INTO
 439         public.environment(
 440           benchmark_language_id
 441           , language_implementation_version_id
 442           , dependencies_id
 443         )
 444       VALUES (found_language_id, found_version_id, found_dependencies_id)
 445       RETURNING environment_id INTO result;
 446     END IF;
 447
 448     RETURN result;
 449   END
 450 $$
 451 LANGUAGE plpgsql;
 452 COMMENT ON FUNCTION public.get_environment_id(citext, citext, jsonb)
 453 IS 'Insert or select language, language version, and dependencies, '
 454       'returning "environment.environment_id".';
 455
 456 -- GET_BENCHMARK_TYPE_ID (full signature)
 457 CREATE OR REPLACE FUNCTION public.get_benchmark_type_id(
 458   benchmark_type citext
 459   , lessisbetter boolean
 460 )
 461 RETURNS integer AS
 462 $$
 463   DECLARE
 464     result integer;
 465   BEGIN
 466     SELECT benchmark_type_id INTO result FROM public.benchmark_type AS bt
 467     WHERE bt.benchmark_type = $1
 468       AND bt.lessisbetter = $2;
 469
 470     IF result IS NULL THEN
 471       INSERT INTO public.benchmark_type(benchmark_type, lessisbetter)
 472       VALUES($1, $2)
 473       RETURNING benchmark_type_id INTO result;
 474     END IF;
 475
 476     RETURN result;
 477   END
 478 $$
 479 LANGUAGE plpgsql;
 480 COMMENT ON FUNCTION public.get_benchmark_type_id(citext, boolean)
 481 IS 'Insert or select benchmark type and lessisbetter, '
 482    'returning "benchmark_type.benchmark_type_id".';
 483
 484 -- GET_BENCHMARK_TYPE_ID (given unique benchmark_type string only)
 485 CREATE OR REPLACE FUNCTION public.get_benchmark_type_id(
 486   benchmark_type citext
 487 )
 488 RETURNS integer AS
 489 $$
 490   DECLARE
 491     result integer;
 492   BEGIN
 493     SELECT benchmark_type_id INTO result FROM public.benchmark_type AS bt
 494     WHERE bt.benchmark_type = $1;
 495
 496     RETURN result;
 497   END
 498 $$
 499 LANGUAGE plpgsql;
 500 COMMENT ON FUNCTION public.get_benchmark_type_id(citext)
 501 IS 'Select benchmark_type_id given benchmark type (e.g. ''time''), '
 502    'returning "benchmark_type.benchmark_type_id".';
 503
 504 -- GET_UNIT_ID (full signature)
 505 CREATE OR REPLACE FUNCTION public.get_unit_id(
 506   benchmark_type citext
 507   , units citext
 508   , lessisbetter boolean DEFAULT NULL
 509 )
 510 RETURNS integer AS
 511 $$
 512   DECLARE
 513     found_benchmark_type_id integer;
 514     result integer;
 515   BEGIN
 516
 517     IF ($3 IS NOT NULL)  -- if lessisbetter is not null
 518     THEN
 519       SELECT public.get_benchmark_type_id($1, $3)
 520         INTO found_benchmark_type_id;
 521     ELSE
 522       SELECT public.get_benchmark_type_id($1)
 523         INTO found_benchmark_type_id;
 524     END IF;
 525
 526     SELECT unit_id INTO result FROM public.unit AS u
 527     WHERE u.benchmark_type_id = found_benchmark_type_id
 528       AND u.units = $2;
 529
 530     IF result IS NULL THEN
 531       INSERT INTO public.unit(benchmark_type_id, units)
 532       VALUES(found_benchmark_type_id, $2)
 533       RETURNING unit_id INTO result;
 534     END IF;
 535
 536     RETURN result;
 537   END
 538 $$
 539 LANGUAGE plpgsql;
 540 COMMENT ON FUNCTION public.get_unit_id(citext, citext, boolean)
 541 IS 'Insert or select benchmark type (e.g. ''time''), '
 542    'units string (e.g. ''miliseconds''), '
 543    'and "lessisbetter" (true if smaller benchmark values are better), '
 544    'returning "unit.unit_id".';
 545
 546 -- GET_UNIT_ID (given unique units string only)
 547 CREATE OR REPLACE FUNCTION public.get_unit_id(units citext)
 548 RETURNS integer AS
 549 $$
 550   SELECT unit_id FROM public.unit AS u
 551   WHERE u.units = units;
 552 $$
 553 LANGUAGE sql STABLE;
 554 COMMENT ON FUNCTION public.get_unit_id(citext)
 555 IS 'Select unit_id given unit name, returning "unit.unit_id".';
 556
 557 -- GET_BENCHMARK_ID (full signature)
 558 CREATE OR REPLACE FUNCTION public.get_benchmark_id(
 559   benchmark_language citext
 560   , benchmark_name citext
 561   , parameter_names text[]
 562   , benchmark_description text
 563   , benchmark_version citext
 564   , benchmark_type citext
 565   , units citext
 566   , lessisbetter boolean
 567 )
 568 RETURNS integer AS
 569 $$
 570   DECLARE
 571     found_benchmark_language_id integer;
 572     found_unit_id integer;
 573     result integer;
 574   BEGIN
 575     SELECT public.get_benchmark_language_id(
 576       benchmark_language
 577     ) INTO found_benchmark_language_id;
 578
 579     SELECT public.get_unit_id(
 580       benchmark_type
 581       , units
 582       , lessisbetter
 583     ) INTO found_unit_id;
 584
 585     SELECT benchmark_id INTO result FROM public.benchmark AS b
 586     WHERE b.benchmark_language_id = found_benchmark_language_id
 587       AND b.benchmark_name = $2
 588       -- handle nullable "parameter_names"
 589       AND b.parameter_names IS NOT DISTINCT FROM $3
 590       AND b.benchmark_description = $4
 591       AND b.benchmark_version = $5
 592       AND b.unit_id = found_unit_id;
 593
 594     IF result IS NULL THEN
 595       INSERT INTO public.benchmark(
 596         benchmark_language_id
 597         , benchmark_name
 598         , parameter_names
 599         , benchmark_description
 600         , benchmark_version
 601         , unit_id
 602       )
 603       VALUES (found_benchmark_language_id, $2, $3, $4, $5, found_unit_id)
 604       RETURNING benchmark_id INTO result;
 605     END IF;
 606
 607     RETURN result;
 608   END
 609 $$
 610 LANGUAGE plpgsql;
 611 COMMENT ON FUNCTION public.get_benchmark_id(
 612   citext
 613   , citext
 614   , text[]
 615   , text
 616   , citext
 617   , citext
 618   , citext
 619   , boolean
 620 )
 621 IS 'Insert/select benchmark given data, returning "benchmark.benchmark_id".';
 622
 623 -- GET_BENCHMARK_ID (by unique columns)
 624 CREATE OR REPLACE FUNCTION public.get_benchmark_id(
 625   benchmark_language citext
 626   , benchmark_name citext
 627   , benchmark_version citext
 628 )
 629 RETURNS integer AS
 630 $$
 631   WITH language AS (
 632     SELECT public.get_benchmark_language_id(benchmark_language) AS id
 633   )
 634   SELECT b.benchmark_id
 635   FROM public.benchmark AS b
 636   JOIN language ON b.benchmark_language_id = language.id
 637   WHERE b.benchmark_name = benchmark_name
 638     AND benchmark_version = benchmark_version
 639 $$
 640 LANGUAGE sql STABLE;
 641 COMMENT ON FUNCTION public.get_benchmark_id(citext, citext, citext)
 642 IS 'Select existing benchmark given unique columns, '
 643    'returning "benchmark.benchmark_id".';