Database File Format > Database Naming Conventions
Before I go into details of the SQLite engine, I will first present database naming conventions and database file structure in the following two subsections.
When an application tries to open a database by making a call to the sqlite3_open API function, it does so by giving the function the name of the database file. The file name can be the relative path name with respect to the current working directory, or the full path name starting from the root of the system file tree. Any regular file name accepted by the native file system is good. There are, however, two notable exceptions:
If the file name is a C language NULL pointer (i.e., 0), SQLite creates and opens a new temporary file;
If the file name is ":memory:" SQLite creates an in-memory database.
In either exception case, when the application closes the database connection, the database becomes extinct, i.e., the database is not persistent.
SQLite chooses all temporary file names at random. The names start with sqlite_ followed by 16 random alphanumeric characters. The files are stored in a standard native temporary file directory. SQLite tries out directories in the order (1) /var/tmp, (2) /usr/tmp, (3) /tmp, (4) the current working directory.
In either of the above-mentioned ways of opening databases (file, temporary database, or in-memory database), the database (created and/or) opened by SQLite is internally named as the main database.
Internally, the database file name is not a database name. They are two different but related concepts in SQLite. By using the SQLite attach command, you can associate the same database file to a database connection as different database names. You can apply operations on the same file via these database names. You may refer to the SQLite homepage to know more about the semantics of attach.
SQLite maintains a separate temporary database for each database connection the application has opened with sqlite3_open; the database is named the temp database. The temp database stores temporary objects such as tables and their indexes. (Applications can use these two names, i.e., main and temp, in their queries. For example, select * from temp.table1 returns all rows from tablel in the temp database and not from the main database. The catalog name for the temp database is sqlite_temp_master.) The temporary objects are only visible within the same database connection (not in other connections to the same database file in the same thread, process, or other processes). SQLite stores the temp database in a separate temporary file that is distinct from the main database file. It deletes the temporary file when the application closes the connection to the main database.