Wishlist:wxSqlite3RecordSet also has First/Previous/Last

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by samsam598 »

Found in other C++ lib(Qt sql,Poco),the basic navigation function is provided by the library.Hope the best C++ sqlite3 lib for wxWidgets could also add this into next release.

I tried to implemented with wxSqlite3,but it is a bit hard to me.Also noticed some other implementation is just put the record data into a STL container and seek back every time when a recordSet->Previous()/Last()/First() is called.Don't know whether this implementation has performance problem.

Regards,
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by utelle »

samsam598 wrote:Found in other C++ lib(Qt sql,Poco), the basic navigation function is provided by the library. Hope the best C++ sqlite3 lib for wxWidgets could also add this into next release.
Unfortunately SQLite doesn't provide scrolling cursors (see also scrolling cursors on the SQLite web site). Therefore providing cursor navigation in a general way isn't possible for SQLite. If you take a look at Poco for example the problem is "solved" by caching the whole result set in memory. This certainly works for small result sets, but it won't work for large result sets consisting of millions of rows or giga bytes of data.
samsam598 wrote:I tried to implemented with wxSqlite3, but it is a bit hard to me. Also noticed some other implementation is just put the record data into a STL container and seek back every time when a recordSet->Previous()/Last()/First() is called. Don't know whether this implementation has performance problem.
As indicated above you would have to cache the data of the whole result set, so you might run out of memory. Using large amounts of memory also could lead to performance problems if the operating system starts to swap memory pages onto disk.

But if you really need to have a data structure in which you may freely navigate and if you know the result set will be small enough to fit in memory wxSQLite3 already provides a class for this purpose: wxSQLite3Table. Use method GetTable of class wxSQLite3Database to execute a query.

But keep in mind that the use of wxSQLite3Table is not recommended as I told you already in a previous thread in June.

Regards,

Ulrich
lollisoft
Earned some good credits
Earned some good credits
Posts: 115
Joined: Sat Jul 23, 2005 3:52 pm
Location: Germany
Contact:

Re: Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by lollisoft »

I think it will be possible and I have done it using another library that does not support cursors.

First of all it is a question about modifying the library or not. In the case of my solution I have modified the library but in long term it may be better not to do that.

For the performance issue I may argue that it is not better if you load all data and only provide 'next'. Even the wxSqlite3 library will not do so, one needs this for cursor functionality. ODBC supports a cursor library for the case if the database API doesn't directly support Cursors. That means the Cursor library does not modify the database library supporting ODBC. Instead it wraps around - I think.

To implement similar stuff, I have choosen to fetch a list of the primary keys the query implicitely contains. This result is cached in memory and reduces the memory footprint. Then when I like to see one row I load the row by the key. This can be seen as a Lazy Load pattern implementation, but not on an object tree related to one entity (customer <- orders). Rather it is a mapping of customer pk <- customer row. (Patterns of Enterprise Application Architecture, M. Fowler, page 200, ISBN: 0321127420, Amazon) Then I have implemented a paging mechanism to further reduce the memory footprint of the primary key result set by storing the min and max value of each page to enable further fetching of subsequent pages by key > max or key < min and reduce the amount of data to the page size. Reading the keys that are smaller the min value of the current page was a bit tricky, but is simple. You have to revert the sort order to get the next close value of key < max page value at top of your result set. Then you also could reduce the resultset to the paging size.

There are other patterns that may relate, like caching, but I haven't those at hand directly relating to database result sets. The caching pattern more likely relates to client server stuff at a greater area than SQL queries and Cursors.

Using this scheme you will have a virtual cursor over a database library not supporting cursors. It may be working only on single primary keys and not combined one as due to lack of testing of my modifications. Also it would not work if there is another sort order in a query.

I still need to decide to backport this enhanvement into the wxActiveRecord library, but the case of not modifying existing libraries is worth thinking twice about backporting this. I more think about a wxCursor library that implements this in general.

What do you think?

