Re: Allowing for schema.table format in SQL::Statement

On 23/11/2011 20:33, Brendan Byrd wrote:
> On Wed, Nov 23, 2011 at 9:15 AM, Jens Rehsack<[email protected]>wrote:
>
>> 2011/11/23 Brendan Byrd<[email protected]>:
>>> Well, I'd actually be using schema as a MIB name.
>> Which is horribly wrong. Tables names like systemGroup are always
>> full qualified. There is no other oid named systemGroup as the one
>> below mib_ii.
>>
> You do run into odd cases like RFC1213-MIB::sysDescr versus
> SNMPv2-MIB::sysDescr.  Yes, I know RFC1213 is obsolete, but you still have
> old devices using stuff like atTable.  The MIB name is optional, but
> putting it in would ensure it would actually hit the v2 version of
> sysDescr.  And who knows what kind of bass-ackwards behavior is buried in
> some of those vendor MIBs.
>
> Still, I'll have to think about whether the support for those edge cases is
> worth it or not...
>
>
>>>   For example,
>>> IF_MIB.ifTable or SNMPv2_MIB.systemGroup.  Dashes are converted to
>>> underscores, since MIBs don't use underscores and SQL doesn't like
>> dashes.
>>
>> This could be easily handled by an DBI::DBD::SqlEngine::Table derived.
>> It's fully documented an illustrated by DBD::File how to create internal
>> aliasing for table names.
>>
> Are we talking about the schema separator (dot), or the dash/underscores?
> I've tackled the latter, and I'm trying to use S::S to play nice with the
> former.
>
>
>>> As far as testing, that's why I'd like to add this in as a flag.
>> I still do not understand what kind of flag you want to add and what
>> behavior should be changed when activated.
>>
> The filtering of schemas within S::S.  Basically, any time you specify
> something like schema.table, S::S strips the schema, and the driver has no
> idea (based on the Statement/Parser structure/methods) that the table ever
> had a schema.  This would be a flag called 'dont_strip_schema' or something
> like that.
>
> When you write a Pure-Perl DBD using SQL::Statement, you're well
>> advised to derive from DBI::DBD::SqlEngine (unless you need to deal
>> with files, then derive from DBD::File).
>>
>> And DBI::DBD::SqlEngine falls back to DBI::SQL::Nano in case
>> no suitable SQL::Statement has been found.
>>
> Well, I'm using SQL::Parser during the ::db::prepare process, and I'm not
> sure if Nano has all of the parsing I need.  I was going to make S::S a
> requirement of the module, anyway.
>
>
>> You're invited to join active discussion on irc://irc.perl.org/#dbi
>> if you can't figure out all details on your own.
>>
> Will do.  Might have some other questions, anyway.
>
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
>> A: Top-posting.
>> Q: What is the most annoying thing in e-mail?
>>
> Sorry, just realized that GMail is using this vertical line for quoting,
> which I can use.  I generally don't like top-posting, either.
>
> And shall
>   SELECT * FROM .1.3.6.1.2.1.4.1.36539.20.1.3"
> be the same as
>   SELECT * FROM smDiskIoTable
> (read: should smDiskIoTable resolved into it's oid before querying)?
> Shall SQL::Statement identify both names as the same table or
> shall both have incidentally the same content?
>
> OIDs will only be used at the last step within the backend.  I'm going to
> enforce proper table names only.  (After all, if we don't have the MIB,
> we're shouldn't be poking around data points we don't understand or can
> even get data types from.)
>
>> Tim [who is happy to know almost nothing about snmp]
> Lucky guy :D
>
> SNMP isn't so difficult as it sounds, but it's different than SQL
> and 1:1 mapping wouldn't work from my perspective.
>
> Probably some SNMP plugins for DBD::Sys might a better idea.
>
> I disagree.  I've worked with SNMP tables in this fashion before, for the
> purposes of translating a big batch of tables into CSV (which then gets
> uploaded to Oracle).  It works, even if there isn't a table for every
> single object.  I've found that the *accessible* objects boil down to four
> types:
>
>
>     1. Tables (NoAccess), which go to a Entry (NoAccess), which have indexes
>     and columns, etc.  IE: your typical table layout.
>     2. Table->Entry->Columns with a RowStatus field that allows for advanced
>     updates/deletes/etc.
>     3. "Global" tables, where the index is always 0, such as systemGroup.
>     4. Notification/trap groups.
>
> All of these could be accessed via SQL with a translatable interface.
> Heck, I'm about 90% finished with the read-only version of this module, and
> I'm working out the bugs in getting Catalyst::Helper::Model::DBIC::Schema
> to produce a set of Result modules for it, complete with the foreign keys
> and all (since I have that support in foreign_key_info).
>
Brendan,

Don't take any of the comments to your postings too harshly - not that 
I'm particularly saying there were any harsh ones. For a start, you are 
writing new DBDs and to my knowledge there have not been many people 
doing that for some time now - personally, I welcome anyone prepared to 
write a new DBD as someone who can contribute to a well used API and 
possibly see issues the rest of us have long forgotten about. The fact 
you've got to 90% of a read-only version is a great accomplishment and I 
welcome your contribution. Tux/Sno Merijn/Jens have a lot of knowledge 
of DBD::File and S::S etc and you'll usually find both of them on 
irc.perl.org / #dbi where I'm sure you could get help/comments/feedback.

Just really wanted to add my note of encouragement - welcome to the fold 
and thanks.

Martin