Addressing a database as a multi-level associative array -

i'm writing web game, it's convenient think complete game state single arbitrary-level hash.

a couple examples of game state being updated:

// defining mission card issued player $game['consumables']['missions']['id04536']['name'] = "build galleon blackbeard"; $game['consumables']['missions']['id04536']['requirements']['lumber'] = 20; $game['consumables']['missions']['id04536']['requirements']['iron'] = 10; $game['consumables']['missions']['id04536']['rewards']['blackbeard_standing'] = 5;  // when player turns in mission card requirements $game['players']['id3214']['blackbeard_standing'] += 5; 

this web game, storing information in database makes sense. need features of database: game state can accessed multiple browsers @ same time, it's non-volatile , easy up, etc.

essentially, want syntax easy reading-to/writing-from associative array of arbitrary depth. need functionality of dealing associative array: not simple reads , writes, ability run foreach loops against it, , on. , need effect perform reads/writes database, not volatile memory.

i'm fond of raw ruby, if there's specific language or framework gives me 1 feature, make rest of project easy enough worth using.

any language, framework? how python+sqlalchemy+postgresql

• python because can create new types behave world regular dicts
• postgres because has 2 particularly interesting types uncommon in other sql databases, we'll in moment.
• sqlalchemy because can of dirty work of dealing rdbms concisely.

using sql database awkward, because normal 'key' want in table has fixed set of columns, ideally, you'd need single column whole "path" nested mapping.

an more irritating problem seem want store range of different types @ leaves. not ideal.

fortunately, postgres can out both issues, using text[] first, can have single column every entry can concisely represent whole path, way down tree. second, can use json, sounds, permitting arbitrary json encodable types, include both strings , numbers in code example.

because lazy, i'll use sqlalchemy most of work. first, need table using above types:

from sqlalchemy import * sqlalchemy.orm import * sqlalchemy.ext.declarative import declarative_base sqlalchemy.dialects import postgres pg  base = declarative_base()  class assocstorage(base):     __tablename__ = 'assoc_storage'     key = column(pg.array(pg.text, as_tuple=true), primary_key=true)     value = column(pg.json) 

that give relational version of single entry in nested mapping. we're of way there already:

>>> engine = create_engine('postgres:///database') >>> base.metadata.create_all(engine)  ## beware, extreem lazyness >>> session = session(bind=engine)    ## unusually lazy, real applications should use `sessionmaker` >>> session.add(assocstorage(key=('foo','bar'), value=5)) >>> session.commit() >>> x = session.query(assocstorage).get((('foo', 'bar'),)) >>> x.key (u'foo', u'bar') >>> x.value 5 

okay, not bad, little more annoying use, mentioned earlier, python type system compliant enough make normal dict, need give class implements proper protocol:

import collections class persistentdictview(collections.mutablemapping):     # "production grade" version should implement these:     __delitem__ = __iter__ = __len__ = notimplemented      def __init__(self, session):         self.session = session      def __getitem__(self, key):         return self.session.query(assocstorage).get((key, )).value      def __setitem__(self, key, value):         existing_item = self.session.query(assocstorage).get((key, ))         if existing_item none:             existing_item = assocstorage(key=key)             self.session.add(existing_item)         existing_item.value = value 

this different code you've posted, have x[a][b][c], requires x[a, b, c].

>>> d = persistentdictview(session) >>> d['foo', 'bar', 'baz'] = 5 >>> d['foo', 'bar', 'baz'] 5 >>> d['foo', 'bar', 'baz'] += 5 >>> session.commit() >>> d['foo', 'bar', 'baz'] 10 

if need nesting reason, behavior bit more work, require little more effort. additionally, totally punts on transaction management, notice explicit session.commit() above.