Geraldization
Catherine Devlin
> Sounds good and I look forward to trying it out soon. On the Gerald
> front I checked in another set of changes this morning and there is only
> one task left on the list for Gerald 0.3;
>
> http://sourceforge.net/apps/trac/halfcooked/milestone/0.3
>
> http://sourceforge.net/apps/trac/halfcooked/query?milestone=0.3&order=priority
I am so happy! Thanks, Andy!
Of course, once I dived in and started using the new code, I realized
we still have Some Issues (TM). Advice please?
1. Objects that aren't your own. In Oracle, JACK may be able to see
tables, etc. that belong to JILL, and have every legitimate reason to
`ls`, `describe`, etc. them. To accommodate this, we're going to have
to collect Gerald schemas for more than just the currently connected
user.
As Gerald is currently written, we would actually need JILL's password
to fetch the schema for Jill.
To figure out a plan, I need to understand better how postgres and
mysql structure ownership of objects. In Oracle, there is one schema
per user. I seem to remember that it's potentially more complex than
that in postgresql.
2. There's some more metadata that I'd like - like LAST_DDL_TIME for
`ls -t` - and it would be nice for the Gerald objects to include that,
too.
3. Keeping metadata up-to-date. If we collect a Gerald schema once
upon making a connection, it will become outdated if table structures,
etc. are changed, whether in the user's session or somewhere else
entirely. Not crucial, but since SQL*Plus metadata queries always
give up-to-date results and I want to be superior to SQL*Plus in every
way, it would be nice to eventually figure out a way to refresh the
Gerald schema as necessary. (It would be easy to let the user trigger
a manual refresh - with a flag on the `ls` call, for example - but I'd
like something "smarter" than that.)
I think we're ultimately going to have to create mixins for Gerald
Schema, Table, etc. objects. I'm fuzzy about how to do that - if a
Gerald Schema has a bunch of Gerald Tables, and I want to say, "Poof!
All your Gerald Tables are now EnhancedSQLPythonTables!", how do I do
that? The phrase "metaclass hacking" is in my head. but I'm not
actually familiar with it. If anybody can point me to a great
tutorial on that topic, I'll be grateful.
Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.org ***
Catherine Devlin
Devlin<catherin...@gmail.com> wrote:
> To figure out a plan, I need to understand better how postgres and
> mysql structure ownership of objects. In Oracle, there is one schema
> per user. I seem to remember that it's potentially more complex than
> that in postgresql.
Okay, I found a really good explanation in postgresql:
http://sql-info.de/postgresql/schemas.html
Now I just need to know how it works in mysql.
Catherine Devlin
>
> 1. Objects that aren't your own. In Oracle, JACK may be able to see
> tables, etc. that belong to JILL, and have every legitimate reason to
> `ls`, `describe`, etc. them. To accommodate this, we're going to have
> to collect Gerald schemas for more than just the currently connected
> user.
>
> As Gerald is currently written, we would actually need JILL's password
> to fetch the schema for Jill.
Okay, it looks like this is not a problem for postgres, since Gerald
schemas accept a schema_name argument. PostgresSchema uses the
schema_name when it queries information_schema views.
For OracleSchema, though, schema_name gets saved into self.name and
then ignored; the DDL queries are issued against user_tables,
user_views, user_sequences, etc., which never give results belonging
to any schema but the currently connected user's own. As written,
there's no way to get at the objects of a different user.
If the queries are changed to all_tables, all_views, all_sequences,
etc., with an added "WHERE owner = (schema.name)" clause, then
OracleSchemas can be used for any schema. (Under the covers, I
believe that the user_* views in Oracle are just all_* views filtered
with WHERE OWNER=USER anyway.)
Andy, I can make that change really easily to oracle_schema.py, if
you'll accept it. It would change functionality, though, if people
call OracleSchema(schema_name="arbitrary-label-that-does-not-match-any-schema-name",
connection_string="oracle:/username:password@host/orcl"); they can do
now against Oracle (only), but with my change, they would get an empty
result.
Alternately, I could add an optional final argument to
OracleSchema.__init__ which would be used to narrow down all_table
queries, leaving the schema_name argument available for an arbitrary
label.
Or, I could just import from a custom version of oracle_schema.py
instead of the genuine Gerald article... but I don't want to do that,
since then we'd have to manually reconcile with all your future
changes.
What do you think?
Catherine Devlin
Well, here it is - I had to make changes in more places than I
expected. Here are a new schema.py and oracle_schema.py, based on the
versions in Gerald's trunk. There are definitely complications in
collecting metadata on other Oracle users' objects. It may be that
I'll ultimately have to request changes in mysql_schema.py, etc. too,
but I'll burn that bridge when I come to it.
Hope you find the suggested changes tolerable... thanks again for all your help!
In other news, I submitted a talk to PyCon. It's mostly on cmd/cmd2,
with sqlpython as an example. It may very well not be accepted -
there are a LOT of good talks in there, and cmd2 is still pretty
obscure - but we'll see.
Anyway, one way or the other, I fully intend to give a lightning talk
on the new superpowered sqlpython at PyCon. The pressure is on! :)
> Catherine,
>
> This looks OK in principle, I just need a little time to get my head around
> how this will work.
>
> Unfortunately I got handed an important (and urgent) project last week so I
> haven't had a lot of time to figure out what to do.
>
> I should get a little spare time this weekend and hopefully spend it working
> out what to do. If it's a lot of change I'll cut a 0.3 release and make the
> change 0.3.5, but if it is simple and doesn't require large amounts of
> refactoring (and unit test changes) I'll put it into 0.3
>
> Regards,
> Andy
> --
> From the desk of Andrew J Todd esq - http://www.halfcooked.com/
Andy Todd
> Hi Andy!
>
> Well, here it is - I had to make changes in more places than I
> expected. Here are a new schema.py and oracle_schema.py, based on the
> versions in Gerald's trunk. There are definitely complications in
> collecting metadata on other Oracle users' objects. It may be that
> I'll ultimately have to request changes in mysql_schema.py, etc. too,
> but I'll burn that bridge when I come to it.
>
> Hope you find the suggested changes tolerable... thanks again for all your help!
>
> In other news, I submitted a talk to PyCon. It's mostly on cmd/cmd2,
> with sqlpython as an example. It may very well not be accepted -
> there are a LOT of good talks in there, and cmd2 is still pretty
> obscure - but we'll see.
>
> Anyway, one way or the other, I fully intend to give a lightning talk
> on the new superpowered sqlpython at PyCon. The pressure is on! :)
>
> On Wed, Aug 19, 2009 at 6:26 AM, Andy Todd<and...@halfcooked.com> wrote:
>> Catherine Devlin wrote:
Your timing was spooky. This morning I managed to get some time and
released Gerald 0.3
This doesn't contain your patch (I'm not that quick ;-) but I have been
doing a little thinking about how to support SQLPython and still keep
the original use case for Gerald supported as well. I'm reluctant to
hack the 'Schema' class because it is designed (and used) for managing
schemas; generating DDL, discovering changes and even occassionally
generating migration code.
So my solution is to create a separate class (called 'User') that is
similar to 'Schema' but that contains every object that a user has
permissions for. I've created a ticket and scheduled it as the only
component for release 0.3.1;
http://sourceforge.net/apps/trac/halfcooked/ticket/16
With your permission I'll use your patch as a starting point and get
cracking on this.
Catherine Devlin
So my solution is to create a separate class (called 'User') that is similar to 'Schema' but that contains every object that a user has permissions for. I've created a ticket and scheduled it as the only component for release 0.3.1;
Andy Todd
>
Before I add the 'User' class I fixed some bugs in the 0.3 release.
Today I released Gerald 0.3.1 and it is available at the project page
and from PyPI:
http://halfcooked.com/code/gerald/
http://pypi.python.org/pypi/gerald
The next release will be 0.3.5 and will include the new 'User' class.
Catherine Devlin
This doesn't contain your patch (I'm not that quick ;-) but I have been doing a little thinking about how to support SQLPython and still keep the original use case for Gerald supported as well. I'm reluctant to hack the 'Schema' class because it is designed (and used) for managing schemas; generating DDL, discovering changes and even occassionally generating migration code.
So my solution is to create a separate class (called 'User') that is similar to 'Schema' but that contains every object that a user has permissions for. I've created a ticket and scheduled it as the only component for release 0.3.1;
The same reasoning applies to postgresql - for instance, people can see things in the Public schema even when they're not the owner. I think that, following the trail you blazed with the User class for Oracle, it would be really easy to parrot it and make something analogous for postgresql. I can write it up if you like, maybe tomorrow.
Everybody else... PyOhio is finally over and I can turn my attention to making sqlpython usable again. :)
Andy Todd
Happy to do so. As a first step I've created a ticket;
https://sourceforge.net/apps/trac/halfcooked/ticket/27
Now all that is needed is some code and tests. I'll see if I can spend
some time on it this weekend but that shouldn't stop anyone else having
a crack ...