Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - BETA versions

Thread: Create Table form is modal


Permlink Replies: 17 - Pages: 2 [ 1 2 | Next ] - Last Post: Nov 30, 2012 9:05 PM Last Post By: Hans Bergsten
Vince Swann

Posts: 169
Registered: 03/27/07
Create Table form is modal
Posted: Jun 2, 2011 2:15 PM
   thread.click_reply Reply
I've just noticed that the Create Table dialog is a modal form, preventing browsing of other objects whilst creating a new table.

This isn't ideal if you want to refer to fields in other tables to determine required field sizes, types, naming conventions, etc.

I may be missing something but I can't see a good reason to prevent browsing of existing objects whilst designing a new one.
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jun 2, 2011 2:36 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
That dialog could also do with being more keyboard-friendly.

If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive.

If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns, not generate an error message. This one is a duplicate issue I've mentioned before but I mention it again as I still find it gets me nearly every time.

If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column.

The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo.

The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 2, 2011 10:20 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

We have discussed making the dialog non-modal and will probably do this, or something else that describes the problem you describe, in a future release along with other usability changes. I have added you vote for it.

Best Regards,
Hans
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 2, 2011 10:47 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive.

ENTER is used for so many things in grid editing that it is hard to also bind it to "Insert New Row", but you can bind something like Ctrl-Enter to this operation in Tool Properties->Key Bindings.

If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns, not generate an error message. This one is a duplicate issue I've mentioned before but I mention it again as I still find it gets me nearly every time.

This is harder than it may seem, because the data type list is retrieved from the database (so we don't have full control over what's in it) and it is read-only, so you can't type "VARCHAR(255)"; making it editable would likely cause other problems. Is it really so much harder to tab to the Size field and add the size there?

If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column.

I'll open a ticket on allowing TAB to be used to select an entry here, since that would make it consistent with other lists, e.g. Auto Completion.

The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo.

It should already work that way. Which database and driver are you using?

The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created.

There are Copy to Editor choices in the SQL Preview area right-click menu. Is that sufficient?

Best Regards,
Hans
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jun 3, 2011 11:29 AM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
 global.attachment temp.png (2.3 KB )
We have discussed making the dialog non-modal and will probably do this, or something else that describes the problem you describe, in a future release along with other usability changes. I have added you vote for it.

Thanks - that'll save me having to use Management Studio or a 2nd copy of DBVis to examine the structure of existing objects whilst designing new ones.

If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive.
ENTER is used for so many things in grid editing that it is hard to also bind it to "Insert New Row", but you can bind something like Ctrl-Enter to this operation in Tool Properties->Key Bindings.

That sounds a good plan - I see it's already also bound to the Insert key, which does seem more intuitive.

If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns
This is harder than it may seem, because the data type list is retrieved from the database (so we don't have full control over what's in it) and it is read-only, so you can't type "VARCHAR(255)"; making it editable would likely cause other problems. Is it really so much harder to tab to the Size field and add the size there?

I can understand the difficulty with this one and I agree that it wouldn't be such a problem once the autocomplete bug is sorted. At present I must type VARCHAR<ENTER><TAB>255, which isn't intuitive.

If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column.
I'll open a ticket on allowing TAB to be used to select an entry here, since that would make it consistent with other lists, e.g. Auto Completion.

Thanks - this would improve the feel of the dialog greatly.

The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo.
It should already work that way. Which database and driver are you using?

I'm accessing Sql Server 2008 via the jTDS driver using windows authentication. I'm configured to use dbo as the default schema. I attach the blank schema dialog I see when I first open the dialog.

The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created.
There are Copy to Editor choices in the SQL Preview area right-click menu. Is that sufficient?

Thanks - that does just what I need. I didn't think to check the right-click menu.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 3, 2011 8:00 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Regarding the default schema not being set in the Schema list, please enable debugging just before you open the Create Table dialog and post/send us the output.

http://www.dbvis.com/products/dbvis/doc/faq/#4.14

Another thing you can try is to run this SELECT statement and tell us what you get:

SELECT
P.DEFAULT_SCHEMA_NAME
FROM
SYS.DATABASE_PRINCIPALS P, SYS.SQL_LOGINS L
WHERE
L.PRINCIPAL_ID = P.PRINCIPAL_ID AND L.NAME = '<userName>'"

where <userName> is replaced with your login account name.

Best Regards,
Hans
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jun 24, 2011 4:58 PM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
The problem it seems is that the query only works for SQL Server logins, not domain authentication.

