# SQLite C Interface

The database connection and prepared statement objects are controlled by a small set of C/C++ interface routine listed below.

sqlite3_open()
sqlite3_prepare()
sqlite3_step()
sqlite3_column()
sqlite3_finalize()
sqlite3_close()

## object types
- sqlite3 → The database connection object. Created by sqlite3_open() and destroyed by sqlite3_close().
- sqlite3_stmt → The prepared statement object. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().
## functions
- sqlite3_open() → Open a connection to a new or existing SQLite database. The constructor for sqlite3.
- sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the database. The constructor for sqlite3_stmt.
- sqlite3_step() → Advance an sqlite3_stmt to the next result row or to completion.
- sqlite3_column() → Column values in the current result row for an sqlite3_stmt.
- sqlite3_finalize() → Destructor for sqlite3_stmt.
- sqlite3_close() → Destructor for sqlite3.
- sqlite3_exec() → A wrapper function that does sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and sqlite3_finalize() for a string of one or more SQL statements.

---
### One-Step Query Execution Interface

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

---
## SQLite features
- Shared Cache Mode → Version 3.3.0 and later supports the ability for two or more database connections to share the same page and schema cache. This feature is useful for certain specialized applications.
- URI Filenames → The names of database files can be specified using either an ordinary filename or a URI. Using URI filenames provides additional capabilities, as this document describes.
---
### [Uniform Resource Identifiers](https://www.sqlite.org/uri.html)
* URI Filenames In SQLite
>Beginning with version 3.7.7 (2011-06-23), the SQLite database file argument to the sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces and to the ATTACH command can be specified either as an ordinary filename or as a Uniform Resource Identifier or URI. The advantage of using a URI filename is that query parameters on the URI can be used to control details of the newly created database connection. For example, an alternative VFS can be specified using a "vfs=" query parameter. Or the database can be opened read-only by using "mode=ro" as a query parameter.
* what is URI Filenames
>If URI filename interpretation is enabled, and the filename argument begins with "file:", then the filename is interpreted as a URI.
[examples](https://www.sqlite.org/c3ref/open.html#urifilenameexamples)

---
[Limits In SQLite](https://www.sqlite.org/limits.html#:~:text=Maximum%20Number%20Of%20Tables%20In,table%20in%20the%20query%20optimizer.)
Maximum Number Of Rows In A Table

The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 281 terabytes will be reached first. A 281 terabytes database can hold no more than approximately 2e+13 rows, and then only if there are no indices and if each row contains very little data.

Maximum Database Size

Every database consists of one or more "pages". Within a single database, every page is the same size, but different databases can have page sizes that are powers of two between 512 and 65536, inclusive. The maximum size of a database file is 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,000 gibibytes).

This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion.

---
sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, size) interface.

Maximum Number Of Rows In A Table
**
About:** The theoretical maximum number of rows in a table is 264 or 18446744073709551616. This limit is unreachable since the maximum database size of 281 terabytes will be reached first. A 281 terabytes database can hold no more than approximately 2e+13 rows, and that too only if there are no indices and if each row contains very little data.

Maximum Number Of Pages In A Database File
About: The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to the value 1073741823, is the maximum number of pages allowed in a single database file. An attempt to insert new data that would cause the database file to grow larger than this will return SQLITE_FULL. SQLite is able to limit the size of a database file to prevent the database file from growing too large and consuming too much disk space. The largest possible setting for SQLITE_MAX_PAGE_COUNT is 4294967294. When used with a maximum page size of 65536, this gives a maximum SQLite database size of about 281 terabytes.
How to change the limit: The max_page_count PRAGMA can be used to raise or lower this limit at run-time.

https://appscms.com/blog/general/size-limits-of-the-sqlite-database

auto_vacuum
https://www.sqlite.org/pragma.html#pragma_auto_vacuum