Logo Search packages:      
Sourcecode: happydigger version File versions  Download package

db.c

/*
 * happydigger - program for cataloging archaeological finds
 * Copyright (C) 2004, 2005 Joop Stakenborg <pg4i@amsat.org>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Library General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */

 /* functions for creating tables and adding, removing and updating records */

#include <gtk/gtk.h>
#include <stdlib.h>
#include "db.h"
#include "support.h"
#include "preferences.h"

extern sqlite3 *db;
extern GtkWidget *window, *startupwindow;
extern preferencestype preferences;
extern statestype state;

/* struct used in some of the callbacks */
struct db_info
{
      int count;
      int line;
      int max;
      int maxid;
} dbinfo;

static int 
sqlite3_exec_printf (sqlite3* db, const char *fmt, sqlite3_callback callback, void *context, char **errmsg, ...)
{
      va_list args;
      va_start(args, errmsg);
      char *query = sqlite3_vmprintf(fmt, args);
      int res = sqlite3_exec(db, query, callback, context, errmsg);
      sqlite3_free(query);
      return res;
}

gint
create_db (gchar *name)
{
      gchar *query, *err;
      gint ret, records = 0;
                              
      query = g_strdup_printf ("CREATE TABLE %s ("
            "find INTEGER PRIMARY KEY,"

            "nr varchar (80),"
            "description varchar (80),"
            "type varchar (80),"
            "material varchar (80),"
            "period varchar (80),"
            "notes varchar (220),"
            "date varchar (80),"
            "value varchar (80),"
            "reference varchar (80),"

            "finddate varchar (80),"
            "location varchar (80),"
            "coordinates varchar (80),"
            "spotnotes varchar (220),"

            "image1 varchar (80),"
            "image2 varchar (80)"
            ");", name);
      ret = sqlite3_exec (db, query, NULL, &records, &err);
      return ret;
}


static int find_tables_callback (void *data, int argc, char **argv, char **columnNames)
{
      GtkWidget *tablecombo;

      tablecombo = lookup_widget (startupwindow, "tablecombo");
      gtk_combo_box_append_text (GTK_COMBO_BOX(tablecombo), argv[0]);
      return 0;
}

