Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

3 Pages123>
Options
Go to last post Go to first unread
Offline ChrisBietz  
#1 Posted : 26 August 2016 03:08:07(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
SQLite interface plugin

SMath Studio compatibility SMath Viewer compatibility mono compatibility Extension page Sources Examples


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:
Code:
SQLiteQuery("path", "query")

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:

Edited by moderator 13 August 2019 15:30:24(UTC)  | Reason: Not specified

thanks 6 users thanked ChrisBietz for this useful post.
on 26/08/2016(UTC),  on 26/08/2016(UTC),  on 26/08/2016(UTC),  on 26/08/2016(UTC),  on 29/08/2016(UTC),  on 23/03/2020(UTC)
Offline mikekaganski  
#2 Posted : 26 August 2016 05:23:50(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
First of all: thank you for sharing with the community!

  1. Could you consider using a identifier (number or string) as a "handle" to open connection? For example,
    Code:
    SQLiteOpen("connection1","path1")="open"
    SQLiteOpen("connection2","path2")="open"
    m:=SQLiteQuery("connection1","query")
    query2:=<some processing involving m>
    SQLiteQuery("connection2",query2)
    <some other processing with both connections 1 and 2>

    Or the SQLiteOpen could return the numeric handle on succcess, or an error with a description on failure.
  2. Quote:
    If no fields are returned by the database engine, the function will return the string "No Results"

    Imo, it would be better to return matrix(0,0) in this case. And please return a matrix in all cases, even on single return, thus eliminating the need for conditionals in the calculations. If one knows that only single return will come, this may be handled by simply using el(result,1).
  3. It is possible to use "=" in SMath strings. You just need to prepate the string elsewhere.


For some reason, SMath Studio 0.98.6081.26558 doesn't start with the plugin, so I cannot test it. Does it handle partial recalculations? Doesn't closing in the end prevent partial recalcs? Is it possible to skip closing the connection altogether and thus skip overhead opening new connection on recalc? I.e. does it internally checks in SQLiteOpen if the connection is already open?

Edited by user 26 August 2016 05:26:42(UTC)  | Reason: Not specified

Best regards,
Mike Kaganski
thanks 2 users thanked mikekaganski for this useful post.
on 26/08/2016(UTC),  on 26/08/2016(UTC)
Offline Jean Giraud  
#3 Posted : 26 August 2016 06:30:17(UTC)
Jean Giraud


Rank: Advanced Member

Groups: Registered
Joined: 04/07/2015(UTC)
Posts: 4,791
Canada

Was thanked: 801 time(s) in 635 post(s)
"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 54 time(s).
Spline Matrix Tutorial.sm (55kb) downloaded 51 time(s).
thanks 2 users thanked Jean Giraud for this useful post.
on 26/08/2016(UTC),  on 26/08/2016(UTC)
Offline mkraska  
#4 Posted : 26 August 2016 09:40:18(UTC)
mkraska


Rank: Advanced Member

Groups: Registered
Joined: 15/04/2012(UTC)
Posts: 1,625
Germany

Was thanked: 909 time(s) in 571 post(s)
Originally Posted by: ChrisBietz Go to Quoted Post
[size=8][b]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 "!=" "a <= b" can be replaced with "(a < b ) or (a is b )"


Actually you can have = in strings. Just use the = symbol from the boolean palette.
Martin Kraska

Pre-configured portable distribution of SMath Studio: https://en.smath.info/wi...th%20with%20Plugins.ashx
thanks 3 users thanked mkraska for this useful post.
on 26/08/2016(UTC),  on 26/08/2016(UTC),  on 26/08/2016(UTC)
Offline ChrisBietz  
#5 Posted : 26 August 2016 22:33:02(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
Thanks to everyone for the helpful replys.

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

Originally Posted by: mikekaganski Go to Quoted Post
Could you consider using a identifier (number or string) as a "handle" to open connection?

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.

Originally Posted by: mikekaganski Go to Quoted Post
Imo, it would be better to return matrix(0,0) in this case. And please return a matrix in all cases, even on single return, thus eliminating the need for conditionals

A very good suggestion, implemented straight away.

Originally Posted by: mikekaganski Go to Quoted Post
It is possible to use "=" in SMath strings.

