Category: sqlite

SQLite – Quick Guide ”; Previous Next SQLite – Overview This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that”s growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain. What is SQLite? SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly. Why SQLite? SQLite does not require a separate server process or system to operate (serverless). SQLite comes with zero-configuration, which means no setup or administration needed. A complete SQLite database is stored in a single cross-platform disk file. SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. SQLite is self-contained, which means no external dependencies. SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads. SQLite supports most of the query language features found in SQL92 (SQL2) standard. SQLite is written in ANSI-C and provides simple and easy-to-use API. SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). SQLite A Brief History 2000 – D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program. 2000 – In August, SQLite 1.0 released with GNU Database Manager. 2011 – Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database). SQLite Limitations There are few unsupported features of SQL92 in SQLite which are listed in the following table. Sr.No. Feature & Description 1 RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented. 2 FULL OUTER JOIN Only LEFT OUTER JOIN is implemented. 3 ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported. 4 Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. 5 VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. 6 GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system. SQLite Commands The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature − DDL – Data Definition Language Sr.No. Command & Description 1 CREATE Creates a new table, a view of a table, or other object in database. 2 ALTER Modifies an existing database object, such as a table. 3 DROP Deletes an entire table, a view of a table or other object in the database. DML – Data Manipulation Language Sr.No. Command & Description 1 INSERT Creates a record 2 UPDATE Modifies records 3 DELETE Deletes records DQL – Data Query Language Sr.No. Command & Description 1 SELECT Retrieves certain records from one or more tables SQLite – Installation SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X. Install SQLite on Windows Step 1 − Go to SQLite download page, and download precompiled binaries from Windows section. Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files. Step 3 − Create a folder C:>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files. Step 4 − Add C:>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result. C:>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> Install SQLite on Linux Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine. $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite − Step 1 − Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section. Step 2 − Run the following command − $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure –prefix=/usr/local $make $make install The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above. Install SQLite on Mac OS X Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps − Step 1 − Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section. Step 2 − Run the following command − $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure –prefix=/usr/local $make $make install The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command − $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises. SQLite – Commands This chapter will take you through simple and useful commands used by SQLite programmers. These commands

SQLite – Triggers ”; Previous Next SQLite Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. Following are the important points about SQLite triggers − SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on one or more specified columns of a table. At this time, SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence, explicitly specifying FOR EACH ROW is optional. Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted, or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows. The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification, or removal of the associated row. Triggers are automatically dropped when the table that they are associated with is dropped. The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename not database.tablename. A special SQL function RAISE() may be used within a trigger-program to raise an exception. Syntax Following is the basic syntax of creating a trigger. CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN — Trigger logic goes here…. END; Here, event_name could be INSERT, DELETE, and UPDATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name. Following is the syntax for creating a trigger on an UPDATE operation on one or more specified columns of a table. CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN — Trigger logic goes here…. END; Example Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we create newly as follows (Drop COMPANY table if you already have it). sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); To keep audit trial, we will create a new table called AUDIT where the log messages will be inserted, whenever there is an entry in COMPANY table for a new record. sqlite> CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL ); Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be created in COMPANY table. Now let”s create a trigger on COMPANY table as follows − sqlite> CREATE TRIGGER audit_log AFTER INSERT ON COMPANY BEGIN INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime(”now”)); END; Now, we will start actual work, Let”s start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. Create one record in COMPANY table as follows − sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 ); This will create one record in COMPANY table, which is as follows − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation in COMPANY table. Similarly, you can create your triggers on UPDATE and DELETE operations based on your requirements. EMP_ID ENTRY_DATE ———- ——————- 1 2013-04-05 06:26:00 Listing Triggers You can list down all the triggers from sqlite_master table as follows − sqlite> SELECT name FROM sqlite_master WHERE type = ”trigger”; The above SQLite statement will list down only one entry as follows − name ———- audit_log If you want to list down triggers on a particular table, then use AND clause with table name as follows − sqlite> SELECT name FROM sqlite_master WHERE type = ”trigger” AND tbl_name = ”COMPANY”; The above SQLite statement will also list down only one entry as follows − name ———- audit_log Dropping Triggers Following is the DROP command, which can be used to drop an existing trigger. sqlite> DROP TRIGGER trigger_name; Print Page Previous Next Advertisements ”;

