#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    Question Newbie: Design of fileaccounting records


    Dear postgres users,

    A little introduction:

    I'am asked to store records about *all* files on our storage-systems in order to bill the costs every 4 weeks. (This is only part of the so called system-accounting here, and has to include cpu-usuage records in the near future too).

    The file record I dreamed up so far looks like:

    CREATE TABLE file (
    id SERIAL NOT NULL,
    mountpoint VARCHAR(40),
    username VARCHAR(40),
    groupname VARCHAR(40),
    pathname VARCHAR(255),
    servername VARCHAR(255),
    firstseen DATE,
    lastseen DATE,
    kilobyte INT2,
    mbdays_debit INT4,
    mbdays_total INT4,
    filename VARCHAR(255),
    PRIMARY KEY (id)
    );

    and one example of many little tables:

    CREATE TABLE mountpoints (
    id SERIAL NOT NULL,
    mountpoint VARCHAR(40) UNIQUE,
    PRIMARY KEY (id)
    );


    My question, and for me as newbie the holy grial, would be to store the mountpoint/groupname etc etc. in seperate tables so I won't store too much data for each given file. ( filepath only gets stored once etc. )

    I'am totally lost reading and trying foreign keys to solve the problem.

    My pseudocode to store the millions of records:
    if (mountpoint exists in table mountpoints )
    use the ID
    else
    add mountpoint to table mountpoints

    and so on for each non-unique field.

    A query could be (totally bogus):

    select file.username,m.mountpoint from mountpoints as m where file.mountpoint = m.mountpoint.id AND m.mountpoint="/data";

    Any good tutorials / learning cases about this trivial setup?

    Thanks for any help to demystify this rather simple (?) structure.

    Regards,
    Leroy

    NB: I'am using perl to store the records
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    here's one part that I don't get:


    if (mountpoint exists in table mountpoints )
    use the ID
    else
    add mountpoint to table mountpoints

    what are you using it for? if you don't find it and you make a new insertion into mountpoints, do you still use the ID that you were looking for? As far as referential integrity, you just want to have a foreign key reperesentation of a "mountpoint" which would be mountpoint_id REFERENCES mountpoints ( id )

    another way of writing foreign keys is
    CREATE CONSTRAINT mountpoint_fk FOREIGN KEY mountpoint_id REFERENCES mountpoints ( id )

    they are the same. I guess what you're wondering is if you make foreign keys on both sides-- you would then have the PRIMARY KEY ( id ) from MOUNTPOINTS also listed in the foreign key. It's only one column so it's not that bad. If you want to add foreign key constraints to an already existing table then the data in the table must meet those constraints. Your use of perl should allow you to know beforehand if an insertion will cause an integrity violation. Otherwise, you could probably also put the select/insert block into a try/catch block that will prob. throw a SQLException about the foreign keys being violated. An insert will fail if the value that is referenced does not exist in the referenced table. You can add delete/update behavior to foreign key referenced values, such as ON DELETE NULL, or ON UPDATE CASCADE

    in the cascade case it will change the matching value that is referenced. (cascading the constraints over all referenced values)

    When you alias a table you do not have to use AS, and that will make it easier for you.

    Your query would then be
    select f.username, m.mountpoint
    FROM mountpoints m, filelisting f
    WHERE f.mountpoint_id = m.id
    AND m.mountpoint="/data";


    You're on the right track overall. Play with the foreign keys. here's the page in the manual about table creation, there's more about table alteration somewhere thereabouts:



    http://www.postgresql.org/docs/7.2/s...eatetable.html

IMN logo majestic logo threadwatch logo seochat tools logo