WACS: Database Schemas

Sets Table
Models Table
Idmap Table
Assoc Table
Download Table
Photographer Table
Tag Table
Vendor Table

Sets Table

This is the main sets table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields. Fieldnames in bold are new additions at this release that are not yet required by the production code release (the SVN version probably will require them at any given point).

create table sets
( setno			number(9) primary key,
  stype                 char(1) not null,
  sstatus               char(1) not null,
  sauto                 char(1),
  srating               char(1),
  sflag                 char(1),
  stechqual             number(2),
  svariety              number(2),
  svisits               number(2),
  sformat		varchar2(10),
  scodec                varchar2(40),
  stitle                varchar2(240),
  sofftitle		varchar2(240),
  sofficon		varchar2(160),
  saddicon		varchar2(160),
  sname                 varchar2(80),
  shair                 varchar2(80),
  smodelno              varchar2(40),
  sphotog		varchar2(6) references photographer,
  ssource               varchar2(80),
  sfoundry              varchar2(80),
  sproddate             date,
  suscattr              char(1),
  snotes                varchar2(240),
  sdesc                 varchar2(2048),
  sindexes              number(6),
  simages               number(6),
  sdurhrs		number(2),
  sdurmin               number(2),
  sdursec               number(2),
  slandx                number(6),
  slandy                number(6),
  sportx                number(6),
  sporty                number(6),
  saspect		varchar(10),
  sbytes		number(12),
  sdvdno		number(6),
  sdvddisc		number(2),
  sdvdtitle		number(3),
  sdvdstartch		number(3),
  sdvdendch             number(3),
  sidlogo		char(1),
  serrors		char(1),
  sduplicates		number(9),
  scatinfo              varchar2(160),
  scatflag		char(1),
  snamestem             varchar2(80),
  sdownload             varchar2(160),
  sarea			varchar2(160),
  scategory		varchar2(160),
  sdirectory		varchar2(240),
  scomments             varchar2(240),
  sadded                date,
  samended              date
);

Values For Short Fields In Sets Table
stype	I	Image Set
	V	Video Clip
	A	Audio File
	S	DVD Scene
sstatus	M	Manually Added, Details Not Checked
	A	Automatically Added, Details Not Checked
	N	Normal - Checked
	G	Good - Thoroughly Checked
	U	Unknown
sauto	N	None
	L	Location Only - catinfo manual
	A	Append to catinfo - remove nothing
	F	Fully auto-generated
srating	5	Finest
	4	Very Good
	3	Good
	2	Reasonable
	1	Mediocre
	0	None Specified
stechqual	as for srating
svariety	5	Very Unusual
	4	Unusual
	3	Neat
	2	Cute Twist
	1	Ordinary
	0	None Specified
sformat	JPEG	JPEG image
	GIF	GIF Image
	PNG	PNG Image
	PNM	PNM Image
	WMV	Windows Media Player Video
	AVI	AVI Video
	QT	QuickTime .mov Video
	MPEG	MPEG Video (1/2)
sidlogo	U	Unknown
	Y	Yes - image/video has burnt-in logo
	N	No - image/video is clean of bugs
serrors	N	None detected
	F	Fixed - faulty images/video have been fixed
	E	Encoding Only - causes message but renders OK
	C	Some Corrupt Images/Segments of video
scatflag	F	Fuck - straight sex
	L	Lesbian - lesbian sex
	G	Group - more than two people having sex, mixed-gender
	T	Toy - Solo but uses toys such as dildo, vibrator, etc
	S	Solo
	N	None - not determined yet
	C	Clothed - non-nude set featuring this model
suscattr	V	Vendor based - use vendor's USC declaration address
	P	Photographer based - use photographer's address for USC declaration
	N	Suppress declaration - NOT RECOMMENDED FOR US RESIDENTS
	G	Generic - include generic text with all vendor addresses
	NB: this field will not be used until release 0.7.x

Models Table

This is the main models table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields. Please see the section on addmodel in the command line applications section for details of basic attributes: hair colour, length, titsize, etc.

create table models 
( modelno           number(6) primary key,
  mname             varchar2(40),
  mhair		    varchar2(15),
  mlength           varchar2(20),
  mtitsize          varchar2(10),
  mcupsize          char(1),
  meyes             varchar2(15),
  mrace		    varchar2(15),
  mattributes	    varchar2(60),
  maliases          varchar2(60),
  mdisting	    varchar2(80),
  musual            varchar2(60),
  mimage            varchar2(80),
  mbigimage	    varchar2(80),
  mstatus	    char(1),
  mrating           char(1),
  mpussy	    char(1),
  mflag		    char(1),
  mvideos           char(1),
  msolo             char(1),
  mstraight         char(1),
  mlesbian          char(1),
  mfetish           char(1),
  mother            char(1),
  mnsets            number(4),
  mnimages          number(7),
  mnvideos          number(4),
  mcountry          varchar2(30),
  mhometown	    varchar2(80),
  mage		    number(3),
  mageyear          number(4),
  mcstatus          char(1),
  mcontact	    varchar2(80),
  mnotes	    varchar2(240),
  madded            date,
  mamended	    date
);

