分类: 2010 April 21

GtkClist展示MySQL数据(2)

之前困惑于GtkClist中数据类型与MySQL数据类型之间转换的问题,在gtk-app-dvel-list邮件列表上发了一个主题提问:

Arthur1989 says:

Hello, I used clist to display data iI fetch from mysql server,but when writing data back into the table of database, I got this question: the data type of clist is always gchar *, while there are quite a lot of other data types in mysql. What can I do to get this question solved? Any tips will be appreciated.

今天发现居然有人把实现的源码都贴到邮件里了..转到博客记下.

Shawn Bakhtlar(***@hotmail.com)replys:


//Ran into the same problem.

//I use a structure like

_IsiField {

	 int type,
	  int pos,
	   int ....

}

//Then create my own list with

_IsiList {

	 GList Fields;
	  GList rows;

}


//Every time retrieve a set of values, I have a routing which sets type to a G_TYPE, which corresponds to the MYSQL_TYPE


//Here is what I do:

GList *
isi_database_fetch_fields(IsiDatabase *self)
{

   MYSQL_FIELD *field;
   IsiFields *l;
   GList *gl = NULL;
   guint column = 0;

   /* Sanity Check */
   g_return_val_if_fail(self != NULL, NULL);
   g_return_val_if_fail(self->priv != NULL, NULL);
   g_return_val_if_fail(self->priv->dispose_has_run != TRUE, NULL);
   g_return_val_if_fail(self->priv->res != NULL, NULL);

   /* Rewind the feild set */
   mysql_field_seek(self->priv->res,0L);


   while((field = mysql_fetch_field(self->priv->res)))
   {
	   /* Initialize a new IsiFields structure */
	   //l = (IsiFields*) g_new0(IsiFields, 1);
	   l = g_new0(IsiFields, 1);

	   /* Set the values */
	   l->alias = g_strdup(field->name);
	   l->name = g_strdup(field->org_name);
	   l->length = field->length;

	   /* always make fields visable */

	   l->hidden = FALSE;
	   l->sortable = FALSE;
	   l->pos = column++;


	   switch (field->type){

		   /* Integer types */
		   case MYSQL_TYPE_TINY:
		   case MYSQL_TYPE_SHORT:
		   case MYSQL_TYPE_INT24:

			   /* Check for signage */
			   if (field->flags & UNSIGNED_FLAG)
				   l->type = G_TYPE_UINT;
			   else
				   l->type = G_TYPE_INT;

			   break;


			   /* Long types */
		   case MYSQL_TYPE_LONG:
		   case MYSQL_TYPE_LONGLONG:

			   /* Check for signage */
			   if (field->flags & UNSIGNED_FLAG)
				   l->type = G_TYPE_ULONG;
			   else
				   l->type = G_TYPE_LONG;
			   break;


			   /* Decimal types */
		   case MYSQL_TYPE_DECIMAL:
		   case MYSQL_TYPE_NEWDECIMAL:
		   case MYSQL_TYPE_FLOAT:
		   case MYSQL_TYPE_DOUBLE:
			   l->type = G_TYPE_DOUBLE;
			   break;

			   /* Bit types */
		   case MYSQL_TYPE_BIT:
			   l->type = G_TYPE_BOOLEAN;
			   break;

			   /* ENUM types */
		   case MYSQL_TYPE_ENUM:
			   l->type = G_TYPE_ENUM;
			   break;

			   /* All other types */
		   default:
		   case MYSQL_TYPE_STRING:
		   case MYSQL_TYPE_VAR_STRING:
		   case MYSQL_TYPE_BLOB:
		   case MYSQL_TYPE_SET:
		   case MYSQL_TYPE_TIMESTAMP:
		   case MYSQL_TYPE_DATE:
		   case MYSQL_TYPE_TIME:
		   case MYSQL_TYPE_DATETIME:
		   case MYSQL_TYPE_YEAR:
		   case MYSQL_TYPE_GEOMETRY:
		   case MYSQL_TYPE_NULL:

			   if(l->length <= 1){
				   l->type = G_TYPE_CHAR;
			   }else{
				   l->type = G_TYPE_STRING;
			   }
			   break;
	   }


	   /*DEBUG*/
	   //g_print("%s %d %d \n", l->alias,l->type,l->length);

	   /* Save pointer to list */
	   gl = g_list_append(gl,(gpointer)l);

   }

   return gl;
}



//now convert the row data to a GList and you have two GLists in your one lists, one with the field header info, the other with the data.
//and create the liststore like this:

GtkTreeModel *
isi_display_liststore_create(IsiDisplay *self, GList *fields)
{
	guint num_fields, i;
	IsiFields *l;
	GtkTreeModel *model;
	GType *types;
	guint search_col_adj = 0;

	/* Sanity Check */
	g_return_val_if_fail(self != NULL, NULL);
	g_return_val_if_fail(self->priv != NULL, NULL);
	g_return_val_if_fail(self->priv->dispose_has_run != TRUE, NULL);

	/* Get the number of fields */
	if(fields != NULL){

		num_fields = g_list_length(fields);

		/* Initialize values based on number of columns */
		types = (GType*) g_new0( GType, num_fields);

		for(i=0;i<num_fields;i++){

			l = (IsiFields*)g_list_nth_data(fields,i);
			types[i] = l->type;

		}

		/* create the model store for data input */
		model =  (GtkTreeModel*) gtk_list_store_newv(num_fields,types);

		g_free(types);

		for(i=0;i<num_fields;i++){

			l = (IsiFields*)g_list_nth_data(fields,i);

			/* Setup sorting functions for the modle */
			switch(l->type){
				case G_TYPE_INT:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_int,(gpointer) l->pos, NULL);
					break;
				case G_TYPE_UINT:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_uint,(gpointer) l->pos, NULL);
					break;
				case G_TYPE_LONG:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_long,(gpointer) l->pos, NULL);
					break;
				case G_TYPE_ULONG:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_ulong,(gpointer) l->pos, NULL);
					break;
				case G_TYPE_DOUBLE:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_double,(gpointer) l->pos, NULL);
					break;
				case G_TYPE_STRING:
					l->sortable=TRUE;
					gtk_tree_sortable_set_sort_func(GTK_TREE_SORTABLE(model), l->pos, sort_by_string,(gpointer) l->pos, NULL);
					break;
			}
		}

	return model;
}

//Hope this helps,
//Shawn

认真的看了一下,发现原来和自己想的差不多,都是事先记录好MySQL数据表中每列的数据类型,往回写时做个判断进行转换..只不过我是想用类似于map的字符串哈希映射实现的,这里变成了switch case罢了..

PS: 本文在vim下通过vimpress插件编辑发布,不知道浏览器看起来怎样,呵呵..

GtkClist展示MySQL数据

What/Why

一个月前就应该交一个作业了,传说中的学生信息管理系统(笑~),一直没放在心上.可我软件工程已经有6个星期没去上过课了,作业也从来没交过,于是这两天还是交个作业上去给点老师面子..因为学着GTK+,所以尝试了下用GTK+来实现..

其实这个作业原理真的很简单,C语言下用mysql_query(&myconnection, sql)执行查询,然后用mysql_store_result(&myconnection)将查询得到的结果集保存下来,之后mysql_field_count(&myconnection)得到行数,然后逐行将行名打印出来,最后逐列将数据打印就搞定了.真正的难点在于如何将结果显示在窗口上,我拿着GtkClist和GtkTreeView左瞧右瞧,最终很悲情选定了GtkClist,仅仅是因为它的文档短了几十页罢了.(悲剧在后面.)

How

垒上代码如下,其中各主要widget从属关系为vbox -> scrolled_window -> clist, 其他需要注意的注释里写得很清楚了:

...

MYSQL myconnection;

MYSQL_RES *res_ptr;

MYSQL_ROW sqlrow;

MYSQL_FIELD *field;

int res;

...

//根据传入的conn(select * from *之类的语句执行),将得到的表内容显示在vbox上

static void make_clist(GtkWidget *vbox, MYSQL conn)

{

GtkWidget *scrolled_window; // 显示数据需要的滚动窗口scrolled_window和表单clist,

GtkWidget *clist, *entry;

int counter; // 迭代器,遍历结果集每一列

scrolled_window = gtk_scrolled_window_new (NULL, NULL); //创建scrolled_window,设置属性为自动

gtk_scrolled_window_set_policy (GTK_SCROLLED_WINDOW (scrolled_window), GTK_POLICY_AUTOMATIC, GTK_POLICY_ALWAYS);

gtk_widget_show(scrolled_window); //将scrolled_window显示出来

gtk_box_pack_start (GTK_BOX (vbox), scrolled_window, TRUE, TRUE, 0); //将scrolled_window添加道vbox中

int cols = mysql_field_count(&conn); // 得到数据表的列数

clist = gtk_clist_new_with_titles(cols, NULL); // 新建clist并显示,列数为cols

gtk_clist_column_titles_show (GTK_CLIST(clist)); //设置clist每列列名是否显示为真

res_ptr = mysql_store_result(&conn); //保存查询结果

for (counter = 0; counter < cols; counter++) //从第0列到最后1列

{

mysql_field_seek(res_ptr, counter); //跳到第counter列

field = mysql_fetch_field(res_ptr); //取出第counter列信息

entry = gtk_entry_new(); //新建文本框

gtk_entry_set_text(GTK_ENTRY(entry), field->name);//将文本框内容设置为数据表第counter列的名字

gtk_widget_show (entry);//显示文本框

gtk_clist_set_column_widget (GTK_CLIST (clist), counter, entry); //将文本框作为widget添加进clist中第counter列

gtk_clist_set_column_title(GTK_CLIST(clist), counter, field->name);//将数据表第counter列名作为clist中第counter列名

gtk_clist_set_column_width (GTK_CLIST (clist), counter, 85);//设置clist中每列长度

}

while((sqlrow = mysql_fetch_row(res_ptr)) != 0L)//取数据表中一整行的信息,遍历至表尾

gtk_clist_append(GTK_CLIST(clist), sqlrow);//将一整行信息压入clist

gtk_container_add (GTK_CONTAINER (scrolled_window), clist);//将clist添加scrolled_window中

gtk_widget_show(clist);//显示clist

}

...

Drawbacks

前面的其实都不是重点(哈哈~),因为有一些缺陷:

  • Clist中数据段不允许插入包括entry在内的所有widget,(尽管在其头文件enum中是有预留widget的),这也就意味着,假如想要可视化的修改数据表内容,那么只可能:
    1. 另外在程序主面板中再多添加行列按钮各一个,新内容输入框一个,最后还有确认按钮一个..虽然需要修改的数据所在的行列可以通过gtk_clist_get_text来得到,但这还是显得足够愚蠢和蹩脚..相信每个最终用户还是更希望可以体验到类似于在Excel中工作的快感的.
    2. 或者监听用户的鼠标事件,当鼠标在clist内点击时,弹出一个对话框,用户可以直接在上面敲入新的内容,回车后程序将自动连入mysql server更新内容.
  • Clist中数据类型全为gchar *,即便可以在读取数据表列名时预先判断其数据类型,然后用map<gchar *filename, gchar *datatype>做个映射,以后更新数据表时先得到数据所在列的列名,然后通过映射找到原来的数据类型,之后再update到数据表中,但这样还是太繁琐了.

前面列的一些不足虽然都有各自的解决方法,但都无法绕开下面这一点: 数据表的更新只能是每个单元每个单元进行的,无法实现逐行逐行的处理..设想一下,假如在Excel中,每修改/添加/删除一个单元后,都需要ctrl+s一下,这是多么的悲剧..

那么,要怎么解决这个问题呢..在mailing list里提问之后,我才发现,或许GtkTreeView天生就适合做展示..我的想法是这样的:先用time()得到当前时间做为一个新的数据表名称table_new,然后执行create table table_new select * from table_old将旧数据表复制到新数据表中,这样子各列的数据类型就得到了..然后delete from table_new将新数据表清空,然后设计一个函数func()将gtktreeview中数据逐行写入table_new,最后删掉table_old,将table_new重命名为table_old就可以了..至于func()怎么实现呢,明天再搞它.

PS

  1. 测试版源码在这http://godorz.info/wp-content/uploads/2010/04/main.test_.zip,编译命令为: gcc -o main.test main.test.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient `pkg-config –cflags –libs gtk+-2.0`,记得把源码里面的用户名,密码和数据库改了…galde文件其实是个xml,里面放的是各控件的属性(名字,位置,大小等),可以华丽地无视之..
  2. 原来博客真的是拿来记思路的..