I asked Ken what he thought of the Postgres 8.2.4 problems that I was
seeing and he sent me a reply. He described how to fix the
schema-qualified table names (which I have committed).
I wanted to pass along some of the other comments he had (along w/
comments of my own), to spur discussion and maybe get some clarity for
how each problem should be handled.
Hopefully, since I'm sending this on a Friday, it won't be completely
ignored. I hope. I hope. I hope.
On Mon, 2007-11-12 at 16:35 -0600, Ken Youens-Clark wrote:
> On Nov 12, 2007, at 4:05 PM, Ben Faga wrote:
> >
> > 2. "ALTER SEQUENCE"
> >
> > The "ALTER SEQUENCE" command isn't defined in the parser. The
> > following
> > causes an error.
> >
> > ALTER SEQUENCE inventory_inventory_id_seq OWNED BY
> > inventory.inventory_id;
> >
> > A simple fix is to add definition catch this case but I am unsure what
> > to do with it. Should it just be discarded?
> >
> > Possible definition:
> >
> > alter : ALTER /sequence/i /[^;]+/ ';' { 1 }
>
> > Alternative definition as catch-all:
> >
> > alter : ALTER WORD /[^;]+/ ';' { 1 }
>
> I don't see a "sequence" rule anywhere, so this would be something
> new. If you're just trying to skip over it, then the latter is just
> fine, I think. If you want to store the ownership somewhere and use
> it later, then you'll probably want to mess around in the
> SQL::T::Schema::Field class. You would probably have to add an
> "owner" attribute to that and then modify the PG Parser to set this
> properly. But it seems like you just want to skip over the definition.
Does SQL::Translator need to deal with ownership?
>
> >
> > 3. SELECT that calls a function.
> >
> > In the Postgres 8.2.4 dump, there is a SELECT statement that calls a
> > function in Postgres.
> >
> > SELECT pg_catalog.setval('inventory_inventory_id_seq', 1, false);
> >
> > This isn't currently handled at all. The only possibilities that I
> > can
> > think of are to either simply ignore it or to implement definitions
> > for
> > each possible Postgres function.
> >
> > Thoughts?
>
> Again, if you just want to skip over this rule (because you're trying
> to use SQLT for something else, like graphing the schema or make a
> Class::DBI module), then just create a rule along the lines of the
> "ALTER WORD" example above to grab everything from a /select/i to not-
> a-semi-colon and move on (as long as a semi-colon can never be a valid
> character inside the SELECT statement, in which case you need to be
> prepared to recognize only the non-escaped version as the statement
> end).
>
I think that what the the select/function is doing is basically setting
the base number in the sequence (I suspect this is used when a sequence
is already in use).
Do we want SQLT to "know" what each built in function does?
Does SQL::Translator even have a place to store sequence-type
information? Should it?
> >
> > 4. COPY statement and "\."
> >
> > Also not handled are copy statements.
> >
> > pg_dump produces the following two statements:
> >
> > COPY inventory (inventory_id, cases, lastorder, lastinvoice) FROM
> > stdin;
> > \.
> >
> > I'm not even sure how this snippet is supposed to be used. I'm
> > guessing
> > that the "\." slurps lines from stdin. The PosgreSQL parser doesn't
> > handle that.
>
> Same thing as #3.
I don't really even understand why this is in the dump. It seems like
it is relying on an external file (ie something that SQLT can't know
about).
Should this just be skipped?
> >
> > 5. REVOKE/GRANT on Schema
> >
> > There is no definition for revoking or granting permissions on a
> > schema
> > as in the following lines.
> >
> > REVOKE ALL ON SCHEMA public FROM PUBLIC;
> > REVOKE ALL ON SCHEMA public FROM postgres;
> > GRANT ALL ON SCHEMA public TO postgres;
> > GRANT ALL ON SCHEMA public TO PUBLIC;
>
>
> Same as #3 if you just want to ignore. I don't think SQLT really
> handles permissions. No reason it couldn't, of course. Those aren't
> as tough to translate as, say, procedures. You could add this to the
> code, or just create rules that would recognize the validity of the
> statements but do nothing with them.
What do others think? Just ignore GRANT/REVOCE permissoins on schemas
or handle them somehow?
Thanks for any input.
Ben
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
--
sqlfairy-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