Originally Posted by: mkraska Go to Quoted Post
Actually you can have = in strings.

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

Originally Posted by: mikekaganski Go to Quoted Post
For some reason, SMath Studio 0.98.6081.26558 doesn't start with the plugin, so I cannot test it. Does it handle partial recalculations? Doesn't closing in the end prevent partial recalcs? Is it possible to skip closing the connection altogether and thus skip overhead opening new connection on recalc? I.e. does it internally checks in SQLiteOpen if the connection is already open?

I'll download 0.98.6081.26558 and try to solve the issue as soon as possible.
Partial recalculation seems to work as far as I can tell.
You are quite right, closing the database connection in the end makes a full recalculation necessary.
Closing the database file is not necessary if the database is only read. At the latest when SMath Studio is closed the database connections are terminated. I am however not sure whether "SQLiteClose" can be safely ignored if write operations to the database have been performed.
Opening a file will not impose additional overhead if the file is already open. The plugin will now recognise this and simply report the handle.

Please let me know if you have further ideas or requests.

Edited by user 26 August 2016 23:02:09(UTC)  | Reason: Not specified

Offline Davide Carpi  
#6 Posted : 27 August 2016 00:34:16(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,297
Man
Italy
Location: Italy

Was thanked: 1164 time(s) in 766 post(s)
Hello ChrisBietz, nice to see such kind of plugin in Smath Good


Originally Posted by: ChrisBietz Go to Quoted Post
You are quite right, closing the database connection in the end makes a full recalculation necessary.
Closing the database file is not necessary if the database is only read. At the latest when SMath Studio is closed the database connections are terminated. I am however not sure whether "SQLiteClose" can be safely ignored if write operations to the database have been performed.


You can always collect any handle opened and close all them internally when SMath closes (Dispose() method) Good
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
thanks 1 user thanked Davide Carpi for this useful post.
on 28/08/2016(UTC)
Offline mikekaganski  
#7 Posted : 27 August 2016 04:05:18(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
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.
Best regards,
Mike Kaganski
thanks 1 user thanked mikekaganski for this useful post.
on 28/08/2016(UTC)
Offline ChrisBietz  
#8 Posted : 28 August 2016 03:22:07(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
Thanks again, Davide Carpi and mikekaganski, for the very helpful suggestions! I will start working on these as soon as I can.


I have updated the plugin
with the following changes (link in initial post):
  • 32 Bit and 64 Bit are now supported

  • The assemblies are now merged, only a single dll file needs to be copied to the plugins folder



Unfortunately I could not reproduce this problem:
Originally Posted by: mikekaganski Go to Quoted Post
For some reason, SMath Studio 0.98.6081.26558 doesn't start with the plugin

I have successfully tested the updated version with SMath Studio versions 0.98.5935, 0.98.6081 and 0.98.6083, both in native 64 bit mode as well as in 32 bit mode using WoW64.

The behaviour of concat seems to be different in 0.98.6081, 0.98.6083 which seems to break the example file (will update soon) for these versions. The plugin itself works for me, however. Any feedback from other users would be much appreciated.

I would be especially grateful if a user with 32 bit system could report whether the plugin works as intended. If this is the case I will upload it to the extension manager as a beta version.
thanks 2 users thanked ChrisBietz for this useful post.
on 28/08/2016(UTC),  on 28/08/2016(UTC)
Offline mikekaganski  
#9 Posted : 28 August 2016 07:17:05(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
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:
Code:
SQLiteQuery(0,"select * from Shapes")
gives me an error "The cast is illegal" (translated from Russian - specific wording may differ).
Code:
SQLiteQuery(0,"select * from Units")
works OK

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

Also2:
Code:
SQLiteQuery(0,"select 'h/tw' from Shapes where AISC_Manual_Label is 'W44X335'")=["TYPE ERROR"]

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

Best regards,
Mike Kaganski
thanks 1 user thanked mikekaganski for this useful post.
on 29/08/2016(UTC)
Offline ChrisBietz  
#10 Posted : 29 August 2016 03:48:35(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
I have updated the plugin
with the following changes (link in initial post):

  • SQLiteOpen is not necessary anymore, the handle in SQLiteQuery is now the filename as suggested by Mike Kaganski

  • SQLiteClose is not necessary anymore. Open database connections are closed on Dispose as suggested by Davide Carpi

  • Fixed illegal cast and type error bugs as kindly reported by by Mike Kaganski (both data type conversion issues)

  • Since deployment as single dll does not seem to be necessary I split the assembly back up to reduce size and increase loading speed

  • Refactoring and minor improvements

  • NULL values are now returned as "" instead of "NULL"



