The ScenarioYou are the owner of an online store, located in Poland. The majority of your customers are from Poland and they speak Polish. But you want to sell your products abroad too and your international customers mainly speak English. So you want your online store to be available in bothPolishandEnglish. You also expect that your products will sell well in France, so you anticipate that you’ll have to prepare a
Vertabelo internally stores your model in an XML format. You can download the XML file and use it to extract some information by using a script. Let’s take a look at the structure of this XML file.Here is the XSD file and an example of the XML file for the above sample database model created in Vertabelo .The root element of the document is the DatabaseModel element with the VersionId attribute. As of today, the current version is 2.1.
Sometimes you don’t want the reverse engineering tool to extract all of the tables in your database. For example, the database may contain technical tables which are logically are not part of the model. This is when the-regexpoption comes in handy.Use the-regexpoption and the tool will extract all tables and views with names matching the regular expression. The tool prints the regular expression it uses so you can verify that your shell parsed the expression correctly. For this option to work, the regular expression has to be in the
Last Saturday, Vertabelo was at Confitura 2014 , this year’s edition of the largest free Java conference in Poland. As usual, the conference was organized at the main campus of the University of Warsaw (my beloved Alma Mater!). Unusually, the conference was in two buildings: Auditorium Maximum and the Old Library. Splitting the conference between two locations was a very good idea. The halls were not as crowded as in the years before. “It feels completely unlike Confitura,” was an opinion I overheard.
Last week the Vertabelo team visited the GeeCON 2014 conference. GeeCON is a well-known Polish Java conference. This year, it was organized in Kraków, one of the most beautiful Polish cities, which had been the capital of Poland up till the 16th century. There were over 1000 participants and about 100 lectures and lecturers. Hats off to the organizers for managing the crowd because they did it perfectly. We went to GeeCON to promote Vertabelo. We spent the conference presenting Vertabelo to participants, explaining what it is and how it differs from other database modeling tools. The participants generally liked the idea of
If the amount of data in your temporary table is growing very fast, you may want to expunge old unnecessary data from your database.The scenario is like this: there is a tablebackup_datawhich contains backup data for various resources. The amount of data in the table is growing very fast, so you want to delete old unnecessary data. However, you still want to keep a little bit of historical data. The business rules for keeping the backups for every resource are a variation of the grandfather-father-son algorithm:
The standard DELETE statement in SQL returns the number of deleted rows.In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted.You can return the columns of your choice.In your code you can process the returned rows in the same way as you would process the results of an SQL query. For example, you may log the data that have been deleted.
Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON.Let’s say I want to select one weather report for each location.The query retrieves one weather report for each location.You can use several expressions in SELECT DISTINCT ON statement.For each resource the query retrieves a single backup data for each week.SELECT DISTINCT ON with ORDER BYThe undecorated SELECT DISTINCT ON selects one row for each group but
If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.)The date arithmetic API in PostgreSQL is like abreath of fresh air.Interval ArithmeticYou can represent a time interval:Add an interval to a date or subtract an interval from a date:Truncating DatesYou can truncate dates to the specified precision.
Today we begin a new series on our blog:Vertabelo Challenges. Once in a while we’ll publish a new database modeling challenge on our blog so you can test your database modeling skills in Vertabelo. Don’t worry – the challenges will be fairly easy. Follow our Database Design 101 series on the blog and on Vertabelo Youtube channel , and you should be able to conquer all of them in no time.