Thanks, Scott. It isn't what I was hoping for, but it was what I was expecting. I really have two problems here, and you touched on them both. First, I have a potentially large blob with HTML in it that needs to be preserved. But it also needs to be searched for plain text. Ideally, an fts2 search would be best. The table size will probably not be humungous for the most part, but I want to allow plenty of room for it to get as big as needed, so I have to take that into consideration when designing a solution.
The best solution is similar to what you suggested. First, to create a virtual table for fts2, but using a JavaScript Regexp to strip the HTML tags before putting them into it. The doing the search with fts2, and linking it back to the records in the original table to display the data. The problem would be if the table got really big. It could take a while to do a fairly trivial search. But for normal cases, it shouldn't be too bad. I expect the typical table size to be only a few megabytes, and that copies pretty quickly. Anyway, thanks for the confirmation that I'm not missing anything that would be helpful. On Oct 7, 5:56 pm, "Scott Hess" <[EMAIL PROTECTED]> wrote: > Your understanding is correct. The SQLite embedded w/in Gears has no > regexp support. There's a bug around adding such support: > http://code.google.com/p/gears/issues/detail?id=632 > > I'm not certain what kinds of queries you want to do. For smaller > tables, you could just do a full table scan and process in JavaScript. > Regular-expression matches are anyhow generally not going to be able > to use an index, so they'd be doing a (more efficient) full table scan > w/in SQLite. > > For larger tables, you might want to use an fts2 table, with all of > the data, then do a post-processing step using JavaScript only. > Basically the fts2 table would act as a filter to keep you from > needing to scan the entire table. Depending on the types of queries > you're doing, this might not be a big gain until things are pretty big > (depends on the proportion of rows being matched before > post-processing). > > -scott > > On Mon, Sep 29, 2008 at 2:32 PM,renaissanceGeek > > > > <[EMAIL PROTECTED]> wrote: > > > I have a db app in which one column contains a blob of HTML code. I > > want to be able to query the text only by filtering the HTML tags > > first. Obviously, I can make a copy of the whole table using > > JavaScript to filter the tags, then query the copy. But that solution > > is too ugly to live. The idea would be to include the same (or > > eqivalent) REGEXP that works with JavaScript right in the SELECT > > command. However, as far as I can tell, while SQLite does allow for a > > REGEXP, it is not implemented by default. Instead it has to be added > > as a user function, which is prohibited by Gears for security reasons. > > > First, is my understanding of these facts correct, or have I missed > > something? And if I am correct, is there a work around that doesn't > > involve making a copy of the table (or a major portion of it)? If I'm > > missing something, please clarify and give an example or two I can use > > to meet my needs. > > > Thanks- Hide quoted text - > > - Show quoted text -
