TableView add data from Database

[mbolder]

Hello Vladimir,

i am now implementing a TableView and wish to show the data from the result of an sql query. How do i access each field in the table and update it in case of a new query ?

I looked at the example, but every time i try and set a field value in the OnDrawCell event, it gets displayed all over the table, not just the one i had set with .Row and .Col.

[VladimirMarkelov]

Hello!

I am not sure what is your code look like. I'll explain how TableView should work.

TableView does not have internal storage - it is pure virtual (like Windows ListView in virtual mode). So, you cannot access a field in its table directly. Before drawing any visible cell a TableView calls a callback(if it is defined) OnDrawCell with argument that contains two fields: Row and Col. Based on these values you should "calculate"(or fetch from your stotage/cache/DB) data and assign to the argument's field Text.

[mbolder]

Thats what I thought, but where am I going wrong here:

var i int
table.OnDrawCell(func(info *ui.ColumnDrawInfo) {
        for result.Next() {
            err := result.Scan(&RE.Kundenname, &RE.Rechnungsnummer, &RE.Belegtext, &RE.Soll, &RE.Haben, &RE.Rechnungsbetrag, &RE.Rechnungsdatum)
            if err != nil {
                panic(err)
            }
            if info.Row == i {
                if info.Col == 0 {
                    info.Text = RE.Kundenname
                }
                if info.Col == 1 {
                    info.Text = RE.Rechnungsnummer
                }
                if info.Col == 2 {
                    info.Text = RE.Soll
                }
                if info.Col == 3 {
                    info.Text = RE.Haben
                }
                if info.Col == 4 {
                    info.Text = RE.Rechnungsbetrag
                }
                if info.Col == 5 {
                    info.Text = RE.Rechnungsdatum
                }
                if info.Col == 6 {
                    info.Text = RE.Belegtext
                }
            }
            i++
        }
    })
``

[VladimirMarkelov]

I see. You try to fill entire column in one call. But OnDrawCell is called for every cell. So, it results in that you run through the data until the end and put the last value into every cell. So the final table has columns filled with the same text.

Ideally your OnDrawCell should look this way (in pseudo-code):

table.OnDrawCell(func(info *ui.ColumnDrawInfo) {
  RE := result.ReadRowByNumber(info.Row)
  switch info.Col {
  case 0:
     info.Text = RE.Kundenname
  case 1:
     info.Text = RE.Rechnungsnummer
  // other columns following...
  }
})

But there is a concern that it may hit performance. I have an idea how to make it a bit better. See #113 - I'll add a few things to make life easier and add a new demo to explain how to use new feature. I'll try to implement it today/tomorrow.

[mbolder]

That´s a brilliant addition, which I implemented immediately. One question in order to make the tui more responsive, I would like e.g every 1000 row, to load the next 1000 datasets from the db. How would you implement that? So far I am pulling as soon as the first row check is finished in the func (d *dbCache) preload(firstRow, rowCount int) with a func getData() the struct containing the slices in order to fill d.data. I tried to limit the data to a 1000 rows by adding LIMIT 1000 to my query, but ones I go beyond that I thought of using OFFSET 1000 LIMIT 1000 in order to get the next 1000 rows. So which variable contains the actual row counter in order to set the offset, load the next dataset and update the cache?

 func (d *dbCache) preload(firstRow, rowCount int) {

    if firstRow == d.firstRow && rowCount == d.rowCount {
        // fast path: view area is the same, return immediately
        return
    }
    RE := getReData(rows??)
        .  // adding the slices to data
        .
}

func getReData(rows int) RE {
       var rc int
       if (rows % 1000 == 0{
          rc=rows
       }else{
         rc=0
       }
      result, err := db.Query(`Select "Number","Client", "Sale", "Amount" from sales ORDER BY "Rechnungsnummer" DESC OFFSET $1 LIMIT 1000`, rc)
    if err != nil {
        panic(err)
    }
    defer result.Close()
       . // scan the query result and creating the slices 
       . 
       return RE // returning the struct containing the slices for d.data
}

[VladimirMarkelov]

As far as I understand, you want to read more rows than TableView contains to speedup the app: by read data from by 1000 records rarely.

If you want to keep only current 1000 rows at a time, you have to deal with the trouble: what to do if TableView top row is 990 and its height is 15. So, it must show rows from 990 to 1014 - they are in different bunches. It is solvable but needs close attention.

If you want just be notified that you need to read 1000 more next row and keep all rows you read before, the easiest way, I think, instead of fields firstRow and rowCount dbCache needs only rowCount. dbCache preload looks this:

 func (d *dbCache) preload(firstRow, rowCount int) {
    if firstRow + rowCount < d.rowCount {
        // all the data already in the cache
        return
    }
    RE := d.readNext1000Rows(d.rowCount) // RE contains the next 1000 rows
        // really it can be fewer than 1000 if table is short, for simplicity I used constant 1000
        d.rowCount += 1000 
       // adding the slices to data
       d.AddRowsToCache(RE)  // just adds new rows to the end of old cached data
       // now cache contains 1000 more lines. It can be memory-heavy if your table is huge
}

func readNext1000Rows(startRow int) RE {
    result, err := db.Query(`Select "Number","Client", "Sale", "Amount" from sales ORDER BY "Rechnungsnummer" DESC OFFSET $1 LIMIT 1000`, startRow)
    if err != nil {
        panic(err)
    }
    defer result.Close()
     ....
      return RE
}