Values For Short Fields In Models Table
mstatus	M	Manually Added, Details Not Checked
	N	Normal - Checked
	G	Good - Thoroughly Checked
	P	Placeholder - Not Real Person
mrating	5	Finest (included in Q= searches)
	4	Very Good (included in Q= searches)
	3	Good (not included in Q= searches)
	2	Reasonable (not included in Q= searches)
	1	Mediocre (not included in Q= searches)
	0	None Specified (listed in U= searches)
mpussy	H	Hairy
	T	Trimmed
	S	Shaven
	N	Not Specified
mflag	N	None
	S	Favourite Solo
	L	Favourite Lesbian
	C	Favourite Cutie
	F	Favourite Straight
	P	Placeholder
mvideos	Y - Yes, does this or N - No, doesn't do this updated by updatestats
msolo
mstraight
mlesbian
mfetish
mother
mcstatus	C	Certain - country of origin stated in bio
	I	Inferred - from location or other models seen with
	G	Guess - based on photographer or building style
	N	None
mrace	Caucasian	Caucasian - European Descent aka White
	Oriental	Oriental - Chinese, Japanese, SE Asian
	Asian	Indian Sub-Continent - India, Pakistan, etc
	Negroid	Negroid - of African Descent aka Black
	Aboriginal	Aboriginal - indigenous peoples - First Nation, Polynesian, etc
	Latina	Latin American - aka Hispanic
	Mixed	Mixed race and others

Idmap Table

This is the idmap table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields, along with some suggested values for isite. Note that now the vendor database has been implemented, it is likely that these two fields will become linked so that idmap.isite = vendor.vsite.

create table idmap
( identryno			number(7) primary key,
  imodelno		        number(6) references models,
  istatus			char(1),
  isite				varchar2(20) not null,
  ikey                          varchar2(30),
  ialtkey			varchar2(30),
  iname				varchar2(30),
  inotes			varchar2(80), 
  iactive			char(1),
  ichanged			date,
  ichecked			date,
  iadded			date,
  iamended			date
);

