Database / wxSQLite

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
AKG
Earned a small fee
Earned a small fee
Posts: 14
Joined: Wed Aug 17, 2016 2:49 pm

Database / wxSQLite

Post by AKG »

=== what I am using ===
I am using an sqlite database and currently trying the wxSQLite
I am using CB16.01
I am on Windows 10

=== my level of understanding ===
I have 20 years experience programming, but I am new to wxWidgets.

=== what i am making ===
I am developing an application that updates a complex database, many relationships and many tables / lists of data on the same window that are related depending on what the user select in on of the tables/lists.

I am using separate windows for the updates of these tables / lists - so when you select a row in a table and click edit, you get a new window where you can edit the data. After you are done with the edit, and click ok, you return - and the tables / lists are now supposed to show the new rows and select them automatically etc.

I am trying to do this with the wxGrid but find it hard to figure out and format / design properly. I get column number on the left side of the grid, and other issues trying to design an format it.

I also cant figure out a fast way to generate code - for the database so i don't have to type so much.... (like you have for example in some Java tools)

=== My questions ===
What I would really appreciate is to hear what others are using when doing similar things. What controls to put on the form, if using the wxGrid or other controls .... if using wxSmith or the other form generator and so on.

In Java or other tools, I have before been able to autogenerate getters and setters for updating tables etc... there are many automated things that can be done... what options exist using wxWidgets ?

The point is that I want the forms and tables i put on the window in the designer to load something i update from the database.
For example if I have a List Control on the window, that I can format in the designer, that is reading a table in memory. When the database object changes the table in memory, the list automatically updates. Then I want to be able to automatically generate code to fill in the memory table by select a databse table and asking it to generate the code with setters and getters etc.

I hope this is making sense... maybe i am not explaining so well. English is also not my native language.
User avatar
doublemax
Moderator
Moderator
Posts: 19103
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: Database / wxSQLite

Post by doublemax »

One warning ahead: You'll have to type much more in C++ than in Java. E.g.AFAIK there are no convenience classes that easily map a database to a visual control. But as you're an experienced programmer yourself, you'll probably write some wrapper classes that make your live easier, so you only have to do this once.

The first and biggest decision to make is which control to use. If you need to display only text, no bitmaps and no checkboxes etc. for the database, i'd recommend to use a virtual wxListCtrl. It'll be much easier to handle than wxGrid. For wxGrid you'd have to write your own wxGridTable.
For example if I have a List Control on the window, that I can format in the designer, that is reading a table in memory.
Avoid to read a complete database into memory. The database *is* your data model. Only fetch the rows you need to display or want to edit. In a "virtual" wxListCtrl, the key is to override wxListCtrl::OnGetItemText() which will ask for the content of a single field each time it needs to paint it on screen. You should fetch that directly from the database. For optimization, you can cache one row, so that you only have one SELECT per row. Sqlite is very good at utilizing the file cache of the underlying OS and this will be fast. I have a small application that displays a sqlite database with a few million rows and you can scroll in realtime in it (from an SSD).

wxSQLite3 will do the ugly work of interfacing sqlite with wxWidgets datatypes. The only thing i don't like about wxSQLite3 is its excessive use of exceptions for non-fatal things, which messes the user code with lots of catch/throw blocks. Therefore i personally use a modified version of wxSQLite3 where all exceptions except one or two are removed.
Use the source, Luke!
AKG
Earned a small fee
Earned a small fee
Posts: 14
Joined: Wed Aug 17, 2016 2:49 pm

Re: Database / wxSQLite

Post by AKG »

Im not sure i understand how you did this ?? Not familiar enough with the widgets control at this time. Can you give me some more details ?
I change the query, get a new result set, and now i have to write code to make my listbox read that new result set... in other tools the control is reading a something like a multi dimentional array (hasmap) - and the db code will fill the result set in to the array and tell the listbox to repaint itself...

but the wxWidget lit box control are not so familiar to me at this tim, so if you have more details it would be great. Im trying to read the documentation, but it does not really explain the inner workings of the list box ...

Thank you...
User avatar
doublemax
Moderator
Moderator
Posts: 19103
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: Database / wxSQLite

Post by doublemax »

I change the query, get a new result set, and now i have to write code to make my listbox read that new result set... in other tools the control is reading a something like a multi dimentional array (hasmap) - and the db code will fill the result set in to the array and tell the listbox to repaint itself...
No. The point of a "virtual" control is that it doesn't store any data at all. Only if it needs to paint a cell, it will ask *you* for the content as a string. For that purpose you have to derive from wxListCtrl and override wxListCtrl::OnGetItemText() with your own code that returns the content.

In a totally non-optimized way it could look like this:

Code: Select all

wxString MyListCtrl::OnGetItemText(long item, long column) const
{
    wxString sql_cmd;
    sql_cmd.Printf("SELECT * FROM table_name LIMIT %d, 1;", item );
    
    // wxSQLite3Database m_db;    (member of MyListCtrl)
    wxSQLite3ResultSet res = m_db.ExecuteQuery( sql_cmd );
    if( res.NextRow() )
      return res.GetAsString( column );
    else
      return wxString::Format( "error (%d.%d)", item, column );
}
After you get this working, you only need two small optimizations for performance:
a) A vector or hashmap that links the visible row number to an ID in the database. That gets rid of the slow "LIMIT" cause.
b) Cache the contents of all fields in the current row, so you get only one SELECT per row.
Use the source, Luke!
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Re: Database / wxSQLite

Post by evstevemd »

Just to add more info, there are UI builder: wxFormbuilder, wxCrafter for CodeLite and wxSmith for Code::Blocks. They do not generate any database mapping as DM have said above but they save a lot of pains in writing UI.
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
Post Reply