What is the best way store column metadata in SQLite (like, is JSON or is Date)

Like telling the user or the browser that this column is JSON, not mere TEXT; or is Date, not mere INTEGER.

I can create a special table called __meta__, but I am not sure that is the best idea.

Comments

[Dian Fay]

If you want to add just the semantic context with a minimum of further complications, Hungarian-style naming conventions are a slightly verbose but low-impact solution: manifest_json, date_created, is_active.

[Pacharapol Withayasakpunt]

I ended up with date_, set_, dict_ prefixes.

is_ is also a nice prefix, but I think json_ prefixes in SQLite should avoided due to JSON1 extension functions also starts with json_.

      dict_media    TEXT NOT NULL DEFAULT '{}',
      set_cardId    TEXT NOT NULL DEFAULT '{}', -- I wonder if this can be indexed
      date_nextReview DATETIME NOT NULL,

and, how to use

    JOIN deck   d   ON json_extract(d.set_cardId, '$.'||c.uid) = 1

      for (const k of Object.keys(r)) {
        const [k1, k2] = k.split('_')
        if (k2 && r[k]) {
          if (k1 === 'dict') {
            r[k2] = JSON.parse(r[k])
            delete r[k]
          } else if (k1 === 'set') {
            r[k2] = Object.keys(JSON.parse(r[k]))
            delete r[k]
          } else if (k1 === 'date') {
            r[k2] = new Date(r[k])
            delete r[k]
          }
        }
      }

[Ian]

Got any examples of meta data you want to store? What's wrong with having another table?

[Pacharapol Withayasakpunt]

I ended up with date_, set_, dict_ prefixes.

      dict_media    TEXT NOT NULL DEFAULT '{}',
      set_cardId    TEXT NOT NULL DEFAULT '{}', -- I wonder if this can be indexed
      date_nextReview DATETIME NOT NULL,

and, how to use

    JOIN deck   d   ON json_extract(d.set_cardId, '$.'||c.uid) = 1

      for (const k of Object.keys(r)) {
        const [k1, k2] = k.split('_')
        if (k2 && r[k]) {
          if (k1 === 'dict') {
            r[k2] = JSON.parse(r[k])
            delete r[k]
          } else if (k1 === 'set') {
            r[k2] = Object.keys(JSON.parse(r[k]))
            delete r[k]
          } else if (k1 === 'date') {
            r[k2] = new Date(r[k])
            delete r[k]
          }
        }
      }

[Pacharapol Withayasakpunt]

Nothing wrong, but I want to know the standard of notifying that,

• This column is JSON serialized, or Date serialized, or boolean serialized.

[Ian]

Really depends on the data. I've not had a use case yet for JSON type