Lothar
OS: Windows 7, Mac OS X (Panther/Leopard/Snow Leopard), SuSE Linux, Debian (PPC), OpenMoko FreeRunner
Compiler: OpenWatcom, GCC
wxWidgets 2.8.x
IDE Makefile based.
RAD My own brewed, Code generation with XSLT and DialogBlocks
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by utelle »

lollisoft wrote:I think it will be possible and I have done it using another library that does not support cursors.
Which library? Please be a bit more explicit.
lollisoft wrote:First of all it is a question about modifying the library or not. In the case of my solution I have modified the library but in long term it may be better not to do that.
You do suggest to modify the underlying SQLite library, don't you? SQLite inherently supports forward cursors only. Changing this would mean to write a complete new database library. I wouldn't go for that.
lollisoft wrote:For the performance issue I may argue that it is not better if you load all data and only provide 'next'. Even the wxSqlite3 library will not do so, one needs this for cursor functionality.
I don't understand your argument. For using a forward cursor only you don't need to load all data. This is required only if you need random access to the records of your result set. If your result set fits into memory SQLite - and wxSQLite3 - allow to load the complete result set and to access the records in random sequence. In wxSQLite3 you use the class wxSQLite3Table for that purpose.
lollisoft wrote:ODBC supports a cursor library for the case if the database API doesn't directly support Cursors. That means the Cursor library does not modify the database library supporting ODBC. Instead it wraps around - I think.
I don't know ODBC very well, but if random access cursors are supported for databases which don't offer this feature natively the result set must be cached sonehow, either in memory or on disk.

BTW there is another issue to consider: if you move the database cursor to the same record again, should the library check whether there were database changes affecting the record set? At least the table feature supported by SQLite doesn't do it.
lollisoft wrote:To implement similar stuff, I have choosen to fetch a list of the primary keys the query implicitely contains.
The latter is simply not true. In general it's not guaranteed that a result set has a primary key. Even if the database system adds implicitly something like a record number, there is usually no way to query the database based on this implicit key only.
lollisoft wrote:This result is cached in memory and reduces the memory footprint.
If your result set has in fact a primary key and the set of primary keys fits into memory, this is the way to go.
lollisoft wrote:Then when I like to see one row I load the row by the key.
Yes, but it has a certain drawback: you always get the latest version of the record. Sometimes this is what you want, sometimes not.
lollisoft wrote:This can be seen as a Lazy Load pattern implementation, but not on an object tree related to one entity (customer <- orders). Rather it is a mapping of customer pk <- customer row. (Patterns of Enterprise Application Architecture, M. Fowler, page 200, ISBN: 0321127420, Amazon) Then I have implemented a paging mechanism to further reduce the memory footprint of the primary key result set by storing the min and max value of each page to enable further fetching of subsequent pages by key > max or key < min and reduce the amount of data to the page size. Reading the keys that are smaller the min value of the current page was a bit tricky, but is simple. You have to revert the sort order to get the next close value of key < max page value at top of your result set. Then you also could reduce the resultset to the paging size.
This method works perfectly if your record set has a primary key and can be implemented with SQLite and wxSQLite3 quite easily.
lollisoft wrote:There are other patterns that may relate, like caching, but I haven't those at hand directly relating to database result sets. The caching pattern more likely relates to client server stuff at a greater area than SQL queries and Cursors.
Well, in the case of SQLite you may use SQLite itself for caching. Just save the result of your query to a temporary table. You may then use the record numbers to fetch a row at random. But certainly it has a price in performance degradation.
lollisoft wrote:Using this scheme you will have a virtual cursor over a database library not supporting cursors. It may be working only on single primary keys and not combined one as due to lack of testing of my modifications. Also it would not work if there is another sort order in a query.
Using a temporary table should always work.
lollisoft wrote:I still need to decide to backport this enhanvement into the wxActiveRecord library, but the case of not modifying existing libraries is worth thinking twice about backporting this. I more think about a wxCursor library that implements this in general.

What do you think?
I don't think it's worth the effort. There is no single "best" solution for the general case.

