PrimalSQL 2012

Post feature requests, product enhancement ideas, and other product-specific suggestions here. Do not post bug reports.
Forum rules
Do not post any licensing information in this forum.
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

PrimalSQL 2012

Post by Bosparan »

Hello,

I recently started testing PrimalSQL as a way to build SQL commands. Now there are some aspects I believe deserve some optimization:

Report Query execution time
How am I going to optimize query performance if the tool isn't telling me how long it takes to execute a query and receive the result? Since query optimization is one of my major tasks in SQL, that'd be fairly important for me.

Primal Sense for fields
While in a query, I'd appreciate Primal Sense for field-names from tables listed in the 'FROM' section of the query.

Execute marked query
I like to piece together and assemble multiple queries in a single file (before storing the finished product properly). PrimalSQL however will only allow me to execute all queries in the file at once. A function to only execute the one where the cursor is in or only those that have been marked would be much appreciated.

Indent Automation during editing
A finished query of mine looks similar to this example:

Code: Select all

SELECT
	*
FROM
	example;
Now what I'd really like would be, that after doing a SELECT, when I press enter, it automatically adds the usual TAB indent, instead of me having to enter it manually. Then, after I add the "*", if I don't add a "," it will undo the indent, as that means the next part of the statement is due. This will also immediately tell me if I forgot a comma, instead of having to search for the problem manually when execution fails.

Displaying the Log
The log is by default nigh illegible, compressed in the lower left corner es it is. I can move it elsewhere if I want, but what I'd actually like is the option to add it as a tab to the result-list, which is currently impossible. The only way to display it horizontally - which is helpful to read the reports without massive scrolling - is at the direct expense of either editor or Results area.

Syntax Validation
It would be great if the editor could validate a query as I write it and highlight any errors.

Database browser context menu
I'd love to have additional options in the Database browser context menus when right-clicking on a view or table, particularly "drop" and "truncate" would be great to make testing creating or filling queries faster. That way I wouldn't have to write a query to do it for me for every table or view I'm creating / filling (and store it in a separate script file right now, see above suggestion).

Auto-Complete and writing conventions
I really like auto-complete. In PowerShell Studio 2012. NOT however in PrimalSQL 2012. It is actually not that I would dislike it in the usual way of things, my problem stems from the fact that it violates my writing conventions, and that aggressively so. My usual convention is to write command words all upper-case and variables, names, etc all lowercase. Makes for easy reading, since the naming conventions for commands aren't as ... distinct as in PowerShell.
However, all auto-completed commands I have yet encountered - thankfully only a select few so far - insist on being lowercase. Now you'll likely argue in favor of disabling auto-complete and enjoy scripting SQL without (which would be OK with me) ... but that doesn't work out as well as I'd like: Once I finish typing SELECT, the drop-down menu will mark its own select so when I press enter next - which in my usual workflow I ought to - it will turn my perfectly nice SELECT into a perfectly nice but non-conformist select. I can prevent this from happening by pressing ESC or CTRL-Z, but I really would prefer not to have to mind it. Breaks concentration and I still mess up occasionally, my habit simply being that strong.
So can I get an option to either turn commands into upper-case versions instead (perfect) or just into stopping messing with me (Well enough)?

Fixing TAB-based snippet insertion
Please not that this is with auto-complete already disabled. Let's assume I type the name of a field while creating a table and name that field "status" (one of the default names for table-fields in our database conventions). It then proposes in the usual drop-down menu what commands, names or snippets I might have been meaning to type, but - as I disabled auto-complete - does not select any one option (just that slight frame around what it would have highlighted had I not disabled autocomplete). That frame in this case would be around the default snippet insert. Now I press tab, as all CREATE TABLE commands Must Be Properly Readable, which means I create a nice table of fields and property types. PrimalSQL 2012 however intelligently detects my desire to have the select snippet inserted and decides to fullfil this desire. Yay.

Primal Sense for data types
it would be nice-to-have Primal Sense detect the declaration of a CREATE TABLE statement and provide suggestions for datatypes and secondary attributes (like NOT NULL) where appropriate.

Manually sortable result tables
I'd love to be able to sort the result tables by any column (and without reloading a query, as some of them usually take some time to complete). This would help a lot in verifying a query result.

Marking braces
I don't know whether I translated the term correctly, anyway, "(", "{", "}" and ")" are fairly useful for structuring things (and often their use is mandatory). Given what a complex statement can thus look, I' dreally appreciate to have the same functionality as in PowerShell Studio, where selecting an opening brace will cause the corresponding closing brace to be highlighted.

Cheers,
Fred

P.s.: Yes, I know, I've assembled quite a list so far. I'm a user, so I am greedy ;)
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

PrimalSQL 2012 continued

Post by Bosparan »

can't add things to the original post any longer, so I'll just add another post with my next wishes :)

Search Database structure
I'd love to be able to search a database structure for tables or fields. Imagine a big, un- or underdocumented database - no need to pick one you created yourself, just any one of the legions of such databases out there - and you get to try to find a certain piece of intel out of it. A search algorithm would be great, don't you think?
Maybe even a counting function ...

