<commit_before>-- use UMetrics_GJY;

set FOREIGN_KEY_CHECKS = 0;
set UNIQUE_CHECKS = 0;

-- truncate Person;
-- truncate PersonAttribute;
-- truncate Attribute;
-- truncate PersonName;
-- truncate Address;
-- truncate PersonAddress;
-- truncate Publication;
-- truncate PublicationAttribute;
-- truncate PersonPublication;
-- truncate Term;
-- truncate PersonTerm;
-- truncate GrantAward;
-- truncate GrantAwardAttribute;
-- truncate PersonGrantAward;




-- ---------------------------------------------------------------------------------------------------
-- Authors
-- ---------------------------------------------------------------------------------------------------

drop table if exists CSXAuthorTemp;
CREATE temporary TABLE `CSXAuthorTemp` (
	`PersonId` int(11) UNSIGNED NOT NULL,
	`cluster` int(11) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`PersonId`),
	INDEX `cluster` (`cluster`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

-- Need to insert rows into the UMetrics.Person table, get those inserted ids back, and link them to
-- the raw ids from the CiteSeerX.authors table.
start transaction;
-- This will insert one row into the Person table for each row in the CiteSeerX.authors table.
insert into Person () select null from (select distinct cluster from CiteSeerX.authors where cluster not in (0, 1, 2)) x;

-- Although this says "last", it is actually the first id of the last insert statement, which was a multiple row insert.
SET @key = LAST_INSERT_ID();  
-- This will insert into CSXAuthorTemp one row for each row in the CiteSeerX.authors table combining it with the PersonId from the Person table.
-- It uses the last insert id and increments it by one for each row.
INSERT INTO CSXAuthorTemp (PersonID, cluster)  
SELECT PersonID, cluster
	FROM
	(
		SELECT @key + @rn as PersonID, cluster, @rn := @rn + 1
		FROM (select @rn:=0) x, (select distinct cluster from CiteSeerX.authors where cluster not in (0, 1, 2)) y
	) z;

COMMIT;

-- Add cluster ID to PersonAttribute
insert ignore into Attribute (Attribute)
	select cluster
		from CSXAuthorTemp;

insert into PersonAttribute (PersonID, AttributeID, RelationshipCode)
	select t.PersonID, a.AttributeId, 'CITESEERX_CLUSTER'
		from CSXAuthorTemp t
			inner join Attribute a on a.Attribute=t.cluster;


-- Add email to PersonAttribute
insert ignore into Attribute (Attribute)
	select email
		from CiteSeerX.authors
		where email is not null;

insert into PersonAttribute (PersonID, AttributeID, RelationshipCode)
	select distinct t.PersonID, a.AttributeId, 'EMAIL'
		from CSXAuthorTemp t
			inner join CiteSeerX.authors ca on ca.cluster=t.cluster
			inner join Attribute a on a.Attribute=ca.email;


-- Add Names to the PersonName table. authors.name is an alias, the primary name is in cannames
insert into PersonName (PersonID, FullName, GivenName, OtherName, FamilyName, RelationshipCode)
	select distinct t.PersonId, cc.canname, cc.fname, cc.mname, cc.lname, 'PRIMARY'
		from CSXAuthorTemp t
			inner join CiteSeerX.cannames cc on cc.id=t.cluster;

insert into PersonName (PersonID, RelationshipCode, FullName )<commit_msg>Minor bug in loading CSX into UMetrics where emails would get added that were never referenced by a Person.<commit_after>-- use UMetrics_GJY;

set FOREIGN_KEY_CHECKS = 0;
set UNIQUE_CHECKS = 0;

-- truncate Person;
-- truncate PersonAttribute;
-- truncate Attribute;
-- truncate PersonName;
-- truncate Address;
-- truncate PersonAddress;
-- truncate Publication;
-- truncate PublicationAttribute;
-- truncate PersonPublication;
-- truncate Term;
-- truncate PersonTerm;
-- truncate GrantAward;
-- truncate GrantAwardAttribute;
-- truncate PersonGrantAward;




-- ---------------------------------------------------------------------------------------------------
-- Authors
-- ---------------------------------------------------------------------------------------------------

drop table if exists CSXAuthorTemp;
CREATE temporary TABLE `CSXAuthorTemp` (
	`PersonId` int(11) UNSIGNED NOT NULL,
	`cluster` int(11) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`PersonId`),
	INDEX `cluster` (`cluster`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

-- Need to insert rows into the UMetrics.Person table, get those inserted ids back, and link them to
-- the raw ids from the CiteSeerX.authors table.
start transaction;
-- This will insert one row into the Person table for each row in the CiteSeerX.authors table.
insert into Person () select null from (select distinct cluster from CiteSeerX.authors where cluster not in (0, 1, 2)) x;

-- Although this says "last", it is actually the first id of the last insert statement, which was a multiple row insert.
SET @key = LAST_INSERT_ID();  
-- This will insert into CSXAuthorTemp one row for each row in the CiteSeerX.authors table combining it with the PersonId from the Person table.
-- It uses the last insert id and increments it by one for each row.
INSERT INTO CSXAuthorTemp (PersonID, cluster)  
SELECT PersonID, cluster
	FROM
	(
		SELECT @key + @rn as PersonID, cluster, @rn := @rn + 1
		FROM (select @rn:=0) x, (select distinct cluster from CiteSeerX.authors where cluster not in (0, 1, 2)) y
	) z;

COMMIT;

-- Add cluster ID to PersonAttribute
insert ignore into Attribute (Attribute)
	select cluster
		from CSXAuthorTemp;

insert into PersonAttribute (PersonID, AttributeID, RelationshipCode)
	select t.PersonID, a.AttributeId, 'CITESEERX_CLUSTER'
		from CSXAuthorTemp t
			inner join Attribute a on a.Attribute=t.cluster;


-- Add email to PersonAttribute
insert ignore into Attribute (Attribute)
	select email
		from CiteSeerX.authors
		where email is not null
			and cluster not in (0, 1, 2);

insert into PersonAttribute (PersonID, AttributeID, RelationshipCode)
	select distinct t.PersonID, a.AttributeId, 'EMAIL'
		from CSXAuthorTemp t
			inner join CiteSeerX.authors ca on ca.cluster=t.cluster
			inner join Attribute a on a.Attribute=ca.email;


-- Add Names to the PersonName table. authors.name is an alias, the primary name is in cannames
insert into PersonName (PersonID, FullName, GivenName, OtherName, FamilyName, RelationshipCode)
	select distinct t.PersonId, cc.canname, cc.fname, cc.mname, cc.lname, 'PRIMARY'
		from CSXAuthorTemp t
			inner join CiteSeerX.cannames cc on cc.id=t.cluster;

insert into PersonName (PersonID, RelationshipCode, FullName )
{'fraction_non_alphanumeric': 0.13912630579297247, 'fraction_numerical': 0.0052231718898385565, 'mean_word_length': 3.2070572569906792, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6032793', 'n_tokens_mistral': 1895, 'n_tokens_neox': 1779, 'n_words': 725}