I'm beginning to understand some of the implementation challenges here. Eric, it'd be great to get that open sourced. I can tell you at least I will jump and prototype on it.
Sergej and Roger, I do like this potential idea as well. Of course, now it's a custom sqlite, but at least the job gets done. Do you think you'll want to do this anytime soon as an open source project? On Wed, Jul 15, 2015 at 2:55 PM, J Decker <d3ck0r at gmail.com> wrote: > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
