Using state in transactions

[Julian May]

Hi guys

I'm fairly new to the Arango-game, and got my basic writing, reading and traversals going, but now i need to write a transaction that needs to use state as part of the transaction (or rethink my overall design) and was hoping to get some input's in this direction.

I'm trying to solve this key scenario:

Given the availability for "DBL"-rooms is 6 in the period from 2015-11-19 to 2015-11-22 

And  the availability for "DBL"-rooms is 9 in the period from 2015-11-22 to 2015-11-28

When the availability for "DBL"-roms is decreased by 2 for the period from 2015-11-20 to 2015-11-24

Then I end up with the following availability for "DBL"-rooms

  |  from 2015-11-19 to 2015-11-20 : 6 "DBL"-rooms available | 

  |  from 2015-11-20 to 2015-11-22 : 4 "DBL"-rooms available |

  |  from 2015-11-22 to 2015-11-24 : 7 "DBL"-rooms available |

  |  from 2015-11-24 to 2015-11-28 : 9 "DBL"-rooms available |

Currently I'm retrieving the "RoomTypeAvailability" documents that intersects with the decreased period out into my C# code, deleting the documents (in this case, 2) and writing (4) new ones - which is faaar from optimal. besides the roundtrip that could be spared, I'd like the atomicity of handling it in one transaction, so I dont end up leaving gaps in the availability, or having overlapping periods for the same roomtype (in this case "DBL").

Any suggestions as to how such a transaction should be written? Or am I barking up the wrong tree, thinking that this is a reasonable thing to be doing in a transaction? 

Many thanks in advance :)

/Julian

[Max Neunhöffer]

Hi Julian,
I am one of the ArangoDB developers.
Just a quick idea: in ArangoDB you can write JavaScript code that is executed on the server as one transaction. Your problem sounds as if your problem could very conveniently be solved by deploying such a JavaScript function to the DB server which does exactly what you do, but on the server as one transactional change.
With the Foxx microservice framework you can even expose this functionality as an additional REST HTTP service, extending the API of ArangoDB.
I am happy to provide more details on this, but probably only tomorrow, since it is already quite late today and I am typing this on my mobile phone.
Cheers,
Max

Am 18. November 2015 20:11:22 MEZ, schrieb Julian May <julia...@gmail.com>:
>Hi guys
>
>I'm fairly new to the Arango-game, and got my basic writing, reading
>and
>traversals going, but now i need to write a transaction that needs to
>use
>state as part of the transaction (or rethink my overall design) and was
>
>hoping to get some input's in this direction.
>
>I'm trying to solve this key scenario:
>

>*Given the availability for "DBL"-rooms is 6 in the period from
>2015-11-19
>to 2015-11-22 *
>*And the availability for "DBL"-rooms is 9 in the period from
>2015-11-22
>to 2015-11-28*
>*When the availability for "DBL"-roms is decreased by 2 for the period
>from
>2015-11-20 to 2015-11-24*
>*Then I end up with the following availability for "DBL"-rooms*
>* | from 2015-11-19 to 2015-11-20 : 6 "DBL"-rooms available | *
>* | from 2015-11-20 to 2015-11-22 : 4 "DBL"-rooms available |*
>* | from 2015-11-22 to 2015-11-24 : 7 "DBL"-rooms available |*
>* | from 2015-11-24 to 2015-11-28 : 9 "DBL"-rooms available |*

[Julian May]

Hey Max

I'm using the arangoclient.net framework to applicate the javascript api, for the exact same reasons - my problem is how to formulate the javascript/AQL.
The documentation is sufficient and concise - I'm just hoping for some pseudo-code or pointers to get me started in the right direction.

I'll start scratching out something and post my babysteps here.

/Julian

[Max Neunhöffer]

Hi Julian,

thinking about your problem, I think it is more convenient to store one
document for each day of the year, rather than documents storing ranges.

This approach makes the logic much simpler.

AQL does not have branches (if statements), therefore it is a bit
awkward to perform the reservation you want and you probably need two
steps.

Therefore I have quickly hacked together a solution with this data
layout using JavaScript transactions. This uses the ArangoDB shell
`arangosh`, but a similar approach should work from other languages.
I am not a .NET expert, but I am sure that this works from there in a
similar way. If not, it is a bug in our .NET driver! :-)

I attach a file "hotels.js" file and then execute the following commands
on the shell:

The following creates the database with a few days and availabilities:

dba = db._create("DBAvailability");
for (i = 20; i <= 24; i++) { dba.insert({_key:"2015-11-"+i, available:6}); }
for (i = 12; i <= 19; i++) { dba.insert({_key:"2015-11-"+i, available:9}); }
db._query("FOR d IN DBAvailability SORT d._key RETURN {date: d._key, avail: d.available}").toArray()

