Home Closet Organization of team development of database structures

Organization of team development of database structures

by admin

Recently a question of team development (about 10 people) of data schema for Oracle database became very pressing in our organization. We worked the old-fashioned way, using the notorious Erwin 3.5.x product, and for the time being we were quite satisfied with its capabilities, placing the file in a centralized version control system and locking it as needed, thereby avoiding collisions with parallel development. But everything flows, everything changes, the team grows, and it is the 21st century around the corner, so we decided to use more modern means. Actually, below is a story about the process of converting the schema into the new format (although the same manufacturer) and organization of means of collective development and versioning support, diluted with some thoughts about the product in general and patterns of using it in our work, in particular. The described process was not without pitfalls, so perhaps the experience of such a transition will be useful to someone.
Narrative Plan :

  • Discussions about the product, comments on usage patterns, swearing about formats.
  • Preparatory work, shamanism in the database.
  • Team development organization, performance optimization.

Product speculation, comments on usage patterns, scolding on formats

Maintaining order in the database schema is important, especially if there is a live development, the number of tables and their relationships are growing, and no one can just keep the whole hierarchy of structures in mind anymore. This is clear to everyone, well, it was clear to us when we started developing for Oracle. It was a long time ago, and in favor was a design tool from Platinum company called Erwin 3.5.2. This tool was studied (by the way, still is studied – I learned it from my friends students) on computer specialties in universities and on the courses of database developers. It was this version that gained some popularity, and I should say, quite justified. Being the coeval of Windows’98 it is quite functional, copes with schemes of a decent size, flexibly adjusted thanks to macros and generates correct scripts for modern versions of Oracle, although designed only for version 8, copes, by the way, with reverse engeneering from the base. We had been using it until recently, and it had not malfunctioned much. However, there are some disadvantages. First, for some reason it is not friendly with network printers. If the default printer is a network printer, then the initial loading of the schema will be very slow, if the printer is connected directly to the machine, or if you use a virtual printer, the same schema is loaded much faster. Secondly, as I mentioned above, it is designed to work with Oracle 8 (and in general the choice of database versions is not particularly great there, MySQL or Postgree are absent in principle), and therefore fine tuning of table structures for new versions, such as the use of partitions, is impossible. Thirdly, we could not set up team development support in it. Potentially ModelMart support is already available in this version, but at one time nobody bothered (actually the team was 2 people), well, later it was impossible to find it as a separate product. Again, a feature of ModelMart (now called Model Manager) is to store the scheme in the database, ie as friendship of old versions of this product with new versions of the database – this is another question. The remaining shortcomings are not decisive, although of course sometimes manifest themselves and very annoying (eg, the search for submodel name is only possible by the first letter, and on the first occurrence; search for tables to add to the submodel is only possible by name of the physical layer, while the reports that are generated, sometimes displayed only logical level; restriction of rights is only possible at the level of access to the scheme file; no alignment of objects on the grid, and other nuances).
There was a turning point, and there was a definite need to move to a modern version of Erwin. The management even agreed to buy the product, and of course no older versions are for sale. But an important condition for the transition is the need to support all the old work. The old work is one file with the entire data schema (about 20Mb and 7 years of development). We have always used Erwin purely for modeling structures, and the logic of the work does not get there. Thus, it’s not a problem to deploy from scratch with a script generated from the program – a reference database with structures, logic and all the technical settings is used to deploy new instances. If we had kept the logic, the problem of version updates would have appeared much earlier, because pl/sql in version 8 and pl/sql in version 10 are two big differences. As a result, we managed to postpone the moment. Since we are interested only in structures and links – let’s clean up the schema from various "stuff" (when using reverse engeneering, triggers connected to the table get into schema, they are easy to find through Entity Reports-> Entity/Trigger options), download from developers site trial version 7.3 "to try" and expect that schema will open in new format (of course I exaggerate, no one expected it to be easy). But no, the seventh version does not see the format ER1 of version 3.
Now is the time to bicker about formats. Erwin 3.5.x stores the schematic in a single .ER1 file (plus backup .BK1). This is such a closed binary format, if you open it to view – well familiar letters will be, but no more. The seventh version of Erwin Data Modeler understands some format .ER1, but with a note that it is the format of the fourth version, not the third. This is probably due to the fact that since the fourth version Platinum’s product was repurchased by Computer Associates (or CA as they are officially called now). I have to make a trick, because Erwin 4.1.x is not officially distributed and no trial versions can be found (much less bought). So, with a clear conscience, we download the pirate version from wherever we can find it. It is strange that for such a well-known and expensive product, the company CA did not provide a utility for converting between version formats. Conversion takes place in memory directly at the time of opening. Initially the file took about 20 Mb and the conversion took a couple of minutes. After that the file weighs 60 Mb. However, I have to say, it seems to load faster than in version 3.

Preparatory work, shamanism in DB

