KinkumaDesign

I needed to learn SQLite for my client work and had enjoyed it.

on terminal

Fortunately SQLite is pre installed in mac OS.

So you can create database instantly.
This code creates database named hello.db.
But actual file won’t create if you don’t execute any SQL command.

sqlite3 hello.db

If you finish sqlite, type “.exit” .

You can configure settings of SQLite with using ~/.sqliterc .

.echo ON
.mode column
.headers ON
.nullvalue "NULL"

If you put this file, sqlite start with these settings.

Create table.

sqlite> create table hello (id integer primary key, message text);

Insert row.

sqlite> insert into hello (message) values ('hello');

You have to be careful typing string literal.
In SQLite string literal is recommended to delimite with single quotes.

Select data

sqlite> select * from hello;

OK. Although these are the most basic SQLite command in terminal, these are enough to develop.

Let’s start to develop in C.

SQLite with gcc

I am interested in gcc. Because I wonder if I don’t have to use XCode when I compile C.

mysqlite1.c

#include <stdio.h>
#include <string.h>
#include "/usr/include/sqlite3.h"

int main(){
	sqlite3 *db;
	int result, id;
	sqlite3_stmt *stmt;
	char *sql, *name;

	result = sqlite3_open_v2("hello.db", &db, SQLITE_OPEN_READONLY, NULL);
	if(result != SQLITE_OK){
		printf("database opened\n");
		sqlite3_close(db);
		return 1;
	}

	sql = "select * from hello;";
	result = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);

	if(result != SQLITE_OK){
		printf("statement error");
		return 1;
	}

	result = sqlite3_step(stmt);
	while(result == SQLITE_ROW){
		id = sqlite3_column_int(stmt, 0);
		name = (char *)sqlite3_column_text(stmt, 1);
		printf("id = %d, name = %s\n", id, name);
		result = sqlite3_step(stmt);
	}
	sqlite3_finalize(stmt);
	sqlite3_close(db);
	return 0;
}

As mac has sqlite3 library, we can include sqlite’s header file.
#include “/usr/include/sqlite3.h”

Let’s compile it in terminal.
If the compiling succeseed, there is a file named mysqlite1.

gcc -Wall -L/usr/lib/sqlite3 mysqlite1.c -lsqlite3 -o mysqlite1

Test executing.

./mysqlite1

We can also compile sources with makefile.
Makefile is convenient way to compile multiple sources or link library.

If you make makefile, then it may be this code.

makefile

CC=gcc
CFLAGS=-Wall
all: mysqlite1

mysqlite1: mysqlite1.o
	$(CC) $(CFLAGS) -L/usr/lib/sqlite3 mysqlite1.o -lsqlite3 -o mysqlite1

mysqlite1.o: mysqlite1.c
	$(CC) $(CFLAGS) -c mysqlite1.c

clean:
	rm -f *.o mysqlite1

After you put this file named makefile, execute this command.

make

If the command execute successfully, mysqlite1 exists in current directory.
And if “clean” command executed, it removes object files( *.o) and mysqlite1.

make clean

Furthermore

This article is based on these books.
>> The Definitive Guide to SQLite
>>An Introduction to GCC: For the GNU Compilers GCC and G++

And these sites.
>> Makefiles by example