Edit Database structure
Imagine being able to create a new table, just by right-clicking on the Tables-Folder, clicking "Create Table", dragging and naming the fields in a comfortable UI and then clicking on the "DoIt" button.
I might get spoiled if I get that feature ;)
Or how about renaming tables? Dropping them? Truncating them? Renaming columns might come in handy as well (or changing the data-type).

Events
PrimalSQL does not show the events of a database (unless you run a SHOW EVENTS command against it, of course). Adding those to the Database Browser would be helpful to me.

Complex Parameter multi-table search
Something a bit more esoteric ...
I'd love to be able to do a complex, parameter-based search accross multiple tables. Lets say I have Database db_example, what I'd like to be able to do is to define one or more names for columns and search conditions for values in those columns. PrimalSQL then goes through each and every table, checks whether it has one or more column that matches the column-names given to the search. Then it snatches up any and all lines whose value at those columns match the search criteria and lists them all as a result.
That would be rather handy when exploring a database and trying to track down all references to a particular value.

Partial refresh of database
If possible, it would be great to be able to refresh only a segment of the database in the database browser. For example, if I have a database with 5 views and 500 tables and I want to refresh the displayed views ... well, let's just say I'll have enough time to fetch a cup of coffee and chat a bit with that unattached hot blonde in the security department. While I'm quite partial to chats with interesting females, I'd also like to be able to refresh the views only.
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: PrimalSQL 2012

Post by davidc »

Thank you for all the suggestions! We really appreciate user feedback.

Get field PrimalSense you can use [TableName]. and it will list the fields for that table. I know your suggestion would like the PrimalSense to determine the table from the statement.


As for the keyword casing, they are derived from the snippets. Simply rename the shortcut using the Upper case and it will resolve your issue.


Thank you once again. I will be sure to forward the suggestions to the team and add it to the wish list.

David

PS. Tell the blonde SAPIEN says hello next time you chat ;)
David
SAPIEN Technologies, Inc.
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

Re: PrimalSQL 2012

Post by Bosparan »

Hi David,

glad you like receiving feedback, cause frankly, I'm not done yet ;)

Commenting on Comments
Writing [tablename].[field] to get Primal Sense for fields works of course. Given our naming conventions however I already know what the fields are named and the tables have longer names than fields. Well, not like it's a major problem or anything.
On the same note: I find it slightly ironic that when I type DROP EVENT PrimalSense proposes tables ^^

Renaming Snippets was not quite as simple a thing, since you store them in the ProgramData folder, not profiles (so users can share snippets, I'd assume). Now it works, I'm not really happy about it (naming conventions a fairly draconian here ... and they apply to Snippets as well).

I have a dream ...
... that one day, after finally finishing up a development project, and I look in utter exhaustion into PrimalSQL, there is this button. This very special button. That one button, that scans the entire database and creates an SQL script file that - in the correct order - creates the entire Database structure - tables, events, procedures and views. Awesome dream, ain't it?

Growing Senile
Can it be that PrimalSQL is growing senile? It keeps forgetting what it worked on last time I ran it. I really am missing the option I have in PowerShell Studio to reopen the files that were open the last time the program was closed. I know, when I klick on "File" I get a list of recently opened files, but those have a habit of not working upon occasion (My script files of all kinds reside on a mapped network share).

Cheers,
Fred

P.s.: Blondie sends thanks for the hello, even though she's a PowerGUI gal :roll:
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: PrimalSQL 2012

Post by davidc »

Thank you once again, we will try to incorporate the requests. Maybe we can find a cure for the senility.

As for the blond, there is no accounting for taste :) (Just kidding) To each their own; whatever works best for her.

David
David
SAPIEN Technologies, Inc.
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

Re: PrimalSQL 2012

Post by Bosparan »

Hello David,

another day in SQL county, with another set of esoteric trouble I encountered and wishes I developed:

Numbered lines
I'd like to have my lines properly numbered. Your log is returning errors with the lines where they may have occured, but PrimalSQL does not show me the numbers, so I have to count them manually.
Not funny in a 200 lines procedure.

Procedure log-output
Running a procedure in PrimalSQL will - of course - output some lines in the log. However, it will only do so for the final SQL command executed within the procedure. It will also remain completely silent, not even reporting the final command, if any other commands have been executed in the script.