/* find tables in the current database */
void find_tables (void)
{
      gchar *err, *query;
      gint ret;

      query = g_strdup_printf
("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
      ret = sqlite3_exec (db, query, find_tables_callback, (void *)&dbinfo, &err);
      if (ret != SQLITE_OK)
            g_print (_("Error on getting tables: %s\n"), err);
      g_free (query);
}

static int max_callback (void *data, int argc, char **argv, char **columnNames)
{
      struct db_info *info=(struct db_info *)data;

      if (argv[0]) 
            info->max = atoi(argv[0]);
      return 0;
}

/* Find the number of entries in the database. Don't use 
 * "SELECT max(find) from finds;" here, it will show the 
 * highest find number instead of the number of records.
 */
gint max_db (void)
{
      gchar *err, *query;
      gint ret;

      query = g_strdup_printf ("SELECT count(*) from %s;", state.table);
      ret = sqlite3_exec (db, query, max_callback, (void *)&dbinfo, &err);
      if (ret != SQLITE_OK)
            g_print (_("Error on searching max: %s\n"), err);
      g_free (query);
      return dbinfo.max;
}

static int maxid_callback (void *data, int argc, char **argv, char **columnNames)
{
      struct db_info *info=(struct db_info *)data;

      if (argv[0]) 
            info->maxid = atoi(argv[0]);
      return 0;
}

gint max_id (void)
{
      gchar *err, *query;
      gint ret;

      query = g_strdup_printf ("SELECT max(find) from %s;", state.table);
      ret = sqlite3_exec (db, query, maxid_callback, (void *)&dbinfo, &err);
      if (ret != SQLITE_OK)
            g_print (_("Error on searching maxid: %s\n"), err);
      g_free (query);
      return dbinfo.maxid;
}

/* go through the list of types and set the correct associated combobox entry */
static void update_combo (GtkWidget *combo, gchar *arg, gchar *preferences)
{
      gchar **split;
      gint index = 0;

      split = g_strsplit (preferences, ",", 0);
      for (;;)
      {
            if (!split[index]) break;
            g_strstrip (split[index]);
            if (g_ascii_strcasecmp (arg, split[index]) == 0)
            {
                  gtk_combo_box_set_active (GTK_COMBO_BOX (combo), index);
                  break;
            }
            index ++;
      }
      g_strfreev (split);
}

static int read_callback (void *data, int argc, char **argv, char **columnNames)
{
      GtkWidget *identry, *nrentry, *descriptionentry, *typecombo, *materialcombo,
            *periodcombo, *notes, *dateentry, *valueentry, *referenceentry,
            *finddateentry, *locationentry, *coordinatesentry, *spotnotes, 
            *image1entry, *image2entry, *imagebutton1, *imagebutton2, *image1,
            *image2;
      GtkTextBuffer *buffer;
      GdkPixbuf *pixbuf1;
      GdkPixbuf *pixbuf2;
      struct db_info *info=(struct db_info *)data;
      
      if (info->line == info->count)
      {
      identry = lookup_widget (window, "identry");
      nrentry = lookup_widget (window, "nrentry");
      descriptionentry = lookup_widget (window, "descriptionentry");
      typecombo = lookup_widget (window, "typecombo");
      materialcombo = lookup_widget (window, "materialcombo");
      periodcombo = lookup_widget (window, "periodcombo");
      notes = lookup_widget (window, "notes");
      dateentry = lookup_widget (window, "dateentry");
      valueentry = lookup_widget (window, "valueentry");
      referenceentry = lookup_widget (window, "referenceentry");

      finddateentry = lookup_widget (window, "finddateentry");
      locationentry = lookup_widget (window, "locationentry");
      coordinatesentry = lookup_widget (window, "coordinatesentry");
      spotnotes = lookup_widget (window, "spotnotes");

      image1entry = lookup_widget (window, "image1entry");
      image2entry = lookup_widget (window, "image2entry");
      imagebutton1 = lookup_widget (window, "imagebutton1");
      imagebutton2 = lookup_widget (window, "imagebutton2");

      gtk_entry_set_text (GTK_ENTRY(identry), argv[0]);
      gtk_entry_set_text (GTK_ENTRY(nrentry), argv[1]);
      gtk_entry_set_text (GTK_ENTRY(descriptionentry), argv[2]);
      update_combo (typecombo, argv[3], preferences.types);
      update_combo (materialcombo, argv[4], preferences.materials);
      update_combo (periodcombo, argv[5], preferences.periods);
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (notes));
      gtk_text_buffer_set_text (buffer, argv[6], -1);
      gtk_entry_set_text (GTK_ENTRY(dateentry), argv[7]);
      gtk_entry_set_text (GTK_ENTRY(valueentry), argv[8]);
      gtk_entry_set_text (GTK_ENTRY(referenceentry), argv[9]);

      gtk_entry_set_text (GTK_ENTRY(finddateentry), argv[10]);
      gtk_entry_set_text (GTK_ENTRY(locationentry), argv[11]);
      gtk_entry_set_text (GTK_ENTRY(coordinatesentry), argv[12]);
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (spotnotes));
      gtk_text_buffer_set_text (buffer, argv[13], -1);

      gtk_entry_set_text (GTK_ENTRY(image1entry), argv[14]);
      gtk_entry_set_text (GTK_ENTRY(image2entry), argv[15]);


      /* rescale images if they are available */
      pixbuf1 = gdk_pixbuf_new_from_file_at_size (argv[14], 150, 150, NULL);
      if (pixbuf1)
      {
            imagebutton1 = lookup_widget (window, "imagebutton1");
            gtk_container_foreach (GTK_CONTAINER(imagebutton1),
                  (GtkCallback)gtk_widget_destroy, NULL);
            image1 = gtk_image_new_from_pixbuf (pixbuf1);
            gtk_container_add (GTK_CONTAINER (imagebutton1), image1);
            gtk_widget_show (image1);
            g_object_unref (pixbuf1);
      }

      pixbuf2 = gdk_pixbuf_new_from_file_at_size (argv[15], 150, 150, NULL);
      if (pixbuf2)
      {
            imagebutton2 = lookup_widget (window, "imagebutton2");
            gtk_container_foreach (GTK_CONTAINER(imagebutton2),
                  (GtkCallback)gtk_widget_destroy, NULL);
            image2 = gtk_image_new_from_pixbuf (pixbuf2);
            gtk_container_add (GTK_CONTAINER (imagebutton2), image2);
            gtk_widget_show (image2);
            g_object_unref (pixbuf2);
      }

      }
      info->count++;
      return 0;
}

/* Load a record. Use the struct to keep track of the record number.
 * We can't use "SELECT * from finds WHERE find=<number>;" here.
 * Although <number> is a unique integer, it does not represent 
 * record numbers, especially if you have deleted records between 1
 * and max, e.g. <number> can be "1 2 8 9 10 11 13 14".
 */
