int main(int argc, char **argv)
{
sqlite3 *db;
sqlite3_open("test.db", &db);
sqlite3_create_function( db, "hello_newman", 1, SQLITE_UTF8, NULL,
hello_newman, NULL, NULL);
/* Log SQL as it is executed. */
log_sql(db,1);
/* Call function with one text argument. */
fprintf(stdout, "Calling with one argument.\n");
print_sql_result(db, "select hello_newman('Jerry')");
/* Call function with two arguments. This will fail as we registered the
** function as taking only one argument. */
fprintf(stdout, "\nCalling with two arguments.\n");
print_sql_result(db, "select hello_newman ('Jerry', 'Elaine')");
/* Call function with no arguments. This will fail too */
fprintf(stdout, "\nCalling with no arguments.\n");
print_sql_result(db, "select hello_newman()");
/* Done */
sqlite3_close(db);
return 0;
}
int main(int argc, char **argv)
{
int rc;
sqlite3 *db;
rc = sqlite3_open("test.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
execute(db, "drop table episodes");
execute(db, "create table episodes(id int, cid int, name text)");
/* Positional Parameters */
fprintf(stdout, "Test Positional Parameters\n");
execute(db, "delete from episodes");
if(test_positional_params(db) != SQLITE_OK) {
goto end;
}
print_sql_result(db, "select * from episodes");
fprintf(stdout, "\n");
/* Numbered Parameters */
fprintf(stdout, "Test Numbered Parameters\n");
execute(db, "delete from episodes");
if(test_numbered_params(db) != SQLITE_OK) {
goto end;
}
print_sql_result(db, "select * from episodes");
fprintf(stdout, "\n");
/* Named Parameters */
fprintf(stdout, "Test Named Parameters\n");
execute(db, "delete from episodes");
if(test_named_params(db) != SQLITE_OK) {
goto end;
}
print_sql_result(db, "select * from episodes");
end:
sqlite3_close(db);
return 0;
}
int cmd_alter(int argc,const char **argv){
// check necessary param
check_cmd_args(argc,argv);
st_show_sql *stsql=(st_show_sql*)malloc(sizeof(st_show_sql));
char type[50];
char like[50];
const char *table="";
char sql[125];
while(*argv){
if(!strcmp(*argv,"--tables")){
stsql->type=*argv+2;
argv++;
}else if(!strcmp(*argv,"--columns")){
const char *t=*argv+2;
argv++;
if(!start_with(*argv,"--")){
usage("about show --columns table-name");
}
sprintf(type,"%s FROM %s%s",t,DBPRE,*argv);
stsql->type=type;
argv++;
}else if(!strcmp(*argv,"--create")){
const char *t=*argv+2;
argv++;
if(!start_with(*argv,"--")){
usage("about show --create table-name");
}
sprintf(type,"%s TABLE %s%s",t,DBPRE,*argv);
stsql->type=type;
argv++;
}else if(!strcmp(*argv,"--variables")){
stsql->type=*argv+2;
argv++;
}else if(!strcmp(*argv,"--like")){
argv++;
cat_sql_like(*argv,like);
argv++;
}else{
argv++;
}
}
stsql->table=table;
stsql->like=like;
parse_show_sql(stsql,sql);
MYSQL_RES *res=select(sql);
print_sql_result(res,1);
free_res(res);
return 0;
}
int main(int argc, char **argv)
{
int rc;
sqlite3 *db;
char *sql;
rc = sqlite3_open("test.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return 1;
}
setup(db);
/* I. Register SQL functions ---------------------------------------------*/
/* Generates and installs validation triggers. */
rc = sqlite3_create_function( db, "add_strict_type_check", 2, SQLITE_UTF8, db,
add_strict_type_check_udf, NULL, NULL);
/* Removes validation triggers. */
sqlite3_create_function( db, "drop_strict_type_check", 2, SQLITE_UTF8, db,
drop_strict_type_check_udf, NULL, NULL);
/* Convenience function for pulling a column's type from sqlite_master. It
* is a fine-grained 'PRAGMA table_info()'.*/
sqlite3_create_function( db, "column_type", 2, SQLITE_UTF8, db,
column_type_udf, NULL, NULL);
/* Type Validation: Called to validate integer. */
sqlite3_create_function( db, "validate_int", 1, SQLITE_UTF8, db,
validate_int_udf, NULL, NULL);
/* Type Validation: Called to validate long integer. Same UDF as above,
* different SQL function name. */
sqlite3_create_function( db, "validate_long", 1, SQLITE_UTF8, db,
validate_int_udf, NULL, NULL);
/* Type Validation: Called to validate float. */
sqlite3_create_function( db, "validate_double", 1, SQLITE_UTF8, db,
validate_double_udf, NULL, NULL);
/* Type Validation: Called to validate long integer. Same UDF as above,
* different SQL function name. */
sqlite3_create_function( db, "validate_float", 1, SQLITE_UTF8, db,
validate_double_udf, NULL, NULL);
/* II. Test ---------------------------------------------------------------*/
log_sql(db,1);
/* Add type check constraint trigger for types.id */
printf("1. Add strict typing:\n");
execute(db, "select add_strict_type_check('types', '*')");
printf("\n");
/* Insert a record with valid id */
printf("2. Insert integer value -- should succeed:\n");
execute(db, "insert into types (x) values (1)");
printf("\n");
/* Insert a record with invalid id type*/
printf("3. Update with invalid values -- should fail:\n");
execute(db, "update types set x = 'abc'");
execute(db, "update types set y = 'abc'");
printf("\n");
/* Remove type check constraint trigger for types.id */
printf("4. Remove strict typing\n");
execute(db, "select drop_strict_type_check('types', '*')");
printf("\n");
/* Insert a record with invalid id type*/
printf("5. Update with non-integer value -- should succeed:\n");
execute(db, "update types set x = 'not an int'");
printf("\n");
printf("6. Select records:\n");
print_sql_result(db, "select * from types");
printf("\n");
/* Test column_type() */
printf("7. Test column_type() UDF\n");
sql = "select column_type('types', 'id') as 'id',\n"
" column_type('types', 'x') as 'x',\n"
" column_type('types', 'y') as 'y'";
print_sql_result(db, sql);
printf("\n");
sqlite3_close(db);
return 0;
}
int cmd_select(int argc,const char **argv){
// check necessary param
check_cmd_args(argc,argv);
st_select_sql *stsql=(st_select_sql*)malloc(sizeof(st_select_sql));
stsql->select="*";
stsql->limit=" LIMIT 20";
char *from="";
char where[256]={""};
char order[128]={""};
char limit[16]={""};
char group[64]={""};
int has_limit=has_cmd_args(argv,"limit");
while(*argv){
if(!strcmp(*argv,"--table") || !strcmp(*argv,"-t")){
argv++;
from=(char*)*argv;
argv++;
}else if(!strcmp(*argv,"--select")){
argv++;
stsql->select=*argv;
argv++;
}else if(!strcmp(*argv,"--limit")){
argv++;
stsql->limit=cat_sql_limit(*argv,limit);
argv++;
}else if(!strcmp(*argv,"--order")){
argv++;
const char *field=*argv;
argv++;
if(*argv==NULL){
cat_sql_sort(field,"ASC",order);
}else{
if(start_with(*argv,"--")){
cat_sql_sort(field,*argv,order);
}else{
cat_sql_sort(field,"ASC",order);
}
}
}else if(!strcmp(*argv,"--group")){
argv++;
stsql->group=cat_sql_group(*argv,"",group);
argv++;
if(has_limit==0){
stsql->limit="";
}
}else if(!start_with(*argv,"--")){
const char *name=*argv+2;
if(!strcmp(name,"id"))
name=get_primary_key(from);
argv++;
const char *value=*argv;
cat_sql_where(name,value,where);
argv++;
}else{
argv++;
}
}
stsql->from=from;
stsql->where=where;
stsql->order=order;
stsql->group=group;
char sql[512];
parse_select_sql(stsql,sql);
MYSQL_RES *res=select(sql);
print_sql_result(res,1);
free_res(res);
return 0;
}
