ApexSQL Edit

Well, I'm in the process of putting some finishing touches on v2.16 before I release it this week (finally) and thought I would post an image of another product I'm in the process of working on. This new product I hope to make into a modeling tool specific to SQL Server with features to help the SQL Server Developer and DBA create and manage database models.

Notice how in the Orders table I have turned on additional viewing options so I can see the defaults, rules, check constraints, indexes, and permissions assigned to the table. My goal for the product is to provide a simple and powerful interface for managing/explorer schemas. For example, the product will provide 2 ways to create a new table, simple and detailed. Detailed will work much like other products. Simple will allow the user to simply enter the field names and the app will create the table using the data dictionary to determine types, sizes, nullability, and even FK constraints so that a complete table can be created quickly and accurately without having to specify the settings for every single column.

The product is still in early alpha stages but a lot of the drawing routines have been meshed out. Below is an image of the current schema view of the product. Still have a ways to go, but be on the lookout for this new product which I hope to have available sometime towards the release of SQL 2005.

I finally added a feature I have had on my personal want list for some time but never got around to it. While I was testing some SCC stuff this weekend and was waiting for it to finish I decided to spec out the feature and was able to get it into the upcoming v2.16 release.

For those of you who currently use the "standard" join syntax of "table join table on", you're life just got much easier. For those of you who still use the legacy join syntax (yes, that's what it's referred to and I do know some of you :), of "from table, table where table.col = table.col", you no longer have any excuse.

With v2.16, after you type "join", the Intellilist that is displayed will contain some new items when there are FK constraints that reference any table in the current query. Lookin at the Northwind database, if I type:

"select * from Orders inner join"

The list that is displayed now has some new entries at the top immediately following any schema:

- Join Customers (FK_Order_Customers)
- Join Employees (FK_Order_Employees)
- Join Order Details (FK_Order_Details_Order)
- Join Shippers (FK_Order_Shippers)

Notice that the options include both FKs that the current table has (Customer, Employee, Shipper) plus tables that reference the current table of Orders (Order Details). Picking any of these options will insert not only the specified table but the join condition as well (including any multi-col join conditions).

So, picking the - Join Customers ... option would then make my query look as follows:

select * from Ordeers inner join Customers on Customers.CustomerID = Orders.CustomerID

Do another inner join and you will get even more options, with the joins from Customer now included in the list. The joins are displayed from the most recent table to the least recent, so joins to Customer would be at the top and joins to Orders would be below these and so forth.

What about aliases, eh. Well, if you defined the table with an alias then the join condition will use that alias. Also, if you have predefined aliases for objects, then the join table and condition inserted will use those predefined aliases as well.

