bucardo/dbdpg — pull request #7 by pilcrow (2014-06-12 13:50:17+00:00)
Title: Better table_info type support:  SYSTEM TABLE, LOCAL TEMPORARY, (INVALID), etc

Issue
---
1. `table_info('', '', '', '%')` is not aware of SYSTEM TABLE and SYSTEM VIEW, however `table_info('', '%', %')` certainly is aware of those types.
2. `table_info` is not aware of LOCAL TEMPORARY objects at all, though pg supports them.
3. `table_info()` ignores invalid types (e.g., 'DUMMY'), meaning that a search for 'OBVIOUSLY_INCORRECT' or a typo'd 'TABEL' [sic] will counter-intuitively return every visible object. There is a (wrong) test case to support this behavior, but the behavior is out of step with other DBDs and, more importantly, contrary to reasonable programmer expectations.

Behavior Changes
---
- `table_info()` type lists supports TABLE, VIEW, SYSTEM TABLE, SYSTEM VIEW, and LOCAL TEMPORARY
- `table_info()` object searching fully supports the above types.
- `table_info()` object searching no longer ignores invalid types — a filter of 'NOSUCH' will return no rows, and 'NOSUCH,LOCAL TEMPORARY' will return only temp objects.
(type filters are strictly matched now ... previously a search for SYSTEM TABLE would have fetched plain TABLE objects)
- *temporary tables* and *temporary views* are both treated as LOCAL TEMPORARY (DBD::SQLite does the same)
(other sessions' temporary objects are suppressed — you can't `table_info()` list another session's temporary view, for example)

Implementation
---
The main `table_info` object list query has been refactored. It's rather simpler to build and to read when finally constructed — we don't worry about type filtering unless we have to, and then in an outer query.

Test cases added.