Paging with TkTreeCtrl + SQlite

TkTreeCtrl is a powerfull and very fast widget, well suited to render data stored in a SQLite database.

Below is a quick and dirty example for rendering SQLite data in TkTreeCtrl + paging the recordset in a very basic way.

WARNING : the following script will create a SQLite db file "Presidents" (12 Ko) in the current working directory.

package require Tk
package require sqlite3
package require treectrl

wm withdraw .


# PART ONE : data storage

# Open connection to Presidents db file + create this file if non existent

sqlite3 db1 Presidents

# Create "Presidents" table and populate it
# (middle names/initials omited)

    db1 eval {CREATE TABLE IF NOT EXISTS Presidents(\
                        Country VARCHAR(2) DEFAULT 'US' CHECK(Country <> ''), 
                        FirstName TEXT NOT NULL CONSTRAINT Check_FirstName CHECK(FirstName <> ''),
                        LastName TEXT NOT NULL CONSTRAINT Check_FirstName CHECK(FirstName <> ''),
                        BirthDate VARCHAR(10) NOT NULL CONSTRAINT Check_BirthDate CHECK(length(BirthDate) = 10), /* ISO8601 format */
                        PRIMARY KEY (FirstName, LastName, BirthDate)\
                        )}
 
    db1 eval {INSERT OR IGNORE INTO Presidents ( FirstName, LastName, BirthDate )
              VALUES ( 'George'   , 'Washington', '1732-02-22' ),
                     ( 'John'     , 'Adams'     , '1735-10-30' ),
                     ( 'Thomas'   , 'Jefferson' , '1743-04-13' ),
                     ( 'James'    , 'Madison'   , '1751-03-16' ),
                     ( 'James'    , 'Monroe'    , '1758-04-28' ),
                     ( 'Andrew'   , 'Jackson'   , '1767-03-15' ),
                     ( 'John'     , 'Adams'     , '1767-07-11' ),
                     ( 'William'  , 'Harrison'  , '1773-02-09' ),
                     ( 'Martin'   , 'Van Buren' , '1782-12-05' ),
                     ( 'John'     , 'Tyler'     , '1790-03-29' ),
                     ( 'James'    , 'Polk'      , '1795-11-02' ),
                     ( 'Zachary'  , 'Taylor'    , '1784-11-24' ),
                     ( 'Millard'  , 'Fillmore'  , '1800-01-07' ),
                     ( 'Franklin' , 'Pierce'    , '1804-11-23' ),
                     ( 'James'    , 'Buchanan'  , '1791-04-23' ),
                     ( 'Abraham'  , 'Lincoln'   , '1809-02-12' ),
                     ( 'Andrew'   , 'Johnson'   , '1808-12-29' ),
                     ( 'Ulysses'  , 'Grant'     , '1822-04-27' ),
                     ( 'Rutherford', 'Hayes'    , '1822-10-04' ),
                     ( 'James'    , 'Garfield'  , '1831-11-19' ),
                     ( 'Chester'  , 'Arthur'    , '1829-10-05' ),
                     ( 'Grover'   , 'Cleveland' , '1837-03-18' ),
                     ( 'Benjamin' , 'Harrison'  , '1833-08-20' ),
                     ( 'William'  , 'McKinley'  , '1843-01-29' ),
                     ( 'Theodore' , 'Roosevelt' , '1858-10-27' ),
                     ( 'William'  , 'Taft'      , '1857-09-15' ),
                     ( 'Woodrow'  , 'Wilson'    , '1856-12-28' ),
                     ( 'Warren'   , 'Harding'   , '1865-11-02' ),
                     ( 'Calvin'   , 'Coolidge'  , '1872-07-04' ),
                     ( 'Herbert'  , 'Hoover'    , '1874-08-10' ),
                     ( 'Franklin' , 'Roosevelt' , '1882-01-30' ),
                     ( 'Harry'    , 'Truman'    , '1884-05-08' ),
                     ( 'Dwight'   , 'Eisenhower', '1890-10-14' ),
                     ( 'John'     , 'Kennedy'   , '1917-05-29' ),
                     ( 'Lyndon'   , 'Johnson'   , '1908-08-27' ),
                     ( 'Richard'  , 'Nixon'     , '1913-01-09' ),
                     ( 'Gerald'   , 'Ford'      , '1913-07-14' ),
                     ( 'Jimmy'    , 'Carter'    , '1924-10-01' ),
                     ( 'Ronald'   , 'Reagan'    , '1911-02-06' ),
                     ( 'George'   , 'Bush'      , '1924-06-12' ),
                     ( 'Bill'     , 'Clinton'   , '1946-08-19' ),
                     ( 'George'   , 'Bush'      , '1946-07-06' ),
                     ( 'Barack'   , 'Obama'     , '1961-08-04' ),
                     ( 'Donald'   , 'Trump'     , '1946-06-14' )
            }


