I have never really understood the "Catalog" concept. From the standard:
ISO/IEC 9075-1:2003 (E) " 4.2.8.1 Catalogs A catalog is a named collection of SQL-schemas, foreign server descriptors, and foreign data wrapper descriptors in an SQL-environment. The mechanisms for creating and destroying catalogs are implementation-defined. " Ok - fair enough. A catalog is a collection of schemas....now this: " 4.2.8.3 The Information Schema Every catalog contains an SQL-schema with the name INFORMATION_SCHEMA that includes the descriptors of a number of schema objects, mostly view definitions, that together allow every descriptor in that catalog to be accessed, but not changed, as though it was SQL-data. " mm, one information_schema per catalog. I don't mind, but I guess this means that once you commit to implementing catalogs, you have to go all the way, and do this too...if you don't, generic clients will get confused (use case: query or reporting tool that accesses drizzle via a JDBC driver. Driver says the RDBMS supports catalogs, client uses this information and then relies on an information_schema being present....) Now the puzzling thing is, I don't see anything in the standard that says how a user/role knows about catalogs...they only mention schema objects: " 4.6.12 Privileges A privilege represents a grant, by some grantor, to a specified grantee (which is either an authorization identifier, a role, or PUBLIC), of the authority required to use, or to perform a specified action on, a specified schema object. " Now, what they say about schemas and owners is this: " 4.2.8.2 SQL-schemas An SQL-schema, often referred to simply as a schema, is a persistent, named collection of descriptors. Any object whose descriptor is in some SQL-schema is known as an SQL-schema object. A schema, the schema objects in it, and the SQL-data described by them are said to be owned by the authorization identifier associated with the schema. " So, in short, if you want to adhere to the standard for this one, you should associate ownership with schemas, not catalogs. How users should be authorized for catalogs, is not clear. Nor is it clear to me what the benefit is of a catalog object, other than that it is just another level. However, if you do implement catalogs, you should also provide an information_schema implementation that is compatible with the existence of catalogs. Personally, having catalogs is not high on my list. Only RDBMS i have worked with that has it, is SQL server and to me it is more a hassle than it's worth (IMO). I do think a schema owner is a useful concept. The standard seems to imply schemas are always owned by an "authorization" which is to all intents and purposes an account (AFAICS). Oracle also implements a per schema owner and MS SQL agrees here too (as in, each schema has an owner) PS I am not saying drizzle should do what Oracle and MS do, but these are shining examples of popular RDBMS-es and for developers it helps if things like this work the same across the board. kind regards, Roland. On Fri, Mar 19, 2010 at 6:20 PM, Jay Pipes <[email protected]> wrote: > Yep, fair enough. Thanks for the excellent explanation! > > -jay > > On Fri, Mar 19, 2010 at 1:18 PM, Eric Day <[email protected]> wrote: >> The argument I could see for not simply using a 'user' is that >> there may be multiple users that want to map to the same catalog >> namespace (essentially an account). So 'jay' and 'eric' have access >> to the 'drizzle.org' catalog, but we may have a different set of >> permissions. The 'account' entity needs to have some object with it, >> and you most likely don't want to reference other user accounts for >> this. For example: >> >> catalog >> schema >> table >> >> drizzle.org >> blog >> posts >> comments >> authors >> wiki >> pages >> accounts >> >> gearman.org >> wiki >> pages >> accounts >> >> Now users can map to any set of permissions for the catalog, schema, >> table, or any combination of them. Now you can have multiple users >> access drizzle.org catalog, or a single user access both drizzle.org >> and gearman.org catalogs. >> >> -Eric >> >> On Fri, Mar 19, 2010 at 12:10:39PM -0400, Jay Pipes wrote: >>> How would that be different from a user, then? >>> >>> In other words, why not just split the innodb buffer pool by the user >>> id (which, BTW, would require a major overhaul of InnoDB...)? >>> >>> What I'm asking is what would be the benefits of one more level of >>> taxonomy when the user ID already allows for such categorization? >>> >>> Cheers! >>> >>> jay >>> >>> On Fri, Mar 19, 2010 at 12:05 PM, Brian Aker <[email protected]> wrote: >>> > Think multi-tenancy. A user can create as many schemas as they like, and I >>> > can split the innodb pool up per catalog. >>> > >>> > Cheers, >>> > --Brian >>> > >>> > On Mar 19, 2010, at 8:52 AM, Jay Pipes <[email protected]> wrote: >>> > >>> >> NULL. >>> >> >>> >> I actually don't think catalogs are all that useful, FWIW... >>> >> >>> >> -jay >>> >> >>> >> On Thu, Mar 18, 2010 at 9:47 PM, Roland Bouman <[email protected]> >>> >> wrote: >>> >>> >>> >>> Hi! >>> >>> >>> >>> On Thu, Mar 18, 2010 at 11:25 PM, Brian Aker <[email protected]> wrote: >>> >>>> >>> >>>> Do we want to just default the value to NULL? >>> >>> >>> >>> SQL standard says it should be NULL in case there is no support for >>> >>> catalogs. >>> >>> >>> >>> >>> >>> >>> >>> -- >>> >>> Roland Bouman >>> >>> http://rpbouman.blogspot.com/ >>> >>> >>> >>> Author of "Pentaho Solutions: Business Intelligence and Data >>> >>> Warehousing with Pentaho and MySQL", >>> >>> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html >>> >>> >>> >>> _______________________________________________ >>> >>> Mailing list: https://launchpad.net/~drizzle-discuss >>> >>> Post to : [email protected] >>> >>> Unsubscribe : https://launchpad.net/~drizzle-discuss >>> >>> More help : https://help.launchpad.net/ListHelp >>> >>> >>> > >>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~drizzle-discuss >>> Post to : [email protected] >>> Unsubscribe : https://launchpad.net/~drizzle-discuss >>> More help : https://help.launchpad.net/ListHelp >> > > _______________________________________________ > Mailing list: https://launchpad.net/~drizzle-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~drizzle-discuss > More help : https://help.launchpad.net/ListHelp > -- Roland Bouman http://rpbouman.blogspot.com/ Author of "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
