Database Properties List – Query #databaseproperties

Problem

Currently under RootsMagic 4, the only way to obtain the Database Properties for a file is to go to File > Properties and use Alt+Prnt Screen (or Ctrl+Prnt Scrn) to copy the screen to the clipboard and use CTRL+V to paste the contents to a program capable of rendering the result.

Solution pt 1

In the meantime, the following code can be used to set up and populate a temporary table (outside of one’s database): (the full solution is integrated in RMtrix_tiny_check.png)

CREATE TEMP TABLE VariableTable
(
Variable TEXT,
VALUE INTEGER
);
 
INSERT INTO VariableTable
SELECT 'People', COUNT(1)
FROM PersonTable;
 
INSERT INTO VariableTable
SELECT 'Families', COUNT(1)
FROM FamilyTable;
 
INSERT INTO VariableTable
SELECT 'Events', COUNT(1)
FROM EventTable;
 
INSERT INTO VariableTable
SELECT 'Alternate names', COUNT(1)
FROM NameTable
WHERE IsPrimary = 0;
 
INSERT INTO VariableTable
SELECT 'Places', COUNT(1)
FROM PlaceTable
WHERE PlaceType = 0;
 
INSERT INTO VariableTable
SELECT 'Sources', COUNT(1)
FROM SourceTable;
 
INSERT INTO VariableTable
SELECT 'Citations', COUNT(1)
FROM CitationTable;
 
INSERT INTO VariableTable
SELECT 'Repositories', COUNT(1)
FROM AddressTable
WHERE AddressType = 1;
 
INSERT INTO VariableTable
SELECT 'To-do tasks', COUNT(1)
FROM ResearchTable
WHERE TaskType = 0;
 
INSERT INTO VariableTable
SELECT 'Multimedia items', COUNT(1)
FROM MultimediaTable;
 
INSERT INTO VariableTable
SELECT 'Multimedia links', COUNT(1)
FROM MediaLinkTable;
 
INSERT INTO VariableTable
SELECT 'Addresses', COUNT(1)
FROM AddressTable
WHERE AddressType = 0;
 
INSERT INTO VariableTable
SELECT 'Correspondence', COUNT(1)
FROM ResearchTable
WHERE TaskType = 1;

Solution pt 2

The following code can then be run to produce the results of the Database Properties List:

SELECT *
FROM VariableTable;

Finally, this code can be run to drop that temporary table (or the SQLite manager can just be closed):

DROP TABLE VariableTable;

The results of the query should be able to be copied or exported in some useful format to a file, depending on the capabilities of the SQLite manager used.

The two items not currently a part of the result set on the first pass of trying to reproduce it include Database and Created.

Discussions & comments from Wikispaces site


ve3meo

Good idea!

ve3meo
29 January 2010 14:02:56

Good idea, Romer! You could add to the page that the results may be copied or exported in some useful format, depending on the sqlite manager used, to a file.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.