Chris.*Prior

Through collaboration with Rex Harvey, World Masters Athletics, Alan Jones has created updated replacements for the running events of 1500 m and longer for track races and 5 km and longer for road races. Data from the 2006.05.28 update.

SQL WMA age standards

Thursday, 6. December 2007, 21:09:47

sql

SELECT f.distance, s.open_class_standard, f.type, f.gender, f.age, f.age_factor, SEC_TO_TIME(TIME_TO_SEC(s.open_class_standard)/f.age_factor), f2.age, f2.age_factor, SEC_TO_TIME(TIME_TO_SEC(s.open_class_standard)/f2.age_factor) FROM wma_age_factors f INNER JOIN wma_open_class_standards s ON f.distance=s.distance
INNER JOIN wma_age_factors f2 ON f.distance=f2.distance
WHERE f.age=32 and f2.age=40 AND f.distance LIKE "HM" AND f.gender=1 AND f2.gender=1 AND s.gender=1 and f.type LIKE "road" and f2.type LIKE "road"

No comments

"dbExpress error" with DBDesigner

Saturday, 27. January 2007, 12:59:34

sql

DBDesigner4.0 uses the old password type, but MySQL has since moved to a different format. So i could not connect to a database. Here is a fix: http://www.mysqlusers.com/msg/37060.html

No comments

spatial data in MySQL

Wednesday, 26. July 2006, 19:31:20

sql, mysql

UPDATE training_strecke_marker SET pt=PointFromText(CONCAT('POINT(',`training_strecke_marker`.`lat`,' ',`training_strecke_marker`.`lon`,')'));

(Posted by Ian Scrivener on February 18 2005 4:33pm @ http://dev.mysql.com/doc/refman/5.0/en/populating-spatial-columns.html)

No comments

Adding a sequence to an existing table

Monday, 24. July 2006, 04:40:15

sql, mysql

I had a database with 2 routes (running courses in a latitude/longitude representation) stored with no order other than that the first inserted value was the 1. 'clickpoint'.

I needed a way to generate a sequence for each route_id. Here is the quick solution:

set @foo=0;
UPDATE training_strecke_marker SET strecke_marker_seq = (SELECT @foo := @foo+1 ) WHERE strecke_id =3;
set @foo=0;
UPDATE training_strecke_marker SET strecke_marker_seq = (SELECT @foo := @foo+1 ) WHERE strecke_id =6;

No comments

Runner's journal

Monday, 10. July 2006, 17:23:59

mysql, sql, sport

2006-08-31:
The main view:

create view sport_journal as select sql_no_cache `training_journal_laufen`.`datum` AS `datum`,unix_timestamp(`training_journal_laufen`.`datum`) AS `unixtime`,`koerperwerte_journal`.`hf_ruhe` AS `hf_ruhe`,time_format(sec_to_time(SUM(time_to_sec(`training_journal_laufen`.`dauer`))),_utf8'%k:%i') AS `dauer`,if((`training_journal_laufen`.`strecke_id` is not null),1,0) AS `has_strecke`,SUM(coalesce(`training_strecke`.`laenge`,`training_journal_laufen`.`laenge`)) AS `laenge`,round(avg((coalesce(`training_strecke`.`AA`,`training_journal_laufen`.`AA`) / 100)),2) AS `aa`,coalesce(`training_strecke`.`name`,NULL) AS `strecke_name`,coalesce(`training_strecke`.`beschreibung`,NULL) AS `strecke_beschreibung`,coalesce(`training_strecke`.`hm_kum_rauf`,NULL) AS `strecke_hm_kum_rauf`,coalesce(`training_strecke`.`hm_kum_runter`,NULL) AS `strecke_hm_kum_runter`,`training_journal_laufen`.`type` AS `art`,time_format( sec_to_time( sum(time_to_sec(`training_journal_laufen`.`dauer`)) / SUM(coalesce(`training_strecke`.`laenge`,`training_journal_laufen`.`laenge`)) ),_utf8'%i:%s') AS `min/km`,`training_journal_laufen`.`hr_avg` AS `hf_avg`,`training_journal_laufen`.`hr_max` AS `hf_max`,round((((coalesce(`training_strecke`.`laenge`,`training_journal_laufen`.`laenge`) * ((coalesce(`training_strecke`.`laenge`,`training_journal_laufen`.`laenge`) * 1000) / (time_to_sec(`training_journal_laufen`.`dauer`) / 60))) * 206) / ((206 - `training_journal_laufen`.`hr_avg`) * 220)),0) AS `greif`,round(((time_to_sec(`training_journal_laufen`.`dauer`) / 60) * ((`training_journal_laufen`.`hr_avg` - `koerperwerte_journal`.`hf_ruhe`) / (206 - `koerperwerte_journal`.`hf_ruhe`))),0) AS `banister`,coalesce(`training_wetter`.`icao`,NULL) AS `icao`,coalesce(`training_wetter`.`time`,NULL) AS `time`,coalesce(`training_wetter`.`metar`,NULL) AS `metar`,coalesce(`training_running_schuh`.`name`,NULL) AS `schuh_name`,coalesce(year(`training_running_schuh`.`kauf_datum`),NULL) AS `schuh_jahr` from ((((`training_journal_laufen` left join `koerperwerte_journal` on((cast(`koerperwerte_journal`.`datum` as date) = cast(`training_journal_laufen`.`datum` as date)))) left join `training_strecke` on((`training_journal_laufen`.`strecke_id` = `training_strecke`.`id`))) left join `training_wetter` on((`training_wetter`.`id` = `training_journal_laufen`.`wetter_id`))) left join `training_running_schuh` on((`training_running_schuh`.`id` = `training_journal_laufen`.`schuh_id`))) where 1 group by `training_journal_laufen`.`datum` order by `training_journal_laufen`.`datum` desc;

The most important table definitions:

-- phpMyAdmin SQL Dump
-- version 2.8.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Sep 01, 2006 at 12:43 PM
-- Server version: 5.0.24
-- PHP Version: 5.1.4-pl6-gentoo
-- 
-- Database: `cprior`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `koerperwerte_journal`
-- 

CREATE TABLE `koerperwerte_journal` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `datum` datetime NOT NULL default '0000-00-00 00:00:00',
  `schlaf` time default NULL,
  `gewicht` float default NULL,
  `hf_ruhe` tinyint(4) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `training_journal_laufen`
-- 

CREATE TABLE `training_journal_laufen` (
  `id` int(11) NOT NULL auto_increment,
  `datum` datetime NOT NULL default '0000-00-00 00:00:00',
  `dauer` time NOT NULL default '00:00:00',
  `wetter_id` int(11) default NULL,
  `type` enum('Reg','LDL','mDL','Tempo','LR','WK') default NULL,
  `strecke_id` int(11) default NULL,
  `km` float default NULL,
  `laenge` decimal(5,2) unsigned default NULL,
  `AA` smallint(6) default NULL,
  `hr_max` mediumint(9) default NULL,
  `hr_avg` mediumint(9) default NULL,
  `schuh_id` varchar(6) NOT NULL,
  `diverses` varchar(255) default NULL,
  `notiz` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `training_laufen_schuh`
-- 

CREATE TABLE `training_laufen_schuh` (
  `id` varchar(6) NOT NULL,
  `name` varchar(128) NOT NULL,
  `km-korrektur` int(11) NOT NULL,
  `preis` float NOT NULL,
  `kauf_datum` date NOT NULL,
  `verkaeufer` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `training_running_schuh`
-- 

CREATE TABLE `training_running_schuh` (
  `id` varchar(6) NOT NULL,
  `name` varchar(128) NOT NULL,
  `km-korrektur` int(11) NOT NULL,
  `preis` float NOT NULL,
  `kauf_datum` date NOT NULL,
  `verkaeufer` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `training_strecke`
-- 

CREATE TABLE `training_strecke` (
  `id` int(11) NOT NULL auto_increment,
  `kuerzel` varchar(32) NOT NULL,
  `name` varchar(64) default NULL,
  `beschreibung` varchar(255) NOT NULL,
  `laenge` decimal(4,2) default NULL,
  `hm_kum_rauf` mediumint(9) default NULL,
  `hm_kum_runter` mediumint(9) default NULL,
  `AA` smallint(6) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `kuerzel` (`kuerzel`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `training_wetter`
-- 

CREATE TABLE `training_wetter` (
  `id` int(11) NOT NULL auto_increment,
  `icao` char(4) NOT NULL,
  `time` datetime NOT NULL,
  `metar` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `sport_journal`
-- 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cprior`.`sport_journal` AS select sql_no_cache `cprior`.`training_journal_laufen`.`datum` AS `datum`,unix_timestamp(`cprior`.`training_journal_laufen`.`datum`) AS `unixtime`,`cprior`.`koerperwerte_journal`.`hf_ruhe` AS `hf_ruhe`,time_format(sec_to_time(sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`))),_utf8'%k:%i') AS `dauer`,if((`cprior`.`training_journal_laufen`.`strecke_id` is not null),1,0) AS `has_strecke`,sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) AS `laenge`,round(avg((coalesce(`cprior`.`training_strecke`.`AA`,`cprior`.`training_journal_laufen`.`AA`) / 100)),2) AS `aa`,coalesce(`cprior`.`training_strecke`.`name`,NULL) AS `strecke_name`,coalesce(`cprior`.`training_strecke`.`beschreibung`,NULL) AS `strecke_beschreibung`,coalesce(`cprior`.`training_strecke`.`hm_kum_rauf`,NULL) AS `strecke_hm_kum_rauf`,coalesce(`cprior`.`training_strecke`.`hm_kum_runter`,NULL) AS `strecke_hm_kum_runter`,`cprior`.`training_journal_laufen`.`type` AS `art`,time_format(sec_to_time((sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`)) / sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)))),_utf8'%i:%s') AS `min/km`,`cprior`.`training_journal_laufen`.`hr_avg` AS `hf_avg`,`cprior`.`training_journal_laufen`.`hr_max` AS `hf_max`,round((((coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`) * ((coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`) * 1000) / (time_to_sec(`cprior`.`training_journal_laufen`.`dauer`) / 60))) * 206) / ((206 - `cprior`.`training_journal_laufen`.`hr_avg`) * 220)),0) AS `greif`,round(((time_to_sec(`cprior`.`training_journal_laufen`.`dauer`) / 60) * ((`cprior`.`training_journal_laufen`.`hr_avg` - `cprior`.`koerperwerte_journal`.`hf_ruhe`) / (206 - `cprior`.`koerperwerte_journal`.`hf_ruhe`))),0) AS `banister`,coalesce(`cprior`.`training_wetter`.`icao`,NULL) AS `icao`,coalesce(`cprior`.`training_wetter`.`time`,NULL) AS `time`,coalesce(`cprior`.`training_wetter`.`metar`,NULL) AS `metar`,coalesce(`cprior`.`training_running_schuh`.`name`,NULL) AS `schuh_name`,coalesce(year(`cprior`.`training_running_schuh`.`kauf_datum`),NULL) AS `schuh_jahr` from ((((`cprior`.`training_journal_laufen` left join `cprior`.`koerperwerte_journal` on((cast(`cprior`.`koerperwerte_journal`.`datum` as date) = cast(`cprior`.`training_journal_laufen`.`datum` as date)))) left join `cprior`.`training_strecke` on((`cprior`.`training_journal_laufen`.`strecke_id` = `cprior`.`training_strecke`.`id`))) left join `cprior`.`training_wetter` on((`cprior`.`training_wetter`.`id` = `cprior`.`training_journal_laufen`.`wetter_id`))) left join `cprior`.`training_running_schuh` on((`cprior`.`training_running_schuh`.`id` = `cprior`.`training_journal_laufen`.`schuh_id`))) where 1 group by `cprior`.`training_journal_laufen`.`datum` order by `cprior`.`training_journal_laufen`.`datum` desc;

-- --------------------------------------------------------

-- 
-- Table structure for table `sport_schuhverbrauch`
-- 

CREATE ALGORITHM=UNDEFINED DEFINER=`cpr`@`localhost` SQL SECURITY DEFINER VIEW `cprior`.`sport_schuhverbrauch` AS select `cprior`.`training_laufen_schuh`.`name` AS `Schuh`,count(0) AS `Anz`,(sum(`cprior`.`training_journal_laufen`.`km`) + `cprior`.`training_laufen_schuh`.`km-korrektur`) AS `SUM(km)` from (`cprior`.`training_journal_laufen` join `cprior`.`training_laufen_schuh` on((`cprior`.`training_laufen_schuh`.`id` = `cprior`.`training_journal_laufen`.`schuh_id`))) group by `cprior`.`training_journal_laufen`.`schuh_id`;

-- --------------------------------------------------------

-- 
-- Table structure for table `sport_statistics_monthly_current_year`
-- 

CREATE ALGORITHM=UNDEFINED DEFINER=`cpr`@`localhost` SQL SECURITY DEFINER VIEW `cprior`.`sport_statistics_monthly_current_year` AS select sql_no_cache month(`cprior`.`training_journal_laufen`.`datum`) AS `monat`,count(0) AS `anz`,sec_to_time(sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`))) AS `monat.SUM(dauer)`,round((sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`)) / (select sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`)) AS `sum(time_to_sec(``dauer``))` from `cprior`.`training_journal_laufen`)),2) AS `monat.%(dauer)`,round(sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)),1) AS `monat.SUM(laenge)`,round((sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) / (select sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) AS `sum(``laenge``)` from (`cprior`.`training_journal_laufen` left join `cprior`.`training_strecke` on((`cprior`.`training_journal_laufen`.`strecke_id` = `cprior`.`training_strecke`.`id`))))),2) AS `monat.%(laenge)`,round(((sum(((coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`) * `cprior`.`training_journal_laufen`.`AA`) / 100)) / sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`))) * 100),1) AS `asphalt.%(laenge)`,round((((sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) * ((sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) * 1000) / (sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`)) / 60))) * 200) / ((200 - avg(`cprior`.`training_journal_laufen`.`hr_avg`)) * 220)),0) AS `greif`,max(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) AS `MAX(laenge)`,min(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) AS `MIN(laenge)` from (`cprior`.`training_journal_laufen` left join `cprior`.`training_strecke` on((`cprior`.`training_journal_laufen`.`strecke_id` = `cprior`.`training_strecke`.`id`))) where (year(`cprior`.`training_journal_laufen`.`datum`) = year(now())) group by month(`cprior`.`training_journal_laufen`.`datum`) order by month(`cprior`.`training_journal_laufen`.`datum`) desc;

-- --------------------------------------------------------

-- 
-- Table structure for table `sport_statistics_weekly`
-- 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cprior`.`sport_statistics_weekly` AS select week(cast(`cprior`.`training_journal_laufen`.`datum` as date),0) AS `kw`,min(`cprior`.`training_journal_laufen`.`datum`) AS `MIN(datum)`,max(`cprior`.`training_journal_laufen`.`datum`) AS `MAX(datum)`,unix_timestamp(min(`cprior`.`training_journal_laufen`.`datum`)) AS `MIN(unixtime)`,unix_timestamp(max(`cprior`.`training_journal_laufen`.`datum`)) AS `MAX(unixtime)`,count(distinct `cprior`.`training_journal_laufen`.`datum`) AS `anz`,sec_to_time(sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`))) AS `dauer`,round(sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)),1) AS `SUM(laenge)`,round(avg(coalesce(`cprior`.`training_strecke`.`AA`,`cprior`.`training_journal_laufen`.`AA`)),0) AS `AVG(aa)`,max(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) AS `MAX(laenge)`,round(((max(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)) / sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`))) * 100),0) AS `MAX()/SUM()`,time_format(sec_to_time((sum(time_to_sec(`cprior`.`training_journal_laufen`.`dauer`)) / sum(coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`)))),_utf8'%i:%s') AS `AVG(min/km)`,sum(round((((coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`) * ((coalesce(`cprior`.`training_strecke`.`laenge`,`cprior`.`training_journal_laufen`.`laenge`) * 1000) / (time_to_sec(`cprior`.`training_journal_laufen`.`dauer`) / 60))) * 206) / ((206 - `cprior`.`training_journal_laufen`.`hr_avg`) * 220)),0)) AS `greif`,round(((time_to_sec(`cprior`.`training_journal_laufen`.`dauer`) / 60) * ((`cprior`.`training_journal_laufen`.`hr_avg` - `cprior`.`koerperwerte_journal`.`hf_ruhe`) / (206 - `cprior`.`koerperwerte_journal`.`hf_ruhe`))),0) AS `banister` from ((`cprior`.`training_journal_laufen` left join `cprior`.`koerperwerte_journal` on((cast(`cprior`.`koerperwerte_journal`.`datum` as date) = cast(`cprior`.`training_journal_laufen`.`datum` as date)))) left join `cprior`.`training_strecke` on((`cprior`.`training_journal_laufen`.`strecke_id` = `cprior`.`training_strecke`.`id`))) where (`cprior`.`training_journal_laufen`.`type` <> _utf8'KoV') group by week(`cprior`.`training_journal_laufen`.`datum`,1) order by `cprior`.`training_journal_laufen`.`datum` desc;

