Materialized View Tables Incompatible with Table Command Regex #6

erikreedstrom · 2015-11-17T17:15:06Z

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 · 2015-11-18T10:55:56Z

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.

ronen mentioned this issue Jun 10, 2016

Support Rails 5 #9

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Materialized View Tables Incompatible with Table Command Regex #6

Materialized View Tables Incompatible with Table Command Regex #6

erikreedstrom commented Nov 17, 2015

ronen commented Nov 18, 2015

Materialized View Tables Incompatible with Table Command Regex #6

Materialized View Tables Incompatible with Table Command Regex #6

Comments

erikreedstrom commented Nov 17, 2015

ronen commented Nov 18, 2015