[Try it] Using sqlite

I have heard of the sqlite database engine for a long time, but I have never been able to play with it because I have always been so cool and hard-working. I remember that when I was working as a standard library for the CAD system, I used sqlite to store those standard parts-as a standard library for the desktop version, and installed and released it to a single machine as a database, sqlite is the best choice. In addition, according to the sqlite support capacity limit of 2 TB, if each standard library part requires 5 MB, it can save 2*1024*1024/5 = 419430, it should be enough!

There are two main reasons for using sqlite this time:

• Recently, I want to write a Weibo knowledge manager for android. sqlite is the best choice for offline data storage.
• The password leaked by csdn can be imported into the database. One is to perform some analysis, and the other is to practice SQL
  Chen Shuo summarized the most common shell command for 10 passwords: awk '{print $3}' www.csdn.net. SQL | sort | uniq-c | sort-nr | head

Basic Concepts

Sqlite is a well-known standalone database system. It is said that it is the most widely deployed database system in the world-as a serverless standalone database, it has been deployed on countless PCs and smart devices. Compared with ms sqlserver, MYSQL, ORACLE, and DB2 deployed only on powerful servers, it naturally takes a great advantage, compared with these large database systems, the advantages of these systems are: Resource occupation is very small, the configuration is extremely simple (no configuration required), in-process database operations, high efficiency; of course, it is not suitable for applications with high concurrency and big data (> 2 TB. This article analyzes in detail that sqlite is suitable for unsuitable scenarios.

Sqlite supports windows, linux, and mac, and of course Android. android. database. sqlite is an api provided by the android sdk. In addition to programming interfaces, sqlite also provides a command line tool, sqlite3, to manage databases: through which you can create databases, tables, insert, query data, and so on.

In addition, the SQL syntax and pragma content listed on the sqlite website are also worth reference.

 

Download and install

Sqlite, as it claims, is zero-configuration, so it can be used once you download and unzip it. Generally, sqlite contains three parts:

• SDK
• Sqlite3 command line tool
• Sqlite3_analyzer command line tool

You need to download different zip packages here for a simple verification of whether sqlite3 works:

D:\Source\Data\Password
$ sqlite3 my.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table company(name varchar(50) primary key, address varchar(50));
sqlite> insert into company values("Morgan Stanley", "Huamu Road");
sqlite> insert into company values("Autodesk", "Caobao Road");
sqlite> select * from company;
Morgan Stanley|Huamu Road
Autodesk|Caobao Road

 

Operation practices

Two examples can be used.

I. Use sqlite3 command line tool for data analysis

The object is the csdn password library. First, import the Password text to the database:

D:\Source\Data\Password
$ sqlite3 csdn.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table user(id varchar(50) primary key, pwd varchar(50), mail varchar(50));
sqlite> .separator " # "
sqlite> .import csdn/www.csdn.net.sql user

Create a user table first, and then use. the separator command sets the column separator and uses. import command import, because the source data format is: userid # password # mail, of course, if someone uses the password "#", import will fail-but it seems that the csdn password library does not. (Maybe hackers have removed these rows beforehand ).

If the data is available, check the number of rows:

sqlite> select count(*) from user；
6428632

 

Now, I want to find the 10 most passwords used in csdn:

sqlite> select pwd, count(*) from user group by pwd order by count(*) desc limit 10;
123456789|235012
12345678|212749
11111111|76346
dearbook|46053
00000000|34952
123123123|19986
1234567890|17790
88888888|15033
111111111|6995
147258369|5965

We can see that some of them are relatively consistent with the keyboard distribution password. As for dearbook, it is another sub-website of csdn. I don't know why there are so many candidates for it; and 147258369, it is obvious that the keyboard is typed in. It seems that there are not a few programmers who use the first keyboard.

 

2. Use the C/C ++ programming interface of sqlite3.

You can directly use the source code or pre-compiled dll files (windows). Here, dll is used, because the direct use of source code is relatively simple:

• Download the dll package, which contains two files: sqlite3.def and sqlite3.dll.
• Download the source code package. The sqlite3.h header file is required.
• Convert sqlite3.def to a lib file:

  $ lib /def:sqlite3.def /machine:x86
  Microsoft (R) Library Manager Version 9.00.30729.01
  Copyright (C) Microsoft Corporation.  All rights reserved.
  
     Creating library sqlite3.lib and object sqlite3.exp

• Create a VC ++ project, copy the C code here, set additonal include directories and additonal include libraries, compile and add the directory of sqlite3.dll to path