Selecting failed DateTime
I had a bug in my SQL (not unusual, as you'll likely have assumed) where I was (without noticing it) filling a date-time column with zero-dates ("00.00.0000 00:00:00"). Selecting that column would cause PrimalSQL to throw an error for failing to convert it to System.DateTime. This meant I had to unearth my Old Hated Nemesis (aka MySQL Workbench, destroyer of SQL scripts, consumer of computing resources) and enter it there, just so I could trouble-shoot the problem.

Logging
If possible, I'd like to be able to write in the log (to mark specific entries). Also be able to actually clear all log-entries.
Then there's the unspecific nature of the output information. Execute 5 commands? Sure, works, but it will only return the aggregated count of affected rows. How about a Per-Command output option?
How about naming commands and logging that name? (Like immediately before a command a specifically formatted comment. /*Name: Command 1*/ or something like that.
And a bit less of an anemic output for creating, dropping and truncatin things might be a good idea...

An attentive Primal Sense
Whenever I successfully create a new table / event / procedure / view I first have to refresh the Database Browser before Primal Sense will know it exists. Why? I just created it using PrimalSQL, didn't I?
This wouldn't bother me all that much if a DB-Browser refresh would complete within a short time. However that takes a few minutes.

Collapsible commands
I can collapse functions in PowerShell Studio, so i only see the first line. Could I get the same function in PrimalSQL for commands?

Comment-Based Regions
Once again a feature from PowerShell Studio that I would like to have: The ability to segment script-code into (collapsible) regions.

Events-, Views- and Procedure-Editor
Imagine being able to directly access and edit the logical components within a database, without having to rewrite the entire creation and alteration codes yourself.
Just open up a procedure, change the select and wrap it up again.
Or validate it for dependencies on database-tables. Or recreate it, based on changes to the system.

Cheers,
Fred
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

Re: PrimalSQL 2012

Post by Bosparan »

Hello,

and another ... no. Actually, this is just a bug report.

PrimalSQL did not recognize an error for some SQL statements against my test MySQL server. "Not recognize" as in "did not realize the server threw an error and terminated the command execution". This is a bit troublesome, as PrimalSQL will then lockout the execute query button, as it thinks the command is still running, while the Stop Query button will not work, as the SQL server has already finished the command as far as it is concerned and disregards the interrupt command.

This means I get to save my script files, close PrimalSQL, restart it and again load all script files manually, since my version is still a bit senile ;)
The offending pieces of code that produce this reproducable error are all heavy on logic elements (type conversions, arithmetics, ...).

Cheers,
Fred

PS: Here's a code example:

Code: Select all

SELECT
	id,
	erstelltDatum,
	firma,
	ueberschrift,
	status,
	faellig,
	anzahlMitarbeiter,
	anzahlLeistungen,
	warteRate,
	leistungsMinuten,
	CONCAT(CAST((leistungsMinuten / 60) AS UNSIGNED INTEGER), 'h ', (leistungsMinuten - (CAST((leistungsMinuten / 60) AS UNSIGNED INTEGER) * 60)), 'm') AS leistungsStunden,
	reaktionszeit,
	totalScore,
	letzteAktion
FROM
	view_dashboard_display_alltickets;
This will reliably produce this error when ran against MySQL in the MySQL's own Workbench:

Code: Select all

Error Code: 1690 BIGINT UNSIGNED value is out of range in '(`tansstest`.`dashboard_maintable`.`leistungsMinuten` - (cast((`tansstest`.`dashboard_maintable`.`leistungsMinuten` / 60) as unsigned) * 60))'
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: PrimalSQL 2012

Post by davidc »

If possible, can you generate a script to create a simple table that will result in this error, so that we can test this on our end?

If you can, please email the script to support@sapien.com.

Thank you,

David
David
SAPIEN Technologies, Inc.
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: PrimalSQL 2012

Post by davidc »

I ran the test script and when I run the following query:

Code: Select all

SELECT
	*,
	CONCAT(CAST((totalScore / 60) AS UNSIGNED INTEGER), 'h ', (totalScore - (CAST((totalScore / 60) AS UNSIGNED INTEGER) * 60)), 'm') AS test
FROM
	view_testBug;
Get the following error in PrimalSQL:

Code: Select all

>> Run Query
BIGINT UNSIGNED value is out of range in '(`test`.`testbug`.`totalScore` - (cast((`test`.`testbug`.`totalScore` / 60) as unsigned) * 60))'
David
David
SAPIEN Technologies, Inc.
Bosparan
Posts: 290
Last visit: Fri Oct 08, 2021 11:59 am

Re: PrimalSQL 2012

Post by Bosparan »

Hello David,

that is a bit odd.
Running it on my machine (a Win7x64 machine, the SQL server runs on a Server 2008 R2) will reproduce the situation without fault:
A never ending, almost uninterruptible query (selecting another connection, trying to close PrimalSQL and klicking on restore application will, after all, restore functionality).

When I run it on the MySQL Workstation, I get the same error you did when running it with PrimalSQL. And get that error within a fraction of a second.

In case it matters:
The Host containing MySQL is a Server 2008 R2 based SBS 2011 server, with 2 Processor Cores (1% load, so idling quite a bit) and 6GB memory (90+% load, so not idling all that much). The system is set up on a VMware ESX host. Since that's the case, I can't try running it locally, to see whether that affects matters.
Oh yes, it shouldn't matter, but just in case: The querying Machine and the MySQL hosting server are in different domains with no trust established (The SBS is part of the lab environment).

Cheers,
Fred
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked