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 )

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