Ideas or Guide on SQLite Search Engine for a relational database?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Ideas or Guide on SQLite Search Engine for a relational database?

Scott

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ideas or Guide on SQLite Search Engine for a relational database?

Andy Goth-3
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 <[hidden email] 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Ideas or Guide on SQLite Search Engine for a relational database?

Domingo Alvarez Duarte
Hello Scoott !

I have done some work with a prototyping tool for data applications
using sqlite https://github.com/mingodad/db-api-server and use for
navigate several opendata sources like (sorry it is in Spainsh)
https://meimporta.eu look here
https://borme.w.meimporta.eu/bormeMalaga-lm.app .

With this tool we can create links that make easy navigate/filter/search
non trivial amounts of relational data.

I used this repository https://github.com/mingodad/db-api-server for a
talk about prototyping database applications, I hope it can be helpful
for you or other people with similar needs.

Cheers !

On 20/01/2019 18:02, Andy Goth wrote:

> 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 <[hidden email] 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users