HTTP tracker phần 4: Sử dụng RDBMS PostgreSQL lưu trữ dữ liệu

docker pull postgres
docker run --name postgres-docker -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres

CREATE TABLE raw_data (
                          id serial PRIMARY KEY,
                          event_time timestamp,
                          device varchar(20),
                          browser varchar(20),
                          result varchar(20),
                          message varchar(20),
                          duration integer,
                          account_id integer
);

CREATE TABLE fact_tracking_data (
    time timestamp,
    device integer,
    browser integer,
    success boolean,
    message integer,
    total_duration bigint,
    event_count integer
);

CREATE TABLE message_dim (
    id serial PRIMARY KEY,
    message varchar(20)
);
insert into message_dim (message) values ('OK'), ('Out_of_Slot'), ('Unknown');

CREATE TABLE device_dim (
    id serial PRIMARY KEY,
    device_name varchar(20)
);
insert into device_dim (device_name) values ('Mobile'), ('Desktop'), ('Unknown');

CREATE TABLE browser_dim (
    id serial PRIMARY KEY,
    browser_name varchar(20)
);
insert into browser_dim (browser_name) values ('Chrome'), ('Firefox'), ('Microsoft Edge'), ('Unknown');

dependencies {
    ...
    compile group: 'io.vertx', name: 'vertx-jdbc-client', version: "${vertxVersion}"
    implementation group: 'io.agroal', name: 'agroal-pool', version: '1.0'
    implementation group: 'org.postgresql', name: 'postgresql', version: '42.5.1'
}

public class MainStarter {
  ...
  //Need to use jdbcPool, so we initialize variable here
  JDBCPool jdbcPool = null;
  public void setJdbcPool(JDBCPool jdbcPool) {
    this.jdbcPool = jdbcPool;
  }
  //We also need to format timestamp when inserting to database
  DateFormat sqlF = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS");
  
  ...
  
  private void saveToPostgres(Date receiveTime, JsonObject raw) {
      jdbcPool
            .preparedQuery("INSERT INTO raw_data(event_time, device, browser, result, message, duration, account_id) " +
                  "VALUES (?, ?, ?, ?, ?, ?, ?)")
            .execute(Tuple.of(sqlF.format(receiveTime), raw.getString("device"), raw.getString("browser"),
                  raw.getString("result"), raw.getString("message"), raw.getInteger("duration"),
                  raw.getInteger("account_id")))
            .onSuccess(rows -> {
               Row lastInsertId = rows.property(JDBCPool.GENERATED_KEYS);
               System.out.println("New ID is " + lastInsertId.getInteger(0));
            })
            .onFailure(cause -> {
               cause.printStackTrace();
               System.out.println("Failure: " + cause.getMessage());
            });
   }

public void handleRequest(RoutingContext context) {
  ...
  try {
    ...
    this.saveToPostgres(receiveTime, dataBody);
  } catch (Exception e) {
     e.printStackTrace();
  }
}

public static void main(String[] args) {
  ...
  //Create Postgres pooled client
  JDBCPool pool = JDBCPool.pool(
        vertx,
        // configure the connection
        new JDBCConnectOptions()
              .setJdbcUrl("jdbc:postgresql://localhost:5432/postgres")
              .setUser("postgres")
              .setPassword("password"),
        // configure the pool
        new PoolOptions()
              .setMaxSize(4)
              .setName("postgres-pool")
  );
  handlerObj.setJdbcPool(pool);
  ...
}

curl --location --request POST 'http://localhost:8080/accept_tracking' \
--header 'Content-Type: application/json' \
--data-raw '{
  "device": "mobile",
  "browser": "Chrome",
  "result": "success",
  "message": "OK",
  "duration": 3432,
  "accountId": null
}'