Dynamic sqlite3 database V1.0 using C language

/* Project name: use C language to dynamically operate the sqilite3 Database
*
* Project member: Zhang Shuangxi
*
* Compiling environment: gcc
*
* Project functions:
* 1. dynamically create a table
* 2. dynamically implement basic table operations (add, delete, modify, and query)
* 3. Clear table data and Delete tables (additional functions)
*
* Project summary:
* 1. technologies involved:
*
* 1. Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite_callback, void *, char **);
* To achieve dynamic database operations, you must dynamically construct SQL statements. You can use the strcat function to construct SQL statements and splice them until the SQL statements are fully constructed.
*
* 2. To implement the effect of simple menu options, you must separate the functions that implement each sub-function. In the switch, select the call
*
* 3,
*
* 2. unexpected gains:
*
* 1. If you want to enter a string with spaces, you cannot use the scanf function. Therefore, scanf cannot achieve the expected results because the scanf function has spaces, line breaks, and carriage returns.
* To solve this problem, you can use the gets function. However, gets is a dangerous function and is not recommended in C language. You must clear the buffer before using the gets function. Otherwise. Will appear
* Unexpected input results, which is caused by the previous uncleared buffer.
*
* 2. How to clear the buffer in Linux C:
* Personal opinion:
* After testing, Linux C cannot clear the buffer using fflush (stdin). After fflush (stdin) is used, the gets function is used. Gets will still read from the buffer,
* The buffer zone is not cleared by fflush (stdin;
* After finding information on the Internet, I learned that setbuf (stdin, null) is a function. After testing, the buffer zone is successfully cleared.
*
* In short, the function for clearing the buffer in Linux C is setbuf (stdin, null );
*
* 3. Programming Problems and Solutions:
*
* 1. correct usage of update database:
* Update table_name set name = 'new _ name', sex = 'new _ sex' where id = id_val; // correct
* In the above sentence, it cannot be replaced with and,
* That is:
* Update table_name set name = 'new _ name' and sex = 'new _ sex' where id = id_val; // the error message returned.
*
* 2. String problems:
* Char * P = "Hello world! "; // This string is read-only and cannot be modified or rewritten. It cannot be strcat (P," Jack ");
*
* Char STR [] = "Hello world! "// This allows you to perform arbitrary operations on strings through pointers or without pointers.
* Char * P;
* P = STR;
*
* 3. Zero string cleaning:
* Char STR [10];
*
* Memset (STR, 0, sizeof (STR ));
*
*
*
*
* Zhang Shuangxi
* 2010.9.29
*
*
**/

# Include <stdio. h>
# Include <stdlib. h>
# Include <string. h>
# Include "sqlite3.h"

/* This struct facilitates operations on the data in the table */

Typedef struct table_column
{
Char column_name [100]; // column name
Char column_type [200]; // data type of the column
} Column;

Void db_create (sqlite3 * dB, char * ptable_name, int column_num, column []);
Int rscallback (void * P, int column, char ** column_val, char ** column_name );
Void db_show (sqlite3 * dB, char * ptable_name );
Void db_insert (sqlite3 * dB, char * ptable_name, int column_num, column []);
Void db_update (sqlite3 * dB, char * ptable_name, int column_num, column []);
Void db_delete (sqlite3 * dB, char * ptable_name );
Void db_empty (sqlite3 * dB, char * ptable_name );
Void db_drop (sqlite3 * dB, char * ptable_name );

