view as model

[jonathanstowe]

Sometimes an SQL query may be too complex to express as a Red expression or there is a DB view that expresses some key business logic that may be shared between applications or some such.

It would be nice if it was possible to define a model as a view with something like:

model Foo is view('someview') {
     has Str $.name           is column;
     has  Str $.something  is column;

      # define the view for ^create-table etc
      method definition(--> Str ) {
           q:to/EOSQL/;
            -- SOME HORRIBLE SQL
            EOSQL
      }
}

It would also be nice if there was some is virtual trait on the model definition such that rather than using the view name as if it were a table, the query itself is used as the "table" source like:

SELECT name, something
FROM ( -- SOME HORRIBLE SQL ) AS someview

which would be useful if, for adminstrative reasons, an application user is unable to create a real view on the DB.

[FCO]

Those seem great ideas! Thanks!

[FCO]

Maybe that's doable by overriding the ResultSeq for that model...

[FCO]

And maybe creating a Red::AST::PlainSelect is Red::AST::Select

[FCO]

I'm working on it. We already have this (not committed yet):

➜  Red git:(master) ✗ raku -I. -MRed -e '

model Bla is virtual-view { has $.id is serial; has $.value is rw is column; method sql { "select 1 as id, 42 as value" } }

red-defaults default => database "SQLite";
schema(Bla).create;
my $*RED-DEBUG = True;

.say for Bla.^all
'
SQL : SELECT
   "bla".id , "bla".value
FROM
   ( select 1 as id, 42 as value ) as bla
BIND: []
Bla.new(id => 1, value => 42)

[FCO]

now we have is view and is virtual-view. Committed.

[FCO]

@jonathanstowe please, close this issue if the implemented change is enough.

[jonathanstowe]

Looks good to me.

[FCO]

Thanks!