-- --------------------------------------------------------

-- 
-- Table structure for table `sport_union_tmp`
-- 

CREATE ALGORITHM=UNDEFINED DEFINER=`cpr`@`localhost` SQL SECURITY DEFINER VIEW `cprior`.`sport_union_tmp` AS (select _utf8'j' AS `t`,`j`.`datum` AS `datum` from `cprior`.`training_journal_laufen` `j`) union all (select _utf8'c' AS `t`,`c`.`datum` AS `datum` from `cprior`.`training_journal_crosstrainer` `c`);

-- --------------------------------------------------------

Original post:
<deleted />

No comments

Functions, stored procedure in >MySQL5

Friday, 23. June 2006, 06:35:02

mysql, sql

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.22-log | 
+------------+
1 row in set (0.00 sec)

mysql> use cprior;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELIMITER @
mysql> CREATE FUNCTION BMI (gewicht FLOAT) 
RETURNS FLOAT 
DETERMINISTIC 
BEGIN 
DECLARE ret FLOAT; 
SET ret = ROUND(gewicht/POWER(1.77,2),1); 
RETURN ret; 
END; @
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT BMI(80);
+---------+
| BMI(80) |
+---------+
|    25.5 | 
+---------+
1 row in set (0.00 sec)

mysql> DROP FUNCTION BMI;
Query OK, 0 rows affected (0.00 sec)

