SQLite - SPLessons

SQLite Pragma

Chapter 19

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Pragma

SQLite Pragma

shape Description

SQLite Pragma is an appropriate type of command used to handle the state flags and different environment variables inside the SQLite settings. SQLite PRAGMA esteems can be perused and it can likewise be setup based on the prerequisites.

shape Syntax

PRAGMA pragma_name;

Pragma => Is a special type of command in SQLite.

Pragma_name =>Pragma table name.

For setting up new esteems inside SQLite Pragma the code will be like PRAGMA pragma_name=value;

Types of pragma

shape Description

SQLite Pragma types can be classified into the following types.They are:

Auto_vacuum pragma

shape Description

The auto-vacuum mode is setup by the auto_vacuum pragma.

PRAGMA[database.]auto_vacuum;
PRAGMA[database.]auto_vacuum=mode;

Where mode can be any of the accompanying:

Pragma value Description
0 or None This is a default mode and which is disabled auto_vacuum.
1 or FULL This is fully automatic and which is auto_vaccum
2 or Incremental It must be manually activated and it is enabled in auto_vaccum

Count_changes pragma

shape Description

The count_changes pragma sets the esteems of Data Manipulation explanation like update, insert and delete. These SQLite Pragma statements are false and doesn’t display the result set by default. Suppose this pragma statement displayed to be true, then all the explanation gives the row and column values in a single set from the table.

PRAGMA count_changes;
PRAGMA count_changes=[true|false];

Case_sensitive_like pragma

shape Description

The case-affectability of the work in LIKE expressions will be taken care of by the case_sensitive_like pragma. Fundamentally, pragma is not legitimate by default, because these operators disregards letter case.

PRAGMA case_sensitive_like=[true|false];

Cache_size pragma

shape Description

Cache_size pragma temporarily arranges the max size of the in-memory page cache, and esteems determines the collection of pages in the cache and contains some specific default page size.i.e; max 2,000 pages and min 10 pages.

PRAGMA[database.]cache_size;
PRAGMA[database.]cache_size=pages;

Freelist_count pragma

shape Description

Freelist_count pragma will give back a solitary whole number specifing number of database pages presently marked are accessible and complimentary.

PRAGMA [database. ]freelist_count;

Database_list pragma

shape Description

Database_list pragma lists down every last database appended.

PRAGMA database_list;

Encoding pragma

shape Description

Encoding pragm handles the strings, and clarifies how this strings are stored and encoded inside the database documents.

PRAGMA encoding;
PRAGMA encoding=format;

Journal_mode pragma

shape Description

Journal_mode pragma organizes the journal mode and handles all these documents that are processed and stored.

PRAGMA journal_mode=mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode=mode;

Index_list pragma

shape Description

Index_list pragma displays each and every index that is related with a table.

PRAGMA [database. ]index_list(table_name);

Index_info pragma

shape Description

Index_info will give back all the instructions related to the index of the database.

PRAGMA [database. ]index_info(index_name);

Page_size pragma

shape Description

Page_size pragma arranges the database pages sizes.And defaults sizes are 512, 1024, 2048, 4096, 8192, 16384 and 32768 bytes.

PRAGMA[database. ]page_size;
PRAGMA[database. ]page_size=bytes;

Page_count pragma

shape Description

Page_count pragma will give back the present sum of pages inside the database.

PRAGMA [database. ]page_size;
PRAGMA [database. ]page_size=bytes;

Max_page_count pragama

shape Description

Max_page_count will arranges the maximum sum of pages to the database.

PRAGMA [database. ]max_page_count;
PRAGMA [database. ]max_page_count=max_page;

Schema_version pragma

shape Description

Schema_version pragma will arrange the schema version esteems, which is stored in the header of the database.

PRAGMA [database. ]schema_version;
PRAGMA [database .]schema_version=number;

Parser_trace pragma

shape Description

This pragma handles debugging and the printing stage in the SQL parser statements.

PRAGMA parser_trace=[true|false];

Secure_delete pragma

shape Description

Secure_delete pragma will keep an eye on what premise the substance has been expelled from the database server.

PRAGMA secure_delete;
PRAGMA secure_delete=[true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete=[true|false];

SQL_trace pragam

shape Description

Is used to stored the result set of SQL statements and display them on the screen.

PRAGMA sql_trace;
PRAGMA sql_trace=[true|false];

Summary

shape Key Points

  • SQLite Pragma – Is an appropriate command to control the state flags.
  • Types of pragma – It includes the setup files and operations that will perform in the SQLite database.