Logo Search packages:      
Sourcecode: happydigger version File versions

db.c

/*
 * happydigger - program for cataloging archaeological finds
 * Copyright (C) 2004 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.
 */

#include <gtk/gtk.h>
#include <sqlite.h>
#include <stdlib.h>
#include "support.h"

extern sqlite *db;
extern GtkWidget *window;

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


void
create_db (void)
{
      gchar *query, *err;
      gint ret, records = 0;
                              
      query = g_strdup_printf ("CREATE TABLE finds ("
            "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)"
            ");");
      ret = sqlite_exec (db, query, NULL, &records, &err);
      if (ret != SQLITE_OK)
            g_error (_("Error on CREATE: %s\n"), err);
      else
            g_print (_("Finds database succesfully created\n"));

}

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 finds;");
      ret = sqlite_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 finds;");
      ret = sqlite_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;
}

static int read_callback (void *data, int argc, char **argv, char **columnNames)
{
      GtkWidget *identry, *nrentry, *descriptionentry, *typeentry, *materialentry,
            *periodentry, *notes, *dateentry, *valueentry, *referenceentry,
            *finddateentry, *locationentry, *coordinatesentry, *spotnotes, 
            *image1entry, *image2entry, *image1, *image2;
      GtkTextBuffer *buffer;
      GdkPixbuf *pixbuf1, *pixbuf1scaled;
      GdkPixbuf *pixbuf2, *pixbuf2scaled;
      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");
      typeentry = lookup_widget (window, "typeentry");
      materialentry = lookup_widget (window, "materialentry");
      periodentry = lookup_widget (window, "periodentry");
      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");
      image1 = lookup_widget (window, "image1");
      image2 = lookup_widget (window, "image2");

      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]);
      gtk_entry_set_text (GTK_ENTRY(typeentry), argv[3]);
      gtk_entry_set_text (GTK_ENTRY(materialentry), argv[4]);
      gtk_entry_set_text (GTK_ENTRY(periodentry), argv[5]);
      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 (argv[14], NULL);
      if (pixbuf1)
      {
            pixbuf1scaled = gdk_pixbuf_scale_simple (pixbuf1, 100, 100, GDK_INTERP_BILINEAR);
            gtk_image_set_from_pixbuf(GTK_IMAGE(image1), pixbuf1scaled);
            g_object_unref (pixbuf1);
            g_object_unref (pixbuf1scaled);
      }
      else
      {
            pixbuf1 = gdk_pixbuf_new (GDK_COLORSPACE_RGB, TRUE, 8, 100, 100);
            gdk_pixbuf_fill (pixbuf1, 0x00000000);
            gtk_image_set_from_pixbuf(GTK_IMAGE(image1), pixbuf1);
            g_object_unref (pixbuf1);
      }

      pixbuf2 = gdk_pixbuf_new_from_file (argv[15], NULL);
      if (pixbuf2)
      {
            pixbuf2scaled = gdk_pixbuf_scale_simple (pixbuf2, 100, 100, GDK_INTERP_BILINEAR);
            gtk_image_set_from_pixbuf(GTK_IMAGE(image2), pixbuf2scaled);
            g_object_unref (pixbuf2);
            g_object_unref (pixbuf2scaled);
      }
      else
      {
            pixbuf2 = gdk_pixbuf_new (GDK_COLORSPACE_RGB, TRUE, 8, 100, 100);
            gdk_pixbuf_fill (pixbuf2, 0x00000000);
            gtk_image_set_from_pixbuf(GTK_IMAGE(image2), pixbuf2);
            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 finds;");
      ret = sqlite_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 finds where find=%d;", id);
      ret = sqlite_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, *typeentry, *materialentry,
            *periodentry, *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");
      typeentry = lookup_widget (window, "typeentry");
      materialentry = lookup_widget (window, "materialentry");
      periodentry = lookup_widget (window, "periodentry");
      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_editable_get_chars (GTK_EDITABLE(typeentry), 0, -1);   
      material = gtk_editable_get_chars (GTK_EDITABLE(materialentry), 0, -1); 
      period = gtk_editable_get_chars (GTK_EDITABLE(periodentry), 0, -1);     
      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 = sqlite_exec_printf (db, 
            "INSERT INTO finds VALUES "
            "(NULL, '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q', '%q');", 
            NULL, NULL, &err, 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, *typeentry, *materialentry,
            *periodentry, *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");
      typeentry = lookup_widget (window, "typeentry");
      materialentry = lookup_widget (window, "materialentry");
      periodentry = lookup_widget (window, "periodentry");
      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_editable_get_chars (GTK_EDITABLE(typeentry), 0, -1);   
      material = gtk_editable_get_chars (GTK_EDITABLE(materialentry), 0, -1); 
      period = gtk_editable_get_chars (GTK_EDITABLE(periodentry), 0, -1);     
      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 = sqlite_exec_printf (db, "UPDATE finds 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, 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