On 1/11/07, Ryan McKinley <[EMAIL PROTECTED]> wrote:
I'd like to be able to add/update documents from an SQL query.
Me too... it's been on the todo list a long time. A lot of people have data in databases, and it's a shame to require code to index their data if it can be expressed in SQL. If it were less common, I'd say it would be better as a standalone app talking to Solr over XML/HTTP, but given that it's *such* a common case, I'd support it going into the core. I'd envision query args instead of XML though... something that could be generated by a browser. overwrite=true&sql=SELECT * FROM my_stats_table&etc The big question in my mind is if the database schema is simple enough for something like this to work... esp w.r.t multi-valued fields. Multiple values in a database may be in multiple rows... can we handle that case somehow? What types of joins can we handle? -Yonik
<addFromSQL mode="add or replace fields" connection="jdbc:mysql://localhost/nblmc?username=xxx&password=xxx" driver="com.mysql.jdbc.Driver" multifieldSeperator="\n" > SELECT * FROM my_stats_table </addFromSQL> This would use the the column names as the field name, and the cell value.toString() as the field value. If the schema says the field can have multiple values AND a multifieldSeperator is defined, it will split the value on that string. To get intended results, you may need to use the 'AS' command and perhaps format the cells using SQL. For example: SELECT itemID AS id, name, DATE_FORMAT( addedTime, '%Y-%m-%dT%H:%i:%s.000Z' ) Should this be an implemented as an Update Plugin? or added directly to the DirectUpdateHandler. If it should be an UpdatePlugin, how do i get started? thanks ryan