Regards,

Ulrich
lollisoft
Earned some good credits
Earned some good credits
Posts: 115
Joined: Sat Jul 23, 2005 3:52 pm
Location: Germany
Contact:

Re: Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by lollisoft »

Sorry for my late response. I do not regularly follow all forums :-(
utelle wrote:
lollisoft wrote:I think it will be possible and I have done it using another library that does not support cursors.
Which library? Please be a bit more explicit.
I have written a wrapper around the wxDatabaseLayer library. The library it self got some extensions to get missing meta information that I need. I'll have to backport it.
utelle wrote:
lollisoft wrote:First of all it is a question about modifying the library or not. In the case of my solution I have modified the library but in long term it may be better not to do that.
You do suggest to modify the underlying SQLite library, don't you? SQLite inherently supports forward cursors only. Changing this would mean to write a complete new database library. I wouldn't go for that.
I have not extended the underlying SQLite library at the end.
utelle wrote:
lollisoft wrote:For the performance issue I may argue that it is not better if you load all data and only provide 'next'. Even the wxSqlite3 library will not do so, one needs this for cursor functionality.
I don't understand your argument. For using a forward cursor only you don't need to load all data. This is required only if you need random access to the records of your result set. If your result set fits into memory SQLite - and wxSQLite3 - allow to load the complete result set and to access the records in random sequence. In wxSQLite3 you use the class wxSQLite3Table for that purpose.
I mean the library must know if it should cache records for moving back operations. The library cannot know this. So if you need that feature you have to tell the library to cache and activate the functions other than 'next'. My changes do not support this feature because I do only load ten primary records for caching. It is a compromise. If I'll start with ORM then I probably do not need cursors anymore as the container containing the entities are doing this. Then even the ten primary records are too much if you have a big ORM model.
utelle wrote:
lollisoft wrote:ODBC supports a cursor library for the case if the database API doesn't directly support Cursors. That means the Cursor library does not modify the database library supporting ODBC. Instead it wraps around - I think.
I don't know ODBC very well, but if random access cursors are supported for databases which don't offer this feature natively the result set must be cached sonehow, either in memory or on disk.
Yes, I'll assume caching. But I do not know how much they cache. Maybe it relates to the number of loaded rows per default = 100, or the like.
utelle wrote: BTW there is another issue to consider: if you move the database cursor to the same record again, should the library check whether there were database changes affecting the record set? At least the table feature supported by SQLite doesn't do it.
I am not sure, if that works pretty well too. The reason is I am not sharing a Sqlite database. It is a local file only. To my implementation, I think I have no caching for the data, only the keys are cached.
utelle wrote:
lollisoft wrote:To implement similar stuff, I have choosen to fetch a list of the primary keys the query implicitely contains.
The latter is simply not true. In general it's not guaranteed that a result set has a primary key. Even if the database system adds implicitly something like a record number, there is usually no way to query the database based on this implicit key only.
Maybe you can not expect this for a foreign database model, but I'll always generate primary keys in my models. But this is application specific and thus it may work for me.
utelle wrote:
lollisoft wrote:This result is cached in memory and reduces the memory footprint.
If your result set has in fact a primary key and the set of primary keys fits into memory, this is the way to go.
Yes. It is not that easy if you have a key with more than one column, or using a different sorting, but in my case it is enough to work with it. My tool is for prototyping database applications, and thus it has some limitations.
utelle wrote:
lollisoft wrote:Then when I like to see one row I load the row by the key.
Yes, but it has a certain drawback: you always get the latest version of the record. Sometimes this is what you want, sometimes not.
Yes, then you'll probably have to implement an in memory representation of your database schema and do not directly operate on the database layer. This is not required for my application - yet, but in work.
utelle wrote:
lollisoft wrote:This can be seen as a Lazy Load pattern implementation, but not on an object tree related to one entity (customer <- orders). Rather it is a mapping of customer pk <- customer row. (Patterns of Enterprise Application Architecture, M. Fowler, page 200, ISBN: 0321127420, Amazon) Then I have implemented a paging mechanism to further reduce the memory footprint of the primary key result set by storing the min and max value of each page to enable further fetching of subsequent pages by key > max or key < min and reduce the amount of data to the page size. Reading the keys that are smaller the min value of the current page was a bit tricky, but is simple. You have to revert the sort order to get the next close value of key < max page value at top of your result set. Then you also could reduce the resultset to the paging size.
This method works perfectly if your record set has a primary key and can be implemented with SQLite and wxSQLite3 quite easily.
I do not know how to handle the case if there is no primary key. If the data contains multible records that even contain double entries, you cannot update one row only - I think. In my opinion any table SHOULD have a primary key or at least an unique index. Using that index may be an option, but you also have to detect the index from the meta information. I do not remember doing that in the my wrapper and wxDatabaseLayer implementation.
utelle wrote:
lollisoft wrote:There are other patterns that may relate, like caching, but I haven't those at hand directly relating to database result sets. The caching pattern more likely relates to client server stuff at a greater area than SQL queries and Cursors.
Well, in the case of SQLite you may use SQLite itself for caching. Just save the result of your query to a temporary table. You may then use the record numbers to fetch a row at random. But certainly it has a price in performance degradation.
lollisoft wrote:Using this scheme you will have a virtual cursor over a database library not supporting cursors. It may be working only on single primary keys and not combined one as due to lack of testing of my modifications. Also it would not work if there is another sort order in a query.
Using a temporary table should always work.
lollisoft wrote:I still need to decide to backport this enhanvement into the wxActiveRecord library, but the case of not modifying existing libraries is worth thinking twice about backporting this. I more think about a wxCursor library that implements this in general.

What do you think?
I don't think it's worth the effort. There is no single "best" solution for the general case.
At the end you will have noted - I am using wxDatabaseLayer - not wxActiveRecord :-)
But the cursor implementation for my purposes is like a quick hack. I think it will fail for several reasons I not yet have tested for.

