⚡️ v2.6.0 Enforce UTC datetimes, add primary index and autoincrement.

[bmeares]

v2.6.0

• Enforce a timezone-aware datetime axis by default.
  Pipes now enforce timezone-naive datetimes as UTC, even if the underlying column type is timezone-naive. To use datetime-naive datetime axes, you must explicitly set the dtype to datetime64[ns].

• Designate the index name primary for primary keys.
  Like the datetime index, the primary index is used for joins and will be created as the primary key in new tables.

  import meerschaum as mrsm
  
  pipe = mrsm.Pipe(
      'demo', 'primary_key',
      columns={'primary': 'pk'},
      instance='sql:local',
  )
  ### Raises a `UNIQUE` constraint failure:
  pipe.sync([
      {'pk': 1},
      {'pk': 1},
  ])

• Add autoincrement to Pipe.parameters
  Like upsert, you may designate an incremental integer primary key by setting autoincrement to True in the pipe parameters. Note that autoincrement will be True if you specify a primary index but do not specify a dtype or pass into the initial dataframe. This is only available for sql pipes.

  import meerschaum as mrsm
  
  pipe = mrsm.Pipe(
      'demo', 'autoincrement',
      columns={'primary': 'id'},
      autoincrement=True,
      instance='sql:local',
  )
  pipe.sync([
      {'color': 'red'},
      {'color': 'blue'},
  ])
  print(pipe.get_data())
  #    id color
  # 0   1   red
  # 1   2  blue
  
  pipe.sync([
      {'color': 'green', 'id': 1},
  ])
  print(pipe.get_data())
  #    id  color
  # 0   1  green
  # 1   2   blue

• Add option static to Pipe.parameters to disable schema modification.
  Set static to True in a pipe's parameters to prevent any modification of the column's data types.

  import meerschaum as mrsm
  
  pipe = mrsm.Pipe(
      'demo', 'static',
      columns={
          'primary': 'id',
          'datetime': 'id',
      },
      dtypes={
          'meta': 'json',
          'id': 'int',
      },
      parameters={
          'static': True,
          'upsert': True,
          'autoincrement': True,
      },
  )
  pipe.sync([{'id': 1, 'meta': {'foo': 'bar'}}])
  
  ### Abort syncing new columns when `static` is set. 
  pipe.sync([{'id': 1, 'color': 'blue'}])
  
  mrsm.pprint(pipe.get_columns_types())
  # {'id': 'BIGINT', 'meta': 'JSONB'}

• Add get_create_table_queries() to build from dtypes dictionaries.
  You may get a CREATE TABLE query from a dtypes dictionary (in addition to a SELECT query). The function meerschaum.utils.sql.get_create_table_query() now also accepts an arguments primary_key and autoincrement to designate a primary key column.

  from meerschaum.utils.sql import get_create_table_queries
  
  queries = get_create_table_queries(
      {
          "id": "int",
          "is_active": "bool",
          "customer_id": "uuid",
          "metadata": "json",
      },
      'new_table',
      'postgresql',
      primary_key="id",
      autoincrement=True,
  )
  print(queries[0])
  # CREATE TABLE "new_table" (
  #     "id" BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  #     "is_active" BOOLEAN,
  #     "customer_id" UUID,
  #     "metadata" JSONB
  # ) 

• Create a multi-column index for columns defined in Pipe.columns.
  To disable this behavior, set unique to None in Pipe.indices.

• Default to BIT for boolean columns in MSSQL.
  The previous workaround was to store bool columns as INT. This change now defaults to BIT when creating new tables. Boolean columns cannot be nullable for MSSQL.

• Improve file protection in edit config.
  Writing an invalid config file will now stop you before committing the changes. The previous behavior would lead to data loss.

• Catch exceptions when creating chunk labels.
  If a datetime bound cannot be determined for a chunk, return pd.NA.

---

This discussion was created from the release ⚡️ v2.6.0 Enforce UTC datetimes, add primary index and autoincrement..