mysql>

Functions cannot access tables.
Stored procedures can.

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf

Note: POWER(1.77,2) That's me

No comments

BMI, avg(weight), trainings-calendar

Thursday, 22. June 2006, 16:37:18

sport, sql, mysql

mysql> select ROUND(AVG(`gewicht`),1) as 'avg kg last 7 days', ROUND((AVG(`gewicht`)/POWER(1.77,2)),1) as 'teh intellectual gemstone called BMI[TM]' FROM koerperwerte WHERE `gewicht` IS NOT NULL AND `datum` >= DATE_SUB(NOW(), INTERVAL '7' DAY );

SELECT WEEK(date(`datum`)) as Week, SEC_TO_TIME(SUM(TIME_TO_SEC(`dauer`))) as duration, SUM(`km`), SEC_TO_TIME(SUM(TIME_TO_SEC(`dauer`))/SUM(`km`)) as 'km/m' FROM training GROUP BY WEEK(`datum`) DESC;

SELECT date_format(`datum`,'%a, %d.%c.') as datum, TIME(`dauer`) as duration, `km`, `type`, SEC_TO_TIME(TIME_TO_SEC(`dauer`)/`km`) as 'm/km', hr_avg, hr_max FROM training GROUP BY training.`datum` DESC;
#
SELECT date_format(`datum`,'%a, %d.%c.') as Datum, TIME(`dauer`) as Dauer, `km`, `type` as Art, SEC_TO_TIME(TIME_TO_SEC(`dauer`)/`km`) as 'm/km', hr_avg, hr_max FROM training WHERE DATE_SUB(curdate(), INTERVAL 14 DAY) <= DATE(`training`.`datum`) GROUP BY training.`datum` DESC;