Then install Erwin Data Modeler 7.3 (bundled with Erwin Model Navigation 7.3), and then separately Erwin Model Manager 7.3. The installation itself does not cause difficulties, but before the first start ModelMart need to configure the database, which will be stored all objects erwinovskoy scheme. CA has taken care of this and the distribution has two guides – we are interested in the Implementation Guide (the second, Administrator Guide is nothing particularly interesting by the way). It is necessary to create (we have oracle – so all told above concerns work with this DBMS) tablespace for model storage, index tablespace, the role of the user who will install models, the Model Manager user (this user can be given a role in installation) and the role for Model Manager users (by the way, we should not forget to give these users and dba). Actually at the first start of Model Manager administrative part it all and will be asked – it is necessary to enter as created administrator, specify tablespace for data and for indexes and wait until the program will create tables and procedures necessary for work. No problem with the initialization, but when you need to delete the settings created (of course, not experiment with the working version), I had an error and a total glitch, after which to connect to the Model Manager failed with error messages (the same nonsense happens when trying to update the license – from the trial to the working, if the model is already created in the trial). Fixed this by deleting all user objects of the Model Administrator and running the MMartInit.ora script in SQL Navigator from the program folder with the ignore errors mode. Something in this script got executed, something did not, but after running the admin console of the Model Manager program was able to connect to the database and reported that I have something, but it is damaged and can restore. After my confirmation, the table structure was recreated and everything worked. I should scold CA for this point: of course before buying such an expensive product, the company wants to test the entire business process in advance, blessing trial versions with full functionality available directly from the manufacturer. And of course after everything is set up and tested, the process of upgrading the licenses to full-featured should not cause any problems.
A few words should be said about choosing a base to work with. It is better to create a new instance, because indexes are sprawling for large models, and model management operations themselves are quite resource-intensive. So the most critical is the removal of the model from the library. Generally we use a database instance with some additional load besides Model Manager, but of course this is not a production server.
So, all the presets are done. The next step is to convert the circuit to the 7.x version. This process is surprisingly long and very memory intensive. However, just leave it for the night also can not work, because Erwin periodically reports on results of work and asks all sorts of trifles (eg, old scheme worked with Oracle 8, but new scheme supports Oracle 10/11, hence the inevitable question from the program "transfer to the new Oracle, or choose something else?", and other stuff like that). So, it’s worth having patience. As a result I’ve got a log-file of detected errors in my schema (I’ve got not so many of them – several duplicated objects and disabled foreign key, which can be easily fixed after reconversion). It should be noted that fresh Erwin takes validation very seriously – found different bugs like "varchar(50" (lack of closing parenthesis in type definition), which were not noticed by earlier versions. Total scheme had the extension .erwin and grew to 112 Mb. The final growth is almost six times larger than its original size. It is loaded at medium speed, of course, not fast, but not too slow.

Organization of team development, performance optimization

The next step is what this is all about, by and large – transferring the schema to the database and organizing the teamwork. To do this, having opened the schema file in Erwin, connect to the Model Manager under the administrator user and select Save model. After selecting the place of the object in the hierarchical scheme of model libraries, the scheme is loaded into the database. Again, everything is predictable – the process is not fast. But sooner or later it will be done, and our schema is already stored in the database and ready for team development. However, the first attempt to open any submodel from the created model (not forgetting to first close the file from which the model was created, otherwise nothing will work) leads to a hang for about 30 minutes. This is certainly not an option. Connect to the database and look at the scheme of the model administrator (say EADMIN, as in the manual). Tables were created, indexes are created, but the statistics for the tables are not collected. As a result: DBMS_STATS.GATHER_SCHEMA_STATS (‘EADMIN’). Next, we are going to deal with tables. Actually the two biggest tables are m7object and m7objectproperty. As the name suggests, the first stores the objects (actually all entities, tables, submodels in hierarchical order), the second their properties. Looking at the properties table data, I noticed quite a lot of entries with "Imported from a previous version of ERwin." in the stringvalue field. It is possible to clean it, but carefully, the size will not be reduced in times, of course, but still.
The original circuit was divided into many subcircuits (subject areas in the notation of version three), each such subcircuit can, in theory, open independently. But, locking is only possible at the circuit level, that’s one. When you save and merge changes to the repository, you still have to load the whole schema, that’s two. And finally, opening an entire schema takes the same amount of time (and a bit more memory) as opening an individual subcircuit, that’s three. So, we can immediately forget about the possibility to work with an individual subcircuit. Altogether we have about 700 mb of memory occupied by the loaded circuit. So far all is not very happy. However, the possibilities are certainly good – there is detailed meringering with repository, and rollback between saved versions, and backward engineering and reverse engineering with base, with another scheme or with a file, saving all the comparison and script generation settings directly in the scheme and so on.
The only minus is the speed. All the machinations with the base and settings allowed only slightly speed up the process. All requests are primitive (though slightly illogical), but the tables themselves are large. We tried to contact the official Russian distributor for a consultation, indicating the apparent hypotheses of the problems. As a result, the answer was that there was no universal solution to the performance problems of converting older models to the new version of Erwin. As a result, we are still at a crossroads between good functionality and poor performance, still struggling, but already looking at competitor products.

You may also like