Values For Short Fields In Idmap Table
istatus	M	Manually Added
istatus	A	Automatically Added
iactive	Y	Yes - active model (refresh list with auto tools)
	D	Dormant - no new sets for a while (don't bother checking)
	N	No - inactive (disabled - since removed, etc)
Some Recommended Site Abbrievations
isite	AMK	AMKingdom.com (aka ATK Galeria)
	ATE	ATKExotics.com
	ATKP	ATKPremium.com
	AW	AbbyWinters.com
	IFG	infocusgirls.com
	JAFN	jennyandfriends.net
	SE	sapphicerotica.com
	TF	teenflood.com

Assoc Table

This is the assoc table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields.

create table assoc 
( assocno			number(9) primary key,
  amodelno			number(6) references models,
  asetno			number(9) references sets,
  astatus			char(1),
  aadded                        date,
  aamended                      date
);

Values For Short Fields In Assoc Table
astatus	M	Manually Added
	G	Generated Automatically
	R	Relationship entry - not the primary model for this set.

Download Table

This is the download table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields. There are a lot of statuses I know, but they each do mean something different; for instance an error set is kept around such that a future auto-generated pull list realises we have already seen that set and doesn't just pull it again as it would if we didn't keep it around.

create table download
( downloadno				number(7) primary key,
  dmodelno                              number(6) references models,
  dsetno                                number(9) references sets,
  dstatus                               char(1),
  dtype                                 char(1),
  dsite                                 varchar2(20) not null,
  dkey                                  varchar2(30),
  dsetkey                               varchar2(40),
  dsetname                              varchar2(240),
  dnotes                                varchar2(240),
  durl                                  varchar2(240),
  darchive                              varchar2(240),
  dpulled                               date,
  dadded                                date,
  damended                              date
);

Values For Short Fields In Download Table
dstatus	U	Not yet attempted
	F	Failed - Retry
	S	Successful - in database
	P	Pending - awaiting unpacking
	A	Aborted - don't download for some reason
	D	Deferred - held back from being downloaded
	R	Relationship Entry - a second model for a set
	E	Error - not the right model, etc
dtype	I	Image Set
	V	Video Clip
	A	Audio File

Photographer Table

This is the photographer table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields.

create table photographer
( pref             varchar2(6) primary key,
  pname            varchar2(40),
  paliases         varchar2(80),
  pgender          char(1),
  paddress         varchar2(120),
  pemail           varchar2(80),
  pwebsite         varchar2(80),
  pusual           varchar2(40),
  pregion          varchar2(20),
  pcountry         varchar2(50),
  plocation        varchar2(50),
  pstyledesc	   varchar2(80),
  prating	   number(2),
  phardness        number(2),
  psolo		   char(1),
  ptoys            char(1),
  plesbian         char(1),
  pstraight        char(1),
  pgroup           char(1),
  pfetish          char(1),
  pdigital	   char(1),
  pfilm		   char(1),
  pvideo           char(1),
  phdvideo         char(1),
  pcamera	   varchar2(40),
  pcamnotes        varchar2(80),
  pcomments        varchar2(240),
  pnotes           varchar2(240),
  pbiography       varchar2(1024),
  padded           date,
  pamended         date
);

Values For Short Fields In Photographer Table
pgender	M	Male
	F	Female
	U	Unknown
pregion	Europe	Europe
	North America	USA and Canada
	South America	South and Central America
	Middle East	Middle East (brave photographer!)
	Asia	Asia (India and the Indian Sub-continent ONLY)
	Orient	Orient (Asia excluding Indian Sub-continent)
	Australasia	Australia and New Zealand
	Africa	Africa
Other	Other
prating	0	None
	1	Awful
	2	Poor
	3	Reasonable
	4	Good
	5	Excellent
phardness	0	None
	1	Soft-focus (very arty)
	2	Glamour
	3	Normal
	4	Hard (close-ups)
	5	Fetish
psolo	Y - Yes, does this O - Occasionally does this or N - No, doesn't do this
ptoys
plesbian
pstraight
pgroup
pfetish
pdigital	Y - Yes, uses this technology or N - No, doesn't use this technology
pfilm
pvideo
phdvideo

Tag Table

This is the tag table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields.

create table tag
( tagno				number(9) primary key,
  tmodelno                      number(6) references models,
  tsetno                        number(9) references sets,
  tstatus			char(1),
  tflag				char(1),
  tgroup			number(3),
  tdesc				varchar2(40),
  towner			varchar2(20),
  texpiry			date,
  tadded			date,
  tamended			date 
);

Values For Short Fields In Tag Table
tstatus	T	Temporary
tstatus	P	Permanent
tflag	M	Model-based tag entry
tflag	S	Set-based tag entry

Vendor Table

This is the vendor table - directly below is the sample SQL command to create it and below that details of the fixed values for each of the short fields. The vendor table is probably the most likely to change over time as it is used primarily for the parsing of pages from other websites, and as the techniques used for that purpose become more sophisticated, the requirements for this table which drives that process may well change. Note that the vendors.xml file in the populate directory primes this table with some (potentially useable) sample entries.

create table vendor
( vsite		   varchar2(20) primary key,
  vname		   varchar2(45),
  vshortname	   varchar2(20) not null,
  vregion	   varchar2(20),
  vcountry	   varchar2(50),
  vweburl          varchar2(120),
  vrating          number(2),
  vtechrate        number(2),
  vuscdecl         varchar2(240),
  vcurrent         char(1),
  vshow		   char(1),
  vsubscribed      char(1),
  vuntil           date,
  vusername        varchar2(80),
  vpassword        varchar2(30),
  vidtimg	   number(2),
  vidtvid          number(2),
  vcomexcl	   varchar2(240),
  vmdirectory      varchar2(240),
  vmdiruse         char(1),
  vmdirpages       number(3),
  vmpage	   varchar2(240),
  vmpaguse         char(1),
  vmbio            varchar2(240),
  vmbiouse         char(1),
  vmvideos         varchar2(240),
  vmviduse         char(1),
  vvidpage	   varchar2(240),
  vviduse          char(1),
  vimgpage         varchar2(240),
  vimguse          char(1),
  valtpage         varchar2(240),
  valtuse          char(1),
  vsrvimg	   varchar2(240),
  vsrvvid          varchar2(240),
  vmultimg	   char(1),
  vmultvid         char(1),
  vnotes	   varchar2(240),
  vadded           date,
  vamended         date
);

Values For Short Fields In Vendor Table
vcurrent	Y	Yes - still an active site
vcurrent	N	No - no longer trading
vshow	Y	Yes - show in indexes
vshow	N	No - hide from indexes
vsubscribed	Y	Yes - have an active subscription
vsubscribed	N	No - not currently subscribed

Next Section: Futures
Back to WACS Documentation Index