Today we are happy to announce that Vertabelo has a new feature we've been working on for some time – views. Now you can easily add them to the diagram, change their definition and other properties and have them generated in your SQL scripts.
Quick overview of views
Let's take a look at how simple it is to create a new view in our editor.
Our goal is to make a view which joins three tables as follows:
Step 1: Create an empty view
To start off, we need to add a new view to our model. Just select the proper tool from the toolbox and click anywhere on the diagram.
What we get is this:
Step 2: Provide a view's definition
Now, we need to go to the right panel and edit the view's basic properties – a name and a SQL query definition:
Now your view will be successfully generated in create/drop SQL scripts. This is OK, but we cannot yet see its columns on the diagram... You have two ways to add them: you can do it manually or you can let our analyzer do that job for you. In order to use the analyzer, click the “Update columns” button. You'll be asked to accept proposed changes:
Ensure that the display is correct and click the “Update columns” button. Here's what you get:
But wait... we forgot about one column – contract.value! Let's add it.
Step 3: Make changes
What we have to do is to change the view's definition (SQL query) and then update the columns visible on the diagram. After we click the “Update columns” button we'll see changes:
Just accept changes and it's done!
If you don't see changes on the diagram, make sure your view's height is big enough to show the column we just added. You can also do it automatically by selecting the “Adjust to text” option from the toolbar:
More Advanced Features
Similar to tables, you can add additional SQL scripts that will be generated before or after a view's definition. You may also set some additional properties which are dependent on the database engine of your model.
One section of properties may require a little more explanation - Dependencies. If one of your views references another view, they must be generated in SQL in the correct order. In such case, you should add dependencies to the dependent view or your SQL may be invalid. All dependencies are visible directly on the diagram:
Notice that this refers only to views, as table definitions are generated before views.
Well, that's it. If you have any questions, problems or ideas, feel free to drop us a line!