1, Database introduction
1.1 introduction to database
Database is a data set that is stored together in a certain way, can be shared with multiple users, has as little redundancy as possible, and is independent of the application. It can be regarded as an electronic filing cabinet - the place where electronic files are stored. Users can add, query, update, delete and other operations on the data in the files.
A database is a warehouse for storing data. It has a large storage space and can store millions, tens of millions and hundreds of millions of data. However, the database does not store the data at will. There are certain rules, otherwise the efficiency of query will be very low. Today's world is an Internet world full of data, full of a large amount of data. That is, the Internet world is the data world. There are many sources of data, such as travel records, consumption records, web pages visited, messages sent, etc. In addition to text type data, images, music and sound are all data.
1.2 introduction to common databases
At present, relational databases mainly include MySQL, SQL Server, silkworm database and Oracle database.
MySQL: free product, widely used by small and medium-sized enterprises.
SQL Server: a commercial product of Microsoft. Microsoft SQL statement has good compatibility and high commercial maturity.
Silkworm counting database: Silkworm counting technology is a database for small and medium-sized enterprises, with good c + + interface characteristics and weak SQL characteristics.
Oracle Database: the most commercialized relational database, excellent performance and enterprise expansion ability.
SQLite database: it is a lightweight database and an ACID compliant relational database management system. It is contained in a relatively small C database. SQLite is an in-process library that implements a self-sufficient, serverless, zero configuration, transactional SQL database engine. It is a zero configuration database, which means that like other databases, it does not need to be configured in the system.
1.3 introduction to SQLite database
SQLite database is an embedded database. Its goal is to be as simple as possible. Therefore, it abandons the complex characteristics of traditional enterprise database and only realizes the necessary functions for the database.
Although simplicity is the primary goal of SQLite, its functions and performance are excellent. It has some characteristics:
- Support ACID transactions (ACID is short for automatic, consistent, Isolated and Durable)
- Zero configuration, without any administrative configuration process
- Support SQL92 standard
- All data is stored in separate files, and the maximum supported files can reach 2TB
- The database can be shared between machines with different bytes
- Small volume
- Low system overhead and high retrieval efficiency
- Easy to use API interface
- It can be bound with TCL, Python, C/C + +, JAVA, Ruby, Lua, Perl, PHP and other languages
- Self contained, independent of external support
- Well commented code
- The code test coverage is more than 95%
- Open source and can be used in any legal way
1.4 sqlite database visual management tool download
SQLite Administrator is a lightweight SQLite visualization tool, which is mainly used to manage SQLite database files, create, design and manage operations, and has the function of creating database, table, view, index, trigger, query and other contents.
The code editor provided by SQLite Administrator has automatic completion and syntax coloring, supports Chinese, and can be used to record personal data and develop SQLite data.
2, sqlite database compilation and installation (ARM)
Objectives: Cross compile sqlite and deploy it to the embedded development board environment.
The currently used target development board is the friendly arm's tiny4412 development board, and the cross compiler version is the official version of 4.5.1
The host uses RedHat 6.3, of course, ubuntu, or other distributions.
2.1 SQLite database download
Download address: SQLite Home Page
2.2 compiling database (ARM)
[wbyq@wbyq pc_work]$ tar xvf /mnt/hgfs/linux-share-dir/sqlite-autoconf-3250200.tar.gz
[wbyq@wbyq pc_work]$ cd sqlite-autoconf-3250200/
[wbyq@wbyq sqlite-autoconf-3250200]$ ./configure --host=arm-linux --prefix=$PWD/install
[wbyq@wbyq sqlite-autoconf-3250200]$ make && make install
[wbyq@wbyq sqlite-autoconf-3250200]$ tree install
install
├── bin
│ └── sqlite3
├── include
│ ├── sqlite3ext.h
│ └── sqlite3.h
├── lib
│ ├── libsqlite3.a
│ ├── libsqlite3.la
│ ├── libsqlite3.so -> libsqlite3.so.0.8.6
│ ├── libsqlite3.so.0 -> libsqlite3.so.0.8.6
│ ├── libsqlite3.so.0.8.6
│ └── pkgconfig
│ └── sqlite3.pc
└── share
└── man
└── man1
└── sqlite3.1
7 directories, 10 files
[wbyq@wbyq sqlite-autoconf-3250200]$
2.3 build compilation environment and program running environment
1. Copy the generated library file to the lib directory of the development board, so that the dynamic library can be found when the executable containing the database is executed on the development board.
[wbyq@wbyq sqlite-autoconf-3250200]$ cp install/lib/*.so* /home/wbyq/rootfs/lib/ -dvf "install/lib/libsqlite3.so" -> "/home/wbyq/rootfs/lib/libsqlite3.so" "install/lib/libsqlite3.so.0" -> "/home/wbyq/rootfs/lib/libsqlite3.so.0" "install/lib/libsqlite3.so.0.8.6" -> "/home/wbyq/rootfs/lib/libsqlite3.so.0.8.6"
2. In order to facilitate finding the header file and library file when the cross compiler compiles and contains the source file of the database, the generated library file and header file need to be copied to the cross compilation directory respectively.
[wbyq@wbyq sqlite-autoconf-3250200]$ sudo cp install/lib/*.so* /home/wbyq/work/arm-linux-gcc/opt/FriendlyARM/toolschain/4.5.1/arm-none-linux-gnueabi/sys-root/usr/lib/ -dvf [wbyq@wbyq sqlite-autoconf-3250200]$ sudo cp install/include/* /home/wbyq/work/arm-linux-gcc/opt/FriendlyARM/toolschain/4.5.1/arm-none-linux-gnueabi/sys-root/usr/include/ -fv
2.4 writing example code
#include <stdio.h>
#include <sqlite3.h>
/* callback The function is called only when the database is select ed */
static int select_callback(void *data, int argc, char **argv, char **azColName){
int i;
printf("%s", (char*)data);
for(i=0; i < argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc,char **argv)
{
sqlite3 *db;
int err;
char *zErrMsg=0;
if(argc!=2)
{
printf("./app <Database file name>\n");
return 0;
}
/*1. Create database*/
err=sqlite3_open(argv[1],&db);
if(err)
{
printf("Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 0;
}
/*2. Construct SQL language in string form*/
/*
CREATE TABLE Is a keyword that tells the database system to create a new table. The CREATE TABLE statement is followed by a unique name or ID of the table
*/
char *sql = " CREATE TABLE TempData( \
ID INT PRIMARY KEY, \
DS18B20Name CHAR(7), \
Number INT, \
Time CHAR(12), \
Data FLOAT \
);";
/*3. Create a data table SensorData using the sql language specified by the sql string*/
sqlite3_exec(db,sql,0, select_callback , &zErrMsg );
/*4. Insert multiple pieces of data into the data table*/
sql = "INSERT INTO 'TempData' VALUES(81,'DS18B20',3,'20191109114322',11.4);" ;
sqlite3_exec(db,sql,0,select_callback,&zErrMsg );
sql = "INSERT INTO 'TempData' VALUES(84,'DS18B20',6,'20191109114323',22.4);" ;
sqlite3_exec(db,sql,0,select_callback,&zErrMsg );
sql = "INSERT INTO 'TempData' VALUES(87,'DS18B20',9,'20191109114323',77.4);" ;
sqlite3_exec(db,sql,0,select_callback,&zErrMsg );
/*5. close database*/
sqlite3_close(db);
printf("Database closed successfully.\n");
return 0;
}
2.5 program compilation test
all: arm-linux-gcc sqlite_create.c -o app -lsqlite3 cp app /home/wbyq/rootfs/code rm app -f
Enter the console terminal test program of the development board.
