Hi all! not exactly a question related to Sequel (but more about Sqlite). I'm asking here because I decided to use Sequel always I have to use a relational DB :)
Problem: I'd like to use a Sqlite3 database also as key/value storage (programming in Ruby). Of course Sqlite3 is not a "mandadatory" choice and I already saw, by example, thta Sequel, foresee Posgres HSTORE/JSON/JSONB extension, but let assume for a moment to use Sqlite: - both in as usual way (relational tables management with Sequel ORM) - and also to have a table just to store that key/value storage (call it nosql if you like), where "value" is a free format [Ruby hash](http://docs.ruby-lang.org/en/2.0.0/Hash.html). Imagine a sqlite3 table with two columns: chat_id | data ------------+------------------------------------------------------ 1276262 | { state: :add_item, list: ['2 pizze 4 Stagioni con peperoncino', '2 mezze minerali gassate] } 1276263 | { state: :new_address, address: '...', location: '..-' } 1276264 | { profile: '...', tastes: {...}, tags: [...] } ... | ... where `data` column must contain a free format hash, varying from record to record with differents hash fields composition. Let say also that I do not need to do any complex search inside the `data` column, but I only need to persist in the DB getting a record (load to RAM) and the putting back the record (dump to DB for persitence). **In the more performant way!**. Let consider the code: require 'sequel' # the great Sequel (https://github.com/jeremyevans/sequel) ORM require 'sqlite3' # for accessing Sqlite3 (https://www.sqlite.org/) database DB = Sequel.connect('sqlite://chat.db') DB.create_table :sessions do string :chat_id, primary_key: true blob :data # case 2, see below # or text :data, case 1, see below end sessions = DB[:sessions] hash = { state: :add_item, list: ['2 pizze 4 Stagioni con peperoncino', '2 mezze minerali gassate] } Now consider inserting some hash data in `sessions` table. Possible solutions in my mind to set the `data` column: 1. [JSON serialize](https://github.com/intridea/multi_json) to a `TEXT` type column ? sessions.insert(chat_id: id, data: Multi_json.dump(hash) 2. [Marshal serialize](http://ruby-doc.org/core-2.2.0/Marshal.html) in a `BLOB` type colum ? sessions.insert(chat_id: id, data: Sequel.blob(Marshal.dump(hash)) BTW: - I'm aware by the fact that Ruby Marshalling could be not portable data solution, but maybe more performant; isnt'it ? - With "session" of course I do not refer to any HTTP session in usual way, but to an application (a chat specifically) session. Any better idea to do this dump/load in a more smart way ? Welcome also any suggestion also using any alternative solution (a part using Sqlite) ? Please sorry for my possibly trivial question here. thanks fin advance for your patience. giorgio -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
