Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Oct 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Sql tables, if's and delete help - newbie

    Hi guys,

    Let me 1stly say that I apologize if me asking for this help here annoys you are are wrong, but I'm new to this and the guy that does these things for me is not available anymore.

    I am trying to learn but I'd really like some help

    Basically this is in regard to my Dayz SQL database where I have tables and want to do something with them to get the desired result

    I have a table called "object_data" and it looks like this:

    http://oi42.tinypic.com/16aqmux.jpg

    I have tried other ways of doing what I want like the following trigger:

    Code:
    DELIMITER $$
    CREATE TRIGGER update_object_datestamp 
        BEFORE UPDATE ON object_data
        FOR EACH ROW BEGIN
        SET NEW.Datestamp = NOW(); 
    END$$
    DELIMITER ;
    but that does not work because every time the server restarts it also updates the datestamp. So that is a no go.

    But now I want to try the following. I am going to explain it in my simple way so hope you understand.

    I want to use the object_data.Worldspace column to keep track if a vehicle has been used/moved and if that did not happen during the last 7 dayz I want to remove it, when I run the cleanup code.

    I guess I can make another Table called "VehicleTracker" and in there keep track of what the "Worldspace" is and the last date that it has changed in the object_data table.

    This is what I'd normally run to do a cleanup:

    Code:
    BEGIN
    DELETE FROM object_data 
    WHERE Hitpoints LIKE '%wheel_1_1_steering",1%'
    AND Hitpoints LIKE '%wheel_1_2_steering",1%'
    AND Hitpoints LIKE '%wheel_2_1_steering",1%'
    AND Hitpoints LIKE '%wheel_2_2_steering",1%';
    END
    But with I'd like add the time thing in there like

    VehicleTracker.LastUpdateDate < NOW() - INTERVAL 7 DAY

    Does that make sense at all?

    I'd like to be able to do / add everything needed in the console (I use Navicat)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,933
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Let's start here:
    but that does not work because every time the server restarts it also updates the datestamp.
    That makes no sense. Why would a server restart cause an update on every row of your table?

    There is something very important in there that you aren't telling us.

    ********

    And this makes little sense to me:
    Code:
    DELETE FROM object_data 
    WHERE Hitpoints LIKE '%wheel_1_1_steering",1%'
    AND Hitpoints LIKE '%wheel_1_2_steering",1%'
    AND Hitpoints LIKE '%wheel_2_1_steering",1%'
    AND Hitpoints LIKE '%wheel_2_2_steering",1%';
    In order for that query to delete anything, you would have to have a SINGLE RECORD where the value of Hitpoints looked something like this:
    Code:
     Joe said "wheel_1_1_steering",1 time, and then Bob said "wheel_1_2_steering",1 time, 
    and then Harry said "wheel_2_1_steering",1 time, and Anne said "wheel_2_2_steering",1 time, as well.
    REALLY???

    My gut feeling here is that you have a badly designed database. But it's pretty hard to tell.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Limpnoodle (10-15-2013)

  • #3
    New to the CF scene
    Join Date
    Oct 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    Sorry if that is not to clear. This is a SQL database for a DayZ Mod

    The database is full of vehicles and there are allowed to be 400 in there. If I delete one or it gets destroyed a new one will spawn on server restart.

    I really do not know why it is updating the datestamp on every single vehicle that already exists on every restart.
    I am trying to find that out still every day.

    The delete code is for the 4 wheels from a vehicle, so yes as you basically said when all 4 wheels of the vehicle is destroyed then it will delete the vehicle.

    But what I'd like to do is get a date/time in there to keep track when last a vehicle was used or moved and seeing that for some reason it updates all the vehicles on restarted I wanted to make use of the worldpace column, which will change if someone actually moves the vehicle.

    I find it a bit hard to explain all this. Sorry

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,933
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    The delete code is for the 4 wheels from a vehicle, so yes as you basically said when all 4 wheels of the vehicle is destroyed then it will delete the vehicle.
    As I suspected, then, this database design is horribly wrong and inadequate.

    I'm not even sure where to start, other than by throwing out the database and redesigning it using proper normalization, proper foreign keys, etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Oct 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm sure you are right. TY for your time!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •