Respect
Schema support
The bits I was allowed to see from the ANS SQL standard ISO/IEC 9075, was the results from a search query for "SQL table name" and I quote.
ISO/IEC 9075 specifies that an object such as an SQL-invoked routine, a user-defined type, a domain, a table, a view, or a privilege shall be part of exactly one schema.
At #36 @robinson-rso raised an interesting question, can we handle schemas. Which is at first I thought nothing strange of surely PDO will take care of that, but does it? PostgreSQL's use of schemas is not all that different to using multiple MySQL databases simultaneously where you would prefix the table name with the database name.
The following tasks items should be actioned.
- [ ] Can we use multiple databases out of the box- create tests to prov it
if not
- [ ] Can we deal with PostgreSQL schema in connection string? this won't solve multiple databases/schemas but at least it's not completely defunct.
- [ ] Investigate how others have approached the problem
- [ ] Discuss and design the ideal solution which will allow multiple databases/schemas
@Respect and the friends of Respect what are your thoughts on this? Please help with suggestions, research and test results.
This is more or less what I was looking for:
From ISO/IEC 9075-1
4.3 Tables A base table is either a schema object or a module object. A base table whose descriptor is in a schema is called a created base table, and may be either persistent or temporary (though its descriptor is persistent in either case). A persistent base table contains 0 (zero) or more rows of persistent SQL-data.
This was also interesting:
8.6.1 Requirements for SQL applications The term “SQL application” is used here to mean a collection of compilation units that contains one or more of: — SQL statements. — Invocations of SQL/CLI routines. — Invocations of externally-invoked procedures. A conforming SQL application shall be processed without syntax error, provided that all of the following is satisfied: — Every SQL statement or SQL invocation is syntactically correct in accordance with ISO/IEC 9075. — The schema contents satisfy the requirements of the SQL application. — The SQL-data conforms to the schema contents. — The user has not submitted for immediate execution an SQL-statement that is not syntactically correct. A conforming SQL application shall not use any additional features, or features beyond the level of conformance claimed. A claim of conformance by an SQL application shall also state: — What implementation-defined elements and actions are relied on for correct performance. — What schema contents are required to be supplied by the user.
I did a patch for ZF to add support to catalogs (similar to schemas) on MSSQL: http://framework.zend.com/issues/browse/ZF-6665. Should be easy.
It is also possible to emulate the "foo.bar" notation on SQLite using the ATTACH DATABASE command. It is even possible to attach another :memory: database. Testing for the query construction is feasible =)
Hello,
I wonder how this implementation to support schemas? Use postgresql and would be very nice to have this in respect!
Hold and hug ..
