How install sql power architect Ubuntu

install sql power architect ubuntu and install sql power architect linux and sql power architect reverse engineer database sql power architect data dictionary
Dr.MohitBansal Profile Pic
Published Date:26-10-2017
Your Website URL(Optional)
Data Modeling with SQL Power Architect We spent the last few chapters understanding PostgreSQL concepts and architecture and the important ACID properties and how they are managed. In this chapter, we will cover data modeling with SQL Power Architect. Data modeling is a vast topic and involves many steps. It helps in understanding business requirements to design and implement a database to meet the requirements. The process involves, typically, generating conceptual, logical, and physical schemas or data models. Understanding the requirements and translating them to a good database design is critical to the implementation of a secure and scalable system. The data models capture a lot of information, which includes the business entities involved, their attributes, the rules (also known as constraints) applicable to the entities and attributes, the relationships between the entities, and so on. SQL Power Architect is a tool that aids in creating the physical model, generating the SQL, actually a subset of SQL - Data Definition Language (DDL), implementing the models for different types of databases, and also visualizing the data models. Before we cover these features, let's first quickly cover a few tools that are used with PostgreSQL and other databases. Tools for databases and their uses There are many tools that are used with databases, each one with a focus on some capabilities. To interact with a database to execute queries, make changes to the database, observe what is happening in the database, and so on, there are client tools such as psql and pgAdmin (pgAdmin III). These do not have a web interface and need to be installed on the machine from which the database will be accessed. Similar capabilities are provided by phpPgAdmin, which provides a web interface. 91 Data Modeling with SQL Power Architect Then, there are products such as TOra ( The key difference here is that TOra is a tool that works with a few databases, whereas pgAdmin has a focus on PostgreSQL. Some tools focus on database administration activities (taking backups, restoring from backups, monitoring performance, killing sessions, monitoring memory usage, and space utilization). Some others focus on executing queries or scripts, describing tables (mostly developer activity). However, these tools are not helpful when it comes to version control. This is not the problem they are trying to solve. Database version control involves many activities such as: • Merging changes to the database structure when there are multiple developers working on database design • Figuring out the changes made to the database in the latest release • Rolling back the changes if necessary Most database tools are not really focused on version control. One can generate scripts and use a tool (such as SVN) to track the changes. Again, the scripts to roll back changes have to be prepared more or less manually. Liquibase (http://www. is a tool that focuses on version control and should be given serious consideration if we make frequent changes to the database schema. Then, of course, there are the database design tools. So, choosing a tool means answering some questions like: • What is the tool mainly used for (development, administration, version control, or design)? • Do you want a thick client tool to be used by a couple of users, or are you looking for a tool to be used by many people from various locations, and hence prefer a web-based tool? • Will you be working with just one database (PostgreSQL) or multiple databases (PostgreSQL, MySQL, and Oracle)? Of course, the important considerations of cost and licensing cannot be overlooked. 92 Chapter 5 Database design tools Let's see why we need a database design tool. Although the number of tables in your database will definitely depend on the type of product you are working on, it's highly unlikely that there will be a system doing serious work with less than 10-20 tables. By the time we add tables to manage users, roles, and privileges, we will have 4-5 tables. Add a few tables to audit user actions and we will have double digits. Then, of course, there will be tables to support the core business. We might have a system that deals with products, customers, and warehouses. Or, we can design a system that deals with flights, routes, inventory, and fare classes. Also, we can build a Facebook-killer system with friends, connections, blog posts and groups, and so on. In short, we end up with a number of tables (ranging from ten to maybe many hundreds). When writing scripts and keeping all the details about the tables and columns and their attributes in Excel work, and that might be the practice followed by many teams, using a data modeling tool helps a lot in visualizing the maze of tables we end up creating. It's also very helpful if we export or publish our tables and their relationships as images or HTML files and explain them to others using these images. Reverse engineering or generating a data model from an existing database is another useful feature of many database design tools. Although there are many database design tools, many of them work only in a Windows environment. There are other free tools that are free, but not yet in version 1.0. We will use SQL Power Architect available under GPL, which is free and definitely stable. The GPL Community Edition version has almost all the necessary key features (such as data modeling, data profiling, reverse and forward engineering). SQL Power Architect is Java based and runs on Windows, Unix, Linux, and Mac OS platforms. We will cover the most basic steps that are necessary to design a few tables, generate the SQL, and export the model in various formats. We will also cover how to reverse engineer an existing database using the tool. SQL Power Architect – downloading and installing The software can be downloaded at download_os. The downloaded file for the Unix/Generic environment is around 22 MB. Once the file is downloaded, we can extract it with the following command: tar xvf ../Downloads/SQL-Power-Architect-generic-jdbc-1.0.7.tar.gz architect-1.0.7/LICENSE 93 Data Modeling with SQL Power Architect … architect-1.0.7/lib/xercesImpl-2.9.1.jar architect-1.0.7/README.generic Ensure that Java is installed and working on your computer. Then, we can change the directory with thecd command, set a variable, and launch the application: cd architect-1.0.7/ pwd /home/jayadevan/sw/architect-1.0.7 export ARCHITECT_HOME=/home/jayadevan/sw/architect-1.0.7 java -jar ARCHITECT_HOME/architect.jar Now, you see a window with two panes. The database pane will be where we add new connections. The playpen pane is where we can add new objects, create relationships, and so on: In the top-left corner, below the menu icons, we have New Project. Each data model is a separate project. Below this, we have Playpen Database. Playpen is the work area where we can add new objects, manipulate objects, and so on. As of now, we don't have any objects here. 94 Chapter 5 Creating tables Let's create a few tables (a customer table, an account table, and a transaction table). Right-click in the right-hand side pane and click on New Table. Now, give the same logical and physical namecustomer_master, and name the primary key as customer_master_pk. Then, click on Save, choose Project, and enterbanking_system as the project name. Now, add a few columns to the table. Right-click on the table, choose New Column, and entercust_id as the column name. Also, select In Primary Key and bigint as the data type. Select Yes for auto increment. In a similar manner, add a few columns (such asfirst_name andlast_name). These are not primary keys and are of the VARCHAR data type. Add another column calleddob of typedate. You can see that these additions also appear in the left-hand side pane under Playpen. Whether primary keys should be intelligent or a meaningless auto-incrementing integer is a debatable topic. For now, we will focus on the features of the design tool. Let's use an auto-incrementing integer. In a similar manner, add a tablecustomer_accounts withaccount_id as the primary key. We will add a few more columns:cust_id (typebigint),branch_id (typebigint), andaccount_type (VARCHAR).We can keepbranch_id asINT, assuming the number of branches will not cross the limits of integer. Now let's try creating a relationship between the two tables. A simple rule will be that we cannot open an account until we capture the basic information of the customer. This means that everycust_id in thecustomer_accounts table should have an entry in the customer_master table. Click on New Identifying Relationship in the right-hand side pane. Then, click oncustomer_master and then oncustomer_accounts. The columncust_id automatically gets added to the primary key box. We can always go and change this later by editing the column's properties. 95 Data Modeling with SQL Power Architect If we click on Relationship, we can see the properties, as shown in the following screenshot: Now, try adding a new table for transactions. It can have the columns: transaction_id as primary key,account_id linking it to account table, date_of_transaction, amount and type of transaction (debit or credit) as other columns. The ability to add domains and custom data types is available in the Enterprise Edition. 96 Chapter 5 Generating SQL Next, let's try generating SQL for the two tables we just created. Click on Tools and select Forward Engineer. Then, choose Create in the Playpen Database and PostgreSQL as the Database Type. You will also notice the Liquibase option, mentioned earlier as a version control tool for databases. Once you click on OK, you can see the SQL generated for PostgreSQL. The SQL generated will vary depending on the database we choose. The SQL generated for PostgreSQL and Liquibase XML generated is shown in the following screenshot: 97 Data Modeling with SQL Power Architect The XML generated is as follows: Now let's connect to a database to create the tables. Navigate to Connections Add Source Connection New Connection. Fill in the appropriate values and click on Test Connection. If all is well, you should see Connection test successful, as shown in the following screenshot: 98 Chapter 5 Now, we can again use the Forward Engineer option and create the tables in the newly added database. The option of saving the SQL is also there. We can also use the Forward Engineer option to copy the data structure from one database to another. Assuming that the source database is MySQL and target is PostgreSQL, the steps will be as follows: 1. Add a connection for the source database (MySQL). 2. Add a connection for the target database (PostgreSQL). 3. Click on Expand All Children under the source connection in the database pane. 4. Drag and drop the objects you want to migrate in the object browser. 5. Use the Forward Engineer option and choose the PostgreSQL connection. 6. Inspect the script generated and select Execute. 99 Data Modeling with SQL Power Architect 7. We can use the Copy Table Data under Tools to move data from one database to another, as shown here: Reverse engineering and making changes Let's try to reverse engineer from the database in which we created objects just now. Click on File and select New Project. Then, in the left-hand side pane, right-click on Add source connection and choose the connection used in the previous step. Right-click on the connection and choose Expand All Children. Now, you can choose and drag and drop the objects you want in the right-hand side pane. When you do this, you will also see them in the left-hand side pane under Playpen. Now, changes can be made to the objects in the workspace. Once these changes are made, click on Tools to select Compare DM. Choose these options, as shown in the following screenshot. Ensure that public is chosen in the schema option (information_schema can be default), and the output format chosen is PostgreSQL. You get the SQL output, which can be either saved or executed: 100 Chapter 5 Exporting the data model Now, we will see how we can export the data model we created. Click on File and you will see the Export Playpen to PDF and Export as HTML options. Choose Generate HTML report, select Use built-in report format, and choose a folder to save the file. An HTML file with the list of tables, proper links between the parent and child tables, and so on is generated. In Chapter 12, PostgreSQL – Extras, quite a few interesting data types such asRANGE,JSON, andHSTORE, and so on are covered. Go through them before you begin database design. 101 Data Modeling with SQL Power Architect Profiling Yet another useful feature of the tool is profiling. This feature is useful when you want to get an idea about the number of tables, columns, data types, and even data content in these tables. One word of caution: if the number of tables is big and/or the number of records runs into many thousands, the tool might take a long time. As the scanning process results in some load on the database server, it's better to use profiling in a development or staging environment rather than the production environment. The profiling step itself is simple. In the left-hand side pane, right-click and choose Add Source Connection. Next, right-click on the database connection in the left- hand side pane and click on Profile . The output shows a list of tables. Choose the one you are interested in studying further, and click on View selected. This gives a detailed view of the table, columns, and data distribution, as shown in the following screenshot. Note that there is a Graph view, Table view, and Column view with different details. The graph view lets you choose any column and provides the values and their percentages, as shown in the following screenshot. The table values provide information (such as max length, min length, average length, max value, min value, and similar metrics). Here too, we have the option to Export as PDF, Export as HTML, and so on. 102 Chapter 5 Demo videos, some of which are relevant for the Community Edition also, are available at architect-demos. Summary In this chapter, we covered the basic steps to be followed to design tables using SQL Power Architect. We also saw how to make changes and generate SQL to be applied to an existing database to sync it with the data model. Also, we covered the export options available for the data model. We also saw that we can profile an existing database with Power Architect. In the next chapter, we will cover two client tools that can be used to work with a PostgreSQL database: pgAdmin3 and psql. 103 Client Tools In the previous chapter, we looked at a tool that is used to design databases. Now, let's cover a few tools that are used with PostgreSQL to manipulate data, create, drop, and alter objects, find out what is happening on the server, and so on. In this chapter, we will cover one GUI and one command-line tool that are used to work with PostgreSQL. We will see how database connections are made, how SQL statements are executed, and how database objects and related metadata can be viewed. We will also look at a couple of advanced use cases (such as generating the plan for queries and changing configuration parameters). GUI tools and command-line tools psql is probably the most favored client tool to work with PostgreSQL, and pgAdmin is the popular GUI tool. We will cover a couple of basic features of pgAdmin and also have a look at a few not-so-basic features. To cover the tool exhaustively with screenshots, it will consume many pages and probably not add much value. There are other options such as phpPgAdmin and TOra, (toolkit for Oracle, but supports many databases) which you could explore. pgAdmin – downloading and installation The URL provides links to various options to get the tool, including downloading and compiling from source. Compiling from source might not be easy, as there are quite a few dependencies and getting all of them to behave is a bit tough, unless you have been installing quite a bit of Linux- or Unix-based software via the compile-from-source option. For Windows, the tool can be installed using the point and click installer. For Linux-based systems, andhttp://wiki. should provide the setup instructions. 105 Client Tools When you use the commands provided in the links to install pgAdmin, pay attention and ensure that you install only the pgAdmin software. If you just copy/paste the commands, you might end up overwriting your PostgreSQL installation. Adding a server The first thing we want to do once pgAdmin has been installed and started is set up a connection. For this, we use the Add Server option. Click on File and choose Add Server. We get a dialog box with various options, as shown in the following screenshot: 106 Chapter 6 Let's look at these options in detail: • Name: This can be anything that we want to use for reference. It could be MyProductionServer1. It's just an identifier to help us figure out which server we are looking at, if we work with many servers. • Host: This is the IP address of the machine where our cluster is running. It can also be the fully qualified hostname. • Port: This is the port where the cluster listens for requests. • Maintenance DB: This is used to specify the initial database that pgAdmin connects to. It's the database where pgAdmin will check for optional but useful modules and extensions (such aspgAgent andadminpack). • Service: This refers to the name of a service configured in thepg_service. conf file. We will not be using it. Detailed documentation about the file is available at libpq-pgservice.html. • Username: This is the user that you will be connecting as. We could choose to save the password. If we do this, the password will be stored in a password file named.pgpass in thehome directory if we are working in a Unix/Linux environment. In Windows, the password file will be under %APPDATA%\postgresql\, and the file will be namedpgpass.conf. We can use the option under the File menu to edit the password file. This covers the basic options. The other tabs have the not-so-basic settings. For example, we can use the SSH Tunnel option if the server does not allow direct connections from the client machine. This is useful when we are working for a client. Only a few machines in the client's network are allowed access from an external network. The machines on which databases are hosted will definitely not be among the machines exposed to external networks. If you are working with the database cluster on a different machine, check whether you have modified the postgresql.conf (listen_addresses entry) andpg_ hba.conf files on the server so that you can connect from a remote machine. The documentation links are runtime-config-connection.html and current/static/auth-pg-hba-conf.html. 107 Client Tools The pgAdmin main window Once we are connected to a server, we can go ahead and see what else is possible with pgAdmin. The main window has three panes, as shown in the following screenshot: In the left-hand side pane, we have the Object Browser pane that shows all objects in a hierarchical manner. At the top of the hierarchy, we have Server Groups and then Servers. This is where choosing a good name for the server helps. At the next level and the levels below is the rest of the hierarchy is visible. The top right-hand side pane provides quite a lot of very useful information. The first tab provides information such as name of the object, object identifier, estimated rows, and comment. Even more important, in many cases, is the next tab: Statistics. This provides information regarding reads, sequential scans, index scans, and other information that will be useful when we are trying to optimize database queries or having another look at the table design. The data shown in the Statistics tab as well as the other tabs in the right-hand side pane changes depending on the type of object we select in the object browser. 108 Chapter 6 The bottom right-hand side pane has the SQL for the object selected. Right-clicking on a table in the Object Browser pane gives you a few options. One of the options is the data viewer. In the data viewer menu, we can edit existing data, add new records, and delete records. The data viewer menu also lets us apply filters and select specific records. In the case of a table, the right-click options lets us take a backup of the table, import data, restore from a backup, and so on, as shown in the following screenshot: 109 Client Tools The Tools menu provides many useful options as well. The important ones are listed next. The Server Configuration option lets us see the current settings, edit them, and reload the parameter values, as shown in the following screenshot. Some of the settings need a server restart. So, the effect might not be visible immediately. The user must have appropriate permissions to use this feature. 110