Hi(gh)!

As announced, here my first version of a relational database structure 
for images on p.b.i:

DATABASE SYSTEM: MySQL (preferably higher than 4.1, allowing subqueries)

TABLES:

images - with the following fields:
- ID (primary key, auto_increment)
- Filename (including path)
- Title
- POVerNick_ID <- table 'povernicks', n:1 relation
- Date (published on p.b.i)
- Format_ID <- table 'formats', n:1 relation
- Size (in bytes)
- Width
- Height
(Number of pixels and aspect ratio can be calculated within MySQL 
queries, therefore no explicit field is needed)
- POVVersion <- table 'povversions', n:1 relation
- ImageVersion
- PostProcessed
- (NumColors)
- (AvgBrightness)
- (AvgSaturation)
- (FreqHue0-35)
- (FreqHue36-71)
- (FreqHue72-107) ... (FreqHue324-359)
- (FreqBright0-10)
- (FreqBright11-20) ... (FreqBright91-100)
- (FreqSatur0-10)
- (FreqSatur11-20) .. (FreqSatur91-100)

(the entries for fields given in brackets are to be calculated by an 
external utility program)

povernicks (nicknames of POVers)
- ID (primary key, auto_increment)
- Nickname

poverreal (real names of POVers - if known!)
- ID (primary key, auto_increment)
- FirstName
- Surname

povernicks_poverreal (linking table between tables 'povernicks' and 
'poverreal', m:n relation)
- ID (primary key, auto_increment)
- POVerNicks_ID <- table 'povernicks'
- POVerReal_ID <- table 'poverreal'

formats
- ID (primary key, auto_increment)
- Extension
- Name

povversions
- ID (primary key, auto_increment)
- Name

keycont1st (content keywords, 1st hierarchy level)
- ID (primary key, auto_increment)
- Name

keycont2nd (content keywords, 2nd hierarchy level)
- ID (primary key, auto_increment)
- Name
- Keycont1st_ID <- table 'keycont1st', n:1 relation

keycont3rd (content keywords, 3rd hierarchy level)
- ID (primary key, auto_increment)
- Name
- Keycont2nd_ID <- table 'keycont2nd', n:1 relation

keycont (content keywords, base level)
- ID (primary key, auto_increment)
- Name
- Keycont3rd_ID <- table 'keycont3rd, n:1 relation

images_keycont (linking table between tables 'images' and 'keycont', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Keycont_ID <- table 'keycont'

keypov (POV features keywords)
- ID (primary key, auto_increment)
- Name

images_keypov (linking table between tables 'images' and 'keypov', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- KeyPOV_ID <- table 'keypov'

scripts (scene files, include files)
- ID (primary key, auto_increment)
- Filename (including path)
- Type (main script vs. include)
- Size (in bytes)
- NumLines

images_scripts (linking table between tables 'images' and 'scripts', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Scripts_ID <- table 'scripts'

externalfiles (any non-script file used for the calculation of the 
scene, such as textures, heightfields, bumpmaps...)
- ID (primary key, auto_increment)
- Filename (including path)
- Type
- Format_ID <- table 'formats', n:1 relation
- Size (in bytes)

images_externalfiles (linking table between tables 'images' and 
'externalfiles', m:n relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Externalfiles_ID <- table 'externalfiles'

addsoftware (additionally used software, i. e. modellers, 
post-processing tools, script generators etc.)
- ID (primary key, auto increment)
- Name
- Link (to download, if freeware/OpenSource)

images_addsoftware (linking table between tables 'images' and 
'addsoftware', m:n relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- AddSoftware_ID <- table 'addsoftware'

See you in Khyberspace!

Yadgar

Post a reply to this message

Le 21/04/2013 02:41, Jörg 'Yadgar' Bleimann nous fit lire :
> Hi(gh)!
> 
> As announced, here my first version of a relational database structure
> for images on p.b.i:
> 
> DATABASE SYSTEM: MySQL (preferably higher than 4.1, allowing subqueries)
> 
> TABLES:

Do you have a picture of it (just with table's names ?)
(indeed, the relational schema would be better, before projection in table)

Post a reply to this message

Hi(gh)!

On 21.04.2013 08:57, Le_Forgeron wrote:
> Le 21/04/2013 02:41, Jörg 'Yadgar' Bleimann nous fit lire :

> Do you have a picture of it (just with table's names ?)
> (indeed, the relational schema would be better, before projection in table)

Voilà:

(see p.b.i)

See you in Khyberspace!

Yadgar

Post a reply to this message

> As announced, here my first version of a relational database structure
> for images on p.b.i:

Looks ok so far. In the images table would it be a good idea to have a 
reference to the original p.b.i thread (message ID or http link or 
something?).

Should be quite easy to write some code that would trawl p.b.i and 
populate the tables (at least most of it, apart from the keywords). 
You'd then need something else running that continuously monitors posts 
to p.b.i and indexes them in real time.

Post a reply to this message

Hi(gh)!

On 22.04.2013 13:55, scott wrote:

> Looks ok so far. In the images table would it be a good idea to have a
> reference to the original p.b.i thread (message ID or http link or
> something?).

This would be a simple attribute, not a table on its own!

> Should be quite easy to write some code that would trawl p.b.i and
> populate the tables (at least most of it, apart from the keywords).
> You'd then need something else running that continuously monitors posts
> to p.b.i and indexes them in real time.

I don't know how to write code to automatically scan a newsgroup...

See you in Khyberspace!

Yadgar

Post a reply to this message

>> Looks ok so far. In the images table would it be a good idea to have a
>> reference to the original p.b.i thread (message ID or http link or
>> something?).
>
> This would be a simple attribute, not a table on its own!

Yes sorry I didn't mean reference as in a databased reference.

>> Should be quite easy to write some code that would trawl p.b.i and
>> populate the tables (at least most of it, apart from the keywords).
>> You'd then need something else running that continuously monitors posts
>> to p.b.i and indexes them in real time.
>
> I don't know how to write code to automatically scan a newsgroup...

Nor do I, but I doubt the nntp client protocol is particularly taxing to 
implement. Just thought it would save a lot of manual work.

Post a reply to this message