On Tue, Nov 25, 2014 at 5:44 AM, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote: > On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <fr...@chagford.com> > declaimed the following: > >>I will explain why such a feature would have been useful for me. >> >>My accounting system handles multiple companies. I needed to figure out a >>way to keep each one's data separate from the others. >> >>Originally I supported two databases - PostgreSQL and Sql Server. They both >>have a concept called 'schemas', which handles my requirement elegantly - >>each company's data is stored in its own schema. >> > > We must have a different impression of what a "schema" consists of. As > I learned it, the "schema" basically came down to the statements defining > the form of the data, the rules (triggers) on it, and the relationships > between data items. http://en.wikipedia.org/wiki/Database_schema > > By that definition, SQLite3 does support "schemas" (it's all the DDL > statements used to create the database less the data itself -- which are > DML). So does Firebird, MySQL, Access/JET
The sense here is one of qualified names. In DB2 and PostgreSQL, you can create a database, and then within that, create schemas. You choose a database when you connect, and then you can write code like this: select customer_name, invoice_date from accts.invoices join crm.customers on (customer_id = crm.customers.id) where blah blah The table names are qualified with their schema names. You can also have a "default schema", which is the one used for unqualified table names, which lets you pick one from a group of related data sets: for example, Frank's accounting system would probably make use of that, such that the code needn't care after startup. It'll just select a default schema (or, in PostgreSQL, alter the search_path), and then most of the code will just use unqualified names. Any data that's not company-specific (maybe the application stores UI config data in the database too) can be stored in a dedicated schema and referenced by qualified name. Technically, a table can be referred to as database_name.schema_name.table_name (and then you can add .column_name after that, but I have NEVER seen anyone use the full four-parter!), but in PostgreSQL, the database_name, if present, must be the one that you connected to. In MySQL, there these things that are kinda databases and kinda schemas, are referred to by both names, and in terms of data separation, primarily act like schemas. In SQLite3, there are basically no schemas, AIUI, but you can use databases to do something similar - with the originally-mentioned limitation. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