SQLite – Constraints ”; Previous Next Constraints are the rules enforced on a data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table. Following are commonly used constraints available in SQLite. NOT NULL Constraint − Ensures that a column cannot have NULL value. DEFAULT Constraint − Provides a default value for a column when none is specified. UNIQUE Constraint − Ensures that all values in a column are different. PRIMARY Key − Uniquely identifies each row/record in a database table. CHECK Constraint − Ensures that all values in a column satisfies certain conditions. NOT NULL Constraint By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NULL is not the same as no data, rather, it represents unknown data. Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs. CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); DEFAULT Constraint The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, SALARY column is set to 5000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00. CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 ); UNIQUE Constraint The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age. Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age − CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 ); PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique IDs. We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a ”longstanding coding oversight”, primary keys can be NULL in SQLite. This is not the case with other databases. A primary key is a field in a table which uniquely identifies each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). Example You already have seen various examples above where we have created COMPANY table with ID as a primary key. CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); CHECK Constraint CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn”t entered into the table. Example For example, the following SQLite creates a new table called COMPANY and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero. CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) ); Dropping Constraint SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table. Print Page Previous Next Advertisements ”;

SQLite – ALTER TABLE Command ”; Previous Next SQLite ALTER TABLE command modifies an existing table without performing a full dump and reload of the data. You can rename a table using ALTER TABLE statement and additional columns can be added in an existing table using ALTER TABLE statement. There is no other operation supported by ALTER TABLE command in SQLite except renaming a table and adding a column in an existing table. Syntax Following is the basic syntax of ALTER TABLE to RENAME an existing table. ALTER TABLE database_name.table_name RENAME TO new_table_name; Following is the basic syntax of ALTER TABLE to add a new column in an existing table. ALTER TABLE database_name.table_name ADD COLUMN column_def…; Example Consider the COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, let”s try to rename this table using ALTER TABLE statement as follows − sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY; The above SQLite statement will rename COMPANY table to OLD_COMPANY. Now, let”s try to add a new column in OLD_COMPANY table as follows − sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1); COMPANY table is now changed and following will be the output from SELECT statement. ID NAME AGE ADDRESS SALARY SEX ———- ———- ———- ———- ———- — 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 It should be noted that newly added column is filled with NULL values. Print Page Previous Next Advertisements ”;

SQLite – GROUP BY Clause ”; Previous Next SQLite GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax Following is the basic syntax of GROUP BY clause. GROUP BY clause must follow the conditions in the WHERE clause and must precede ORDER BY clause if one is used. SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2….columnN ORDER BY column1, column2….columnN You can use more than one column in the GROUP BY clause. Make sure whatever column you are using to group, that column should be available in the column-list. Example Consider COMPANY table with the following records. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 If you want to know the total amount of salary on each customer, then GROUP BY query will be as follows − sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; This will produce the following result − NAME SUM(SALARY) ———- ———– Allen 15000.0 David 85000.0 James 10000.0 Kim 45000.0 Mark 65000.0 Paul 20000.0 Teddy 20000.0 Now, let us create three more records in COMPANY table using the following INSERT statements. INSERT INTO COMPANY VALUES (8, ”Paul”, 24, ”Houston”, 20000.00 ); INSERT INTO COMPANY VALUES (9, ”James”, 44, ”Norway”, 5000.00 ); INSERT INTO COMPANY VALUES (10, ”James”, 45, ”Texas”, 5000.00 ); Now, our table has the following records with duplicate names. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 Again, let us use the same statement to group-by all the records using NAME column as follows − sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; This will produce the following result. NAME SUM(SALARY) ———- ———– Allen 15000 David 85000 James 20000 Kim 45000 Mark 65000 Paul 40000 Teddy 20000 Let us use ORDER BY clause along with GROUP BY clause as follows − sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; This will produce the following result. NAME SUM(SALARY) ———- ———– Teddy 20000 Paul 40000 Mark 65000 Kim 45000 James 20000 David 85000 Allen 15000 Print Page Previous Next Advertisements ”;

SQLite – HAVING Clause ”; Previous Next HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by GROUP BY clause. Syntax Following is the position of HAVING clause in a SELECT query. SELECT FROM WHERE GROUP BY HAVING ORDER BY HAVING clause must follow GROUP BY clause in a query and must also precede ORDER BY clause if used. Following is the syntax of the SELECT statement, including HAVING clause. SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 Example Consider COMPANY table with the following records. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 Following is the example, which will display the record for which the name count is less than 2. sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 2 Allen 25 Texas 15000 5 David 27 Texas 85000 6 Kim 22 South-Hall 45000 4 Mark 25 Rich-Mond 65000 3 Teddy 23 Norway 20000 Following is the example, which will display the record for which the name count is greater than 2. sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 10 James 45 Texas 5000 Print Page Previous Next Advertisements ”;

