Andrei,

So if I'm understanding you correctly, if structs are used, and there's a database change, then the app has to be rebuilt. If Variants, then only the app's config file has to be updated (or the app can look in the database to get such information) - have I got this right?

Steve

On Oct 7, 2011, at 10:14 AM, Adam Ruppe wrote:

> In my database.d, I used a database row struct to provide both integer and key based indexes.

Funny… I have a ResultSet object which contains an array of Column objects.  Here's the basic idea:

class ResultSet {
    static class Column {
        int index();
        string name();
        int size();
        int length();
        void[] data();
        bool opEquals(NullValue);
        T as(T)(); // return raw if T matches underlying or call to!(T) on field as appropriate
    }

    size_t numCols();
    bool nextResultSet();
    bool next();
    bool prev();
    bool first();
    bool last();
    void update(); // SQLSetPos(rowNumber=0)
    Column opIndex(size_t);
    // easy enough to add opIndex(string)
}

Does your Row equate to the ResultSet as above?

On Oct 7, 2011, at 11:27 AM, Steve Teale wrote:

> Andrei,
> 
> So if I'm understanding you correctly, if structs are used, and there's a database change, then the app has to be rebuilt. If Variants, then only the app's config file has to be updated (or the app can look in the database to get such information) - have I got this right?

I'd say yes, though qualify this by saying that a properly designed app will target only views and stored procedures, which insulate the app from schema changes.

On 2011-10-07 16:11, Steve Teale wrote:
> I'm thinking that for each database that gets covered there will
> need to be two modules, like:
>
> etc.c.mysql
> etc.mysqld
>
> The etc.c.xxx modules would be completely different between databases -
> just translations of the necessary header files. The xxxd modules
> would be as similar as possible consistent with allowing the strong
> points of individual database systems to show through, and the weak
> points of others to be avoided. I don't think all should be reduced
> to some LCD.
>
> These modules should attempt to make a good range of capabilities
> available to the D programmer, but they not have to be all encompassing.
> Those users who want to do really fancy things can drop back to the
> low-level interface. They should probably have the following capabilities:
>
> 1) Establishing and closing database connections.
>
> 2) The capability to execute literal SQL statements - execSQL()
> if you like. Some of these will generate result sets, of which more below.
>
> 3) The capability to create prepared statements with in and out
> parameters and association of the parameters with a source, and
> then to execute these. This breaks down into several components/
> capabilities, which could be labeled:
>
> 3a) createPreparedStatement() - marshal parameters, associate them
> with a sourceand have the server prepare the statement.
>
> 3b) execStatement() - for those SQL statements that don't have a
> result set.
>
> 3c) execStatementIncremental()/getNext() - execute the prepared statement,
> then fetch the result rows one at a time into some kind of result set.
>
> 3d) execStatementAll() - execute the prepared statement and get all
> the resulting rows into some kind of result set.
>
> 3e) (maybe) execScalar() - do the whole sequence prepare, execute,
> and get a single value result set placed into a D variable.
>
> 3f) (maybe) execStoredProcedure() - another 'do the whole thing'
> capability TBD.
>
> It is when we come to the nature of the result sets that there is
> likely to be dissent. I favor arrays of structs, but we should
> probably do arrays of arrays of variants too for those situations
> where structures can't be sorted out at compile time. There needs
> to be some symmetry between what is used here, and what can be used
> as input to operations such as a prepared insert. It is of course
> vital that this part of each middle layer produce exactly the same
> kind of results. Otherwise the top layer could become difficult.
>
> On top of this set of two modules for each database, I envisage a
> higher-level module - etc.dbops - that provides a bunch of convenience
> templates for various common database operations, spanning the databases.
> Once the middle layer is in place, this top layer should be relatively
> easy to implement. It should be noted though that all these database
> wrappers will be monstrously difficult to test.
>
> I am at the point with MySQL where I can get the result of a plain
> old query into an array of a checked structure type. I have the
> prepared statement stuff, and know how the result will be created from
> a prepared query (the execStatementAll() case) - I just have to plow
> through a bit more binding and fetching.
>
> This is probably rather general and vague, but I would like to get
> comments so we can iterate toward a practical design.
>
> Thanks
> Steve

I don't know if this will be necessary to have a special function for but it would be nice to be able to get batches of results from a query. Or this might be solved by the range interface.

-- 
/Jacob Carlborg

Sean Kelly wote:
> Does your Row equate to the ResultSet above?

Fairly similar.

Mine looks something like this:

interface ResultSet {
        // name for associative array to result index
        int getFieldIndex(string field);
        string[] fieldNames();

        bool empty();
        Row front();
        void popFront();
        int length();
}

struct Row {
    // the actual column is returned as a string - probably should
    // change that, but mysql, postgres, and sqlite all offered that
    // and it works for me, so I went with it
    string opIndex(size_t idx) {}
    string opIndex(string columnName) {}
    int opApply(...) {}

    private string[] data;
}



Then, each of the database implementations use that little ResultSet interface  to feed Row structs back to the user code.

On 10/7/11 1:27 PM, Steve Teale wrote:
> Andrei,
>
> So if I'm understanding you correctly, if structs are used, and there's a database
> change, then the app has to be rebuilt.

Yes. Touched to update the structs and rebuilt.

> If Variants, then only the app's config
> file has to be updated (or the app can look in the database to get such
> information) - have I got this right?

Yah. The exact behavior depends on the assumption the app makes about the resultset. Consider:

auto result = db.sql(queryString);
enforce(result.front[0].type == typeid(double));
...

In this case you'd pretty much need to touch the code if the queryString returns something else than a double in the first column.


Andrei

Andrei Alexandrescu wrote:
> On 10/7/11 12:02 PM, Piotr Szturmaj wrote:
>> Did you see http://pszturmaj.github.com/ddb/db.html ? It maps tuples,
>> structs and arrays in similar manner.
>
> I did. Looks good. Why do you need DBRow? You could simply use
> Variant[], the given struct, or Tuple directly.

Sure, that's a good idea. I used DBRow because I wanted generic set/setNull for any of its base types, but it could be done with function templates anyway...

The only thing that makes DBRow useful is opIndex access to Variant[] fields, like row["field"].

On 10/07/11 15:00, Piotr Szturmaj wrote:
> Andrei Alexandrescu wrote:
>> On 10/7/11 12:02 PM, Piotr Szturmaj wrote:
>>> Did you see http://pszturmaj.github.com/ddb/db.html ? It maps tuples,
>>> structs and arrays in similar manner.
>>
>> I did. Looks good. Why do you need DBRow? You could simply use
>> Variant[], the given struct, or Tuple directly.
>
> Sure, that's a good idea. I used DBRow because I wanted generic
> set/setNull for any of its base types, but it could be done with
> function templates anyway...
>
> The only thing that makes DBRow useful is opIndex access to Variant[]
> fields, like row["field"].

Oh that makes sense, thanks.

Andrei