Indexing data from database

Question

I am trying to index data from database. I am doing that successfully. But I have a problem. I want to use one index for whole database. All the db tables have at least 3 columns with the same name (I want to be like this). For instance I have these tables: members, new_members, books, journals and cds. All these have columns named: id, model, biog. So, in all the db tables the id (auto incremented) starts from 1. When I am querying Solr using filter (fq=model:journal) it is returning nothing. Querying for books returns a portion of data (I have 5 rows and it returns 2. I am using : in order to retrieve all the rows ). I know that the data is in Solr’s “data” file. I think there is a conflict of some kind. How can I have a single index with all these tables without any conflicts?

The data-config.xml:

<?xml version="1.0" encoding="utf-8"?>

<dataConfig>

  <dataSource type="JdbcDataSource" 
              driver="com.mysql.jdbc.Driver"
              url="jdbc:mysql://127.0.0.1:3306/rental" 
              user="root" 
              password="1a2b3c4d"/>

  <document>

    <entity name="members" pk="id" transformer="HTMLStripTransformer" query="select id, firstname, lastname, biog, model from members">
        <field column="id" name="id" /> 
        <field column="firstname" name="firstname" stripHTML="true" />
        <field column="lastname" name="lastname" stripHTML="true" />
        <field column="biog" name="biog" stripHTML="true" />
        <field column="model" name="model" stripHTML="true"  />
    </entity>

    <entity name="new_members" pk="id" transformer="HTMLStripTransformer" query="select id, firstname, lastname, biog, model from new_members">
        <field column="id" name="id" /> 
        <field column="firstname" name="firstname" stripHTML="true" />
        <field column="lastname" name="lastname" stripHTML="true" />
        <field column="biog" name="biog" stripHTML="true" />
        <field column="model" name="model" stripHTML="true" />
    </entity>


  <entity name="books" pk="id" transformer="HTMLStripTransformer" query="select id, title, description, model from books">
        <field column="id" name="id" /> 
        <field column="title" name="title" stripHTML="true" />
        <field column="description" name="biog" stripHTML="true" />
        <field column="model" name="model" stripHTML="true" />
    </entity>


  <entity name="journals" pk="id" transformer="HTMLStripTransformer" query="select id, title, description, model from journals">
        <field column="id" name="id" /> 
        <field column="title" name="title" stripHTML="true" />
        <field column="description" name="biog" stripHTML="true" />
        <field column="model" name="model" stripHTML="true" />
    </entity>


  <entity name="cds" pk="id" transformer="HTMLStripTransformer" query="select id, title, description, model from cd">
        <field column="id" name="id" /> 
        <field column="title" name="title" stripHTML="true" />
        <field column="description" name="biog" stripHTML="true" />
        <field column="model" name="model" stripHTML="true" />
    </entity>

  </document>
</dataConfig>

The schema.xml (fields):

<fields>
  <field  name="id" type="string" indexed="true" stored="true" /> 
  <field  name="model" type="text_en" indexed="true" stored="true"  />
  <field  name="firstname" type="text_en" indexed="true" stored="true"/>
  <field  name="lastname" type="text_en" indexed="true" stored="true"/>
  <field  name="title" type="text_en" indexed="true" stored="true"/>
  <field  name="biog" type="text_en" indexed="true" stored="true"/>
</fields>
<uniqueKey>id</uniqueKey>
<defaultSearchField> biog </defaultSearchField> 

Answer 1

your uniqueKey is not really unique, you need each entity to have different pk than the others, one easy way is just to append something to the id, for example, for books:

query="select CONCAT('b-',id) as id,..."

this way book ids will be b-1, b-2, do the same for the other tables with different prefixes