Use sqlite3 to build small and medium website Databases

Recently, I have been familiar with the sqlite3 database for a while, and I like this lightweight database more and more. The advantage is that it does not need to be installed and is small in size;ProgramIt does not need to install additional drivers on the server, which is obvious in embedded development. However, I am not used to graphical interface operations at the beginning, the operation is especially inconvenient. After reading the information on the Internet, it is generally said that its performance is a combination of access (basically on an order of magnitude). I am thinking: can a website with millions of users use this lightweight database? I think it is feasible. In database optimization, we usually use database/table sharding. This can be used on sqlite3. My idea is: for example, there are 1 million users on the site, one requirement is that each user's action should be recorded. For example, each posting of a blog post must record the publishing time, publishing IP address, and other information, this information is displayed in its personal dynamics. At this time, we can design a library: templatedb. db3: the structure of a table is as follows (sample ):

Assume that a user's userid = 1051170; then we can use a certain algorithm: USERID/1000 = (INT) 1051 (meaning that each dB serves only some modules of 1000 users) put it in/data/action_1051.db3 and check whether the database exists before insertion. If not, copy a templatedb file to the target database; this greatly reduces the lock problem caused by data concurrency and reduces the service pressure on the primary database.
Of course, this design also has some shortcomings: if we need to change some structure of the table or add a module after a period of time in the actual production process, this change will be disastrous.

Finally, attach the use notes (I am using the MS entlib 4.1 Framework + vs 2008 ):
1. Go to the official website to download http://www.sqlite.org/download sqlite3 and create database and table;
2. Download System. Data. SQLite. DLL to the http://sqlite.phxsoftware.com/and add references to the project;
3. Web. config Configuration

1 < Connectionstrings >
2 < Add name = " SQLite " Connectionstring = " Data Source = E:/workspace/csharptest/sqliteapp/app_data/userscore. DB " Providername = " System. Data. SQLite " />
3 </ Connectionstrings >
4

4. Query:

Query data 1 Private   Void BIND (){
2 Database DB = Databasefactory. createdatabase ( " SQLite " );
3 String SQL =   " Select * From useraction " ;
4 Dataset DS = DB. executedataset (commandtype. Text, SQL );
5 If (DS ! =   Null )
6 {
7 If (Ds. Tables [ 0 ]. Rows. Count >   0 )
8 {
9 Gdvlist. datasource = DS;
10 Gdvlist. databind ();
11 }
12 }
13 }
14

5. add data:

Insert data 1 Protected   Void Button#click ( Object Sender, eventargs E)
2 {
3 Database DB = Databasefactory. createdatabase ( " SQLite " );
4 String SQL =   " Insert into useraction (userid, username, nickname, scoretype, score, content, addip) "
5 +   " Values (@ userid, @ username, @ nickname, @ scoretype, @ score, @ content, @ addip) " ;
6 Dbcommand cmd = DB. getsqlstringcommand (SQL );
7 DB. addinparameter (CMD, " @ Userid " , Dbtype. string, Int . Parse (tbuid. Text ));
8 DB. addinparameter (CMD, " @ Username " , Dbtype. String, tbusername. Text );
9 DB. addinparameter (CMD, " @ Nickname " , Dbtype. String, tbnick. Text );
10 DB. addinparameter (CMD, " @ Scoretype " , Dbtype. String, tbsctype. Text );
11 DB. addinparameter (CMD, " @ Score " , Dbtype. int32, Int . Parse (tbscore. Text ));
12 DB. addinparameter (CMD, " @ Content " , Dbtype. String, tbcontent. Text );
13 DB. addinparameter (CMD, " @ Addip " , Dbtype. String, request. userhostaddress );
14 DB. executenonquery (CMD );
15 Response. Write ( " Successful! " );
16 BIND ();
17 }
18

Effect:

Oh... it is no different from other database operations.