admin/trading_card_games
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
trading_card_games/sql/90000_usp_populate.sql
Teddy Middleton-Smith d72d9c705b Initial commit.
2026-01-28 22:05:35 +00:00

566 lines
19 KiB
SQL
Raw Permalink Blame History

-- Change Set
INSERT INTO tcg.public.TCG_Change_Set (
comment
, updated_last_by_user_id
)
VALUES (
'Initial population of lookup tables.'
, 3
);
-- User
INSERT INTO tcg.public.TCG_User (
firstname
, surname
, email
, is_super_user
, change_set_id
)
VALUES (
'Teddy'
, 'Smith'
, 'edward.middletonsmith@gmail.com'
, TRUE
, 1
);
INSERT INTO tcg.public.TCG_Change_Set (
comment
, updated_last_by_user_id
)
VALUES (
'Alter created by user id in User table from initial upload.'
, 3
);
UPDATE tcg.public.TCG_User
SET
created_by_user_id = 3
, updated_last_by_user_id = 3
, change_set_id = 4
;
-- Condition
INSERT INTO tcg.public.TCG_Condition (
name
, code
, description
, summary
, us_name
, change_set_id
, created_by_user_id
, updated_last_by_user_id
)
VALUES
(
'Mint' -- name
, 'M' -- code
, $$A mint card is in perfect condition; no excuses. This means, that the front is in perfect condition, there are no scratches on the surface, and the surface is perfectly clean. For the back it means, that the card is indistinguishable from cards of a newly openend booster. If a card has a signature or a Grand Prix stamp it can never be graded Mint, even if the card stock is otherwise in Mint condition.
In most cases it doesn't make sense to grade a card as Mint. For newer cards, the value of a Mint card is basically the same as a Near Mint card. Older cards (1993-96), however, may command a high premium if they are in actual Mint condition - usually professionally graded. Thus 'Mint' is mainly a grading for cards where there is a high collectors' interest or a high professional grade. For cards that are sold mostly for playing purpose, a Near Mint grade is a safer choice.$$ -- description
, 'Perfect' -- summary
, 'Mint' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Near Mint' -- name
, 'NM' -- code
, $$A Near Mint card looks like it has never been played without sleeves. Small allowances can be made, but the card generally shows no wear.
The border of NM card can have small white spots, but they must be very few and very small. When the card is inspected under bright daylight, the surface must generally appear clean. It can have a few minor spots, but scratches can never be allowed for NM cards.
Generally a Near Mint card is in a condition that would make it considered unmarked if played in an unsleeved deck. (Not recommended!)
As the Mint grade is often not used for cards of newer expansions, Near Mint usually means Near Mint or better (equivalent to the American NM/M grade).$$ -- description
, 'Booster-Fresh' -- summary
, 'Near Mint' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Excellent' -- name
, 'EX' -- code
, $$An Excellent cards look like it was used for a few games without sleeves. For Excellent cards it is almost always clearly visible upon first inspection that the card is not in perfect condition. However, although the damage is clearly visible it is only of minor severity.
Excellent cards usually have a couple of white spots at the corners or around the border. The surface may have minor scratches, that are visible upon closer inspection. However, the card cannot be graded Excellent if the creases are so deep that they are visible upon first sight.
An Excellent card is usually in a condition where it is not quite clear if the card would be considered marked or unmarked if it would be played in a tournament without sleeves.
The American equivalent usually is Slightly Played or Lightly Played (not to be confused with the European Light Played).$$ -- description
, 'Minor-Wear' -- summary
, 'Slightly Played' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Good' -- name
, 'GD' -- code
, $$A Good card looks like it might have been used for a long tournament without sleeves.
Cards in Good condition usually show strong wear all around the card. The edges and corners have many white spots, the surface usually has scratches and the card usually has accumulated some dirt on its surface. However, the card still only has damage that stems from regular play. The card has no water damage or bends whatsoever.
A Good card (and all cards in worse condition) are clearly in a condition that would make them ineligible for play without sleeves as they would be considered marked.
The American equivalent to this is usually 'Moderately Played' or 'Very Good'. Note that 'Good' is a bit of a misnomer. A Good card doesn't really look good. In fact it looks pretty beat up, making the American Very Good even more of a misnomer.$$ -- description
, 'Visible-Wear' -- summary
, 'Moderately Played' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Light Played' -- name
, 'LP' -- code
, $$A Light Played card looks as if it has been used without sleeves for an extended period of time.
A Light Played card is clearly legal for play in a sleeved deck. It has also not been tampered with (inked border, random scribblings on the card etc.). If both of these criteria apply the card may look very bad, but it can be graded Light Played.
The American equivalent usually is 'Played' or 'Good'.$$ -- description
, 'Severe-Wear' -- summary
, 'Played' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Played' -- name
, 'PL' -- code
, $$A Played card looks as bad as you can get a card through regular use without sleeves.
A Played card looks extremely bad, and it is doubtful if the card is tournament legal even in a sleeved deck. However, the card has not been tampered with otherwise (inked border, random scribblings on the card etc.).
The American equivalent usually is Heavily Played or Good.$$ -- description
, 'Damaged' -- summary
, 'Heavily Played' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Poor' -- name
, 'PO' -- code
, $$A Poor card has damage that cannot normally have stemmed from regular use of the card.
A card in Poor condition is literally destroyed. It is either obviously illegal for tournament play or has been tampered with in ways that destroy its worth almost completely (inked border, random scribblings on the card etc.).
Americans usually use Poor in the same way.$$ -- description
, 'Destroyed' -- summary
, 'Poor' -- us_name
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
;
'
-- MTG Finish
INSERT INTO tcg.public.TCG_MTG_Finish (
name
, description
, change_set_id
, created_by_user_id
, updated_last_by_user_id
)
VALUES
(
'Standard' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Foil' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Etched Foil' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Borderless' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Extended Art' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
, (
'Showcase' -- name
, '' -- description
, 1 -- change_set_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
)
;
-- Inventory
SELECT I_T.temp_id -- COUNT(*)
, I_T.name
, MAIN_CARD.name
, I_T.set_code
, MAIN_CARD.SET
, I_T.collector_number
, MAIN_CARD.collector_number
, MAIN_CARD.set_type
, I_T.display_order
, I_T.token_rear_side_name
, REAR_CARD.name AS rear_name
, I_T.token_rear_side_set_code
, REAR_CARD.SET AS rear_set
, I_T.token_rear_side_collector_number
, REAR_CARD.collector_number AS rear_collector_number
, REAR_CARD.set_type AS rear_set_type
FROM tcg.public.TCG_MTG_Inventory_Temp I_T
LEFT JOIN tcg.public.TCG_MTG_Card MAIN_CARD
ON (
(
I_T.is_token = FALSE
AND LOWER(I_T.set_code) = MAIN_CARD.SET
)
OR (
I_T.is_token = TRUE
AND MAIN_CARD.set_type = 'token'
AND 't' || LOWER(I_T.set_code) = MAIN_CARD.set
)
/*
OR (
I_T.set_code LIKE 'FIN%'
AND C.set = 'fin'
)
*/
)
AND I_T.collector_number = MAIN_CARD.collector_number
LEFT JOIN tcg.public.TCG_MTG_Card REAR_CARD
ON (
(
I_T.is_token = FALSE
AND LOWER(I_T.set_code) = REAR_CARD.SET
)
OR (
I_T.is_token = TRUE
AND REAR_CARD.set_type = 'token'
AND 't' || LOWER(I_T.set_code) = REAR_CARD.set
)
)
AND I_T.collector_number = REAR_CARD.collector_number
WHERE -- 1=1 OR
MAIN_CARD.card_id IS NULL
-- OR C.name <> I_T.name
-- OR C.set_type = 'token'
OR (
I_T.is_token = TRUE
AND REAR_CARD.card_id IS NULL
)
-- I_T.name = 'Idyllic Beachfront'
-- GROUP BY I_T.temp_id
ORDER BY I_T.display_order
;
SELECT -- *
C.name
, C.set
, C.collector_number
, *
FROM tcg.public.TCG_MTG_Card C
WHERE
-- C.name LIKE 'Dragon Illusion'
C.set = 'tdm'
AND C.collector_number = '294'
ORDER BY C.set, C.collector_number
;
INSERT INTO tcg.public.TCG_MTG_Inventory (
card_id
, finish_id
, condition_id
, sleeve_colour_name
, location_name
, acquired_from
, acquired_on
, cost_gbp
, sale_price_gbp
, is_sold
, is_destroyed
, notes
, alterations_customisations
, grading_company_name
, grading_score
, subgrades
, misprint_errors
, miscut_errors
, playability
, owner_user_id
, ownership_status_name
, trading_status_name
, loaned_to_user_id
, loan_start_on
, loan_end_on
, provenance
, signed_by_names
, signature_condition_name
, active
, created_by_user_id
, updated_last_by_user_id
, change_set_id
, token_rear_side_card_id
)
SELECT
MAIN_CARD.card_id
, I_T.finish_id
, I_T.condition_id
, I_T.sleeve_colour_name
, I_T.location_name
, I_T.acquired_from
, I_T.acquired_on
, I_T.cost_gbp
, I_T.sale_price_gbp
, I_T.is_sold
, I_T.is_destroyed
, I_T.notes
, I_T.alterations_customisations
, I_T.grading_company_name
, I_T.grading_score
, I_T.subgrades
, I_T.misprint_errors
, I_T.miscut_errors
, I_T.playability
, I_T.owner_user_id
, I_T.ownership_status_name
, I_T.trading_status_name
, I_T.loaned_to_user_id
, I_T.loan_start_on
, I_T.loan_end_on
, I_T.provenance
, I_T.signed_by_names
, I_T.signature_condition_name
, I_T.active
, I_T.created_by_user_id
, I_T.updated_last_by_user_id
, I_T.change_set_id
, REAR_CARD.card_id
FROM tcg.public.TCG_MTG_Inventory_Temp I_T
INNER JOIN tcg.public.TCG_MTG_Card MAIN_CARD
ON (
(
I_T.is_token = FALSE
AND LOWER(I_T.set_code) = MAIN_CARD.SET
)
OR (
I_T.is_token = TRUE
AND MAIN_CARD.set_type = 'token'
AND 't' || LOWER(I_T.set_code) = MAIN_CARD.set
)
/*
OR (
I_T.set_code LIKE 'FIN%'
AND C.set = 'fin'
)
*/
)
AND I_T.collector_number = MAIN_CARD.collector_number
LEFT JOIN tcg.public.TCG_MTG_Card REAR_CARD
ON (
(
I_T.is_token = FALSE
AND LOWER(I_T.set_code) = REAR_CARD.SET
)
OR (
I_T.is_token = TRUE
AND REAR_CARD.set_type = 'token'
AND 't' || LOWER(I_T.set_code) = REAR_CARD.set
)
)
AND I_T.collector_number = REAR_CARD.collector_number
ORDER BY I_T.display_order
;
-- SELECT * FROM tcg.public.TCG_MTG_Inventory_Temp;
SELECT
-- I.inventory_id
C.card_id
, C.name
, C.card_faces
FROM /* tcg.public.TCG_MTG_Inventory I
INNER JOIN*/ tcg.public.TCG_MTG_Card C -- ON I.card_id = C.card_id
WHERE
-- C.name LIKE '%Yuna%'
C.set_type = 'token'
-- AND C.card_faces LIKE '%name'': ''Treasure'
AND C.name IN ('Treasure', 'Clue')
ORDER BY C.card_id -- I.inventory_id
;
SELECT
C.set
, C.collector_number
, COUNT(*)
FROM tcg.public.TCG_MTG_Card C
GROUP BY
C.set
, C.collector_number
HAVING COUNT(*) > 1
;
-- Deck Lookup Tables
INSERT INTO tcg.public.TCG_MTG_Deck_Commander_Bracket (
name
, description
, display_order
, created_by_user_id
, updated_last_by_user_id
, change_set_id
)
VALUES
(
'Exhibition' -- name
, $$Throw down with your ultra-casual Commander deck!
Winning is not the primary goal here, as it's more about showing off something unusual you've made. Villains yelling in the art? Everything has the number four? Oops, all Horses? Those are all fair game! The games here are likely to go long and end slowly.
Just focus on having fun and enjoying what the table has brought!
Deck Building: No cards from the Game Changers list. No intentional two-card infinite combos, mass land denial, or extra-turn cards. Tutors should be sparse.$$ -- description
, 1
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'Core' -- name
, $$The easiest reference point is that the average current preconstructed deck is at a Core (Bracket 2) level.
While Bracket 2 decks may not have every perfect card, they have the potential for big, splashy turns, strong engines, and are built in a way that works toward winning the game. While the game is unlikely to end out of nowhere and generally goes nine or more turns, you can expect big swings. The deck usually has some cards that aren't perfect from a gameplay perspective but are there for flavor reasons, or just because they bring a smile to your face.
Deck Building: No cards from the Game Changers list. No intentional two-card infinite combos or mass land denial. Extra-turn cards should only appear in low quantities and are not intended to be chained in succession or looped. Tutors should be sparse.$$ -- description
, 2
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'Upgraded' -- name
, $$These decks are souped up and ready to play beyond the strength of an average preconstructed deck.
They are full of carefully selected cards, with work having gone into figuring out the best card for each slot. The games tend to be a little faster as well, ending a turn or two sooner than your Core (Bracket 2) decks. This also is where players can begin playing up to three cards from the Game Changers list, amping up the decks further. Of course, it doesn't have to have any Game Changers to be a Bracket 3 deck: many decks are more powerful than a preconstructed deck, even without them!
These decks should generally not have any two-card infinite combos that can happen cheaply and in about the first six or so turns of the game, but it's possible the long game could end with one being deployed, even out of nowhere.
Deck Building: Up to three cards from the Game Changers list. No intentional early-game two-card infinite combos. Extra-turn cards should only appear in low quantities and are not intended to be chained in succession or looped. No mass land denial.$$ -- description
, 3
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'Optimized' -- name
, $$It's time to go wild!
Bring out your strongest decks and cards. You can expect to see explosive starts, strong tutors, cheap combos that end games, mass land destruction, or a deck full of cards off the Game Changers list. This is high-powered Commander, and games have the potential to end quickly.
The focus here is on bringing the best version of the deck you want to play, but not one built around a tournament metagame. It's about shuffling up your strong and fully optimized deck, whatever it may be, and seeing how it fares. For most Commander players, these are the highest-power Commander decks you will interact with.
Deck Building: There are no restrictions (other than the banned list).$$ -- description
, 4
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'cEDh' -- name
, $$This is high power with a very competitive and metagame-focused mindset.
"Mindset" is a key part of that description: Much of it is in how you approach the format and deck building. It's not just no holds barred, where you play your most powerful cards like in Bracket 4. It requires careful planning: There is care paid into following and paying attention to a metagame and tournament structure, and no sacrifices are made in deck building as you try to be the one to win the pod. Additionally, there is special care and attention paid to behavior and tableside negotiation (such as not making spite plays or concessions) that play into the tournament structure.
cEDH, or "competitive Commander" and similar names, is where winning matters more than self-expression. You might not be playing your favorite cards or commanders, as pet cards are usually replaced with cards needed in the meta, but you're playing what you think will be most likely to win.
Deck Building: There are no restrictions (other than the banned list).
$$ -- description
, 5
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
;
'
INSERT INTO tcg.public.TCG_MTG_Deck (
name
, commander_bracket_id
, created_by_user_id
, updated_last_by_user_id
, change_set_id
)
VALUES
(
'Final Fantasy: Counter Blitz' -- name
, 2 -- commander_bracket_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'Tarkir Dragonstorm: Jeskai Striker' -- name
, 2 -- commander_bracket_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
, (
'The Lost Caverns of Ixalan: Blood Rites' -- name
, 2 -- commander_bracket_id
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
)
;
SELECT *
FROM tcg.public.TCG_MTG_Deck D
;
INSERT INTO tcg.public.TCG_MTG_Deck_Inventory_Link (
deck_id
, inventory_id
, display_order
, created_by_user_id
, updated_last_by_user_id
, change_set_id
)
SELECT
CASE WHEN C.set LIKE '%fic' OR C.set LIKE '%fin' THEN 1 ELSE 2 END AS deck_id
, I.inventory_id
, CASE WHEN C.set LIKE '%fic' OR C.set LIKE '%fin' THEN I.inventory_id ELSE I.inventory_id - 106 END AS display_order
, 3 -- created_by_user_id
, 3 -- updated_last_by_user_id
, 1 -- change_set_id
FROM tcg.public.TCG_MTG_Inventory I
INNER JOIN tcg.public.TCG_MTG_Card C ON I.card_id = C.card_id
ORDER BY I.inventory_id
;
INSERT INTO tcg.public.TCG_Change_Set (
comment
, updated_last_by_user_id
)
VALUES (
'Remove extra cards from Tarkir Dragonstorm deck.'
, 3
);
UPDATE tcg.public.TCG_MTG_Deck_Inventory_Link DIL
SET
active = FALSE
, change_set_id = 6
WHERE DIL.inventory_id IN (217, 218)
;
Reference in New Issue View Git Blame Copy Permalink