About Me
Blog
RESTful Web Services
Amazon S3 Essentials
Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Saturday, 26 January 2013

Redhat - Simple Example With MySQL DB

07:43 1 comment
STEP 1) Create project “mysqldsTest” and create “mysqldsTest.war” file

STEP 2) Create “mysqldsTest” App Openshift:

$ rhc app create -a mysqldsTest -t jbossas-7

STEP 3) Before starting adding content removing the current pom.xml and other artifacts
created at application creation is required:

$ git rm -r src pom.xml

$ git commit -m "removing artifacts from application creation phase"

STEP 4) Copy the “mysqldsTest.war” & “mysql-connector-java-5.1.20.jar” into “mysqldsTest/deployments/”

STEP 5) Add to your openshift git commit and push:

Now that you have a war file ready for deployment (mysqldsTest/deployments/mysqldsTest.war)

lets add it to the git repository and then commit and finally we will push the war to the openshift express instance.

Open a terminal window and switch the working directory to the application’s deployments
directory then execute the following commands:

$ cd deployments/

$ git add mysqldsTest.war

$ git commit -m "depolying myapp application" mysqldsTest.war

$ git push

Note that git repository is created at the root project level so all files can be added to git and not only the deployment.

STEP 6) 2 Ways To access MySQL Database:

    a) Using PhpAdmin:
        - Click on “Add Cartridge +” and Add “phpMyAdmin 3.4”
    - Save the Credentials.

b) Using SSH (Open Terminal and Execute following commands):

   
You will be now at “[mysqldsTest-jsr10.rhcloud.com ~]\>” path (i.e. “mysqldsTest” Applications root directory)
    [mysqldsTest-jsr10.rhcloud.com ~]\> mysql
       
        You will be now at “mysql>” path

mysql> show databases;

STEP 7) Your push completed and you application should be available at the following
openshift URL:

http://mysqldstest-jsr10.rhcloud.com/mysqldsTest/index.jsp

Read More

Import MySQL database with Apache Solr - Delta Import

07:04 No comments

Step 1: Create table structure in MySQL Database



MySQL Table Structure:

CREATE TABLE `apache_solr_delta_import`.`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,
`last_modified` TIMESTAMP,
PRIMARY KEY (`ID`)
);

CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`last_modified` TIMESTAMP,
PRIMARY KEY (`id`)
);

CREATE TABLE `feature` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`last_modified` TIMESTAMP,
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
);

CREATE TABLE `item_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CATEGORY_ID` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`last_modified` TIMESTAMP,
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
);

Sample Data in MySQL:

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




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://localhost:3306/apache_solr_delta_import" user="root" password="root" />
    <document name="products">
       <entity name="item" pk="ID" query="select * from item"
               deltaImportQuery="select * from item where ID = '${dataimporter.delta.id}'"
               deltaQuery="select id from item where last_modified &gt; '${dataimporter.last_index_time}'">
               <entity name="feature" pk="ITEM_ID"
                   query="select DESCRIPTION as features from feature where ITEM_ID = '${item.ID}'"
                   deltaQuery="select ITEM_ID from feature where last_modified > '${dataimporter.last_index_time}'"
                   parentDeltaQuery="select ID from item where ID = ${feature.ITEM_ID}"/>


           <entity name="item_category" pk="ITEM_ID, CATEGORY_ID"
                   query="select CATEGORY_ID from item_category where ITEM_ID = '${item.ID}'"
                   deltaQuery="select ITEM_ID, CATEGORY_ID from item_category where last_modified > '${dataimporter.last_index_time}'"
                   parentDeltaQuery="select ID from item where ID=${item_category.ITEM_ID}">
               <entity name="category" pk="ID"
                       query="select DESCRIPTION as cat from category where ID = '${item_category.CATEGORY_ID}'"
                       deltaQuery="select ID from category where last_modified &gt; '${dataimporter.last_index_time}'"
                       parentDeltaQuery="select ITEM_ID, CATEGORY_ID from item_category where CATEGORY_ID=${category.ID}"/>
           </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:
    • DIH Admin Console :

  • To check the data added in Solr:

  • Add New Data now in all table and check again
        insert into item values(id,'item 2','item 2 manu','1.2','100.23','1',1,'includes item 2',CURRENT_TIMESTAMP);
insert into feature values(id,'feature item 2',2,CURRENT_TIMESTAMP);
insert into category values(id,'electronics',CURRENT_TIMESTAMP);
insert into item_category values(id,2,2,CURRENT_TIMESTAMP);
http://localhost:8983/solr/select/?q=*%3A*&version=2.2&start=0&rows=10&indent=on
Read More

Import MySQL database with Apache Solr - Full Import

07:03 3 comments

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:
Read More
Subscribe to: Posts (Atom)
Find me on Facebook! Follow me on Twitter!