SQLite Interface Plugin

---

Description:
This plugin allows SMath Studio to interface the SQLite relational database engine. It has been developed for .NET 2.0 and SMath 0.98.5935 or newer


Download & Installation:
For SMath versions 0.98.5935 or newer the plugin can be conveniently downloaded and installed using the extension manager.

Example and source code are now available in the SVN as well: https://smath.info/svn/p...ic/plugins/SQLitePlugin/

To manually install the plugin, copy the folder (and all its contents) from the "SQLitePluging-[version].zip" archive into "%appdata%\SMath\extensions\plugins\".
To run the example extract the contents of the "SQLiteExample-[version].zip" to any directory, make sure database and SMath sheet are located in the same directory and open the example sheet. Some older versions of SMath seem to require an additional "eval" in some of the statements, so two versions of the example sheet are provided.

Usage
Usage of the plugin is very simple:

This will open the database file at the specified path (if not already open), execute the specified query and return the corresponding results. The results of the query will be returned as a matrix of corresponding size. A single result will still be returned as a 1x1 matrix. If no results are found the plugin will return Matrix(0,0). Empty (NULL) entries will be returned as the custom unit "'NULL" and are distinguishable from empty strings
All used database files are automatically closed when SMath Studio is closed.

Example



Limitations, Known Problems and Remarks

• Beta version, use with caution
  
• I have not yet checked whether different culture settings lead to conversion errors
  
• Only SELECT statements have been tested so far
  Limited testing on write access queries seems promising, so far
  
• Two DLL files need to be copied to the plugins folder, I intend to merge the assemblies in future
  Assemblies merged, plugin is now a single dll
  Complete folder provided, Extension Manager upload in progress
  
• Only 64 bit is currently supported, sorry. Working on 32 bit support
  Both 64 and 32 bit now supported (tested in 5935, 6081 and 6083 using WoW64)
  
• Since it is not possible to use "=" within a SMath string this has to be considered when designing a query. "IS" and is usually a suitable replacement. "IS NOT" serves as a replacement for "!="
  The boolean 'equal' (Ctrl + =) works very well (tested). Thanks Martin Kraska and Mike Kaganski for pointing out my error