gint
load_from_db (gint line)
{
      gint ret;
      gchar *query, *err;

      dbinfo.line = line;
      dbinfo.count = 1;
      query = g_strdup_printf ("SELECT * from %s;", state.table);
      ret = sqlite3_exec (db, query, read_callback, (void *)&dbinfo, &err);
      if (ret != SQLITE_OK)
            g_print ("Error on SELECT: %s\n", err);
      return ret;
}

void
delete_from_db (gint id)
{
      gint ret;
      gchar *query, *err;
                              
      query = g_strdup_printf ("DELETE from %s where find=%d;", state.table, id);
      ret = sqlite3_exec (db, query, NULL, NULL, &err);
      if (ret != SQLITE_OK)
            g_print (_("Error on DELETE: %s\n"), err);
      return;
}

/* add a new record and return it's id */
gint
add_to_db (void)
{
      gchar *nr, *description, *type, *material, *period, *text,
            *date, *value, *reference, *finddate, *location, *coordinates, 
            *spottext, *image1, *image2, *err;
      GtkWidget *nrentry, *descriptionentry, *typecombo, *materialcombo,
            *periodcombo, *notes, *dateentry, *valueentry, *referenceentry,
            *finddateentry, *locationentry, *coordinatesentry, *spotnotes, 
            *image1entry, *image2entry;
      gint ret;
      GtkTextBuffer *buffer;
      GtkTextIter start, end;

      nrentry = lookup_widget (window, "nrentry");
      descriptionentry = lookup_widget (window, "descriptionentry");
      typecombo = lookup_widget (window, "typecombo");
      materialcombo = lookup_widget (window, "materialcombo");
      periodcombo = lookup_widget (window, "periodcombo");
      notes = lookup_widget (window, "notes");
      dateentry = lookup_widget (window, "dateentry");
      valueentry = lookup_widget (window, "valueentry");
      referenceentry = lookup_widget (window, "referenceentry");

      finddateentry = lookup_widget (window, "finddateentry");
      locationentry = lookup_widget (window, "locationentry");
      coordinatesentry = lookup_widget (window, "coordinatesentry");
      spotnotes = lookup_widget (window, "spotnotes");

      image1entry = lookup_widget (window, "image1entry");
      image2entry = lookup_widget (window, "image2entry");

      nr = gtk_editable_get_chars (GTK_EDITABLE(nrentry), 0, -1);
      description = gtk_editable_get_chars (GTK_EDITABLE(descriptionentry), 0, -1);
      type = gtk_combo_box_get_active_text (GTK_COMBO_BOX(typecombo));  
      material = gtk_combo_box_get_active_text (GTK_COMBO_BOX(materialcombo));      
      period = gtk_combo_box_get_active_text (GTK_COMBO_BOX(periodcombo));
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (notes));
      gtk_text_buffer_get_start_iter (buffer, &start);
      gtk_text_buffer_get_end_iter (buffer, &end);
      text = gtk_text_buffer_get_text (buffer, &start, &end, TRUE);
      date = gtk_editable_get_chars (GTK_EDITABLE(dateentry), 0, -1);   
      value = gtk_editable_get_chars (GTK_EDITABLE(valueentry), 0, -1); 
      reference = gtk_editable_get_chars (GTK_EDITABLE(referenceentry), 0, -1);     

      finddate = gtk_editable_get_chars (GTK_EDITABLE(finddateentry), 0, -1); 
      location = gtk_editable_get_chars (GTK_EDITABLE(locationentry), 0, -1); 
      coordinates = gtk_editable_get_chars (GTK_EDITABLE(coordinatesentry), 0, -1); 
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (spotnotes));
      gtk_text_buffer_get_start_iter (buffer, &start);
      gtk_text_buffer_get_end_iter (buffer, &end);
      spottext = gtk_text_buffer_get_text (buffer, &start, &end, TRUE);

      image1 = gtk_editable_get_chars (GTK_EDITABLE(image1entry), 0, -1);     
      image2 = gtk_editable_get_chars (GTK_EDITABLE(image2entry), 0, -1);

      ret = sqlite3_exec_printf (db, 
            "INSERT INTO %q VALUES "
"(NULL, '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q');", 
            NULL, NULL, &err, state.table, nr, description, type, material, period,
            text, date, value, reference, finddate, location, coordinates, spottext,
            image1, image2);
      if (ret != SQLITE_OK)
            g_print (_("Error on INSERT: %s\n"), err);

      g_free (nr);
      g_free (description);
      g_free (type);
      g_free (material);
      g_free (period);
      g_free (text);
      g_free (date);
      g_free (value);
      g_free (reference);

      g_free (finddate);
      g_free (location);
      g_free (coordinates);
      g_free (spottext);

      g_free (image1);
      g_free (image2);

      return max_id ();
}