I have the ok from the developer of wxDatabaseLayer to backport, but I'll have to create unit tests and integrate them. Also I like to get a feedback of the tests I'll add to check if I have thought about all.

Currently I am still at my main project, so it will take another while to really start backporting :-(

Implementing it directly in wxSQLite3RecordSet is a complete different implementation, but the ideas may be the same. And yes - using cursors depends :-)
utelle wrote:

Regards,

Ulrich
Regards,

Lothar
OS: Windows 7, Mac OS X (Panther/Leopard/Snow Leopard), SuSE Linux, Debian (PPC), OpenMoko FreeRunner
Compiler: OpenWatcom, GCC
wxWidgets 2.8.x
IDE Makefile based.
RAD My own brewed, Code generation with XSLT and DialogBlocks
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Wishlist:wxSqlite3RecordSet also has First/Previous/Last

Post by utelle »

lollisoft wrote:I have written a wrapper around the wxDatabaseLayer library. The library it self got some extensions to get missing meta information that I need. I'll have to backport it.
From the rest of your posting I get the impression that you built an O/R mapping layer on top of wxDatabaseLayer. If that is so, it could probably adapted to work directly with wxSQLite3. That might be interesting to some users. But O/R mapping is a completely different story than the topic of this thread. The original poster wanted to have a general scrolling cursor feature for the result sets of wxSQLite3.
lollisoft wrote:
utelle wrote:
lollisoft wrote:First of all it is a question about modifying the library or not. In the case of my solution I have modified the library but in long term it may be better not to do that.
You do suggest to modify the underlying SQLite library, don't you? SQLite inherently supports forward cursors only. Changing this would mean to write a complete new database library. I wouldn't go for that.
I have not extended the underlying SQLite library at the end.
Well, you're contradicting yourself. First you said you modified the library, now you say you didn't. Is your work publicly available for inspecting and/or using it?
lollisoft wrote:I mean the library must know if it should cache records for moving back operations. The library cannot know this. So if you need that feature you have to tell the library to cache and activate the functions other than 'next'. My changes do not support this feature because I do only load ten primary records for caching. It is a compromise. If I'll start with ORM then I probably do not need cursors anymore as the container containing the entities are doing this. Then even the ten primary records are too much if you have a big ORM model.
Caching a limited number of rows doesn't solve the scrolling cursor problem at all.
lollisoft wrote:
utelle wrote:
lollisoft wrote:ODBC supports a cursor library for the case if the database API doesn't directly support Cursors. That means the Cursor library does not modify the database library supporting ODBC. Instead it wraps around - I think.
I don't know ODBC very well, but if random access cursors are supported for databases which don't offer this feature natively the result set must be cached sonehow, either in memory or on disk.
Yes, I'll assume caching. But I do not know how much they cache. Maybe it relates to the number of loaded rows per default = 100, or the like.
IMHO this is nonsense. If scrolling cursors are supported for a database backend which doesn't support scrolling cursors you have to cache the whole result set.
lollisoft wrote:
utelle wrote: BTW there is another issue to consider: if you move the database cursor to the same record again, should the library check whether there were database changes affecting the record set? At least the table feature supported by SQLite doesn't do it.
I am not sure, if that works pretty well too. The reason is I am not sharing a Sqlite database. It is a local file only. To my implementation, I think I have no caching for the data, only the keys are cached.
Most applications using SQLite (and wxSQLite3) have local database files. Nevertheless the application might be multi-threaded. It depends on the requirements of the application whether changes by another thread or another application need to be recognized or not.
lollisoft wrote:
utelle wrote:
lollisoft wrote:To implement similar stuff, I have choosen to fetch a list of the primary keys the query implicitely contains.
The latter is simply not true. In general it's not guaranteed that a result set has a primary key. Even if the database system adds implicitly something like a record number, there is usually no way to query the database based on this implicit key only.
Maybe you can not expect this for a foreign database model, but I'll always generate primary keys in my models. But this is application specific and thus it may work for me.
Then you're lucky having primary keys. In case of a composed key the WHERE clause might get quite complex to implement a scrolling cursor feature but it's certainly doable. In the general case you don't have a primary key.