Int main (INT argc, char * argv [])
{
Sqlite3 * dB;
Int empty = 1;
Int ret = 0;
Char * errmsg = NULL;
Int choice =-1;
Char table_name [20];
Char * ptable_name;
Int column_num;
Int I;
Column column [10];
Ptable_name = table_name;

Ret = sqlite3_open ("student. DB", & dB );
If (Ret! = Sqlite_ OK)
{
Perror ("slqite3_open ");
Exit (1 );
}

While (choice! = 0)
{
Printf ("Please input your choise:/N ");
Printf ("------------------------------------------------------------------/N ");
Printf ("| 0. exit | 1. create | 2. show | 3. insert | 4. update | 5. delete | 6. empty | 7. drop |/N ");
Printf ("------------------------------------------------------------------/N ");
Scanf ("% d", & choice );

Switch (choice)
{
Case 0:
Break;
Case 1:
Printf ("we will create a table for you, Please input the name of your table:/N ");
Scanf ("% s", ptable_name );
Printf ("Please input the number of column:/N ");
Scanf ("% d", & column_num );
Printf ("Please input column_name column_type:/N ");

For (I = 0; I <column_num; I ++)
{
Scanf ("% S % s", column [I]. column_name, column [I]. column_type );
}

Db_create (dB, table_name, column_num, column );
Break;
Case 2:
Db_show (dB, table_name );
Break;
Case 3:
Db_insert (dB, table_name, column_num, column );
Break;
Case 4:
Db_update (dB, table_name, column_num, column );
Break;
Case 5:
Db_delete (dB, table_name );
Break;
Case 6:
Db_empty (dB, table_name );
Break;
Case 7:
Db_drop (dB, table_name );
Break;
Default:
Printf ("your choice is not exist! /N ");
// Printf ("Please input a number again:/N ");
// Scanf ("% d", & choice );
Exit (0 );


}

}

Return 0;
}

