Skip to content

Instantly share code, notes, and snippets.

@kwk

kwk/README.md

Last active July 29, 2022 10:29
Show Gist options
  • Save kwk/fbad99f8dc60f680761ff4d98b1cb656 to your computer and use it in GitHub Desktop.
Save kwk/fbad99f8dc60f680761ff4d98b1cb656 to your computer and use it in GitHub Desktop.
Download ZIP
Transform Copr Build log API to Postgres Insert statements
Raw
README.md

REDAME

I wanted to create a dashboard in grafana about copr builds and needed to feed the build data into a postgres database. From there I can consume it in Grafana using a Postgres Data Source.

In order to have a simple conversion at hand I've written the script copr-json-to-sql.sh which prints SQL-INSERT-statements for the build information in a denomalized form.

The schema for the copr_build_logs table can be found in copr_build_logs.sql.

For example, you can call the script to get information about the builds of the @kdesig/python-qt6 project which is featured here.

$ curl -sL 'https://copr.fedorainfracloud.org/api_3/build/list/?ownername=%40kdesig&projectname=python-qt6' \
| copr-json-to-sql.sh

You should see an output similar to this:

INSERT INTO copr_build_logs (
    owner_name,
    project_name,
    submitter,
    source_package_name,
    source_package_url,
    source_package_version,
    project_dirname,
    state,
    repo_url,
    build_id,
    ended_on_ts,
    started_on_ts,
    submitted_on,
    is_background,
    chroots
) VALUES 
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-qt6', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04200339/python-qt6-6.2.3-1.fc35.src.rpm', '6.2.3-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4200339, to_timestamp(1649808972), to_timestamp(1649803664), to_timestamp(1649803622), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64']), 
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-pyqt6-sip', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04200338/python-pyqt6-sip-13.3.0-1.fc35.src.rpm', '13.3.0-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4200338, to_timestamp(1649804303), to_timestamp(1649803642), to_timestamp(1649803595), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64']), 
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-qt6', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04199264/python-qt6-6.2.3-1.fc35.src.rpm', '6.2.3-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4199264, to_timestamp(1649802694), to_timestamp(1649797473), to_timestamp(1649796495), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64'])

    ON CONFLICT ON CONSTRAINT copr_build_logs_pkey
    DO UPDATE SET
        submitter=excluded.submitter,
        source_package_name=excluded.source_package_name,
        source_package_url=excluded.source_package_url,
        source_package_version=excluded.source_package_version,
        project_dirname=excluded.project_dirname,
        state=excluded.state,
        repo_url=excluded.repo_url,
        ended_on_ts=excluded.ended_on_ts,
        started_on_ts=excluded.started_on_ts,
        submitted_on=excluded.submitted_on,
        is_background=excluded.is_background,
        chroots=excluded.chroots
    ;
