Database handling internal to Procon

[phogue]

Database connections and querying should be completely handled by Procon, removing control from plugins to establish and directly query a database. This should ease user setup and tighten the sandbox on plugin actions.

Benefits

• User sets up a database connection once, plugins then just "use a database".
• We can develop a little bit of abstraction so a plugin does not need to know what database is being used for very simple storage (key-value-pair type storage)
• No duplication of connections to a single database
• No permissions issues by hosts on plugin sandboxes
• Simple migration handling can be written for plugins to use
• Plugins can have tables as "Global", "Plugin Specific" and "Connection+Plugin Specific".
• Database is accessed in in a unified way, potentially allowing plugins to share collected data.

Requirements

• No external libraries like NHibernate or other bloatware.
• No ORM. Everything must be remoted as a simple query string from sandbox to procon, so Procon can never accept objects that are not defined in Procon.Core.
• Don't define objects in Procon.Core specifically for ORM in tables.
• No ORM.
• Simple abstraction via CodeIgniter style active records (they renamed it I think). It's not active record at all really, it's simply a query builder that is passed in to different drivers to build a query string.

var sqlQuery = new SqlQuery()->Select("something")->From("somewhere")->OrderBy("column");

var serializer = new MysqlSerializer();

var text = sqlQuery->ToString(serializer);
// Select `something` FROM `somewhere` ORDER BY `column`;

CommandResult result = this.ProxyCommand(..., text);

result.Rows... // or something like this.

• Must support NoSQL and SQL
• Must not be so crazy that it never happens.

Notes

• Anything beyond simple storage will require the plugin to write specific plugins for their target database. The chances are pretty good people will only use MySQL anyway.

Three people have tried to implement something like this in Procon. Some import every library written in .NET to get something sort of working. Others are very specific on MySQL functionality. Keep. It. Simple.

[m4cx]

According to these requirements, I think we should implement a whole storage abstraction layer.

So the admin can decide weather to store data in mysql, nosql, filesystem, sql, oracle or whatever.

We should also think about migration paths, changes to a data model and so on.

[phogue]

Migration handling will be included, but will need to be initiated by a plugin to ensure its tables are updated. Procon will setup a migration collection itself when a database is opened, but otherwise all alterations will be passed from the plugin like other queries.

Migration will support version, up() and down(). Probably indexed by the plugin Guid. More than likely a bunch of other meta data for kicks.

Collections, via standardized naming convention, will be restricted to a plugin, a plugin on a specific connection or global to all connections/plugins.

I'm not busting a gut on other storage options beyond MySql, MongoDb and SqlLite. I've had a couple people ask for MsSql about a year ago, otherwise most hosts just run MySql. I'm the one pushing Mongo on people cause I think it's neat :)

Procon itself only requires a migration model, otherwise it shouldn't know of anything in the database. With this in mind something can easily be thrown together, much like a plugin would. The entire database support is simply so plugins have something to use and we can lock the the plugin appdomain security down a little bit.

Results of queries will more than likely be passed back to the plugin/daemon client in json. Allows for queries to be initiated over the daemon with results. Since we're using the Newtonsoft.Json plugin developers will be able to easily deserialize this back to an array of a certain type of object.

[phogue]

This branch has been merged in. Procon.Database and Procon.Database.Serialization are functionally complete. Refactoring and changes will undoubtedly occur.

I need this in the main branch to begin adding functionality to Procon.Core.Database namespace so plugin unit testing of database functionality can begin easily.