\n| <\/td>\n | Optional code eg. alphanumeric code which may be required for import or export<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n <\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section admin_label=”section” fullwidth=”off” specialty=”off” transparent_background=”off” background_color=”#f4f2f4″ allow_player_pause=”off” inner_shadow=”off” parallax=”off” parallax_method=”off” padding_mobile=”off” make_fullwidth=”off” use_custom_width=”off” width_unit=”on” make_equal=”off” use_custom_gutter=”off”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]\n Useful SQL Queries<\/h1>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row admin_label=”row”][et_pb_column type=”1_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”] [\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]\nCount usage of detail type by record type<\/h4>\nThis query simply reports the usage of each detail type for each record type. It does not consult the record type structures, so it cannot list detail types in a record type which have not been used, or detail types which should not occur for a given record type. The database health check function in Heurist identifies these additional conditions.<\/p>\n select<\/b> rec_RecTypeID, rty_Name, dtl_DetailTypeID, dty_Name, count(*)
\nfrom <\/b>Records, recDetails, defRecTypes, defDetailTypes
\nwhere <\/b>(Records.rec_ID = recDetails.dtl_RecID) and
\n(recDetails.dtl_DetailTypeID=defDetailTypes.dty_ID) and
\n(Records.rec_RecTypeID = \u00a0defRecTypes.rty_ID)
\ngroup by<\/b> rec_RecTypeID, dtl_DetailTypeID
\norder by <\/b>rec_RecTypeID, dtl_DetailTypeID;<\/p>\nInsert records<\/h4>\n For example, to insert a new (blank) record, we can simply run the following SQL:<\/p>\n \u00a0INSERT INTO Records (rec_Type, rec_UGrpID, l………….) VALUES (…………………..)<\/p>\n This record will be incomplete as it contains no values and no composite record title is created, so it is necessary to know a few fairly simple rules in order to insert correct data. Heurist also has some rebuild and data verification functions which can come to the rescue.<\/p>\n Insert fields<\/h4>\nTo insert field values into a record, one simply uses the field codes<\/p>\n \u00a0insert into recDetails (dtl_RecID, dtl_dtyID, dtl_Value) VALUES ( …………)<\/p>\n This can also be used to insert data from a temporary table into which some existing data has been loaded, for example (the Heurist ID of existing records is in hid<\/em>,\u00a0134 is the field into which the data is being inserted):<\/p>\ninsert into recDetails (dtl_RecID,dtl_DetailTypeID,dtl_Value) select hid,134,entry from tmp_pdf_texts where `hid` IS NOT NULL<\/p>\n It can be useful to use Heurist’s report formatter to format an output file which has the Heurist IDs in it, which can then be edited with additional data to create a file for import using this method (or via the Heurist import functions).<\/p>\n Delete records and fields<\/h4>\nRecords are easily deleted, and Heurist’s cascading referential integrity takes care of deleting associated fields, bookmarks and tags. For instance, to delete a specific record and all its values:<\/p>\n delete from Records where rec_ID = 2785<\/p>\n or to delete all records and fields with a value under 10 in field 37 (we will return to internal codes in a moment)<\/p>\n delete from Records, recDetails
\nwhere (rec_ID = dtl_RecID) AND (dtl_dtyID = 37) AND (dtl_Value < 10)<\/p>\n Modify field values<\/h4>\nIn the same way, field values can be systematically modified using:<\/p>\n update recDetails set dtl_Value = ???? where <condition><\/p>\n Locating records with specific detail values<\/h4>\nSELECT r.rec_ID, d.dtl_Value FROM Records r left join recDetails d
\non rec_Id=dtl_RecID and dtl_DetailTypeID=5
\nwhere rec_RecTypeId=1 and (d.dtl_Value is null or d.dtl_Value=”);<\/p>\n Record Type 1 is Relationships, Detail type 5 is the Relationship Type, this searches for null and blank values in this detail type<\/p>\n Finding ‘floating’ records (not linked from another record)<\/h4>\nThis query looks for multimedia records (type 5) not linked from another record. Omit final Where restriction ot RecTypeId=5 for any records which are unlinked.<\/p>\n SELECT r.rec_ID, r.rec_Title, d.dtl_DetailTypeID FROM Records r left join recDetails d on r.rec_ID=d.dtl_Value and d.dtl_DetailTypeID in (select dty_ID from defDetailTypes where dty_Type=’resource’) where rec_RecTypeID=5 and d.dtl_Value is NULL;<\/p>\n Obtaining user and group IDs<\/h4>\nChanging or deleting field values is even easier <to do><\/p>\n Adding pointers from a record to its child records<\/h4>\nIn some cases, child records – such as events in a person’s life – may be imported into the database using the CSV \/ TSV importer. These child events will contain a pointer to the parent (Person) record, normally set by matching the name(s) of the parent, eg. person name(s), place name, reference ID or other unique identifier of the parent. You then wish to set record pointer fields in the parent records pointing to the child records. The following query shows how to do this:<\/p>\n Eventlet records are code 24 and contain a pointer field (code 16) back to their parent (Person). Person records contain a record pointer field (code 79) which points to eventlets belonging to that person.<\/p>\n insert into recDetails (dtl_RecID,dtl_DetailTypeID,dtl_Value,dtl_ValShortened)<\/div>\n select recDetails.dtl_Value,79,rec_ID,rec_ID<\/div>\n — record ID of person to be updated, eventlet record pointer code, eventlet record ID x 2<\/div>\n from Records,recDetails<\/div>\n where (Records.rec_ID=recDetails.dtl_RecID) — detail belongs to the record<\/div>\n and (Records.rec_RecTypeID=24) — Record is an eventlet record<\/div>\n and (recDetails.dtl_DetailTypeID=16);<\/div>\n — detail is a record pointer to the parent person of the eventlet record<\/div>\n <\/div>\n Check for duplicate records (on multiple fields)<\/h4>\nSELECT<\/b> firstnames, surname,COUNT(*)\u00a0AS<\/b> Cnt
\nFROM\u00a0<\/b>temp
\nGROUP<\/b>BY<\/b> SURNAME, FIRSTNAMES
\nHAVING<\/b> Cnt >1<\/p>\nAverage Active Users<\/h4>\n This procedure can be modified for other metrics. Run the following scripts:<\/p>\n get_metrics_for_all.sql<\/h5>\nrm metrics.csv
\nfor db in `echo “select schema_name from information_schema.schemata where schema_name like ‘hdb_%’ “| mysql -u root -psmith18`; do mysql –skip-column-names -u root -psmith18 $db < update_h3_mysql_structure.sql \u00a0>> metrics.csv; \u00a0done<\/p>\n get_metrics_for_db.sql<\/h5>\nSELECT database(),’,’, ‘rec’ as typ,’,’,year(rec_Modified) as yr,’,’,month(rec_Modified)as mth,’,’,count(distinct day(rec_Modified),rec_OwnerUGrpID) as cnt from Records group by year(rec_Modified),month(rec_Modified) order by year(rec_Modified),month(rec_Modified),day(rec_Modified),rec_OwnerUGrpID;<\/p>\n Note: the extra commas are b\/c MySQL exports with tabs and imports with commas …<\/i><\/p>\n \u00a0Empty and then load metrics into Heurist_METRICS.newrec_person_month_counts<\/p>\n id int(11) NOT NULL auto_increment, \u00a0db varchar(100) collate utf8_unicode_ci NOT NULL, \u00a0typ text collate utf8_unicode_ci NOT NULL, \u00a0yr year(4) NOT NULL, \u00a0mth tinyint(2) NOT NULL, \u00a0cnt mediumint(9) NOT NULL COMMENT ‘Number of distinct users\/days of record or bookmark mods per month’, \u00a0PRIMARY KEY \u00a0(id)<\/p>\n \u00a0To get metrics result:<\/p>\n SELECT yr,mth,sum(cnt) FROM newrec_person_month_counts group by yr,mth order by yr,mth<\/p>\n \u00a0Alternative direct insertion approach (but current script won\u2019t pass database as parameter, so it tries to open Records in the metrics database:<\/p>\n insert into Heurist_METRICS.newrec_person_month_counts (db,typ, yr, mth, cnt) ( SELECT database(), ‘rec’ as typ,year(rec_Modified) as yr,month(rec_Modified)as mth,count(distinct day(rec_Modified),rec_OwnerUGrpID) as cnt from ????.Records
\ngroup by year(rec_Modified),month(rec_Modified)
\norder by year(rec_Modified),month(rec_Modified),day(rec_Modified),rec_OwnerUGrpID)<\/p>\n Alternative person-month metrics based on bookmarks:<\/p>\n \u00a0SELECT ‘bkm’ as typ,year(bkm_Modified) as year, month(bkm_Modified) as month, count(distinct day(bkm_Modified),bkm_UGrpID)
\nfrom usrBookmarks
\ngroup by year(bkm_Modified),month(bkm_Modified)
\norder by year(bkm_Modified),month(bkm_Modified),day(bkm_Modified),bkm_UGrpID<\/p>\n Select the detail types (fields) of the given record type<\/h5>\nSELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID WHERE rst.rst_RectypeID=1;<\/p>\n Map layer data source<\/h5>\ndty_Type = resource<\/p>\n dty_PtrTargetRectypeIDs = 11, 1014, 1017, 1018, 1021<\/p>\n Use the record name<\/h5>\n$rectype<\/p>\n use rst_DisplayName<\/p>\n dty_Type=\u201cresource\u201d<\/p>\n \u00a0Count occurrence<\/h5>\nSELECT COUNT(rl.rl_ID) FROM recLinks rl WHERE rl_DetailTypeID=1018;<\/p>\n DATABASE SUMMARY QUERIES<\/h6>\nSelect all resource detail (field) types for the given record type<\/h5>\nSELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE “resource”;<\/p>\n Use:<\/p>\n rst.rst_DetailTypeID as id<\/p>\n rst.rst_DisplayName as name<\/p>\n dty.dty_PtrTargetRectypeIDs as ids<\/p>\n Retrieve count:<\/p>\n SELECT COUNT(rd.dtl_ID) as count FROM recDetails rd WHERE rd.dtl_DetailTypeID=1083;<\/p>\n Use:<\/p>\n COUNT(rd.dtl_ID) as count<\/p>\n Combine queries:<\/p>\n SELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID INNER JOIN recDetails rd ON rd.dtl_DetailTypeID=rst.rst_DetailTypeID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE “resource” GROUP BY rst.rst_DetailTypeID;<\/p>\n Correct fields:<\/p>\n SELECT rst_DetailTypeID as id, rst_DisplayName as name, COUNT(rd.dtl_ID) as count, dty.dty_PtrTargetRectypeIDs as ids FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID INNER JOIN recDetails rd ON rd.dtl_DetailTypeID=rst.rst_DetailTypeID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE “resource” GROUP BY rst.rst_DetailTypeID;<\/p>\n Retrieve the pointed records<\/p>\n Count the occurrence:<\/p>\n SELECT COUNT(r2.rec_ID) as count, rl.rl_DetailTypeID, r1.rec_Title, r1.rec_RecTypeID, r2.rec_Title, r2.rec_RecTypeID FROM recLinks rl INNER JOIN Records r1 ON r1.rec_ID=rl.rl_SourceID INNER JOIN Records r2 ON r2.rec_ID=rl.rl_TargetID WHERE r1.rec_RecTypeID=5 AND r2.rec_RecTypeID=10;<\/p>\n Use:<\/p>\n COUNT(rd.dtl_ID) as count<\/p>\n
\n Dictionary of Sydney<\/h4>\nUpdate to split up entities into multiple entity types<\/p>\n Insert the next available ID (30) into defRectyes, copying the current rectype (in this case 25):<\/p>\n INSERT INTO\u00a0defRecTypes (SELECT 30,”Entity30″, rty_OrderInGroup, rty_Description, rty_TitleMask, rty_CanonicalTitleMask, rty_Plural, rty_Status, rty_OriginatingDBID, rty_NameInOriginatingDB, rty_IDInOriginatingDB, rty_NonOwnerVisibility, rty_ShowInLists, rty_RecTypeGroupID, rty_RecTypeModelIDs, rty_FlagAsFieldset, rty_ReferenceURL, rty_AlternativeRecEditor, rty_Type, rty_ShowURLOnEditForm, rty_ShowDescriptionOnEditForm, rty_Modified, rty_LocallyModified
\nFROM `hdb_DoS3_redesign`.`defRecTypes` where rty_ID=25);<\/p>\n INSERT INTO hdb_DoS3_redesign.defRecStructure (rst_RecTypeID,rst_DetailTypeID, rst_DisplayName, rst_DisplayHelpText, rst_DisplayExtendedDescription, rst_DisplayOrder, rst_DisplayWidth, rst_DefaultValue, rst_RecordMatchOrder, rst_CalcFunctionID, rst_RequirementType, rst_NonOwnerVisibility, rst_Status, rst_MayModify, rst_OriginatingDBID, rst_IDInOriginatingDB, rst_MaxValues, rst_MinValues, rst_DisplayDetailTypeGroupID, rst_FilteredJsonTermIDTree, rst_PtrFilteredIDs, rst_OrderForThumbnailGeneration, rst_TermIDTreeNonSelectableIDs, rst_Modified, rst_LocallyModified)
\nSELECT\u00a030, rst_DetailTypeID, rst_DisplayName, rst_DisplayHelpText, rst_DisplayExtendedDescription, rst_DisplayOrder, rst_DisplayWidth, rst_DefaultValue, rst_RecordMatchOrder, rst_CalcFunctionID, rst_RequirementType, rst_NonOwnerVisibility, rst_Status, rst_MayModify, rst_OriginatingDBID, rst_IDInOriginatingDB, rst_MaxValues, rst_MinValues, rst_DisplayDetailTypeGroupID, rst_FilteredJsonTermIDTree, rst_PtrFilteredIDs, rst_OrderForThumbnailGeneration, rst_TermIDTreeNonSelectableIDs, rst_Modified, rst_LocallyModified
\nfrom hdb_DoS3_redesign.defRecStructure where rst_RecTypeID=25;<\/p>\n detail 75 is Entity type, codes are as below:<\/p>\n use hdb_DoS3_redesign;
\nupdate Records, recDetails set rec_RecTypeID=30
\nwhere (Records.rec_ID=recDetails.dtl_RecID)
\nAnd (rec_RecTypeID=25) and (dtl_DetailTypeID=75) and (dtl_Value=3291);<\/p>\n artefact=3291 –> rectype 30
\nbuilding 3294 –> 31
\nevent 3296 –> 32
\nnat ftr 3298 –> 33
\norg 3300 –> 34
\npers 3301 –> 35
\nplace 3302 –> 36
\nstruc 3305 \u00a0–> 37<\/p>\n Change all entities of a particular type into the appropriate record type<\/h5>\nuse hdb_DoS3_redesign;
\nupdate Records, recDetails set rec_RecTypeID=30
\nwhere (Records.rec_ID=recDetails.dtl_RecID)
\nAnd (rec_RecTypeID=25) and (dtl_DetailTypeID=75) and (dtl_Value=3291);<\/p>\n Rectype 25 is Entity (undifferentiated), Detail type 75 = entity type, Dtl_value 3291 is Artefact<\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n<\/span>","protected":false},"excerpt":{"rendered":"Accessing Heurist Data via SQL Heurist is underpinned by a clearly defined SQL data model implemented in MySQL. Because the structure of every Heurist database is identical and the data […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":" Heurist is underpinned by a clearly defined SQL data model implemented in MySQL. Because the structure of every Heurist database is identical and the data model is simple and highly normalised, it is extremely easy to bypass the web interface and manipulate the underlying data directly with SQL statements. The underlying data includes the semantic structure of the Heurist database, making the database fully self-documenting.<\/p> Please note that this page is a work in progress and, to some degree, internal notes on useful SQL and scripts, which we expect to complete rather slowly unless it becomes a higher priority. Probable completion 2015.<\/em><\/p>Use of\u00a0codes for record types, fields and terms<\/h5>Data in a Heurist database is never referenced by name or label, always by internal ID, allowing record type names, field names and terms to be edited at will without affecting existing data.<\/p> In order to manipulate data in the database you therefore need to know the internal code values used by your database.<\/p> Note: Internal IDs for the same record type\/field type\/term will vary between databases (some essential items have fixed low-number codes which are established when the database is created). However, all items have an origin database ID and origin database code which together provide a unique identifier or 'concept ID' across the whole Heurist network, allowing for later cross-database search and comparison.<\/i><\/p>
Database\u00a0tables<\/h2>
\u00a0<\/h2> The following section describes some of the more important fields in the Heurist database tables.<\/p> Record types<\/h4>defRecTypes<\/strong><\/em> table:\u00a0Records are stored in the Records data table. rec_RecTypeID identifies the type of each record<\/p>| rty_ID<\/td> | Code identifying the record type (max 65000)<\/td><\/tr> | | rty_Name<\/td> | The name of the record (object) type (max 63 char)<\/td><\/tr><\/tbody><\/table>Record details (fields)<\/h4>defDetailTypes<\/strong><\/em> table:\u00a0Fields are stored as key-value pairs in the recDetails data table. dtl_RecID specifies the key. The use of the term 'detail' is a historical legacy.<\/p>| dty_ID<\/td> | Code identifying the field\/attribute\/detail<\/td><\/tr> | | dty_Name<\/td> | The name of the field\/attribute\/detail<\/td><\/tr> | | dty_Type<\/td> | The type of data stored in this field\/attribute<\/td><\/tr><\/tbody><\/table>Field data types:<\/h4>| enum,\u00a0relationtype<\/td> | Term ID (relationtype is specialised version for relationships)<\/td><\/tr> | | resource<\/td> | Record ID (record pointer value)<\/td><\/tr> | | freetext, blocktext<\/td> | single and multi line (memo)<\/td><\/tr> | | integer, float:<\/td> | numeric values<\/td><\/tr> | | date<\/td> | date\/time value (may include fuzzy date descriptors)<\/td><\/tr> | | file<\/td> | FK > recUploadedFiles (references local or remote file)<\/td><\/tr> | | geo<\/td> | geographic object (geometry stored as WKT)<\/td><\/tr> | | \u00a0<\/td> | \u00a0<\/td><\/tr><\/tbody><\/table>Terms<\/h4>defTerms<\/strong><\/em> table:\u00a0Terms are organised in a hierarchical tree. Term IDs are used as values in the key-value pairsTerms<\/p>| trm_ID<\/td> | Code identifying the term<\/td><\/tr> | | trm_Label<\/td> | Human readable term<\/td><\/tr> | | trm_Code<\/td> | Standardised codes eg. ISO<\/td><\/tr> | | trm_Domain<\/td> | enum indicates a normal term, relation indicates relationship type terms used in relationship records<\/td><\/tr> | | trm_ParentTermID<\/td> | ID of the parent term in the hierarchy. 0 or NULL indicates a vocabulary (top level) - vocabularies can never be used as values in a record<\/td><\/tr> | | \u00a0<\/td> | Optional code eg. alphanumeric code which may be required for import or export<\/td><\/tr><\/tbody><\/table>
Useful SQL queries<\/h2>
Count usage of detail type by record type<\/h4>This query simply reports the usage of each detail type for each record type. It does not consult the record type structures, so it cannot list detail types in a record type which have not been used, or detail types which should not occur for a given record type. The database health check function in Heurist identifies these additional conditions.<\/p> select<\/b> rec_RecTypeID, rty_Name, dtl_DetailTypeID, dty_Name, count(*)
from <\/b>Records, recDetails, defRecTypes, defDetailTypes
where <\/b>(Records.rec_ID = recDetails.dtl_RecID) and
(recDetails.dtl_DetailTypeID=defDetailTypes.dty_ID) and
(Records.rec_RecTypeID = \u00a0defRecTypes.rty_ID)
group by<\/b> rec_RecTypeID, dtl_DetailTypeID
order by <\/b>rec_RecTypeID, dtl_DetailTypeID;<\/p>\u00a0Insert records<\/h4> For example, to insert a new (blank) record, we can simply run the following SQL:<\/p> \u00a0INSERT INTO Records (rec_Type, rec_UGrpID, l.............) VALUES (.......................)<\/p> \u00a0This record will be incomplete as it contains no values and no composite record title is created, so it is necessary to know a few fairly simple rules in order to insert correct data. Heurist also has some rebuild and data verification functions which can come to the rescue.<\/p> Insert fields<\/h4>To insert field values into a record, one simply uses the field codes<\/h4>\u00a0insert into recDetails (dtl_RecID, dtl_dtyID, dtl_Value) VALUES ( ............)<\/p> This can also be used to insert data from a temporary table into which some existing data has been loaded, for example (the Heurist ID of existing records is in hid<\/em>,\u00a0134 is the field into which the data is being inserted):<\/p>insert into recDetails (dtl_RecID,dtl_DetailTypeID,dtl_Value) select hid,134,entry from tmp_pdf_texts where `hid` IS NOT NULL<\/p> It can be useful to use Heurist's report formatter to format an output file which has the Heurist IDs in it, which can then be edited with additional data to create a file for import using this method (or via the Heurist import functions).<\/p> Delete records and fields<\/h4>Records are easily deleted, and Heurist's cascading referential integrity takes care of deleting associated fields, bookmarks and tags. For instance, to delete a specific record and all its values:<\/p> delete from Records where rec_ID = 2785<\/p> or to delete all records and fields with a value under 10 in field 37 (we will return to internal codes in a moment)<\/p> delete from Records, recDetails
where (rec_ID = dtl_RecID) AND (dtl_dtyID = 37) AND (dtl_Value < 10)<\/p> \u00a0Modify field values<\/h4>In the same way, field values can be systematically modified using:<\/p> update recDetails set dtl_Value = ???? where <\/p>Locating records with specific detail values<\/h4>SELECT r.rec_ID, d.dtl_Value FROM Records r left join recDetails d
on rec_Id=dtl_RecID and dtl_DetailTypeID=5
where rec_RecTypeId=1 and (d.dtl_Value is null or d.dtl_Value='');<\/p> \u00a0Record Type 1 is Relationships, Detail type 5 is the Relationship Type, this searches for null and blank values in this detail type<\/p> Finding 'floating' records (not linked from another record)<\/h4>This query looks for multimedia records (type 5) not linked from another record. Omit final Where restriction ot RecTypeId=5 for any records which are unlinked.<\/p> SELECT r.rec_ID, r.rec_Title, d.dtl_DetailTypeID FROM Records r left join recDetails d on r.rec_ID=d.dtl_Value and d.dtl_DetailTypeID in (select dty_ID from defDetailTypes where dty_Type='resource') where rec_RecTypeID=5 and d.dtl_Value is NULL;<\/p> \u00a0<\/p> Obtaining user and group IDs<\/h4>Changing or deleting field values is even easier <\/p>Adding pointers from a record to its child records<\/h4>In some cases, child records - such as events in a person's life - may be imported into the database using the CSV \/ TSV importer. These child events will contain a pointer to the parent (Person) record, normally set by matching the name(s) of the parent, eg. person name(s), place name, reference ID or other unique identifier of the parent. You then wish to set record pointer fields in the parent records pointing to the child records. The following query shows how to do this:<\/p> Eventlet records are code 24 and contain a pointer field (code 16) back to their parent (Person). Person records contain a record pointer field (code 79) which points to eventlets belonging to that person.<\/p> insert into recDetails (dtl_RecID,dtl_DetailTypeID,dtl_Value,dtl_ValShortened)<\/div> select recDetails.dtl_Value,79,rec_ID,rec_ID<\/div> -- record ID of person to be updated, eventlet record pointer code, eventlet record ID x 2<\/div> from Records,recDetails<\/div> where (Records.rec_ID=recDetails.dtl_RecID) -- detail belongs to the record<\/div> and (Records.rec_RecTypeID=24) -- Record is an eventlet record<\/div> and (recDetails.dtl_DetailTypeID=16);<\/div> -- detail is a record pointer to the parent person of the eventlet record<\/div> \u00a0<\/div> Check for duplicate records (on multiple fields)<\/h4>SELECT<\/b> firstnames, surname,COUNT(*)\u00a0AS<\/b> Cnt
FROM\u00a0<\/b>temp
GROUP<\/b>BY<\/b> SURNAME, FIRSTNAMES
HAVING<\/b> Cnt >1<\/p>Average Active Users<\/h4> This procedure can be modified for other metrics. Run the following scripts:<\/p> get_metrics_for_all.sql<\/h6>rm metrics.csv
for db in `echo \"select schema_name from information_schema.schemata where schema_name like 'hdb_%' \"| mysql -u root -psmith18`; do mysql --skip-column-names -u root -psmith18 $db < update_h3_mysql_structure.sql \u00a0>> metrics.csv; \u00a0done<\/p> get_metrics_for_db.sql<\/h6>SELECT database(),',', 'rec' as typ,',',year(rec_Modified) as yr,',',month(rec_Modified)as mth,',',count(distinct day(rec_Modified),rec_OwnerUGrpID) as cnt from Records group by year(rec_Modified),month(rec_Modified) order by year(rec_Modified),month(rec_Modified),day(rec_Modified),rec_OwnerUGrpID;<\/p> Note: the extra commas are b\/c MySQL exports with tabs and imports with commas ...<\/i><\/p> \u00a0Empty and then load metrics into Heurist_METRICS.newrec_person_month_counts<\/p> id int(11) NOT NULL auto_increment, \u00a0db varchar(100) collate utf8_unicode_ci NOT NULL, \u00a0typ text collate utf8_unicode_ci NOT NULL, \u00a0yr year(4) NOT NULL, \u00a0mth tinyint(2) NOT NULL, \u00a0cnt mediumint(9) NOT NULL COMMENT 'Number of distinct users\/days of record or bookmark mods per month', \u00a0PRIMARY KEY \u00a0(id)<\/p> \u00a0To get metrics result:<\/p> SELECT yr,mth,sum(cnt) FROM newrec_person_month_counts group by yr,mth order by yr,mth<\/p> \u00a0Alternative direct insertion approach (but current script won\u2019t pass database as parameter, so it tries to open Records in the metrics database:<\/p> insert into Heurist_METRICS.newrec_person_month_counts (db,typ, yr, mth, cnt) ( SELECT database(), 'rec' as typ,year(rec_Modified) as yr,month(rec_Modified)as mth,count(distinct day(rec_Modified),rec_OwnerUGrpID) as cnt from ????.Records
group by year(rec_Modified),month(rec_Modified)
order by year(rec_Modified),month(rec_Modified),day(rec_Modified),rec_OwnerUGrpID)<\/p> Alternative person-month metrics based on bookmarks:<\/p> \u00a0SELECT 'bkm' as typ,year(bkm_Modified) as year, month(bkm_Modified) as month, count(distinct day(bkm_Modified),bkm_UGrpID)
from usrBookmarks
group by year(bkm_Modified),month(bkm_Modified)
order by year(bkm_Modified),month(bkm_Modified),day(bkm_Modified),bkm_UGrpID<\/p> Select the detail types (fields) of the given record type<\/h6>SELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID WHERE rst.rst_RectypeID=1;<\/p> Map layer data source<\/h6>dty_Type = resource<\/p> dty_PtrTargetRectypeIDs = 11, 1014, 1017, 1018, 1021<\/p> Use the record name<\/h6>$rectype<\/p> use rst_DisplayName<\/p> dty_Type=\u201cresource\u201d<\/p> \u00a0Count occurrence<\/h6>SELECT COUNT(rl.rl_ID) FROM recLinks rl WHERE rl_DetailTypeID=1018;<\/p> \u00a0DATABASE SUMMARY QUERIES<\/h6>Select all resource detail (field) types for the given record type<\/h6>SELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE \"resource\";<\/p> Use:<\/p> rst.rst_DetailTypeID as id<\/p> rst.rst_DisplayName as name<\/p> dty.dty_PtrTargetRectypeIDs as ids<\/p> Retrieve count:<\/p> SELECT COUNT(rd.dtl_ID) as count FROM recDetails rd WHERE rd.dtl_DetailTypeID=1083;<\/p> Use:<\/p> COUNT(rd.dtl_ID) as count<\/p> Combine queries:<\/p> SELECT * FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID INNER JOIN recDetails rd ON rd.dtl_DetailTypeID=rst.rst_DetailTypeID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE \"resource\" GROUP BY rst.rst_DetailTypeID;<\/p> Correct fields:<\/p> SELECT rst_DetailTypeID as id, rst_DisplayName as name, COUNT(rd.dtl_ID) as count, dty.dty_PtrTargetRectypeIDs as ids FROM defRecStructure rst INNER JOIN defDetailTypes dty ON rst.rst_DetailTypeID=dty.dty_ID INNER JOIN recDetails rd ON rd.dtl_DetailTypeID=rst.rst_DetailTypeID WHERE rst.rst_RectypeID=1020 AND dty.dty_Type LIKE \"resource\" GROUP BY rst.rst_DetailTypeID;<\/p> Retrieve the pointed records<\/p> Count the occurrence:<\/p> SELECT COUNT(r2.rec_ID) as count, rl.rl_DetailTypeID, r1.rec_Title, r1.rec_RecTypeID, r2.rec_Title, r2.rec_RecTypeID FROM recLinks rl INNER JOIN Records r1 ON r1.rec_ID=rl.rl_SourceID INNER JOIN Records r2 ON r2.rec_ID=rl.rl_TargetID WHERE r1.rec_RecTypeID=5 AND r2.rec_RecTypeID=10;<\/p> Use:<\/p> COUNT(rd.dtl_ID) as count<\/p>
Dictionary of Sydney<\/h4>Update to split up entities into multiple entity types<\/p> Insert the next available ID (30) into defRectyes, copying the current rectype (in this case 25):<\/p> INSERT INTO\u00a0defRecTypes (SELECT 30,\"Entity30\", rty_OrderInGroup, rty_Description, rty_TitleMask, rty_CanonicalTitleMask, rty_Plural, rty_Status, rty_OriginatingDBID, rty_NameInOriginatingDB, rty_IDInOriginatingDB, rty_NonOwnerVisibility, rty_ShowInLists, rty_RecTypeGroupID, rty_RecTypeModelIDs, rty_FlagAsFieldset, rty_ReferenceURL, rty_AlternativeRecEditor, rty_Type, rty_ShowURLOnEditForm, rty_ShowDescriptionOnEditForm, rty_Modified, rty_LocallyModified
FROM `hdb_DoS3_redesign`.`defRecTypes` where rty_ID=25);<\/p> INSERT INTO hdb_DoS3_redesign.defRecStructure (rst_RecTypeID,rst_DetailTypeID, rst_DisplayName, rst_DisplayHelpText, rst_DisplayExtendedDescription, rst_DisplayOrder, rst_DisplayWidth, rst_DefaultValue, rst_RecordMatchOrder, rst_CalcFunctionID, rst_RequirementType, rst_NonOwnerVisibility, rst_Status, rst_MayModify, rst_OriginatingDBID, rst_IDInOriginatingDB, rst_MaxValues, rst_MinValues, rst_DisplayDetailTypeGroupID, rst_FilteredJsonTermIDTree, rst_PtrFilteredIDs, rst_OrderForThumbnailGeneration, rst_TermIDTreeNonSelectableIDs, rst_Modified, rst_LocallyModified)
SELECT\u00a030, rst_DetailTypeID, rst_DisplayName, rst_DisplayHelpText, rst_DisplayExtendedDescription, rst_DisplayOrder, rst_DisplayWidth, rst_DefaultValue, rst_RecordMatchOrder, rst_CalcFunctionID, rst_RequirementType, rst_NonOwnerVisibility, rst_Status, rst_MayModify, rst_OriginatingDBID, rst_IDInOriginatingDB, rst_MaxValues, rst_MinValues, rst_DisplayDetailTypeGroupID, rst_FilteredJsonTermIDTree, rst_PtrFilteredIDs, rst_OrderForThumbnailGeneration, rst_TermIDTreeNonSelectableIDs, rst_Modified, rst_LocallyModified
from hdb_DoS3_redesign.defRecStructure where rst_RecTypeID=25;<\/p> detail 75 is Entity type, codes are as below:<\/p> use hdb_DoS3_redesign;
update Records, recDetails set rec_RecTypeID=30
where (Records.rec_ID=recDetails.dtl_RecID)
And (rec_RecTypeID=25) and (dtl_DetailTypeID=75) and (dtl_Value=3291);<\/p> artefact=3291 --> rectype 30
building 3294 --> 31
event 3296 --> 32
nat ftr 3298 --> 33
org 3300 --> 34
pers 3301 --> 35
place 3302 --> 36
struc 3305 \u00a0--> 37<\/p> Change all entities of a particular type into the appropriate record type<\/h6>use hdb_DoS3_redesign;
update Records, recDetails set rec_RecTypeID=30
where (Records.rec_ID=recDetails.dtl_RecID)
And (rec_RecTypeID=25) and (dtl_DetailTypeID=75) and (dtl_Value=3291);<\/p> Rectype 25 is Entity (undifferentiated), Detail type 75 = entity type, Dtl_value 3291 is Artefact<\/p>","_et_gb_content_width":"","footnotes":""},"yoast_head":"\n Accessing Heurist Data with SQL - Heurist Network<\/title>\n<meta name=\"description\" content=\"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/heuristnetwork.org\/heurist-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Accessing Heurist Data with SQL - Heurist Network\" \/>\n<meta property=\"og:description\" content=\"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/heuristnetwork.org\/heurist-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Heurist Network\" \/>\n<meta property=\"article:modified_time\" content=\"2021-09-09T07:30:55+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@HeuristNetwork\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/heuristnetwork.org\/heurist-sql\/\",\"url\":\"https:\/\/heuristnetwork.org\/heurist-sql\/\",\"name\":\"Accessing Heurist Data with SQL - Heurist Network\",\"isPartOf\":{\"@id\":\"https:\/\/heuristnetwork.org\/#website\"},\"datePublished\":\"2014-05-09T14:33:54+00:00\",\"dateModified\":\"2021-09-09T07:30:55+00:00\",\"description\":\"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/heuristnetwork.org\/heurist-sql\/#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/heuristnetwork.org\/heurist-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/heuristnetwork.org\/heurist-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/heuristnetwork.org\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Accessing Heurist Data with SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/heuristnetwork.org\/#website\",\"url\":\"https:\/\/heuristnetwork.org\/\",\"name\":\"Heurist Network\",\"description\":\"Building a community of Heurist users\",\"publisher\":{\"@id\":\"https:\/\/heuristnetwork.org\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/heuristnetwork.org\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/heuristnetwork.org\/#organization\",\"name\":\"Heurist\",\"url\":\"https:\/\/heuristnetwork.org\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\/\/heuristnetwork.org\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/heuristnetwork.org\/wp-content\/uploads\/2015\/01\/HeuristFaviconTransparant.png\",\"contentUrl\":\"https:\/\/heuristnetwork.org\/wp-content\/uploads\/2015\/01\/HeuristFaviconTransparant.png\",\"width\":66,\"height\":66,\"caption\":\"Heurist\"},\"image\":{\"@id\":\"https:\/\/heuristnetwork.org\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/HeuristNetwork\",\"https:\/\/www.youtube.com\/channel\/UCuXxTPFSyqoPRoEb5HzDiBw\",\"https:\/\/en.wikipedia.org\/wiki\/Heurist\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Accessing Heurist Data with SQL - Heurist Network","description":"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/heuristnetwork.org\/heurist-sql\/","og_locale":"en_US","og_type":"article","og_title":"Accessing Heurist Data with SQL - Heurist Network","og_description":"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.","og_url":"https:\/\/heuristnetwork.org\/heurist-sql\/","og_site_name":"Heurist Network","article_modified_time":"2021-09-09T07:30:55+00:00","twitter_card":"summary_large_image","twitter_site":"@HeuristNetwork","twitter_misc":{"Est. reading time":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/heuristnetwork.org\/heurist-sql\/","url":"https:\/\/heuristnetwork.org\/heurist-sql\/","name":"Accessing Heurist Data with SQL - Heurist Network","isPartOf":{"@id":"https:\/\/heuristnetwork.org\/#website"},"datePublished":"2014-05-09T14:33:54+00:00","dateModified":"2021-09-09T07:30:55+00:00","description":"The Humanities databasing tool built by humanists, for humanists. Data can be easily inserted or removed using basic SQL.","breadcrumb":{"@id":"https:\/\/heuristnetwork.org\/heurist-sql\/#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/heuristnetwork.org\/heurist-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/heuristnetwork.org\/heurist-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/heuristnetwork.org\/"},{"@type":"ListItem","position":2,"name":"Accessing Heurist Data with SQL"}]},{"@type":"WebSite","@id":"https:\/\/heuristnetwork.org\/#website","url":"https:\/\/heuristnetwork.org\/","name":"Heurist Network","description":"Building a community of Heurist users","publisher":{"@id":"https:\/\/heuristnetwork.org\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/heuristnetwork.org\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en"},{"@type":"Organization","@id":"https:\/\/heuristnetwork.org\/#organization","name":"Heurist","url":"https:\/\/heuristnetwork.org\/","logo":{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/heuristnetwork.org\/#\/schema\/logo\/image\/","url":"https:\/\/heuristnetwork.org\/wp-content\/uploads\/2015\/01\/HeuristFaviconTransparant.png","contentUrl":"https:\/\/heuristnetwork.org\/wp-content\/uploads\/2015\/01\/HeuristFaviconTransparant.png","width":66,"height":66,"caption":"Heurist"},"image":{"@id":"https:\/\/heuristnetwork.org\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/HeuristNetwork","https:\/\/www.youtube.com\/channel\/UCuXxTPFSyqoPRoEb5HzDiBw","https:\/\/en.wikipedia.org\/wiki\/Heurist"]}]}},"_links":{"self":[{"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/pages\/1233"}],"collection":[{"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/comments?post=1233"}],"version-history":[{"count":18,"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/pages\/1233\/revisions"}],"predecessor-version":[{"id":4703,"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/pages\/1233\/revisions\/4703"}],"wp:attachment":[{"href":"https:\/\/heuristnetwork.org\/wp-json\/wp\/v2\/media?parent=1233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |
|
|
|
|