Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^3: key value in text format

by The_Dj (Beadle)
on Nov 06, 2019 at 15:34 UTC ( [id://11108372]=note: print w/replies, xml ) Need Help??


in reply to Re^2: key value in text format
in thread key value in text format

I second SQlite. I use it a lot, and if your dataset is big(ish), you don't want to be parsing flat files for every lookup.

How you store this depends on just how many [ key1=value1, key2=value2] there are.
If there is a fixed list of 'key1','key2' and it's not too many: bite the bullet and add the columns.

Otherwise, you get to use a link (This is Database 101. search for 'database normalization')

each record should be
[id], field1, field2

then in a separate table
[link_id], key1, value1 [link_id], key2, value2

You can also have a 3rd table with
[link_id], source_file_name
or whatever other metadata you need to keep.

SQLite very kindly has a magic ROWID column. It wont' return it on SELECT *, but will on SELECT ROWID,*.
Of course you get to do two queries for each lookup: SELECT ROWID,* FROM main_data and then SELECT * FROM extra_data WHERE link_id = ?

HTH.

TO clarify: if your 'real' data is
name=bob, age=75, [ hair=balding, glasses=bifocal] name=john, age=20, [ sport=chess ]


Then you get:
Primary table:
1, bob, 45 2, john, 20


lookup table:
1,hair,balding 1,glasses,bifocal 2,sport,chess

Replies are listed 'Best First'.
Re^4: key value in text format
by pwagyi (Monk) on Nov 07, 2019 at 02:05 UTC

    The link method to handle key/value pair is a good idea. but I think I might just go with JSON (column) for all key/value pairs. What is the trade-off between the two approaches?

      Searchability.

      using a linked table, you can do
      SELECT * FROM main_data WHERE ROWID IN ( SELECT link_id FROM extra_data WHERE key1 = ? AND value1 LIKE ? )

      Consider also:
      do you want to put a storage method (JSON) inside a different storage method (SQL)?
        Modern databases understand JSON and can search inside the structure, see e.g. MariaDB or PostgreSQL.
        map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11108372]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (1)
As of 2024-04-25 01:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found