Void db_create (sqlite3 * dB, char * ptable_name, int column_num, column [])
{
Int ret, I;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;

Psql = SQL;

Strcpy (Psql, "create table ");

Strcat (Psql, ptable_name );

Strcat (Psql ,"(");

Strcat (Psql, column [0]. column_name );
Strcat (Psql ,"");
Strcat (Psql, column [0]. column_type );
Strcat (Psql, "primary key autoincrement ");
Strcat (Psql ,",");

For (I = 1; I <column_num-1; I ++)
{
Strcat (Psql, column [I]. column_name );
Strcat (Psql ,"");
Strcat (Psql, column [I]. column_type );
Strcat (Psql ,",");
}

Strcat (Psql, column [column_num-1]. column_name );
Strcat (Psql ,"");
Strcat (Psql, column [column_num-1]. column_type );
Strcat (Psql ,");");

Printf ("/nsqlite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;
}

Int rscallback (void * P, int column, char ** column_val, char ** column_name)
{
Int I;
* (Int *) p = 0;

For (I = 0; I <column; I ++)
{
Printf ("% S> % S/T", column_name [I], column_val [I]);
}
Printf ("/N ");
Return 0;
}

Void db_show (sqlite3 * dB, char * ptable_name)
{
Int ret = 0;
Char * errmsg = NULL;
Int empty = 1;
Char SQL [200];
Char * Psql;

Printf ("/N ");
Psql = SQL;

Strcpy (Psql, "select * from ");
Strcat (Psql, ptable_name );
Strcat (Psql ,";");

Ret = sqlite3_exec (dB, Psql, rscallback, & empty, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

If (empty)
{
Printf ("the table is empty! /N ");
}

Printf ("/N ");
Return;
}

Void db_insert (sqlite3 * dB, char * ptable_name, int column_num, column [])
{
Int ret = 0;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;
Char TMP [20];
Int I;

Psql = SQL;
Strcpy (Psql, "insert ");

Strcat (Psql, ptable_name );
Strcat (Psql ,"(");
For (I = 1; I <column_num-1; I ++)
{
Strcat (Psql, column [I]. column_name );
Strcat (Psql ,",");
Strcat (Psql ,"");
}
Strcat (Psql, column [column_num-1]. column_name );
Strcat (Psql ,")");
Strcat (Psql, "values (");
For (I = 1; I <column_num-1; I ++)
{
Printf ("Please input % s/n", column [I]. column_name );
If (strcmp (column [I]. column_type, "text") = 0)
{
Strcat (Psql ,"/'");
Memset (TMP, 0, sizeof (TMP ));
Scanf ("% s", TMP );
Strcat (Psql, TMP );
Strcat (Psql ,"/'");
Strcat (Psql ,",");
}
Else
{
Memset (TMP, 0, sizeof (TMP ));
Scanf ("% s", TMP );
Strcat (Psql, TMP );
Strcat (Psql ,",");
Strcat (Psql ,"");
}
}
Printf ("Please input % s/n", column [column_num-1]. column_name );
If (strcmp (column [column_num-1]. column_type, "text") = 0)
{
Scanf ("% s", TMP );
Strcat (Psql ,"/'");
Strcat (Psql, TMP );
Strcat (Psql ,"/'");
Strcat (Psql ,");");
}
Else
{
Scanf ("% s", TMP );
Strcat (Psql, TMP );
Strcat (Psql ,");");
}

Printf ("/nsqlite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;
}

Void db_update (sqlite3 * dB, char * ptable_name, int column_num, column [])
{
Int ret = 0;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;
Char new_val [20];
Char new_id [3];
Int I;

Psql = SQL;
Strcpy (Psql, "Update ");
Strcat (Psql, ptable_name );
Strcat (Psql, "set ");
For (I = 1; I <column_num-1; I ++)
{
Strcat (Psql, column [I]. column_name );
Strcat (Psql, "= ");
If (strcmp (column [I]. column_type, "text") = 0)
{
Strcat (Psql ,"/'");
Memset (new_val, 0, sizeof (new_val ));
Printf ("Please input a new % s/n", column [I]. column_name );
Scanf ("% s", new_val );
Strcat (Psql, new_val );
Strcat (Psql ,"/'");
Strcat (Psql ,",");
}
Else
{
Memset (new_val, 0, sizeof (new_val ));
Printf ("Please input a new % s/n", column [I]. column_name );
Scanf ("% s", new_val );
Strcat (Psql, new_val );
Strcat (Psql ,",");
}
}

Strcat (Psql, column [I]. column_name );
Strcat (Psql, "= ");

If (strcmp (column [column_num-1]. column_type, "text") = 0)
{
Strcat (Psql ,"/'");
Memset (new_val, 0, sizeof (new_val ));
Printf ("Please input a new % s/n", column [I]. column_name );
Scanf ("% s", new_val );
Strcat (Psql, new_val );
Strcat (Psql ,"/'");
Strcat (Psql ,"");
}
Else
{
Memset (new_val, 0, sizeof (new_val ));
Printf ("Please input a new % s/n", column [I]. column_name );
Scanf ("% s", new_val );
Strcat (Psql, new_val );
Strcat (Psql ,"");
}

Strcat (Psql, "where id = ");
Printf ("Please input the ID that you want to change its value:/N ");
Scanf ("% s", new_id );
Strcat (Psql, new_id );
Strcat (Psql ,";");

Printf ("SQLite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;

}

Void db_delete (sqlite3 * dB, char * ptable_name)
{
Int ret = 0;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;
Char tmp_name [20];
Char tmp_id [3];

Psql = SQL;
Strcpy (Psql, "delete from ");
Strcat (Psql, ptable_name );
Strcat (Psql, "where id = ");
Printf ("Please input a ID that you want to delete:/N ");
Scanf ("% s", tmp_id );
Strcat (Psql, tmp_id );
Strcat (Psql ,";");

Printf ("/nsqlite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;
}

Void db_empty (sqlite3 * dB, char * ptable_name)
{
Int ret = 0;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;

Psql = SQL;
Strcpy (Psql, "delete from ");
Strcat (Psql, ptable_name );
Strcat (Psql ,";");

Printf ("/nsqlite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;
}

Void db_drop (sqlite3 * dB, char * ptable_name)
{
Int ret = 0;
Char * errmsg = NULL;
Char SQL [200];
Char * Psql;

Psql = SQL;
Strcpy (Psql, "Drop table ");
Strcat (Psql, ptable_name );
Strcat (Psql ,";");

Printf ("/nsqlite> % s/n", Psql );

Ret = sqlite3_exec (dB, Psql, null, null, & errmsg );
If (Ret! = Sqlite_ OK)
{
Printf ("error: % d: % s/n", RET, errmsg );
Exit (1 );
}

Return;
}