<div dir="ltr">Hi everybody,<div><br></div><div>MySQL has no array support, so I'd like to proceed carefully.</div><div>The only reference I found in MySQL documentation is about an aggregate function to form a concatenate string.</div><div><br></div><div>Otherwise one has to work with some ad hoc array serialization or temporary tables.</div><div><br></div><div>Marco<br><div class="gmail_extra"><br><div class="gmail_quote">2015-04-28 10:17 GMT+02:00 Markus Demleitner <span dir="ltr"><<a href="mailto:msdemlei@ari.uni-heidelberg.de" target="_blank">msdemlei@ari.uni-heidelberg.de</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Pat, hi DAL,<br>
<br>
As we're currently talking about extending ADQL, maybe this is the<br>
time to try and come up with something; it would definitely help the<br>
obscore use case.<br>
<span class=""><br>
On Mon, Apr 27, 2015 at 09:07:46AM -0700, Patrick Dowler wrote:<br>
> Anyway, I do agree that arrays can be a useful denormalisation technique and<br>
> I wouldn't be against putting some effort into adding support for arrays of<br>
> primitives (booleans and numbers) to ADQL and TAP.<br>
<br>
</span>Although I've been deeply unhappy in particular about string arrays<br>
in VOTable (which we'd presumably need for obscore), I tend to agree<br>
about the desirability of arrays in *AD*QL.<br>
<br>
To make that happen, I think we need to figure out what we want in<br>
terms of features and then figure out if everyone can go along.<br>
<br>
In terms of features, I'd say the minimum is:<br>
<br>
* 1 D arrays<br>
* simple indexing: with a function or rather [] notation?<br>
* building arrays on the fly: the array_agg aggregate function<br>
<br>
SQL1992 doesn't have any of this, so we'd have to steal from<br>
somewhere else (I'd propose postgres' grammar, which syntactically<br>
unfortunately is quite a bit different from SQL1992's grammar).<br>
<br>
Features wie might possibly want in addition could include some of:<br>
<br>
* n D arrays<br>
* inline array literals ("select [ra, dec], [pmra, pmde] from...")<br>
* operators/functions, e.g., as in<br>
<a href="http://www.postgresql.org/docs/current/static/functions-array.html" target="_blank">http://www.postgresql.org/docs/current/static/functions-array.html</a><br>
<br>
Are there, maybe, other, lower-hanging fruit?<br>
<br>
<br>
As a first step, I think it'd be useful if people running the various<br>
database backends could report how much trouble the respective<br>
features would be for their backends. If some of the major ones have<br>
no array support at all, I'd say it's a non-starter right there,<br>
otherwise we could think of a subset of features that won't cause<br>
havoc in our ADQL grammar.<br>
<br>
So --<br>
<br>
Postgres obviously wouldn't have trouble with any of this.<br>
<br>
SQLite has no native array support I'm aware of, but if I had a<br>
SQLite backend I suspect I could fairly easily inject the necessary<br>
facilities at least for the basic function set (though I've never<br>
actually done it, so this might be vain overconfidence).<br>
<br>
Anyone else?<br>
<br>
Cheers,<br>
<br>
Markus<br>
<br>
Not sure about anything else.<br>
<br>
Cheers,<br>
<br>
Markus<br>
<br>
</blockquote></div><br></div></div></div>