Query-based Verity collections

[brian_s]

I am looking into using Verity to search my database rather than using
on-demand querying (written and working). I have several questions regarding
the management of Verity query-based collections: 1. Since this is an active
database and has 50+ tables and well over 100K rows, how often should I update
the collection? I don't think it will be possible after a new entry into the db
because from what I understand by reading the available documentation, I have
to re-run the query to repopulate the collection. Is this true? Since this
database is in 3rd normal form, this could take quite some time to fill the
collection with the data I need (multiple joins across multiple tables).
Initial tests on my dev server to create a collection with just a subset of the
data I need takes well over an hour... 2. Once the collection has been
created, is it now available for use by all that need it? Surely I don't have
to create the collection for each logged in user? 3. If certain users are not
allowed to access certain data, how do I prevent that within the collection? Is
it even possible? Or should I create collections that contain only the data
allowed to be searched and manage access by roles (this is what I think I
should do). Any help here would be appreciated...

[mpwoodward *TMM*]

brian_s wrote:

> I am looking into using Verity to search my database rather than using
> on-demand querying (written and working). I have several questions regarding
> the management of Verity query-based collections: 1. Since this is an active
> database and has 50+ tables and well over 100K rows, how often should I update
> the collection? I don't think it will be possible after a new entry into the db
> because from what I understand by reading the available documentation, I have
> to re-run the query to repopulate the collection. Is this true? Since this
> database is in 3rd normal form, this could take quite some time to fill the
> collection with the data I need (multiple joins across multiple tables).
> Initial tests on my dev server to create a collection with just a subset of the
> data I need takes well over an hour... 2. Once the collection has been
> created, is it now available for use by all that need it? Surely I don't have
> to create the collection for each logged in user? 3. If certain users are not
> allowed to access certain data, how do I prevent that within the collection? Is
> it even possible? Or should I create collections that contain only the data
> allowed to be searched and manage access by roles (this is what I think I
> should do). Any help here would be appreciated...
>

You'd have to do some tests and get some timings, but with that much
data the updates can take some time. I'm working on a project right now
where we're creating a single front end to search multiple disparate
sources, and we have lots of Verity collections involved. In the cases
where we're creating Verity collections from database content we're only
reindexing Verity once a day and just informing the users that in some
cases the data may be a day behind, but pointing them to the direct
source for more current information.

To answer your questions more specifically ...

1. Yes, you do have to reindex the Verity collections to get the
newly-inserted or updated data from the database. Once the Verity
collection is created from the database records the two are completely
independent of one another.

2. The collections are available to all users server-wide.

3. Restricting access to specific records within a single collection
wouldn't really be feasible (at least not based on what I've done with
Verity). The approach of splitting things up into multiple collections
is probably your best bet, because then you can restrict things when you
build the collections from the database content and control who can
search what collections in your application itself.

This sort of thing is always a challenge--balancing the need for
"real-time" data vs. server resources, etc. Unfortunately there isn't
really a blanket solution and it really depends on the needs of the
users in combination with the available resources you have.

Matt

--
Matt Woodward
Team Macromedia - ColdFusion

[Josho]

What about action="Update" and action="Delete"? I thought <CFINDEX> allowed you
to dynamically update your index with these actions?

That being said, I am trying to implement that in an app right now, and I'm
having much difficulty. The action="Delete" seems very simple, yet when I
execute it, nothing happens. What in the world???

So are you saying that the only way to update an index is to completely
reindex because this functionality doesn't work?? Please tell me this isn't so..

Josh

[brian_s]

Matt, Thanks for your response. I am also trying to figure out how to
differentiate the fields from the original query using <cfsearch>. I thought
that the category attribute might have something to do with it, but I can't
seem to figure out how. What I want to do is use Verity to search through text
in a 'description' field (among others spread through several db tables) and
then display the result set in a field delimited table (with drill-downs into
more detail). Any thoughts? It looks as if Verity is going to need a lot of
offline processing time to keep up to date. I don't know if we can afford to
not have updates available the next day. As far as restricting views into the
database, that can be done, as you say, with multiple collections based on the
user role. I don't see a problem with that. What I see as a problem is, again,
processing all the different collections.

[Tom Jordahl]

Yes, you can use the action="Update" to update a single record in a
collection that has thousands of documents (which are rows in this
scenario).

Action="Delete" works by removing the document with the key specified in the
Key attribute. This value must match the key returned from cfsearch
exactly.

Use the new CFMX 7 status attribute to see if and how many documents were
deleted with the Delete action.

--
Tom Jordahl
Macromedia Server Development

[Tom Jordahl]

Brian,

Once you create the collection and populate it with data, you should be able
to index a smaller subset of data, which you could generate with a SQL query
that returns the updated rows.

--
Tom Jordahl
Macromedia Server Development