current position:Home>SQLite3 minimalist Tutorial & go operating data structures using SQLite memory mode

SQLite3 minimalist Tutorial & go operating data structures using SQLite memory mode

2022-05-15 07:09:34Donghai Chen Guangjian

SQLite brief introduction

key word : RDBMS (embedded), C

SQLite is a database engine written in the C language. It is not a standalone app; rather, it is a library that software developers embed in their apps.

SQLite It's an open source 、 Embedded relational database . It was originally released in 2000 year , In portability 、 Ease of use 、 Compactness 、 Outstanding performance in terms of effectiveness and reliability .

For programmers ,SQLite It's like a data conveyor , It provides a convenient method to bind the data of the application . Like a conveyor belt , Yes SQLite There is no end to the use of .

Except just as a storage container ,SQLite It can also be used as a simple data processing tool . If the size and complexity are appropriate , Use SQLite It's easy to Convert the data structure used by the application into a table , And stored in an internal database . In this way , You can manipulate interrelated data , Can complete a very heavy task page, do not have to write their own algorithm to operate and sort the data structure .

“SQLite It's free software . Although I am its architect and the main writer of the code , but SQLite My program is not .SQLite It doesn't belong to anyone , Nor is it within the scope of copyright protection . All who used to be SQLite People who have contributed code to the project have signed an affidavit to publish their contribution to the public domain , I keep the originals of these affidavits in the office safe . I also try my best to ensure that SQLite Proprietary algorithms are not used in , These precautions mean you can use it in any form SQLite, Without paying royalties 、 Any fee or other license restrictions ..... Seek forgiveness and forgive others . Because you've got it for free SQLite, Please also give something to others for free in return . Be a volunteer , Contribute other software projects or find other ways to repay . (Richard Hipp Charlotte, NC April 11, 2006)
--- Excerpt from : “SQLite Authoritative guide .”

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** The code in this file implements the function that runs the
** bytecode of a prepared statement.
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
*/

features

1. ACID Business

2. Zero configuration – No need to install and manage configuration

3. Stored in a single Disk files A complete database in

4. Database files can be freely shared among machines in different byte order

5. Support database size to 2TB

6. Small enough , roughly 13 Line ten thousand C Code , 4.43M

7. Faster than some popular databases in most common database operations

8. Simple , Easy API

9. contain TCL binding , At the same time through Wrapper Support binding in other languages

10. Well annotated source code , And with 90% The above test coverage

11. Independent : No extra dependence

12. The source code is completely open source , You can use it for any purpose , Including selling it

13. Support multiple development languages ,C, C++, PHP, Perl, Java, C#,Python,Ruby etc.

SQLite command

Standards for interacting with relational databases SQLite The order is similar to SQL. The order includes CREATE、SELECT、INSERT、UPDATE、DELETE and DROP. Based on the nature of their operations, these commands can be divided into the following :

DDL - Data definition language

command describe
CREATE Create a new table , A view of a table , Or other objects in the database .
ALTER Modify an existing database object in the database , Like a watch .
DROP Delete the entire table , Or a view of a table , Or other objects in the database .

DML - Data operation language

command describe
INSERT Create a record .
UPDATE Modify the record .
DELETE Delete record .

DQL - Data query language

command describe
SELECT Retrieve some records from one or more tables .

SQLite Small as it is , But supported by SQL sentence Not inferior to other open source databases , It supports SQL Include :

ATTACH DATABASE

BEGIN TRANSACTION

comment

COMMIT TRANSACTION

COPY

CREATE INDEX

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DELETE

DETACH DATABASE

DROP INDEX

DROP TABLE

DROP TRIGGER

DROP VIEW

END TRANSACTION

EXPLAIN

expression

INSERT

ON CONFLICT clause

PRAGMA

REPLACE

ROLLBACK TRANSACTION

SELECT

UPDATE

SQLite data type

SQLite There is no type . This means that you can save any type of data into any column of any table you want to save , Whatever the data type declared in this column ( Only in one case is not , Explain later ). about SQLite It is perfectly valid for a field not to specify a type . Such as :

Create Table ex1(a, b, c);

indeed SQLite Allow ignoring data types , But still suggest in your Create Table Specifies the data type in the statement . Because data types are important for you to communicate with other programmers , Or you can give a hint or help when you are ready to replace your database engine .

SQLite Support common data types , Such as :

CREATE TABLE ex2
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5)
k VARYING CHARACTER (24),
l NATIONAL VARYING CHARACTER(16)

As mentioned earlier, in some cases , SQLite The fields are not untyped . That is, when the field type is ”Integer Primary Key” when .

Go operation SQLite example

Import dependence go-sqlite3

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    ...
)

Create database

database, err := sql.Open("sqlite3", "./alpha.db")

Create table

stmt, _ := database.Prepare("create table if not exists user(id integer primary key, firstname text, lastname text)")
stmt.Exec()

insert data

