Closed Orillion360 closed 9 years ago
Even if we aren't using my django work. I suggest we use a table model like the one I created on my side: https://github.com/Dokifansubs/anidex_django/blob/master/index/models.py
I can add the missing fields that you guys have extra like group details or something. (table names and field types can easily be modified as well. I can tell it what table name to bind the class to and it will change the SQL creates. The types are dependent on the which db we use, which will prob be MySQL.)
Just a Note: We aren't using foreign keys currently. We prob should unless we plan to switch to a NoSQL DB.
This single file generates the following SQL statements to get us started with the tables: (Index creation statements are at the end)
BEGIN;
CREATE TABLE index_group
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
name
varchar(200) NOT NULL,
website
varchar(200) NOT NULL
)
;
CREATE TABLE index_user_groups
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id
integer NOT NULL,
group_id
integer NOT NULL,
UNIQUE (user_id
, group_id
)
)
;
ALTER TABLE index_user_groups
ADD CONSTRAINT group_id_refs_id_32221ade
FOREIGN KEY (group_id
) REFERENCES index_group
(id
);
CREATE TABLE index_user
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
name
varchar(200) NOT NULL
)
;
ALTER TABLE index_user_groups
ADD CONSTRAINT user_id_refs_id_0e8b9b10
FOREIGN KEY (user_id
) REFERENCES index_user
(id
);
CREATE TABLE index_tracker
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
url
varchar(200) NOT NULL
)
;
CREATE TABLE index_torrentstat
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
info_hash
varchar(40) NOT NULL,
query_date
datetime NOT NULL,
seeders
integer NOT NULL,
leechers
integer NOT NULL,
completed
integer NOT NULL
)
;
CREATE TABLE index_torrent_trackers
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
torrent_id
integer NOT NULL,
tracker_id
integer NOT NULL,
UNIQUE (torrent_id
, tracker_id
)
)
;
ALTER TABLE index_torrent_trackers
ADD CONSTRAINT tracker_id_refs_id_f13edfdd
FOREIGN KEY (tracker_id
) REFERENCES index_tracker
(id
);
CREATE TABLE index_torrent_groups
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
torrent_id
integer NOT NULL,
group_id
integer NOT NULL,
UNIQUE (torrent_id
, group_id
)
)
;
ALTER TABLE index_torrent_groups
ADD CONSTRAINT group_id_refs_id_63c48fc4
FOREIGN KEY (group_id
) REFERENCES index_group
(id
);
CREATE TABLE index_torrent
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
name
varchar(200) NOT NULL,
owner_id
integer NOT NULL,
likes
integer NOT NULL,
size
integer NOT NULL,
info_hash
varchar(40) NOT NULL,
date
datetime NOT NULL
)
;
ALTER TABLE index_torrent
ADD CONSTRAINT owner_id_refs_id_987e0d4d
FOREIGN KEY (owner_id
) REFERENCES index_user
(id
);
ALTER TABLE index_torrent_trackers
ADD CONSTRAINT torrent_id_refs_id_3aa92f82
FOREIGN KEY (torrent_id
) REFERENCES index_torrent
(id
);
ALTER TABLE index_torrent_groups
ADD CONSTRAINT torrent_id_refs_id_6981c024
FOREIGN KEY (torrent_id
) REFERENCES index_torrent
(id
);
CREATE TABLE index_torrentfile
(
torrent_id
integer NOT NULL PRIMARY KEY,
name
varchar(200) NOT NULL,
data
longblob NOT NULL
)
;
ALTER TABLE index_torrentfile
ADD CONSTRAINT torrent_id_refs_id_b19f506e
FOREIGN KEY (torrent_id
) REFERENCES index_torrent
(id
);
CREATE INDEX index_user_groups_6340c63c
ON index_user_groups
(user_id
);
CREATE INDEX index_user_groups_5f412f9a
ON index_user_groups
(group_id
);
CREATE INDEX index_torrent_trackers_dacd18be
ON index_torrent_trackers
(torrent_id
);
CREATE INDEX index_torrent_trackers_ac0d7e39
ON index_torrent_trackers
(tracker_id
);
CREATE INDEX index_torrent_groups_dacd18be
ON index_torrent_groups
(torrent_id
);
CREATE INDEX index_torrent_groups_5f412f9a
ON index_torrent_groups
(group_id
);
CREATE INDEX index_torrent_cb902d83
ON index_torrent
(owner_id
);
COMMIT;
Created a model that can be viewed in MySQL Workbench.
Done, thanks Delwack!
Need more indices!