# PART TWO : display the content of table Presidents in TkTreeCtrl

# Variables needed for paging the recordset, using LIMIT + OFFSET clauses :

# Remark : LIMIT + OFFSET use for recordset paging is an unefficient method on big recordset (choosed here for simplicity)

set ::_nbLigW 10    ;# nb of records, read as a whole, from SQlite and inserted in one chunk in a range of items of the TreeCtrl widget.
                     # A "page", materialization of this chunk, is identified by "pageW" variable
set ::pageW 0       ;# starts from page 0

# register the count of records present in table Presidents

set nbRecords [db1 eval "SELECT count(*) FROM Presidents"]


proc InitData {} {
    # fill the treectrl with a subset of Presidents table
    
    # traverse the current page of the recordset : 
    db1 eval "SELECT Country, FirstName, LastName, BirthDate
              FROM Presidents
              ORDER BY LastName, FirstName
              LIMIT $::_nbLigW OFFSET $::pageW * $::_nbLigW" result {
        
        set item [.t.frame.tree item create -parent root]
        
        foreach col $result(*) {
            
            .t.frame.tree item style set $item [.t.frame.tree column id $col] s1
            
            .t.frame.tree item text $item [.t.frame.tree column id $col] $result($col)
        }
    }
}

# Create a toplevel treectrl

# toplevel
    
    toplevel .t
    wm protocol .t WM_DELETE_WINDOW {exit}

#  treectrl + scrollbars
    
    ttk::frame .t.frame
    ttk::scrollbar .t.frame.sy -command [namespace code [list .t.frame.tree yview]] -orient vertical
    ttk::scrollbar .t.frame.sx -command [namespace code [list .t.frame.tree xview]] -orient horizontal
    
    treectrl .t.frame.tree -yscrollcommand [list .t.frame.sy set] \
                           -xscrollcommand [list .t.frame.sx set]
    
    pack .t.frame.sy   -side right  -fill both
    pack .t.frame.sx   -side bottom -fill both
    pack .t.frame.tree -side left   -fill both -expand 1
    
    pack .t.frame -side top -fill both -expand 1
    
    # Create style
        
        .t.frame.tree element create el1 text
        
        .t.frame.tree style create s1
        .t.frame.tree style elements s1 el1
        .t.frame.tree style layout s1 el1 -iexpand nsew
    
    # Create columns (Country, FirstName, LastName, BirthDate) :
        
        foreach col [list Country FirstName LastName BirthDate] {
            
            .t.frame.tree column create -text $col \
                                        -tags $col
        }
    
    # Create treectrl' items with data loaded from table Presidents :
    # -> call proc InitData
        
        InitData

# buttons for navigation
    
    ttk::frame .t.btns
    
    # button "previous page"
    ttk::button .t.btns.previous -text "previous page" \
                                 -command {
        
        # If already on the first page
        if {$::pageW == 0} {break}
        
        # Calculate the previous page
        incr ::pageW -1
        
        # Delete all existing items
        .t.frame.tree item delete 1 [.t.frame.tree item lastchild root]
        
        # Create new items for the new page
        InitData
    }
    
    # button "next page"
    ttk::button .t.btns.next -text "next page" \
                             -command {
        
        # If already on the last page
        if {$::_nbLigW * ($::pageW + 1) >= $nbRecords} {break}
        
        # Calculate next page
        incr ::pageW
        
        # Delete all existing items
        .t.frame.tree item delete 1 [.t.frame.tree item lastchild root]
        
        # Create new items for the new page
        InitData
    }
    
    pack .t.btns.next     -side right -padx 10
    pack .t.btns.previous -side right -padx 10
    
    pack .t.btns -side bottom -pady 10

update

# Resize toplevel
wm geometry .t [winfo reqwidth .t]x[winfo reqheight .t]