CEDER.NET database tables -------------------------------------------------------------------------------- ================================================== Special table (indicates whether edits are locked) ================================================== CREATE TABLE LockedDb (Def2Locked int, // 0=false, 1=true AllLocked int, // 0=false, 1=true LockedId int AUTO_INCREMENT PRIMARY KEY) // ignored (always 1) INSERT INTO LockedDb (Def2Locked) VALUES (0) ========================== AACE REGISTRATION database ========================== CREATE TABLE AaceRegistrationDb (RegistrationYear int, FirstName1 varchar(40), LastName1 varchar(40), FirstName2 varchar(40), LastName2 varchar(40), Address varchar(255), City varchar(30), StateId int, Zip varchar(30), CountryName varchar(32), PhoneHome varchar(30), PhoneMobile varchar(30), PhoneWork varchar(30), EmailHome varchar(50), EmailWork varchar(50), PaidDate1 date, PaidBy1 int, //0=n/a, 1=cash, 2=check, 3=wire transfer, 4=rollover, 5=refund PaidCheckNo1 varchar(12), PaidAmount1 real, PaidDate2 date, PaidBy2 int, //0=n/a, 1=cash, 2=check, 3=wire transfer, 4=rollover, 5=refund PaidCheckNo2 varchar(12), PaidAmount2 real, PaidDate3 date, PaidBy3 int, //0=n/a, 1=cash, 2=check, 3=wire transfer, 4=rollover, 5=refund PaidCheckNo3 varchar(12), PaidAmount3 real, PreRegistered int, //0=no, 1=pre-registered, 2=walk-in, 4=comp one, 8=comp two Days int, //a sum of the following: 1=Wed, 2=Thurs, 4=Fri, 8=Sat, 16=one session, 32=two sessions DanceLevel int, // index into SquareLevels Letter1Date date, Letter2Date date, OtherInfo blob, ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE AaceSuggestionBox (Comments blob, Name varchar(50), Email varchar(50), ModDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) ================ ANIMATE database ================ CREATE TABLE AnimateDb (CallName varchar(50), DefLink varchar(50), IncludeText blob, AnimateId int AUTO_INCREMENT PRIMARY KEY) ================ ARTICLE database ================ CREATE TABLE ArticleDb (Title varchar(255), ReprintedFrom varchar(255), AuthorArtistId1 int, AuthorArtistId2 int, AuthorArtistId3 int, AuthorArtistId4 int, URL varchar(200), Blurb blob, ArticleText blob, SectionId int, CategoryId int, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ArticleSection (SectionName varchar(80), SectionId int AUTO_INCREMENT PRIMARY KEY, SortOrder int) CREATE TABLE ArticleCategory (SectionId int, CategoryName varchar(80), SortOrder int, // how to sort the categories within the section CategoryId int AUTO_INCREMENT PRIMARY KEY) ======================= BANDE DESSINEE database ======================= CREATE TABLE BandeDessineeSeries (SeriesName varchar(64) NOT NULL, WebSiteUrl varchar(100), Blurb blob, SeriesId int AUTO_INCREMENT PRIMARY KEY, Unique(SeriesName)) INSERT INTO BandeDessineeSeries (SeriesName) VALUES ("Natacha") CREATE TABLE BandeDessineePublisher (PublisherName varchar(64) NOT NULL, WebSiteUrl varchar(100), LogoUrl varchar(100), LogoHeight int, LogoWidth int, Blurb blob, PublisherId int AUTO_INCREMENT PRIMARY KEY, Unique(PublisherName)) INSERT INTO BandeDessineePublisher (PublisherName) VALUES ("Dupuis") CREATE TABLE BandeDessineeAuthorList (AuthorName varchar(60) NOT NULL, AuthorFirstName varchar(30), AuthorLastName varchar(30), AuthorId int AUTO_INCREMENT PRIMARY KEY, Unique(AuthorName)) INSERT INTO BandeDessineeAuthorList (AuthorName) VALUES ("-") [AuthorId = 1 for this entry] INSERT INTO BandeDessineeAuthorList (AuthorName, AuthorFirstName, AuthorLastName) VALUES ("François Walthéry", "François", "Walthéry") CREATE TABLE BandeDessineeLanguage (LanguageName varchar(64) NOT NULL, LanguageId int AUTO_INCREMENT PRIMARY KEY, Unique(LanguageName)) INSERT INTO BandeDessineeLanguage (LanguageName) VALUES ("French") [LanguageId = 1 for this entry] INSERT INTO BandeDessineeLanguage (LanguageName) VALUES ("English - U.K.") CREATE TABLE BandeDessineeMedia (MediaName varchar(64) NOT NULL, MediaId int AUTO_INCREMENT PRIMARY KEY, Unique(MediaName)) INSERT INTO BandeDessineeMedia (MediaName) VALUES ("Hardback") [MediaId = 1 for this entry] INSERT INTO BandeDessineeMedia (MediaName) VALUES ("Softback") INSERT INTO BandeDessineeMedia (MediaName) VALUES ("Softback (small)") CREATE TABLE BandeDessineeItem (SeriesId int NOT NULL, BookNumber varchar(20) NOT NULL, Title varchar(100), PublisherId int, YearPublished int, LanguageId int, MediaId int, PictureUrl varchar(255), Synopsis blob, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data AuthorId int, AuthorId2 int, AuthorId3 int, IllustratorId int, IllustratorId2 int, ColorerId int, Copies int, Rating int, InWishList int, // 1 = in wish list, 0 = not in wish list RecordId int AUTO_INCREMENT PRIMARY KEY, Unique(SeriesId,BookNumber,LanguageId)) CREATE TABLE BandeDessineeRating (ProseRating varchar(20), Rating int PRIMARY KEY) INSERT INTO BandeDessineeRating VALUES ("not rated", 0) INSERT INTO BandeDessineeRating VALUES ("dislike", 1) INSERT INTO BandeDessineeRating VALUES ("below average", 2) INSERT INTO BandeDessineeRating VALUES ("average", 3) INSERT INTO BandeDessineeRating VALUES ("good", 4) INSERT INTO BandeDessineeRating VALUES ("excellent", 5) ======================== CALLER AND CUER database ======================== CREATE TABLE SquareLevels (level varchar(16), VariantFlag int, // 0 = false, 1 = yes (e.g., MSV) ind int PRIMARY KEY) INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('Beginner', 5, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('Basic', 10, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('BV', 15, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('Mainstream', 20, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('MSV', 25, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('Plus', 30, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('PLV', 35, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('A1', 40, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('A1V', 45, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('A2', 50, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('A2V', 55, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C1', 60, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C1V', 65, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C2', 70, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C2V', 75, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C3A', 80, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C3AV', 85, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C3B', 90, 0); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C3BV', 95, 1); INSERT INTO SquareLevels (level, ind, VariantFlag) VALUES ('C4', 100, 0); CREATE TABLE RoundLevels (level varchar(16), ind int PRIMARY KEY) INSERT INTO RoundLevels VALUES ("Easy", 10) INSERT INTO RoundLevels VALUES ("Phase I", 20) INSERT INTO RoundLevels VALUES ("Phase II", 30) INSERT INTO RoundLevels VALUES ("Phase III", 40) INSERT INTO RoundLevels VALUES ("Phase IV", 50) INSERT INTO RoundLevels VALUES ("Phase V", 60) INSERT INTO RoundLevels VALUES ("Phase VI", 70) CREATE TABLE LicenseDb (OrganizationName varchar(32), ProseName varchar(255), WebSiteUrl varchar(255), CountryId int, LicenseId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO license VALUES ("BMI & ASCAP", "", 1, 10) // NOTE: 10 is hard-coded in some HTML pages!!! INSERT INTO license VALUES ("APRA", "http://www.apra.com.au/", 2, 20) CREATE TABLE callerdb (CheckedByVdc int, UpdateRequested int, // 0 = none requested; 6 = 6 month request Status int, // 0 = normal; 1 = retired; 2 = deceased FirstName varchar(40), LastName varchar(40), MiddleName varchar(40), // or Middle Initial (with period) NickName varchar(40), // do not include quotes SuffixName varchar(40), // Jr., Sr. (with period) PartnerFirstName varchar(40), PartnerLastName varchar(40), Address varchar(255), City varchar(30), StateId int, Zip varchar(30), CountryName varchar(32), PhoneHome varchar(30), PhoneMobile varchar(30), PhoneWork varchar(30), FaxHome varchar(30), FaxWork varchar(30), EmailHome varchar(50), EmailWork varchar(50), URL varchar(100), PictureUrl varchar(100), PictureHeight int, PictureWidth int, Caller int, MinSquare int, MaxSquare int, Cuer int, MinRound int, MaxRound int, Fulltime int, Traveling int, Recording int, Callerlab int, ACA int, ECTA int, GCA int, Youth int, Handicapable int, Contralab int, Roundalab int, Seminars int, Coach int, OneNightStands int, CDP int, Clogging int, Mixers int, LLines int, CW int, Contra int, Traditional int, Sound int, LicenseId int, OtherInfo blob, MoreInfo blob, PIN varchar(4), ModDateTime datetime, CreateDateTime datetime, weblink int, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE CallerSaveState (CountryName varchar(32), State varchar(30), Fulltime int, Traveling int, Recording int, Callerlab int, ACA int, ECTA int, Youth int, Handicapable int, Contralab int, Roundalab int, Seminars int, Coach int, Picture int, NewOrUpdated int, Website int, OtherInfo int, OneNightStands int, CDP int, Clogging int, Mixers int, LLines int, CW int, Contra int, Traditional int, Sound int, MinSquare int, MaxSquare int, MinRound int, MaxRound int, Style varchar(10), SortBy varchar(10), NameOrder varchar(10), Region varchar(20), ModDateTime datetime, caller varchar(30), temp varchar(30), FirstName varchar(30), LastName varchar(30), sql blob, QueryExecuted int, IpAddress varchar(30) PRIMARY KEY) ================================ CAITLYN'S BEANIE BABY collection ================================ CREATE TABLE BeanieBabyType (TypeName varchar(64) NOT NULL, TypeId int AUTO_INCREMENT PRIMARY KEY, Unique(TypeName)) INSERT INTO BeanieBabyType (TypeName) VALUES ("Beanie Baby") [TypeId = 1 for this entry] INSERT INTO BeanieBabyType (TypeName) VALUES ("Beanie Buddy") CREATE TABLE BeanieBabyItem (TypeId int NOT NULL, BeanieName varchar(100) NOT NULL, PictureUrl varchar(255), Poem blob, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data Copies int, BirthDate date, Genre varchar(100), CaitlynRating int, TagFlag int, // 0 = false, 1 = true OriginalNineFlag int, // 0 = false, 1 = true RecordId int AUTO_INCREMENT PRIMARY KEY, Unique(TypeId,BeanieName)) CREATE TABLE BeanieBabyRating (ProseRating varchar(20), Rating int PRIMARY KEY) INSERT INTO BeanieBabyRating VALUES ("not rated", 0) INSERT INTO BeanieBabyRating VALUES ("OK", 1) INSERT INTO BeanieBabyRating VALUES ("fair", 2) INSERT INTO BeanieBabyRating VALUES ("good", 3) INSERT INTO BeanieBabyRating VALUES ("excellent", 4) INSERT INTO BeanieBabyRating VALUES ("favorite", 5) ====================== CHECKER CHEAT database ====================== CREATE TABLE CheckerCheat (CallName varchar(50), Handle int, Text blob, RecordId int AUTO_INCREMENT PRIMARY KEY) ====================== CHOCOLATE BAR database ====================== CREATE TABLE ChocolateLabel (LabelName varchar(64) NOT NULL, WebSiteUrl varchar(100), LogoUrl varchar(100), LogoHeight int, LogoWidth int, SiteSellsBarsFlag int, // 0 = false, 1 = true FavoriteChocolatier int, // 0 = false, 1 = true BeanToBarFlag int, // 0 = false, 1 = true Blurb blob, CountryId int, StateId int, City varchar(40), LabelId int AUTO_INCREMENT PRIMARY KEY, Unique(LabelName)) CREATE TABLE ChocolateVendor (VendorName varchar(100) NOT NULL, VendorCityState varchar(100), VendorWebSiteUrl varchar(100), VendorPictureUrl varchar(255), VendorPictureHeight int, VendorPictureWidth int, VendorAddress varchar(255), VendorComments blob, VendorRating int, // 0 = not rated, 1 to 5 OutOfBusinessFlag int, // 0 = false, 1 = true VendorId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ChocolateItem (LabelId int NOT NULL, Title varchar(100), PercentCacao real, WeightOz real, WeightG real, Ingredients blob, IngredientIdList varchar(255), // format ",nnn,mmm,nnn," - indices into table ChocolateIngredient VendorId int, // index into ChocolateVendor PictureUrl varchar(255), // full size ReviewUrl varchar(255), // external review Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data VicRating int, DebRating int, LoveAtFirstBiteFlag int, // 0 = false, 1 = true YearPurchased int, ChocolatierCommments blob, Variety varchar(40), OriginId int, AllergenInfo varchar(255), SupersededFlag int, // 0 = false, 1 = true (a later version exists) FirstInstanceFlag int, // 0 = false, 1 = true (first instance of this bar) FirstInstanceRecordId int, // index into ChocolateItem (index of first instance) BarWidthMm real; BarHeightMm real; FlagMask varchar(20), // format ",1,2,4,8," (1=Pure, 2=AlmostPure, 4=Milk, 8=NoSoy) DiscontinuedFlag int, // 0 = false, 1 = true (bar no longer available) ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ChocolateRating (ProseRating varchar(20), Rating int PRIMARY KEY) INSERT INTO ChocolateRating VALUES ("not rated", 0) INSERT INTO ChocolateRating VALUES ("1", 1) INSERT INTO ChocolateRating VALUES ("2", 2) INSERT INTO ChocolateRating VALUES ("3", 3) INSERT INTO ChocolateRating VALUES ("4", 4) INSERT INTO ChocolateRating VALUES ("5", 5) INSERT INTO ChocolateRating VALUES ("6", 6) INSERT INTO ChocolateRating VALUES ("7", 7) INSERT INTO ChocolateRating VALUES ("8", 8) INSERT INTO ChocolateRating VALUES ("9", 9) INSERT INTO ChocolateRating VALUES ("10", 10) CREATE TABLE ChocolateIngredient (IngredientName varchar(40), IngredientId int AUTO_INCREMENT PRIMARY KEY, Description blob, UNIQUE(IngredientName)) CREATE TABLE ChocolateOrigin (OriginName varchar(50), Description blob, CountryId int, OriginId int AUTO_INCREMENT PRIMARY KEY, UNIQUE(OriginName)) ===================== CHOREOGRAPHY database ===================== CREATE TABLE Difficulty (DifficultyStr varchar(16), DifficultyInd int PRIMARY KEY) INSERT INTO Difficulty VALUES ("Easy", 10) INSERT INTO Difficulty VALUES ("Medium", 20) INSERT INTO Difficulty VALUES ("Hard", 30) CREATE TABLE SeqType (SeqTypeProseName varchar(50), SeqTypeShortName varchar(30), SortOrder int, SeqTypeInd int AUTO_INCREMENT PRIMARY KEY); INSERT INTO SeqType VALUES ("Static Set ==> Resolve (Normal sequence)", "SS to Resolve", 5); INSERT INTO SeqType VALUES ("Singing call (Corner progression)", "Singing (corner)", 10); INSERT INTO SeqType VALUES ("Singing call (R-H Lady progression)", "Singing (r-h lady)", 20); INSERT INTO SeqType VALUES ("Singing call (Opener-Middle-Closer)", "Singing Break", 30); INSERT INTO SeqType VALUES ("Stir the bucket", "Stir the Bucket", 40); INSERT INTO SeqType VALUES ("Gimmick", "Gimmick", 50); INSERT INTO SeqType VALUES ("Interesting choreographic idea", "Interesting", 60); INSERT INTO SeqType VALUES ("Static Set ==> Resolve (Short sequence)", "Short", 70); INSERT INTO SeqType VALUES ("Static Set ==> Resolve (Asymmetric)", "Asymmetric", 75); INSERT INTO SeqType VALUES ("Static Set ==> Zero Line", "SS to ZL", 80); INSERT INTO SeqType VALUES ("Static Set ==> Zero Box", "SS to ZB", 90); INSERT INTO SeqType VALUES ("Zero Line ==> Resolve", "ZL to Resolve", 95); INSERT INTO SeqType VALUES ("Zero Line ==> Zero Line", "ZL to ZL", 100); INSERT INTO SeqType VALUES ("Zero Line ==> Zero Box", "ZL to ZB", 110); INSERT INTO SeqType VALUES ("Zero Box ==> Resolve", "ZB to Resolve", 115); INSERT INTO SeqType VALUES ("Zero Box ==> Zero Line", "ZB to ZL", 120); INSERT INTO SeqType VALUES ("Zero Box ==> Zero Box", "ZB to ZB", 130); INSERT INTO SeqType VALUES ("Zero (Facing Lines)", "Zero (Facing Lines)", 160); INSERT INTO SeqType VALUES ("Zero (Eight Chain Thru)", "Zero (Eight Chain Thru)", 150); INSERT INTO SeqType VALUES ("Zero (Parallel Waves)", "Zero (Parallel Waves)", 140); INSERT INTO SeqType VALUES ("Zero (Facing Couples)", "Zero (Facing Couples)", 165); INSERT INTO SeqType VALUES ("Six Couple (Rectangle)", "Six Couple", 170); INSERT INTO SeqType VALUES ("Six Couple (Hex)", "Hex", 180); INSERT INTO SeqType VALUES ("Two Couple (Facing Couples)", "Two Couple", 190); CREATE TABLE ChoreoDb (CheckedByVdc int, LevelValidated int, // 0 = false, 1 = true SeqTypeInd int, Text blob, SubmittedBy int, // Index into 'callerdb' where 0 = anonymous LevelInd int, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data HandleList varchar(255), // stored with a comma on both ends (e.g., ",9,122,2124,") DifficultyInd int, VicThumbs int, // -1 = thumbs down, 0 = nothing, 1 = thumbs up ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) // for creating a CSDS database CREATE TABLE ChoreoSaveState (Sql blob, IpAddress varchar(30), ModDateTime datetime, DesiredLevel int, DesiredSeqType int, DesiredHandle int, DesiredAuthor int, DesiredDifficulty int, RecordId int AUTO_INCREMENT PRIMARY KEY) ============= CLUB database ============= CREATE TABLE ClubDb (CheckedByVdc int, UpdateRequested int, // 0 = none requested; 6 = 6 month request ClubName varchar(80), City varchar(50), StateId int, CountryName varchar(32), Location blob, ContactEmail varchar(50), ContactInfo blob, WebsiteUrl varchar(100), MapUrl varchar(255), LogoUrl varchar(100), LogoHeight int, LogoWidth int, MeetingFreq int, // 1 = first, 2 = second, 4 = third, 8 = fourth, 16 = fifth, 31 = every, 32 = irregular, 64 = alternate MeetingDay int, // 1 = Mon, 2 = Tue, 3 = Wed, 4 = Thu, 5 = Fri, 6 = Sat, 7 = Sun, 10 = weekend MeetingTime varchar(255), DanceType int, // 0 = dance; 1 = class; 2 = workshop; 3 = tape group SquareLevel int, // "MinSquare" MaxSquare int, RoundLevel int, // "MinRound" MaxRound int, Caller int, // index into CCDB or 0 = "-", -1 = Guest, -2 = Local callers, -3 = National callers, -4 = Tapes Caller2 int, // (same info as above) Caller3 int, // (same info as above) Caller4 int, // (same info as above) Cuer int, // (same info as above) Cuer2 int, // (same info as above) MoreInfo blob, PIN varchar(4), ModDateTime datetime, CreateDateTime datetime, weblink int, RecordId int AUTO_INCREMENT PRIMARY KEY) ================= COMMENTS database ================= CREATE TABLE CommentsDb (DatabaseInd int, // 1=BandeDessineeItem, 2=BeanieBabyItem, // 3=ChocolateItem, 4=ChoreoDb, // 5=NoteServiceDb, 6=PokemonCardItem, // 7=RecordList, 8=WinchesterDb, // 9=PokemonCharacter, 10=PhotoArchiveCaption // 11=StuffDb DatabaseRecordId int, CreateDateTime datetime, Name varchar(100), Email varchar(100), Comments blob, CommentsId int AUTO_INCREMENT PRIMARY KEY) ======================= COMMENT FILTER database ======================= CREATE TABLE BadWords (Phrase varchar(70), RecordId int AUTO_INCREMENT PRIMARY KEY, UNIQUE(Phrase)) ================ CONTACT database ================ CREATE TABLE ContactDb (FirstName varchar(30), LastName varchar(30), Address varchar(255), City varchar(30), StateId int, Zip varchar(30), CountryId int, PhoneHome varchar(30), PhoneMobile varchar(30), PhoneWork varchar(30), FaxHome varchar(30), FaxWork varchar(30), EmailHome varchar(50), EmailWork varchar(50), URL varchar(100), FlagStr varchar(80), // A concatenation of ContactFlag|FlarChar CallerDbIndex int, // index into Caller and Cuer Database (0 = none) CsdsVersion varchar(10), CsdsUserKey varchar(14), CsdsUserId int, OtherInfo blob, ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ContactFlag (FlagName varchar(40), FlagChar varchar(2), ContactFlagInd int AUTO_INCREMENT PRIMARY KEY) INSERT INTO ContactFlag VALUES ("CSDS User", "A", 1) INSERT INTO ContactFlag VALUES ("Vic's Contact", "B", 2) INSERT INTO ContactFlag VALUES ("Debbie's Contact", "C", 3) INSERT INTO ContactFlag VALUES ("Relative", "D", 4) INSERT INTO ContactFlag VALUES ("Square Dancer", "E", 5) INSERT INTO ContactFlag VALUES ("Co-Worker", "F", 6) INSERT INTO ContactFlag VALUES ("Mutual Friend", "G", 7) INSERT INTO ContactFlag VALUES ("Caitlyn's Friend", "H", 8) INSERT INTO ContactFlag VALUES ("Callerlab Challenge Committee", "I", 9) INSERT INTO ContactFlag VALUES ("X-Mas Card", "J", 10) INSERT INTO ContactFlag VALUES ("Caller", "K", 11) ==================== COUNTRIES and STATES ==================== CREATE TABLE Countries (CountryName varchar(128), FlagData varchar(32), TinyFlag int, // (indicates we got a tiny flag too) 0 = false, 1 = true CountryId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO Countries (Country) VALUES ('USA') CREATE TABLE States (StateName varchar(40), StateAbbreviation varchar(10), CountryId int, StateId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO States VALUES ('Alabama', 'AL', 1, 1) ==================== DEFINITIONS database ==================== CREATE TABLE ExperimentalCallsDb (CallName varchar(100), Definition blob, Author varchar(100), CallerDbIndex int, // (author) index into Caller and Cuer Database (0 = none) MoreText blob, // Examples or Comments Date date, // date written RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE DefinitionDb (CallName varchar(100), Definition blob, AuthorId int, // index into DefinitionAuthorList AuthorId2 int, // index into DefinitionAuthorList AuthorId3 int, // index into DefinitionAuthorList YearWritten varchar(20), EvalStr blob, // intended for diagrams MoreText blob, // Examples or Comments Handle int, OldCallFlag int, // 0 = false, 1 = true Source int, // 0 = normal, 1 = Burleson's, 2 = Jay King, 3= SCVSDA notes, 4=SIO, // 5=Callerlab, 6=Jack Lasry, 7=Choreo Breakdown, 8=New 'N Notes, // 9=Mikeside Management, 10=Notes for European Callers ItemNumber int, // burleson's #, jay king page, scvsda year+month, or SIO year+month RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE FosterCardsDb (CallName varchar(100), EntryType int, // 1 = call, 2 = round, 3 = general SetNumber int, CardNumber int, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE DefinitionAuthorList (AuthorName varchar(60) NOT NULL, AuthorFirstName varchar(30), AuthorLastName varchar(30), CallerDbIndex int, // index into Caller and Cuer Database (0 = none) AuthorId int AUTO_INCREMENT PRIMARY KEY, Unique(AuthorName)) INSERT INTO DefinitionAuthorList (AuthorName) VALUES ("-") [AuthorId = 1 for this entry] ============= DEF2 database ============= CREATE TABLE Def2Db (PhpFilename varchar(100), // (includes .php4) CallName varchar(100), AuthorId1 int, // index into DefinitionAuthorList AuthorId2 int, // index into DefinitionAuthorList AuthorId3 int, // index into DefinitionAuthorList YearWritten varchar(20), Handle1 int, Handle2 int, Handle3 int, Handle4 int, Handle5 int, AnimateLink int, LevelInd1 int, // index into SquareLevels LevelInd2 int, // index into SquareLevels (calls such as Explode have 2 levels!) LevelInd3 int, // index into SquareLevels (calls such as Single and Rotate have 3 levels!) JapaneseFlag int, // 0=false, 1=true GermanFlag int, // 0=false, 1=true SwedishFlag int, // 0=false, 1=true DanishFlag int, // 0=false, 1=true CzechFlag int, // 0=false, 1=true JapaneseTranslatorId int, GermanTranslatorId int, SwedishTranslatorId int, DanishTranslatorId int, CzechTranslatorId int, EnglishSubDefText blob, JpSubDefText blob, DeSubDefText blob, SeSubDefText blob, DkSubDefText blob, CzSubDefText blob, Def2Id int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE Def2ItemDb (Def2Id int, // index into Def2Db LangId int, // 1=English, 2=Japanese, 3=German, 4=Swedish, 5=Danish, 6=Czech SpotInd int, // text location within current php file ItemTranslatorId int, Text blob, TLM varchar(10), // yyyy-mm-dd Def2ItemId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE Def2Translator (Name varchar(100), LoginName varchar(40), PIN varchar(4), LangId int, // 1=English, 2=Japanese, 3=German, 4=Swedish, 5=Danish, 6=Czech TranslatorId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO Def2Translator (Name,LangId) VALUES ("Thomas Borowski",3) INSERT INTO Def2Translator (Name,LangId) VALUES ("Lars-Erik Morell",4) INSERT INTO Def2Translator (Name,LangId) VALUES ("Arne Pedersen og Britta Bertel",5) INSERT INTO Def2Translator (Name,LangId) VALUES ("Kiyoshi Kikuchi",2) CREATE TABLE Def2Cookie (IpAddress varchar(30), LoginName varchar(40), PIN varchar(6), // allow for admin password LangId int, ModDateTime datetime, CookieId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE Def2Picture (PictureUrl varchar(255), // relative to http://www.ceder.net/ ObjectCode blob, PictureId int AUTO_INCREMENT PRIMARY KEY, Unique(PictureUrl)) =========== EMAIL Table (for admin2/send_email.php4) =========== CREATE TABLE EmailDb (FirstName varchar(30), LastName varchar(30), Email varchar(50), ModDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) ============== EVENT Database ============== CREATE TABLE EventDb (CheckedByVdc int, DateBegin date, DateEnd date, EventTypeId int, CallerDbIndex int, [0=ALL,1=Vic,-2=Caitlyn] Properties int, // 0 = normal; 1 = new date or location; 2 = cancelled EventName varchar(80), City varchar(60), StateId int, CountryName varchar(32), Levels varchar(100), Directions blob, ContactInfo blob, WebSiteUrl varchar(100), FlyerUrl varchar(100), SmallFlyerPictureUrl varchar(100), ContractUrl varchar(100), OtherInfo blob, BookingContactInfo blob, EmailContact varchar(50), CallerList blob, CuerList blob, ContraList varchar(200), LineList varchar(200), LinkedCallerList blob, // field generated via new2.php4 LinkedCuerList blob, // field generated via new2.php4 LinkedContraList blob, // field generated via new2.php4 LinkedLineList blob, // field generated via new2.php4 Beginners int, Basic int, Mainstream int, Plus int, A1 int, A2 int, C1 int, C2 int, C3A int, C3B int, C4 int, Easy int, PhaseI int, PhaseII int, PhaseIII int, PhaseIV int, PhaseV int, PhaseVI int, ClubId int, // pointer into ClubDb Status int, // pointer into EventStatusTypeDb Private int, IncludeInAll int, WeeklyGroup int, PIN varchar(4), ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE EventTypeDb (EventTypeName varchar(32), EventTypeId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO EventTypeDb VALUES ("-", 1) INSERT INTO EventTypeDb VALUES ("Dance", 10) CREATE TABLE EventStatusTypeDb (StatusType varchar(32), StatusTypeInd int PRIMARY KEY) INSERT INTO EventStatusTypeDb VALUES ("-", 0) INSERT INTO EventStatusTypeDb VALUES ("Reserved (for this group)", 10) INSERT INTO EventStatusTypeDb VALUES ("Tentative (awaiting contract)", 20) INSERT INTO EventStatusTypeDb VALUES ("Confirmed (contract signed)", 30) CREATE TABLE SpecialEventDb (DateBegin date, EventName varchar(100), PhpBlob blob, HiddenFlag int, // 0=false, 1=true SpecialEventId int AUTO_INCREMENT PRIMARY KEY) ============ FAQ database ============ CREATE TABLE FaqDb (Question blob, Answer blob, CategoryId int, SectionId int, ShortTitle varchar(80), ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE FaqSection (SectionId int AUTO_INCREMENT PRIMARY KEY, SortOrder int, SectionName varchar(80) ) CREATE TABLE FaqCategory (SectionId int, CategoryName varchar(80), // e.g., "step-by-step instructions" SortOrder int, // sort order for categories within the section CategoryId int AUTO_INCREMENT PRIMARY KEY) ==================== FLASH CARDS database ==================== CREATE TABLE FlashUser (UserName varchar(60) NOT NULL, Email varchar(50), PIN varchar(4), ModDateTime datetime, LastHandle int, UserId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE FlashItem (UserId int, Handle int, SortOrder int, Unique(UserId,Handle)) ============ FTP database ============ CREATE TABLE FtpUser (UserName varchar(60) NOT NULL, LoginName varchar(40) NOT NULL, PIN varchar(4), UserId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE FtpItem (UserId int, Filename varchar(255) NOT NULL, Description varchar(255), CanBeDeletedFlag int, // 0 = false, 1 = true DateAdded date, Unique(UserId,Filename), RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE FtpSave (UserId int) =============== GETOUT database =============== CREATE TABLE getout (permnum int4, levelindex int, text varchar(200), RecordId int AUTO_INCREMENT PRIMARY KEY) ============== KANJI database ============== CREATE TABLE KanjiDb (KanjiChar varchar(4), DictDescription blob, // with
tags, around on, around kun StrokeCount int, DictRadicalNum int, DictNum int, // # in dictionary OrangeNum int, // # in orange book BlackNum int, // # in black book OnList varchar(100), // chinese pronunciation KunList varchar(100), // japanese pronunciation NotFound int, // 0=false, 1=true KanjiId int AUTO_INCREMENT PRIMARY KEY, Unique(KanjiChar)) CREATE TABLE JapEngWord (JapText varchar(100), // 4-letter hex codes with leading and trailing commas EngText varchar(100), GenreIdList varchar(255), // JapEngGenreIds with leading and trailing commas JapEngWordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE JapEngGenre (GenreName varchar(100), // e.g., animal GenreId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE JapRadical (RadicalName varchar(100), DictRadicalNum int, KanjiChar varchar(4), StrokeCount int, RadicalId int AUTO_INCREMENT PRIMARY KEY, Unique(DictRadicalNum)) ============= LINK database ============= CREATE TABLE LinkSection (LinkType int, // 1=SquareDance, 2=Private, 3=Chocolate, 4=BD, 5=BB, 6=Choreo, 7=Pokemon SectionName varchar(50), SortOrder int, SectionId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO LinkSection VALUES (1, "Resources", 100) INSERT INTO LinkSection VALUES (1, "Callers", 200) INSERT INTO LinkSection VALUES (1, "Cuers", 275) INSERT INTO LinkSection VALUES (1, "Groups, Organizations, and Associations", 300) INSERT INTO LinkSection VALUES (1, "Events", 400) INSERT INTO LinkSection VALUES (1, "Products", 500) INSERT INTO LinkSection VALUES (1, "Services", 600) INSERT INTO LinkSection VALUES (1, "Information", 700) INSERT INTO LinkSection VALUES (1, "Not yet categorized", 800) INSERT INTO LinkSection (LinkType, SectionName, SectionId) VALUES (2, "Restaurants", 1000) INSERT INTO LinkSection (LinkType, SectionName, SectionId) VALUES (2, "Gardening", 1400) INSERT INTO LinkSection (LinkType, SectionName, SectionId) VALUES (2, "Not yet categorized", 1800) CREATE TABLE LinkCategory (CategoryName varchar(70), SortOrder int, SectionId int, CategoryId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO LinkCategory VALUES (1, "General Purpose Web Sites", 100) INSERT INTO LinkCategory VALUES (1, "Noteworthy Web Sites", 110) INSERT INTO LinkCategory VALUES (1, "Links to Links", 120) INSERT INTO LinkCategory VALUES (1, "Regional", 150) INSERT INTO LinkCategory VALUES (1, "Lists of Callers", 200) INSERT INTO LinkCategory VALUES (1, "Caller Web Sites", 210) INSERT INTO LinkCategory VALUES (1, "Resources for Callers", 220) INSERT INTO LinkCategory VALUES (1, "Cuer Web Sites", 280) INSERT INTO LinkCategory VALUES (1, "Professional Organizations", 300) INSERT INTO LinkCategory VALUES (1, "Caller Associations", 305) INSERT INTO LinkCategory VALUES (1, "Dancer Groups", 310) INSERT INTO LinkCategory VALUES (1, "Lists of Events", 400) INSERT INTO LinkCategory VALUES (1, "Products", 500) INSERT INTO LinkCategory VALUES (1, "Apparel", 505) INSERT INTO LinkCategory VALUES (1, "Publications", 510) INSERT INTO LinkCategory VALUES (1, "Resort and Campgrounds", 520) INSERT INTO LinkCategory VALUES (1, "Record Companies", 550) INSERT INTO LinkCategory VALUES (1, "Record Dealers", 560) INSERT INTO LinkCategory VALUES (1, "General", 600) INSERT INTO LinkCategory VALUES (1, "Dancer Services", 610) INSERT INTO LinkCategory VALUES (1, "Attire", 620) INSERT INTO LinkCategory VALUES (1, "Articles", 710) INSERT INTO LinkCategory VALUES (1, "History", 720) INSERT INTO LinkCategory VALUES (1, "Humor", 725) INSERT INTO LinkCategory VALUES (1, "Terminology", 730) INSERT INTO LinkCategory VALUES (1, "Not yet categorized", 820) CREATE TABLE LinkDb (CheckedByVdc int, SectionId int, CategoryId int, Title varchar(80), BefTitle varchar(100), AftTitle varchar(60), URL varchar(100), BefPictureUrl varchar(100), BefPictureHeight int, BefPictureWidth int, AftPictureUrl varchar(100), AftPictureHeight int, AftPictureWidth int, Rating int, // 0 = not rated, 1 to 5 CountryId int, StateId int, Region varchar(40), OtherInfo blob, AdminComments blob, ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) ============= LIST database ============= CREATE TABLE ListDb (Text varchar(60), LevelInd int, GarbageFlag int, // 0 = false, 1 = true (hide from ChoreoDb include files) DefinitionUrl varchar(40), // relative to http://www.ceder.net/def/ DefinitionId int, // index into DefinitionDb (for flashcards) FcDefTextEnglish blob, // Flashcard definition text, english CallType varchar(1), NPart int, NDan int, CsdsLevelText varchar(4), FlagStr varchar(20), Handle int PRIMARY KEY) CREATE TABLE ListCallType (CallType varchar(1), CallTypeName varchar(40)) INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('D','Direction'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('F','Formation'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('I','Identifier'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('N','Call'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('P','Pseudocall'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('Y','Concept'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('M','Meta-Concept'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('Z','Numeric'); INSERT INTO ListCallType (CallType, CallTypeName) VALUES ('@','Delimiter'); CREATE TABLE ListFlagType (FlagType varchar(1), FlagTypeName varchar(40)) INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('F','First part'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('G','Garbage'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('A','Asymmetric'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('I','Space invader'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('L','Last part'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('M','Mirror same'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('R','Requires flow'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('S','Sex-dependent'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('Z','No divide'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('2','Pseudo 2-dancer'); INSERT INTO ListFlagType (FlagType, FlagTypeName) VALUES ('K','Skip flash card'); ===================== NOTE SERVICE database ===================== CREATE TABLE NoteServiceDb (CallName varchar(100), Handle int, WorkingOnItFlag int, // 0=false, 1=true CederChestPage varchar(100), Definition blob, TeachingHints blob, DosAndDonts blob, Equivalents blob, FlowConsiderations blob, GoodUses blob, BadUses blob, Gimmicks blob, Extensions blob, ChoreoList blob, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) ====================== PATTER SAYING database ====================== CREATE TABLE PatterSaying (Patter varchar(255), Comments varchar(128), Type int, // 0 = short, 1 = long RecordId int AUTO_INCREMENT PRIMARY KEY) ====================== PHOTO ARCHIVE database ====================== CREATE TABLE PhotoArchiveSet (Title varchar(255), Year int, Month int, Description blob, Folder varchar(255), ThumbWidth int, ThumbHeight int, NormalWidth int, NormalHeight int, SortKey int, SetId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PhotoArchiveCaption (SetId int, PictureFilename varchar(255), Caption varchar(255), ExternalComments blob, // hidden concatenation of CommentsDb data Description blob, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE VideoItem (Title varchar(255), Description blob, Year int, Month int, VideoFilename varchar(255), Duration int, NBytes int, SortKey int, VideoSectionId int, VideoId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE VideoSection (SectionName varchar(80), SectionSortOrder int, VideoSectionId int AUTO_INCREMENT PRIMARY KEY) ================ PICTURE database ================ CREATE TABLE PictureSection (SectionName varchar(255), SortKey int, SectionId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PictureSet (SetName varchar(255), // e.g., "Japan" Paren varchar(255), // e.g., "(January 2005)" Description blob, SectionId int, SortKey int, OldPhpUrl varchar(255), PrivateFlag int, // 0 = false, 1 = true [hides it from pictures.php4 menu!] PasswordList varchar(255), [e.g., ",pw1,pw2,pw3,"] CreateDateTime datetime, SetId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PictureItem (Url varchar(255), Height int, Width int, SetId int, SortKey int, Caption varchar(255), Description blob, FullUrl varchar(255), ThumbUrl varchar(255), RecordId int AUTO_INCREMENT PRIMARY KEY) ======================= POKEMON CARD collection ======================= CREATE TABLE PokemonCardItem (CardTypeId int NOT NULL, CardTitle varchar(100) NOT NULL, CardTitleJapan varchar(100), // in hex ISO-2022-JP PictureUrl varchar(255), CharacterId int, // index into PokemonCharacter table IllustratorId int, // index into PokemonIllustrator table IllustratorId2 int, // index into PokemonIllustrator table CardSetId int, // index into PokemonCardSet table Quantity int, CaitlynCardRatingValue int, HolographicFlag int, // 0 = false, 1 = true ReverseHolographicFlag int, // 0 = false, 1 = true FirstEditionFlag int, // 0 = false, 1 = true PromoFlag int, // 0 = false, 1 = true NoTradeFlag int, // 0 = false, 1 = true CounterfeitFlag int, // 0 = false, 1 = true PokedexDescription blob, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data CountryId int, Year int, HpValue int, WeaknessTypeId1 int, // index into PokemonType table WeaknessTypeId2 int, WeaknessOffset int, // (positive) WeaknessOperator int, // 1=x, 2=+, 3=-, 4=? ResistanceTypeId1 int, // index into PokemonType table ResistanceTypeId2 int, ResistanceOffset int, // (positive) ResistanceOperator int, // 1=x, 2=+, 3=-, 4=? RetreatCost int, // (0-3) RarityId int, // index into PokemonRarity table CardNumberInSet int, // numerator CnsUseAsciiFlag int, // flag to display CardNumberInSet as ASCII (0 = false, 1 = true) CnsLeadingHFlag int, // flag to display "H" before numerator and denominator (0 = false, 1 = true) CnsLeadingDpFlag int, // flag to display "DP" before numerator and denominator (0 = false, 1 = true) CnsLeadingShFlag int, // flag to display "SH" before numerator and denominator (0 = false, 1 = true) CnsLeadingArFlag int, // flag to display "AR" before numerator and denominator (0 = false, 1 = true) CnsLeadingHgssFlag int, // flag to display "HGSS" before numerator and denominator (0 = false, 1 = true) CnsLeadingBwFlag int, // flag to display "BW" before numerator and denominator (0 = false, 1 = true) HideDenominatorFlag int, // flag to not display the slash and the denominator (0 = false, 1 = true) RecordId int AUTO_INCREMENT PRIMARY KEY, Unique(CardTypeId,CardSetId,CardTitle,CountryId)) CREATE TABLE PokemonCharacter (CharacterName varchar(100) NOT NULL, Species varchar(50), CharacterNumber int, RegionId int, CharacterRatingValue int, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data TypeId1 int, TypeId2 int, EvolutionStageId int, LengthInches real, WeightPounds real, CharacterPictureUrl varchar(255), CharacterId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PokemonFigurine (FigurineName varchar(100) NOT NULL, CharacterId int, // index into PokemonCharacter FigurinePictureUrl varchar(255), FigurineId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PokemonCardRating (CardRatingName varchar(20), CardRatingValue int PRIMARY KEY) INSERT INTO PokemonCardRating VALUES ("not rated", 0) INSERT INTO PokemonCardRating VALUES ("OK", 1) INSERT INTO PokemonCardRating VALUES ("fair", 2) INSERT INTO PokemonCardRating VALUES ("good", 3) INSERT INTO PokemonCardRating VALUES ("excellent", 4) INSERT INTO PokemonCardRating VALUES ("favorite", 5) CREATE TABLE PokemonCardSet (CardSetName varchar(64) NOT NULL, IconUrl varchar(255), CardsPerSet int, // denominator SortOrder int, CardSetId int AUTO_INCREMENT PRIMARY KEY, Unique(CardSetName)) CREATE TABLE PokemonCardType (CardTypeName varchar(32) NOT NULL, IconUrl varchar(255), CardTypeId int AUTO_INCREMENT PRIMARY KEY, Unique(CardTypeName)) INSERT INTO PokemonCardType (CardTypeName) VALUES ("Standard") [CardTypeId = 1 for this entry] INSERT INTO PokemonCardType (CardTypeName) VALUES ("Energy") [CardTypeId = 2 for this entry] INSERT INTO PokemonCardType (CardTypeName) VALUES ("Trainer") [CardTypeId = 3 for this entry] CREATE TABLE PokemonType (TypeName varchar(64) NOT NULL, IconUrl varchar(255), TypeId int AUTO_INCREMENT PRIMARY KEY, Unique(TypeName)) CREATE TABLE PokemonEvolutionStage (EvolutionStageName varchar(32) NOT NULL, IconUrl varchar(255), EvolutionStageId int AUTO_INCREMENT PRIMARY KEY, Unique(EvolutionStageName)) CREATE TABLE PokemonIllustrator (IllustratorName varchar(60) NOT NULL, IllustratorId int AUTO_INCREMENT PRIMARY KEY, Unique(IllustratorName)) CREATE TABLE PokemonRarity (RarityName varchar(32) NOT NULL, IconUrl varchar(255), RarityId int AUTO_INCREMENT PRIMARY KEY, Unique(RarityName)) INSERT INTO PokemonRarity (RarityName) VALUES ("Circle") INSERT INTO PokemonRarity (RarityName) VALUES ("Diamond") INSERT INTO PokemonRarity (RarityName) VALUES ("Star") CREATE TABLE PokemonRegion (RegionName varchar(32) NOT NULL, IconUrl varchar(255), RegionId int AUTO_INCREMENT PRIMARY KEY, Unique(RegionName)) INSERT INTO PokemonRegion (RegionName) VALUES ("Kanto") INSERT INTO PokemonRegion (RegionName) VALUES ("Johto") INSERT INTO PokemonRegion (RegionName) VALUES ("Hoenn") INSERT INTO PokemonRegion (RegionName) VALUES ("Sinnoh") CREATE TABLE PokemonMove (MoveName varchar(100), MoveNameJapan varchar(100), // in hex ISO-2022-JP MoveDescription blob, EcTypeId1 int, EcTypeId2 int, EcTypeId3 int, EcTypeId4 int, EcTypeId5 int, DamageCount int, DamageOperator int, // 1=x, 2=+, 3=-, 4=? CardRecordId int, SortKey int, MoveId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE PokemonEvolve (TriggerName varchar(100), FromCharacterId int, ToCharacterId int, EvolveId int AUTO_INCREMENT PRIMARY KEY) =============== RECORD database =============== CREATE TABLE RecordLabel (LabelName varchar(32) NOT NULL, WebSiteUrl varchar(100), LogoUrl varchar(100), LogoHeight int, LogoWidth int, Abbreviation varchar(50), Owner varchar(255), Blurb blob, FamilyId int, // 0 = none, // 1 = Desert / Mountain / Ocean / Prairie / Ute // 2 = Chicago Country / Cross Country // 3 = Chaparral / Coyote / Roadrunner // 4 = Circle-D / A-Bar-K // 5 = Scope / Big Mac // 6 = Silver Sounds / Sounds 2000 / Square One // 7 = Sting / Snow // 8 = Buckskin / Rawhide // 9 = Chinook / Hoedowner // 10 = ESP / Jopat // 11 = Crown / Castle // 12 = C-Bar-C / Seven-Cs // 13 = Eagle / Golden Eagle // 14 = HiHat / Blue Ribbon / DJ / Elk / Merry-Go-Round // 15 = 4-Bar-B / Quadrille / Cardinal // 16 = Rockin'M / Stampede / TJ // 17 = Blue Star / B Sharp / Bob Cat / Bogan / // Dance Ranch / E-Z / Lore / Petticoat Patter / // Rockin'A / Swinging Square // 18 = Red Boot / Red Boot Star / G & W / Gold Star / Flutter Wheel // 19 = Solid Gold / Silver Streak // 20 = Black Mountain (Valley) / Pioneer / Square Tunes // 21 = Grand / Enis // 22 = Mac Gregor / Cascade / Belco / Kalox / Longhorn // 23 = Top / Grenn / FTC / CEM // 24 = Mustang / Lightning S // 25 = Cimmaron / Roofers // 26 = Rhythm / Lone Star // 27 = Lou Mac / Mar Let // 28 = // 29 = Mason Dixon Line / County Line // 30 = Jay-Bar-Kay / Pilgrim // 31 = Fine Tune / Gold Wing / Sharpshooter // 32 = Thunderbird / Falcon // 33 = Decca / Coral LabelId int AUTO_INCREMENT PRIMARY KEY, UNIQUE(LabelName)) INSERT INTO RecordLabel (LabelName) VALUES ("Quadrille") CREATE TABLE RecordType (TypeName varchar(40), TypeId int PRIMARY KEY) INSERT INTO RecordType VALUES ("Singing Call", 1) INSERT INTO RecordType VALUES ("Patter", 2) INSERT INTO RecordType VALUES ("Patter (two instrumentals)", 3) INSERT INTO RecordType VALUES ("Singing Call (two instrumentals)", 4) INSERT INTO RecordType VALUES ("Round", 5) INSERT INTO RecordType VALUES ("Round (two-sided)", 6) INSERT INTO RecordType VALUES ("Contra", 7) INSERT INTO RecordType VALUES ("Contra (two-sided)", 8) INSERT INTO RecordType VALUES ("Line", 9) INSERT INTO RecordType VALUES ("Line (two-sided)", 10) INSERT INTO RecordType VALUES ("Sing-Along", 11) INSERT INTO RecordType VALUES ("Sing-Along (two-sided)", 12) INSERT INTO RecordType VALUES ("Singing Call (two vocals)", 13) INSERT INTO RecordType VALUES ("Singing Call (vocal) + Patter", 14) INSERT INTO RecordType VALUES ("Singing Call (instrumental) + Patter", 15) INSERT INTO RecordType VALUES ("Patter (two vocals)", 16) INSERT INTO RecordType VALUES ("Mixer", 17) CREATE TABLE MediaType (MediaName varchar(32), MediaId int PRIMARY KEY) INSERT INTO MediaType VALUES ("33+1/3 rpm vinyl", 1) INSERT INTO MediaType VALUES ("45 rpm vinyl", 2) INSERT INTO MediaType VALUES ("78 rpm vinyl", 3) INSERT INTO MediaType VALUES ("CD", 4) INSERT INTO MediaType VALUES ("MP3", 5) CREATE TABLE AuthorArtistList (AuthorName varchar(60) NOT NULL, AuthorFirstName varchar(30), AuthorLastName varchar(30), CallerDbIndex int, CcdbFixed int, // 0 = false, 1 = true (if true, CallerDbIndex will NOT change by code) Squares int, // 0 = false, 1 = true Rounds int, // 0 = false, 1 = true Definitions int, // 0 = false, 1 = true Articles int, // 0 = false, 1 = true PictureUrl varchar(255), PictureHeight int, PictureWidth int, Biography blob, AdminComments blob, AuthorArtistId int AUTO_INCREMENT PRIMARY KEY, Unique(AuthorName)) INSERT INTO AuthorArtistList (AuthorName) VALUES ("-") [AuthorArtistId = 1 for this entry] INSERT INTO AuthorArtistList (AuthorName) VALUES ("Bob Huff") CREATE TABLE RecordList (LabelId int NOT NULL, RecordNumber varchar(20) NOT NULL, Title varchar(100), OldCallsRecordIdList varchar(255), // indices into DefinitionDb, stored with a comma on both ends (e.g., ",9,122,2124,") Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data CueSheet blob, // cue sheet for Side A CsLevel1 varchar(255), // Figure Level in cue sheet side A (separated by commas) CsProgression1 varchar(40), // Figure Progression in cue sheet side A (1=corner,2=rhlady) (separated by commas) OmcLevel1 varchar(255), // OMC Level (Opener, Middle Break, Closer) OmcProgression1 varchar(40), // OMC Progression CueSheet2 blob, // cue sheet for Side B CsLevel2 varchar(255), // Level for figure in cue sheet side B (separated by commas) CsProgression2 varchar(40), // Progression for figure in cue sheet side B (1=corner,2=rhlady) (separated by commas) OmcLevel2 varchar(255), // OMC Level (Opener, Middle Break, Closer) OmcProgression2 varchar(40), // OMC Progression LyricsId int, // lyrics for Side A LyricsId2 int, // lyrics for Side B Mp3Pathname varchar(128), // mp3 pathname for Side A Mp3Pathname2 varchar(128), // mp3 pathname for Side B Bpm int, // BPM for Side A (Mp3Pathname) Bpm2 int, // BPM for Side B (Mp3Pathname2) Duration int, // Duration [seconds] for Side A (Mp3Pathname) Duration2 int, // Duration [seconds] for Side B (Mp3Pathname2) AuthorArtistId1 int, // index into AuthorArtistList AuthorArtistId2 int, // index into AuthorArtistList AuthorArtistId3 int, // index into AuthorArtistList AuthorArtistId4 int, // index into AuthorArtistList AuthorArtistId5 int, // index into AuthorArtistList AuthorArtistId6 int, // index into AuthorArtistList Copies int, Rating int, TypeId int, // index into RecordType MediaId int, // index into MediaType InWishList int, // 1 = in wish list, 0 = not in wish list TLM varchar(10), // yyyy-mm-dd TZZ varchar(8), // hh:mm:ss RecordId int AUTO_INCREMENT PRIMARY KEY, Unique(LabelId,RecordNumber)) CREATE TABLE LyricsTable (Title varchar(128), Lyrics blob, Comments blob, Links blob, Year int, ComposedBy varchar(150), LyricsBy varchar(150), RecordedBy varchar(150), TLM varchar(10), // yyyy-mm-dd TZZ varchar(8), // hh:mm:ss LyricsId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE RecordRating (ProseRating varchar(20), Rating int PRIMARY KEY) INSERT INTO RecordRating VALUES ("not rated", 0) INSERT INTO RecordRating VALUES ("dislike", 1) INSERT INTO RecordRating VALUES ("below average", 2) INSERT INTO RecordRating VALUES ("average", 3) INSERT INTO RecordRating VALUES ("good", 4) INSERT INTO RecordRating VALUES ("excellent", 5) CREATE TABLE Phrases (Phrase varchar(70), Handle int, Handle2 int, Handle3 int, Handle4 int, RecordId int AUTO_INCREMENT PRIMARY KEY, UNIQUE(Phrase)) CREATE TABLE IgnorePhrases (IgnorePhrase varchar(70), RecordId int AUTO_INCREMENT PRIMARY KEY, UNIQUE(IgnorePhrase)) CREATE TABLE RecordSaveSql (SqlStr blob, ModDateTime datetime, SqlId int AUTO_INCREMENT PRIMARY KEY) ================ RELEASE database ================ CREATE TABLE ReleaseItem (PurchaseDate date, Name varchar(100), ContactId int, // index into ContactDb FullVersionFlag int, // 1 = full version, 0 = sqrot/demo PaidByInd int, // 1 = 2CO, 2 = Cash, 3 = Check, 4 = Complimentary Comments blob, PaidAmount real, CsdsVersion varchar(20), SqrotVersion varchar(20), ReleaseId int AUTO_INCREMENT PRIMARY KEY) ================== RESOURCES database ================== CREATE TABLE ResourceType (TypeName varchar(20), ResourceTypeId int AUTO_INCREMENT PRIMARY KEY) INSERT INTO ResourceType VALUES ("Organizations", 2) INSERT INTO ResourceType VALUES ("Publications", 3) INSERT INTO ResourceType VALUES ("Dance Halls", 4) INSERT INTO ResourceType VALUES ("General Resources", 5) CREATE TABLE ResourcesDb (Title varchar(128), ResourceTypeId int, City varchar(50), StateId int, CountryName varchar(32), URL varchar(100), Email1 varchar(50), Name1 varchar(60), // name corresponding to Email1 Email2 varchar(50), Name2 varchar(60), // name corresponding to Email2 weblink int, InfoLine varchar(255), ModDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY) ========================= REVISION HISTORY database ========================= CREATE TABLE RevisionHistory (ProgramId int, // 1=CSDS, 2=SQROT Version varchar(20), // e.g., "1.13.02" ReleaseDate datetime, ChangesMade blob, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE VersionInfo (ProgramId int, // 1=CSDS, 2=SQROT VersionNumber varchar(20), // e.g.,'1.21.11' MonthYearStr varchar(20), // e.g.,'(December 2006)' ExecVer varchar(20), // e.g.,'1.21.11' ExecDate varchar(20), // e.g.,'2006+December+26' ExecBytes varchar(20), // e.g.,'1,470,991' ExecExe varchar(30), // e.g.,'csdszip12111.exe' CompVer varchar(20), // e.g.,'1.21.10' CompDate varchar(20), // e.g.,'2006+December+01' CompBytes varchar(20), // e.g,'14,834,688' CompExe varchar(30) // e.g.,'csds12110complete.exe' ) INSERT INTO VersionInfo (ProgramId) VALUES (1) INSERT INTO VersionInfo (ProgramId) VALUES (2) ============== TO DO database ============== CREATE TABLE ToDoDb (Text blob, SectionId int, CategoryId int, PriorityId int, RecordId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ToDoPriority (PriorityStr varchar(20), PriorityId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ToDoSection (SectionName varchar(80), SectionId int AUTO_INCREMENT PRIMARY KEY) CREATE TABLE ToDoCategory (CategoryName varchar(80), SectionId int, CategoryId int AUTO_INCREMENT PRIMARY KEY) ============== STUFF database ============== CREATE TABLE StuffDb (Title varchar(100) NOT NULL, SectionId int, // 1 = chocolate books, // 2 = pokemon paraphernalia, // 3 = beaniebaby paraphernalia PictureUrl varchar(100), PictureHeight int, PictureWidth int, Description blob, Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data StuffId int AUTO_INCREMENT PRIMARY KEY) =============== UPLOAD database =============== CREATE TABLE UploadDb (Filename varchar(80), Username varchar(50), Email varchar(50), Comments blob, UploadId int AUTO_INCREMENT PRIMARY KEY) =================== WINCHESTER database =================== CREATE TABLE WinchesterDb (SongTitle varchar(255), TapeNumber varchar(10), // e.g., "101" TrackNumber varchar(10), Mp3Url varchar(255), RecordListRecordId int, // RecordList.RecordId Bpm int, Transciption blob, LevelSortStr varchar(10), Comments blob, ExternalComments blob, // hidden concatenation of CommentsDb data MoreInfo blob, HandleList varchar(255), // stored with a comma on both ends (e.g., ",9,122,2124,") RecordId int AUTO_INCREMENT PRIMARY KEY) ===================== Sample SQL statements ===================== INSERT INTO CheckerCheat (CallName, Handle, Text) VALUES ('Alter The Diamond',138,'Diamond Snake') UPDATE callerdb SET CheckedByVdc = 1 WHERE RecordId = 28 'Delete record '------------- DELETE FROM callerdb WHERE RecordId = 25 'Fields '------ ADD ----- ALTER TABLE callerdb ADD PictureUrl varchar(255) CHANGE -- ALTER TABLE callerdb CHANGE EmailWork EmailWork varchar(40) DELETE -- ALTER TABLE FaqDb DROP TLM RENAME -- ALTER TABLE callerdb CHANGE Email EmailWork varchar(40) // finds all records with a 'bad' country SELECT callerdb.RecordId, callerdb.CountryName FROM callerdb LEFT JOIN Countries ON callerdb.CountryName=Countries.CountryName where Countries.CountryName is NULL 'Add Unique to table '------------------- ALTER TABLE RecordList ADD Unique(LabelId,RecordNumber) // find duplicate records in record database before label+num had to be unique SELECT A.RecordId AS ARec, B.RecordId AS BRec FROM RecordList AS A LEFT JOIN RecordList AS B ON ((A.LabelId = B.LabelId) AND (A.RecordNumber = B.RecordNumber)) WHERE (A.RecordId != B.RecordId) SELECT * FROM RecordLabel WHERE WebSiteUrl LIKE 'http://www.supreme%' SELECT * FROM ListDb WHERE DefinitionUrl LIKE '%php3%' SELECT LabelName FROM RecordLabel WHERE IFNULL(LogoUrl,'') = '' ORDER BY LabelName (finds empty or NULL LogoUrls) 'Rename Table '------------ ALTER TABLE SaveState RENAME AS CallerSaveState 'Fixing auto increment problem (moving from H to W): '--------------------------------------------------- Within phpmyadmin, goto table structure, set 'Extra' field of ID column to 'auto_increment', then execute SQL: ALTER TABLE tbl AUTO_INCREMENT = 533;