Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

Schemas

        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.