Geekly Articles each Day
📜 ⬆️ ⬇️

Generating classes from a database using DataGrip


This small note will show how to write the DataGrip extension to generate code (in this case POCO (C #) classes) based on tables from almost any database (SQL Server, Oracle, DB2, Sybase, MySQL, PostgreSQL, SQLite, Apache Derby, HyperSQL, H2).


Foreword



DataGrip is a relatively new IDE from JetBrains for working with different DBMS that has some API for expanding the functionality. The task is to use it to write a POCO (C #) class generator.
If you do not want to read all this, but simply wish to start generating classes, then here is the link to the repository with the script .


Script writing


DataGrip allows you to extend your functionality using scripts (Scripted Extensions). Groovy, Clojure and JavaScript are supported. The documentation on the website about this is rather brief, but there are examples and an archive with an API in the form of Java source code. Source code can be found in <DataGrip_installation_dir>/lib/src/src_database-openapi.zip . Examples can be found in the IDE itself in the Files -> Scratches panel. DataGrip also supports scripts for exporting data to various formats (extractors, this article will not be discussed), examples for the csv, json and html formats are also in the Scratches panel.


So, for writing the script, we will use Clojure, the basis was taken as an example of the POJO generator from IDE.
Of course, syntax highlighting and autocompletion for Clojure are not in DataGrip, so you can use any other editor.
To begin with, let's configure mapping types from the database to C # types and declare some constants.


Code
 (def usings "using System;") (def default-type "string") (def type-mappings [ [["bit"] "bool"] [["tinyint"] "byte"] [["uniqueidentifier"] "Guid"] [["int"] "int"] [["bigint"] "long"] [["char"] "char"] [["varbinary" "image"] "byte[]" true] ; cannot be null [["double" "float" "real"] "double"] [["decimal" "money" "numeric" "smallmoney"] "decimal"] [["datetime" "timestamp" "date" "time"] "DateTime"] [["datetimeoffset"] "DateTimeOffset"] ]) (def new-line "\r\n")

Next, we write a function that leads the string to PascalCase.


Code
 (defn- poco-name [name] (apply str (map clojure.string/capitalize (re-seq #"(?:[AZ]+)?[az\d]*" name))))

Matching a type from a DB to a type in C # based on mappings that we defined earlier.


Code
 (defn- poco-type [data-type is-null] (let [spec (.. data-type getSpecification toLowerCase) spec-matches? (fn [pattern] (= (re-find #"^\w+" spec) pattern)) mapping-matches? (fn [[ps tn]] (when (some spec-matches? ps) [tn])) [type cant-be-null] (some mapping-matches? type-mappings) nullable-type (if (and type (not cant-be-null) is-null) (str type "?") type)] (or nullable-type default-type)))

The function that receives all the columns from the table, calls the match function and collects the object we need for further saving. Here we use methods from the API, for example com.intellij.database.util.DasUtil/getColumns , all these methods can be viewed in the src_database-openapi.zip archive mentioned above.


Code
 (defn- field-infos [table] (let [columns (com.intellij.database.util.DasUtil/getColumns table) field-info (fn [column] {:name (poco-name (.getName column)) :type (poco-type (.getDataType column) (not (.isNotNull column)))})] (map field-info columns)))

Text generation for properties and classes, nothing special just string concatenation. As well as the function of writing this text to a file.


Code
 (defn- property-text [field-info] (let [type (:type field-info) name (:name field-info)] (str " public " type " " name " { get; set; } " new-line))) (defn- poco-text [class-name fields] (apply str (flatten [usings new-line new-line "public class " class-name " " new-line "{" new-line (interpose new-line (interleave (map property-text fields))) "}" new-line]))) (defn- generate-poco [directory table] (let [class-name (poco-name (.getName table)) fields (field-infos table) file (java.io.File. directory (str class-name ".cs")) text (poco-text class-name fields)] (com.intellij.openapi.util.io.FileUtil/writeToFile file text)))

Finally, the function opens the dialog to select the directory for saving files and the function that determines the selected tables and starts the generation.


Code
 (defn- generate-pocos [directory] (let [table? (partial instance? com.intellij.database.model.DasTable)] (doseq [table (filter table? SELECTION)] (generate-poco directory table)))) (.chooseDirectoryAndSave FILES "Choose directory" "Choose where to generate POCOs to" (proxy [com.intellij.util.Consumer] [] (consume [directory] (generate-pocos directory) (.refresh FILES directory))))

Install script



Full script code on github .
To install, simply copy the file. Generate POCO.clj Generate POCO.groovy in IDE> Files> Scratches> Extensions> DataGrip> schema.
And in the context menu of the tables, the corresponding submenu item will appear in the Scripted Extensions section.


Result


From the following table


It was
 CREATE TABLE Users ( Id INT PRIMARY KEY NOT NULL IDENTITY, first_name NVARCHAR(255), Last_Name NVARCHAR(255), Email VARCHAR(255) NOT NULL, UserGuid UNIQUEIDENTIFIER, Age TINYINT NOT NULL, Address NVARCHAR(MAX), photo IMAGE, Salary MONEY, ADDITIONAL_INFO NVARCHAR(42) );

the following class will be generated:


It became
 using System; public class Users { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public Guid? UserGuid { get; set; } public byte Age { get; set; } public string Address { get; set; } public byte[] Photo { get; set; } public decimal? Salary { get; set; } public string AdditionalInfo { get; set; } }

Conclusion


In such a fairly simple way, you can generate C # classes that describe your tables in the database and make life a little less routine. Of course, you are not limited to only POCO C # classes, you can write a generator of anything, for another language, framework, etc.


')

Source: https://habr.com/ru/post/301324/

More articles:


All Articles