Wiki
Clone wikiBibSonomy / development / features / History
Database
Update new content id for Gold-Standard (runtime Biblicious: few seconds)
CREATE TABLE tmp_gold_standard AS
SELECT lgs.content_id, lgs.simhash1
FROM log_gold_standard lgs;
INSERT INTO tmp_gold_standard
SELECT gs.content_id, gs.simhash1
FROM gold_standard gs;
UPDATE log_gold_standard lgs
JOIN (SELECT tgs2.content_id, tgs2.simhash1
FROM tmp_gold_standard tgs2
ORDER BY content_id ASC) tgs
ON (tgs.simhash1 = lgs.new_simhash1 AND tgs.content_id > lgs.content_id)
SET lgs.new_content_id = tgs.content_id
WHERE lgs.new_content_id = 0;
DROP TABLE tmp_gold_standard;
Update current_content_id * Every second statement has to be iterated until no more changes occur * Goldstandard in seconds, 4 iterations * Bibtex in 7-9 sec per iteration (11 sec for first statement), 79 iterations * Bookmark in (24 sec for first statement), * To avoid having to type all iterations by hand use the following script
#!/bin/bash pw=PASSWORD a=10 while [ $a -gt 0 ]; do a=`mysql -u USER -p$pw bibsonomy -e "STATEMENT; SELECT ROW_COUNT();" | head -n 4 | tail -n 1`; echo $a; done
UPDATE log_bibtex lba
LEFT JOIN log_bibtex lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lba.new_content_id
WHERE lbb.content_id IS NULL AND lba.new_content_id != 0;
UPDATE log_bibtex lba
JOIN log_bibtex lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lbb.current_content_id
WHERE lbb.current_content_id != 0 AND lba.new_content_id != 0;
-- Until no more rows are changed
UPDATE log_bookmark lba
LEFT JOIN log_bookmark lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lba.new_content_id
WHERE lbb.content_id IS NULL AND lba.new_content_id != 0;
UPDATE log_bookmark lba
JOIN log_bookmark lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lbb.current_content_id
WHERE lbb.current_content_id != 0 AND lba.new_content_id != 0;
-- Until no more rows are changed
UPDATE log_gold_standard lga
LEFT JOIN log_gold_standard lgb ON (lga.new_content_id = lgb.content_id)
SET lga.current_content_id = lga.new_content_id
WHERE lgb.content_id IS NULL AND lga.new_content_id != 0;
UPDATE log_gold_standard lga
JOIN log_gold_standard lgb ON (lga.new_content_id = lgb.content_id)
SET lga.current_content_id = lgb.current_content_id
WHERE lgb.current_content_id != 0 AND lga.new_content_id != 0;
-- Until no more rows are changed
Add Indexes
Create index content_id_idx on log_tas (content_id);
TODO: Check for other indexes to be used on the log tables (compare biblicious)
Updated