Now we load the function in the "hotels.js" file, which must be in the
current directory:

load = require("internal").load;
load("hotels.js")

To make a reservation for some days, we need a list of the dates:

l = [];
for (i = 18; i <= 22; i++) { l.push("2015-11-"+i); }

Now we can call "reserveRooms" as follows to reserve 4 double rooms
for all these dates and fail (without a change) if there is not enough
availability:

reserveRooms(l, 4)
db._query("FOR d IN DBAvailability SORT d._key RETURN {date: d._key, avail: d.available}").toArray()

This time it worked, a second time it will no longer work:

reserveRooms(l, 4)
db._query("FOR d IN DBAvailability SORT d._key RETURN {date: d._key, avail: d.available}").toArray()

JavaScript throws an exception here, in any case, the transaction is aborted,
which should be reported by the driver.

The actual function in "hotels.js" is quite simple:


function reserveRooms (dates, nr) {
// dates must be an array of dates in the form 2015-11-29
// and nr must be a positive integer
// this executes a transaction that reserves nr rooms on all the dates
db._executeTransaction( {
collections: {
write: [ "DBAvailability" ]
},
action: `function () {
var dba = require("internal").db.DBAvailability;
var dates = ${JSON.stringify(dates)};
var nr = ${nr};
for (var i = 0; i < dates.length; i++) {
var a = dba.document(dates[i]);
if (a.available < nr) {
throw "Not enough availability on date " + dates[i];
}
dba.update(dates[i], { available: a.available - nr });
}
}`
} );
}


It simply executes a transaction and sends the JavaScript function that
contains the code for it over *as a string*! So your .NET driver will
give you a possibility to do the very same. Note that the string
containing the JavaScript action function must contain the values of
all the arguments "dates" and "nr". I achieve this by using the
new fancy JavaScript template string functionality. I am sure C#
offers some convenient way to put together a string containing
JavaScript code.

I hope this helps, just ask again if you get stuck.

Best regards,
Max.

[Julian May]

Wow - thank you so much! I'll try it out right away.
I started TDD'ing my way through this but got stuck on binding collection-name variables >_<

I'll get back on this in a bit

[Julian May]

Hey Max

This works very well - And i see the advantage of working with a document per date in this regard.
Thanks for including the exception - even though this is not a constraint (negative availability is common), it was good to see how to enforce consistency within a transaction.

I imagine this will cause a lot of io though, which i was hoping to "smart" my way around, since this very solution is causing problems in the legacy (ms-sql) system.
Maybe this is premature optimization, and i should focus on implementing the functionality the simple way first...

/Julian

[Wilfried Gösgens]

Hi,
Bind Parameters in AQL are different for regular variables and variables parametrizing collections.
its @param, and @@collection.
You then put them in an associative array that way:
{
 'param': 'hihi',
 '@collection': 'haha'
}

See https://docs.arangodb.com/Aql/Invoke.html and https://docs.arangodb.com/cookbook/AvoidingInjection.html#bind-parameter-types for more details.

[Julian May]

Hey Wilfried

I see - I was using @@collection as documentation suggested, but could figure out how to name the argument. 

• collection
• throws "no matching argument @collection"
• @collection
• throws "unexpected symbol"

The problems was that i was not encapsulating the key with 's ( @collection !=  '@collection' )

Thanks!

/Julian

[Julian May]

How do I go about writing for example upserting 2000 documents in one transaction?
If i can't start a transaction and append to that transaction in chucks, and then commit the entire transaction, I have a problem (unless there is some feature in the REST api to handle huge payloads so I dont need to chunk).
Since I'm now moving towards a document per day, opening a room for 5 years now presents this challenge...

Thanks

/Julian

[Julian May]

Oh, I think i got it - write a transaction that uses the DATE_ADD to somehow enumerate from the beginning of the period to the end of the period, instantiating the individual documents server-side....

Something like this:

function(roomType, startDate, numberOfDays, availabilityModification){

  for(i = 0; i < numberOfDays; i++)

  {

    var thisDate = DATE_ADD(startDate,i,"day");

    UPSERT({ roomTypeCode: roomType, date: thisDate });
    INSERT({ roomTypeCode: roomType, date: thisDate, available: availabilityModification }); 

    UPDATE({ roomTypeCode: roomType, date: thisDate, available: OLD.available + availabilityModification });

  }
}

..right?

[Max Neunhöffer]

This looks perfectly good to me. You get transactional behaviour, if
anything goes wrong, it will throw an exception and nothing has happened
at all.

Cheers,
Max.

> --
> You received this message because you are subscribed to the Google Groups "ArangoDB" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to arangodb+u...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Julian May]

Cool - thank you very much for the educational support :)
/Julian