-
The idea is that multiple users using the same database can cleanly
separate their tables, views (stored procs, etc) by prefixing them with
their login, so jack's jack.importantTable is distinct from jill's
jill.importantTable. There are
administrative benefits ('Jack left and we don't like his work; can we kill
everything he did?' Ans: 'Yes, let
me just
drop his schema..', with aliases, jill.importantTable can be made available to
everybody as
'importantTable', permissions can be hung off schemas). The common notation
(jill.importantTable)
would map to databasename.tablename in the current sqlite arrangement.
-
This doesn't really make a lot of sense for an embedded database.
-
I could use this. Im trying to use sqlite as a 'fake database'
that i can use in testing suite. For sqlite to be a good 'fake' of
something like Oracle it would help a lot if it had the ability to do stuff
like 'select * from blah.PERSON'. In this case the 'blah' is the schema name.
PERSON is the table name.
Another example: 'select zipcode from blorg.ADDRESS'. blorg is the schema
name, ADDRESS is the
table name.
right now it is giving 'no such database as blah' or 'no such database as
blorg'. i try to 'create database blah' but of cousre that doesnt work either.
at the very least, it could accept table names that have a '.' in them. this would fake schemas good enough for me. right now it doesnt seem to allow it.
-
You can fake this syntax if you split the "schemas" off into seperate
files, then do an ATTACH
DATABASE blorg.db AS blorg; SELECT zipcode FROM blorg.address;
The above misses the whole point. Schema's are not "users".
Often, people don't use database schemas to their full advantage. By schema, I'm not talking about the design details of tables and columns. Standard ANSI SQL defines a schema to be a collection of database elements. Earlier versions of MS SQL Server didn't support the schema, but had the user in the syntax. For example: dbo.TableName
Starting with SQL Server 2005, SQL Server now supports Server.Database.Schema.Table.
Many times, people separate logically grouped tables into their own databases. Architecturally, this is non ideal. One bad thing about this is that database names are likely to become hard coded. For example, USE [MyDB]
and SELECT C FROM MyDB..Table
This is bad because now there can't be multiple instances of the system on one server. Normally, one can simply create multiple databases on the same server. For example, a database for dev, test, stage, demo, etc. With multiple databases and hard coded db names, each instance of the application must be on a different server.
A better approach is to have multiple different schemas in the same database. This results in
SELECT C FROM MySchema.Table
So, SQLLite should support schemas, not because of the server issue, which is unique to MS SQL server, but because it helps separate logical groupings of tables.