fix: handle values according to their received type, not the column-defined type (fixes #137)

[rentalhost]

Since SQLite is flexible with types, we can't blindly trust the column-defined type. Instead, we now determine the display type based on the type of the received data.

Here's how it works:

• A string received type will be treated as TEXT.
• A bigint received type will be treated as INTEGER (since the BigNumberCell component handles bigint, which aligns with how INTEGER data is managed internally).
• A number received type will be treated as REAL (because the NumberCell component can handle number, which can represent both integer or float in JavaScript, so even if an INTEGER is treated as number, it won't be an issue).
• An object (Array) received type will be treated as BLOB.
• BLOB or NULL types are handled as generic types (managed by the GenericCell component).
• When there is no received type (e.g., when inserting a new value), we default to using the column type as before.

Additional improvements:

• useTableResultCellRenderer() has been refactored into a direct callback instead of a React hook since it no longer has hook dependencies (specifically, useDatabaseDriver()). As there was only one use of this hook, I moved the call directly to the final point of use instead of passing it down through component dependencies.

Additional notes:

• It would be important to test with other data sources. I only tested with SQLite and Turso.

[vercel[bot]]

@rentalhost is attempting to deploy a commit to the Mediaload Team on Vercel.

A member of the Team first needs to authorize it.

[invisal]

This approach has several short-coming

• We won't be able to edit cell with NULL value
• We won't be able to edit new inserted cell.
• If header is INTEGER and cell is TEXT. Let say cell value is "LibSQL". Then when user change it to "100" which valid integer, it should switch from TextCell to BigNumberCell.

I believe you are in the right track, just need a few twist:

if (
  header.dataType === TableColumnDataType.INTEGER &&
  databaseDriver.supportBigInt() &&
  isBigNumber(value)
) {
  // BigNumberCell
} else if ((
  header.dataType === TableColumnDataType.INTEGER ||
  header.dataType === TableColumnDataType.REAL
) && isNumber(value)) {
  // NumberCell
} else if (
  header.dataType === TableColumnDataType.TEXT &&
  isString(value)
) {
  // TextCell
} else {
  // GenericCell
}

function isBigNumber(value) {
  if (typeof value === 'bigint') return true;
  if (value === null) return true;
  if (value === undefined) return true;

  try {
    BigInt(value);
    return true;
  } catch {
    return false;
  }
}

function isNumber(value) {
  if (typeof value === 'number') return true;
  if (value === null) return true;
  if (value === undefined) return true;
  return Number.isFinite(value);
}

function isString(value) {
  if (typeof value === 'string') return true;
  if (value === null) return true;
  if (value === undefined) return true;
  return false;
}

[rentalhost]

Perfect!

I tried to compare as much as I could between the current version and the proposed one, always aiming to keep the current behavior intact while only modifying the issue with NULL where it shouldn’t appear. So here are a few points:

• We won't be able to edit cell with NULL value
  • The BLOB field can never be edited, so even if it's NULL, it also cannot be modified. However, my implementation overlooked this when the field was NULL for a non-BLOB field. This is fixed in the latest commit I just made. This happened because the determineCellType() function was checking for object (which is the typeof for null) and ended up treating it as BLOB instead of returning undefined and using the column type instead. I had initially used Array.isArray(), but I switched to a simpler check and incorrectly ignored the typeof for null.
• We won't be able to edit newly inserted cells.
  • This issue was caused by the previous one, so it’s also resolved now.
• If header is INTEGER and cell is TEXT. Let’s say the cell value is "LibSQL". Then when the user changes it to "100", which is a valid integer, it should switch from TextCell to BigNumberCell.
  • From what I could assess, and if I understood correctly, this is exactly the current behavior. This change happens as soon as the commit is made.

---

Essa tradução mantém a clareza e o contexto técnico do original.

[rentalhost]

Regarding the last point, just to be sure I understood correctly: you were making a statement, not asking a question, right? If it’s a question, SQLite will automatically convert a value that looks like a number to an INTEGER when it's received in an INTEGER field, rather than keeping it as the wrong type, TEXT, if that’s the case.

[invisal]

Regarding the last point, just to be sure I understood correctly: you were making a statement, not asking a question, right? If it’s a question, SQLite will automatically convert a value that looks like a number to an INTEGER when it's received in an INTEGER field, rather than keeping it as the wrong type, TEXT, if that’s the case.

I was not sure what was the sqlite behavior, so I was raising concern.

@rentalhost lets me test further. It looks good so far.

[rentalhost]

This is specified in the Type Affinity section, specifically in section 3.4.

Note that in the first INSERT example, even though all VALUES are sent as strings, they are converted to their affinity types according to the column type. So, "500.0" is kept as a string in a TEXT column but is converted to an integer for the INTEGER column.

In the third example, the test is done using 500 as an integer literal, so it is cast to a string in the TEXT column but remains an integer in the INTEGER column.