Saturday, 26 January 2013

Step 1: Create table structure in MySQL Database



MySQL Table Structure:
delimiter $$

CREATE TABLE `item` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '    ',
`NAME` varchar(450) DEFAULT NULL,
`MANU` varchar(450) DEFAULT NULL,
`WEIGHT` float DEFAULT NULL,
`PRICE` float DEFAULT NULL,
`POPULARITY` int(11) DEFAULT NULL,
`INSTOCK` tinyint(4) DEFAULT NULL,
`INCLUDES` varchar(450) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$

delimiter $$

CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$

delimiter $$

CREATE TABLE `feature` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_feature_1` (`item_id`),
CONSTRAINT `fk_feature_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$


delimiter $$

CREATE TABLE `item_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CATEGORY_ID` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_item_category_1` (`CATEGORY_ID`),
KEY `fk_item_category_2` (`item_id`),
CONSTRAINT `fk_item_category_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_item_category_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$

Sample Data in MySQL:

insert into item values(id,'item 1','item 1 manu','1.2','100.23','1',1,'includes item 1');
insert into feature values(id,'feature item 1',1);
insert into category values(id,'music');
insert into item_category values(id,1,1);



Step 2: Register the Handler in solrconfig.xml




Path: "mySolr/solr/example/solr/conf/solrconfig.xml"

<requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">
<lst name="defaults">
<str name="config">data-config.xml</str>
       </lst>
</requestHandler>



Step 3: Create data-config.xml and add the following content




Path: "mySolr/solr/example/solr/conf/"

<dataConfig>
    <dataSource driver="com.mysql.jdbc.Driver" type="JdbcDataSource"
       url="jdbc:mysql://10.2.5.130:3306/wekho" user="root" password="" />
    <document name="products">
       <entity name="item" query="select * from item">
           <field column="ID" name="id" />
           <field column="NAME" name="name" />
           <field column="MANU" name="manu" />
           <field column="WEIGHT" name="weight" />
           <field column="PRICE" name="price" />
           <field column="POPULARITY" name="popularity" />
           <field column="INSTOCK" name="inStock" />
           <field column="INCLUDES" name="includes" />

           <entity name="feature" query="select description from feature where item_id='${item.ID}'">
               <field name="features" column="description" />
           </entity>
           <entity name="item_category" query="select CATEGORY_ID from item_category where item_id='${item.ID}'">
               <entity name="category" query="select description from category where id = '${item_category.CATEGORY_ID}'">
                   <field column="description" name="cat" />
               </entity>
           </entity>
       </entity>
    </document>
</dataConfig>

Step 4: Add the required JDBC Connector jar file




Path: "mySolr/solr/example/lib/"

In our case we used MySQL so we added : mysql-connector-java-5.1.20.jar


Step 5: Run Commands




Path: "mySolr/solr/example/"

  • Run the Apache Solr server from example directory:
    java -jar start.jar

  • Loading data:
    • Using URL :
OR

    • DIH Admin Console :

  • To check the data added in Solr:

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Once the list has been developed, go over the list to determine which data elements are missing and, equally important, which data elements are not needed. Superfluous data extracts a high price in a computerized database - more input, more disk space, more maintenance to keep the data current.oracle dashboards

    ReplyDelete

Find me on Facebook! Follow me on Twitter!