Olav Grønås Gjerde

profile image
Full stack system architect with passion for Unix, Java, Python and databases.
Twitter @olavgg
1 year ago

Grails GORM Criteria with sub query for has many collection

Today I solved something that I had to write about.

I have list of excursions, that can be added to a incident event. So one incident can have multiple excursions. As these excursions are being connected to incidents we only want to display excursions that is not yet connected to a incident.

So how to write this with using Grails Criteria?


List<Excursion> excursions = Excursion.withCriteria {
            
    sensor{
        eq("assetProject", assetProject)
    }
                        
    between("startDateTime", a, b)
    eq("isSuppressed", false)

    if( showOnlyExcursionsNotInIncident ){
                
        // The trick here is the subquery builder
        DetachedCriteria subQuery = DetachedCriteria.forClass(Incident, 'i')
            .with {
                createAlias("i.excursions", "e2")
                setProjection Projections.property('e2.id')
                add Restrictions.conjunction()
                    .add(
                        Restrictions.eqProperty(
                            'e2.id',
                            'this.id'
                        )
                   )
            }
                        
        add Subqueries.notExists(subQuery)
    }
        
    order "startDateTime", "desc"
}

Which will generate the following SQL

select
        ....
    from
        excursion this_ 
    inner join
        sensor sensor_ali1_ 
            on this_.sensor_id=sensor_ali1_.id 
    where
        (
            sensor_ali1_.asset_project_id=?
        ) 
        and this_.start_date_time between ? and ? 
        and this_.is_suppressed=? 
        and not exists (
            select
                ee2x1_.id as y0_ 
            from
                incident i_ 
            inner join
                incident_excursion excursione3_ 
                    on i_.id=excursione3_.incident_excursion_id 
            inner join
                excursion ee2x1_ 
                    on excursione3_.excursion_id=ee2x1_.id 
            where
                (
                    ee2x1_.id=this_.id
                )
        ) 
    order by
        this_.start_date_time desc

Learning to do this stuff with criterias is awesome, as it is much easier to do custom filtering with criterias than using raw SQL. It can also be easier to read.

1 year ago

Grails GORM HQL Eager fetch

Sometimes you want to select associated objects using a single select query. Also called eager fetching.

Todo this with HQL in Grails is a simple as:

List<Sensor> sensors = Sensor.findAll("FROM Sensor s INNER JOIN FETCH s.equipmentType")
1 year ago

Lighter select queries with Grails Criteria and DTO's

If you have a huge domain class like this:

class Sensor {

    Long id
    SensorType sensorType
    String parentTag
    String parentTagDescription
    EquipmentType equipmentType
    String runningSignalTimeserieName
    Boolean isPumpActive
    AssetProject assetProject
    SensorUnit unit
    Segment segment
    String segmentDescription
    String tag
    String description
    String deactivatedDescription
    String note
    String pAndID
    Float lValue
    Float llValue
    Float lllValue
    Float hValue
    Float hhValue
    Float hhhValue
    SensorStatus status
    Boolean deactivated = false
    LocalDateTime dateCreated
    LocalDateTime lastUpdated
    Person createdBy
    Person lastUpdatedBy
    SensorPumpGroup sensorPumpGroup
    String flowTimeserieName
    String flowTemperatureTimeserieName
    String flowPressureTimeserieName
    Double pipeArea
    Double flowDensity
    String flowDescription
    String velocityTimeserieName
    Long lastTimestampForVelocityTimeserie

}

You will always select all fields for your queries if you do not use do not use DTO's (Data Transfer Object). GORM has excellent support for DTO's with its data services. But its not mentioned in the documentation for how you can use DTO's with criterias.

Luckily it is really easy, first create a simple POJO/POGO

class SensorTag {
    long id
    String tag
}

Then write the criteria as this:

List<SensorTag> sensors = Sensor.withCriteria {
    resultTransformer(Transformers.aliasToBean(SensorTag))
    projections {
        property("id", "id")
        property("tag", "tag")
    }
    eq('deactivated', false)
} as List<SensorTag>
1 year ago

Java NIO file descriptor leak issue with Files.list() method

This blog you're reading, is built on Micronaut. And each blog post is stored as a plain text file instead of a database. I just wanted something simple, where I could also easily SSH into the server for modifying and adding new content. However I recently stubled upon a file descriptor leak. I just couldn't figure out how, as everything was wrapped in Java's try with resource.

Anyway using lsof

