Crypto Copycats Collectibles - Buy! Sell! Hodl! Sire!
Copycats Database Tools
-
copycats - incl.
kitty
command line tool to (auto-)read kitty data records (in comma-separated values (CSV)) into an in-memory SQLite database and print reports - bitcat - bit catalog kitty browser; browse your (digital) bit(s) collections
Database Tables
Table Diagram
SQL Tables (in SQLite Dialect)
CREATE TABLE kitties (
id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
name VARCHAR,
genes_kai VARCHAR NOT NULL,
gen INTEGER NOT NULL,
birthdate DATETIME NOT NULL,
day_count INTEGER NOT NULL,
matron_id INTEGER,
sire_id INTEGER,
body_id INTEGER NOT NULL,
pattern_id INTEGER NOT NULL,
coloreyes_id INTEGER NOT NULL,
eyes_id INTEGER NOT NULL,
color1_id INTEGER NOT NULL,
color2_id INTEGER NOT NULL,
color3_id INTEGER NOT NULL,
wild_id INTEGER NOT NULL,
mouth_id INTEGER NOT NULL
);
CREATE TABLE genes (
id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
kitty_id INTEGER NOT NULL,
n INTEGER NOT NULL,
gene VARCHAR NOT NULL,
gene_n INTEGER NOT NULL,
trait_id INTEGER NOT NULL
);
CREATE TABLE traits (
id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
trait_type_id INTEGER NOT NULL,
name VARCHAR NOT NULL,
n INTEGER NOT NULL,
kai VARCHAR NOT NULL,
tier INTEGER
);
CREATE TABLE trait_types (
id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
name VARCHAR NOT NULL,
[key] VARCHAR NOT NULL
);
Database Setup
Use the kitty setup command to setup an SQLite database and (auto-)read all datafiles. Example:
$ kitty setup
This will create:
- a single-file SQLite database
kitties.db
- setup all tables
- add all known traits and trait types (body, pattern, eyes, ...) and
- (auto-)read all datafiles (
**/*.csv
) in the.
and all subdirectories
Note: Use the -i/--include
option to change the default data directory (that is, .
)
and use the -n/--dbname
option to change the default SQLite database name (that is, kitties.db
)
and use the -d/--dbpath
option to change the default SQLite database path (that is, .
).
Showtime! Use the sqlite3 command line tool and try some queries. Example:
$ sqlite3 kitties.db
sqlite> SELECT * FROM kitties WHERE id = 1;
1||ccac 7787 fa7f afaa 1646 7755 f9ee 4444 6766 7366 cccc eede|0|2017-11-23 06:19:59|...
sqlite> SELECT * FROM genes WHERE trait_id = 14; -- sphynx (14)
1|1|0|d|0|14
3|1|2|r2|2|14
4|1|3|r3|3|14
38|2|1|r1|1|14
146|5|1|r1|1|14
181|6|0|d|0|14
183|6|2|r2|2|14
...
Database Queries
SQL
Find all kitties with a trait
Let's use the trait savannah (fur) with the id 0:
SELECT id FROM kitties WHERE body_id = 0
Find all kitties with two traits
Let's use the trait savannah (fur) with the id 0 and the trait tiger (pattern) with the id 33:
SELECT id FROM kitties
WHERE body_id = 0 AND pattern_id = 33
Find all kitties with a trait (in any gene d/r1/r2/r3)
Note: All traits (12 x 32 = 384) are numbered with ids from 0 to 383 in the traits database table. Let's use the trait savannah (fur) with the id 0:
SELECT kitty_id FROM genes WHERE trait_id = 0
Find all kitties with a dominant (visible) trait
Note: Use gene
column (d
/r1
/r2
/r3
) or the numeric gene_n
column (0/1/2/3): Let's use the trait savannah (fur) with the id 0
and a dominant (d) gene:
SELECT kitty_id FROM genes
WHERE trait_id = 0 AND gene='d'
Find all kitties with two traits (in any gene d/r1/r2/r3)
Use two query with "intersect" the result. Let's use the trait savannah (fur) with the id 0 and the trait tiger (pattern) with the id 33:
SELECT kitty_id FROM genes WHERE trait_id = 0
INTERSECT
SELECT kitty_id FROM genes WHERE trait_id = 33
Using Models w/ ActiveRecord in Ruby
Find all kitties with a trait
Let's use the trait savannah (fur) with the id 0:
Kitty.find_by( body: Trait.find_by( name: 'savannah' ))
# -or -
Kitty.find_by( body_id: 0)
Find all kitties with two traits
Let's use the trait savannah (fur) with the id 0 and the trait tiger (pattern) with the id 33:
Kitty.find_by( body: Trait.find_by( name: 'savannah' ),
pattern: Trait.find_by( name: 'tiger' ))
# -or -
Kitty.find_by( body_id: 0, pattern_id: 33 )
Find all kitties with a trait (in any gene d/r1/r2/r3)
Let's use the trait savannah (fur) with the id 0:
genes = Gene.find_by( trait: Trait.find_by( name: 'savannah' )) # query
#-or-
genes = Gene.find_by( trait_id: 0 )
genes.map { |gene| gene.kitty } # get kitties (from gene)
Find all kitties with a dominant (visible) trait
Let's use the trait savannah (fur) with the id 0 and a dominant (d) gene:
genes = Gene.find_by( trait: Trait.find_by( name: 'savannah' ),
d: 'd' ) #query
#-or-
genes = Gene.find_by( trait_id: 0, d: 'd' )
genes.map { |gene| gene.kitty } # get kitties (from gene)
Find all kitties with two traits
Use two query with "intersect" the result. Let's use the trait savannah (fur) and the trait tiger (pattern):
genes = Gene.select('kitty_id').where( trait: Trait.find_by( name: 'savannah' )).intersect(
Gene.select('kitty_id').where( trait: Trait.find_by( name: 'pattern' )))
genes.map { |gene| gene.kitty } # get kitties (from gene)
Datasets
(Crypto) Kitties on the Blockchain -
public dataset in comma-separated values (CSV) format in blocks of a thousand kitties each (e.g.
000.csv
incl. 1-999,
001.csv
incl. 1000-1999,
002.csv
incl. 2000-2999,
and so on). The data records for kitties incl. id, gen(eration), matron+sire ids, birthdate, 48 (12x4) genes in kai (base32) notation, and more.
Add your dataset here!