void
update_db (gint id)
{
      gchar *nr, *description, *type, *material, *period, *text,
            *date, *value, *reference, *finddate, *location, *coordinates, 
            *spottext, *image1, *image2, *err;
      GtkWidget *nrentry, *descriptionentry, *typecombo, *materialcombo,
            *periodcombo, *notes, *dateentry, *valueentry, *referenceentry,
            *finddateentry, *locationentry, *coordinatesentry, *spotnotes, 
            *image1entry, *image2entry;
      gint ret;
      GtkTextBuffer *buffer;
      GtkTextIter start, end;

      nrentry = lookup_widget (window, "nrentry");
      descriptionentry = lookup_widget (window, "descriptionentry");
      typecombo = lookup_widget (window, "typecombo");
      materialcombo = lookup_widget (window, "materialcombo");
      periodcombo = lookup_widget (window, "periodcombo");
      notes = lookup_widget (window, "notes");
      dateentry = lookup_widget (window, "dateentry");
      valueentry = lookup_widget (window, "valueentry");
      referenceentry = lookup_widget (window, "referenceentry");

      finddateentry = lookup_widget (window, "finddateentry");
      locationentry = lookup_widget (window, "locationentry");
      coordinatesentry = lookup_widget (window, "coordinatesentry");
      spotnotes = lookup_widget (window, "spotnotes");

      image1entry = lookup_widget (window, "image1entry");
      image2entry = lookup_widget (window, "image2entry");

      nr = gtk_editable_get_chars (GTK_EDITABLE(nrentry), 0, -1);
      description = gtk_editable_get_chars (GTK_EDITABLE(descriptionentry), 0, -1);
      type = gtk_combo_box_get_active_text (GTK_COMBO_BOX(typecombo));  
      material = gtk_combo_box_get_active_text (GTK_COMBO_BOX(materialcombo));      
      period = gtk_combo_box_get_active_text (GTK_COMBO_BOX(periodcombo));
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (notes));
      gtk_text_buffer_get_start_iter (buffer, &start);
      gtk_text_buffer_get_end_iter (buffer, &end);
      text = gtk_text_buffer_get_text (buffer, &start, &end, TRUE);
      date = gtk_editable_get_chars (GTK_EDITABLE(dateentry), 0, -1);   
      value = gtk_editable_get_chars (GTK_EDITABLE(valueentry), 0, -1); 
      reference = gtk_editable_get_chars (GTK_EDITABLE(referenceentry), 0, -1);     

      finddate = gtk_editable_get_chars (GTK_EDITABLE(finddateentry), 0, -1); 
      location = gtk_editable_get_chars (GTK_EDITABLE(locationentry), 0, -1); 
      coordinates = gtk_editable_get_chars (GTK_EDITABLE(coordinatesentry), 0, -1); 
      buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (spotnotes));
      gtk_text_buffer_get_start_iter (buffer, &start);
      gtk_text_buffer_get_end_iter (buffer, &end);
      spottext = gtk_text_buffer_get_text (buffer, &start, &end, TRUE);

      image1 = gtk_editable_get_chars (GTK_EDITABLE(image1entry), 0, -1);     
      image2 = gtk_editable_get_chars (GTK_EDITABLE(image2entry), 0, -1);
      ret = sqlite3_exec_printf (db, "UPDATE %q SET "
            "nr='%q',description='%q',type='%q',material='%q',period='%q',"
            "notes='%q',date='%q',value='%q',reference='%q',"
            "finddate='%q',location='%q',coordinates='%q',spotnotes='%q',"
            "image1='%q',image2='%q' "
            "WHERE find=%d;", 
            NULL, NULL, &err, state.table, nr, description, type, material, period,
            text, date, value, reference, finddate, location, coordinates, spottext,
            image1, image2, id);
      if (ret != SQLITE_OK)
            g_print (_("Error on UPDATE: %s\n"), err);

      g_free (nr);
      g_free (description);
      g_free (type);
      g_free (material);
      g_free (period);
      g_free (text);
      g_free (date);
      g_free (value);
      g_free (reference);

      g_free (finddate);
      g_free (location);
      g_free (coordinates);
      g_free (spottext);

      g_free (image1);
      g_free (image2);
}

Generated by  Doxygen 1.6.0   Back to index