How to stop doing the same thing over and over

Do you enjoy repeating routine operations over and over? Neither do I. Yet every time I worked with the Rostelecom storage in the SQL client, I had to manually specify all the joins between tables. And this despite the fact that in 90% of cases, the fields and conditions for joining tables were the same from query to query! It would seem that any SQL client has autocompletion features, but they don’t always work for storages: they rarely have unique constraints and foreign keys set up for performance reasons, and without that, the program doesn't know how the entities are related and what it can suggest to you.

After going through denial, anger, bargaining, depression, and approaching acceptance, I decided — why not try to implement autocompletion myself, with blackjack and all? I use the dbeaver client, which is written in Java, and it has a community version with open-source code. I came up with a straightforward plan:

1. Find the classes in the source code that handle autocompletion
2. Redirect them to work with external metadata and pull the join information from there
3. ??????
4. PROFIT

I quickly figured out the first point — found a request in the bug tracker to adjust the autocompletion feature and in the related commit discovered the SQLCompletionAnalyzer class. I looked at the code — it was exactly what I needed. Now I just had to rewrite it to make everything work. I waited for a free evening and began to think through the implementation. I decided to keep the table relationship rules (metadata) in json. I hadn't had practical experience with this format and the current task seemed like an opportunity to correct this oversight.

To work with json, I decided to use the json-simple library from Google. That's where the surprises began. As it turned out, dbeaver, being a true application, is written on the Eclipse platform using the OSGi framework. For experienced developers, this thing provides convenience in managing dependencies; for me, it felt more like dark magic that I was clearly unprepared for: as usual, I specified the imports for the json-simple library at the top of the edited class, listed it in pom.xml, after which the project categorically refused to build properly and crashed with errors.

Fixing the build errors ended up being: I specified the library not in pom.xml but in the manifest manifest.mf, as required by OSGI, indicating it as an import-package. Not the prettiest solution, but it works. Then came the next surprise. If you're developing in IntelliJ IDEA, you can't just launch the debug of your Eclipse-based project; a novice developer must suffer as much as an analyst without query autocompletion. The developers themselves came to the rescue, detailing in the wiki all the necessary steps. The most frustrating part is that even after all this effort, the project still wouldn't run in debug mode with the JSON library included via import-package (despite the fact that it compiled successfully into the final product).

By that time, I had begun to feel the inconvenience of using JSON for my task — after all, the metadata was supposed to be edited manually, and the XML format is better suited for that. Another argument in favor of XML was the presence of all necessary classes in the JDK, which allowed me to stop struggling with an external library. I happily transferred all metadata from JSON to XML and began to refine the autocompletion logic.

Metadata Example

dim_account
        dim_partner
        
        
    
    
        dim_account
        dim_branch

As a result, I made changes to the SQLUtils and SQLCompletionAnalyzer classes. The idea is this: if the program fails to find suitable autocompletion proposals based on the basic logic, it checks for possible joins via the external XML file. The file contains pairs of tables with the fields indicating how these tables should be linked. Constraints on the technical validity dates of records, eff_dttm and exp_dttm, and the logical deletion flag, deleted_ind, are set by default.

After making changes to the code, the question arose — who will fill in the metadata file? There are many entities in the storage, so it's cumbersome to write all the links by myself. In the end, I decided to assign this task to my analyst colleagues. I uploaded the metadata file to svn, from where it can be checked out to the local directory with the program. The principle is this: a new entity appeared in the storage? One analyst adds possible joins to the file, commits the changes, and the others check it out to themselves and enjoy the working auto-completion: community, knowledge accumulation, and so on. I held a workshop for my colleagues on how to use the program and wrote an article in Confluence — now our company has one more convenient tool.

Working on this feature gave me the understanding that it's not worth fearing to delve into open-source projects — as a rule, they have a clear architecture, and even basic knowledge of the language is enough for experimentation. And with a certain degree of perseverance, you can even eliminate those annoying routine operations, saving time for new experiments.

Source: habr.com