SELECT WEEK(date_sub(`datum`, interval if(dayofweek(`datum`)-2>=0,dayofweek(`datum`)-2, dayofweek(`datum`)-2+7) DAY)) as Week, COUNT(*) as Anz, SEC_TO_TIME(SUM(TIME_TO_SEC(`dauer`))) as dauer, SUM(`km`) as 'Ges.km', SEC_TO_TIME(SUM(TIME_TO_SEC(`dauer`))/SUM(`km`)) as 'm/km' FROM training GROUP BY WEEK(date_sub(`datum`, interval if(dayofweek(`datum`)-2>=0,dayofweek(`datum`)-2, dayofweek(`datum`)-2+7) DAY)) DESC;

No comments

MySQL add time values

Thursday, 22. June 2006, 06:52:30

sql, mysql

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `duration` ) ) ) AS total_time FROM training;

as in e.g.:
SELECT date(`datum`), TIME(`dauer`) as duration, `km`, SEC_TO_TIME(TIME_TO_SEC(`dauer`)/`km`) as 'km/m' FROM training GROUP BY datum;

16:15 cprior * Kool-Aid: I bought me a heart rate monitor for my excercises today!
16:15 cprior * Haven't owned one in 10 years
16:16 cprior * they became incredibly cheap, I bought one "on impulse"
16:18 cprior * I got one without those fancy "download to PC and cellphone" feature.
16:18 cprior * I will never, ever sink data in a proprietory, binary format
16:19 cprior * so if they don't write "exportable to text-files" all over the package I refuse...
16:19 cprior * If I /really/ wanted to keep track of my excersises I could always put it ina database table

