{"id":114,"date":"2020-05-13T12:37:40","date_gmt":"2020-05-13T10:37:40","guid":{"rendered":"https:\/\/9h1lo.wordpress.com\/?p=114"},"modified":"2024-01-14T22:13:01","modified_gmt":"2024-01-14T21:13:01","slug":"mysql-connector-and-web-interface-for-dx-spider","status":"publish","type":"post","link":"https:\/\/www.9h1lo.net\/index.php\/2020\/05\/13\/mysql-connector-and-web-interface-for-dx-spider\/","title":{"rendered":"MySQL Connector and Web-Interface for DX Spider"},"content":{"rendered":"\n<p>DXSpider is Copyright \u00a9 1998-2007 by Dirk Koopman G1TLH.&nbsp;<a href=\"http:\/\/www.dxcluster.org\/\">http:\/\/www.dxcluster.org<\/a><\/p>\n\n\n\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-55550373-d047-40e7-ac3c-7267da3c257e\" href=\"https:\/\/www.9h1lo.net\/wp-content\/uploads\/2024\/01\/dxmysql.zip\">dxmysql<\/a><a href=\"https:\/\/www.9h1lo.net\/wp-content\/uploads\/2024\/01\/dxmysql.zip\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-55550373-d047-40e7-ac3c-7267da3c257e\">Download Scipts<\/a><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"purpose\">Purpose:<\/h3>\n\n\n\n<p>The purpose of the script is to extract the DXSpots from the DXSpider data files and write them into a MySQL database. This gives the facility of interfacing to the spots in many ways, mainly from a web interface, which was the main reason for developing this script.<\/p>\n\n\n\n<p>MySQL offers a fast and query-based database that can also be accessed from logbook programs ect.<\/p>\n\n\n\n<p>Prerequisites:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Linux Operating System (Will probably work on Windows also)<\/li>\n\n\n\n<li>DXSpider<\/li>\n\n\n\n<li>Apache 2.2<\/li>\n\n\n\n<li>MySQL 5<\/li>\n\n\n\n<li>PHP5<\/li>\n<\/ul>\n\n\n\n<p>Create the database schema:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; CREATE DATABASE `dxcluster` ;\n\nCreate the tables:\n\nCREATE TABLE `dxcluster`.`spots` (\n\n`freq` DOUBLE( 12, 3 ) UNSIGNED NOT NULL ,\n`dxcall` VARCHAR( 85 ) NOT NULL ,\n`datetime` VARCHAR( 85 ) NOT NULL ,\n`commets` VARCHAR( 100 ) NULL ,\n`spotter` VARCHAR( 85 ) NOT NULL\n\n) ENGINE = MYISAM\n\nNow we create the scripts for datacollection:\n\nCreate a file called start.txt and newspots.dat\n\ntouch start.txt\n\ntouch newspots.dat\n\nNow create a file called dxmysql.sh and insert the following:\n\ni=$(cat \/spider\/data\/spots\/2008\/start.txt)\n\ndoy=`date +%j`\n\nv=`cat \/spider\/data\/spots\/2008\/$doy.dat |wc -l`\n\nsed -n \"$i,$v p\" \/spider\/data\/spots\/2008\/$doy.dat &gt; \/spider\/data\/spots\/2008\/newspots.dat\n\nlet \"v++\"\n\necho $v &gt; \/spider\/data\/spots\/2008\/start.txt\n\nmysql -u &lt;mysqlusername&gt; -p&lt;mysqlpassword&gt; -e \"load data infile '\/spider\/data\/spots\/2008\/newspots.dat' into table dxcluster.spots fields terminated by '^' lines terminated by '\\n';\"<\/code><\/pre>\n\n\n\n<p>Now create a cron job (\/etc\/crontab) as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>0 0 * * * root echo \"1\" &gt; \/spider\/data\/spots\/2008\/start.txt\n\n* * * * * root \/spider\/data\/spots\/2008\/dxmysql.sh &gt; \/dev\/null<\/code><\/pre>\n\n\n\n<p>Please ensure that the DXSpider server is set to use UTC as timezone.<\/p>\n\n\n\n<p>Upload the PHP scripts to your \/var\/www directory<\/p>\n\n\n\n<p>You only need to edit the file clusterdb.inc.php to set your MySQL user and pass.<\/p>\n\n\n\n<p>Now access&nbsp;<a href=\"http:\/\/your\/\">http:\/\/your_site\/cluster.php<\/a><\/p>\n\n\n\n<p>This software is provided as-is and comes with no guarantees. It is provided under the Creative Commons licence. Please visit&nbsp;<a href=\"http:\/\/creativecommons.org\/licenses\/GPL\/2.0\/\">http:\/\/creativecommons.org\/licenses\/GPL\/2.0\/<\/a>&nbsp;for more details.<\/p>\n\n\n\n<p>Please leave the \u201cPowered by 9H1LO DX Engine\u201d and link to&nbsp;<a href=\"http:\/\/www.9h1lo.net\/\">www.9h1lo.net<\/a>&nbsp;on the html pages.<\/p>\n\n\n\n<p><strong>New update for 2010. The dxmysql.sh script has been updated to support auto year change<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\nyear=`date +%Y`\ni=$(cat \/spider\/data\/spots\/$year\/start.txt)\ndoy=`date +%j`\nv=`cat \/spider\/data\/spots\/$year\/$doy.dat |wc -l`\nsed -n \"$i,$v p\" \/spider\/data\/spots\/$year\/$doy.dat &gt; \/spider\/data\/spots\/$year\/newspots.dat\nlet \"v++\"\necho $v &gt; \/spider\/data\/spots\/$year\/start.txt\nmysql -u &lt;USER&gt; -p&lt;PASS&gt; -h &lt;HOST&gt; -e \"load data local infile '\/spider\/data\/spots\/$year\/newspots.dat' into table dxcluster.spots fields terminated by '^' lines terminated by '\\n';\"\n\n<\/code><\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>DXSpider is Copyright \u00a9 1998-2007 by Dirk Koopman G1TLH.&nbsp;http:\/\/www.dxcluster.org Purpose: The purpose of the script is to extract the DXSpots from the DXSpider data files and write them into a MySQL database. This gives the facility of interfacing to the spots in many ways, mainly from a web interface, which was the main reason for &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/www.9h1lo.net\/index.php\/2020\/05\/13\/mysql-connector-and-web-interface-for-dx-spider\/\">Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[13,15,25,29,36],"class_list":["post-114","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-cluster","tag-dxspider","tag-linux","tag-mysql","tag-software","item-wrap"],"_links":{"self":[{"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/posts\/114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/comments?post=114"}],"version-history":[{"count":2,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/posts\/114\/revisions"}],"predecessor-version":[{"id":594,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/posts\/114\/revisions\/594"}],"wp:attachment":[{"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/media?parent=114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/categories?post=114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.9h1lo.net\/index.php\/wp-json\/wp\/v2\/tags?post=114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}