Dynamic sqlite3 database V1.0 using C language

Source: Internet
Author: User
Tags psql

/* 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;
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.