Database

[johnno1956]

Quick question.

Does anyone have instructions on creating a simple database application using Naalaa? As I have to record my blood sugars on a daily basis, I figured that rather than using N7 for just games, why not try to create a practical application?

LibreOffice has a builtin DB application... but where is the fun in that? lol

Any suggestions or advice would be appreciated.

Thank you.

J

[Marcus]

It's funny, but I thought about this, writing a database in n7, the other week, when I wrote something about "weekly challenges".

I don't think it should be too difficult. I'll see if I can make an example this weekend.

[Marcus]

This program will probably be very confusing. My woman and daughter went to violin class, so I put this together very quickly while waiting for them to return (when they do we'll buy pizza  ).

I'm not sure if you'll be able to figure out how it works. But if you think it might be what you're looking for, I can add more stuff and hopefully explain better how to use it. Right now you can create a database (that can be saved or loaded in json format). You can create tables in this database and add entries to the tables. A table has a primary key (column) that can be of auto-increment type or not. You can use a Find-function to find an entry by its primary key or a ... REALLY weird Query function to query a list of entries based on your own condition/conditions. The Query-thing probably needs lots of more explanation.

I imagine it could be fun to use the undocumented ngui library (used by ned and the editors) to create a gui for a thing like this ...

Code:

' A database test
' ---------------

' Use json as fileformat.
include "json.n7"
' Might want to sort things.
include "qsort.n7"

' DateString
' ----------
' A helper function when printing dates from a time value.
function DateString(t)
    dt = datetime(t)
    ' Create a date string in the format yyyy-mm-dd (that's the Swedish way ...).
    return dt.year + "-" + str(dt.month, 2) + "-" + str(dt.day, 2)
endfunc

' Create a database object.
myDatabase = Database()


' Create a table named Weight. Set 'id' to be the primary key and let it be auto-incremented.
myDatabase.CreateTable("Weight", ["name", "date", "weight", "id"], "id", true)

' Add an entry to the Weight table. No need to set 'id', the database takes care of it since it's
' auto-incremented.
entry = [name: "Marcus",
        date: time(2022, 10, 03),
        weight: 86]       
myDatabase.Insert("Weight", entry)

' Add another entry for Marcus.
entry = [name: "Marcus",
        date: time(2022, 11, 18),
        weight: 92]
myDatabase.Insert("Weight", entry)
' Add a couple of more entries for Marcus in a faster way.
myDatabase.Insert("Weight", [name: "Marcus", date: time(2021, 05, 13), weight: 73])
myDatabase.Insert("Weight", [name: "Marcus", date: time(2021, 09, 24), weight: 77])
myDatabase.Insert("Weight", [name: "Marcus", date: time(2021, 12, 9), weight: 79])
' Add some entries for Benny.
myDatabase.Insert("Weight", [name: "Benny", date: time(2022, 5, 30), weight: 69])
myDatabase.Insert("Weight", [name: "Benny", date: time(2022, 11, 17), weight: 73])
myDatabase.Insert("Weight", [name: "Benny", date: time(2022, 9, 4), weight: 70])


' Create another table with a non-auto-incremented primary key, 'socialSecurityNo'.
myDatabase.CreateTable("People", ["firstName", "lastName", "socialSecurityNo"], "socialSecurityNo", false)
' Add some people to the table.
myDatabase.Insert("People", [firstName: "Marcus", lastName: "Johansson", socialSecurityNo: 19790519])
myDatabase.Insert("People", [firstName: "Frank", lastName: "Tank", socialSecurityNo: 19640316])
myDatabase.Insert("People", [firstName: "Oliver", lastName: "Smith", socialSecurityNo: 19921004])
' This line would cause an assertion, since the primary key 19790519 already exists.
'myDatabase.Insert("People", [firstName: "Elsa", lastName: "Frozen", socialSecurityNo: 19790519])


' Save the database to db.json.
myDatabase.Save("db.json")

' Load the data back from file, just to show that it works ...
myDatabase.Load("db.json")

' Use the Find function to find an entry with a specific primary key.
entry = myDatabase.Find("People", 19640316)
if entry
    pln "Found a person with the social security number 19640316: " +
        entry.firstName + " " + entry.lastName
    pln
endif


' Use the Query function to find all weight entries for Marcus. This may look extremely weird.
' The first argument to the Query function is the table name. The second argument is a function
' that Query will call once for each entry in the Weight-table. The first argument to that function
' is an entry and the second argument is ... whatever we want it to be. In this case it's just a
' string. We add this string as the third argument to Query.
entries = myDatabase.Query(
        "Weight",
        function(e, params)
            return e.name = params
        endfunc,
        "Marcus")
