My pasteblog

This is not my Zettelkasten but a durable .bash_history

Subscribe to RSS feed

Posts tagged with "mysql"

ranking with MySQL

Friday, September 25, 2009 9:41:50 AM

http://code.openark.org/blog/mysql/sql-ranking-without-self-join-revisited

No comments

MySQL replication

Thursday, December 25, 2008 5:03:07 PM

master: server-id 1, remote login for replicatiomn user is possible
slave: change master to
master_host
master_user
master_password
master_log_file NOT from master/etc/my.cnf, BUT from master show master status, i.e. "bin.000004" or similar
master_log_pos

slave start

tail -f /var/log/mysqld.log (d!)

No comments

lighttpd (cygwin), PHP5.2.0, Windows 2000, symfony

Wednesday, February 7, 2007 7:01:18 PM

, , ,

I can only tolerate W2K with cygwin installed, and its package manager got me lighttpd.
This needed configuration:
cp /e^T/li^T/lighttpd.conf.default /etc/lighttpd/lighttpd.conf
vi ESC .
Only static HTML at first:
server.document-root = "/cygdrive/e/www/localhost/"
echo "test" > /cygdrive/e/www/localhost/index.php
echo "test" > /cygdrive/e/www/localhost/index.htm
/usr/sbin/lighttpd.exe -D -f /etc/lighttpd/lighttpd.conf #I could CTRL-C terminate it
#/usr/sbin/lighttpd.exe -f /etc/lighttpd/lighttpd.conf #goes to background mode, ps ux needed to get the PID
Big mistake: index.php takes precedence over index.htm and I got a 403 error. Removing the index.php helped.

Get PHP from http://www.php.net/downloads.php
Put it to C:\PHP and include the cgi version!
Version PHP5.2.0 did not work with yaz, pspell, nt$something and a 4th extension that I have already forgotten, so I excluded them.
The peardev.bat has some ^ characters in it, removed them by hand.
Double-clicking (*yuck*) to initialize PEAR, then I did the usual channel-discover etc. as per symfony docs. Then symfony installed without any problems.

Back to lighttpd: Un-commented cgi module, cgi.assign = (".php"=>"C:\PHP\php-cgi.exe")
Got a "no input file specified" error, added in php.ini a docroot E:\www\localhost as per http://www.php.net/manual/de/security.cgi-bin.php 1st comment.
A phpinfo(); in index.php worked then.
Changed BOTH php.ini and lighttpd.conf to point to a symfony-style $mypath/web/ directory, un-commented mod_rewrite, mod_redirect, mod_alias in lighttpd.conf and added
alias.url = ( "/sf/" => "/cygdrive/c/PHP/PEAR/data/symfony/web/sf/" )
url.rewrite-once = (
"^/(.*\..+(?!html))$" => "$0",
"^/(.*)\.(.*)" => "$0",
"^/([^.]+)$" => "/index.php/$1",
"^/$" => "/index.php"
)
I didn't bother to setup a vhost, as this installation is just for testing anyways. I got that above rewrite stuff from the symfony wiki IIRC (taken from another host).
Now c:\php\symfony init-project test && C:\PHP\symfony init-app frontend brought me the familiar symfony welcome screen.

I downloaded mysql-essential-5.0.27-win32.msi and installed ot as a service (for now). Configuration was done with the bundled "MySQL Server Instance Config Wizard". In cmd.com mysql -u root -pmyPassword and create databse symfony.

In the symfont directory I changed both config/database.yml AND propel.ini including username:password in both of them. I could then copy the schema.yml from the First Project tutorial and run propel-*-* to create the database tables.


Music to my ears!

I downloaded http://xdebug.org/link.php?url=xdebug200rc3-521-win although it said PHP5.2.1+ and put it in my C:\PHP\ext and added zend_extension_ts="C:/PHP/ext/php_xdebug-2.0.0rc3-5.2.1.dll" to my php.ini -- that's all to install xdebug. In cmd.com a C:\PHP\php-cgi.exe brought no error message, if the xdebug dll is too old it says something along the lines "Xdebug requires Zend Engine API version 220051025.
The Zend Engine API version 220060519 which is installed, is newer.
Contact Derick Rethans at http://xdebug.org for a later version of Xdebug."

