samples.txt

Here are some query samples I've been playing with.

After inserting this data:

INSERT INTO incoming_packages (data) values (
'{
  "name": "foo.example.org",
  "os": "FreeBSD",
  "version": "12.0-RELEASE-p8",
  "repo": "http://pkg.freebsd.org/FreeBSD:12:amd64/latest/",
  "packages": {
    "package": [
      "apr-1.6.5.1.6.1_1",
      [
        "bacula9-client-9.4.3"
      ],
      [
        "bash-5.0.7"
      ]
    ]
  }
}');

I can do this query:

samdrucker=# select id, data->>'name' from incoming_packages;
 id |     ?column?      
----+-------------------
  1 | "foo.example.org"
(1 row)

samdrucker=# 

Insert into host table:

INSERT INTO host (name, os, version, repo) 
   SELECT data->>'name', data->>'os', data->>'version', data->>'repo'
     FROM incoming_packages
    WHERE id = 1;

View that data:

samdrucker=# SELECT * FROM host;
 id |       name        |    os     |      version      |                       repo                        
----+-------------------+-----------+-------------------+---------------------------------------------------
  1 | "foo.example.org" | "FreeBSD" | "12.0-RELEASE-p8" | "http://pkg.freebsd.org/FreeBSD:12:amd64/latest/"
(1 row)

See those "quotes"? That's part of the JSON.  That might be an issue on
queries.

If we do another insert, we get an error, as expected:

samdrucker=# INSERT INTO hoste (name, os, version, repo) 
   SELECT data->>'name', data->>'os', data->>'version', data->>'repo'
     FROM incoming_packages
    WHERE id = 1;
ERROR:  duplicate key value violates unique constraint "hoste_name_key"
DETAIL:  Key (name)=("foo.example.org") already exists.
samdrucker=# 

Instead, we can do an update

INSERT INTO host (name, os, version, repo) 
   SELECT data->>'name', data->>'os', '12.0-RELEASE-p9', data->>'repo'
     FROM incoming_packages
    WHERE id = 1
  ON CONFLICT(name) 
  DO UPDATE SET os      = EXCLUDED.os,
                version = EXCLUDED.version,
                repo    = EXCLUDED.repo;

Noticed how we have updated the version:

samdrucker=# SELECT * FROM host;
 id |       name      |   os    |     version     |                      repo                       
----+-----------------+---------+-----------------+-------------------------------------------------
  1 | foo.example.org | FreeBSD | 12.0-RELEASE-p9 | http://pkg.freebsd.org/FreeBSD:12:amd64/latest/
(1 row)

Inserting the packages

1 - split package into name and version:
    foo-1.2.3
    split on the rightmost hypen.
    Everything to the left is package name.
    Everything to the right is version.

    Try (.+)-[^-]+$

INSERT INTO package (name) values ('apr')
  ON CONFLICT(name)
  DO NOTHING
  RETURNING id;

INSERT INTO package_version (package_id, version) values (1, '1.6.5.1.6.1_1')
  ON CONFLICT ON CONSTRAINT package_version_package_id_version_key
  DO NOTHING
  RETURNING id;

INSERT INTO host_package (host_id, package_version_id) values(1, 1);
  ON CONFLICT ON CONSTRAINT host_package_host_id_package_version_id_key
  DO NOTHING;


INSERT INTO package (name) values ('bacula9-client')
  ON CONFLICT(name)
  DO NOTHING
  RETURNING id;

INSERT INTO package_version (package_id, version) values (5, '9.4.3')
  ON CONFLICT ON CONSTRAINT package_version_package_id_version_key
  DO NOTHING
  RETURNING id;

INSERT INTO host_package (host_id, package_version_id) values(7, 3)
  ON CONFLICT ON CONSTRAINT host_package_host_id_package_version_id_key
  DO NOTHING;


Now the queries for output:

What servers have this package installed?

samdrucker=# SELECT H.name
samdrucker-#   FROM package P JOIN package_version PV ON P.name     = 'apr' 
samdrucker-#                                         AND P.id       = PV.package_id
samdrucker-#                                         AND PV.version = '1.6.5.1.6.1_1'
samdrucker-#                  JOIN host_package HP    ON PV.id      = HP.package_version_id
samdrucker-#                  JOIN host         H     ON HP.host_id = H.id
samdrucker-#   ORDER BY 1;
      name       
-----------------
 foo.example.org
(1 row)

Or by function:

# select * from HostsWithPackage('apr');
 hostswithpackage 
------------------
 foo.example.org
(1 row)

I think including the package version up there might be a good idea.

What packages are installed on this server:
samdrucker=# SELECT P.name || '-' || PV.version
samdrucker-#   FROM host H JOIN host_package    HP ON H.name                = '"foo.example.org"'
samdrucker-#                                      AND H.id                  = HP.host_id
samdrucker-#               JOIN package_version PV ON HP.package_version_id = PV.id
samdrucker-#               JOIN package         P  ON PV.package_id         = P.id
samdrucker-#   ORDER BY 1;
     ?column?      
-------------------
 apr-1.6.5.1.6.1_1

Or using the function:

samdrucker=# SELECT * FROM PackagesOnHost('foo.example.org');
    packagesonhost    
----------------------
 apr-1.6.5.1.6.1_1
 bacula9-client-9.4.3
(2 rows)


Eventually the way you can update the list of packages is like this:

SELECT HostAddPackages('{
  "name": "foo.example.org",
  "os": "FreeBSD",
  "version": "12.0-RELEASE-p8",
  "repo": "http://pkg.freebsd.org/FreeBSD:12:amd64/latest/",
  "packages": {
    "package": [
      "apr-1.6.5.1.6.1_1",
      [
        "bacula9-client-9.4.3"
      ],
      [
        "bash-5.0.7"
      ]
    ]
  }
}');