In my opinion, this is the best thing since the soup and sandwich combo at Panera Bread (I'm on my way there shortly :)

We will also be providing the ability to define custom joins in a future release so that the user can manually define join conditions that might exist outside of the normal foreign key relationships defined in the database.

The inserted tables will also follow the "Insert Object Owner" setting and insert the table schema for the new table if turned on.

Well, we are almost done with the v2.16 release. We still have a few small items to finish development on dealing with some task issues as well as the new object mapping management screen, but we are hoping to release v2.16 very shortly.

This has been one of our longest releases, dealing with the complexities of supporting SCC and the amount of testing required to handle SCC as well as to make sure that Source Safe still works using the VSS API. We did add a new feature over the last couple of days to help make working with parameters a little easier. When the user hovers over a parameter/variable in the editor script, the editor will attempt to show the user the parameter/variable and its declared data type. If the item is a parameter and has an optional value, then that information should also be shown to the user as well.

After we release the v2.16 release, we hope to get back to having releases out every 5-6 weeks or so. We have backlogged a lot of new features we would like to add for the next release so stay tuned.

Thanks,

The ApexSQL Edit Team

This is a screenshot of the new mapping mgmt screen which will allow mapping operations to be performed using drag and drop of files and objects onto each other.

This example shows a database on the left and some Source Controlled items on the right. You can see how the form shows the linkings between the various database objects and their corresponding Source Controlled items (in this case a Source Gear Vault system being accessed through SCC).

Well, the SCC integration is almost complete and ready for testing by some users who have volunteered to test the SCC against a variety of systems.

My initial tests seem to show that it should work fairly well although with SCC being a somewhat limiting API, all of the features currently in Edit for SrcSafe will not be available with SCC. For example, the user will not be able to navigate the entire SC explorer as they can with SrcSafe but will be able to navigate at a project level. We are working to make it easy to switch between SCC projects and even have "saved" SCC projects so that the user can switch between them easily.

Some other differences will be in things like the history of files. Edit shows a custom history form where SCC shows the history form from the SCC system (if available and supported by a vendor's SCC implementation).

Once SCC is complete and out to testers we will begin on the object mapping mgmt screen and hope to have it complete sometime late next week. At that point we will begin wrapping up some items, adding any last minutes fixes and hopefully prepare for the release which should be the last week of March.

This has been the longest amount of time between releases, mainly due to the complexity of adding in the SCC integration. Future release should go back to 4-5 week range instead of the almost 7 weeks that this release has taken. In the future we may concentrate on getting out releases quicker with smaller number of items so possible 1-2 major enhancements and other smaller issues per release so that we can get changes out to the community quicker since this release has many items that would improve the current use by all users but is in a state where it cannot be released due to SCC and object mapping in a state of flux.

Well, SCC integration is coming along. I've been able to check out, check in, undo check out, and add items in the schema mapping. I still have a ways to go like mapping a database, handling the source control explorer, etc. Hope to have all of those done over the weekend so that I can start getting some SCC users to test the basic functionality early next week. The process is going a little slower than I had hoped but it is coming along and working very nicely (at least with SourceGear Vault which is the SCC system I'm testing against).

I also finished the Task form and item mods so that when you open the task form now it will immediately search all open windows for tasks and then will thread off looking at procs, functions, and trigger texts in open databases. These will appear in the task window as they are found. Double clicking on a task that is in a database script will now open the script in a new window if the object is not mapped or open the mapped object if it is. I am thinking about looking at mapped object scripts as well for tasks.

I also added the functionality although still need the UI to do custom keywords in the editor. I have added 5 server/database types

UserTableToken
UserViewToken
UserProcedureToken
UserFunctionToken
UserDataTypeToken

and one universal type:

UserCustomToken

That can be modified so that items in these parsed cases will format as defined by the user, so the user can modify formatting color for these items in the editor.

By default, when connecting to a database, the editor will load the basic types for the five main types, so that by default the editor will use tables/views/etc. from the current connection and highlight them. The user can customize these per server/database using a list of specified tokens or a sql script that returns a resultset.

The UserCustomToken can be customized by entering a list of tokens.

This feature will make it so that tables/procedures/etc in the current script are highlighted differently than standard tokens which should make scripts easier to read and items easier to find.

Well, I was finally able to hook a schema object to an SCC based Source Controlled item (in Source Gear Vault). I was able to successfully connect to the SCC system from Edit and also get latest and check out the file. I was not however able to check it back in since something is not wired correctly on that piece, but hey, who needs to check things in :)

Hopefully by early next week we should have the SCC functionality integrated and can begin full testing and possible involve others in the user community who use various SCC enabled systems since we are only testing using Source Gear Vault. We are currently working on opening pre-mapped objects so currently we have to add the mapping entry manually. For those that will be using the SCC object mapping, it will behave slightly different when specifying linkages between the database and SCC items since SCC does not allow the full tree navigation of all projects that Source Safe API does. This means that different "folders" or "projects" will maintain separate entries and may require a login when switching between projects. We are trying to minimize the logins required to the SCC system but some systems will require the SCC user to login more than others. This is not something related to Edit but related to SCC and the specific Source Control system.

We are looking forward to getting SCC out there for users to begin using and also the new object mapping interface to make the process of Database and Source Control integration easier and more powerful. Please continue to check the blog for additional information that we will be posting about the SCC integration.

For v2.16, I've modified the way Tasks work to make it more useful and provide more power in this feature. In addition to only having TODO tasks, you can now how TODO, BUG, and REMARK tasks. Additionally, you can create your own Task types and use them in the editor to markup your own specific tasks.

In addition to different types of tasks, you can now assign a user and status to a task and have that information displayed in the task view screen. So, the following task would identify itself for the user "Ashton" and having a status of "Open":

-- BUG{User:Ashton;Status:Open}: This item needs to be fixed.

Too much to type you say, well, you can now double click on the task item and a task edit form will display with the current task information and allow the user to edit it and have the task information change. Status and Task types can be selected from predefined lists. A status list is provided and can be modified to include other statuses.

All of this information becomes useful when you view the tasks using the Task viewer. On this form you can view all of the tasks for open files and then group by task type, user, status, etc. In addition, after the form opens, it will also look at any editor windows that have connections and look for task items in the text of system objects for these connections. These items will also appear in the task list and double clicking on these items will open the script from the database for the user to view/edit.

The improvements to tasks should make them more useful in managing tasks, bugs, etc in SQL scripts.

