search

SchemaPlus / schema_plus_core

Provides an internal extension API to ActiveRecord, in the form of middleware-style callback stacks
MIT License
7 stars 29 forks source link

Materialized View Tables Incompatible with Table Command Regex #6

Open erikreedstrom opened 8 years ago

erikreedstrom commented 8 years ago

The regex used to parse table commands identified below does not currently allow for creating a table based on a query. This prevents the use of tables as materialized views.

Example

create_table :long_query,
             as: "SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id")

generates:

CREATE TABLE "long_query" AS
  SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id

https://github.com/SchemaPlus/schema_plus_core/blob/master/lib/schema_plus/core/sql_struct.rb#L12-L14

ronen commented 8 years ago

Oh dear yes good point. I don't think it'd be too bad to fix this, but I'm a bit swamped now... any chance you'd be willing to make a PR?

Thinking out loud I think the regex could be enhanced like this:

%r{
          ^
          (?<command>.*\bTABLE\b) \s*
            (?<quote>['"`])(?<name>\S+)\k<quote> \s*
            ( 
                 (
                    \( \s*
                      (?<body>.*) \s*
                    \) \s*
                   (?<options> \S.*)?
                 )
                 |
                 (
                  (?<as> \s+ AS \s+)
                  (?<body>.*) \s*
                 )
            )
            $
}mxi

and parse! could set

self.create_as? = !!m[:as]

and in the assemble method example create_as? to choose the proper reconstruction. The create_as? boolean would need to be added to the doc as part of the API.

Plus would need to set up a test for this case in spec/sql_struct_spec.rb (Of course do that first and see that it fails!)

...any interest in doing this? If not I'll get to it when I get a chance but might not be several weeks.