fco / red Goto Github PK

Some table names are not acceptable, like the ones with - or, for some reason, "commit". Document acceptable table names and check for them in the create-table meta-method.

A lesson taught by all existing attempts at creating ORMs (in any language) but one that has yet to be learned is that while most SQL statements are rather simple, there will be use cases that require some real SQL mastery and if the ORM does not allow for the latter, it's value is greatly diminished.

It seems like every ORM starts out with the simple stuff. How can we make fetching one row from a table simple? How do we allow for boolean expressions in the where clause? Let's make joining simple tables by id key trivial.

These (and a couple more) are the things that ORMs make really simple but then real users come along and ask for more and more complicated things which then get bolted on top of the simple mechanisms. And suddenly you reach the point, where writing the SQL query is the easy part, but getting your ORM to generate that query starts getting really hard or downright impossible. Then you end up with an application that's a crude mixture of ORM code and hand crafted SQL statements combining the disadvantages of both.

The conclusion is that for the API design one should start with the absolute worst and most complicated SQL statement imaginable and find an API that allows one to express this. This statement should at least contain:

• Common Table Expressions (preferably recursive)
• Window Functions
• Correlated subqueries in the select clause
• Subqueries in the from clause
• (Nested) calls to SQL functions with calling conventions that do not like C (i.e. extract(hour from some_timestamp_column)
• Subqueries as arguments to function calls
• Unions
• Type coercion ("foo::int" or "interval '1 day'")
• Grouping and ordering
• Special operators like #> (Get JSON object at specified path in PostgreSQL)
• Conditionals (CASE foo WHEN bar THEN baz ELSE qux END)

I'm sure there are a couple more interesting aspects of SQL, but this should get you started. They are all features that are in use in our code base. The trick will be to make these possible while keeping the trivial cases simple.

To finish, here's an example of a real life query from our production code that DBIx::Class is completely unequipped to handle:

with product_active(product_id, active, changetime, active_end) as (
    select product_id,
        active,
        changetime,
        lead(product_active_history.changetime, 1, now()::timestamp without time zone)
            OVER (
                PARTITION BY product_active_history.product_id
                ORDER BY product_active_history.changetime
            )
            as active_end
    from product_active_history
        join products on product_active_history.product_id = products.id
        join customers on products.customer_id = customers.id
    where products.article_id = 62
        and country_id in (1, 2, 3, 4)
        and products.customer_id not in (885, 840, 246, 362, 3233, 6378)
    order by product_active_history.product_id, product_active_history.changetime
),
valuation_date as (
    select (generate_series('2000-01-01'::date, now(), interval '1 year'))::date as day
)
select *,
    100.0 * (cancelled - lag(cancelled) over (order by day)) / online as cancelled_percent,
    100.0 * (redesign - lag(redesign) over (order by day)) / online as redesign_percent
from (
    select
        day,
        count(nullif(product_active.active_end > valuation_date.day, false)) as finished,
        count(nullif(product_active.active_end > valuation_date.day and product_active.active, false)) as online,
        count(nullif(product_active.active = false and product_active.active_end > valuation_date.day, false)) as offline,
        count(nullif(product_active.active = false and not exists (
            select * from products successor where successor.predecessor_id = product_active.product_id
        ) and product_active.active_end > valuation_date.day, false)) as cancelled,
        count(nullif(
            product_active.active = false
                and exists (select * from products successor where successor.predecessor_id = product_active.product_id)
                and product_active.active_end > valuation_date.day,
            false)) as redesign
    from
        valuation_date
        join product_active on product_active.changetime <= valuation_date.day
    group by day
) as data
order by day

How should I flat a ResultSeq?

Driver should have its own prepare, execute and transaction methods

To use it in a join

Should Person.all.map: { .posts } return a ResultSeq of ResultSeqs?

Use :ver<>?

model CD {
   has           $!artist-id is referencing{ Artist.id };
   has Artist $.author is relationship{ .artist-id };
}
model Artist {
   has UInt $.id is column{ :id };
   has CD   @.cds is relationship{ .artist-id };
}

The trait will receive a block and run it passing: if the type of the attribute is positional the attribute type, else the model’s type. It’s return should be a column that is referencing some other column. It will create a new result seq using that.

I'm a Perl 6 novice that would like to help where able. What are the installation instructions to get started?

Create more tests:

• Relationship
• SQL Translation
• DB Drivers

Create it and make is column{ :id } use it.
Way to use:

::?CLASS.^add-primary-key: { .bla, .ble }

#17

On Pg it should use a db enum, on SQLite it should create a table and an relationship

model Post is rw {
   ...
   has Person $.author is relationship{ .author-id }
}

my $post = Post.^load: :42id;
$post.author = Person.^create: |%data;

This should create a new Person and change that Post's author-id to its id

Make it be useful to test drivers

is id equivalents to is column{ :id, :!nullable }

Today it’s “possible” to do things like:

Artist.where: Artist.id == CD.author-id AND CD.id == Track.cd-id;

But how to do left join, inner join, etc?

Some methods of Model should be moved to that too, where and all for example

Something like:

Person.where({ .id > 30 }).subset: “name”;

And that would only select the name column and return a object with only the name attribute

It shouldn't map relations to objects, it should map relations to language syntax. So, should it be a LSRM?

Maybe an join field too that should be an enum<left right inner ...>

To be used on joins

rename it because it will be used on select (#1 ) too

Use the same base as Red::Driver::SQLite

is serial os equivalent to is column{ :id, :!nullable, :auto-increment }