I'm cc'ing the list now because this could be interesting for everyone. (Guys, see below. Mohit has just completed 2 missing features in my work on json_server. We now have fully functioning key-value API, in addition to Stewart's earlier SQL-over-HTTP API.)
On Wed, Mar 21, 2012 at 12:13 AM, Mohit Srivastava <[email protected]> wrote: > What is in future version ? *** I think we need to sit down one day and write a proper blueprint, but here goes: 0.3 = usability, refactoring and multithreading The Json parser likes to throw exceptions. This will crash entire drizzle server. So we need to wrap all this code into try/catch blocks. (It's possible that there really aren't any bugs left, but it's a dangerous piece of code...) Currently the http library is multi-threaded. We need to create a pool of processing threads to which the libevent http server hands over each request. Related to this it probably makes sense to make this code object oriented and probably also break into multiple files. Small usability things like allowing to set default schema and table name as drizzled options. Perhaps also add authentication at this point. Or later. (I'm thorn between doing http authentication or just tossing a username and password into the json query structure.) 0.4 (and beyond, don't know what would be each specific version) Range scans for key: ability to specify a minimum and maximum value for _id. For this we also need to specify how to express that in json. Ie now you do { "_id" : 1 } but json doesn't allow { "_id" > 1 AND "_id" < 10 } so we have to invent some syntax there. SELECT and UPDATE on secondary key: GET { "document" : { "person" : { "firstname" : "Henrik" } } } will return this record: { "_id" : 1, "document" : { "person" : { "firstname" : "Henrik", "lastname" : "Ingo" } } } To achieve that we will utilize the JS plugin in the select queries. See http://docs.drizzle.org/plugins/js/index.html Oh, btw the JS plugin is still single threaded too. Might be worth fixing before going into next step: After that, arbitrary combinations for the query parameters. For instance, give a range of _id values, then some other key like "firstname" and all of this goes into the WHERE of the SQL. Now, all this looks nice except that _id is the only thing that is indexed. It would be nice to also have something that resembles secondary indexes, but what are we going to do when our data is just some json in a TEXT field? My plan here is to: * we need to implement something like stored procedures, but building on the JS plugin. * after that it's easy to implement something like triggers * now, add some json/http command where user can tell Drizzle that a secondary index should be maintained for some key inside the json document. * Create a helper table - this is our index * Using our JavaScript triggers, whenever an inserted or updated json document matches the key given by user, insert the key+value and the corresponding _id value into the index table * When receiving a query that is using the "secondary index", we will actually query the helper table and then join by _id field to the actual json table. If that last part was too much, don't worry I've had like 2 SW architects at work give input to this thinking, and over several lunch breaks. I'll write it better into a blueprint one day. At this point we would really have covered pretty much everything that is possible to do with single table, single statement operations. I don't see that multi-statement transactions are necessarily what you want to start doing over http anyway, so it might be the end of the road. We would now have pretty much feature parity with MongoDB and CouchDB. As a side effect we would also have created JavaScript based (quasi) stored procedures and triggers into Drizzle. We don't need it, but to make these procedures generally useful, one would also want to bind the Execute API to be available from within JavaScript, so that you could create JavaScript procedures that do something like res = db.execute("SELECT * FROM mytable"); // now iterate over res and do something... and maybe a "CALL myprocedure();" syntax into Drizzle parser to conveniently call these procedures. (Now, don't try to do all of this by tomorrow, because then I would be seriously out of ideas :-) henrik > On Wed, Mar 21, 2012 at 3:42 AM, Henrik Ingo <[email protected]> > wrote: >> >> This is amazing :-) Please just push to the same branch and I'll >> include that too. >> >> You have just completed what I wanted to call 0.2 version of this >> plugin. Thanks!!! >> >> henrik >> >> On Wed, Mar 21, 2012 at 12:11 AM, Mohit Srivastava >> <[email protected]> wrote: >> > Hi Henrik, >> > >> > I am done with create part also. And for me its working fine. >> > >> > -- >> > Mohit >> > >> > >> > On Wed, Mar 21, 2012 at 3:30 AM, Henrik Ingo <[email protected]> >> > wrote: >> >> >> >> You probably then need to also start a new transaction. Try "BEGIN;". >> >> Or create a new session object from scratch. >> >> >> >> henrik >> >> >> >> On Tue, Mar 20, 2012 at 11:40 PM, Mohit Srivastava >> >> <[email protected]> wrote: >> >> > But stuck in making entry just after creation of table. Trying to >> >> > figure >> >> > out >> >> > that. >> >> > >> >> > mohit >> >> > >> >> > >> >> > On Wed, Mar 21, 2012 at 3:02 AM, Henrik Ingo >> >> > <[email protected]> >> >> > wrote: >> >> >> >> >> >> Ah, that simple. Makes sense :-) >> >> >> >> >> >> henrik >> >> >> >> >> >> On Tue, Mar 20, 2012 at 11:27 PM, Mohit Srivastava >> >> >> <[email protected]> wrote: >> >> >> > Use Commit before sql query . >> >> >> > It is better explain in slave plugin. >> >> >> > >> >> >> > mohit >> >> >> > >> >> >> > >> >> >> > On Wed, Mar 21, 2012 at 2:55 AM, Henrik Ingo >> >> >> > <[email protected]> >> >> >> > wrote: >> >> >> >> >> >> >> >> It works? So what's the secret? >> >> >> >> >> >> >> >> henrik >> >> >> >> >> >> >> >> On Tue, Mar 20, 2012 at 11:19 PM, Mohit Srivastava >> >> >> >> <[email protected]> wrote: >> >> >> >> > I did that , able to create table using execute :) >> >> >> >> > >> >> >> >> > >> >> >> >> > On Wed, Mar 21, 2012 at 2:44 AM, Henrik Ingo >> >> >> >> > <[email protected]> >> >> >> >> > wrote: >> >> >> >> >> >> >> >> >> >> I remember seeing a comment that the Execute API is >> >> >> >> >> automatically >> >> >> >> >> wrapping your sql statement into a transaction. However, in >> >> >> >> >> InnoDB >> >> >> >> >> (ie >> >> >> >> >> Drizzle/MySQL in general) a CREATE TABLE and ALTER TABLE >> >> >> >> >> statement >> >> >> >> >> cannot be part of a transaction. >> >> >> >> >> >> >> >> >> >> I don't know how to then do a CREATE TABLE with the Execute >> >> >> >> >> API. >> >> >> >> >> Maybe >> >> >> >> >> there is some other method to call so that it doesn't >> >> >> >> >> automatically >> >> >> >> >> add a transaction for you. (...and if not, maybe we can add >> >> >> >> >> such >> >> >> >> >> a >> >> >> >> >> method.) I suggest you ask on the mailing list and/or read >> >> >> >> >> your >> >> >> >> >> way >> >> >> >> >> through the source code, starting at drizzled/execute.h. >> >> >> >> >> >> >> >> >> >> I branched your previous bzr repo and started reading it and >> >> >> >> >> compiling >> >> >> >> >> it. But I then realized that I have a few small fixes in my >> >> >> >> >> own >> >> >> >> >> tree >> >> >> >> >> that I hadn't pushed to launchpad, so now unfortunately they >> >> >> >> >> are >> >> >> >> >> not >> >> >> >> >> included in your branch. It's ok, I can merge them, but I just >> >> >> >> >> need >> >> >> >> >> an >> >> >> >> >> hour or two to properly focus a bit more while doing that. >> >> >> >> >> >> >> >> >> >> henrik >> >> >> >> >> >> >> >> >> >> On Tue, Mar 20, 2012 at 5:58 PM, Mohit Srivastava >> >> >> >> >> <[email protected]> wrote: >> >> >> >> >> > Hi Henrik; >> >> >> >> >> > >> >> >> >> >> > When I execute ""CREATE TABLE `test`.`t7` (_id INT PRIMARY >> >> >> >> >> > KEY >> >> >> >> >> > auto_increment, document TEXT); using >> >> >> >> >> > execute.run(sql,resultset); >> >> >> >> >> > Got this execption : >> >> >> >> >> > "Transactional DDL not supported" >> >> >> >> >> > >> >> >> >> >> > I didn't figure out this. Any Idea ? >> >> >> >> >> > >> >> >> >> >> > -- >> >> >> >> >> > Mohit >> >> >> >> >> > >> >> >> >> >> > On Tue, Mar 20, 2012 at 4:43 PM, Mohit Srivastava >> >> >> >> >> > <[email protected]> wrote: >> >> >> >> >> >> >> >> >> >> >> >> Yes , I got what i want. >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> >> >> Mohit >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> On Tue, Mar 20, 2012 at 11:50 AM, Henrik Ingo >> >> >> >> >> >> <[email protected]> >> >> >> >> >> >> wrote: >> >> >> >> >> >>> >> >> >> >> >> >>> On Tue, Mar 20, 2012 at 5:18 AM, Mohit Srivastava >> >> >> >> >> >>> <[email protected]> wrote: >> >> >> >> >> >>> > Hi Henrik, >> >> >> >> >> >>> > >> >> >> >> >> >>> > Thanks for link. >> >> >> >> >> >>> > >> >> >> >> >> >>> > Yeah , I can code more things. >> >> >> >> >> >>> > >> >> >> >> >> >>> >> >> >> >> >> >>> Excellent! It's so great to have new blood in the project >> >> >> >> >> >>> :-) >> >> >> >> >> >>> >> >> >> >> >> >>> Then I suggest you try your luck with implementing CREATE >> >> >> >> >> >>> TABLE >> >> >> >> >> >>> too. I >> >> >> >> >> >>> already shared the "back-of-the-napkin" design earlier: >> >> >> >> >> >>> >> >> >> >> >> >>> The idea is that if you POST a JSON object to a table that >> >> >> >> >> >>> doesn't >> >> >> >> >> >>> exist, then a matching table should automatically be >> >> >> >> >> >>> created, >> >> >> >> >> >>> and >> >> >> >> >> >>> then >> >> >> >> >> >>> the object is inserted into the new table. Example >> >> >> >> >> >>> POST {"_id" : "1"; "document" : <arbitrary json> } >> >> >> >> >> >>> -> This would create the table I emailed you earlier. >> >> >> >> >> >>> -> _id is optional, there will always be the _id column >> >> >> >> >> >>> -> the name of the second column can be something else >> >> >> >> >> >>> than >> >> >> >> >> >>> "document", CREATE TABLE should use what is in the >> >> >> >> >> >>> incoming >> >> >> >> >> >>> JSON >> >> >> >> >> >>> object. >> >> >> >> >> >>> -> there can be more than one column (in addition to _id) >> >> >> >> >> >>> >> >> >> >> >> >>> > Here is few things I figure out in json_server.cc >> >> >> >> >> >>> > >> >> >> >> >> >>> > // } elseif ( req->type == EVHTTP_REQ_PUT ) { >> >> >> >> >> >>> > //process_api02_json_put_req( req, NULL); Either put >> >> >> >> >> >>> > functionality >> >> >> >> >> >>> > need to >> >> >> >> >> >>> > modify or it comments just for test purpose ? >> >> >> >> >> >>> >> >> >> >> >> >>> I think originally it was commented out because PUT also >> >> >> >> >> >>> exists >> >> >> >> >> >>> only >> >> >> >> >> >>> in event2/http.h. >> >> >> >> >> >>> >> >> >> >> >> >>> But it turns out that PUT and POST are pretty much the >> >> >> >> >> >>> same. >> >> >> >> >> >>> so >> >> >> >> >> >>> you >> >> >> >> >> >>> could just direct it to process_api02_json_post_req() and >> >> >> >> >> >>> it >> >> >> >> >> >>> should >> >> >> >> >> >>> work. >> >> >> >> >> >>> >> >> >> >> >> >>> The trick is that in REST HTTP semantics POST maps to SQL >> >> >> >> >> >>> INSERT >> >> >> >> >> >>> and >> >> >> >> >> >>> PUT to UPDATE. However, it is common to also use POST for >> >> >> >> >> >>> UPDATE. >> >> >> >> >> >>> That's fine with me, the code will work since the >> >> >> >> >> >>> MySQL/Drizzle >> >> >> >> >> >>> specific REPLACE command does both INSERT and UPDATE >> >> >> >> >> >>> automatically. >> >> >> >> >> >>> If >> >> >> >> >> >>> the ID doesn't already exist, it becomes an insert. >> >> >> >> >> >>> >> >> >> >> >> >>> > Also for json like {"b" : "test"} , the entry in >> >> >> >> >> >>> > database >> >> >> >> >> >>> > is >> >> >> >> >> >>> > 1,"test" >> >> >> >> >> >>> > but I >> >> >> >> >> >>> > think it should be like 1,test . It take " in string >> >> >> >> >> >>> > too. >> >> >> >> >> >>> >> >> >> >> >> >>> No, this is correct. The value in the TEXT columns need to >> >> >> >> >> >>> be >> >> >> >> >> >>> values >> >> >> >> >> >>> that can be passed into the Json parser. So it has to be a >> >> >> >> >> >>> quoted >> >> >> >> >> >>> string, a json structure or a number. However, unquoted >> >> >> >> >> >>> strings >> >> >> >> >> >>> will >> >> >> >> >> >>> produce an error. (You can insert one manually and see >> >> >> >> >> >>> what >> >> >> >> >> >>> happens.) >> >> >> >> >> >>> >> >> >> >> >> >>> If you insert deeper json structures like {"_id" : 1, >> >> >> >> >> >>> "document" >> >> >> >> >> >>> : >> >> >> >> >> >>> { >> >> >> >> >> >>> "person" : { "firstname" : "Henrik", "lastname" : "Ingo" } >> >> >> >> >> >>> } >> >> >> >> >> >>> } >> >> >> >> >> >>> and >> >> >> >> >> >>> then SELECT *, you'll understand the idea. >> >> >> >> >> >>> >> >> >> >> >> >>> > Also , What replication policy Drizzle uses ? I only >> >> >> >> >> >>> > know >> >> >> >> >> >>> > about >> >> >> >> >> >>> > "rack >> >> >> >> >> >>> > aware >> >> >> >> >> >>> > replication policy". >> >> >> >> >> >>> > Is there any place where I can find such theories >> >> >> >> >> >>> > related >> >> >> >> >> >>> > to >> >> >> >> >> >>> > drizzle >> >> >> >> >> >>> > codebase? >> >> >> >> >> >>> >> >> >> >> >> >>> New replication docs were just committed yesterday: >> >> >> >> >> >>> >> >> >> >> >> >>> http://docs.drizzle.org/replication/index.html >> >> >> >> >> >>> >> >> >> >> >> >>> I suppose this is what you meant? >> >> >> >> >> >>> >> >> >> >> >> >>> henrik >> >> >> >> >> >>> >> >> >> >> >> >>> >> >> >> >> >> >>> >> >> >> >> >> >>> -- >> >> >> >> >> >>> [email protected] >> >> >> >> >> >>> +358-40-8211286 skype: henrik.ingo irc: hingo >> >> >> >> >> >>> www.openlife.cc >> >> >> >> >> >>> >> >> >> >> >> >>> My LinkedIn profile: >> >> >> >> >> >>> http://www.linkedin.com/profile/view?id=9522559 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> >> [email protected] >> >> >> >> >> +358-40-8211286 skype: henrik.ingo irc: hingo >> >> >> >> >> www.openlife.cc >> >> >> >> >> >> >> >> >> >> My LinkedIn profile: >> >> >> >> >> http://www.linkedin.com/profile/view?id=9522559 >> >> >> >> > >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> [email protected] >> >> >> >> +358-40-8211286 skype: henrik.ingo irc: hingo >> >> >> >> www.openlife.cc >> >> >> >> >> >> >> >> My LinkedIn profile: >> >> >> >> http://www.linkedin.com/profile/view?id=9522559 >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> [email protected] >> >> >> +358-40-8211286 skype: henrik.ingo irc: hingo >> >> >> www.openlife.cc >> >> >> >> >> >> My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559 >> >> > >> >> > >> >> >> >> >> >> >> >> -- >> >> [email protected] >> >> +358-40-8211286 skype: henrik.ingo irc: hingo >> >> www.openlife.cc >> >> >> >> My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559 >> > >> > >> >> >> >> -- >> [email protected] >> +358-40-8211286 skype: henrik.ingo irc: hingo >> www.openlife.cc >> >> My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559 > > -- [email protected] +358-40-8211286 skype: henrik.ingo irc: hingo www.openlife.cc My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559 _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