I could not get xcache to work with PHP5.2.0, but on another machine with PHP-5.1.6

No comments

spatial data in MySQL

Wednesday, July 26, 2006 7:31:20 PM

, 

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, July 24, 2006 4:40:15 AM

,

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, July 10, 2006 5:23:59 PM

, ,

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 />
[/CODE]

No comments

Functions, stored procedure in >MySQL5

Friday, June 23, 2006 6:35:02 AM

, 

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, June 22, 2006 4:37:18 PM

, ,

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, June 22, 2006 6:52:30 AM

,

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

Nested sets in MySQL

Sunday, April 2, 2006 1:34:29 PM

Just found that article which holds most of the queries imaginable:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

No comments

Mass-renaming MySQL tables with simple bash scripting

Sunday, April 2, 2006 10:49:53 AM

,

I found out more about stripping from bash variables at IBM's developerworks, written by Daniel Robbins.

~ $ mysql espressopunkt --exec "SHOW TABLES;" > tables.txt #CAVEAT: Rremove additional line at top. THere's probably an automated way for that but this post already tales longer than the task, so why bother...
~ $ for t in `cat tables.txt`;do echo $t;_t=${t#kaffeeweb_};echo $_t;mysql espressopunkt_dev --exec "ALTER TABLE $t RENAME $_t";done

No comments

MySQL: Updating with subqueries in v4.1+

Tuesday, March 7, 2006 10:32:23 PM

The subquery may only return one result, that's why there is the WHERE-contraint mac.id = mat.machine_id. I tested the subquery without that additional WHERE clause to verify all results at once.

UPDATE kaffeeweb_machine mac SET ebay_current_qty = (
SELECT COUNT(*)
FROM kaffeeweb_eBay_matches_machine mat
JOIN kaffeeweb_eBay_GetSearchResultsResponse sr ON mat.output_id = sr.ItemId
WHERE sr.listingdetails_endtime > NOW() AND
mac.id = mat.machine_id
GROUP BY mat.machine_id
)


Even multiple JOINs work:

UPDATE kaffeeweb_manufacturer man SET ebay_current_qty = (
 SELECT COUNT(*)
 FROM kaffeeweb_machine mac
 JOIN kaffeeweb_eBay_matches_machine mat ON mac.id = mat.machine_id
 JOIN kaffeeweb_eBay_GetSearchResultsResponse sr ON mat.output_id = sr.ItemId
 WHERE sr.listingdetails_endtime > NOW()
 AND mac.manufacturer_id = man.id
 GROUP BY mac.manufacturer_id
)

No comments

Sorting in MySQL similar to PHP's natsort()

Tuesday, February 28, 2006 6:14:06 PM

mysql> select DISTINCT(Country) as c from kaffeeweb_eBay_output ORDER BY c desc;
+------+
| c    |
+------+
| 77   |
| 71   |
| 45   |
| 3    |
| 2    |
| 19   |
| 16   |
| 15   |
| 14   |
| 10   |
| 1    |
+------+
11 rows in set (0.01 sec)


But:

mysql> select DISTINCT(Country) as c from kaffeeweb_eBay_output ORDER BY [URL=http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html]CAST[/URL](Country as signed integer) desc;
+------+
| c    |
+------+
| 77   |
| 71   |
| 45   |
| 19   |
| 16   |
| 15   |
| 14   |
| 10   |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.02 sec)

1 comment

MySQL backups

Sunday, February 5, 2006 8:25:15 AM

,

mysqldump --opt -u user -p database [table] | gzip -c > db[_table].sql.tar.gz

gunzip -c db[_table].sql.tar.gz | mysql -u user -p database

No comments

An IRC log bot with MySQL and flat-file fallback

Saturday, February 4, 2006 9:52:28 PM

, , , ...

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, January 12, 2006 6:58:24 PM

, , ,

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

February 2012
S M T W T F S
January 2012March 2012
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