This package includes 4 extensions:
- babelfishpg_tsql
- Supports the tsql language.
- babelfishpg_tds
- Supports the tds connection.
- babelfishpg_common
- Supports the various datatypes in MSSQL.
- babelfishpg_money
- supports the
money
type in MSSQL. This is a variation of the opensource fixeddecimal extension.
- supports the
The following build instructions comply with Ubuntu 20.04 and Amazon Linux 2 environment.
-
First install dependent tools and libraries
Install
postgresql-devel
,libicu
,libxml2
,openssl
anduuid-devel
packages in order to build Babelfish.You'll also need to install
gcc
,gcc-c++
,java
andbison
.sudo apt-get install uuid-dev openjdk-8-jre \ libicu-dev libxml2-dev openssl libssl-dev python-dev \ libossp-uuid-dev libpq-dev pkg-config g++ build-essential bison
For RHEL-flavoured distributions, this is the command for dealing with the required dependencies:
sudo yum install libicu-devel libxml2-devel \ openssl-devel uuid-devel postgresql-devel gcc gcc-c++ java
-
Now it's time to build the Postgres engine. In the Postgres engine directory, run these sequence of commands to build the PG engine modified for Babelfish (Github repo: https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish):
./configure --prefix=$HOME/postgres/ --without-readline --without-zlib --enable-debug CFLAGS="-ggdb" --with-libxml --with-uuid=ossp --with-icu make -j 4 2>error.txt make install make check
Also build and install the extensions because uuid-ossp.so is a runtime dependency for babelfish:
cd contrib && make && sudo make install
-
Get the latest version of cmake (version 3+ is required)
To get and use the latest version of cmake:
wget https://github.com/Kitware/CMake/releases/download/v3.20.6/cmake-3.20.6-linux-x86_64.sh sh cmake-3.20.6-linux-x86_64.sh
-
Install ANTLR
The
babelfishpg_tsql
contrib has a plug-in parser generated by ANTLR, which depends on cmake andantlr4-cpp-runtime-4.9.3
. Unfortunately, there aren't binaries for C++ targets available. You'll also need haveuuid-devel
installed in order to installantlr4-cpp-runtime-4.9.3
.First copy the jar file in
contrib/babelfishpg_tsql/antlr/thirdparty/antlr/
to another location:cd babelfish_extensions/contrib/babelfishpg_tsql/antlr/thirdparty/antlr/ sudo cp antlr-4.9.3-complete.jar /usr/local/lib
Compile antlr4:
wget http://www.antlr.org/download/antlr4-cpp-runtime-4.9.3-source.zip unzip -d antlr4 antlr4-cpp-runtime-4.9.3-source.zip cd antlr4 mkdir build && cd build cmake .. -DANTLR_JAR_LOCATION=/usr/local/lib/antlr-4.9.3-complete.jar -DCMAKE_INSTALL_PREFIX=/usr/local -DWITH_DEMO=True make sudo make install
Copy libantlr4-runtime to postgres/lib
cp /usr/local/lib/libantlr4-runtime.so.4.9.3 ~/postgres/lib/
- If you come across the error
-- Checking for module 'uuid' -- No package 'uuid' found CMake Error at /usr/local/share/cmake-3.21/Modules/FindPkgConfig.cmake:554 (message): A required package was not found Call Stack (most recent call first): /usr/local/share/cmake-3.21/Modules/FindPkgConfig.cmake:776 (_pkg_check_modules_internal) CMakeLists.txt:44 (pkg_check_modules)
Check that you have
uuid-devel
installed. If so, go toantlr4/CMakeLists.txt
and comment out the linepkg_check_modules(UUID REQUIRED uuid)
by adding a#
to the beginning of the line.More information about installing ANTLR4 can be found at this link.
-
Set environment variables and paths
- Set the
PG_CONFIG
environment variable to where you installed the DB enginepostgresql_modified_for_babelfish
. For example, I installed it under~/postgres
(the default location), so I setPG_CONFIG
as follows:
export PG_CONFIG=~/postgres/bin/pg_config
- Set
PG_SRC
to where the DB engine source package is as we need access to a few engine source files in order to build the Babelfish extensions. For example:
export PG_SRC=~/workplace/postgresql_modified_for_babelfish
- Set the cmake environment variable to where you installed cmake (as defined above in step 2). For example:
export cmake=~/workplace/cmake-3.20.6-linux-x86_64/bin/cmake
- Update the file
contrib/babelfishpg_tsql/antlr/CMakeLists.txt
with the correct antlr4-runtime path (if not there already). For example:
SET (MYDIR /usr/local/include/antlr4-runtime/)
- Set the
make
and make install
each extension within the extension project's contrib
directory:
cd contrib/babelfishpg_money
make && make install
cd ../babelfishpg_common
make && make install
cd ../babelfishpg_tds
make && make install
cd ../babelfishpg_tsql
make && make install
-
Install the SQL server command line tools by following steps 1 - 3 under Install the SQL Server command-line tools. Use the RHEL7 URL in step 1.
- For convenience, add
/opt/mssql-tools/bin/
to your PATH environment variable. This enables you to run the tools without specifying the full path. For Z shell (the default shell for Cloud Desktop), add the following line to your~/.zshrc
file:
export PATH=/opt/mssql-tools/bin:$PATH
- For convenience, add
-
From where you installed
postgres
in the./configure
step adjust the paths of these commands and run them to start the Postgres server:~/postgres/bin/initdb -D ~/postgres/data/ ~/postgres/bin/pg_ctl -D ~/postgres/data/ -l logfile start
-
Modify
~/postgres/data/postgresql.conf
by uncommenting and adjusting the following 2 properties:listen_addresses = '*' shared_preload_libraries = 'babelfishpg_tds'
-
Modify
~/postgres/data/pg_hba.conf
to allow connections from allowed IP addresses, replacing10.x.y.z
with your IP address. E.g.host all all 10.x.y.z/32 trust
-
Now run this to apply the changes:
~/postgres/bin/pg_ctl -D ~/postgres/data/ -l logfile restart
-
Connect via psql using the command
~/postgres/bin/psql -U your_user_name
. Create the extension and set up essential parameters. Please be aware you need to choose either 'single-db' or 'multi-db' mode during this provisioning step and you CAN NOT change it later. Refer to our documentation page for more information on 'single-db' vs 'multi-db' mode.CREATE USER babelfish_user WITH CREATEDB CREATEROLE PASSWORD '12345678' INHERIT; DROP DATABASE IF EXISTS babelfish_db; CREATE DATABASE babelfish_db OWNER babelfish_user; \c babelfish_db CREATE EXTENSION IF NOT EXISTS "babelfishpg_tds" CASCADE; GRANT ALL ON SCHEMA sys to babelfish_user; ALTER SYSTEM SET babelfishpg_tsql.database_name = 'babelfish_db'; ALTER SYSTEM SET babelfishpg_tds.set_db_session_property = true; ALTER DATABASE babelfish_db SET babelfishpg_tsql.migration_mode = 'single-db'|'multi-db'; SELECT pg_reload_conf(); CALL SYS.INITIALIZE_BABELFISH('babelfish_user');
- If you run into errors connecting to psql such as
psql: error: could not connect to server: No such file or directory
try giving permissions by using this command instead:sudo ~/postgres/bin/psql -d postgres -U your_user_name
- If you run into errors connecting to psql such as
-
Try connecting to Babelfish via SQLCMD
sqlcmd -S localhost -U babelfish_user -P 12345678
- Install Maven: https://maven.apache.org/install.html
- cd to test/JDBC
cd test/JDBC
- run cleanup.sh, init.sh and mvn test
./cleanup.sh ./init.sh mvn test
For detailed instructions on how to write, add, and run tests in JDBC test framework, refer to the online instructions.