<div class="gmail_quote">On Thu, Sep 6, 2012 at 2:49 PM, Ian Lynagh <span dir="ltr"><<a href="mailto:ian@well-typed.com" target="_blank">ian@well-typed.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
* Only a single process can use the database at once. For example, if<br>
the admins want a tool that will make it easier for them to approve<br>
user requests, then that tool needs to be integrated into the Hackage<br>
server (or talk to it over HTTP), rather than being standalone.<br></blockquote><div><br></div><div><br></div><div>This much is wrong; acid-state does have functionality for accessing/modifying the database from other processes, namely System.Data.Remote, but I haven't investigated deeply.</div>
<div><br></div><div>That said, I think there would be some potential advantages of moving to SQL. My personal preference would for PostgreSQL, particularly because of the wealth of datatypes it provides, or can be added to it via already existing third-party plugins. Compared to MySQL, it's definitely less fluff, more stuff.</div>
<div><br></div><div>For example, the schema I sketched out for the account request processing uses hstore, which is an extension (included with the standard postgres distribution, even) that provides a field of type (Map String String), to store both public profile information as well as HTTP headers the client sent with the request, as well as indexes on that type. Or, we might store module names using the ltree extension, which is a perfect match for the problem: PostgreSQL would then understand the components of the module name, be able to index those, and provide a variety of additional operators to query them. </div>
<div><br></div><div><a href="http://www.postgresql.org/docs/9.1/static/hstore.html">http://www.postgresql.org/docs/9.1/static/hstore.html</a></div><div><a href="http://www.postgresql.org/docs/9.1/static/ltree.html">http://www.postgresql.org/docs/9.1/static/ltree.html</a></div>
<div><br></div><div>Also, the rules and trigger systems provided by PostgreSQL are considerably more capable than anything in MySQL, which allow you to do things such as create updateable views (with some work). Also, I find PL/pgSQL and asynchronous notifications particularly useful, and while I don't know MySQL well enough to definitively say that MySQL has no answer to these features, there isn't anything obvious that is as simple and powerful as asynchronous notifications nor as fully featured as PL/pgSQL. (And, there are a ton of other PL/*** languages out there, somebody even has a very rudimentary start on PL/Haskell.) </div>
<div><br>One of the most common use cases for asynchronous notifications is to be able to efficiently and robustly respond to changes made to a database. (Robust in the sense that the client making the change doesn't know that other processes need to be informed of said change, as this will instead be handled by a trigger that NOTIFYs the interested clients.)</div>
<div><br></div><div>Best,</div><div>Leon</div></div>