For SQLite you could implement scrolling cursors by selecting the original query into a temporary table and using the implicit integer primary key SQLite creates to access individual rows. In SQLite you can access this implicit key using the pseudo column ROWID. The price for this solution is that you materialize the query result. And that requires storage space and time.
lollisoft wrote:I do not know how to handle the case if there is no primary key. If the data contains multible records that even contain double entries, you cannot update one row only - I think. In my opinion any table SHOULD have a primary key or at least an unique index. Using that index may be an option, but you also have to detect the index from the meta information. I do not remember doing that in the my wrapper and wxDatabaseLayer implementation.
Updateing a row is a different story. Of course you need to be able to identify a row. If you're not you can only operate on the set of rows sharing the same key, but that might just be what you want.

For detecting the index from the meta information I don't see a reason. The database system will use indices to fulfill your queries efficiently. Usually you can't influence directly which indices are used and which not, although you may guide the database system by how you formulate your query.

For persistent tables you'll usually define primary keys or unique indices although this is not required by SQL. For general query results primary or unique keys might not exist at all.
lollisoft wrote:At the end you will have noted - I am using wxDatabaseLayer - not wxActiveRecord :-)
I have no experience with wxDatabaseLayer nor with wxActiveRecord.
lollisoft wrote:Implementing it directly in wxSQLite3RecordSet is a complete different implementation, but the ideas may be the same. And yes - using cursors depends :-)
I still believe there is no general solution except caching the complete result set either in memory or as a temporary table. But you can't tell in advance whether it's feasible or not.

If an application is confronted with the task to display millions of rows using a scrolling cursor I'd say the developer should rethink the concepts of the application. No user wants to scroll through millions of rows. In that case filter functions should be provided to limit the size of a result set to a reasonable number of rows.

Regards,

Ulrich
Post Reply