How Table-Oriented Programming and Red ORM can Work Together

[wayland]

Hi!

I put together some thoughts on how Red ORM and Table-Oriented Programming can work together.
I cover three main ideas:

• What Table-Oriented Programming is (so people know what I'm talking about)
• What Red ORM is (because I just skimmed some of the doco, and would like to be corrected if I've misunderstood)
• How we might be able to work together (just some thoughts); including disadvantages (because I naturally spot problems)

Hoping this is a suitable place to post this; my idea is to spark a bit of discussion, so that we'll have some ideas on where we overlap and where we differ. which could be handy.

What is TOP (Table-Oriented Programming)?

The goal of TOP is to make it easy to deal with Tables in Raku.

Make it Easy

• There will be objects (types) that represent tables, tuples, fields, databases, and the like.
• Regardless of the backend format, the frontend interface will remain the same. This means that you can switch from CSV files to an SQL database with fairly minimal code changes
• There will be operators to do things with tables. This will include the regular set operators (despite these being lists rather than sets), and will also include some of the relational operators, such as joins, but also things like sort/map/grep/reduce

Tables

A table can be in any format; this specifically includes SQL and spreadsheets, but also eg. the output of many unix commands (whitespace-separated).

Data-Oriented Programming

TOP is part of a larger family of Data-Oriented Programming, which will also
include:

• Tree-Oriented Programming (think of a mashup of XPath, XSLT, Raku Grammars, but also Makefiles and Puppet)
• Logic Programming (not sure about where this is going, but will likely involve predicates being a reverse function call, where the capture and the signature are reversed, and all multi-candidates are called, and return an array of the results)

What is Red ORM (Object-Relational Model)?

ORM

An ORM maps programming language objects onto a relational data storage model. The main reasons for doing this are:

• Most programming languages don't support tables well; this gives them capabilities in this direction (specifically, to query DB tables as if they where arrays of objects)
• For larger amounts of data, the Relational model tends to be more efficient

Red ORM

Red ORM uses models (like classes) with attributes on the traits that specify the SQL data type into which the attribute should be translated. This is a pretty powerful model for an ORM.

There's also talk about using RakuAST to translate Raku code into SQL, so that more of the code can be run on the SQL server when this is advantageous.

Working Together

The advantages

• Potentially collaborate on RakuAST material
• Use of TOP Operators will have people writing their code in SQL-like ways, which can be more easily translated to RakuAST
• Potentially collaborate on variety of backends
• Both have a high level of syntactic integration into Raku (unlike eg. DataQueryWorkflows, which is using strings of a sublanguage to do queries)

The Disadvantages

• Both Red ORM and TOP are works in progress, and collaboration may be hampered by one being ready to take on a project when the other isn't yet ready
• Specifically, the TOP operators are not yet implemented. For plans, see TOP Operators
• The code probably isn't very efficient yet; the Postgres code, for example, uses cursors, which are not recommended for most uses
• TOP doesn't support MySQL at this point (but does support Postgres)
• Likely to head in different directions. When the main TOP features are implemented, the lead will work on Tree-Oriented Programming for a while, before returning to TOP (this will enable the use of tree-oriented programming when dealing with eg. HTML tables and OpenDocument Spreadsheet tables)

Having said that, there will still be areas where we can work together.

[FCO]

The main intention with Red queries is to query DB tables as if they where arrays of objects, for example, if you have a post table (and it's equivalent model) and you want to get all posts that title starts with "A", for example, you would run: @posts.grep: *.starts-with: "A". Or if you want author name of all posts older than 3 days: @posts.grep(*.published-at < DateTime.now.earlier: :3days).map: *.author.name. That works if @posts is an array populated with Post objects, or if it is: my @posts := Post.^all (where Post.^all (or Post.^rs) is a ResultSeq representing the rows on the post table).

One thing that I think would be a good start, would be a way to convert ResultSeqs to TOP.

[wayland]

• Thanks! Have adjusted the first bullet point under the ORM section to incorporate some of your wording.
• With ResultSeq, I assume that:
  • You're referring to https://github.com/FCO/Red/blob/master/lib/Red/ResultSeq.rakumod
  • You're thinking that we should do something like role Red::ResultSeq[Mu $of = Any]; also does Relation ?

[FCO]

No, I was more thinking on having a CAST to a TOP type.

[wayland]

I'm not seeing anything about CAST in Raku, and in Red, it's listed only in the documentation as an SQL reserved word. Can you expand a bit?

Thanks! 🙂

[FCO]

Sure, I mean something like:

Post.^all.Relation

And sorry, by cast I meant coercion (Raku/problem-solving#227 (comment))

[wayland]

Hmm.

To clarify, the model I'm using is that things like Table and View and TupleSet all do the Relation role.

Each of them allows fetching Tuples via the Positional role.

So might your example instead be:

Post.Relation

...and/or...

Post.^all.Tuple

...or have I misunderstood what you were doing there?

[FCO]

Something like that, but just to be clear, Post represents the table post, but not its data... the data from the table post is represented by Post.^all as if it was an array of Post objects (now Post being just a class). I'm saying that because (even not knowing much about Top) I suppose Tuple would not be the best way to represent Post.^all, I would think that would be an array of tuples... or maybe a TupleSet (I'm just guessing by its name...)

[wayland]

Oh, I think I see. So the return value of Post.^all is an Array[Post]?

[wayland]

Also, I've spotted some big differences:
Tables and columns:

• Red ORM has a separate type for each table, and maps the columns to attributes
• TOP maps all tables to Table, and all tuples to Tuple, and maps the columns to Associative parameters. At some point I expect to give each table its own Signature to do type checking

(Scalar) Types:

• Red ORM has you explicitly declare the database types. TOP tries to guess from the Raku types

Each serves its own purpose well 🙂

[FCO]

The returned type from Post.^all is ResultSeq[Post] that can be used as if it was Array[Post] (but will translate what you do to SQL and run it o DB when you try to get its values).

Red also tries to guess column types by its Raku types, but the user can define a different type if they want.

[wayland]

@FCO Thanks! Yeah, TOP is a little the same. does Positional accesses the rows, and does Associative accesses the columns.