Updating a table with PK and direct data assignment throws an error

[tevanburen]

For tables with primary key constraints that had their data set using example_db.tables.example_table.data = []; , running an update statement is throwing an error for me. I'll attach pseudo-code below, and here's a link to an example on jsfiddle: https://jsfiddle.net/0wmhx149/15/

Updating data with no pk works fine:

// pseudo-code
a = new alasql.Database();
a.exec("create table with no pk");
a.tables.table.data = [ some data ] ;
a.exec("update table");
// update runs correctly

Updating data with pk works fine if using insert statements:

// pseudo-code
a = new alasql.Database();
a.exec("create table with no pk");
a.exec("insert some data");
a.exec("update table");
// update runs correctly

Updating data with pk and direct assignment fails

// pseudo-code
a = new alasql.Database();
a.exec("create table with pk");
a.tables.table.data = [ some data ] ;
a.exec("update table");
// update throws an error

The error is Something wrong with index on table

Two obvious workarounds are

1. Don't use PKs
2. Don't directly assign data

[IrwantoCia]

Hello @mathiasrw , I did a little debugging on this issue.

While debugging, I noticed the following behavior when we assign data directly:

It replaces the entire data in the table variable.
It does not assign the uniqs attribute, which is required when performing table.update.
Below is an example of the table data when using exec followed by directly assigning data.

  data: [ { example_id: 2, example_str: 'hello' } ],
  columns: [
    {
      columnid: 'example_id',
      dbtypeid: 'INT',
      dbsize: undefined,
      dbprecision: undefined,
      notnull: true,
      identity: undefined
    },
    {
      columnid: 'example_str',
      dbtypeid: 'VARCHAR',
      dbsize: 15,
      dbprecision: undefined,
      notnull: true,
      identity: undefined
    }
  ],
  xcolumns: {
    example_id: {
      columnid: 'example_id',
      dbtypeid: 'INT',
      dbsize: undefined,
      dbprecision: undefined,
      notnull: true,
      identity: undefined
    },
    example_str: {
      columnid: 'example_str',
      dbtypeid: 'VARCHAR',
      dbsize: 15,
      dbprecision: undefined,
      notnull: true,
      identity: undefined
    }
  },
  inddefs: {},
  indices: {},
  uniqs: { '71311067': { '1': [Object] } },
  uniqdefs: {},
  identities: {},
  checks: [],
  checkfns: [],
  beforeinsert: {},
  afterinsert: {},
  insteadofinsert: {},
  beforedelete: {},
  afterdelete: {},
  insteadofdelete: {},
  beforeupdate: {},
  afterupdate: {},
  insteadofupdate: {},
  pk: {
    columns: [ 'example_id' ],
    onrightfns: 'r["example_id"]',
    onrightfn: [Function: anonymous],
    hh: 71311067,
    pkaddr: 2
  },
  defaultfns: '',
  onupdatefns: '',
  insert: [Function (anonymous)],
  delete: [Function (anonymous)],
  deleteall: [Function (anonymous)],
  update: [Function (anonymous)],
  dirty: true
}

As we can see example_id 1 is inserted into uniqs and data, but after assign table.data, the data got replaced and the uniqs is not updated.