Name resolution of SQL objects

Informix®

Informix uses the following form to identify an SQL object:

database[@dbservername]:][{owner|"owner"}.]identifier

The ANSI convention is to use double quotes for identifier delimiters (For example: "customer"."cust_name").

Informix database object names are not case-sensitive in non-ANSI databases. When using double-quoted identifiers, Informix becomes case sensitive.

With non-ANSI Informix databases, you do not have to give a schema name before the tables when executing an SQL statement:

SELECT ... FROM customer WHERE ...

In Informix ANSI compliant databases:

The table name must include "owner", unless the connected user is the owner of the database object.
The database server shifts the owner name to uppercase letters before the statement executes, unless the owner name is enclosed in double quotes.

SQLite

SQLite database object names are case-insensitive. Using double quotes to surround table names in possible. However, the letter case is kept even without double quotes:

sqlite> CREATE TABLE tab1 ( pk INT );
sqlite> CREATE TABLE "TAB2" ( pk INT );
sqlite> CREATE TABLE Tab3 ( pk INT );
sqlite> .tables
TAB2  Tab3  tab1
sqlite> .schema "TAB3" 
CREATE TABLE Tab3 ( pk INT );

In an SQLite, if a prefix is specified as part of an object reference, it must be either "main", or "temp" or the schema-name of an attached database. There is no such concept as user schema in SQLite.

Solution

To write portable SQL, regarding database object names:

Use simple database object names (without any owner/schema prefix)
Do not use double quotes to surround database object identifiers.
If needed, define public synonyms to reference database objects in others databases/schema.
Specify database object identifiers in lowercase.