No comments

SELECT eggs FROM Refrigerator

Thursday, 11. May 2006, 07:25:38

sql, fun

More Cooking with SQL - by digitaldc (Score: 5, Funny) Thread

SELECT eggs (*) AS ingredients  
FROM Refrigerator 
GROUP BY NO.dozen 
INSERT INTO my_fryingpan (eggs1, butter1) 
UPDATE my_table SET toast = ‘lightly browned’  
INSERT hole INTO toast 
MERGE eggs INTO toast 
USING ( 
SELECT Eggs, Toast) 
CREATE Breakfast eggy_toast 
DELETE FROM my_table WHERE plate = ‘N’

No comments

An IRC log bot with MySQL and flat-file fallback

Saturday, 4. February 2006, 21:52:28

irc, bot, php, mysql ...

A bit hackish, but working.

Usage: ~$ php path/to/followingFile.php

<?php
/**
 * An IRC logbot based on PEAR::NET_SmartIRC
 *
 * Logs an IRC channel to a MySQL database, triggered by "ActionHandler"s
 * as implemented in SmartIRC.
 *
 * ActionHandler values are defined in Net_SmartIRC/SmartIRC/defines.php
 * Normal conversation can be caught by SMARTIRC_TYPE_CHANNEL,
 * a /me is a SMARTIRC_TYPE_ACTION
 *
 * There is currently no logging for joins/quits/etc.
 *
 * This script has been written on a server with PHP Version 4.4.0-4
 * and a MySQL server version 4.1.15
 *
 * @author Chris Prior <public@prior-i.de>
 *
 */

//include the pear class (doesn't need an include_path for that dir)
include_once('php/Net/SmartIRC.php');

class irclogbot extends Net_SmartIRC
{
  var $hasDbConn=false;
  var $mysql_host='localhost';
  var $mysql_user='cpr';
  var $mysql_password='pass';
  var $mysql_db='irc';
  var $mysql_table='irc_log_test';
  var $log_obj;
  var $stats_obj;
  var $loggables=array('nick','ident','host','type','from','channel','message');
  var $logfileIfMysqlError='errorMysqlINSERT.log';

  //function irclogbot(&$log_obj,&$stats_obj2){
  //}

  /*
   * overwriting existing function
   */
  function _nicknameinuse()
  {   
      $newnickname = $this->_nick.'_';
      $this->changeNick($newnickname, SMARTIRC_CRITICAL);
      $this->_register();
  }

  //unused
  function _register()
  {   
      $this->_send('/msg NickServ IDENTIFY sdcy0', SMARTIRC_CRITICAL);
  }

  /*
   * Basic MySQL auth
   */
  function connect_database()
  {
      $link = @mysql_connect($this->mysql_host, $this->mysql_user, $this->mysql_password)
         or die("Keine Verbindung möglich: " . mysql_error());
      $this->hasDbConn = @mysql_select_db("cpr") or die("db select failed.");
  }