Well, I was able to implement custom tooltips when you hover over items in the editor. This should provide benefit to let users see info about a table/view/function/proc while in the editor. One of the nicer things I've found in testing is that, especially with table values functions, it will show not only the parameters, but the columns in the table that is returned.

Below is the tip from the Customers table, notice how it also incorporates MS_Description properties, if defined, for the table and columns:



Also, here is a function with a parameter and some columns:



I will probably add @parms for the v2.16 release. I may try to columns at a later date because to do columns properly, we'll have to do more parsing to try and determine ownership of the column.

If you have comments about what you see in this post, please post in the forums under BLOG:TOOLTIP so that others will know what the post is in reference to.

Also, in the shots, you might notice the new document tab list at top. This piece of the app was changed since the new control fixes the flicker when switching documents and provides the ability to re-arrange tabs as well as smart ctrl+tab tab switching.

Thanks

Just a short tidbit from work last night.

I finally switched Edit over to a different document tab control that seems to be much improved. It has the following improvements from the previous:

1. It looks better :) It has the same look and feel as the rest of the toolbar and menu items.
2. Allows for tab reordering. You can now drag tabs into different positions within the tab list.
3. No flicker when clicking. Now when you click a tab there will not be any of the current flicker where windows min/max or whatever it is that they do currently.
4. Intelligent Ctrl+Tab switching. Ctrl+Tab will now switch documents more like VS in that it Ctrl+Tab will go back to last document and not just cycle through documents. We try to see if this can be user customizable so that it can be turned off and have Ctrl+Tab work like in VS (which will be the default)

Kind of a minor change, but should reduce the current annoyance that is caused when you click on a tab to switch documents along with adding the additional features.

There is currently a feature in Edit that most people are unaware of (I'm sure it's my fault :)

In an Edit window type the following:

-- TODO: My first TODO item

Now, press Ctrl+F2. A task list window will appear and will list the TODO item that you just added to your file. If the file you added it to has a filename, then that filename will appear. If there is no filename, then it will indicate that. In () the line number of the TODO item will appear. You can jump to that line number in that file/window by double clicking on the TODO item in the list. The active window should switch to the current window and the cursor should be on the line number that was specifed for the TODO item.

The task list will currently display TODO items for any open query windows. We would like to expand this feature in several ways. We would like to give the task list the ability to look in the database itself and find any procs, functions, triggers that have TODO items listed in them and allow the user to double click on these and have them opened. If the item is object mapped then it would open the mapped file and not the database item instead so the user could work on the source controlled version.

We also thought about making the TODO line non-editable by users and having the TODO process managed by the app. This would allow the user to add more TODO info such as user, status, etc., and allow the user to change the status of TODO items, add additional info, assign to users, etc., such that when viewing the task list, users could view TODO items specific to them, of a specific status, etc.

Try out the TODO feature now and see if you think it is useful in its current state and if it would be useful with the changes listed above or if you have changes you think that would make it even more useful.

Please post forum topics under the subject "BLOG:TODO Tasks".

Thanks

The last several days development had mainly been on small items such as small fixes and items posted by users. We have added the following new items so far this week:

1. Ability to script constraints
2. AutoReplacements that span multiple lines will now be inserted at current indentation level.
3. UDFs that return tables will now provide Intellisense for the columns, ie. select * from dbo.udfThatReturnsTable() f where f.[Column List should appear here]
4. Fixed some issues with the syntax generated when executing a sproc to make it include named parameters.
5. Included OUTPUT for out parms when they are inserted by intellisense
6. Added ability to have lists and data lists in SQL templates.
7. A content divider will now appear under "go" to provide visual separation of batches. (Default is ON, can be turned OFF)

Also fixed an issue where intellisense would replace what user typed with closest matched item, some other fixes as well.

We did change the visual display such that the document list now takes up the entire width of the app and works slightly different. Instead of the <> to loop through tabs, it now has a drop down when there are more tabs, much like what you get when there are more toolbar options to display. The reason for this move is to first of all to go with a single control suite for menuing and toolbars and also this new method fixes the problem that occurs currently when clicking on a toolbar item and having the windows do whatever it is they do where they go to normal size then re-maximize. This should make the windows flicker-less when switching by clicking on a doc tab.

We are also looking at possible providing tooltip items when a user hovers over text in the editor. So, when hovering over a table or column, a tooltip item will be presented that might contain some information. For a column, we thought about maybe having datatype (although datatype would most likely come from looking up column in database and not specific to the current context. Not sure how useful this would be since it could br wrong but would take too much time to parse and determine what table column came from, maybe).

Not sure what info would be helpful for stored procs or tables or functions. Maybe list of params, MS_Description property or something else? If you have ideas, please post them on the forums with the subject NEW FEATURE:Tooltips so other readers of the blog will see the post and we can attempt to keep all posts to this together.

