You can try reading the Fossil source code to see how it handles full-text searching across multiple tables.
https://fossil-scm.org/index.html/artifact?fn=src/search.c&ci=trunk On Sat, Jan 19, 2019, 06:10 Scott <scottvall...@yahoo.com wrote: > > I apologize, I sent this from a different email than I registered > accidentally. > Hi Everyone! > The Ask and what I’ve done: > I'm building a research database with an embedded SQLite DB using Intellij > for development. I've created the data entry point for the users; however, > I'm looking for anything anyone might want to share or suggest as a code, > link, or design, whatever it may be, on developing a search engine for a > relational database. I’ve been reading through tutorials on > SQLiteTutorials.net to learn a little more about indexing and searching. > I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having > difficulty finding anything that may assist with a multi-table search. I > guess it would nice to have an example to follow. I wouldn’t think this > would be the first-time for this, but I’m a little naïve too. > > I'm not asking for someone to solve the problem but just provide a > direction that may save a lot of research time. I don’t know if my images > will post or be available through this email, but I've provided some images > below of the GUI and the SQLite DB which has 8 tables all linking back to > the primary table called "Source". This is a new task for me and my > development skills in Java. > > The Goal: > This Research DB users want to be able to search the database either by > Source, Topic, Question, Comments, Quotes, Authors, which most are a one to > many with one a many to many relationship. A single source, for example a > book entered in the database, could be linked to multiple topics, > questions, Comments, Quotes, etc. So the users may want to search each of > these separately on a particular subject, or possibly all the tables for a > particular subject. Topic will help the mostly, but a topic may be > addressed in a comment or quote, etc, that may not be associated directly > with a particular topic. So there is a need to search a number of fields > over 8 tables. My thoughts are that the search criteria should probably > return into a table list, then when the user selects a particular row, this > data would populate into corresponding fields for readability similar to > the "Entry" tab (see below). > > The only thing I suspect may make this easier than expected is that no > matter whether the user searches by Topic, or Question, or Comment, etc, > the search will always join all the tables and return all the fields for > anything linked back to a source. However, the source will be associated to > many of each of those. > > Thanks, > > Scott > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
