Tutorial dua - Membuat database, mengisi dan menjalankan query SQL

Tutorial ini adalah yang kedua dalam seri pemrograman SQLite di C. Jika Anda menemukan tutorial pertama ini, silakan kunjungi Pertama tutorial Pemrograman SQLite di C.

Baru ke database? Baca Apa adalah sebuah database relasional?
Dalam tutorial sebelumnya saya menjelaskan bagaimana untuk set-up Visual Studio 2010/2012 (baik gratis Express versi atau yang komersial) untuk bekerja dengan SQLite sebagai bagian dari program atau disebut melalui dll mandiri. Kami akan melanjutkan dari sana.

Database dan Tabel

SQLite menyimpan koleksi tabel dalam database file tunggal, biasanya berakhir dengan. Db. Setiap tabel seperti spreadsheet, terdiri dari sejumlah kolom dan setiap baris memiliki nilai.

Jika membantu, memikirkan setiap baris sebagai struct, dengan kolom dalam tabel sesuai dengan bidang dalam struct.

Sebuah tabel dapat memiliki banyak baris yang akan muat pada disk. Ada batas atas tapi besar 18.446.744.073.709.551.616 nya harus tepat.

Anda dapat membaca batas SQLite di situs web mereka. Sebuah tabel dapat memiliki hingga 2.000 kolom atau jika anda mengkompilasi ulang sumber, Anda dapat max untuk awesome 32.767 kolom.

THE SQLite API

Untuk menggunakan SQLite, kita perlu untuk membuat panggilan ke API. Anda dapat menemukan pengenalan API ini pada Pengenalan resmi SQLite C / C + + halaman web Interface. Ini adalah kumpulan fungsi dan mudah digunakan.

Pertama kita perlu pegangan ke database. Ini adalah tipe sqlite3 dan dikembalikan oleh panggilan untuk sqlite3_open (nama file, ** PPDB). Setelah itu kita jalankan SQL.

Mari kita sedikit menyimpang pertama meskipun dan membuat database yang dapat digunakan dan beberapa tabel menggunakan SQLiteSpy. (Lihat tutorial sebelumnya untuk link ke itu dan SQLite Database Browser).

Acara dan Raga

Database about.db akan menggelar tiga meja untuk mengelola kegiatan di beberapa tempat. Peristiwa ini akan menjadi pesta, disko dan konser dan akan berlangsung di lima tempat (alpha, beta, charlie, delta dan echo). Ketika Anda pemodelan sesuatu seperti ini, sering kali membantu untuk memulai dengan spreadsheet. Demi kesederhanaan, saya hanya akan menyimpan tanggal tidak waktu.

Spreadsheet memiliki tiga kolom: Tanggal, Venue, Jenis Acara dan sekitar sepuluh peristiwa seperti ini. Tanggal dijalankan dari tanggal 21 sampai 30 Juni 2013.

Sekarang SQLite tidak memiliki eksplisit jenis tanggal, sehingga lebih mudah dan lebih cepat untuk menyimpannya sebagai int dan cara yang sama bahwa Excel menggunakan tanggal (hari sejak Jan 1, 1900) memiliki nilai-nilai int 41.446-41.455. Jika Anda meletakkan tanggal dalam spreadsheet kemudian format kolom tanggal sebagai nomor dengan 0 tempat desimal, itu terlihat seperti ini:

Tanggal, Venue, Event Type
41.446, Alpha, Partai
41.447, Beta, Konser
41.448, Charlie, Disco
41.449, Delta, Konser
41.450, echo, Partai
41.451, Alpha, Disco
41.452, Alpha, Partai
41.453, Beta, Partai
41.454, Delta, Konser
41.455, Echo, Bagian
Sekarang kita bisa menyimpan data ini dalam satu meja dan untuk suatu contoh sederhana, mungkin akan diterima. Namun praktek desain database yang baik memerlukan beberapa normalisasi.

Data barang-barang unik seperti jenis tempat harus dalam tabel sendiri dan jenis acara (pesta dll) juga harus dalam satu. Akhirnya seperti yang kita dapat memiliki beberapa jenis acara di beberapa tempat, (a banyak hubungan) kita membutuhkan tabel ketiga untuk menampung.

Tiga tabel adalah:

tempat - memegang semua lima tempat
eventtypes - memegang semua tiga jenis acara
Peristiwa - memegang tanggal ditambah tempat id ditambah acara Jenis id. Saya juga menambahkan bidang deskripsi untuk acara ini misalnya "Jim Birthday".
Dua tabel pertama memegang jenis data sehingga tempat memiliki nama alpha echo. Saya telah menambahkan sebuah id integer sebagai baik dan membuat indeks untuk itu. Dengan sejumlah kecil tempat (5) dan jenis acara (3), itu bisa dilakukan tanpa indeks, tetapi dengan tabel yang lebih besar itu akan menjadi sangat lambat. Jadi setiap kolom yang kemungkinan akan dicari, menambahkan indeks, sebaiknya bilangan bulat

SQL untuk membuat ini:

membuat tempat tabel (
idvenue int,
teks venue)

membuat indeks ivenue di tempat (ideventtype)

membuat tabel eventtypes (
ideventtype int,
Eventtype teks)

membuat indeks ieventtype pada eventtypes (idvenue)

membuat acara tabel (
idevent int,
tanggal int,
ideventtype int,
idvenue int,
Teks deskripsi)

membuat indeks ievent pada peristiwa (tanggal, idevent, ideventtype, idvenue)
Indeks pada peristiwa meja memiliki tanggal, idevent, jenis acara dan tempat. Itu berarti kita bisa query tabel acara "semua acara kencan", "semua peristiwa di tempat yang", "semua pihak" dll dan kombinasi dari orang-orang seperti "semua pihak di tempat yang" dll

Setelah menjalankan SQL membuat tabel query, tiga tabel dibuat. Catatan Aku telah menempatkan semua yang sql dalam file teks create.sql dan itu termasuk data untuk mengisi beberapa tiga tabel.

Jika Anda menempatkan, di akhir baris seperti yang saya lakukan di create.sql maka Anda dapat batch dan menjalankan semua perintah dalam satu pergi. Tanpa, Anda harus menjalankan masing-masing dengan sendirinya. Dalam SQLiteSpy, klik F9 untuk menjalankan semuanya.

Saya juga termasuk sql untuk menjatuhkan ketiga tabel di dalam komentar multi-line menggunakan / * .. * / Sama seperti di C. Cukup pilih tiga baris dan melakukan ctrl + F9 untuk mengeksekusi teks yang dipilih.

Perintah-perintah ini memasukkan lima tempat:

masukkan ke dalam tempat (idvenue, venue) nilai (0, 'Alpha');
masukkan ke dalam tempat (idvenue, venue) nilai (1, 'Bravo');
masukkan ke dalam tempat (idvenue, venue) nilai (2, 'Charlie');
masukkan ke dalam tempat (idvenue, venue) nilai (3, 'Delta');
masukkan ke dalam tempat (idvenue, venue) nilai (4, 'Echo');
Sekali lagi saya telah menyertakan tanda komentar teks ke tabel kosong, dengan menghapus dari baris. Ada tidak membatalkan jadi hati-hati dengan ini!

Hebatnya, dengan semua data dimuat (memang tidak banyak) database seluruh file pada disk hanya 7KB.

Data acara

Daripada membangun sekelompok sepuluh pernyataan insert, saya menggunakan Excel untuk membuat file csv untuk data acara. Dan kemudian digunakan SQLite3 utilitas baris perintah (yang datang dengan SQLite) dan perintah berikut untuk mengimpor.

Catatan: (.) ​​Setiap sejalan dengan periode prefix adalah perintah. Gunakan. Membantu untuk melihat semua perintah. Untuk menjalankan SQL ketik saja di tanpa awalan periode.

Pemisah.,
Impor. "C: \ \ data \ \ aboutevents.csv" peristiwa
pilih * dari peristiwa;
Anda harus menggunakan blackslashes ganda \ \ di jalur impor untuk setiap folder. Hanya melakukan baris terakhir setelah. Impor telah berhasil. Ketika SQLite3 menjalankan pemisah standar adalah: sehingga harus diubah menjadi koma sebelum impor.

Kembali ke Kode

Sekarang kita memiliki database penuh diisi, mari kita menulis kode C untuk menjalankan query SQL yang mengembalikan daftar partai, dengan deskripsi, tanggal dan tempat.