Thanks to Mike Kaganski for testing the plugin so quickly and reporting problems.
Originally Posted by: mikekaganski Go to Quoted Post
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.


I was not aware of the correct way to install plugins before, thanks for this information. The crash can be avoided by using a folder name which is any valid GUID,
e.g: "%appdata%\SMath\extensions\plugins\a79333d4-bf34-4aa6-93e5-5a06b6548d24\1.0.6083.4".
The updated zip file in the initial post now contains this folder. (GUID generated here)

Originally Posted by: mikekaganski Go to Quoted Post
Next question/feature request: a UI for creating DB and tables.

Good idea! I guess convenience is much more important than HD memory usage. As soon as I find out how to add menu items I will bundle the SQLite Plugin with SQLite Studio (GPL)

I will look into points 3 and 4 over the next couple of days but from what I understand SQLite seems to already handle these automatically to some degree. I'm fairly confident that write access queries would be immediately committed as the plugin is currently implemented, but I will double check and confirm this. Another problem that arises from this is insert speed. Since every query individually implicitly creates a transaction, allocates memory, prepares and execures a statement etc. write access is slow at the moment. The use of preprepared statements and transactions could improve this tremendously, but I don't yet see how to integrate this with SMath in a sensible way... will spend some more thought on this.

Edited by user 29 August 2016 03:51:07(UTC)  | Reason: Not specified

thanks 2 users thanked ChrisBietz for this useful post.
on 29/08/2016(UTC),  on 29/08/2016(UTC)
Offline mikekaganski  
#11 Posted : 29 August 2016 03:56:11(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
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).

SQLite01.png

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

Best regards,
Mike Kaganski
thanks 2 users thanked mikekaganski for this useful post.
on 29/08/2016(UTC),  on 31/08/2016(UTC)
Offline mikekaganski  
#12 Posted : 29 August 2016 11:14:06(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
Testing 1.0.6083.4

Previously reported errors are gone, thanks!
But I see a problem with returning empty result sets:

SQLite02.png

Something's wrong with the approach I suggested (using matrix(0,n)). Maybe Andrey could suggest something here?
Best regards,
Mike Kaganski
thanks 1 user thanked mikekaganski for this useful post.
on 31/08/2016(UTC)
Offline Davide Carpi  
#13 Posted : 29 August 2016 20:32:52(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,297
Man
Italy
Location: Italy

Was thanked: 1164 time(s) in 766 post(s)
Originally Posted by: mikekaganski Go to Quoted Post
But I see a problem with returning empty result sets:

SQLite02.png

Something's wrong with the approach I suggested (using matrix(0,n)). Maybe Andrey could suggest something here?


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

If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
thanks 2 users thanked Davide Carpi for this useful post.
on 30/08/2016(UTC),  on 31/08/2016(UTC)
Offline Jason McCool  
#14 Posted : 29 August 2016 22:33:46(UTC)
Jason McCool


Rank: Member

Groups: Registered
Joined: 27/05/2016(UTC)
Posts: 21
Man
United States
Location: Little Rock, Arkansas

Was thanked: 1 time(s) in 1 post(s)
I'm not very familiar with SQL, but this looks like it would be very useful! I've been thinking of how best to handle some worksheets I've been writing that need to lookup a variety of tabular data like AISC steel beam sizes and so on. I'd gotten a start on some worksheets by just using a matrix of manually-entered data for the particular beam size needed, but I didn't want to invest time in one particular data lookup method before figuring out which would be best, as I had seen the Excel plugin shortly after that, and AISC does make their tables available in spreadsheet format. But I also knew that my old company used databases and SQL for pulling info rather than Excel with its overhead. So I'd just been wondering if there were some kind of database functionality available that I could tap into in SMath. Looking forward to seeing your developments on this! Wish I knew enough about this to help with testing like these other guys, but I'll probably be learning as I go once you get this release-ready, so thanks, and good work!

