DIF moved to GitHub on 2018-21-05. Please use it as new reference site.
See also the new DIF-derived package
SID. It does not require MySQL/MariaDB source code and it is usable with any Linux/MacOS installation!
DIF is a set of tools aimed at implementing a powerful indexing system
for astronomical catalogs and other data with spherical coordinates,
stored into
MySQL /
MariaDB databases.
DIF is able to use both
HTM and
HEALPix
pixelization schemas and it allows very fast query execution even on billion-row tables.
DIF has been developed on the GNU/Linux platform and is released under
the
GPL license. This site contains all news, updates,
documentation and downloadable software packages. The site is still
under development, so check for updates.
# DIF QUICK START
#
# Ensure DBD::mysql is installed
sudo cpan install DBD::mysql
# or the OS specific command, e.g. on Mac OS using MacPorts:
sudo port install p5-dbd-mysql
# or on Debian/Ubuntu:
sudo apt-get install libdbd-mysql-perl
# Download packages in a temporary directory
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
wget http://ross.iasfbo.inaf.it/dif/dif-0.5.5.tar.gz
# Unpack mysql
tar xvzf mysql-5.7.24.tar.gz
# the directory mysql-5.7.24 will be created
# Compile and install DIF:
tar xvzf dif-0.5.5.tar.gz
cd dif-0.5.5
./configure --with-mysql-source=/complete/path/to/mysql-5.7.24
make
sudo make install
# Install DIF database (the mysql root password will be asked)
dif --install
# The latter command installs an example table with all the Messier
# objects. To display the content of such table use:
mysql> use DIF;
mysql> SELECT * from Messier;
# Install the HTM index on the example table DIF.Messier
dif --index-htm DIF Messier 6 Ra Decl
# Use DIF index to search in a circular region:
SELECT * FROM Messier_htm_6 WHERE DIF_Circle(82, 22, 100);
# Ensure you are using the index prepending `DESCRIBE` to previous query:
mysql> DESCRIBE SELECT * FROM Messier_htm_6 WHERE DIF_Circle(82, 22, 100);
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
| 1 | SIMPLE | dif | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Using where |
| 1 | SIMPLE | Messier | NULL | ref | htmID_6 | htmID_6 | 2 | DIF.dif.id | 1 | 100.00 | Using index condition; Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
Below a typical installation on a Linux / Mac system with a locally compiled MySQL 5.7 source distribution:
# DIF QUICK START
#
# Ensure DBD::mysql is installed
sudo cpan install DBD::mysql
# Download packages in a temporary directory
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
wget http://ross.iasfbo.inaf.it/dif/dif-0.5.5.tar.gz
# Compile and install mysql
tar xvzf mysql-5.7.24.tar.gz
cd mysql-5.7.24/
cmake . -DWITH_BOOST=boost
make
sudo make install
sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql
# Create the file `/etc/my.cnf` with the following content:
[mysqld]
secure-file-priv = ""
# Initialize the server.
# IMPORTANT: Take note of the generated random password!
cd /usr/local/mysql
sudo bin/mysqld --initialize --user=mysql
sudo bin/mysql_ssl_rsa_setup
# Start the server (this command must be executed as root)
/usr/local/mysql/bin/mysqld_safe --user=mysql &
# Connect as root using the random password generated above...
mysql -u root -p
# ... and change it with:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
# Now logout and re-connect with the new password
mysql -u root -p
# Compile and install DIF:
tar xvzf dif-0.5.4.tar.gz
cd dif-0.5.4
./configure --with-mysql-source=/complete/path/to/mysql-5.7.24
make
sudo make install
# Install DIF database (the mysql root password will be asked)
dif --install
# The latter command installs an example table with all the Messier
# objects. To display the content of such table use:
mysql> use DIF;
mysql> SELECT * from Messier;
# Install the HTM index on the example table DIF.Messier
dif --index-htm DIF Messier 6 Ra Decl
# Use DIF index to search in a circular region:
SELECT * FROM Messier_htm_6 WHERE DIF_Circle(82, 22, 100);
# Ensure you are using the index prepending `DESCRIBE` to previous query:
mysql> DESCRIBE SELECT * FROM Messier_htm_6 WHERE DIF_Circle(82, 22, 100);
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
| 1 | SIMPLE | dif | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Using where |
| 1 | SIMPLE | Messier | NULL | ref | htmID_6 | htmID_6 | 2 | DIF.dif.id | 1 | 100.00 | Using index condition; Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+------------+------+----------+------------------------------------+
# Stop the mysql server with:
/usr/local/mysql/bin/mysqladmin -u root -p shutdown
Change log
- 2020-03-17: V. 0.5.5. Fixed rectangular region selection defined by 4 corners.
- 2020-01-28: V. 0.5.5. Removed -fno-rtti CXXFLAGS.
- 2019-05-07: V. 0.5.5. configure update for "-std=c++14" usage check. This is needed by MySQL v. 8.0.16.
- 2019-01-29: V. 0.5.5. Support MariaDB 10.3.12. Makefile uses "-std=c++x0" instead of "std=c++11. Removed dif.tex and dif.pdf, adding "doc/README.md".
- 2018-11-29: V. 0.5.5. Support MariaDB 10.3
- 16/11/2018: V. 0.5.5. No need to use a source code installed version of MySQL/MariaDB.
- 22/05/2018: V. 0.5.4. Ported to GitHub. Added README.md.
- 19/10/2017: V. 0.5.4. Makefile fix for MariaDB 10.2.9.
- 09/08/2017: V. 0.5.4. Support MariaDB 10.2.
- 08/06/2017: V. 0.5.4. Fix returned list of partial pixels in HEALPix rectangular regions.
- 11/09/2016: V. 0.5.4. Added support for MySQL > 5.7.9.
- 04/08/2016: V. 0.5.4. MariaDB 10.1 support. Replaced HEALPix C++ library with version 3.30. Rectangular region selections for HEALPix. Added various UDFs and SQL procedures. Extended dif script capabilities (run "dif -h").
- 23/05/2014: V. 0.5.3. MySQL 5.6, 5.7 and MariaDB 10.0 support. Added UDFs HTMsNeighb and HEALPMaxS. Added DIF_sNeighb DB engine region search area (HTM trixel neighbors at higher depth).
- 30/01/2013: V. 0.5.2. MySQL 5.5 and MySQL Cluster 7.2 support.
- 29/08/2012: V. 0.5.2. Debian and new GCC version support.
- 10/04/2012: V. 0.5.2. Full Mac OS X (+MacPorts) support.
- 26/10/2010: V. 0.5.2. dif script upgrade.
- 15/03/2010: V. 0.5.2. New version using multi-indexing capabilities. Please see documentation.
- 17/07/2009: V. 0.5.0. New version using multi-indexing capabilities. First release.
- 12/11/2008: V. 0.3.3-alpha1. New version using the 64-bit INT HEALPix lib and with several UDF added!
- 10/07/2008: Giorgio Calderone , V. 0.3.2-alpha3 - Bugfix.
- 08/01/2008: Giorgio Calderone , V. 0.3.2-alpha2 - Documentation updated.
- 11/11/2007: Giorgio Calderone , V. 0.3.2-alpha1 - First release of DIF, as a standalone package, until now it has been part of the MCS project. It support both HTM and HEALPix (either RING or NESTED) pixelization schemas.