Skip to content

Sample queries

Jump to bottom
Dan Langille edited this page Nov 1, 2019 · 1 revision

This page contains sample database queries which can be performed.

There will be a web interface as well.

Where is php72-7.2.24 installed?

samdrucker=# SELECT H.name
  FROM package P JOIN package_version PV ON P.name     = 'php72' 
                                        AND P.id       = PV.package_id
                                        AND PV.version = '7.2.24'
                 JOIN host_package HP    ON PV.id      = HP.package_version_id
                 JOIN host         H     ON HP.host_id = H.id
  ORDER BY 1;
             name              
-------------------------------
 gelt.unixathome.org
 samdrucker.int.unixathome.org
 slocum.int.unixathome.org
(3 rows)

samdrucker=#

We have functions too

Multi-table queries aren't always the way to go, so we have this:

samdrucker=# select * from HostsWithPackage('sudo');
       hostswithpackage        
-------------------------------
 slocum.int.unixathome.org
 gelt.unixathome.org
 samdrucker.int.unixathome.org
(3 rows)

samdrucker=#

What packages are installed on this host?

samdrucker=# SELECT P.name || '-' || PV.version
  FROM host H JOIN host_package    HP ON H.name                = 'samdrucker.int.unixathome.org'
                                     AND H.id                  = HP.host_id
              JOIN package_version PV ON HP.package_version_id = PV.id
              JOIN package         P  ON PV.package_id         = P.id
  ORDER BY 1;
                  ?column?                   
---------------------------------------------
 SamDruckerClientShell-0.0.0.1.20191023100_3
 anvil-0.0.17
 apache24-2.4.41
 apr-1.7.0.1.6.1
 bash-5.0.11
 bind-tools-9.14.7
 ca_root_nss-3.47
 curl-7.66.0
 cyrus-sasl-2.1.27
 db5-5.3.28_7
 expat-2.2.8
 gdbm-1.18.1_1
 gettext-runtime-0.20.1
 icu-65.1,1
 indexinfo-0.3.1
 jo-1.2
 joe-4.6,1
 json-c-0.13.1_1
 libargon2-20190702
 libedit-3.1.20190324,1
 libevent-2.1.11
 libffi-3.2.1_3
 libidn2-2.2.0
 liblockfile-1.16
 liblz4-1.9.2,1
 libnghttp2-1.39.2
 libunistring-0.9.10_1
 libxml2-2.9.9
 lockfile-progs-0.1.18
 logcheck-1.3.20
 mime-construct-1.11_2
 mod_php72-7.2.24
 mysql57-client-5.7.27
 nagios-plugins-2.2.1_8,1
 nginx-1.16.1_4,2
 nrpe3-3.2.1
 p5-IPC-Signal-1.00_1
 p5-MIME-Types-2.17
 p5-Proc-WaitStat-1.00_1
 pam_ssh_agent_auth-0.10.3_1
 pcre-8.43_2
 perl5-5.30.0
 php72-7.2.24
 php72-json-7.2.24
 php72-pgsql-7.2.24
 pkg-1.12.0
 postfix-3.4.7_1,1
 postgresql11-client-11.5
 protobuf-3.9.2,1
 py27-ply-3.11
 py27-setuptools-41.4.0
 python-2.7_3,2
 python2-2_3
 python27-2.7.17
 readline-8.0.0
 sudo-1.8.29
 xtail-2.1
(57 rows)

samdrucker=#

Once more, with a function:

samdrucker=# SELECT * FROM PackagesOnHost('samdrucker.int.unixathome.org') ORDER BY 1;
               packagesonhost                
---------------------------------------------
 SamDruckerClientShell-0.0.0.1.20191023100_3
 anvil-0.0.17
 apache24-2.4.41
 apr-1.7.0.1.6.1
 bash-5.0.11
 bind-tools-9.14.7
 ca_root_nss-3.47
 curl-7.66.0
 cyrus-sasl-2.1.27
 db5-5.3.28_7
 expat-2.2.8
 gdbm-1.18.1_1
 gettext-runtime-0.20.1
 icu-65.1,1
 indexinfo-0.3.1
 jo-1.2
 joe-4.6,1
 json-c-0.13.1_1
 libargon2-20190702
 libedit-3.1.20190324,1
 libevent-2.1.11
 libffi-3.2.1_3
 libidn2-2.2.0
 liblockfile-1.16
 liblz4-1.9.2,1
 libnghttp2-1.39.2
 libunistring-0.9.10_1
 libxml2-2.9.9
 lockfile-progs-0.1.18
 logcheck-1.3.20
 mime-construct-1.11_2
 mod_php72-7.2.24
 mysql57-client-5.7.27
 nagios-plugins-2.2.1_8,1
 nginx-1.16.1_4,2
 nrpe3-3.2.1
 p5-IPC-Signal-1.00_1
 p5-MIME-Types-2.17
 p5-Proc-WaitStat-1.00_1
 pam_ssh_agent_auth-0.10.3_1
 pcre-8.43_2
 perl5-5.30.0
 php72-7.2.24
 php72-json-7.2.24
 php72-pgsql-7.2.24
 pkg-1.12.0
 postfix-3.4.7_1,1
 postgresql11-client-11.5
 protobuf-3.9.2,1
 py27-ply-3.11
 py27-setuptools-41.4.0
 python-2.7_3,2
 python2-2_3
 python27-2.7.17
 readline-8.0.0
 sudo-1.8.29
 xtail-2.1
(57 rows)

samdrucker=#
Clone this wiki locally