SQLite – PRAGMA ”; Previous Next SQLite PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on the requirements. Syntax To query the current PRAGMA value, just provide the name of the pragma. PRAGMA pragma_name; To set a new value for PRAGMA, use the following syntax. PRAGMA pragma_name = value; The set mode can be either the name or the integer equivalent but the returned value will always be an integer. auto_vacuum Pragma The auto_vacuum pragma gets or sets the auto-vacuum mode. Following is the simple syntax. PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode; Where mode can be any of the following − Sr.No. Pragma Value & Description 1 0 or NONE Auto-vacuum is disabled. This is the default mode which means that a database file will never shrink in size unless it is manually vacuumed using the VACUUM command. 2 1 or FULL Auto-vacuum is enabled and fully automatic which allows a database file to shrink as data is removed from the database. 3 2 or INCREMENTAL Auto-vacuum is enabled but must be manually activated. In this mode the reference data is maintained, but free pages are simply put on the free list. These pages can be recovered using the incremental_vacuum pragma any time. cache_size Pragma The cache_size pragma can get or temporarily set the maximum size of the in-memory page cache. Following is the simple syntax. PRAGMA [database.]cache_size; PRAGMA [database.]cache_size = pages; The pages value represents the number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages. case_sensitive_like Pragma The case_sensitive_like pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores the letter case. Following is the simple syntax. PRAGMA case_sensitive_like = [true|false]; There is no way to query for the current state of this pragma. count_changes Pragma count_changes pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. Following is the simple syntax. PRAGMA count_changes; PRAGMA count_changes = [true|false]; By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return a one-column, one-row table consisting of a single integer value indicating impacted rows by the operation. database_list Pragma The database_list pragma will be used to list down all the databases attached. Following is the simple syntax. PRAGMA database_list; This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and the file associated. encoding Pragma The encoding pragma controls how strings are encoded and stored in a database file. Following is the simple syntax. PRAGMA encoding; PRAGMA encoding = format; The format value can be one of UTF-8, UTF-16le, or UTF-16be. freelist_count Pragma The freelist_count pragma returns a single integer indicating how many database pages are currently marked as free and available. Following is the simple syntax. PRAGMA [database.]freelist_count; The format value can be one of UTF-8, UTF-16le, or UTF-16be. index_info Pragma The index_info pragma returns information about a database index. Following is the simple syntax. PRAGMA [database.]index_info( index_name ); The result set will contain one row for each column contained in the index giving column sequence, column index with-in table and column name. index_list Pragma index_list pragma lists all of the indexes associated with a table. Following is the simple syntax. PRAGMA [database.]index_list( table_name ); The result set will contain one row for each index giving index sequence, index name and flag indicating whether the index is unique or not. journal_mode Pragma The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax. PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode; There are five supported journal modes as listed in the following table. Sr.No. Pragma Value & Description 1 DELETE This is the default mode. Here at the conclusion of a transaction, the journal file is deleted. 2 TRUNCATE The journal file is truncated to a length of zero bytes. 3 PERSIST The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid. 4 MEMORY The journal record is held in memory, rather than on disk. 5 OFF No journal record is kept. max_page_count Pragma The max_page_count pragma gets or sets the maximum allowed page count for a database. Following is the simple syntax. PRAGMA [database.]max_page_count; PRAGMA [database.]max_page_count = max_page; The default value is 1,073,741,823 which is one giga-page, which means if the default 1 KB page size, this allows databases to grow up to one terabyte. page_count Pragma The page_count pragma returns in the current number of pages in the database. Following is the simple syntax − PRAGMA [database.]page_count; The size of the database file should be page_count * page_size. page_size Pragma The page_size pragma gets or sets the size of the database pages. Following is the simple syntax. PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes; By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database. parser_trace Pragma The parser_trace pragma controls printing the debugging state as it parses SQL commands. Following is the simple syntax. PRAGMA parser_trace = [true|false]; By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands. recursive_triggers Pragma The recursive_triggers pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. Following is the simple syntax. PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false]; schema_version Pragma

SQLite – INDEXED BY Clause ”; Previous Next The “INDEXED BY index-name” clause specifies that the named index must be used in order to look up values on the preceding table. If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails. The “NOT INDEXED” clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints. However, the INTEGER PRIMARY KEY can still be used to look up entries even when “NOT INDEXED” is specified. Syntax Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement. SELECT|DELETE|UPDATE column1, column2… INDEXED BY (index_name) table_name WHERE (CONDITION); Example Consider table COMPANY We will create an index and use it for performing INDEXED BY operation. sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite> Now selecting the data from table COMPANY you can use INDEXED BY clause as follows − sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Print Page Previous Next Advertisements ”;

