Jumper and Sphinx - integrating the best of both worlds

We had a customer who requested some direction on integrating the Jumper 2.0 tagging feature into a number of very large distributed databases. The customer had just implemented the Sphinx search engine on each of these databases.

Many of these SQL databases contained very large numbers of tables. The primary challenge was that many of these tables contained cryptic table and column names that made it very difficult to interpret exactly what the data was in the tables.

They wanted to integrate the Jumper 2.0 bookmarking engine into the Sphinx search engines so that users could apply knowledge tags to the legacy database tables. In this way users could search locally for data and then tag the data they had searched with relevant knowledge tags. In the initial design meeting it was determined that integrating the Jumper tagging fields directly into the Sphinx search interface would be the best approach and storing the tags to a central Jumper mySQL index. Provided below is a quick example of this integration.

The first thing created was a simple form in HTML.

<-html->
<-head->
<-title->Jumper Tagging Fields<-/title->
<-/head>

<-body->
<-form method="post" action="jumper_update.php">

User Name:<-br />

<-input type="text" name="creator_id" size="10" /><-br />

Table Name:<-br />

<-input type="text" name="title" size="40" /><-br />

Description:<-br />

<-input type="text" name="body" size="300" /><-br />

Database Hostname:<-br />

<-input type="text" name="url_title" size="255" /><-br />

Keywords:<-br />

<-input type="text" name="meta_keywords" size="200" /><-br />

Database Location & Access:<-br />

<-input type="text" name="meta_location" size="200" /><-br />

Realted Data:<-br />

<-input type="text" name="meta_link" size="200" /><-br />

Type of Data:<-br />

<-input type="text" name="data_type" size="30" /><-br />

Date:<-br />

<-input type="text" name="date_posted" size="30" /><-br />

// Next we need to add the submit button to the web page. //

<-input type="submit" value="Update Database"

<-/form>

<-/body>
<-/html>


The next step is to create jumper_update.php file. This will update the database with the new knowledge tags that have been applied to the database tables. Create a new file called jumper_update.php

$creator_id = $_POST['creator_id'];
$title = $POST['title];
$body = $POST['body'];
$url_title = $POST['url_title'];
$meta_keywords $POST['meta_keywords'];
$meta_location $POST['meta_location'];
$meta_link $POST['meta_link'];
$meta_datatype $POST['meta_datatype'];
$date_posted $POST['date_posted'];

mysql_connect("localhost", "username", "password") or die ('Error: ' . mysql_error());
mysql_select_db("s_jmp_entry");

$query="INSERT INTO Table (id, creator_id, title, body, url_title, meta_keywords, meta_location, meta_link, meta_datatype, date_posted)VALUES ('NOT NULL','"$creator_id."','"$title."','"$body."','"$url_title."','"$meta_keywords."','"$meta_location."','"$meta_link."','"$meta_datatype."','"$date_posted."')";

mysql_query($query) or die ('Error updating database');

echo "Database Updated With: " .$creator_id. " ".$title." "$body." "$url_title." "$meta_keywords." "$meta_location." "$meta_link." "$meta_datatype." "$date_posted ;

?>

We are working on inserting the knowledge tags directly into the Sphinx index so that users can search data by both full-text and knowledge tags to improve discoverability of the legacy data. We will keep you posted.

-apology for the all the hyphens it was the only way to get the blog to accept the HTML.

Jumper 2.0 is an Open Community

The Jumper 2.0 Open Source Project is a community effort. We invite open participation in this blog. Anyone who is currently using the Jumper 2.0 platform, is currently doing development on the platform, or has just installed the platform please feel free to contribute. To become an author contact sperry@jumpernetworks.com and we will extend author privileges.