  /*
   * the workhorse logging method
   *
   * Checks for content of $data-> object as returned from PEAR class,
   * constructs a SQL statement and inserts with connection check
   *
   * Outputs some stuff to stdout
   * Don't confuse the 2 stdout sources: parent::setDebug also does it!
   *
   * never change the methodname to 'log', incompatible to the PEAR class!
   *
   */
  function my_log(&$irc='',&$data='')
  {
    $doit=true;
    //quick hack to filter out server msg marked S*_TYPE_CHANNEL
    foreach($this->loggables as $v){
      if (!isset($data->$v)) $doit=false;
      //else echo "$v has " . $data->$v."\n"; //debug
    }
    if($doit){
      //there is an 'ACTION' prefix for the message, I strip it.
      //To recognize a type `action` I still have the value 256 in the col `type`
      if(256==$data->type) {
        $data->message = substr($data->message,8);
        $data->message = substr($data->message,0,strlen($data->message)-1);
        }//end if type 256 == action
      /**
       * The following does not work if there is no db link,
       * so I use mysql_real_escape_string selectively.
       * This leaves the error log unescaped!!!
       *
      */
      //split for original debugging as above, could be merged into one foreach
      foreach($this->loggables as $v) {
        //http://php.net/manual/en/function.stripslashes.php#51390
        if (get_magic_quotes_gpc())
          $data->$v = stripslashes($data->$v);
        //can't use it on the ready query as it would return 
        // *VALUES '\'myNickName\',*
        if(!is_numeric($data->$v) AND $this->hasDbConn ) 
          $data->$v = @mysql_real_escape_string($data->$v);
      }
    }//end if $doit
    //no linebreaks or mysql_real_escape_string breaks the statement!
    $sql = sprintf("INSERT INTO %s "
      . "(`nick`, `ident`, `host`, `type`, `from`, `channel`, `message` ) "
      . " VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s');",
      $this->mysql_table,
      $data->nick,
      $data->ident,
      $data->host,
      $data->type,
      $data->from,
      $data->channel,
      $data->message
    );
    //echos to stdout
    echo $sql;

    /**
     * Now we are fighting hard to preserve the log
     */
    //the mysql INSERT query
    if(!$this->hasDbConn){
      $irc->connect_database();
      if(!$this->hasDbConn){
        $this->logInsertIfMysqlError($sql);
      }
    }
    //new if, not elseif, so a success above triggers
    if($this->hasDbConn == true AND $doit)
    {
      //echo "hasDbConn\n";
      $result = @mysql_query($sql);
      if (!$result) {
        echo('invalid query: ' . mysql_error());
        $this->logInsertIfMysqlError($sql);
      }
    }//end if hasDbConn*
    else {
      $this->logInsertIfMysqlError($sql);
      echo "\n WARNING! Has no DB connection! WARNING!\n";
    }
    echo "\n\n";
  }

  /**
   * Fallback if no MySQL conn
   *
   * I got an 'invalid query: MySQL server has gone away' after long a silence.
   * This is to preserve the INSERT statements for later addition to the db.
   */
  function logInsertIfMysqlError($content){

    echo "Trying to log into error logfile ".$this->logfileIfMysqlError."\n";
    if(!file_exists($this->logfileIfMysqlError)){
      if (touch($this->logfileIfMysqlError) === false){
        echo "cannot touch the non-existing file!"."\n";
        continue;
      }
    }
    if (!is_writable($this->logfileIfMysqlError)) {
       if (!chmod($this->logfileIfMysqlError, 0666)) {
         echo "Cannot change the mode of file ($this->logfileIfMysqlError)"."\n";
         continue;
       };
     }
     if (!$fp = @fopen($this->logfileIfMysqlError, "a")) {
       echo "Cannot open file ($this->logfileIfMysqlError)"."\n";
       continue;
     }
     if (fwrite($fp, $content."\n") === FALSE) {
       echo "Cannot write to file ($this->logfileIfMysqlError)"."\n";
       continue;
     } 
     else echo "ERROR LOGGED! Wrote into ".$this->logfileIfMysqlError."\n";
     if (!fclose($fp)) {
       echo "Cannot close file ($this->logfileIfMysqlError)"."\n";
       continue;
     }
      
  }//end function

  /**
   * Calling this at startup might catch the worst scenario and
   * renders the above logInsertIfMysqlError($str) useless in many respects
   */
  function testForErrorLogfile(){
     if (!$fp = @fopen($this->logfileIfMysqlError, "a")) {
       echo "Cannot open file ($this->logfileIfMysqlError)"."\n";
       exit;
     }
  }//end function