Story
I started using SMath Studio about 6 months ago, when I got sufficiently annoyed with the free version of MathCAD (no linterp? seriously?) and have since grown quite fond of it. I had a look around the Forum and the sheer amount of time, effort and dedication some of the people involved seem to invest is rather inspiring.
Yesterday I found myself in need of a way to conveniently load a whole set of material properties and coefficients for curve fits by only specifying a part number - a task that a relational database seemed well suited for. I could not find an existing database interface and was quite interested in learning more about SMath plugin development, so I wrote the plugin myself (having previous experience with SQLite and C#).
The performance seems pretty good, compared with the Excel I/O plugin. Comparing the corresponding examples the SQLite version executes on my machine initially in 0.7s, then in about 0.03s once the DB is opened. The Excel version takes 3.2s initially, 0.18s once the excel sheet is open.
Since I enjoy the privilege to freely use SMath Studio, I gladly provide my plugin here as well, hoping you may find it to be useful.
Please feel free to have a look and let me know what you think. I would very much welcome any problem reports, suggestions for improvements, requests for further explanations etc.

Acknowledgements

• Alex.M - I have taken the liberty here to adapt the example and database form Alex' Excel I/O plugin to demonstrate the SQLite interface within a context that might be familiar to some users. I had to change some property names since SQLite ignores case, b and B have become b1 and b2 etc.
  
• Davide Carpi - The XlsxImportExport plugin demonstrated to me how to properly return results in form of a Matrix. Also thanks for the useful feedback
  
• Martin Kraska - I found the "SMath Studio Handbuch" to be a great starting point and reference
  
• Martin Kraska & Kay Graubmann - The Maxima plugin source code provided some valuable insights into SMath plugin development
  
• Mike Kaganski - His quick testing, excellent feedback and good ideas have substancially accellerated and improved the development of this plugin

Changelog

• 08/09/16 - Made plugin available to download via extension manager, uploaded source code and example to SVN, fixed relative path as suggested by Davide, changed loading of linked SQLite assemblies for x86 and x64 to allow installation from extension manager for SMath below 8069
  
• 31/08/16 - Removed single argument SQLiteQuery due to inconsistencies with partial recalculation. Null entries in database return custom unit 'NULL and are now distinguishable from empty strings, simplified code. Fixed and improved the example file, added typetest table to example database
  
• 29/08/16 - Updated SQLiteQuery to use path as handle, removed SQLiteOpen and SQLite close, added automatic closing of database connections at SMath exit, typecasting bugfixes, refactoring and minor improvements, repackaged for proper plugin installation
  
• 27/08/16 - Merged assemblies, now the plugin only consists of a single dll. Correct SQLite interop is automatically loaded, 64bit and 32bit are now supported. (Tested with SMath 5935, 6081 and 6083 )
  
• 26/08/16 - Added support for multiple simultaneous database connections, returning results consistently as matrix now
  
• 25/08/16 - First release

SQLite resources:

• https://sqlite.org/about.html - SQLite overview
  
• https://www.sqlite.org/datatype3.html - More about SQLite type affinities
  
• http://www.tutorialspoint.com/sqlite/index.htm - Useful SQLite tutorial
  
• http://portableapps.com/...atabase_browser_portable - Portable SQLite database file editor

Edited by moderator 06 November 2020 12:51:18(UTC)  | Reason: Not specified

"with the free version of MathCAD (no linterp? seriously?)"
Crappy PTC vs so nice Mathsoft !
In case you need linterp in what works from your free version:
=> here is the code, except for the Mathcad index 0 ORIGIN.
In supplement, I have the code l_p_cspline [not attached].

Jean

Interpolate [Linterp, Table].sm (40kb) downloaded 81 time(s).
Spline Matrix Tutorial.sm (55kb) downloaded 78 time(s).

Thanks to everyone for the helpful replys.

I have edited the the inital post with an updated version of the plugin.


Yes, thank you. This is exactly what I have done, SQLiteOpen will now return the index of the database connection that has just been opened, starting with 0. To prevent memory leaks associated with undesired reopening of database files and to speed up sheet recalculation the SQLite plugin will not reopen the same file again and instead return the handle that has been generated when the file was opened the first time. If the file has since been closed it will be reopened but the handle will not change.


A very good suggestion, implemented straight away.



Thank you for pointing out my mistake, tested and found to be working nicely, updated in original post.

On further consideration, I see the following possible improvements to SQLite plugin interface:

1. SQLiteOpen() is unnecessary.
Rationale: currently, you maintain internally a structure that maps a DB path, "handle" that is returned by SQLiteOpen() and (presumably) an internal session handle. Each SQLiteOpen() invocation checks if the DB denoted by path is already open, and if so, it returns already assigned "handle", otherwise it creates a new session.
This may be implemented instead in any functional SQLite function (like SQLiteQuery()), so that the only SMath database "handle" would be the DB path itself.
The proposal is the following: drop SQLiteOpen(); in all other SQLite functions, make a mandatory first argument "path". At each point in the code where SQLite DB is accessed, use the map lookup to find out if the DB is already open, and open it if necessary.
I believe that the overhead of string comparison is negligible compared to SQL parsing etc., and this would simplify the interface and make it more functional (avoid purely auxiliary function that doesn't return result that is directly useful for user).

2. SQLiteClose is unnecessary.
Rationale: the function's intention is apparently to (1) flush the possible changes made by modifying SQL, and (2) to release the DB file (e.g., to allow its renaming/removing/other file management). But the function isn't guaranteed to be executed. E.g.: if the DB is open (and possibly modified), then goes a lengthy block of calculations, and after that, the SQLiteClose is invoked. A user can interrupt the calculations, thus disrupting the intended resource flush/cleanup. Also, see no.4 below for a concurrent access discussion.
So, to have a robust DB handling, the following is proposed: drop SQLiteClose() entirely as purely auxiliary; instead, on plugin cleanup*, close all open DB connections properly**.
* The cleanup is either at plugin unload (at SMath unload), or at sheet close - see no.4 below.
** The proposed method of closing properly is Commit - see no.3 below.

3. Two new functions are needed: SQLiteCommit(path) and SQLiteRoolback(path).
Rationale: these are functions that allow user to control if and when the data is actually committed to DB. It mat be used if necessary; and may be omitted, thus defaulting to commit at DB closing (see no.2 above).

4. Concurrency should be considered.
It is possible that two sheets in one SMath process try to access the same DB simultaneously; or that two SMath instances try to access same DB; or that SMath and another app try to use the same DB; or two different computers try to use a DB on a network share.
I am not familiar with how SQLite solves these situations itself; it is a sane assumption that accessing a DB from different processes could be prohibited (file lock).
Opening it from the same process / different sheets is required to be thread-safe. Also, it is desirable that closing any single SMath sheet would commit data and close its connections (as discussed in no.2 above).
Alternatively, if there isn't a way to catch the "sheet closing" event, then it could be done at SMath exit, but it's an inferior solution, because it will keep DB files locked after using sheets are closed.
But there is a problem WRT commit/rollback logic. If it's possible to keep different transactions for each sheet, then there is a question how the resulting conflicting commits will be handled. Consider this: sheet A inserts a record to a table into a DB; sheet 2 inserts another record into the same DB. Sheet 1 is closed. Sheet 2 is closed. If they maintained different transactions, the two records may happen to have the same primary key -> ...? If they share the same transaction, then the problem is different: if sheet 1 commits, and after that sheet 2 rollbacks, then the rollback of sheet 2 would be unsuccessful. I suppose that if these problems are not solved in a consistent way, then concurrent use of DBs should be prohibited.

Ok, the plugin opens *if* it is placed into %ProgramFiles(x86)%\SMath Studio\Plugins. That's a wrong place for custom plugins.

On the other hand, if I put it into %appdata%\SMath\extensions\plugins\SQLite\1.0.6083.3, and put a config.ini in parent folder with "1.0.6083.3", then SMath crashes on start.

Next question/feature request: a UI for creating DB and tables. That would be wonderful, esp. for those who don't have other utilities for that. I don't think it's best done with custom functions, rather some dialog UI would be perfect... or a menu item that starts a free external utility for that, that is distributed in the directory with the plugin. Well, I propose dialog UI just because I don't see a good way to do it via functions - but I may well be wrong...

Also:
gives me an error "The cast is illegal" (translated from Russian - specific wording may differ).
works OK

I suppose it may have something to do with decimal separator? Or a problem when returning an empty value?

Also2:

Edited by user 28 August 2016 09:48:11(UTC)  | Reason: Not specified

Great! Thank you for your effort!
Will test ASAP.

Is isolated SQLiteQuery(sql) still available (as updated top post suggests)? (I doubt it's useful. A variable with DB path is short enough, and won't allow for difficult-to-find errors where a user inserts a SQLite query to another DB before the simplified query, and will see strange results).



Also: an idea how to return NULLS: you may return instead SMath custom unit 'NULL. You don't have to define units beforehand; it won't cause "undefined" errors, and will allow testing for "if return='NULL then ...". Also it will help differentiate between returned real empty strings and nulls.

A side question. I see some fancy column names in the sample DB (like "h/tw" ); how do I query for them (aside from using "*" syntax)?

Edited by user 29 August 2016 09:42:16(UTC)  | Reason: Not specified

I think the approach "matrix as result" is good, maybe there is something to improve in the SS logic. If you try to assign matrix(0,n) to a variable, you can show mat(0,n) in a standalone display of the result with numeric evaluation if at definition is stored symbolically, but not if it is stored numerically (that is the same error while you are trying to display the inline result); this affects also rows()/cols()/length() (unable to process the variable when it is stored numerically). I'll talk with Andrey about this.

BTW 'NULL as result is very good idea, +1 for this.

Edited by user 29 August 2016 21:05:51(UTC)  | Reason: Not specified

Davide,
I must yet get used to your new status as SMath co-developer. I haven't yet had a chance to congratulate you with that Thank you for all your past and future contributions! And I will remember to address my questions WRT SMath internals to both of you from now on.

WRT empty matrices. There used to be yet another application for them: using as initializers for loops, to assign an empty n-col 0-row matrix to a variable, and then use stack() uniformly in a loop to fill it (of course, if the number of rows is known in advance, that may be done differeently and more efficient). This functionality was definitely available at some point (unfortunately, I cannot find a link to it atm, because I explained this technique in Russian forum, that is unavailable now).

I have updated the plugin
with the following changes (link in initial post):

• Removed single argument SQLiteQuery due to inconsistencies with partial recalculation.
  
• Null entries in database return custom unit 'NULL and are now distinguishable from empty strings, simplified code.
  
• Fixed and improved the example file
  
• Added typetest table to example database and tested every case successfully

Thanks yet again for all the useful feedback I've received.


I completely oversaw this issue, to preserve consistency and repeatability even with partial recalculation I have removed the single argument version of SQLiteQuery


A very good idea again, implemented.


Either of the following will work:



Thanks for your interest Jason. I'd quite happily convert that spreadsheet into an SQLite database if it helps your endeavours

Edited by user 31 August 2016 14:57:22(UTC)  | Reason: Not specified

I appreciate the offer, but it looks like you already have. I downloaded your extension and example, and the DB Browser app you linked to, and used it to view the "AISC Shape Lookup.db" file from your example. That is the dataset that I downloaded from the AISC websitein an Excel spreadsheet. The only difference I see so far is that the last columns, PA and PB, are identical in your db file. I've never had a need for that data, but looking in my spreadsheet, they are different, and AISC's readme says that PB is the shape perimeter used in Design Guide 19 for fire resistance, while PA is the shape perimeter minus one flange surface. Minor detail,and won't affect anything I do, but I thought I should let you know.

This really opens up some exciting possibilities for me on some of my worksheets once I learn how to use this database stuff Thanks again!
Jason