Jump to content


Photo

A platform to share queries - ask questions

SQL Queries

  • Please log in to reply
4 replies to this topic

#1 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 19 May 2019 - 02:47 AM

I opened a gedcom from MyHeritage.com (+5000 people all imported from shared trees, no consistency and lot of mistakes).

 

I have spend quite some time on those forums to find what were the best practices, used in-build tools to clean duplicates, letters capitalisation, etc. but it quickly becomes necessary to download an SQL software such as SQLiteSpy to run queries on the entire database.

 

I found a few ones on SQLite tools for Rootsmagic (https://sqlite4roots...space/home.html), great to add weddings fact to all couples and had to spend like an hour per query to modify them. I wanted for example an alt Name (married) "Mrs. wife.firstname husband.lastname", a death fact to all person not living (who had no death fact), add a * to all suffix to identify they were imported from MyHeritage (unreliable), put about (abt) in front of all dates, remove all 'Y' in details, etc. 

 

I have poor knowledge of SQL (only through Microsoft Access but advanced in VBA), so this is only through testing simple modifications that I can  achieve what I want to so far. I am pretty sure a few people have already written those or some other people would be interested in mine. There seemed to be a wiki but links are broken.

 

I would love to have a querry group, like RootsMagic Wish List (or stack overflow), where we could share our queries and ask-answer rootsmagic-sql specific questions


Rootsmagic 7.5.9.0 with a lot of SQL queries (SQLiteSpy) and a bit of Family Historian 6.2 (tree view and map)


#2 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 19 May 2019 - 04:57 AM

I would love to have a querry group, like RootsMagic Wish List (or stack overflow), where we could share our queries and ask-answer rootsmagic-sql specific questions

 

This is something I believe Rootsmagic should embrace in the future and it would effectively take some of the pressure off development for many little needs leaving Rootsmagic to improve the program core and GUI's

 

I say that as SQL is the preserve of the few as regards Rootsmagic and a competitor software package actually host a Plugin Store allowing users to submit and download plugins from there, effectively what TomH provides. The difference here is those downloaded PlugIns can be run from within the competitor software making them available to all and not the few. I would imagine the vendor does some validation on the scripts before hosting them on their site and whilst still becoming familiar with the community surrounding that software this appears to be a very welcome additional tool.

 

The scripts from TomH have filled many gaps for many users but again only the few who are brave enough to go there, so maybe Rootsmagic will consider facilitating the running of validated scripts from within Rootsmagic 8.


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#3 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3590 posts

Posted 19 May 2019 - 05:24 AM

The SQLite for RootsMagic site has moved to https://sqlitetoolsforrootsmagic.com/  The old wikispaces site where it was hosted shut down, so it had to move.

 

The site does support discussions and the sharing of queries. Most of the queries are native SQLite, but the site does include information about running queries from Microsoft Access and more generally about using OBDC with SQLite so that any database engine plus things like Microsoft Excel can be used to run queries. The site is managed by Tom Holden who also provides the majority of the queries. RootsMagic staff does not participate in the site. I do not know if they monitor it or not.

 

I think the ability to run SQLite queries from within RM itself would be an excellent enhancement to the product, making the power of SQLite queries available to many more RM users than is currently practical. However, running SQLite queries from within RM itself would really only be useful if RM itself became much less modal in its operation. Right now, running SQLite queries outboard means you can run your query in one window, make changes in RM in another window, run your query again in the first window, and so on back and forth. It's a very useful way to work. Very preliminary indications from one RM8 demo are encouraging in regard to non-modal operation but the demo was very brief. Time will have to tell how non-modal RM8 really proves to be, but as I say, I'm very encouraged.

 

Jerry



#4 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 663 posts

Posted 20 May 2019 - 07:58 AM

Bruce Buzbee was the featured speaker at the RootsMagic seminar at the Rutherford Hayes Presidential Library two weeks ago.  He had a brief demo of RM Ver 8.  He demonstrated the non-modal features they are working on.  As a positive sign, the demo worked smoothly and did not crash.  He said they might release RM8 the next day or as far in the future as the end of December.



#5 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 20 May 2019 - 01:10 PM

I think the ability to run SQLite queries from within RM itself would be an excellent enhancement to the product

 

Indeed, next to Tools menu, another menu "Queries" (taking all .sql stored in a specific folder) or even some icons linked to our top .sql would be awesome. 


Rootsmagic 7.5.9.0 with a lot of SQL queries (SQLiteSpy) and a bit of Family Historian 6.2 (tree view and map)