  //doesn't work as expected, but I don't care
  function my_quit(&$this, &$ircdata)
  {
    echo 'FooBar';
    $this->quit("The Bot sfLog has quit.");
  }

}//end class ######################################

$irc = &new irclogbot();
$irc->testForErrorLogfile();
//$irc->connect_database();

//pear/Net_SmartIRC/SmartIRC/defines.php
$irc->setDebug(SMARTIRC_DEBUG_NOTICE|SMARTIRC_DEBUG_CONNECTION|SMARTIRC_TYPE_ACTION|SMARTIRC_TYPE_CHANNEL);
$irc->setUseSockets(TRUE);
$irc->registerActionhandler(SMARTIRC_TYPE_CHANNEL, '^sfLog: !quit', $irc, 'my_quit');

//pear/Net_SmartIRC/SmartIRC/defines.php
//SMARTIRC_TYPE_CHANNEL is defined as `2` and this gets inserted into the db
//never change the methodname to 'log', as it is incompatible to the PEAR class
$irc->registerActionhandler(SMARTIRC_TYPE_CHANNEL, '^.?', $irc, 'my_log');

//type channel does not catch /me et al.
$irc->registerActionhandler(SMARTIRC_TYPE_ACTION, '^.?', $irc, 'my_log');

$irc->connect('irc.freenode.de', 6667);

//Usage: login(nick, realname, usermode, username, password);
$irc->login('nick', 'Chris Prior', 8, 'nick','pass');

$irc->join(array('#test'));
$irc->listen();
$irc->disconnect();
?>

So you need the sql schema? Here it is:

DROP TABLE IF EXISTS `irc_log`;
CREATE TABLE IF NOT EXISTS `irc_log` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `nick` varchar(32) NOT NULL default '',
  `ident` varchar(64) NOT NULL default '',
  `host` varchar(254) NOT NULL default '',
  `type` varchar(4) NOT NULL default '',
  `from` varchar(254) NOT NULL default '',
  `channel` varchar(32) NOT NULL default '',
  `message` text NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `nick_msg` (`nick`,`message`)
)DEFAULT CHARSET=utf8;

2 comments

MySQL table for eBay API

Thursday, 12. January 2006, 18:58:24

ebay, api, sql, mysql

DROP TABLE IF EXISTS `eBay_output`;
CREATE TABLE IF NOT EXISTS `eBay_output` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(255) default NULL,
`subtitle` varchar(255) default NULL,
`CurrencyId` char(2) default NULL,
`Country` char(2) default NULL,
`PostalCode` varchar(8) default NULL,
`Link` varchar(255) default NULL,
`CurrentPrice` float default NULL,
`LocalizedCurrentPrice` varchar(32) default NULL,
`BINPrice` varchar(111) default NULL,
`BidCount` int(11) default NULL,
`FeedbackScore` varchar(111) default NULL,
`FeedbackPercent` varchar(111) default NULL,
`StartTime` datetime default NULL,
`EndTime` datetime default NULL,
`BillPointRegistered` varchar(111) default NULL,
`ItemProperties_BoldTitle` varchar(111) default NULL,
`ItemProperties_Featured` varchar(111) default NULL,
`ItemProperties_Gallery` varchar(111) default NULL,
`ItemProperties_GalleryFeatured` varchar(111) default NULL,
`ItemProperties_GalleryURL` varchar(255) default NULL,
`ItemProperties_Picture` varchar(111) default NULL,
`ItemProperties_Highlight` varchar(111) default NULL,
`ItemProperties_Border` varchar(111) default NULL,
`ItemProperties_New` varchar(111) default NULL,
`ItemProperties_BuyitNow` varchar(111) default NULL,
`ItemProperties_IsFixedPrice` varchar(111) default NULL,
`ItemProperties_Type` varchar(111) default NULL,
`ItemProperties_Gift` varchar(111) default NULL,
`ItemProperties_CharityListing` varchar(111) default NULL,
`ItemProperties_MotorsGermanySearchable` varchar(111) default NULL,
`ShippingDetail_ShippingType` varchar(111) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

No comments

S	M	T	W	T	F	S
October 2009December 2009

1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

My pasteblog

Posts tagged with "sql"

Chris.*Prior

Tags

Chris.*Prior is blogging on My Opera

**Chris.*Prior** is blogging on My Opera