SQLite Database Servers App

SQLite

by SQLite

Relational Database Management System
Helps with: Database Servers
Similar to: Couch DB App MySQL App PostgreSQL App Redis App More...
Source Type: Open
License Types:
BSD
Supported OS:
Languages: C CPP

What is it all about?

SQLite is a relational database system.It supports ACID, and implements most of SQL (Standard Query Language). Unlike many other relational databases, SQLite whole system is linked to the application that uses it, so a separate ODBC connection, the database manager or database server is not required. The database itself can be regarded as entirely computer memory, or stored in a single file that is locked in for a period of transactions.

Key Features

◾Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures. ◾Zero-configuration - no setup or administration needed. ◾Full SQL implementation with advanced features like partial indexes and common table expressions. ◾A complete database is stored in a single cross-platform disk file. Great for use as an application file format. ◾Supports terabyte-sized databases and gigabyte-sized strings and blobs. (Seelimits.html.). ◾Small code footprint: less than 500KiB fully configured or much less with optional features omitted. ◾Simple, easy to use API.Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately. Well-commented source code with 100% branch test coverage. ◾Available as a single ANSI-C source-code file that is easy to compile and hence is easy to add into a larger project. ◾Self-contained: no external dependencies. Cross-platform: Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems. ◾Sources are in the public domain. Use for any purpose. ◾Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.


Resources

Resource Type

Link

introduction http://zetcode.com/db/sqlite/introduction/

Pricing

Yearly
Monthly
Lifetime
Free
Freemium
Trial With Card
Trial No Card
By Quote

Description

Free

Product Analysis

Data Handling

SQLite

SQLite

By Alvie Amar | 8/11/2016 | Product Analysis |Beginners

SQLite is public domain software that provides a relational database management system. The “lite” in SQLite means that it is lightweight because it has nothing to install and nothing to configure. It is an embedded database engine that directly reads and writes into ordinary disk files because unlike other databases, it has no separate server. It packages the database into one single file with a cross platform format that contains the layout of the database and the actual data stored in the various tables. It is well suited to embedded systems because of its small size and conservative resource use. Unlike other RDBMS, SQLite uses dynamic-type system for tables which allows us to put any value in any column regardless of type.

 

SQLite was designed in the spring of 2000 by Dwayne Richard Hipp while he was working for General Dynamics that was on contract with the United States Navy. He based the semantics and syntax of SQLite on PostgreSQL 6.5. His aim was to design SQLite to be operated without installing a database management system so that a database administrator wouldn’t be a need anymore.

 

SQLite has the following features:

 

Serverless

  • SQLite Library accesses its storage files directly so a separate system or server process is not a requirement.

Zero Configuration

  • No server required means no configuration.

Self Contained

  • A single library contains the entire database system.

Small Runtime Footprint

  • The build is less than a megabytes of code and only a few megabytes of memory are used.

Transactional

  • SQLite transactions are ACID-compliant

Full Featured

  • Supports most query languages

Reliable

  • SQLite’s development team does code testing and verification.


What is SQLite good for?

Some SQLite roles are similar to those client-server RDBMS products and some of its roles were to fill in the gaps of other database management system. For example client-server RDBMS needs a lot of upkeep as a result it limits how and where it can be deployed. SQLite fills in this gap by providing a tool to store, organize and manipulate data to make it smaller. The purpose of SQLite is to complement rather than replace the larger client-server RDBMS in circumstances where simplicity is more essential than capacity.

 

Here are some advantages of SQLite:

 

Speed

  • SQLite has no socket or TCP/IP overhead. It is 2 to 3 times faster than MySQL and postgresQL and has faster PHP interface.

Functionality

  • SQLite data storage is upto 2tb. It has a small footprint and its database is easily moveable.

Security

  • Each SQLite user has its own independent database(s).


Examples and Code Snippets

 

Let’s assume you have already installed SQLite in your computer. Run sqlite3.exe, It will show a Console Application that will allow you to work with SQLite databases.

 

This command will let you connect, create a new directory, and navigate to the newly created directory.

C:\Users\Demo> mkdir sqlite_databases

C:\Users\Demo> cd sqlite_databases

 

We will create a database and name it ‘demoDBtest.db’

C:\Users\Demo\sqlite_databases> sqlite3 demoDBtest.db



After creating the database, this information will show:

C:\Users\Demo\sqlite_databases> sqlite3 demoDBtest.db

SQLite version 3.7.15.2 2016-23-06 05:21:21

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

 

