I would like to build a search engine in my application. For this I have a text box where the user can input any text. I would like to search (%like%) for the text the user has provided against all tables/columns in the db.
is there any design pattern for which I should use to achieve this? I don't wish to build a query with m*n (tables X columns) with any %like% statement.
I don't think we have design pattern for this (at least I am not aware of) What will you do with the search result? If you need to search through more than one column the best way is to have concatenated value of all the key columns as one column or through view and search the result. However if the query is based on the view there is possibility that this query may never return or timeout.
If this search feature is really important the best way I would do is - Create a new column with concatenation of all the key columns - Create text/interMedia index (I assumed you were using Oracle from syntax %LIKE%)
I don't wish to build a query with m*n
You don't have to manually write the SQL, you can generate the SQL using the meta data tables like ALL_TABLES, ALL_TAB_COLUMNS (in Oracle) . I wouldn't try this as this wouldn't give me any meaningful information without right indexes.
Explore the text index it has lot for features than simple string match. [ April 06, 2007: Message edited by: Purushothaman Thambu ]