SQLite – UNION Clause ”; Previous Next SQLite UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be of the same length. Syntax Following is the basic syntax of UNION. SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here the given condition could be any given expression based on your requirement. Example Consider the following two tables, (a) COMPANY table as follows − sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ———- ——————– ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 (b) Another table is DEPARTMENT as follows − ID DEPT EMP_ID ———- ——————– ———- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6 Now let us join these two tables using SELECT statement along with UNION clause as follows − sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This will produce the following result. EMP_ID NAME DEPT ———- ——————– ———- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance The UNION ALL Clause The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well. Syntax Following is the basic syntax of UNION ALL. SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here the given condition could be any given expression based on your requirement. Example Now, let us join the above-mentioned two tables in our SELECT statement as follows − sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This will produce the following result. EMP_ID NAME DEPT ———- ——————– ———- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance Print Page Previous Next Advertisements ”;

SQLite – Perl ”; Previous Next In this chapter, you will learn how to use SQLite in Perl programs. Installation SQLite3 can be integrated with Perl using Perl DBI module, which is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a standard database interface. Following are simple steps to install DBI module on your Linux/UNIX machine − $ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install If you need to install SQLite driver for DBI, then it can be installed as follows − $ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz $ cd DBD-SQLite-1.11 $ perl Makefile.PL $ make $ make install DBI Interface APIs Following are important DBI routines, which can suffice your requirement to work with SQLite database from your Perl program. If you are looking for a more sophisticated application, then you can look into Perl DBI official documentation. Sr.No. API & Description 1 DBI->connect($data_source, “”, “”, %attr) Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Datasource has the form like − DBI:SQLite:dbname = ”test.db” where SQLite is SQLite driver name and test.db is the name of SQLite database file. If the filename is given as ”:memory:”, it will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is actual device file name, then it attempts to open the database file by using its value. If no file by that name exists, then a new database file by that name gets created. You keep second and third parameter as blank strings and the last parameter is to pass various attributes as shown in the following example. 2 $dbh->do($sql) This routine prepares and executes a single SQL statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. Here, $dbh is a handle returned by DBI->connect() call. 3 $dbh->prepare($sql) This routine prepares a statement for later execution by the database engine and returns a reference to a statement handle object. 4 $sth->execute() This routine performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected. Here, $sth is a statement handle returned by $dbh->prepare($sql) call. 5 $sth->fetchrow_array() This routine fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list. 6 $DBI::err This is equivalent to $h->err, where $h is any of the handle types like $dbh, $sth, or $drh. This returns native database engine error code from the last driver method called. 7 $DBI::errstr This is equivalent to $h->errstr, where $h is any of the handle types like $dbh, $sth, or $drh. This returns the native database engine error message from the last DBI method called. 8 $dbh->disconnect() This routine closes a database connection previously opened by a call to DBI->connect(). Connect To Database Following Perl code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. #!/usr/bin/perl use DBI; use strict; my $driver = “SQLite”; my $database = “test.db”; my $dsn = “DBI:$driver:dbname=$database”; my $userid = “”; my $password = “”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. Keep the above code in sqlite.pl file and execute it as shown below. If the database is successfully created, then it will display the following message − $ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully Create a Table Following Perl program is used to create a table in the previously created database. #!/usr/bin/perl use DBI; use strict; my $driver = “SQLite”; my $database = “test.db”; my $dsn = “DBI:$driver:dbname=$database”; my $userid = “”; my $password = “”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; my $stmt = qq(CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);); my $rv = $dbh->do($stmt); if($rv < 0) { print $DBI::errstr; } else { print “Table created successfullyn”; } $dbh->disconnect(); When the above program is executed, it will create COMPANY table in your test.db and it will display the following messages − Opened database successfully Table created successfully NOTE − In case you see the following error in any of the operation − DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398 In such case, open dbdimp.c file available in DBD-SQLite installation and find out sqlite3_prepare() function and change its third argument to -1 instead of 0. Finally, install DBD::SQLite using make and do make install to resolve the problem. INSERT Operation Following Perl program shows how to create records in the COMPANY table created in the above example. #!/usr/bin/perl use DBI; use strict; my $driver = “SQLite”; my $database = “test.db”; my $dsn = “DBI:$driver:dbname=$database”; my $userid = “”; my $password = “”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00 );); $rv = $dbh->do($stmt) or