On Thursday, 3 March 2016 at 18:48:08 UTC, Chris Wright wrote:
You were a bit vague before. I interpreted you as saying "just
offer a range and an array-like API, and then you can use it
with std.algorithm". But if you meant to offer an API that is
similar to std.algorithm and also array-like, that's more
feasible.
I agree I could be better in describing the concept. But I just
sketched the idea.
You're still left with the task of transpiling D to SQL.
If someone wants to use SQL in its *full power* no D API nor any
other language will suffice. Mainly because it will be always a
traslation layer . The only we can to is to provide an aid like
things suggested by Andrei (sql parser, value binding, etc).
This model does not work with CouchDB.
I don't know CouchDB so I can't comment.
You must avoid using std.algorithm and std.range functions
assiduously because they would offer terrible performance.
For big data in db, plain vanilla std.algorithm won't be
insufficient. I agree.
* No support for complex queries.
Not sure what you mean by complex queries. Also I think the
API allows arbitrary complex queries.
Aggregates, especially with joins. Computed fields.
Regarding computed fields and other database vendor specific
features you are right.
But on the other hand aggregations and joins can be represented
as objects and proxies of objects.
* No support for joins.
Can be done by @attributes or other linking functionality
between DbCollections.
With attributes, you need users to define aggregate types
instead of just using Row and the like. That's ORM territory.
I don't like ORM with respect to SQL. But quasi object database
which can look similar to ORM is not a problem for me.
At a previous job I maintained an internal BI site that exposed
50-100 different queries, each with their own set of result
fields. We didn't want to use ORM there; it would have been
cumbersome and inappropriate.
I can see your point. But the problem can be solved by not using
SQL.
Also, that assumes that you will always want a join when
querying a table. I maintained an application once, using ORM,
in which we sometimes wanted an eager join and sometimes wanted
a lazy one. This posed a nontrivial performance impact.
Something like DbProxy would handle lazy "joins".
I'm not sure ORM would be a candidate for phobos.
As I don't plan to use an (traditional) ORM I'm not involved.
However if other people would find it worthy I don't object.
* No support for projections.
You mean something like referring to part of the item's
fields? I see no problem here.
Let me point you to the existence of the TEXT and BLOB
datatypes. They can each hold 2**32 bytes of data in MySQL.
This is something a DbProxy would handle. Eventually:
struct OrginalObject
{
int id;
string bigString;
}
struct StrippedObject
{
int id;
}
then
auto collA = db.collection!OrginalObject("Big");
auto collA = db.collection!StrippedObject("Big");
In the second line the string is not fetched.
I'm not splitting those off into a separate table to port my
legacy database to your API. I'm not dragging in multiple
megabytes of data in every query.
If you're going full ORM, you can add lazy fields. That adds
complexity. It's also inefficient when I know in advance that I
need those fields.
* In your implementation, updates must bring every affected
row over the wire, then send back the modified row.
In my implementation there is no wire (that's why I call it
embedded). However I thought we talk about API and not
particular implementation. I don't see how this API excludes
RPC. Query strings (e.g. SQL) can be provided in old fashioned
way.
I'm running a website and decide that, with the latest changes,
existing users need to get the new user email. So I write:
UPDATE users SET sent_join_email = FALSE;
-- ok; 1,377,212 rows affected
Or I'm using your database system. If it uses std.algorithm, I
have to iterate through the users list, pulling each row into
my process's memory from the database server, and then I have
to write everything back to the database server.
Depending on the implementation, it's using a database cursor
or issuing a new query for every K results. If it's using a
database cursor, those might not be valid across transaction
boundaries. I'm not sure. If they aren't, you get a large
transaction, which causes problems.
If your database system instead offers a string-based API
similar to std.algorithm, you might be able to turn this into a
single query, but it's going to be a lot of work for you.
For client-server approach I agree with the above. For embedded
design (as in my project) this is not a case.
* Updates affect an entire row. If one process updates one
field in a row and another one updates a different field, one
of those
writes gets clobbered.
I think this is just a "must have" for any db engine. I don't
see how it applies to the proposed API other than any
implementation of db engine has to handle it properly.
Without transactions, MySQL supports writing to two different
columns in two different queries without those writes
clobbering each other.
That's handling it properly.
Designing a good locking mechanism will be a challenging task,
that is what I'm sure :)
When I say DbCollection should behave similar to an ordinal
array I don't mean it should be an ordinal array.
* The API assumes a total ordering for each DbCollection.
This
is not valid.
I don't know what you mean here. Example would be good.
opIndex(size_t offset) assumes the database supports a
one-to-one mapping between offsets and rows.
[...]
This is a terrible usage pattern, but by offering opIndex and
length operations, you are recommending it.
I don't recommend it. I just added it for evaluation. I'm aware
it only works when the collection is not mutated. I think the
same goes for all shared collections (also those in memory)
Finally, IMO any DB API will be biased toward one solution.
Cheers
Piotrek