On Wed, Jul 15, 2015 at 10:03 AM, Roger Binns <rogerb at rogerbinns.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 07/15/2015 08:22 AM, Sergej Jure?ko wrote: > > What do you guys think? Is it stupid, could it be improved? > > I recommend looking at Mongodb and Postgres first to see how they do > queries. It would be better to be compatible with them where > practical, rather than being gratuitously different. > > (mentions postresql methods and MongoDB) http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf http://www.postgresql.org/docs/9.1/static/hstore.html My question is, how do you have a intelligent (orderly, structured) query of unstructured data? I can imagine ways to store and retrieve such things, but how do you query for 'give me the count of what I don't know is available' ? http://docs.mongodb.org/manual/reference/sql-comparison/ - good comparison of sorts... but then when it gets to how it get implemented... they're relying on external library isntead of 'SQL' or really any sort of query language. (day1-2 and day 3-4 links...) https://www.mongodb.com/blog/post/mongodb-vs-sql-day-1-2?jmp=docs&_ga=1.94603548.1409473473.1436992997 https://www.mongodb.com/blog/post/mongodb-vs-sql-day-3-5?jmp=docs&_ga=1.94603548.1409473473.1436992997 >From an application standpoint, this doesn't resemble my interface to using SQL and getting results back into code; Java looks horrible. But then again my library was built as an abstraction around ODBC and cleverly hides details so applications can actually just do work.... http://api.mongodb.org/c/current/executing-command.html (the C interface to mongodb to execute a command) At least the hstore is a extension to the query language, and not a library. ----------- I guess I've been looking at this strictly from a SQL standpoint, rather than using sqlite3_* interfaces to do the work... since My library just has 3 commands... GetSQLConnection, DoSQLCommand, (okay and a set... GetSQLRecord, GetSQLNext, EndSQL) then using these, I can defiantly see how MonogoDB can be built as a layer on top of that using a hash storage technique... table object( objid int, parent_objid int, type int, name_id int, value varchar ) table object_names( name_id int, name varchar unique ) if they're grouped into some document also can add table doc( docid, name, root_objid ) where type is 0 - array, and all object with this objid as it's parent_id is in this array, (ignore this_object's value; query of sub-data will ignore name... well actually the name should be the index into the array so the order can be preserved) 1 - object, (NULL value) 2 - data (use value as int, float, bool or string) I would also build a name dictionary and store a name_id instead of the literal name since typically Int comparisons are faster... at least in a well(long) named schema it would save a few bytes overall In the day 1-2 MongoDB link he uses a 'user' master object with 'contact_number' detail object... select_contact() { select objid from object where name = 'user', and type = 1/*object*/ and parent=0 for each object select * from object where name='contact_number' and type = 1/*object*/ and parent=objid /* do something to store in variant result data type */ } viola JSON query in SQL via a library; that's certainly what the internals of MongoDB result in and again maybe RECURSIVE and WITH operators in sqlite's SQL might be useful for simplifying the looping // join user.contact.numbers .. users that have the same numbers join_contacts() { select objid from object where name='user' and type=1 and parent=0 for each objid select objid as cn,value from object where name='contact_number' and type=1 and parent=objid for each cn /* contact number objid */ results = select_by_number( value ) /* routine which is a select user.contact_number=value */ foreach result if( result.objid == objid ) continue /* skip this one */ /* add to variant result */ } some of the above could be selected with an inner join into one select, but for simplicity broke it into more atomic steps... /* och = object contact number, ou = object user */ select ocn.value,ocn.objid as contact_objid from object as ou join object as ocn on ocn.parent_objid=ou.objid where ou.value='john' and ou.type=1 and ocn.type=2 /*and ocn.name='work'*/ if you know that you have something resembling structure to the data... J