gtk database string grid

BlitzMax Forums/MaxGUI Module/gtk database string grid

Chris C

(Posted 2007) [#1]

This is still very much an ugly hack but it does work within limits!

I'm posting it here in the naive hope that all the talent that the 250 byte challenged showed off can be turned to somthing useful

I don't have vast amounts of time but with help we could have a cross platform database bound set of components

By preference I'd prefer to drop the procedural type api of maxgui and adopt a cleaner OO gui api for max

@brucey although rows_affected can be used to tell how many rows are in a query (according to the mysql manual) this is not likely to be the case with other engines as you know, it would be handy to have a "proper" query.numrows() method

superstrict 
framework bah.gtkmaxgui
import brl.eventQueue
import BaH.DBMySQL
import brl.random

extern
'GtkWidget* gtk_table_new (gint rows, gint columns, gint homogeneous)
	function gtk_table_new:byte ptr(rows:int,columns:int,homo:int)
'void gtk_table_attach (GtkTable *table, GtkWidget *child, gint left_attach, gint right_attach, gint top_attach, gint bottom_attach, gint xoptions, gint yoptions, gint xpadding, gint ypadding)
	function gtk_table_attach(table:byte ptr,child:byte ptr,la:int,ra:int,ta:int,ba:int,xo:int,yo:int,xpad:int,ypad:int)
	function gtk_scrolled_window_add_with_viewport(c:byte ptr,p:byte ptr)
end extern

type gtkTable extends TgtkGadget
	Field scrollWindow:Byte Ptr
	Function createTable:gtkTable(x:Int, y:Int, w:Int, h:Int,r:int,c:int, group:TGadget)

		Local gadget:gtkTable=new gtkTable
		gadget.handle=gtk_table_new(r,c,false)

		gadget.scrollWindow = gtk_scrolled_window_new(null,null)

'		gtk_container_set_resize_mode(gadget.scrollWindow, GTK_RESIZE_QUEUE)
'		gtk_scrolled_window_set_policy(gadget.scrollWindow, GTK_POLICY_AUTOMATIC, GTK_POLICY_AUTOMATIC)

		gtk_scrolled_window_add_with_viewport(	gadget.scrollWindow, gadget.handle)

		gtk_layout_put(TGTKContainer(group).container, gadget.scrollwindow, x, y)
		gtk_widget_set_size_request(gadget.scrollwindow, w, Max(h,0))

		gtk_widget_show(gadget.scrollWindow)

		gtk_widget_show(gadget.handle)
		' map the new gadget - so we can find it later if required
		If gadget Then
			GadgetMap.Insert(TGTKInteger.Set(Int Ptr(gadget.handle)[0]),gadget)
		End If
		
		If group Then
			gadget._SetParent group
		End If
		gadget.SetShape x,y,100*10,24*10

		Return gadget
	End Function
endtype

' have to nick this code cause i need a parentless gadget
function initTextField(g:TGTKTextField,x:Int, y:Int, w:Int, h:Int, label:String, _style:Int)

		g.handle = gtk_entry_new()
'		init(GTK_TEXTFIELD, x, y, w, h, style)
		g.SetRect(x,y,w,h)
		g.class = GTK_TEXTFIELD
		g.kids = New TList
		g.style = _style

		If g.style Then
			g.isPassword = True
			gtk_entry_set_visibility(g.handle, False)
		End If

		' causes the default gadget to be activated when Enter is pressed inside this Text Field.
		g_object_set_int(g.handle, "activates-default", True)
		g.setShow(True)

'		gtk_layout_put(TGTKContainer(group).container, g.handle, x, y)
		gtk_widget_set_size_request(g.handle, w, Max(h,0))

		' add callbacks
		g_signal_cb2(g.handle, "changed", g.OnTextChanged, g, g.Destroy, 0)
		g_signal_cb3(g.handle, "key-press-event", g.OnKeyDown, g, g.Destroy, 0)
		g_signal_cb3(g.handle, "focus-out-event", g.OnFocusLost, g, g.Destroy, 0)
		' catch right-mouse buttons
		g_signal_cb3(g.handle, "button-press-event", g.OnMouseDown, g, g.Destroy, 0)

Endfunction

	

local win:TGadget=createwindow("table test",64,64,464,264,null,WINDOW_TITLEBAR|WINDOW_STATUS|WINDOW_CLIENTCOORDS)
local tab:gtkTable=gtkTable.createTable(32,32,400,200,20,20,win)

const GTK_EXPAND:int=1
const GTK_FILL:int=4



global db:TDBConnection = LoadDatabase("MYSQL", "maxtest", "localhost", 0, "root", "root123")

If Not db Then
	DebugLog("Didn't work...")
	End
End If

If db.hasError() Then
	errorAndClose(db)
End If

global cols:int
global rows:int
global txt:TGTKTextField[]
If db.isOpen() Then

	populate()

	Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
	If db.hasError() Then
		errorAndClose(db)
	End If

	query.nextRow()
	Local record:TQueryRecord = query.rowRecord()
	
	'For SELECT statements, mysql_stmt_affected_rows() works like mysql_num_rows().
	' [[from mysql manual probably wont work for other db's]]
	cols=record.count()
	rows=query.rowsAffected()+1
	tab.SetShape 32,32,150*cols,24*rows
	txt=txt[..cols*(rows+1)]
	for local n:int=1 until record.count()+1
			local f:TQueryField=record.getField(n-1)
			local s:string
			select f.fType
				case DBTYPE_STRING
					s="string"
				case DBTYPE_INT
					s="integer"
				case DBTYPE_DOUBLE
					s="double"
				case DBTYPE_FLOAT
					s="float"
				case DBTYPE_LONG
					s="long"
				case DBTYPE_DATE
					s="date"
				case DBTYPE_BLOB
					s="blob"
				case DBTYPE_DATETIME
					s="dtae/time"
				case DBTYPE_TIME
					s="time"
			end select
			'print "field "+n+" "+f.name+" is a "+s
			
			txt[n]=new TGTKTextField
			initTextField(txt[n],0,0,150,24,"",0)
			txt[n].settext(f.name)
			gtk_table_attach(tab.handle,TgtkGadget(txt[n]).handle,n,n+1,0,1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)

	next


	query = db.executeQuery("SELECT * from person")
	If db.hasError() Then
		errorAndClose(db)
	End If

	local i:int=0
	While query.nextRow()
		Local record:TQueryRecord = query.rowRecord()
		i:+1
		txt[1+i*cols]=new TGTKTextField
		txt[2+i*cols]=new TGTKTextField
		txt[3+i*cols]=new TGTKTextField
		txt[4+i*cols]=new TGTKTextField
		txt[5+i*cols]=new TGTKTextField
		txt[6+i*cols]=new TGTKTextField
		txt[7+i*cols]=new TGTKTextField
		initTextField(txt[1+i*cols],0,0,150,24,"",0)
		initTextField(txt[2+i*cols],0,0,150,24,"",0)
		initTextField(txt[3+i*cols],0,0,150,24,"",0)
		initTextField(txt[4+i*cols],0,0,150,24,"",0)
		initTextField(txt[5+i*cols],0,0,150,24,"",0)
		initTextField(txt[6+i*cols],0,0,150,24,"",0)
		initTextField(txt[7+i*cols],0,0,150,24,"",0)
		txt[1+i*cols].settext(TDBInt(record.value(0)).value)
		txt[2+i*cols].settext(TDBString(record.value(1)).value)
		txt[3+i*cols].settext(TDBString(record.value(2)).value)
		txt[4+i*cols].settext(TDBint(record.value(3)).value)
		txt[5+i*cols].settext(TDBFloat(record.value(4)).value)
		txt[6+i*cols].settext(TDBDouble(record.value(5)).value)
		txt[7+i*cols].settext(TDBLong(record.value(6)).value)
		
		'print DBInt(record.value(0)).value + ". Name = " + TDBString(record.value(1)).value + " " + TDBString(record.value(2)).value
		gtk_table_attach(tab.handle,TgtkGadget(txt[1+i*cols]).handle,1,2,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[2+i*cols]).handle,2,3,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[3+i*cols]).handle,3,4,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[4+i*cols]).handle,4,5,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[5+i*cols]).handle,5,6,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[6+i*cols]).handle,6,7,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)
		gtk_table_attach(tab.handle,TgtkGadget(txt[7+i*cols]).handle,7,8,i,i+1,GTK_EXPAND | GTK_FILL,GTK_EXPAND | GTK_FILL,0,0)

	Wend
	
			
	db.close()
	
End If


While True
	WaitEvent() 
'	Print CurrentEvent.ToString()
	Select EventID()
		Case EVENT_WINDOWCLOSE
			print "window closed"
			End
	End Select
Wend


Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	End
End Function


function populate()


Type TPersonStuff
	Field forename:String
	Field surname:String
	Field dataInt:Int
	Field dataFloat:Float
	Field dataDouble:Double
	Field dataLong:Long
End Type

	Local names:String[][] = [ ..
		[ "Alfred", "Aho" ],   ..
		[ "Brian", "Kernighan" ], ..
		[ "Peter", "Weinberger" ], ..
		[ "Fred", "Basset" ], ..
		[ "Peter", "Parker" ], ..
		[ "George", "Hemingway" ], ..
		[ "Sally", "Streets" ], ..
		[ "Dr","Who"], ..
		[ "Dr","No"]..
		]
		
	Local pstuff:TPersonStuff[] = New TPersonStuff[names.length]
	For Local i:Int = 0 Until names.length
		pstuff[i] = New TPersonStuff
		pstuff[i].forename = names[i][0]
		pstuff[i].surname = names[i][1]
		pstuff[i].dataInt = Rnd(1, 10)
		pstuff[i].dataFloat = Rnd(1, 10)
		pstuff[i].dataDouble = Rnd(1, 10)
		pstuff[i].dataLong = Rnd(1, 100000000000:Long)
	Next


	db.executeQuery("DROP TABLE if exists person")
'DebugStop
	' Create a new table
	Local s:String = "CREATE TABLE if not exists person (id integer primary key AUTO_INCREMENT, " + ..
	  " forename varchar(30), surname varchar(30), dataint integer, datafloat float, datadouble double, datalong bigint )"

	db.executeQuery(s)

	If db.hasError() Then
		errorAndClose(db)
	End If

	' get a new query object 
	Local query:TDatabaseQuery = TDatabaseQuery.Create(db)

	' prepare the insert statement
	' by preparing it once, the database can reuse it on succesive inserts which is more efficient.
	query.prepare("INSERT INTO person values (NULL, ?, ?, ?, ?, ?, ?)")
	
	If db.hasError() Then
		errorAndClose(db)
	End If

	' iterate round the array inserting new entries
	For Local i:Int = 0 Until names.length
		query.bindValue(0, TDBString.Set(pstuff[i].forename))
		query.bindValue(1, TDBString.Set(pstuff[i].surname))
		query.bindValue(2, TDBInt.Set(pstuff[i].dataInt))
		query.bindValue(3, TDBFloat.Set(pstuff[i].dataFloat))
		query.bindValue(4, TDBDouble.Set(pstuff[i].datadouble))
		query.bindValue(5, TDBLong.Set(pstuff[i].dataLong))
'DebugStop
		query.execute()
		
		If db.hasError() Then
			errorAndClose(db)
		End If
	Next
endfunction

Brucey

(Posted 2007) [#2]

it would be handy to have a "proper" query.numrows() method

Can't be done in a cross-database way, since most databases don't know how many rows are returned until you fetch them. And it would be a bit silly for me to be pre-fetching 3 million rows just to get a row count :-)