Raw
copr-json-to-sql.sh
INSERT INTO copr_build_logs (
owner_name,
project_name,
submitter,
source_package_name,
source_package_url,
source_package_version,
project_dirname,
state,
repo_url,
build_id,
ended_on_ts,
started_on_ts,
submitted_on,
is_background,
chroots
) VALUES
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-qt6', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04200339/python-qt6-6.2.3-1.fc35.src.rpm', '6.2.3-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4200339, to_timestamp(1649808972), to_timestamp(1649803664), to_timestamp(1649803622), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64']),
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-pyqt6-sip', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04200338/python-pyqt6-sip-13.3.0-1.fc35.src.rpm', '13.3.0-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4200338, to_timestamp(1649804303), to_timestamp(1649803642), to_timestamp(1649803595), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64']),
('@kdesig', 'python-qt6', 'thunderbirdtr', 'python-qt6', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6/srpm-builds/04199264/python-qt6-6.2.3-1.fc35.src.rpm', '6.2.3-1.fc35', 'python-qt6', 'succeeded', 'https://download.copr.fedorainfracloud.org/results/@kdesig/python-qt6', 4199264, to_timestamp(1649802694), to_timestamp(1649797473), to_timestamp(1649796495), false, ARRAY['fedora-rawhide-x86_64', 'fedora-36-x86_64', 'fedora-rawhide-aarch64', 'fedora-36-aarch64'])
ON CONFLICT ON CONSTRAINT copr_build_logs_pkey
DO UPDATE SET
submitter=excluded.submitter,
source_package_name=excluded.source_package_name,
source_package_url=excluded.source_package_url,
source_package_version=excluded.source_package_version,
project_dirname=excluded.project_dirname,
state=excluded.state,
repo_url=excluded.repo_url,
ended_on_ts=excluded.ended_on_ts,
started_on_ts=excluded.started_on_ts,
submitted_on=excluded.submitted_on,
is_background=excluded.is_background,
chroots=excluded.chroots
;
Raw
copr_build_logs.sql
DROP TABLE IF EXISTS "copr_build_logs";
-- See common/copr_common/enums.py for the definition of the enum
-- "failed": 0, # build failed
-- "succeeded": 1, # build succeeded
-- "canceled": 2, # build was canceled
-- "running": 3, # SRPM or RPM build is running
-- "pending": 4, # build(-chroot) is waiting to be picked
-- "skipped": 5, # if there was this package built already
-- "starting": 6, # build was picked by worker but no VM initialized yet
-- "importing": 7, # SRPM is being imported into dist-git
-- "forked": 8, # build(-chroot) was forked
-- "waiting": 9, # build(-chroot) is waiting for something else to finish
-- "unknown": 1000, # undefined
DROP TYPE IF EXISTS copr_build_logs_state_type;
CREATE TYPE copr_build_logs_state_type AS ENUM ('failed', 'succeeded', 'canceled', 'running', 'pending', 'skipped', 'starting', 'importing', 'forked', 'waiting', 'unknown');
CREATE TABLE "public"."copr_build_logs" (
"owner_name" text NOT NULL,
"project_name" text NOT NULL,
"build_id" bigint NOT NULL,
"started_on_ts" TIMESTAMP WITHOUT TIME ZONE,
"ended_on_ts" TIMESTAMP WITHOUT TIME ZONE,
"build_time_secs" bigint GENERATED ALWAYS AS (EXTRACT(epoch FROM ended_on_ts) - EXTRACT(epoch FROM started_on_ts)) STORED,
"started_on_year" integer GENERATED ALWAYS AS (EXTRACT (year FROM started_on_ts)) STORED,
"started_on_month" integer GENERATED ALWAYS AS (EXTRACT (month FROM started_on_ts)) STORED,
"started_on_hour" integer GENERATED ALWAYS AS (EXTRACT (hour FROM started_on_ts)) STORED,
"started_on_minute" integer GENERATED ALWAYS AS (EXTRACT (minute FROM started_on_ts)) STORED,
"started_on_isodow" integer GENERATED ALWAYS AS (EXTRACT (isodow FROM started_on_ts)) STORED,
"started_on_week" integer GENERATED ALWAYS AS (EXTRACT (week FROM started_on_ts)) STORED,
"ended_on_year" integer GENERATED ALWAYS AS (EXTRACT (year FROM ended_on_ts)) STORED,
"ended_on_month" integer GENERATED ALWAYS AS (EXTRACT (month FROM ended_on_ts)) STORED,
"ended_on_hour" integer GENERATED ALWAYS AS (EXTRACT (hour FROM ended_on_ts)) STORED,
"ended_on_minute" integer GENERATED ALWAYS AS (EXTRACT (minute FROM ended_on_ts)) STORED,
"ended_on_isodow" integer GENERATED ALWAYS AS (EXTRACT (isodow FROM ended_on_ts)) STORED,
"ended_on_week" integer GENERATED ALWAYS AS (EXTRACT (week FROM ended_on_ts)) STORED,
"is_background" boolean,
"project_dirname" text NOT NULL,
"repo_url" text NOT NULL,
"source_package_name" text NOT NULL,
"source_package_url" text NOT NULL,
"source_package_version" text NOT NULL,
"submitted_on" timestamp NOT NULL,
"submitter" text NOT NULL,
"chroots" text[],
"state" copr_build_logs_state_type NOT NULL,
"last_modified" timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "copr_build_logs_pkey" PRIMARY KEY ("owner_name", "project_name", "build_id")
);
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
NEW.last_modified = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE OR REPLACE TRIGGER copr_logs_last_modified
BEFORE INSERT OR UPDATE
ON copr_build_logs
FOR EACH ROW
EXECUTE PROCEDURE update_last_modified();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment