Difference between revisions of "User:FeralKitty/Local/PV DB stuff"

Latest revision as of 16:05, 8 March 2012

Steps to build database

Fetch details for the PV cards

Fetch everything (which simply runs the other fetch scripts).

./fetch_everything

Fetch the category members for each species cards category.

./fetch_species_members

Fetch the category members for each type of card.

./fetch_types_members

Fetch the imageinfo for the members of each category.

./fetch_imageinfo

Parse the downloaded xml data

Parse the xml for each category of cards.

./parse_xml.pl > pass1

Check to make sure no cards have more than one type.

grep , pass1

Parse the xml for each card image.

./iiparse_xml.pl > image_data

Download cards

Build a list of cards that need to be fetched.

If the card doesn't already exist locally, or the wiki's card is more recent, prepare to download it.

./download_cards.pl < pass1 > tmp_curl_fetch

Download any missing/out-of-date cards.

sh tmp_curl_fetch

Read the barcodes

Read the barcodes from the PV cards. See Misc stuff for unrelated steps, including saving the previous barcodes.txt

cd ~/pv/VP2Beta; ~/pv/pvr_dimensions.pl > barcodes.txt 2> barcodes_skipped.txt

Check to see if any cards weren't readable.

Shouldn't be any issues, assuming no more blurry, scanned, or very-low-resolution cards were uploaded.

cat barcodes_skipped.txt

Check for duplicate cards.

~/pv/check_for_dups.pl

Associate barcodes

Lookup barcode for card and append to record.

./associate_barcodes.pl < pass1 > pass2

Check to see if any card didn't get a barcode.

Shouldn't be any issue, since all cards were readable.

cut -d: -f8 < pass2 | sort | uniq | head

Generate descriptions

./generate_names.pl < pass2 > pass3

Retrieve any PVA/DL feed data

curl http://pinataisland.info/viva/PVA/DL?action=raw -o DL

Write out the PVA/DL data

Note: Uses pass4.sav, so make sure this corresponds to the app's DB

./update_dl_feed.pl < pass3 | sort -n > newDL

Add row id and favorites field

Add a primary key and favorite field to the record. Also escape any spaces or 's in the card title, so it can be used as a URL.

./add_favorites.pl < pass3 > pass4

To create a new database

sqlite3 -init create.sql pv.db
-- Loading resources from create.sql
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .separator :
sqlite> .import pass4 cards
sqlite> select count(*) from cards;
1861
sqlite> .quit

To verify PlaceTag IDs

egrep ':Fruit:' pass3 | ./pass3.pl --filename | ./barcodetxt2bitdiff.pl --short | ./reader.pl

To create PlaceTag property lists

egrep ':Fruit:' pass3 | ./pass3.pl | ./reader.pl | sort | ./create_plist.pl > Fruit.plist