Menu
▾
▴
Re: [misterhouse-users] Using MySQL
|
From: Jocelyn B. <joc...@vi...> - 2004-12-28 23:10:11
|
Thanks Gaetan.
Jocelyn
----- Original Message -----
From: "Gaetan Lord" <mis...@ga...>
To: <mis...@li...>
Sent: Monday, December 27, 2004 12:45 AM
Subject: Re: [misterhouse-users] Using MySQL
> Sound like my SQL module got some interest. Sorry, this is a long email
>
> Few advices before using it.
>
> This was only tested on Linux, and may not work on windows.
> You need to have Mysql installed, with basic configuration (admin
> user/password. access etc)
>
> This module, was written for my personal use, because the database
> provided by misterhouse
> was not flexible enough for my personal need. The module is not very
> complex, it's only a way to call
> MySQL from misterhouse, in the misterhouse way.
>
> This module need DBI.pm package to work. This is not included by default
> with misterhouse. On Linux, it could be installed
> directly from CPAN, and I believe it should be also available from
> ActiveState for the Windows users.
>
> I never publish it for many reason
>
> - because it require a good knowledge of perl and SQL syntax.
> Misterhouse hide some perl
> fonctionality, which is good because someone without programming
> knowledge could
> program event with misterhouse. This module require perl knowledge,
> not that much,
> but a little bit more. You need to know how to do SQL query.
>
> - I don't really like to write documentation, so I would appreciate if
> someone could help me with the
> documentation before we ask Bruce to include the module in the
> distribution. The more we put in the doc,
> the less the number of email on the list. English is my second
> language, so I do a lot of mistake, that's why
> I also request help on this side.
>
> I personaly use this module to store in a single table with all my
> iButtons temperature sensors (5). This allow me to query the database in
> many ways, with only a single query. I do store the following temperature.
> - Basement
> - Lounge
> - Ceiling
> - outside
> - Furnace
>
> I store all the alarm event coming from my DSC alarm system
> I store all the incoming phone
> I could query all the phone coming from a specific number, and when
> they call.
> I also have my phonebook
> Soon I will have the outside phone call. I should have spare time to
> look at David Satterfield module for the CK602 telephone data logger.
>
> I include in attachment the SQL.pm file.
>
> The fisrt thing you have to do, before using the module, is to create
> the database. My database is simply named "mh". In this database,
> I have many tables, which contains my misterhouse data. This step need
> to be done before using the module. This would be a very complex task
> to do inside a misterhouse module, so it is left to the user prior to
> use misterhouse. There is some MySQL frontend to help people creating
> database and tables. If, so far, you don't undertsand what we are
> talking about, then this module is probably not for you, sorry.
>
> When the database and tables are defined, you have to configure the
> mh.ini/mh.private.ini file
> dbi_database = mh
> dbi_user = root
> dbi_password = .......
> dbi_location = localhost
>
> With this, when starting MySQl you should have message similar to the
> following
> 12/25/2004 11:40:05 PM Starting SQL module
> 12/25/2004 11:40:05 PM Table: AlarmEvent Records: 189
> 12/25/2004 11:40:05 PM Table: Phonebook Records: 220
> 12/25/2004 11:40:05 PM Table: Phonelog_5551212 Records: 599
> 12/25/2004 11:40:05 PM Table: iButtonsArchive Records: 46822
> 12/25/2004 11:40:05 PM Table: iButtonsSerial Records: 5
>
> Now, you could use the module to do insert, query etc.
>
> I try to explain "insert" and "query" call
>
> First to use the package, you need to add the following in one of your
> file misterhouse files.
> use SQL;
> # SQL startup
> $SQL = new SQL;
> I do have a file named Startup.pl, who contains those lines.
>
> == Inserting data in a table
> My alarm table named AlarmEvent have 2 fields, timestamp and message,
> here's the piece of code I use to
> insert a new entry in the table, at least on a linux box
>
> # insert in SQL database
> undef %TempData;
> my $TimeNow=`date "+%Y-%m-%d %H:%M:%S"`;
> chomp $TimeNow;
> $TempData{'timestamp'}=$TimeNow;
> $TempData{'message'} = "EnglishMsg";
> $SQL->insert("AlarmEvent",\%TempData) if $EnglishMsg eq"NOMESSAGE";
>
> We make sure , the hash Tempdata doesn't exists
> We define TimeNow has the timestamp
> We put 2 values in TempData hash, which are my 2 fields in the table
> Then we insert the values in the database if $EnglishMsg eq "NOMESSAGE"
> The 2 fields in the AlarmEvent table are named "timestamp" and "message"
>
>
> == doing a query in the database
> My iButton table have 6 fields. The timestamp, and 5 iButton values
> I produce, every 5 minutes, a new graphic of my house temperature
> (inside). The graphic contains data for the
> last 7 days (ie $DayTotal)
>
> # get SQL data
> $DayTotal=7;
> my $DateQuery="TO_DAYS(NOW()) - TO_DAYS(Time) <=$DayTotal;";
> my @TempInfo = $SQL->select( "iButtonsArchive",
> "Time,Cuisine,Plafond,SousSol", "$DateQuery" );
> print_log "Fetching $#TempInfo temperature data from
> iButtonsArchive" if $DebugGraph;
>
> if ( $TempInfo[0] ) {
> print DATA "# Time Cuisine Plafond SousSol\n";
> foreach (@TempInfo) {
> my ( $Date, $Cuisine, $Plafond, $SousSol ) = @$_;
> $Date =~ s/ /_/g; # for gnuplot
> $Cuisine = -999 if $Cuisine == 0;
> $Plafond = -999 if $Plafond == 0;
> $SousSol = -999 if $SousSol == 0;
> print DATA "$Date $Cuisine $Plafond $SousSol\n";
> }
> }
>
> To make this simple,
> The $SQL->select require 3 parameters.
> The first one is the table name
> The second one is the table fields we want to extract
> The 3rd one, is the SQL query
>
> this is the SQL query done
> select Time,Cuisine,Plafond,SousSol from iButtonsArchive where
> TO_DAYS(NOW()) - TO_DAYS(Time) <=7
> It get all the events for the field Time,Cuisine,Plafond,SousSol in
> table iButtonsArchive that occurs in the last 7 days.
>
> The array @TempInfo is actually a reference to an array. To extract the
> data, you have to use the
> foreach (@TempInfo) {
> my ( $Date, $Cuisine, $Plafond, $SousSol ) = @$_;
> .....
> }
>
> This will go through all the rows returned by MySQL, and extract the
> date, kitchen temp, ceiling temp and basement temp.
>
>
> There is other call, like "last" which retrieve the last entry in the
> database and also "update" which do change
> a record. I don't want to include them in this email for now. But if
> someone need it ,and would like to document it,
> I'll be happy to help on those.
>
> This could be probably written in a simpler way, but again, this was
> written for my personal use. Hopefully it could others.
>
> Thank You
>
> PS: not sure if attachment would work. If not, I'll put them on my website
>
>
> Phantom Automation wrote:
> > Anyone know a 'best place to start' for using MySQL with MH?
> >
> > I want to start storing historical data in a database and a SQL database
> > would be my first pick.
> >
> > Thanks
> >
> > Peter
>
>
|