stmt, _ = database.Prepare("insert into user( firstname, lastname) values(?,?)")
stmt.Exec("Jack", "Chen")

Query data

var id int
var firstname string
var lastname string
rows, err := database.Query("select id, firstname, lastname from user")
if nil != err {
    fmt.Println(err)
}
for rows.Next() {
    rows.Scan(&id, &firstname, &lastname)
    fmt.Println(strconv.Itoa(id) + ": " + firstname + " " + lastname)
}

The complete code is as follows

Source code :

package sqlite

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    "strconv"
    "time"
)

func SQLiteDemo() {
    var start = time.Now().UnixMicro()
    database, err := sql.Open("sqlite3", fmt.Sprintf("./alpha%d.db", start))
    if nil != err {
        fmt.Println(err)
    }
    stmt, _ := database.Prepare("create table if not exists user(id integer primary key, firstname text, lastname text)")
    stmt.Exec()

    stmt, _ = database.Prepare("insert into user( firstname, lastname) values(?,?)")
    stmt.Exec("Jack", "Chen")

    var id int
    var firstname string
    var lastname string
    rows, err := database.Query("select id, firstname, lastname from user")
    if nil != err {
        fmt.Println(err)
    }
    for rows.Next() {
        rows.Scan(&id, &firstname, &lastname)
        fmt.Println(strconv.Itoa(id) + ": " + firstname + " " + lastname)
    }

    var end = time.Now().UnixMicro()
    fmt.Printf("SQLiteDemo: %d\n", end-start)

}


// main
package main

import (
    "go_basics/sqlite"
)

func main() {
    sqlite.SQLiteDemo()
}

Disk file mode , A simple " Build table - Insert - Inquire about " The operation takes time 5467 μs.

1: Jack Chen
SQLiteDemo: 5467

You can see , Every SQLite DB There is one. root surface : sqlite_master

select * from sqlite_master;

--output
type,name,tbl_name,rootpage,sql
table,user,user,2,"CREATE TABLE user(id integer primary key, firstname text, lastname text)"

SQLite Memory mode use

SQLite In-Memory Pattern

SQLite Databases are usually stored in a single ordinary disk file . We can also use memory mode .SQLite The most common way for a database to simply exist in memory is to use a special file name :memory: Open database . let me put it another way , The name of the real disk file is not passed to sqlite3_open() function , Instead, pass in a string :memory: . for example :

rc = sqlite3_open(":memory:",&db);

Instructions :

  1. After calling this interface , No disk files will be opened . Instead, create a new database in memory .
  2. After the database connection is closed , The database no longer exists .
  3. every last memory Databases are different from each other . therefore , Open two database connections , The file name of each database connection is “:memory:”, Two separate in memory databases will be created .

Using memory SQLite

Using memory SQLite posture : sql.Open("sqlite3", ":memory:") , The code is as follows :

database, err := sql.Open("sqlite3", ":memory:")

Full source code :

func SQLiteInMemoryDemo() {
    var start = time.Now().UnixMicro()

    database, err := sql.Open("sqlite3", ":memory:")
    if nil != err {
        fmt.Println(err)
    }
    stmt, _ := database.Prepare("create table if not exists user(id integer primary key, firstname text, lastname text)")
    stmt.Exec()

    stmt, _ = database.Prepare("insert into user( firstname, lastname) values(?,?)")
    stmt.Exec("Jack", "Chen")

    var id int
    var firstname string
    var lastname string
    rows, err := database.Query("select id, firstname, lastname from user")
    if nil != err {
        fmt.Println(err)
    }
    for rows.Next() {
        rows.Scan(&id, &firstname, &lastname)
        fmt.Println(strconv.Itoa(id) + ": " + firstname + " " + lastname)
    }

    var end = time.Now().UnixMicro()
    fmt.Printf("SQLiteInMemoryDemo: %d", end-start)

}

// main
package main

import (
    "fmt"
    "go_basics/sqlite"
)

func main() {
    sqlite.SQLiteDemo()
    sqlite.SQLiteInMemoryDemo()
}

Performance time consuming : 434 μs ( It's disk mode 10 times +)

1: Jack Chen
SQLiteInMemoryDemo: 434

SQLite Architecture & Architecture Composition

SQLite Have a delicate 、 Modular architecture , And introduces some unique methods to manage relational database . It is organized in 3 In the subsystem 8 It consists of two independent modules , Pictured 1-2 Shown . This model divides the query process into several discrete tasks , It's like working on an assembly line . Compile query statements at the top of the architecture stack , Execute it in the middle , Handle the storage and interface of the operating system at the bottom .

SQLite Storage engine

SQLite Calculation engine

Reference material

https://www.youtube.com/watch?v=gpxnbly9bz4https://blog.csdn.net/cunjie3951/article/details/106917999

copyright notice
author[Donghai Chen Guangjian],Please bring the original link to reprint, thank you.
https://en.chowdera.com/2022/131/202205102230274720.html

Random recommended