sudo lsof +D /opt/olavblog

I could see all files that had a file descriptor, and I could see many duplicates. This was because something wasn't properly closed.

Finding the bug ment that I had to use the step by step debugger and check what happened one line after another. In the end I found the faulty line in my code:

Stream<Path> files = Files.list(folderPath)

There is a note in the method documentation mentioning this

@apiNote

 * This method must be used within a try-with-resources statement or similar
 * control structure to ensure that the stream's open directory is closed
 * promptly after the stream's operations have completed.

So the right thing todo is to wrap this in a try with resources statement

try(Stream<Path> files = Files.list(folderPath)){
    files.forEach(filePath -> {
         .......
    });
}
1 year ago

Restart IPMI BMC LOM, without power reset.

Once in a while IPMI/BMC/LOM may freeze and the only way to get it back is to pull the power cord. This will interrupt running services. If you are lucky though, you can use ipmitool to reset your BMC

ipmitool mc reset cold
2 years ago

Install and configure pgAdmin4 for FreeBSD with UWSGI and Nginx

This guide will help you install pgAdmin4 on your FreeBSD machine. What is pgAdmin? It is a free open source graphical management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. It has a powerful query tool with colour syntax highlighting and graphical query plan display. For people that is not familiar with the Linux shell and the psql client, pgAdmin4 is an excellent tool for interacting with PostgreSQL.

(Screenshot of pgAdmin4)

FreeBSD Setup

Update ports

$ sudo portsnap fetch && portsnap extract

Install necessary packages (you can omit BATCH if you want to configure installations)

$ sudo make -C /usr/ports/lang/python36 install clean BATCH=yes
$ sudo make -C /usr/ports/devel/py-setuptools install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/devel/py-virtualenv install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/databases/py-sqlite3 install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/databases/py-psycopg2 install clean FLAVOR=py36 BATCH=yes
$ sudo make -C /usr/ports/www/uwsgi install clean BATCH=yes

We use Python 3.6 as that is currently working with pgAdmin4 version 4.6.

Setup Python Environment

Create pgadmin app folder and data folder

$ sudo mkdir -p /opt/pgadmin/data

Create virtual python environment

$ sudo virtualenv --python=/usr/local/bin/python3.6 /opt/pgadmin/pgadm_env

Activate virtual env (make sure you are using bash, not sh)

$ source /opt/pgadmin/pgadm_env/bin/activate

Install and configure pgAdmin4

Check the latest release of pgAdmin4 here https://ftp.postgresql.org/pub/pgadmin/pgadmin4/

(pgadm_env)$ sudo pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.6/pip/pgadmin4-4.6-py2.py3-none-any.whl

We also need a local uwsgi binary

(pgadm_env)$ sudo pip install uwsgi

Create local pgadmin config file

$ sudo vim /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4/config_local.py

Add following content to config_local.py

import os
DATA_DIR = os.path.realpath(os.path.expanduser(u'/opt/pgadmin/data'))
LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')
SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')
SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')
STORAGE_DIR = os.path.join(DATA_DIR, 'storage')

Create PGAdmin superuser

(pgadm_env)$ sudo python /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4/setup.py
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: myemail@example.org
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Create pgadmin user

$ sudo pw useradd pgadmin

Make user pgadmin the owner of the pgadmin installation

$ sudo chown -R pgadmin:pgadmin /opt/pgadmin

Configure UWSGI

This is the most annoying and probably hardest part. Hopefully this guide will help you through this.

First we want to use UWSGI Emperor mode, it makes it possible for us to to host multiple python applications through UWSGI. Even with different python version.

Create Vassals folder for UWSGI

$ sudo mkdir -p /usr/local/etc/uwsgi/vassals

Create pgadmin4.ini

$ sudo vim /usr/local/etc/uwsgi/vassals/pgadmin4.ini
[uwsgi]
uid = pgadmin
gid = pgadmin
socket = /tmp/pgadmin4.sock
chmod-socket = 666
logto = /var/log/uwsgi.log
chdir = /opt/pgadmin/pgadm_env/lib/python3.6/site-packages/pgadmin4
wsgi-file = pgAdmin4.wsgi
virtualenv = /opt/pgadmin/pgadm_env
unprivileged-binary-patch = /opt/pgadmin/pgadm_env/bin/uwsgi
threads = 10

Edit /etc/rc.conf

uwsgi_enable="YES"
uwsgi_flags="--emperor /usr/local/etc/uwsgi/vassals"

