A plugin for pgModeler, the ERD tool for PostgreSQL.

The query builder v0.9.2 can now be considered in beta state, please help fix bugs reporting at github.

pgModeler has reached a certain level of maturity.

While it has grown into a good database management software, it focuses primarily on conception and modelling, with its mastery of the Data Definition Language SQL subset, which help database architects get to a rigorous implementation.

This plugins gives a shot at the Data Query Language SQL subset. It is aimed primarily at :

• students, database analysts, IT or business advisors... whom do not have high SQL skills. It allows them to create queries from visual objects, and may help them get on-board with SQL basics along the way.
• folks with advanced SQL skills whom frequently write long, tedious and repetitive queries for their applications.

Explanatory video peertube || youtube

This is the traditional feature set ; you can insert tables, columns, relations in the builder ; group, sort them...

The plugin will then output the SQL, and allow you to save the code, or run it directly from the management section.

Explanatory video peertube || youtube

This is a primer in the FLOSS world! You get candidate join paths, ranked by score, for the items you inserted in the "SELECT" clause of the query. This can prove useful in discovering a new database, previously reverse-engineered from production (pgModeler can do that !). Currently, to be of any interest, the database and/or the model shall have foreign-key relationships declared. Otherwise, you can beforehand look at tools such as linkifier.

This graphical query builder relies on graph algorithms, mainly Dijkstra's path-finding and Dreyfus-Wagner for Steiner trees. See the video above for more details, and how to use the solver. Some implementation research history.

A few white papers about SQL-join solvers :

• http://resources.mpi-inf.mpg.de/yago-naga/naga/download/ICDEResearchLong09_264.pdf
• https://www.ics.uci.edu/~chenli/pub/sigmod2009-tastier.pdf
• https://researcher.watson.ibm.com/researcher/files/in-diptsaha/sap-nlq.pdf
• http://www.vldb.org/pvldb/vol6/p1222-guerra.pdf
• https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/icde07steiner.pdf

There is no need to do anything special to build the core of the query builder. Once you have cloned the plugin subproject into the root of the source-tree of pgmodeler, you can run qmake normally.

By default, the query builder is built without its SQL-join solver. The solver has a heavy dependency : the Practical Aproximations Algorithms Library (paal), and paal depends itself onto the famous C++ boost library (paal might even get merged into it at some point).

There are multiple ways of building the solver, between being fully assisted, or doing it completely manually. Consider that even if you want to get maximum guiding as we will see below (qmake -r CONFIG+=INTERACTIVE_QMAKE), it is better to follow next manual point for boost. (If you ask the guided way for boost, it will clone the entire boost repo.)

You will need boost. The recommended path to get it is to use the one shipped in your system.

• GNU/Linux - Get it from your package manager, for example sudo apt install libbost-dev.
• Windows - Getting boost can be done from minGW repository. In Msys2's minGW64 console : pacman -S mingw-w64-x86_64-boost.
• Mac - brew install boost

You can otherwise install the pre-compiled libraries from https://boost.org, or, as is done in setup.sh boost, "build" boost headers from their git repo. This is a longer path. If you go the manual way, don't forget to add the path to boost in graphicalquerybuilder.pro.

Paal dependency will get cloned from its repository, and slightly tweaked (see what setup.sh paal does). For paal you'd better follow the assisted way.

To tell qmake that you want to build the solver, you can either :

• set the variables in graphicalquerybuilder.conf before running qmake. This simply consists in replacing "y" by "n". E.g., from pgModeler's source root
  • you want to build the solver, sed -i.bak s/GQB_JOIN_SOLVER=\"n\"/GQB_JOIN_SOLVER=\"y\"/ plugins/graphicalquerybuilder/graphicalquerybuilder.conf.
  • you have boost installed, sed -i.bak s/BOOST_INSTALLED=\"n\"/BOOST_INSTALLED=\"y\"/ plugins/graphicalquerybuilder/graphicalquerybuilder.conf.
• get assisted throughout the setup adding the flag INTERACTIVE_QMAKE. Still from pgmodeler source root :

$QT_ROOT/bin/qmake -r CONFIG+=INTERACTIVE_QMAKE CONFIG+=release PREFIX=$INSTALLATION_ROOT pgmodeler.pro

Finally compile pgmodeler, referring to the installation documentation.

If you have already configured your environment to build pgmodeler once, building the whole

project with the query builder core + solver, on a GNU/Linux station, should work with :

#Getting ready-to-go boost on a debian-derivative
sudo apt install libboost-dev

#Getting the sources
git clone https://www.github.com/pgmodeler/pgmodeler
#Might need to git checkout the correct branch while all is not in master
cd pgmodeler
INSTALLATION_ROOT=$PWD #or wherever out-of-tree you want to
git clone https://www.github.com/pgmodeler/plugins
#Same as above

#Getting paal
cd plugins/graphicalquerybuilder
./setup.sh paal
cd -

#Tweaking the conf file
sed -i.bak s/GQB_JOIN_SOLVER=\"n\"/GQB_JOIN_SOLVER=\"y\"/ plugins/graphicalquerybuilder/graphicalquerybuilder.conf
sed -i.bak s/BOOST_INSTALLED=\"n\"/BOOST_INSTALLED=\"y\"/ plugins/graphicalquerybuilder/graphicalquerybuilder.conf

#Running qmake
QT_ROOT= #type your Qt path here, where bin and include folders are
$QT_ROOT/bin/qmake -r CONFIG+=release PREFIX=$INSTALLATION_ROOT pgmodeler.pro

#Building
make -j$(nproc) && make install

UI translations are appreciated of course, and you can help on plenty other topics.

• Maxime Chambonnet - Initial work

See also the list of contributors -soon you hopefully ? :) who participated in this project.

Thank you to Raphael Araújo e Silva, the author of pgModeler, for his great software that made this feature possible!

Enjoy!