Jason
Jason McCool
Robbins Engineering
Little Rock, AR, USA
Offline mikekaganski  
#15 Posted : 30 August 2016 02:49:26(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
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 Good 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. Good

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).
Best regards,
Mike Kaganski
thanks 1 user thanked mikekaganski for this useful post.
on 30/08/2016(UTC)
Offline Davide Carpi  
#16 Posted : 30 August 2016 17:35:23(UTC)
Davide Carpi


Rank: Advanced Member

Groups: Registered, Advanced Member
Joined: 13/01/2012(UTC)
Posts: 2,297
Man
Italy
Location: Italy

Was thanked: 1164 time(s) in 766 post(s)
Thanks for the congratulations Mike. I'm very grateful to Andrey for this opportunity Friends

I know about this useful feature, that's why I think this particular form should be enforced

BTW the russian side of the forum is still available for reference Good
If you like my plugins please consider a donation to SMath Studio; for personal contributions to me: paypal.me/dcprojects
thanks 1 user thanked Davide Carpi for this useful post.
on 31/08/2016(UTC)
Offline ChrisBietz  
#17 Posted : 31 August 2016 14:49:01(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
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.

Originally Posted by: mikekaganski Go to Quoted Post
SQLite01.png

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

Originally Posted by: mikekaganski Go to Quoted Post
you may return instead SMath custom unit 'NULL.

A very good idea again, implemented.

Originally Posted by: mikekaganski Go to Quoted Post
I see some fancy column names in the sample DB (like "h/tw" ); how do I query for them

Either of the following will work:
Code:
Select `twdet/2` from Shapes;
Select 'twdet/2' from Shapes;


Originally Posted by: Jason McCool Go to Quoted Post
I'm not very familiar with SQL, but this looks like it would be very useful! (...) AISC does make their tables available in spreadsheet format

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

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

thanks 2 users thanked ChrisBietz for this useful post.
on 31/08/2016(UTC),  on 31/08/2016(UTC)
Offline mikekaganski  
#18 Posted : 31 August 2016 15:14:22(UTC)
mikekaganski


Rank: Advanced Member

Groups: Registered
Joined: 17/01/2013(UTC)
Posts: 296
Man
Russian Federation
Location: Khabarovsk, Russia

Was thanked: 151 time(s) in 107 post(s)
Thanks Chris for your great work!

Originally Posted by: ChrisBietz Go to Quoted Post

Mike Kaganski - His quick testing, excellent feedback and good ideas have substancially accellerated and improved the development of this plugin


Oh, thank you, but you know, generating ideas is light years away easier than all that coding heavy-lifting.

Originally Posted by: ChrisBietz Go to Quoted Post

Either of the following will work:
Code:
...
Select 'twdet/2' from Shapes;



Well, this specific second form doesn't work for me; (actually I tried it before) but the first one you've suggested works like a charm! Thanks.
Best regards,
Mike Kaganski
Offline Jason McCool  
#19 Posted : 31 August 2016 16:31:25(UTC)
Jason McCool


Rank: Member

Groups: Registered
Joined: 27/05/2016(UTC)
Posts: 21
Man
United States
Location: Little Rock, Arkansas

Was thanked: 1 time(s) in 1 post(s)
Originally Posted by: ChrisBietz Go to Quoted Post


Originally Posted by: Jason McCool Go to Quoted Post
I'm not very familiar with SQL, but this looks like it would be very useful! (...) AISC does make their tables available in spreadsheet format

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


I appreciate the offer, but it looks like you already have.Yes 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 Yahoo Thanks again!
Jason
Jason McCool
Robbins Engineering
Little Rock, AR, USA
Offline ChrisBietz  
#20 Posted : 31 August 2016 20:14:50(UTC)
ChrisBietz

Rank: Newbie

Groups: Registered
Joined: 23/08/2016(UTC)
Posts: 8
Man
United Kingdom
Location: Wymondham

Was thanked: 16 time(s) in 5 post(s)
I am trying to upload the plugin to the extension manager.
http://smath.info/upload/Extensions.aspx redirects me to http://smath.info/ReleaseManager/ which unfortunately results in a Server Error for me ExtensionManagerError.zip (2kb) downloaded 28 time(s).
Would anyone happen to know how to get around this?
Users browsing this topic
3 Pages123>
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.