Example to create a table inside the database we created:

sqlite> CREATE TABLE EMPLOYEE (


    ID INT PRIMARY KEY  NOT NULL,


    NAME           TEXT NOT NULL,


    AGE            INT  NOT NULL,

    ADDRESS        CHAR(50)

    SALARY         INT

);

 

This command creates a table called EMPLOYEE inside the demoDBtest.db database. It has five columns named ID, NAME, AGE, ADDRESS and SALARY.

 

To show the list of tables in the database, use this command: .table

sqlite> .table

EMPLOYEE

sqlite>

 

To view the table structure, use this command: .schema <table name> It shows the original CREATE statement including all its indices.

 

sqlite> .schema EMPLOYEE

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY  NOT NULL,     

    NAME           TEXT NOT NULL,

    AGE            INT  NOT NULL,

    ADDRESS        CHAR(50)     

    SALARY         INT

);

 

Insert data to the table:

 

sqlite> INSERT INTO EMPLOYEE (ID, NAME, AGE, ADDRESS, SALARY)


       VALUES (1, 'John Paul', 32, 'California', 20000 ),  


              (2, 'Alladin', 25, 'Texas', 15000 ),

              (3, 'Alvie', 23, 'Norway', 20000 ),

              (5, 'Peter', 27, 'Texas', 85000 ),

              (6, 'Kim', 22, 'South-Hall', 45000.00 );



To fetch all the inserted data:

 

sqlite> .header on

sqlite> .mode column

sqlite> SELECT * FROM EMPLOYEE

 

The result would look like this:

 

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul     32        California  20000

2           Alladin       25        Texas       15000

3           Alvie         23        Norway      20000

4           Miming        25        Washington  65000

5           Peter         27        Texas       85000

6           Kim           22        South-Hall  45000

 

To fetch only selected fields/columns:

 

sqlite> SELECT ID, NAME, SALARY FROM EMPLOYEE:

 

Above query will produce the following result: 

 

ID          NAME        SALARY

----------  ----------  ----------

1           John Paul   20000

2           Alladin     15000

3           Alvie       20000

4           Miming      65000

5           Peter       85000

6           Kim         45000

 

To update a column/field: Example we will update the address of the employee with ID = 6,

sqlite> UPDATE EMPLOYEE SET ADDRESS = 'Philippines' WHERE ID = 6;


Now, EMPLOYEE table would have the following records: 

 

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul    32         California   20000

2           Alladin      25         Texas        15000

3           Alvie        23         Norway       20000

4           Miming       25         Washington   65000

5           Peter        27         Texas        85000

6           Kim          22         Philippines  45000

 

From this,

 

6           Kim         22          South-Hall        45000


To
this, 

 

6           Kim         22          Philippines       45000

 

Let's assume that employee Peter is no longer working in the company so we would like to  delete his records to our database. The command should be like this. 

 

sqlite> DELETE FROM EMPLOYEE WHERE ID = 7;


Now EMPLOYEE table will have following records: 

 

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul   32          California  20000

2           Alladin     25          Texas       15000

3           Alvie       23          Norway      20000

4           Miming      25          Washington  65000

6           Kim         22          Texas       45000



Conclusion

 

This basic overview of SQlite should help clarify whether it is ideal to use SQLite, as opposed to a full-blown RDBMS like Mysql or Postgres. SQLite has wrappers and drivers in all the major languages and can keep running on general systems. In the event that you want to dive in deeper, there are lots of references available on the net. Good luck, and have fun learning SQLite.

 

 

By Alvie Amar | 8/11/2016 | Product Analysis

{{CommentsModel.TotalCount}} Comments

Your Comment

{{CommentsModel.Message}}

Top DiscoverSDK Experts

User photo
200
Noor Khan
Senior Software Engineer (Web)
GUI | Data Handling and 17 more
View Profile
User photo
80
Eugene Klymenko
.Net Developer
Data Handling | Web and 14 more
View Profile
User photo
60
Billy Joel Ranario
Full Stack Web Developer and Article Writer
GUI | Data Handling and 31 more
View Profile
User photo
20
Enrico Lorenzo Rodriguez
Experienced web and back-end developer
Data Handling | Web and 10 more
View Profile
Show All

Interested in becoming a DiscoverSDK Expert? Learn more

X

Compare Products

Select up to three two products to compare by clicking on the compare icon () of each product.

{{compareToolModel.Error}}

Now comparing:

{{product.ProductName | createSubstring:25}} X
Compare Now