I have also done some more testing with my SCC interface and it looks like it is about ready to be inserted. I plan to test some object mappings against an SCC system tonight using the interface. This will involve manually changing the object mapping file to include info specific to SCC integration. If this works we will go about fully integrating SCC into Edit. Once the integration is finished we will begin with the Object Mapping Mgmt screen to manage the object mapping process.

That's all for developmkent today, please post on the forums if you have questions or issues.

Thanks

Using ApexSQL Edit, some of the things you can do to make your life less cluttered is to use the features of the application to hide information not relevant to what you are working on.

Edit provides several means to help clean up the clutter. One feature Edit provides is the ability to completely hide a database from the schema listing. How many of us have ever opened up model ot tempb to see what's in there. Basically these databases (and most likely master and msdb) just sit on the list of databases and take up room. Using Edit, you can right click on a database to bring up its context menu and pick the "Hide Database" option to remove the database from the listing. You can hide as many databases as you choose to. To make the hidden databases visible again you can right click on the server and choose the "Show All Databases" option.

Speaking of right clicking on the Server node, did you know you can print the schema treeview to printer. Simply right click on the server node and choose either print or print preview to view the treeview (as it currently appears now) and print it.

In addition to hiding databases, you can also add individual items within a database. There is not current UI to handle this, but you can add nodes to the Settings.xml file to have items excluded from the schema explorer.

The setting (you can have any number of excludes) takes a regular expression and will exclude anything that matches the RegEx value. For example, the following would exclude any object that started with sp_ (ie. system sprocs):

sp_\w*

Want to exclude those pesky dt_ tables:

dt_\w*

The items need to go in the Settings.xml file located in the main Edit directory underneath the Settings node, ie:


...

...
PLACE YOUR EXCLUDES HERE
...



The SchemaExcludes are a temporary feature until Edit can provide a full featured filtering system for displaying objects. The system we have planned will provide exclude and include plans with the ability to quickly switch between defined plans so you will be able to create filtering that includes all objects related to specific applications.

If you have a topic you would like to see explained in these Using Edit posts, please let us know through email or on the forums.

Thanks

# posted by Ashton Hobbs @ 10:55 AM 0 comments

Well, had a busy day on the forums today answering user suggestions, issues, questions. I did get a lot of items that should make it in v2.16 which will probably be available 2nd week or so of March.

While I am getting things in order to start on SCC integration next week, I have been working on adding a new Intellisense like feature that I'm calling Value Lists. How this will work is that it will take actual data based off of items provided automatically by Edit or customized by the user and will show data values in an intellisense type method to the user.

As an example, take the Customers table in Northwind. If the user were to type:

select * from customers where customerid =

At the point where the user types the "=", a list would appear will all of the items from the customer table such that it would show something like:

ALFKI - Alfred...
BEANC - ...
etc.

The user could then pick a value from the list or press the ESC key to cancel out of the list like normal.

How this would work would be that it would look at the column prior to the "=" and see that it is CustomerID, it would then look up CustomerID in a list similar to how it works for quick info and if the column was found (along with table/database/server/owner/schema matches) then it would display data from that table. This would also work if the user did the following query:

select * from orders where customerid =

Again, here the user would be shown a list of users for them to pick and insert into the script. The list itself could be turned on/off and the columns for which data values or displayed could be customized as well. This feature should really help when dealing with foreign keys or codes where you try to filter by some code or key that is not obvious or not well know. For example, you may not have a list for a Sex column, but a State column might have:

AK - Alaska
AL - Alabama
AR - Arkansas
AZ - Arizona
...

Another feature we are looking at for v2.16 is the ability to create project workspaces which can contain several types of items such as script files on disk as well as schema objects. The project would provide an easy way to deal with scripts and schema objects so that you could group items into a project metaphor and work on them that way. The project workspace could also have interchangeable connections such that you will be able to pick the connection for the workspace and have that connection applied to all queries. We are hoping that this will make it easier to manage projects that involve many script files but that also use schema objects.

For displaying the project workspace, I am thinking about displaying an additional schema tab with the workspace objects in place of the current server/database/object items. The workspace would then work similar to the current schema in that it would let users group things into folders, right click to open, etc. The list of items we want for project workspaces may be more exhaustive than what we can get into v2.16 but we hope to have an initial stab at it in the v2.16 release.

After a suggestion in the forums, we have changed how we calculate initial result grid sizes. The change should make viewing vertical results more useful. Below is a sample image from running sp_help 'sysobjects' and the resulting view of how results are spaced: