DEV Community

Pacharapol Withayasakpunt
Pacharapol Withayasakpunt

Posted on

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.

Top comments (6)

Collapse
 
dmfay profile image
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.

Collapse
 
patarapolw profile image
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]
          }
        }
      }
Collapse
 
1e4_ profile image
Ian

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

Collapse
 
patarapolw profile image
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]
          }
        }
      }
Collapse
 
patarapolw profile image
Pacharapol Withayasakpunt • Edited

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

  • This column is JSON serialized, or Date serialized, or boolean serialized.
Collapse
 
1e4_ profile image
Ian

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