' List the entries returned by Query in a couple of different orders.
if sizeof(entries) > 0
    ' Print them in the order we got them.
    pln "Marcus unordered weight: "
    for i = 0 to sizeof(entries) - 1
        pln "  " + DateString(entries[i].date) + ": " + entries[i].weight
    next
   
    ' Print them ordered by date.
    '  Now, ugh, qsort works a bit like the Query function. We can use it to sort an array using
    ' our own compare-function. The function should compare a with b and return a negative number if
    ' a should come before b, a positive number if b should come before a or 0 if they're equal.
    qsort(entries,
            function(a, b)
                ' Yes, instead of this, we could just write "return a.date - b.date". I just want to
                ' make it clear how it works.
                if a.date < b.date  return -1
                elseif a.date > b.date  return 1
                else  return 0
            endfunc)
    pln "Marcus weight ordered by date:"
    for i = 0 to sizeof(entries) - 1
        pln "  " + DateString(entries[i].date) + ": " + entries[i].weight
    next
   
    ' Print them ordered by weight.
    qsort(entries, function(a, b); return a.weight - b.weight; endfunc)   
    pln "Marcus weight ordered by weight:"
    for i = 0 to sizeof(entries) - 1
        pln "  " + DateString(entries[i].date) + ": " + entries[i].weight
    next   
endif

system "pause"

' Database
' --------
' Return a new database.
function Database()
    db = []
   
    ' 'tables' contains all tables.
    db.tables = []
   
    ' Load
    ' ----
    ' Load database from json file.
    db.Load = function(filename)
        result = JSON_FromFile(filename)
        if result
            this.tables = result
            return true
        else
            return false
        endif
    endfunc
   
    ' Save
    ' ----
    ' Save database to json file.
    db.Save = function(filename)
        json = JSON_ToString(this.tables)
        f = createfile(filename)
        write file f, json
        free file f
    endfunc

    ' CreateTable
    ' -----------
    db.CreateTable = function(name, columns, primaryKey, autoIncrement)
        this.tables[name] = [
                columns: columns,
                primaryKey: primaryKey,
                autoIncrement: autoIncrement,
                data: []]
    endfunc
   
    ' Insert
    ' ------
    ' Insert an object into table.
    db.Insert = function(tableName, obj)
        ' Terminate if database doesn't contain table.
        assert key(this.tables, tableName), "Database has not table named " + tableName
        t = this.tables[tableName]
        ' Make a copy of obj.       
        newObj = []
        ' Add all columns from obj.
        foreach c in t.columns
            ' Don't copy primary key if using autoIncrement.
            if not (t.autoIncrement and c = t.primaryKey)
                assert key(obj, c), "Object has no key named " + c
                newObj[c] = obj[c]
            endif
        next
        ' Generate primary key if autoIncrement.
        if t.autoIncrement
            maxValue = 0
            foreach entry in t.data  maxValue = max(maxValue, entry[t.primaryKey])
            newObj[t.primaryKey] = maxValue + 1
        endif
        ' Make sure an entry with the primary key doesn't already exist.
        foreach entry in t.data
            assert not entry[t.primaryKey] = newObj[t.primaryKey], "Primary key " +
                    newObj[t.primaryKey] + " already exists in table " + tableName
        next
        t.data[sizeof(t.data)] = newObj   
    endfunc
   
    ' Find
    ' ----
    ' Find an entry in table with the given primary key, return an unset variable if not found.
    db.Find = function(tableName, primaryKey)
        if key(this.tables, tableName)
            t = this.tables[tableName]
            ' Return a COPY of the entry. We can't let the outside program mess with the actual entries.
            foreach entry in t.data  if entry[t.primaryKey] = primaryKey  return copy(entry)
        endif
        return unset
    endfunc
   
    ' Query
    ' -----
    ' Return a list with copies of all objects that are accepted by the function f.
    db.Query = function(tableName, f, params)
        result = []
        if key(this.tables, tableName)
            foreach entry in this.tables[tableName].data
                if f(entry, params)  result[sizeof(result)] = copy(entry)
            next
        endif
        return result
    endfunc
 
    return db
endfunc

Edit ... Now I feel kind of stupid, maybe you just wanted some hints on ways to write a database in naalaa yourself. Sorry, I got carried away I'll see if I can explain better what exactly I'm doing - how the database actually works. But now it's pizza time

[johnno1956]

Thank you. I appreciate the example but the DB that I would need will only have about 3 or 4 fields. Primarily I would like to replace my Blood Sugar Log Book with something that does not have to be replaced after each book is filled.

The only things I record are: The day's date (at the moment it is just like DD MMM); The blood sugar level (##.#); A comment if the levels are a bit high or a bit low (what food effected the level change) - less than 50 characters; May once a month I will record my weight (###.# kg)

I was planning on using a simple menu to maybe Add an entry; Delete an Entry; Edit an entry; Load; Save and Exit. I have no idea as to how to use the ngui lib, so I am kind of leaning towards, the old DOS styled menu... If by some strange twist of fate that, maybe a ngui tutorial should happen to materialise out of thin air, I could be convinced to have the application 'look' more 'native'....

I will work my way through your provided example to try and see 'what makes it tick'... But you were correct an assuming that I would like "hints" of how to code it... I would disagree with the 'stupid' part... lol

I like the old saying, "You can give a man a fish and you will feed him for a day or you can teach a man to fish and you will feed him for a lifetime." It is a good thing that I like fish... lol

Thank you for your efforts so far. Much appreciated. Time to experiment...

Pizzaa? Not fair... *sigh* It has been a long time since I have had Pizza... *sigh*

[Marcus]

Alright, I'll start writing some ngui examples and post them in another thread!