Re: Table case sensitivity with SQL::Statement
On Tue, Dec 6, 2011 at 5:54 AM, Jens Rehsack rehs...@googlemail.com wrote: 2011/12/2 Brendan Byrd sineswi...@gmail.com: Well, as long as it's reflected as a schema all throughout, that's fine. However, I'm not convinced that it's not going to strip out the schema from the SQL, unless DBD::Sys is overloading the affected subs. It follows a completely different concept - it takes the things you want to prefix before identifier names in $dbh private attributes. Attribute names encode table name - see http://search.cpan.org/dist/DBD-Sys/lib/DBD/Sys.pm#Metadata for details. This name construction is not finalized, it could be extended if necessary. I'm still not able to even install this via CPAN, and I'm fine with using S:S / D:D:SE. Okay, so why does mine need to go in the DBD::Sys::Plugin namespace, and not: DBD::File DBD::CSV DBD::RAM DBD::Chart DBD::PO DBD::Google DBD::Amazon DBD::Mock DBD::Excel DBD::DBM etc., etc., etc. ? Well, you want to fetch values from an snmp daemon? So you're not going to read it from a file. Because of this, everything derived from DBD::File doesn't care. DBD::Google and DBD::Amazon are unmaintained, DBD:RAM is temporarily a wrapper around S::S temp tables and DBD::Mock - well, it's clear why it's something unwanted for your goal, isn't it? Who says S:S requires files? It has methods for pulling individual rows, seeking, inserting, etc. That could apply for anything. Sure, DBD::Google / Amazon are unmaintained, but it shows that S:S can go outside the bounds of a file. Why not take the additions you have for DBD::Sys and put them in S:S? It just seems to be a better home and a better name for code that parses the SQL and defines the data types. Remaining is now DBD::AnyData - but it's depreciated because of no time. It needs some separation between storage and coding layers and some more interfaces (eg. access structure). So it's not a good point to start, too. Yeah, though I'm still using it, for lack of a better table importer. I just discovered that D:AD suffers from the same quoted tables problem. It doesn't know what to do with something like SELECT * FROM types, despite having a table called types. I can patch it unless you have a better direction on what kind of module I should use to pass an ARRAY table. And if you're going to say DBD::Sys again, can you at least provide some sample code on how that would work? I'm still not understanding the concept, outside of it being an abstract /proc DBD. Granted, I imagine some of that has to do with timeframes (since DBD::Sys appears to be a newer platform), but there seems to a lack of uniformity with it all. I'm not really blaming anybody for that, but it's just has ended up that way. Four different subclasses overloads from three different CPAN packages seem to link together the abstract DBD object code. Yet there is no indication on which one to start. Four different subclasses? o.O From three different CPAN packages? Abstract DBD object? Like you said yourself: DBD::Sys -- DBI::DBD::SqlEngine -- DBI::SQL::Nano -- SQL::Statement Which are in three different CPAN packages. By abstract DBD object, I mean the non-standard form of a database. Not Oracle, MS SQL, MySQL, but files, trees/objects, and other abstract forms of 2D/3D relational data. S:S makes that possible, but why is the code fragmented all over creation, overloading itself upon itself? How is somebody supposed to figure out how to create interfaces if there is no indication on if I should be following SQL::Statement::Embed, DBI::DBD, or some undocumented method on using DBD::Sys? And all of this custom code per driver that lies OUTSIDE of the driver. DBI needs a private variable extension. DBIx::...::Storage::DBI apparently needs a module for each DBD. (Yet there isn't a generic one for D:D:SE or S:S.) SQL::Dialects::Role needs its own module, but at least THAT is extensible from within the DBD. You don't need that (SQL::Dialects::Role)! You might not understand this, but step back. And I can't help you with any kind of DBIx integration - and I wouldn't recommend to bundle DBD::* distributions with DBIx::*::Storage::* modules. DBI - Yes, I do, if I need to use private variables DBIC - Yeah, I know that's another topic and probably another mailing list, but I am trying to use these for DBIC. S:D:R - Usually, yes. If I want to change the data types, I'll need to create a new Role. If I want to change the Reserved Words, I need to create a new Role. Same goes for removing any of the other sections. Just get_table_name, get_col_names, and collect_data? You have to provide all data at once - see DBD::Sys::Plugin and DBD::Sys::CompositeTable for reasons. Then that's a bit too simple for my needs. If I'm querying a few tables from 300 hosts (which is not an uncommon request), I'd like to be able to get the data as fast as the
Re: Table case sensitivity with SQL::Statement
2011/12/2 Brendan Byrd sineswi...@gmail.com: On Thu, Dec 1, 2011 at 1:27 AM, Jens Rehsack rehs...@googlemail.com wrote: On Wed, Nov 30, 2011 at 4:14 AM, Jens Rehsack rehs...@googlemail.com wrote: But, we still need an outlet to put these things in. I keep hearing about a I see no reason to do this _now_. Nothing you desire needs S::S changes, only the way you desire it. That's why I tried to lead you another way yesterday. Well, as long as it's reflected as a schema all throughout, that's fine. However, I'm not convinced that it's not going to strip out the schema from the SQL, unless DBD::Sys is overloading the affected subs. It follows a completely different concept - it takes the things you want to prefix before identifier names in $dbh private attributes. Attribute names encode table name - see http://search.cpan.org/dist/DBD-Sys/lib/DBD/Sys.pm#Metadata for details. This name construction is not finalized, it could be extended if necessary. Remember the very first important thing you learned when you digged how to write a new DBD: Don't do it! It's still important to remember this. If it's not necessary, avoid it. Okay, so why does mine need to go in the DBD::Sys::Plugin namespace, and not: DBD::File DBD::CSV DBD::RAM DBD::Chart DBD::PO DBD::Google DBD::Amazon DBD::Mock DBD::Excel DBD::DBM etc., etc., etc. ? Well, you want to fetch values from an snmp daemon? So you're not going to read it from a file. Because of this, everything derived from DBD::File doesn't care. DBD::Google and DBD::Amazon are unmaintained, DBD:RAM is temporarily a wrapper around S::S temp tables and DBD::Mock - well, it's clear why it's something unwanted for your goal, isn't it? Remaining is now DBD::AnyData - but it's depreciated because of no time. It needs some separation between storage and coding layers and some more interfaces (eg. access structure). So it's not a good point to start, too. Granted, I imagine some of that has to do with timeframes (since DBD::Sys appears to be a newer platform), but there seems to a lack of uniformity with it all. I'm not really blaming anybody for that, but it's just has ended up that way. Four different subclasses overloads from three different CPAN packages seem to link together the abstract DBD object code. Yet there is no indication on which one to start. Four different subclasses? o.O From three different CPAN packages? Abstract DBD object? Well, I don't see where you are. Start analyzing from http://search.cpan.org/dist/DBD-Sys/ by clicking on DBD::Sys - and from there follow the linked pages. If you have additional questions, please (as long as you don't ask to blame CPAN module structure and way of installation / package management) ask with some more details ;) And all of this custom code per driver that lies OUTSIDE of the driver. DBI needs a private variable extension. DBIx::...::Storage::DBI apparently needs a module for each DBD. (Yet there isn't a generic one for D:D:SE or S:S.) SQL::Dialects::Role needs its own module, but at least THAT is extensible from within the DBD. You don't need that (SQL::Dialects::Role)! You might not understand this, but step back. And I can't help you with any kind of DBIx integration - and I wouldn't recommend to bundle DBD::* distributions with DBIx::*::Storage::* modules. Heck, I'm not even against re-writing the SNMP module for DBD::Sys. (Especially since I can't seem to get rid of this limit_dialect error, and as soon as I correct some things to use D:D:SE, now IT is f'ing complaining about the schema.table format.) It just seems like it would be an odd fit in front of all of those other plugins that are clearly only related to filesystems and OS stats. Well, other plugins are very welcome. But I've created some common examples only (remember: free time project - not got paid for it). Plus, I'm still not getting where this is supposed to start. DBD::Sys::Plugin's POD is barely a page, and most of the example plugins almost look too simple. They are! There is no rocket science. Just get_table_name, get_col_names, and collect_data? You have to provide all data at once - see DBD::Sys::Plugin and DBD::Sys::CompositeTable for reasons. What about fetch_row or foreign_key_info? fetch_row is not required - you can loop over the list of all rows you got. And foreign_key_info is a nice to have for SQL::Statement v2 and DBI::DBD::SqlEngine v2 (with data dictionary support). I would accept a patch for DBD::Sys what adds foreign key information like DBI::DBD::SqlEngine or DBD::File handles it (Merijn can probably say more about that, he introduced a PoC for this in DBD::CSV and it became generalized during DBD::File / DBI::DBD::SqlEngine refactoring. What if I don't want to collect all of the data at once? You probably want to have a look at Coro in that situations. What if I want write support? I just don't see how a complex module fits here, and nor do I see
Re: Table case sensitivity with SQL::Statement
On Thu, Dec 1, 2011 at 1:27 AM, Jens Rehsack rehs...@googlemail.com wrote: On Wed, Nov 30, 2011 at 4:14 AM, Jens Rehsack rehs...@googlemail.com wrote: But, we still need an outlet to put these things in. I keep hearing about a I see no reason to do this _now_. Nothing you desire needs S::S changes, only the way you desire it. That's why I tried to lead you another way yesterday. Well, as long as it's reflected as a schema all throughout, that's fine. However, I'm not convinced that it's not going to strip out the schema from the SQL, unless DBD::Sys is overloading the affected subs. Remember the very first important thing you learned when you digged how to write a new DBD: Don't do it! It's still important to remember this. If it's not necessary, avoid it. Okay, so why does mine need to go in the DBD::Sys::Plugin namespace, and not: DBD::File DBD::CSV DBD::RAM DBD::Chart DBD::PO DBD::Google DBD::Amazon DBD::Mock DBD::Excel DBD::DBM etc., etc., etc. ? Granted, I imagine some of that has to do with timeframes (since DBD::Sys appears to be a newer platform), but there seems to a lack of uniformity with it all. I'm not really blaming anybody for that, but it's just has ended up that way. Four different subclasses overloads from three different CPAN packages seem to link together the abstract DBD object code. Yet there is no indication on which one to start. And all of this custom code per driver that lies OUTSIDE of the driver. DBI needs a private variable extension. DBIx::...::Storage::DBI apparently needs a module for each DBD. (Yet there isn't a generic one for D:D:SE or S:S.) SQL::Dialects::Role needs its own module, but at least THAT is extensible from within the DBD. Heck, I'm not even against re-writing the SNMP module for DBD::Sys. (Especially since I can't seem to get rid of this limit_dialect error, and as soon as I correct some things to use D:D:SE, now IT is f'ing complaining about the schema.table format.) It just seems like it would be an odd fit in front of all of those other plugins that are clearly only related to filesystems and OS stats. Plus, I'm still not getting where this is supposed to start. DBD::Sys::Plugin's POD is barely a page, and most of the example plugins almost look *too* simple. Just get_table_name, get_col_names, and collect_data? What about fetch_row or foreign_key_info? What if I don't want to collect all of the data at once? What if I want write support? I just don't see how a complex module fits here, and nor do I see what complexities it solves for me. -- Brendan Byrd/SineSwiper sineswi...@gmail.com
Re: Table case sensitivity with SQL::Statement
2011/11/29 Brendan Byrd sineswi...@gmail.com:
Found a potential bug, but it looks deliberate, so I wanted to see why it
was coded that way. In SQL::Statement, open_tables is using $self-tables()
for mostly everything, which will contain the correct case for all of the
table names. (IE: FOOBAR = foobar and FOOBAR = FOOBAR) However, when
calling open_table (the DBD method), it uses the following code:
undef $@;
eval {
my $open_name = $self-{org_table_names}-[$count];
$t-{$name} = $self-open_table( $data, $open_name,
$createMode, $lockMode );
};
So, it's deliberately grabbing the untouched (pre-corrected) table name from
org_table_names and passing it to open_table. What's the reasoning behind
this?
The reason is quite simple. The corrected table name is not corrected from
the point of view of the storage backend (in case of DBD::CSV the file
names on disk),
they are corrected from point of view of SQL standard and SQL indentifier
attribute (lower case all, upper case all, leave them as they were).
The storage backend might need to do it's own correction, as DBD::File
does.
Should we worry about it or not?
Well, I don't worry about this for SQL::Statement 1.xxx.
If we correct it to enforce proper
ODBC case-sensitivity, that may suddenly break the DBD and users of those
DBDs that are expecting to query a CSV DB via SELECT * FROM
UPPERCASE_filename and getting an error because it's now going to convert
that to lowercase. If we don't correct it, there's the potential for two
different cased files to have the same table key and screwing stuff up with
JOIN. (Yeah, it's rare for somebody to actually be using both FOOBAR and
foobar in the same query...)
I hope this wouldn't happen, because SQL::Statement should detect (and
internally
correct /FOOBAR/ and /foobar/ into /foobar/, but in fact, I'm not sure.
You might go ahead and add a test for this.
I guess this is delegating the enforcement of these standards to the DBD, or
should it be higher up?
This is handled by the base class of all SQL::Statement based DBD's, in
DBI::DBD::SqlEngine. See sql_identifier_case and sql_quoted_identifier_case
$dbh attribute description.
DBD::File acts upon the values of those attributes, but other DBD's might
implement other actions.
I agree in fundamental issues in S::S and DBI::DBD interaction with standards.
The reason to create DBI::DBD::SqlEngine was primarily to introduce an
abstraction layer to hide SQL::Statement details and issues. From view of
any DBD derived from DBI::DBD::SqlEngine, details should be hidden in
SqlEngine. All changes we make must not affect this API for now.
We all agree (all we talked 2 years ago named Merijn Brand, Martin Evans,
Tim Bunce and myself), that we might need one big shot for a new
DBI::DBD::SqlEngine API - but for stability reasons please no regular
improvements. I see how long it took for example for Steffen Winkler to
improve his DBD::MO (or myself for DBD::AnyData) to the new
Pure-Perl-DBD-API.
Further, I've seen you got a lot of DBI/DBD related information yesterday
(I was busy at $work and couldn't talk on IRC). What was still missing
was enlightening you about DBI::DBD::SqlEngine and DBD::File/DBD::DBM
internal details. Probably we find a day were we can talk, but for the
moment the guys guided you yesterday can help you further patching
your own repository checkouts and create tests for your patches and
guide you how to test dependent modules.
I'm the last one who will reject good patches with tests on SQL::Statement
and approvals for DBI and tests on DBI, DBD::CSV and DBD::Sys
running fine.
There is a reason for the http://search.cpan.org/dist/Bundle-Test-SQL-Statement/
distribution - it's test coverage. Probably Tux as DBD::CSV maintainer can agree
that issues between SQL::Statement changes and Pure-Perl-DBD's are
getting rarer since these Tests exist.
Best regards,
Jens
--
Brendan Byrd/SineSwiper sineswi...@gmail.com
Computer tech, Perl wizard, and all-around Internet guru
Re: Table case sensitivity with SQL::Statement
2011/11/30 Brendan Byrd sineswi...@gmail.com: Please don't leave ML out ;) Adding back again. On Wed, Nov 30, 2011 at 4:14 AM, Jens Rehsack rehs...@googlemail.com wrote: The reason is quite simple. The corrected table name is not corrected from the point of view of the storage backend (in case of DBD::CSV the file names on disk), they are corrected from point of view of SQL standard and SQL indentifier attribute (lower case all, upper case all, leave them as they were). The storage backend might need to do it's own correction, as DBD::File does. That's fine. I just added this to open_table: # enforce proper capitalization (even though we probably don't need it) $table = index($table, '') == -1 ? lc $table : $table; Which was partly borrowed from S:S' original lowercasing routine. Might be worthy of throwing this into the starter open_tables sub in S:S:Embed docs, since other folks might want the SQL level table name. I think I shall go to move the content of S::S::Embed fully into DBI::DBD::SqlEngine and simply refer from S::S::E to DBI::DBD::SE. Don't cramp at S::S - DBI makes the rules! So DBI::DBD::SqlEngine and subsequent docs must be read. If we correct it to enforce proper ODBC case-sensitivity, that may suddenly break the DBD and users of those DBDs that are expecting to query a CSV DB via SELECT * FROM UPPERCASE_filename and getting an error because it's now going to convert that to lowercase. If we don't correct it, there's the potential for two different cased files to have the same table key and screwing stuff up with JOIN. (Yeah, it's rare for somebody to actually be using both FOOBAR and foobar in the same query...) I hope this wouldn't happen, because SQL::Statement should detect (and internally correct /FOOBAR/ and /foobar/ into /foobar/, but in fact, I'm not sure. You might go ahead and add a test for this. Well, that's my point. If you have two tables FOOBAR and foobar in a JOIN, technically, from SQL's POV, they are the same table, and S:S might balk at a unsupported self-join. Though, the user actually wanted to hit two different files. If the user wants that, he must quote the identifiers! It's a (very) minor edge case, but I'll look at a test for that. The test case should prove correct behaviour of S::S - not obscure and wrong behaviour. I guess this is delegating the enforcement of these standards to the DBD, or should it be higher up? This is handled by the base class of all SQL::Statement based DBD's, in DBI::DBD::SqlEngine. See sql_identifier_case and sql_quoted_identifier_case $dbh attribute description. DBD::File acts upon the values of those attributes, but other DBD's might implement other actions. Well, the separation of the SQL and storage layers makes sense. I guess that's what you were driving at with your conversation on IRC: better separation of proper layers. It just not simply a question of making sense, it's a critical to clarify responsibilities. S::S delegates responsibility for reading / persisting data to the table and the table instrumentation to a derived engine instance. I agree in fundamental issues in S::S and DBI::DBD interaction with standards. The reason to create DBI::DBD::SqlEngine was primarily to introduce an abstraction layer to hide SQL::Statement details and issues. From view of any DBD derived from DBI::DBD::SqlEngine, details should be hidden in SqlEngine. All changes we make must not affect this API for now. We all agree (all we talked 2 years ago named Merijn Brand, Martin Evans, Tim Bunce and myself), that we might need one big shot for a new DBI::DBD::SqlEngine API - but for stability reasons please no regular improvements. I see how long it took for example for Steffen Winkler to improve his DBD::MO (or myself for DBD::AnyData) to the new Pure-Perl-DBD-API. I guess that's fine. I understand how there's potentially 50 different DBDs out there that could have issues with backwards compatibility with S:S and D:D:SE, though it has to get broken at some point. I see S:S (or the whole SqlEngine suite) as something that can potentially be as powerful as a real DB. Throw everything into DBIC with a Federated DB of all of your data, relationalized to all the right points, and you end up with a single interface for ALL data. (Sorry, that's another thread...) Well, not really. I pointed DBD::Sys to you yesterday. Keep in mind, you can extend it with plugins. Keep in mind, those plugins can do several things. One plugin could query SNMP sources, another plugin can query oracle db stats, etc. pp. But, we still need an outlet to put these things in. I keep hearing about a 2.0 version, and if it doesn't already, let's get the repository built. Break all we want as this new version isn't exactly meant to be backwards compatible (but still have the same goal in mind). I see no reason to do this _now_. Nothing you desire needs