Baru SQL? Baca Apa itu SQL?
pilih tanggal, deskripsi, tempat dari peristiwa, tempat
mana ideventtype = 0
dan events.idvenue = venues.idvenue
Hal ini bergabung menggunakan kolom idvenue antara peristiwa dan meja tempat sehingga kami mendapatkan nama tempat yang tidak idvenue nilai int.

SQLite C API Fungsi

Ada banyak fungsi tetapi kita hanya perlu segelintir. Urutan pengolahan:

Buka Database dengan sqlite3_open (), keluar jika telah kesalahan membukanya.
Siapkan SQL dengan sqlite3_prepare ()
Lingkaran menggunakan slqite3_step () sampai catatan tidak lebih
(Dalam loop) memproses setiap kolom dengan sqlite3_column ...
Akhirnya panggilan sqlite3_close (db)
Ada langkah opsional setelah memanggil sqlite3_prepare mana setiap disahkan pada parameter terikat, namun kami akan menyimpan itu untuk tutorial masa depan.

Jadi dalam program yang tercantum di bawah kode semu untuk langkah-langkah utama adalah:

Buka database.
Siapkan sql
do {
  if (Langkah = SQLITE_OK)
    {
      Ekstrak tiga kolom dan output)
    }
  } Sementara langkah == SQLITE_OK
Tutup Db
Sql kembali tiga nilai jadi jika sqlite3.step () == SQLITE_ROW maka nilai-nilai yang disalin dari jenis kolom yang sesuai. Saya telah menggunakan int dan teks. Saya menampilkan tanggal sebagai nomor tetapi merasa bebas untuk mengubahnya menjadi kencan.

Ambil contoh kode (juga tercantum di bawah).
Daftar Contoh Kode

/ / Sqltest.c: Program SQLite3 sederhana dalam C dengan D. Bolton (C) 2013 http://cplus.about.com

# Include
# Include "sqlite3.h"
# Include <string.h>
# Include <windows.h>

char * dbname = "C: \ \ devstuff \ \ devstuff \ \ CPLUS \ \ tutorial \ \ c \ \ sqltest \ \ about.db";
char * sql = "pilih tanggal, deskripsi, tempat dari peristiwa, tempat di mana ideventtype = 0 dan events.idvenue = venues.idvenue";

sqlite3 * db;
sqlite3_stmt * stmt;
Char pesan [255];

int tanggal;
char * deskripsi;
char * tempat;

int main (int argc, char * argv [])
{
        / * Membuka database * /
        int hasil = sqlite3_open (dbname, & db);
        if (hasil! = SQLITE_OK) {
                printf ("Gagal membuka database% s \ n \ r", sqlite3_errstr (hasil));
                sqlite3_close (db);
                kembali 1;
        }
        printf ("Dibuka db% s OK \ n \ r", dbname);

        / * Mempersiapkan sql, biarkan STMT siap untuk loop * /
        hasil = sqlite3_prepare_v2 (db, sql, strlen (sql) +1, & stmt, NULL);
        if (hasil! = SQLITE_OK) {
                printf ("Gagal untuk menyediakan database% s \ n \ r", sqlite3_errstr (hasil));
                sqlite3_close (db);
                kembali 2;
        }

        printf ("SQL disiapkan ok \ n \ r");

        / * Mengalokasikan memori untuk decsription dan tempat * /
        description = (char *) malloc (100);
        tempat = (char *) malloc (100);

        / * Lingkaran membaca setiap baris sampai langkah mengembalikan apa pun selain SQLITE_ROW * /
        do {
                Hasil = sqlite3_step (stmt);
                if (hasil == SQLITE_ROW) {/ * dapat membaca data * /
                         date = sqlite3_column_int (stmt, 0);
                         strcpy (deskripsi, (char *) sqlite3_column_text (stmt, 1));
                         strcpy (venue, (char *) sqlite3_column_text (stmt, 2));
                         printf ("Pada% d di% s untuk '% s' \ n \ r", tanggal, tempat, deskripsi);
                }
       } Sementara (hasil == SQLITE_ROW);

    / * Finish off * /
        sqlite3_close (db);
        bebas (deskripsi);
        gratis (tempat);
        return 0;