Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Schema support #45

Open
4 tasks
nickl- opened this issue Nov 8, 2013 · 4 comments
Open
4 tasks

Schema support #45

nickl- opened this issue Nov 8, 2013 · 4 comments

Comments

@nickl-
Copy link
Member

nickl- commented Nov 8, 2013

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.

@nickl-
Copy link
Member Author

nickl- commented Nov 8, 2013

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.

@nickl-
Copy link
Member Author

nickl- commented Nov 8, 2013

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.

@alganet
Copy link
Member

alganet commented Dec 3, 2013

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 =)

@cristopher-rodrigues
Copy link

Hello,

I wonder how this implementation to support schemas?
Use postgresql and would be very nice to have this in respect!

Hold and hug ..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants