Database

[kwibus]

Hello,

I am learning to write AHK scripts; I have to say, it is an awesome language!
My challenge is: a "database" program: creating, deleting, displaying (in a GUI / grid / Listview), editing, reading, writing, all of it.
I found some examples, in the docs, forums, how to read a csv / text file. (see code below)
Now for the rest of it - how to get the contents in an internal Array / Object / Structure? How to display in a 2-dimensional grid, what to do to add / delete / edit records, fields?
How to write the content back to disk, to retrieve?
What is the best / easiest method? In csv / text format, or binary file, or use SQL database-like methods?

Any tip is welcome!

Thank you,

Ad

Code: Select all

; Parse a comma separated (CSV) file: (nested Loops)
Loop, Read, email.csv	; InputFile should not be in quotes
{
	LineNumber = %A_Index%
	Loop, Parse, A_LoopReadLine, CSV, %A_Space%
	{
		MsgBox, 4,, Field %LineNumber%-%A_Index% is:`n%A_LoopField%`n`nContinue?
		IfMsgBox, No
			Break
	}
}

; Create an array, initially empty:
Array := Object()

; Write to the array:
Loop, Read, email.csv	; This loop retrieves each line from the file, one at a time.
{
	Array.Insert(A_LoopReadLine)	; Append this line to the array.
}

; Read from the array:
for index, element in Array	; Recommended approach in most cases.
{
	MsgBox % "Element number " index " is " element
}

[Exaskryz]

Whichever way you feel most comfortable. The closest database program I've done is actually something that edits a webpage, so I used Loop, Read to parse through an .html file (in hindsight FileRead may have been a better option). Depending on the complexity, you might opt for an ini file (IniWrite/IniRead).

There may well be a better option though, or maybe a more industry standard.

[kwibus]

Hello,

Me again. Using AHK, it is easy to Read in a file, like a CSV / text with x lines into a variable. The second step is also not too difficult, to Loop, Parse and get each line separately in an Array or Object.
But, I am struggeling with the third step: to get the y individual fields from a "record" into a 2-dimensional "table". I do not see a way to represent an Array with 2 or more dimensions; like Array[x][y], how that is done in other languages.
Are there examples, how this can be done in AHK?

Thank you, Ad

[FanaticGuru]

I do not see a way to represent an Array with 2 or more dimensions; like Array[x][y], how that is done in other languages.

I believe Array[x][y] is supported but I always do Array[x,y] or Array [x,y,z] etc. As many dimensions as you like.

Code: Select all

Food := {}
Food["Fruit","Red"] := "Apple"
MsgBox % Food["Fruit","Red"]

FG

[SifJar]

https://autohotkey.com/docs/Objects.htm ... _of_Arrays

Basically, array[x][y] := "example" will work if an object called "array" already exists, array[x,y] := "example" will work regardless, making the object if necessary.

[FanaticGuru]

Below is an example of some techniques for working with array/objects.

Code: Select all

Food := {}
ListofStuff := {}

ListofStuff.Insert("Apple")
ListofStuff.Insert("Watermelon")
Food["Fruit","Red"] := ListofStuff

for key, val in Food.Fruit.Red
	MsgBox % key "`t" val

Getting a good grasp on arrays can really help your coding efficiency. The idea of combining small objects to create larger objects can be very useful in databases.

FG

[lexikos]

The thing to remember with "multi-dimensional arrays" like Food["Fruit","Red"] is that they are really arrays of arrays. Food.Fruit or Food["Fruit"] will return an array equivalent to {"Red": ListofStuff}, and Food.Delete("Fruit") will effectively remove all Food["Fruit", xxx] items.

@FanaticGuru:

Deprecated: Insert is not recommended for use in new scripts. Use InsertAt, Push, ObjRawSet or a simple assignment instead.
Source: Object

In this case, Push.

@SifJar:
Incorrect. array[x][y] := "example" will only work if both array and array[x] contain objects. array[x,y] := "example" requires array to contain an object, but will initialize array[x] automatically if needed.

[kwibus]

As you will understand by now, if you did not guess already, I am a total newby to AHK. Not so much to programming in general.
Anyway, the next small step makes me very happy; incredible, what AHK can do in only a few lines of code:

Read in a (small) .csv file, gotten from a spreadsheet.

Code: Select all

Table := {}
Loop, Read, email.csv
{
	x = %A_Index%
	Loop, Parse, A_LoopReadLine, CSV, %A_Space%
		Table[x, A_Index] := A_LoopField
}

Gui, Add, ListView, vMyListView Grid, Achternaam|Voornaam|e-mail
for x in Table
	if x > 1	; skip the first line, that contains the headings.
		LV_Add(, Table[x, 1], Table[x, 2], Table[x, 3])
Gui, Show
return

GuiClose:
ExitApp

My next question for help is:
Of course, the approach above is not general at all.
If a table contains a lot of columns, this approach is very lame.
What I need is an example, or tip, how to get a bigger table into the ListView.

And then, after that is accomplished, how to do adding / deleting / editing etc. with thw Table data?

Thank you, Ad

[kwibus]

Please, help! I have been reading lots of documentation, and I found a library ObjCSV.ahk, but that one is too complicated for me. Can someone give me somewhat simpler examples.
What I need now, are examples / tips, how to convert CSV to a "Collection", an "object of objects" or "array of arrays", if I understand it well.

Thank you, Ad

[Coco]

Here's the GitHub repo for ObjCSV.ahk. The repo contains multiple demo scripts that should help you get started with its usage. Here are the author's posts regarding ObjCSV:

• ObjCSV Library v0.1 - Library to load and save CSV files to/from objects and ListView
• ObjCSV Library Tutorial - Basic

[kwibus]

Thank @Coco:
I'll read the documentation and work through the demo's.
But I keep asking myself: is such a vast library really necessary for my task? It is difficult to understand.
I think there are a lot of functions in it that I never will use.

Ad

[kwibus]

Code: Select all

strFilePath := "email.csv"	; gebruik := ! anders gaat het fout! HEEL BELANGRIJK !
fn_1(strFilePath)

fn_1(strFile)
{	; function to read in the first line of a CSV, the "header fields".
	; all field names should go into the object "Keys".
	; I am so puzzled, why doesn't this work?
	Keys := {}
	FileRead, strData, %strFile%
	strData := Trim(strData, "`r`n")	; remove empty line (record) at begin or end of string
	;MsgBox % strData	; strData is a string, not an Array, nor Object
	Loop, Parse, strData, `n, `r	; chop string into substrings
		if A_Index = 1
			Loop, Parse, A_LoopField, CSV
				Keys.Push(%A_LoopField%)
	for x in Keys
		MsgBox % Keys[A_Index]
}

This does not work; what am I doing wrong?

Thanks, Ad

[kwibus]

Hello, I found it; this small "challenge" is answered!

Code: Select all

strFilePath := "email.csv"	; gebruik := ! anders gaat het fout! HEEL BELANGRIJK !
col_Obj := fn_ReadCols(strFilePath)
for key, val in col_Obj.Cols
	MsgBox % "column " key " is " val

fn_ReadCols(strFile)
{	; function to read in the first line of a CSV, the "header fields".
	; all field names should go into the object "Keys".
	obj := []
	Keys := []
	FileRead, strData, %strFile%
	strData := Trim(strData, "`r`n")	; remove empty line (record) at begin or end of string
	Loop, Parse, strData, `n, `r	; chop string into substrings
		if A_Index = 1
			Loop, Parse, A_LoopField, CSV
				Keys.Push(A_LoopField)
	obj["Cols"] := Keys
	return obj
}

Ad