Jump to content


Photo

A new Forum for SQLite interfaces to RM data


  • Please log in to reply
24 replies to this topic

#1 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 17 December 2009 - 12:37 PM

This is to request the creation of a new forum under the RootsMagic root for the collection of topics on interfacing to the RM SQLite database for queries, reports and data modifications that are difficult or impossible to carry out from within the RootsMagic application. A suggested title would be "SQLite Tools".

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#2 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 17 December 2009 - 01:25 PM

This is to request the creation of a new forum under the RootsMagic root for the collection of topics on interfacing to the RM SQLite database for queries, reports and data modifications that are difficult or impossible to carry out from within the RootsMagic application. A suggested title would be "SQLite Tools".


I would like to see that too.

#3 JackieB

JackieB

    New Member

  • Members
  • Pip
  • 3 posts

Posted 18 December 2009 - 08:52 AM

This is to request the creation of a new forum under the RootsMagic root for the collection of topics on interfacing to the RM SQLite database for queries, reports and data modifications that are difficult or impossible to carry out from within the RootsMagic application. A suggested title would be "SQLite Tools".


I would like this as well.

#4 MVS

MVS

    Advanced Member

  • Members
  • PipPipPip
  • 326 posts

Posted 22 December 2009 - 04:26 AM

I would like this also. MVS

#5 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3551 posts

Posted 22 December 2009 - 04:37 AM

I would like this also. MVS

I would also, but it goes without saying that it needs to carry a pinned strong warning/disclaimer.

Keeping ones customers and their important views at a distance is never a good approach

 

User of Family Historian 7.0, Rootsmagic 7.6.3

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#6 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 30 December 2009 - 09:10 AM

Bruce may not be comfortable hosting this as he might not want to have the headaches associated with the problems that can be caused.

We could start a bulletin board at Yahoo Groups or somewhere else. Our purpose, as I understand what is desired, is simply to enhance our use of RM4 with things like advanced queries and reports.

To kick this off, I've put a few reference items on line at:
http://mykindred.com/RM4/

.... This is just to get it started -- I am NOT interested in hosting this on my site or in being anything other than a contributor.

Tom

#7 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 30 December 2009 - 04:06 PM

Well, it has been almost two weeks since I posted this Wish and sent a PM to Bruce. I also sent a PM to Michael later on requesting the same thing. So maybe your observation is right, given the lack of response, Admittedly, it is a holiday period which could slow things down.

A Yahoo group would be an improvement for storing files and message attachments but its message archive search engine sucks. I'm an experienced Yahoo group owner/moderator and could readily set one up but would want at least one other co-owner in case something happens to me. I'm not familiar with other groups like MSN's.

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#8 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 30 December 2009 - 05:59 PM

I have added more data. This isn't my field of expertise.
Let me know if you have anything to contribute.

http://mykindred.com/RM4/

#9 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 30 December 2009 - 06:03 PM

Well, it has been almost two weeks since I posted this Wish and sent a PM to Bruce. I also sent a PM to Michael later on requesting the same thing. So maybe your observation is right, given the lack of response, Admittedly, it is a holiday period which could slow things down.

A Yahoo group would be an improvement for storing files and message attachments but its message archive search engine sucks. I'm an experienced Yahoo group owner/moderator and could readily set one up but would want at least one other co-owner in case something happens to me. I'm not familiar with other groups like MSN's.



I also do not care for the Yahoo Groups format. We could set something up on rootsweb but it wouldn't be password protected.

#10 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 30 December 2009 - 07:41 PM

I have added more data. This isn't my field of expertise.
Let me know if you have anything to contribute.

http://mykindred.com/RM4/


Good stuff, Tom! You have made a great start on documenting the database with these structural reports and the beginnings of data definitions. What SQLite manager are you using for this?

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#11 Romer

Romer

    Advanced Member

  • Members
  • PipPipPip
  • 2092 posts

Posted 30 December 2009 - 08:36 PM

