Re: [dotNetRDF-Develop] Update on progress with new SQL backend

[Strydom, K. <jst...@ha...>]

Hi Rob,

 

I think option one would be the preferred upgrade path.

 

Regards,

Koos Strydom

 

From: Rob Vesse [mailto:rv...@do...] 
Sent: Friday, 22 July 2011 7:28 PM
To: Strydom, Koos; rpd...@be...
Cc: dot...@li...
Subject: Update on progress with new SQL backend

 

Hi Koos and Rob

So I wanted to give you an update on the upcoming new SQL backend and
get your input on a few points.

Design and Performance

So the new SQL backend has a schema fairly similar to the existing
backend (so read/write performance is pretty similar) but it has been
designed to do all the IO using stored procedures since this allows for
the schema to change in future (or alternative schemas to be used)
provided that they support the necessary stored procedures.

The code has also been designed to take advantage of some new API
features to provide reasonable performance for out of memory SPARQL i.e.
you can make queries and updates against the database without having to
load all the data into memory.  This makes the system much more scalable
in the long term since you don't need so much memory to make requests
against the store though this results in a speed trade off i.e.
in-memory query is typically much faster but requires significantly more
memory.  

One of the main advantages of this new out of memory query mechanism is
that the memory usage is much lower and there is no time overhead of
waiting to load data into memory before answering queries.  So
particularly for people deploying the backend in environments with
limited resources e.g. shared hosting it will be much more usable.

Security

The new backend also improves on security providing a set of roles for
users which determine what actions they can take on the store, the point
of this being it makes it much easier to secure user interaction with
the store.  Currently the roles I'm incorporating are as follows:

*	Admin - Full read/write privileges plus the ability to
completely empty the store (though not destroy the database schema)
*	Read/Write - Full read/write privileges
*	Read/Insert - Full read privileges and can insert new data but
not overwrite/delete existing data
*	Read Only - Full read privileges but no write priveleges

If you guys can think of any other useful roles for the backend please
let me know?

Note that someone with read/write privileges can effectively empty the
store completely by deleting one graph at a time but only the admin can
do it in a single command.  Additionally only the admin can actually
empty the nodes table which has the effect of resetting the store.

Code Changes

In order to use the new backend you will have to make some minor
code/configuration changes which I will provide documentation for nearer
the time but hopefully this will not be too disruptive.

One thing to note is that as part of a wider strategy regarding
reduction of dependencies in the core library the new SQL backend will
reside in a separate library dotNetRDF.Data.Sql.dll which allows us to
maintain the code separately and add support for additional database
back ends (e.g. MySql, Oracle etc) in future without increasing
dependencies in the core library.  In the initial release only Microsoft
SQL Server will be supported, hopefully mySql support will follow in a
subsequent release and Oracle eventually since there are issues with
getting Oracle licenses for the products required to actually develop
and test against.

Upgrade Paths

The main thing I wanted to get your opinion on was how you would prefer
to upgrade your existing databases?  The options I am considering are as
follows:

1.	Provide a standalone tool which can be run at the command line
which will migrate from the old format to the new one.  Migration could
either be in-place or to a new empty database.  For in-place migration
the old data could be maintained and made restorable for rollbacks if
desired?
2.	Make the upgrade silent and automatic within the code.  This has
issues with the fact that it puts the upgrade mechanism into the library
and creates a potentially very long delay when you first access your
store with the new code as the upgrade has to take place.
3.	Do a partial silent upgrade.  As the database schemas are not
too dissimilar it may be possible to just do a partial upgrade by
creating a set of stored procedures within the existing database.  This
would make it act like a new backend but the performance would likely be
worse than the new backend.  The advantage of this option is that unlike
2 the upgrade would be very quick and barely noticeable and unlike
either of the other options would not modify your existing data in any
way so you could continue using old code to talk to it as well as the
new code.

My personal preference is Option 1 but as users of the existing SQL
backend I'd appreciate your feedback on this.  Whatever is decided
Option 1 will probably be provided in addition to either of the other
options - ideally I'd prefer to just have option 1 and not bother with
options 2 and 3 but I'm happy to work with whatever you'd prefer.

Best Regards,

Rob Vesse


*****************************
NOTICE - This message from Hatch is intended only for the use of the individual or entity to which it is addressed and may contain information which is privileged, confidential or proprietary. 
Internet communications cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, arrive late or contain viruses. By communicating with us via e-mail, you accept such risks.  When addressed to our clients, any information, drawings, opinions or advice (collectively, "information") contained in this e-mail is subject to the terms and conditions expressed in the governing agreements.  Where no such agreement exists, the recipient shall neither rely upon nor disclose to others, such information without our written consent.  Unless otherwise agreed, we do not assume any liability with respect to the accuracy or completeness of the information set out in this e-mail.  If you have received this message in error, please notify us immediately by return e-mail and destroy and delete the message from your computer.