Postgres SEQUENCEs for generating po_line_number suffixes

Requirements - https://folio-org.atlassian.net/browse/MODORDSTOR-34

In order to proceed with this story we need to come up with a solution on how to generate unique sequential numbers for PO Lines.
Namely, PO lines might be added in parallel by several users and we may have several instances of the module running. So implementing it without a mechanism to guarantee uniqueness in these conditions, there is a risk to end up with duplicate numbers. The things are further complicated by the fact that we need to keep separate sequence per Order.

Investigation

Postgres provides the SEQUENCE command, which can be used to provide a unique sequential number series akin to MySQL's AUTOINCREMENT feature. Unlike AUTOINCREMENT, which is applied directly to table columns, a sequence is created outside of a table.

Example:

> CREATE SEQUENCE phonebook_id_seq;
> CREATE TABLE phonebook(id INTEGER DEFAULT NEXTVAL('phonebook_id_seq'), phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32));

This example first creates a sequence, then uses that sequence to define the behaviour of the id column in the phonebook table. This is typically how Postgres users recreate MySQL's AUTOINCREMENT functionality to generate unique object ids. Since sequences exist as separate database entities from tables, they can be used independent of tables as well.

Example:

> CREATE SEQUENCE ponumber_seq;

> SELECT * FROM NEXTVAL('ponumber_seq');

> nextval

> ---------

> 2

Here the sequence is called directly as part of a query, with its current value being incremented by NEXTVAL() before it is returned. When called this way, the result is guaranteed to be unique.

In order to verify that SEQUENCE is supported in the FOLIO environment, the following code was run from within a unit test:

PostgresClient dbClient = PostgresClient.getInstance(vertx, "test_tenant");
CompletableFuture<ResultSet> selectCompleted = new CompletableFuture<>();
String sql = "CREATE SEQUENCE ponumber_seq; SELECT * FROM SETVAL('ponumber_seq',13);SELECT * FROM NEXTVAL('ponumber_seq');";
dbClient.select(sql, result -> {
  if(result.succeeded()) {
    logger.info("--- mod-vendors-test: Result success!!! ");
    selectCompleted.complete(result.result());
  }
  else {
    logger.info("--- mod-vendors-test: Result failure!!!");
    selectCompleted.completeExceptionally(result.cause());
  }
});
ResultSet results = selectCompleted.get(5, TimeUnit.SECONDS);
JsonObject mismatchedRowCount = results.toJson();
logger.info("--- mod-vendors-test: Finished SQL call ... " + mismatchedRowCount);

The output:

INFO: --- mod-vendors-test: Finished SQL call ... {"columnNames":["nextval"],"numColumns":1,"numRows":1,"results":[[14]],"rows":[{"nextval":14}]}

Reference - Postgresql Sequences

https://www.ntchosting.com/encyclopedia/databases/postgresql/sequence/

Solution Proposal

When a Purchase Order is created, create a corresponding sequence in the DB using the po_number as the name of the sequence
1. ```
CREATE SEQUENCE <po_number>_seq MINVALUE 1 MAXVALUE 999;
```
When a PO Line is created, call the sequence with the corresponding po_number to get a unique suffix value
1. ```
SELECT * FROM NEXTVAL('<po_number>_seq');
```
When a Purchase Order has a status change to "OPEN", delete the sequence from the DB
1. ```
DROP SEQUENCE <po_number>_seq;
```