Ben Faga wrote:
> 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?
>
>
In the long run it probably should do.
>>> 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?
>
>
I'm not sure that understanding what the PG functions do is a good idea
in principle, but it might be needed for a few select cases.
Yes we do have somewhere to store it - the extra info place in the field
that MySQL modules uses to store things like character set or unsigned-ness:
=head2 extra
Get or set the field's "extra" attibutes (e.g., "ZEROFILL" for MySQL).
Accepts a hash(ref) of name/value pairs to store; returns a hash.
$field->extra( qualifier => 'ZEROFILL' );
my %extra = $field->extra;
=cut
>>> 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?
>
ISTR that \. ends the input - its coming from any lines between the
"FROM stdin;" and the "\." as CSV. So yes, SQLT can probably ignore this.
>
>>> 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
