Announcement

Collapse
No announcement yet.

DB Manager Discussion

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    DB Manager Discussion

    This is the discussion for the the DB Manager script

    ---
    Todd
    My HomeSeer | My Website | HomeSeer WebRing
    Tags: None
    #2
    OK, in case there is any confusion with the sql queries, here it is explained:

    From the '10 day query' in the file:
    <pre class="ip-ubbcode-code-pre">sql="DELETE FROM Temperature"
    sql=sql & " WHERE SampleDate &lt; (date() - 10)"
    sql=sql & " AND SampleDate NOT LIKE '%:00:%' "
    sql=sql & " AND SampleDate NOT LIKE '%:15:%' "
    sql=sql & " AND SampleDate NOT LIKE '%:30:%' "
    sql=sql & " AND SampleDate NOT LIKE '%:45:%' " </pre>

    The first line is self explanatory, you are going to delete from the 'Temperature' table.

    The second line begins the where clause to choose what I want to delete (without this, it would delete everything). SampleDate is the column in my database that contains the timestamp of when that temperature reading was taken. Since I am just dealing with the ones that are over 10 days old. I tell it that I want all the entries that are less then (or older) then the current date minus 10 days. That isn't the VB date function, that date() is going to be filled in by the DBMS when it execute the sql statement. That's why it is passed as litteral text and not the function.

    The next 4 lines whittle it down some more. Basically I am deleting everything EXCEPT the 15s of the hour. So that's why it is saying NOT LIKE. It will exclude those and not delete them. The %s are SQL's wildcard character. You should be able to use the more standard * wildcard character since it is allowed in Access, but I would test it out on a copy of the database first. The time stamp is in the format "Some date information 10:04:35 AM". So it will ignore everything except the ":04:" (in this case). And since it isn't one of the 15's that line is going bye-bye (as long as the date is over 10 days ago)

    Hopefully that helps any of you wanting to make your own delete statements

    ---
    Todd
    My HomeSeer | My Website | HomeSeer WebRing

    Comment

      #3
      Nice idea to to weed-out older data that does not need the same resolution as the more recent entries.

      Compacting is also a nice idea. I need to look into UltraLog to see if there is a scripting interface to it that will allow the database to be closed then reopened after the compation is done.

      When temperature data is being collected at 1 minute intervals then there is a reasonable chance that the database will be locked by the temperature script at the instant that vr_dbManager tried to do its thing. Are there any retries implemented with some wait inteval for the retry?

      Comment

        #4
        I thought there would be, too. But it appears that since it only takes a couple seconds to do all that. I'll have to look into placing some sorta retry 'n' times into it. For now all it will do is spit out an error [img]/infopop/emoticons/icon_smile.gif[/img] I've been running it for a week now without any problems. Of course the capture times on the TEMP05 don't vary, either. My quick and dirty solution would be to either offset the script execution time by a few seconds or unplug, then plug in the TEMP05 so that the timer starts at a different time. Or another idea is to close the com port, run the script, then reopen it. Would work in this case, but not for ultralog. I'm not using it (yet), so I don't really know how it works or interacts with the log. Although this may not be the best of solutions, you can do something similar to the temp05 solution and just disable all those scripts, then run this, then enable all those.

        I'm also looking into modularizing (sp?) it so all you do is call a function and give it the sql statement and it does the rest. And want to do that to the compact part, too.

        Also, since if your constantly compacting it on a daily basis, it won't take as long to do it then if you only do it once a week or whenever you remember to.

        ---
        Todd
        My HomeSeer | My Website | HomeSeer WebRing

        Comment

          #5
          VR,

          Just wanted to let you know your script works great. I have a lot of CDJ entries in the log and it's just great to be able to get rid of them in the database. I'm using DooLog and it's unfortunate I can't compact the database but I can do that manually when Homeseer is not running.

          Not sure if there's a way to automate the compact outside Homeseer but as it is now, it works wonderful.

          Thanks for your great work,

          Simon

          Comment

            #6
            Thanks for the comments...

            If you have access on the homeseer computer, there is a command line option you can do that will compact the database. So you can create a batch file that does it. Although you have to do a couple little tweaks, you can get that to work in an ASP page as well.

            Haven't looked at how dooLog works, but I was looking at ultralog and it looks like it just grabs info from the hs log every 5 mins or whatever you set it to. So you could have the compact run inbetween that. But compacting is really only needed when you delete alot of stuff. If all your doing is adding to it (like the log2db scripts out there) then you don't even have to worry about compacting them.

            ---
            Todd
            My HomeSeer | My Website | HomeSeer WebRing

            Comment

              #7
              The connection to the database remains open when Utltralog plugin is running. When it is open then compacting cannot be done. Methods would be needed in the plugin to disconnect and reconnect and I do not know if they exist

              Comment

                #8
                The simple solution is to switch to SQL Server. This can be done using a full blown SQL Server or just with SQL Server Desktop (MSDE).

                You can still use Access (2000 or XP) to do all the DB tinkering from SQL but then you have the power of a full db server on hand and eliminate the need for compacting completely.

                Unfortunately though CDJ doesn't support using SQL instead of Access which would be a simple change in CDJ and a change that I have been begging for Nirvis to make for the last 2 years.

                Comment

                  #9
                  I use MySQL for a few other databases and it's free/open source. And I'm pretty sure someone made a plugin for access so that you can interact with MySQL databases through access. There's still an optimize function, but that change isn't nearly as drastic as it is in Access. I think all the optimize in MySQL is does some misc stuff to any indexes and stuff. Like you said, the transition isn't all that difficult. For the most part all you have to do is change one line so that it connects to ODBC instead of directly to access. That was actually going to be my next step if I couldn't figure out how to autimatically compress access db's

                  ---
                  Todd
                  My HomeSeer | My Website | HomeSeer WebRing

                  Comment

                  Previous template Next
                  Working...
                  X