Announcement

Collapse
No announcement yet.

External Database Create Table SQL Commands

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    External Database Create Table SQL Commands

    Code:
    CREATE TABLE tblLog(
    id int identity,
    Log_Date datetime,
    Log_Type varchar(100),
    Log_Data varchar(2048),
    CONSTRAINT pkID3 PRIMARY KEY (ID)
    )
    
    CREATE INDEX idx01 ON tblLog (Log_Date ASC)
    CREATE INDEX idx02 ON tblLog (Log_Type ASC)
    
    CREATE TABLE tblSyslog(
    id int identity,
    Syslog_Date datetime,
    Syslog_Facility int,
    Syslog_Severity int,
    Syslog_Hostname varchar(50),
    Syslog_Msg_Tag varchar(32),
    Syslog_Msg_Content varchar(2048),
    CONSTRAINT pkID4 PRIMARY KEY (ID)
    )
    
    CREATE INDEX idx03 ON tblSyslog (Syslog_Date ASC)
    CREATE INDEX idx04 ON tblSyslog (Syslog_Facility ASC)
    CREATE INDEX idx05 ON tblSyslog (Syslog_Severity ASC)
    CREATE INDEX idx06 ON tblSyslog (Syslog_Hostname ASC)
    CREATE INDEX idx07 ON tblSyslog (Syslog_Msg_Tag ASC)
    
    CREATE TABLE tblDeviceValueLog(id int identity,
     ts datetime,
     hc char(1) NOT NULL,
     dc varchar(3) NOT NULL,
     data1 int NOT NULL,
     data2 int NOT NULL,
     CONSTRAINT pkID1 PRIMARY KEY (ID)
     )
     
    CREATE INDEX idx08 ON tblDeviceValueLog (ts ASC)
    CREATE INDEX idx09 ON tblDeviceValueLog (hc ASC)
    CREATE INDEX idx10 ON tblDeviceValueLog (dc ASC)
    
    CREATE TABLE tblDeviceStatusLog(id int identity,
     ts datetime,
     hc char(1) NOT NULL,
     dc varchar(3) NOT NULL,
     status int NOT NULL,
     data1 int NOT NULL,
     data2 int NOT NULL,
     CONSTRAINT pkID2 PRIMARY KEY (ID)
     )
     
    CREATE INDEX idx11 ON tblDeviceStatusLog (ts ASC)
    CREATE INDEX idx12 ON tblDeviceStatusLog (hc ASC)
    CREATE INDEX idx13 ON tblDeviceStatusLog (dc ASC)
    Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

    #2
    What Database is this for?

    Comment


      #3
      It looks pretty generic, so it should work for any recent database. It is fails, then some of the id fields may need to be tweaked.
      Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

      Comment


        #4
        Anyone try this with MSSQL yet?
        HomeSeer 2, HomeSeer 3, Allonis myServer, Amazon Alexa Dots, ELK M1G, ISY 994i, HomeKit, BlueIris, and 6 "4k" Cameras using NVR, and integration between all of these systems. Home Automation since 1980.

        Comment


          #5
          Seems to work as long as one creates a database first. The SQL script did not seems to create the database. Maybe it did and I didn't see it.

          Either way, it appears the script ran without error.

          Now will try to connect UltraLog to MSSQL.

          [Update]

          Didn't see anything where one could attempt to connect to a MSSQL database within the plugin. If at all possible, I think that I would prefer MSSQL as I already have experience in this product and have another application which requires that DBMS system.

          Can you respond how to instruct/configure the plugin to connect to MSSQL dbms? I already have run the script and the database is created. I named the database UltraLog is is running on the same machine as HS3 and the plugin.
          Last edited by Krumpy; October 15, 2017, 09:22 PM.
          HomeSeer 2, HomeSeer 3, Allonis myServer, Amazon Alexa Dots, ELK M1G, ISY 994i, HomeKit, BlueIris, and 6 "4k" Cameras using NVR, and integration between all of these systems. Home Automation since 1980.

          Comment


            #6
            Not sure if you have missed this post or ignoring it as you no longer want to deal with the Microsoft SQL DBMS environment.

            A reply with be greatly appreciated. Thanks!
            HomeSeer 2, HomeSeer 3, Allonis myServer, Amazon Alexa Dots, ELK M1G, ISY 994i, HomeKit, BlueIris, and 6 "4k" Cameras using NVR, and integration between all of these systems. Home Automation since 1980.

            Comment


              #7
              Originally posted by Krumpy View Post
              Not sure if you have missed this post or ignoring it as you no longer want to deal with the Microsoft SQL DBMS environment.

              A reply with be greatly appreciated. Thanks!
              Hello Krumpy,

              I dropped support for databases other than sqlite in the HS3 release when I converted from ASPX to HomeSeer jQuery.

              Regards,
              Ultrajones
              Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

              Comment

              Working...
              X