Tom, besides FactTypeTable, the only other non-system table not specific to a user's data entry seems to be found in RoleTable, which you might also want to add to your page. I believe these were the two tables that I identified when I first began looking at the RM4 database structure a few weeks or so ago. I think that you'll want to pick up those rows in RoleTable with RoleID < 59 (similar to FactTypeID < 1000 that you would've used for FactTypeTable). This number may change in the future if additional built-in roles are added.

Actually, some of the places in PlaceList seem to have been built-in, but I'm not sure that they were intended to be so due to the limited number of entries.

I recognize that you'll be limiting the amount of data to your website, but it may be a helpful start for others who might have any interest and for wherever this information may ultimately end up! Thanks.

#12 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 30 December 2009 - 10:57 PM

I also do not care for the Yahoo Groups format. We could set something up on rootsweb but it wouldn't be password protected.


I had a stab at Wikispaces but it took a lot more effort than a Yahoo group. I guess that is because it is supposed to do more with discussion tied to pages, etc. Have a look at http://sqlitetoolsfo...wikispaces.com/. There is some limited control over who sees/does what in the free subscription.

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#13 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 31 December 2009 - 05:56 AM

To answer the question of what I used to get the data from the RM4 database -- I just fiddled around with several yesterday morning. The one that seemed to work best for me was SQLite2009 Pro, but I hadn't tried the highest recommended on Tom's page:
http://sqlitetoolsfo.../SQLiteManagers

From what I saw of the queries already there, it looks like you're already making progress -- what is it that is wanted? ... more queries? ... ?

WRT the post about the Role table -- please send whatever you think might be useful to me via email and I'll add it. (tcloud@@austin.rr.com remove the extra '@')

The index page http://mykindred.com/RM4/ and the page of table structures aren't what I had envisioned -- I intended to break it up into one web page per table, add columns for field definitions and notes and we can collaborate on the function of each field.

There doesn't seem to be a lot of interest in this. From the log of hits on the pages I put up, there seems to be maybe half a dozen different IPs that visited them. IOW there's no need for Bruce to devote time to something only a dozen or so users will use, IMO.

** One thing I requested be added when I beta-tested the DNA function of RM4 was the ability to batch data into the program. That is a query I'd like to see now -- DNA data versus person ID number and whatever else is necessary. Having to enter each Y-STR value one at a time is a drag, and especially when you might have 20 or more participants in a group.

#14 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 31 December 2009 - 06:42 AM

What is RMNOCASE in the field definition? I'm assuming this is a variation on NOCASE defined for RootsMagic?

#15 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 31 December 2009 - 06:55 AM

What is RMNOCASE in the field definition? I'm assuming this is a variation on NOCASE defined for RootsMagic?


Yes. And because it is not compiled into the 3rd party SQLite managers, they raise all kinds of flags and one insurmountable barrier - you cannot UPDATE a table that has a field set to a RMNOCASE collation.

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#16 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 31 December 2009 - 07:09 AM

From what I saw of the queries already there, it looks like you're already making progress -- what is it that is wanted? ... more queries? ... ?

Not only. This could be the website that you don't want to host. Because we both were negative about Yahoo groups for this and Wikispaces is supposed to support a collaborative wiki, I thought we could give it a try.

The index page http://mykindred.com/RM4/ and the page of table structures aren't what I had envisioned -- I intended to break it up into one web page per table, add columns for field definitions and notes and we can collaborate on the function of each field.

Do that on the wiki?

There doesn't seem to be a lot of interest in this. From the log of hits on the pages I put up, there seems to be maybe half a dozen different IPs that visited them. IOW there's no need for Bruce to devote time to something only a dozen or so users will use, IMO.

Creating a new forum on the RootsMagic IPBoard would take 5 minutes at most. Bruce and Michael would have to make no commitment of time beyond that but it would be nice to see some support of the 'open database' concept. To start, what development tool(s) do they use. Could we get a tool with RMNOCASE compiled? Short of that, a copy of the RMNOCASE collation sequence.

** One thing I requested be added when I beta-tested the DNA function of RM4 was the ability to batch data into the program. That is a query I'd like to see now -- DNA data versus person ID number and whatever else is necessary. Having to enter each Y-STR value one at a time is a drag, and especially when you might have 20 or more participants in a group.

Depends what table the data goes into. If it has a RMNOCASE collated field in it, it's a non-starter unless we can get a tool that has SQLite extended with RMNOCASE.

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#17 Romer

Romer

    Advanced Member

  • Members
  • PipPipPip
  • 2092 posts

Posted 31 December 2009 - 07:34 AM

WRT the post about the Role table -- please send whatever you think might be useful to me via email and I'll add it.

Tom, I'll send a CSV file of RoleTable built-in data to you shortly. The roles won't be of as of much widespread interest as the fact types, but may be of interest to some.

*edited
I should add that the sentences are the unmodified sentences provided with the RM4 software.

#18 tcloud

tcloud

    Member

  • Members
  • PipPip
  • 24 posts

Posted 31 December 2009 - 09:44 AM

I've added to http://mykindred.com/RM4/
- Role Table
- notes for various field definitions
- link to the RM4 SQLite Wiki
- other stuff ...

I have called the Role Table "Role Types" ... does that seem the best description?

Please email me comments, additions, corrections .... I am not using RM4, so the field definitions I put there are based on past experience and could be incorrect.

#19 Romer

Romer

    Advanced Member

  • Members
  • PipPipPip
  • 2092 posts

Posted 31 December 2009 - 10:05 AM

Tom, I've mapped out just about all the tables/fields, so can supply you additional data for the Notes column under your various Table Structures links. I'll see what I can put together for you once I get a bit of additional free time.

I can't find sources for some of the tables and fields, however, which could be partly due to my not taking advantage of all the various product features available (or due to them not actually being populated within the program (or them being populated with the same value)), so would appreciate help from other users.

Perhaps it might even be useful for those of us interested to review a couple tables a day, including what I provide you. We could start in order from the top down and knock it all out in about a week-and-a-half. Due to not using RM4, you're exempted if you wish!

There are twenty-two non-system tables in total. I'd actually first be interested to hear from people as to which of your database tables contain no data. Mine is LinkTable, but wonder if the table could be related to use of the nFS-integrated features. Speaking of which, I hope that we have some people who use nFS and RM4 LDS support who are helping out with looking over the definition of these various tables and fields.

#20 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6444 posts

Posted 31 December 2009 - 11:24 AM

Thinking that a Wiki type of website would be the appropriate forum in which we can collaborate on:
  • documenting the RootsMagic database structure and dictionary
  • developing SQL queries
  • reviewing SQLite tools
  • developing custom reports

I set up a a Wiki titled SQLite Tools for RootsMagic 4 on Wikispaces at http://sqlitetoolsfo...wikispaces.com/.

Please join. As a member, "You'll be able to easily edit pages, upload files, and join our discussions" (quote from standard invitation).

Discussions cluster around pages maintaining a focus. Members can revise existing pages or create new ones (I think). A history of revisions is logged.

I'm new at this and have set up just a skeleton beginning. tccloud's website content could be transferred there and other members can pitch in to elaborate the tables.

2GB of storage is allowed in this free subscription I have taken so there's plenty of space for this new forum.

Tom user of RM7630 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.