The query you posted fails to return any rows:
SELECT
P.DEFAULT_SCHEMA_NAME
FROM
SYS.DATABASE_PRINCIPALS P, SYS.SQL_LOGINS L
WHERE
L.PRINCIPAL_ID = P.PRINCIPAL_ID AND L.NAME = '<userName>'

However, this query returns one row with the correct default schema:
SELECT DEFAULT_SCHEMA_NAME
FROM sys.database_principals
WHERE NAME = '<userName>'

I have a debug log as well if you still need it but this looks like the smoking gun to me.

BTW, confirmed still broken in version 8.0.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 27, 2011 10:42 AM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Thanks. Which version of the database are you using?

Best Regards,
Hans
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jun 27, 2011 11:32 AM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
It's SQL Server 2008 set up to allow both SQL logins and windows authentication.

Normally we log in using windows logins but soem of our legacy applications still require SQL logins.

A quick fix to work with both would seem to be to look at P.NAME instead of L.NAME and remove the join to the SQL_LOGINS table entirely.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 27, 2011 9:17 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

I admit I don't have a lot of experience with this, so please correct me if I'm wrong.

In my test database, I also use SQL logins. They are listed in the SQL_LOGINS view with a PRINCIPAL_ID, which is then mapped to a default schema in DATABASE_PRINCIPALS. Hence, the current query works fine in my case.

If I just change to the query you suggest, the configuration I use would fail, because the login name is not among the names listed in DATABASE_PRINCIPALS; that table must be joined via the PRINCIPAL_ID from DATABASE_PRINCIPALS, as it is today.

From reading a bit about this, it seems like SYS_LOGINS should always have an entry for all types of logins, e.g. for both SQL Logins and Windows Logins. Are you saying that is not the case in your configuration?

Best Regards,
Hans
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jun 29, 2011 11:19 AM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
Hi Hans,

I can confirm that DATABASE_PRINCIPALS contains both sql logins and windows logins in my case.

In fact it must also be there in your case too if the full query works as the original query performs an INNER JOIN (although in pre-ansi join syntax) to the DATABASE_PRINCIPALS table.

Hence if no matching row existed in DATABASE_PRINCIPALS, no row would be returned in the resultset even if one existed in SQL_LOGINS.

To return the data from SQL_LOGINS even if DATABASE_PRINCIPALS didn't exist the WHERE clause would have to perform an OUTER JOIN (using *= or =* in the old syntax, which no longer works in SQL Server). Even then though, without a row in DATABASE_PRINCIPALS, the column DEFAULT_SCHEMA_NAME in the resultset would end up being NULL.

I can confirm a row also exists for both types of login in the SYSLOGINS table but as that table doesn't contain the default schema it's not going to solve the original problem.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jun 29, 2011 7:29 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Thanks for the additional info.

I will have a look at this in more detail next week. I'm on the road at the moment and don't have reliable access to my SQL Server test database.

Best Regards,
Hans
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jul 6, 2011 8:45 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

In my config, DATABASE_PRINCIPALS does not contain the SQL user I use to login in the NAME column. Instead, SQL_LOGINS maps the user name (in the NAME column) to a principal ID (in the PRINCIPAL_ID column), which is then mapped to a principal name with a default schema in the DATABASE_PRINCIPALS table via the principal ID. So you're right that the DATABASE_PRINCIPALS contains the SQL logins even in my case, but only the PRINCIPAL_ID for it, not the NAME.

However, I found a standard function that can hopefully be used to solve the problem for both our configurations, namely User_Name(). As far as I can tell, it always returns a value that matches a principal name in DATABASE_PRINCIPALS. We should therefore be able to replace the current query with this one:

SELECT
DEFAULT_SCHEMA_NAME
FROM
SYS.DATABASE_PRINCIPALS
WHERE
NAME = User_Name();

Please verify that it gives the correct result for your configuration. And if anyone sees a problem with it, please let us know.

Best Regards,
Hans
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Create Table form is modal
Posted: Jul 7, 2011 1:16 PM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
Hi Hans,

I can confirm that solution also works fine with my SQL Server 2008 setup.

Thanks for taking the time to find it.
Hans Bergsten


Posts: 1,579
Registered: 10/11/06
Re: Create Table form is modal
Posted: Jul 7, 2011 7:27 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Thanks for confirming. The new query will be used in the next maintenance release.

Best Regards,
Hans

Point your RSS reader here for a feed of the latest messages in all forums