Cómo programar SQLite en C - Tutorial dos

Este tutorial es el segundo de una serie sobre la programación de SQLite en C. Si usted encontró este tutorial primero, por favor ve al Primer tutorial sobre Programación de SQLite en C.

En el tutorial anterior, se explica cómo configurar Visual Studio 2010/2012 (ya sea la versión gratuita Express o la comercial) para trabajar con SQLite como parte de su programa o llamado a través de un dll independiente.

Seguiremos a partir de ahí.

 

 

Bases de datos y tablas

SQLite almacena una colección de tablas en una sola base de datos de archivos, normalmente terminando en.db. Cada tabla es como una hoja de cálculo, consta de varias columnas y cada fila tiene valores.

Si ayuda, piense en cada fila como una estructura, con las columnas en la tabla que corresponden a los campos en la estructura.

Una tabla puede tener tantas filas como quepan en el disco. Hay un límite superior, pero es enorme, 18.446.744.073.709.551.616 para ser precisos.

Puedes leer los límites de SQLite en su sitio web. Una tabla puede tener hasta 2.000 columnas o, si recompila la fuente, puede maximizarla hasta un máximo de 32.767 columnas.

 

LA API de SQLite

Para usar SQLite, necesitamos hacer llamadas a la API. Puede encontrar una introducción a esta API en la página web oficial de Introducción a la interfaz C/C++ de SQLite. Es una colección de funciones y fácil de usar.

Primero, necesitamos manejar la base de datos. Esto es del tipo sqlite3 y es devuelto por una llamada a sqlite3_open( nombre de archivo, ** ppDB).

Después de eso, ejecutamos el SQL.

Hagamos una pequeña digresión primero y creemos una base de datos utilizable y algunas tablas usando SQLiteSpy. (Vea el tutorial anterior para enlaces a este y al Navegador de la Base de Datos SQLite).

 

Eventos y lugares

La base de datos about.db contendrá tres tablas para gestionar eventos en varios lugares.

Estos eventos serán fiestas, discotecas y conciertos y tendrán lugar en cinco sedes (alpha, beta, charlie, delta y echo). Cuando estás modelando algo como esto, a menudo ayuda empezar con una hoja de cálculo. Por simplicidad, guardaré una fecha, no una hora.

La hoja de cálculo tiene tres columnas: Fechas, lugar, tipo de evento y unos diez eventos como este. Las fechas van del 21 al 30 de junio de 2013.

Ahora SQLite no tiene un tipo de fecha explícita, por lo que es más fácil y rápido almacenarlo como un int y de la misma manera que Excel usa fechas (días desde el 1 de enero de 1900) tiene valores int 41446 a 41455. Si pones las fechas en una hoja de cálculo, entonces formatea la columna de fecha como un número con 0 decimales, se ve algo así:

 

Date,Venue,Event Type

41446,Alpha,Party

41447,Beta,Concert

41448,Charlie,Disco

41449,Delta ,Concert

41450,echo,Party

41451,Alpha,Disco

41452,Alpha,Party

41453,Beta,Party

41454,Delta ,Concert

41455,Echo,Part

Ahora podríamos almacenar estos datos en una tabla y para un ejemplo tan simple, probablemente sería aceptable. Sin embargo, una buena práctica de diseño de bases de datos requiere cierta normalización.

Los elementos de datos únicos, como el tipo de lugar, deben estar en su propia tabla y los tipos de evento (fiesta, etc.) también deben estar en uno.

Finalmente, como podemos tener múltiples tipos de eventos en múltiples lugares, (una relación de muchos a muchos) necesitamos una tercera mesa para sostenerlos.

Las tres mesas son:

 

  • lugares de reunión - contiene los cinco lugares de reunión
  • eventtypes - contiene los tres tipos de eventos
  • eventos - contiene la fecha más el id del lugar de celebración más el id del tipo de evento. También agregué un campo de descripción para este evento, por ejemplo, "Cumpleaños de Jim".

Las dos primeras tablas contienen los tipos de datos de modo que los lugares tienen nombres alfa a eco. También he añadido un número entero y he creado un índice para ello. Con el pequeño número de sedes (5) y tipos de eventos (3), se podría hacer sin un índice, pero con mesas más grandes, será muy lento. Así que cualquier columna en la que sea probable que se busque, agregue un índice, preferiblemente un número entero

El SQL para crear esto es:

create table venues(

idvenue int,

venue text)

create index ivenue on venues(ideventtype)

create table eventtypes(

ideventtype int,

eventtype text)

create index ieventtype on eventtypes(idvenue)

create table events(

idevent int,

date int,

ideventtype int,

idvenue int,

description Text)

create index ievent on events(date, idevent, ideventtype,idvenue)

El índice de la tabla de eventos tiene fecha, evento, tipo de evento y lugar de celebración. Esto significa que podemos consultar la tabla de eventos para "todos los eventos en una fecha", "todos los eventos en un lugar", "todas las fiestas", etc. y combinaciones de estos como "todas las fiestas en un lugar", etc.

Después de ejecutar las consultas de crear tabla SQL, se crean las tres tablas. Nota: He puesto todo ese sql en el archivo de texto create.sql e incluye datos para poblar algunas de las tres tablas.

Si pones ; al final de las líneas como he hecho en create.sql, entonces puedes por lotes y ejecutar todos los comandos de una sola vez. Sin el ; usted tiene que ejecutar cada uno por sí mismo. En SQLiteSpy, haz clic en F9 para ejecutarlo todo.

También he incluido sql para dejar caer las tres tablas dentro de los comentarios multilínea usando /* . Igual que en C. Seleccione las tres líneas y haga ctrl + F9 para ejecutar el texto seleccionado.

Estos comandos insertan los cinco lugares:

insert into venues (idvenue,venue) values (0,'Alpha');

insert into venues (idvenue,venue) values (1,'Bravo');

insert into venues (idvenue,venue) values (2,'Charlie');

insert into venues (idvenue,venue) values (3,'Delta');

insert into venues (idvenue,venue) values (4,'Echo');

De nuevo he incluido texto comentado a tablas vacías, con la eliminación de líneas. No se puede deshacer, así que ten cuidado con esto.

Sorprendentemente, con todos los datos cargados (aunque no mucho), el archivo de base de datos completo en el disco es de sólo 7 KB.

Datos del evento

En lugar de crear un montón de diez instrucciones de inserción, usé Excel para crear un archivo.csv para los datos del evento y luego usé la utilidad de línea de comandos SQLite3 (que viene con SQLite) y los siguientes comandos para importarlo.

Nota: Cualquier línea con un prefijo de punto (.) es un comando. Use .help para ver todos los comandos. Para ejecutar SQL, simplemente introdúzcalo sin prefijo de período.

.separator ,

.import "c:\\data\\aboutevents.csv" events

select * from events;

Tienes que usar barras negras dobles en la ruta de importación para cada carpeta. Sólo haga la última línea después de que el archivo.import haya tenido éxito. Cuando se ejecuta SQLite3, el separador predeterminado es un :, por lo que debe cambiarse a una coma antes de la importación.

Volver al código

Ahora que tenemos una base de datos completa, escribamos el código C para ejecutar esta consulta SQL que devuelve una lista de partes, con descripción, fechas y lugares.

select date,description, venue from events,venues

where ideventtype = 0

and events.idvenue = venues.idvenue

Esto hace una unión usando la columna de idvenue entre los eventos y la tabla de lugares para que obtengamos el nombre del lugar y no su valor int idvenue.

Funciones de la API de SQLite en C

Hay muchas funciones, pero sólo necesitamos un puñado. El orden de procesamiento es:

  • Abrir la base de datos con sqlite3_open(), salir si hay error al abrirla.
  • Preparar el SQL con sqlite3_prepare()
  • Haga un bucle usando slqite3_step() hasta que no haya más registros
  • (En el bucle) procesa cada columna con sqlite3_column....
  • Por último, llame a sqlite3_close(db)

Hay un paso opcional después de llamar a sqlite3_prepare donde cualquier parámetro pasado está vinculado, pero lo guardaremos para un futuro tutorial.

Así que en el programa que se enumeran a continuación el pseudo código para los pasos principales son:

// sqltest.c : Simple SQLite3 program in C by D. Bolton (C) 2013 http://cplus.about.com

#include <stdio.h>

#include "sqlite3.h"

#include <string.h>

#include <windows.h>

char * dbname="C:\\devstuff\\devstuff\\cplus\\tutorials\\c\\sqltest\\about.db";

char * sql = "select date,description, venue from events,venues where ideventtype = 0 and events.idvenue = venues.idvenue";

sqlite3 * db;

sqlite3_stmt *stmt;

char message[255];

int date;

char * description;

char * venue;

int main(int argc, char* argv[])

{

        /* open the database */

        int result=sqlite3_open(dbname,&db) ;

        if (result != SQLITE_OK) {

                printf("Failed to open database %s\n\r",sqlite3_errstr(result)) ;

                sqlite3_close(db) ;

                return 1;

        }

        printf("Opened db %s OK\n\r",dbname) ;

 

        /* prepare the sql, leave stmt ready for loop */

        result = sqlite3_prepare_v2(db, sql, strlen(sql)+1, &stmt, NULL) ;

        if (result != SQLITE_OK) {

                printf("Failed to prepare database %s\n\r",sqlite3_errstr(result)) ;

                sqlite3_close(db) ;

                return 2;

        }

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

        /* allocate memory for decsription and venue */

        description = (char *)malloc(100) ;

        venue = (char *)malloc(100) ;

        /* loop reading each row until step returns anything other than SQLITE_ROW */

        do {

                result = sqlite3_step (stmt) ;

                if (result == SQLITE_ROW) { /* can read data */

                         date = sqlite3_column_int(stmt,0) ;

                         strcpy(description, (char *)sqlite3_column_text(stmt,1)) ;

                         strcpy(venue, (char *)sqlite3_column_text(stmt,2)) ;

                         printf("On %d at %s for '%s' \n\r",date,venue,description) ;

                }

       } while (result == SQLITE_ROW) ;

    /* finish off */

        sqlite3_close(db) ;

        free(description) ;

        free(venue) ;

        return 0;

}

En el siguiente tutorial, miraré a update, e insertaré sql y explicaré cómo enlazar parámetros.

(0 votes)