smartclickhouse/ts/smartclickhouse.classes.timedatatable.ts

import * as plugins from './smartclickhouse.plugins';
import { SmartClickHouseDb } from './smartclickhouse.classes.smartclickhouse';

export type TClickhouseColumnDataType = 'String' | "DateTime64(3, 'Europe/Berlin')" | 'Float64';
export interface IColumnInfo {
  database: string;
  table: string;
  name: string;
  type: TClickhouseColumnDataType;
  position: string;
  default_kind: string;
  default_expression: string;
  data_compressed_bytes: string;
  data_uncompressed_bytes: string;
  marks_bytes: string;
  comment: string;
  is_in_partition_key: 0 | 1;
  is_in_sorting_key: 0 | 1;
  is_in_primary_key: 0 | 1;
  is_in_sampling_key: 0 | 1;
  compression_codec: string;
  character_octet_length: null;
  numeric_precision: null;
  numeric_precision_radix: null;
  numeric_scale: null;
  datetime_precision: '3';
}

export class TimeDataTable {
  public static async getTable(smartClickHouseDbRefArg: SmartClickHouseDb, tableNameArg: string) {
    const newTable = new TimeDataTable(smartClickHouseDbRefArg, tableNameArg);

    // create table in clickhouse
    await smartClickHouseDbRefArg.clickhouseClient
      .queryPromise(`CREATE TABLE IF NOT EXISTS ${newTable.tableName} (
      timestamp DateTime64(3, 'Europe/Berlin'),
      message String
    ) ENGINE=MergeTree() ORDER BY timestamp`);

    await newTable.updateColumns();
    console.log(`=======================`)
    console.log(
      `table with name "${newTable.tableName}" in databse ${newTable.smartClickHouseDbRef.options.database} has the following columns:`
    );
    for (const column of newTable.columns) {
      console.log(`>> ${column.name}: ${column.type}`);
    }
    console.log('^^^^^^^^^^^^^^\n');

    return newTable;
  }

  // INSTANCE
  public smartClickHouseDbRef: SmartClickHouseDb;
  public tableName: string;

  constructor(smartClickHouseDbRefArg: SmartClickHouseDb, tableNameArg: string) {
    this.smartClickHouseDbRef = smartClickHouseDbRefArg;
    this.tableName = tableNameArg;
  }

  public columns: IColumnInfo[] = [];
  public seenPaths: { pathName: string; type: TClickhouseColumnDataType }[] = [];

  /**
   * updates the columns
   */
  public async updateColumns() {
    this.columns = await this.smartClickHouseDbRef.clickhouseClient.queryPromise(`
      SELECT * FROM system.columns
      WHERE database LIKE '${this.smartClickHouseDbRef.options.database}'
      AND table LIKE '${this.tableName}'
    `);
    return this.columns;
  }

  /**
   * stores a json and tries to map it to the nested syntax
   */
  public async addData(dataArg: any) {
    // the storageJson
    let storageJson: { [key: string]: any } = {};
    
    // helper stuff
    const typeConversion: {[key: string]: TClickhouseColumnDataType} = {
      string: 'String',
      number: 'Float64',
    };
    const getClickhouseTypeForValue = (valueArg: any) => {
      return typeConversion[(typeof valueArg) as string];
    }
    const checkPath = async (pathArg: string, typeArg: TClickhouseColumnDataType) => {
      let columnFound = false;
      for (const column of this.columns) {
        if (pathArg === column.name) {
          columnFound = true;
          break;
        }
      }
      if (!columnFound) {
        await this.smartClickHouseDbRef.clickhouseClient.queryPromise(`
          ALTER TABLE ${this.tableName} ADD COLUMN ${pathArg} ${typeArg} FIRST
        `);
        await this.updateColumns();
      }
    };

    // key checking
    const flatDataArg = plugins.smartobject.toFlatObject(dataArg);
    for (const key of Object.keys(flatDataArg)) {
      const value = flatDataArg[key];
      if (key === 'timestamp' && typeof value !== 'number') {
        throw new Error('timestamp must be of type number');
      } else if (key === 'timestamp') {
        storageJson.timestamp = flatDataArg[key];
        continue;
      }
      // lets deal with the rest
      const clickhouseType = getClickhouseTypeForValue(value);
      await checkPath(key, clickhouseType);
      storageJson[key] = value;
    }

    const result = await this.smartClickHouseDbRef.clickhouseClient.insertPromise(this.tableName, [
      storageJson,
    ]);
    return result;
  }
}