Now you can start UWSGI

$ sudo service uwsgi start

You still need to configure Nginx though

Configure Nginx

Create a new virtual host file for nginx that looks like tihs


upstream pgadmin_upstream {
  server unix:/tmp/pgadmin4.sock;
}

server {
      listen 80;
      server_name pgadmin.example.org;
      access_log off;
      error_log off;
      ## redirect http to https ##
      return 301 https://$server_name$request_uri;
}

# HTTPS server
server {
  listen 443 ssl http2;
  server_name pgadmin.example.org;

  ssl_certificate      /path/to/my/ssl/server.crt;
  ssl_certificate_key   /path/to/my/ssl//server.key;
  ssl_session_timeout  5m;

  error_page   500 502 503 504  /50x.html;
    # Only allow local network
  allow 192.168.1.0/24; 
  deny all;
  
  ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
  ssl_ciphers EECDH AES128:RSA AES128:EECDH AES256:RSA AES256:EECDH 3DES:RSA 3DES:EECDH RC4:RSA RC4:!MD5;
  ssl_prefer_server_ciphers on; 

  location / {
    uwsgi_pass pgadmin_upstream;
    include uwsgi_params;
    uwsgi_modifier1 30;
  }
}
2 years ago

Default Sequence Generator Strategy for Grails 3 and Grails 4

When using PostgreSQL together with Grails, one thing that has annoyed me when creating domain classes is that I have to manually add a line for telling hibernate how it should create, name and use the sequences.

Finally Hibernate now has a new SequenceStyleGenerator this solves this:

Create your application.groovy file, application.yml will not work and write this:

grails.gorm.default.mapping = {
    id (
            generator: 'org.hibernate.id.enhanced.SequenceStyleGenerator',
            params: [prefer_sequence_per_entity: true]
    )
}
2 years ago

Secure cookie flag for Grails 3

Add this to your application.yml

server:
    session:
        cookie:
            domain: adminpanel-test-boost.ai
            http-only: true
            path: /
            secure: true
6 years ago

Home/Small Office Quiet 10Gbps Switch with FreeBSD 10.2-RELEASE

Today you can get 10Gbps network cards for less than $20 in the US. There are two popular models, Mellanox ConnectX-2 EN and Chelsio S310E-CR.

Switches are still expensive though. Even switches with only four 10G ports, and they may be noisy. Luckily you can grab two dual port Mellanox MNPH29D-XTR ConnectX-2 EN for around $150 and create your own “switch” easily with FreeBSD.

You need a motherboard with two or more PCIe slots(8x or 16x slot sizes). If you want a 6 port switch you need three PCIe slots, unless you get a network card like Chelsio T440-CR that has 4 ports.

FreeBSD 10.2-RELEASE ships with the Mellanox drivers, but they’re not ready to use immediately. You will need to compile and install the drivers first.

As root:

cd /usr/src/sys/modules/mlxen && make && make install && kldload mlxen

Now you are ready to create a network bridge that will act as a switch. First step is to list your device names:

ifconfig -l
mlxen0 mlxen1 mlxen2 mlxen3

Second step is to create the bridge adapter:

ifconfig bridge create

Add the ports to the bridge:

ifconfig bridge0 addm mlxen0 addm mlxen1 addm mlxen2 addm mlxen3

Make sure links are up/enabled:

ifconfig mlxen0 up
ifconifg mlxen1 up
ifconfig mlxen2 up
ifconfig mlxen3 up

Finally set the ip address for the bridge adapter:

ifconfig bridge0 inet 192.168.0.10/24

That is all. You now have a basic software based switch, connect your other systems, give them a proper ip-address and see them talk together just fine. However, wait! A bridge adapter should be much slower than a traditional switch? Yes, that is true, but for only 4-6 ports, it should not be that significant.

So I had to benchmark this, four servers with Ubuntu Linux, two of them as iperf servers connect to one dual port NIC (mlxen0 & mlxen1) and two as iperf clients connected to the other NIC (mlxen2 & mlxen3). I ran all of them at the same time and made sure all of the traffic had to go through the PCIe bus for five minutes.

The result was an average speed of 17GB/s, 8.5 GB/s each, with no tuning and the default MTU set to 1500. That should be plenty for everyone doing 10Gbps on small scale. If you need more performance, you have plenty of room left by spending endless nights of reading about network tuning in FreeBSD :-)