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

View post

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 • May 15 '20

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]
          }
        }
      }