13:11:41.633586 IP 192.168.1.156.56747 > 192.168.1.32.1701: l2tp:[TLS](0/0)Ns=0,Nr=0 *MSGTYPE(SCCRQ) *PROTO_VER(1.0) *HOST_NAME(anonymous) *FRAMING_CAP(AS) *ASSND_TUN_ID(31946) *RECV_WIN_SIZE(1) 13:11:41.633688 IP 192.168.1.32 > 192.168.1.156: ICMP 192.168.1.32 udp port 1701 unreachable, length 105 13:11:41.634490 IP 192.168.1.156.56747 > 192.168.1.32.1701: l2tp:[TLS](0/0)Ns=1,Nr=0 *MSGTYPE(StopCCN) *ASSND_TUN_ID(31946) *RESULT_CODE(6) 13:11:41.634542 IP 192.168.1.32 > 192.168.1.156: ICMP 192.168.1.32 udp port 1701 unreachable, length 72
13:11:41.633586 IP 192.168.1.156.56747 > 192.168.1.32.1701: l2tp:[TLS](0/0)Ns=0,Nr=0 *MSGTYPE(SCCRQ) *PROTO_VER(1.0) *HOST_NAME(anonymous) *FRAMING_CAP(AS) *ASSND_TUN_ID(31946) *RECV_WIN_SIZE(1) 13:11:41.633688 IP 192.168.1.32 > 192.168.1.156: ICMP 192.168.1.32 udp port 1701 unreachable, length 105 13:11:41.634490 IP 192.168.1.156.56747 > 192.168.1.32.1701: l2tp:[TLS](0/0)Ns=1,Nr=0 *MSGTYPE(StopCCN) *ASSND_TUN_ID(31946) *RESULT_CODE(6) 13:11:41.634542 IP 192.168.1.32 > 192.168.1.156: ICMP 192.168.1.32 udp port 1701 unreachable, length 72
opkg update ...(略 opkg list ip opkg install ip
opkg list iptables-mod-nat-extra opkg install iptables-mod-nat-extra
opkg list djbdns-tools
opkg install diffutils
config interface 'lan'
option ifname 'eth1'
# option type 'bridge'
option proto 'static'
option ipaddr '192.168.178.1'
option netmask '255.255.255.0'
option ip6assign '60'
config setup
# strictcrlpolicy=yes
# uniqueids = no
conn %default
ikelifetime=60m
keylife=20m
rekeymargin=3m
keyingtries=1
keyexchange=ikev1
authby=secret
conn openwrt2
also=rw
right=192.168.1.116
auto=add
# /etc/ipsec.secrets - strongSwan IPsec secrets file 192.168.1.202 192.168.1.116 : PSK 0sFpZAZqEN6Ti9sqt4ZP5EWcqx
# ipsec.conf - strongSwan IPsec configuration file
# basic configuration
config setup
# strictcrlpolicy=yes
# uniqueids = no
conn %default
ikelifetime=60m
keylife=20m
rekeymargin=3m
keyingtries=1
keyexchange=ikev1
authby=secret
conn home
left=192.168.1.116
leftfirewall=yes
right=192.168.1.202
rightsubnet=192.168.254.0/24
auto=add
# /etc/ipsec.secrets - strongSwan IPsec secrets file 192.168.1.116 : PSK 0sFpZAZqEN6Ti9sqt4ZP5EWcqx
ipsec restart
ipsec up home
oot@OpenWrt:/etc# ipsec status
no files found matching '/etc/strongswan.d/*.conf'
Security Associations (1 up, 0 connecting):
home[1]: ESTABLISHED 11 minutes ago, 192.168.1.116[192.168.1.116]...192.168.1.202[192.168.1.202]
home{1}: INSTALLED, TUNNEL, reqid 1, ESP SPIs: c326ca0a_i c3dc83fc_o
home{1}: 192.168.1.116/32 === 192.168.254.0/24
root@OpenWrt:/etc# ping 192.168.254.2
PING 192.168.254.2 (192.168.254.2): 56 data bytes
^C
--- 192.168.254.2 ping statistics ---
15 packets transmitted, 0 packets received, 100% packet loss
CONFIG_FEATURE_TRACEROUTE_USE_ICMP was set to n っぽい。
busybox は Trace route は UDP ガチガチでICMPと結果は違うものになる。
C:\Program Files\Oracle\VirtualBox>VBoxManage.exe convertfromraw c:\vm\openwrt-15.05.1-x86-generic-combined-ext4.img c:\vm\ow15051.vdi --format VDI
Converting from raw image file="c:\vm\openwrt-15.05.1-x86-generic-combined-ext4.img" to file="c:\vm\ow15051.vdi"... Creating dynamic image with size 55050240 bytes (53MB)...
|_______|| __|_____|__|__||________||__| |____|
|__| W I R E L E S S F R E E D O M
-----------------------------------------------------
CHAOS CALMER (15.05.1, r48532)
-----------------------------------------------------
* 1 1/2 oz Gin Shake with a glassful
* 1/4 oz Triple Sec of broken ice and pour
* 3/4 oz Lime Juice unstrained into a goblet.
* 1 1/2 oz Orange Juice
* 1 tsp. Grenadine Syrup
-----------------------------------------------------
root@OpenWrt:/# ls
bin init mnt rom sys var
dev lib overlay root tmp www
etc lost+found proc sbin usr
root@OpenWrt:/# ping www.yahoo.co.jp
ping: bad address 'www.yahoo.co.jp'
root@OpenWrt:/# passwd
Changing password for root
New password:
Bad password: too weak
Retype password:
Password for root changed by root
root@OpenWrt:/#
root@OpenWrt:/etc/config# cat network
config interface 'loopback'
option ifname 'lo'
option proto 'static'
option ipaddr '127.0.0.1'
option netmask '255.0.0.0'
config interface 'wan'
option ifname 'eth0'
option proto 'dhcp'
config interface 'lan'
option ifname 'eth1'
# option type 'bridge'
option proto 'static'
option ipaddr '192.168.254.1'
option netmask '255.255.255.0'
option ip6assign '60'
config interface 'wan6'
option ifname 'eth1'
option proto 'dhcpv6'
config globals 'globals'
option ula_prefix 'fd2a:82cf:1d52::/48'
root@OpenWrt:/etc/config# ping yahoo.co.jp PING yahoo.co.jp (■.□.△.○): 56 data bytes 64 bytes from ■.□.△.○: seq=0 ttl=52 time=86.698 ms 64 bytes from ■.□.△.○: seq=1 ttl=52 time=42.318 ms 64 bytes from ■.□.△.○: seq=2 ttl=52 time=87.604 ms 64 bytes from ■.□.△.○: seq=3 ttl=52 time=55.463 ms
192.168.254.1 からの応答: バイト数 =32 時間 =1ms TTL=64
opkg update ...(略
opkg list | grep strongswan
opkg install strongswan ...（略 /etc/rc.common: line 143: ipsec: not found
root@OpenWrt:/# opkg remove strongswan
Removing package strongswan from root...
/etc/rc.common: line 143: ipsec: not found
You can force removal of packages with failed prerm scripts with the option:
--force-remove
No packages removed.
Collected errors:
* pkg_run_script: package "strongswan" prerm script returned status 127.
* opkg_remove_pkg: not removing package "strongswan", prerm script failed
opkg install strongswan-default
root@OpenWrt:~# which ipsec /usr/sbin/ipsec root@OpenWrt:~#
cat /cygdrive/z/db_finance_bak.sql|mysql -u root -p db_finance2 Enter password:実行してRASPBERRY PI から DUMPした
$ /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf 2016-04-09 11:20:11: (/usr/src/ports/lighttpd/lighttpd-1.4.37-1.i686/src/lighttpd-1.4.37/src/ configfile.c.1355) the selected event-handler in unknown or not supported: linux-sysepoll 2016-04-09 11:20:11: (/usr/src/ports/lighttpd/lighttpd-1.4.37-1.i686/src/lighttpd-1.4.37/src/ server.c.649) setting default values failed
$ /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf 2016-04-09 11:23:46: (/usr/src/ports/lighttpd/lighttpd-1.4.37-1.i686/src/ lighttpd-1.4.37/src/network.c.959) server.network-backend has a unknown value: sendfile
$ /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf2016-04-09 11:25:20: (/usr/src/ports/lighttpd/lighttpd-1.4.37-1.i686/src/lighttpd-1.4.37/src/server.c.927) can't have more connections than fds/2: 1024 256
$ /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf2016-04-09 11:26:20: (/usr/src/ports/lighttpd/lighttpd-1.4.37-1.i686/src/lighttpd-1.4.37/src/network.c.410) can't bind to port: localhost 80 Address already in use
mysql> describe tbl_dg_devide; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | s | char(4) | NO | PRI | | | | d | char(8) | NO | PRI | | | | f | float | YES | | NULL | | | t | float | YES | | NULL | | | mul | float | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
mysql> select * from tbl_dg_devide where s="9984"; +------+----------+------+------+---------+ | s | d | f | t | mul | +------+----------+------+------+---------+ | 9984 | 19960326 | 1 | 1.4 | 0.71429 | | 9984 | 19960925 | 1 | 1.4 | 0.71429 | | 9984 | 19970326 | 1 | 1.3 | 0.76923 | | 9984 | 20000425 | 1 | 3 | 0.33333 | | 9984 | 20051228 | 1 | 3 | 0.33333 | +------+----------+------+------+---------+ 5 rows in set (0.01 sec)ソフトバンクの1996年3月2X日の終値を表示
mysql> select d, s,c from tbl_dg_ohlc where s="9984" and d LIKE "1996032%"; +----------+------+-------+ | d | s | c | +----------+------+-------+ | 19960321 | 9984 | 26800 | | 19960322 | 9984 | 26000 | | 19960325 | 9984 | 26300 | | 19960326 | 9984 | 19200 | | 19960327 | 9984 | 19800 | | 19960328 | 9984 | 20200 | | 19960329 | 9984 | 21800 | +----------+------+-------+ 7 rows in set (0.01 sec)ソフトバンクの1996年3月2X日の終値を表示↑の二つの表から修正値の数式を出力させてみる。
mysql> select a.s,tbl_dg_ohlc.d,GROUP_CONCAT(a.mul separator '*') from (select s,d,mul from tbl_dg_devide where s="9984" ) as a, tbl_dg_ohlc where a.s = tbl_dg_ohlc.s and a.d > tbl_dg_ohlc.d and tbl_dg_ohlc.d LIKE "1997032%" group by tbl_dg_ohlc.s,tbl_dg_ohlc.d ; +------+----------+-----------------------------------+ | s | d | GROUP_CONCAT(a.mul separator '*') | +------+----------+-----------------------------------+ | 9984 | 19970321 | 0.33333*0.76923*0.33333 | | 9984 | 19970324 | 0.33333*0.33333*0.76923 | | 9984 | 19970325 | 0.33333*0.33333*0.76923 | | 9984 | 19970326 | 0.33333*0.33333 | | 9984 | 19970327 | 0.33333*0.33333 | | 9984 | 19970328 | 0.33333*0.33333 | +------+----------+-----------------------------------+ 6 rows in set (0.44 sec)どうだろう。なぜか、CONCATの順番が入れ替わったりしているが概ね機能してそうな気がする。
select b.s,b.d, IFNULL(GROUP_CONCAT(a.mul separator '*'),1)
from
(select s,d,mul from tbl_dg_devide where s="9984" ) as a
right join
(select * from tbl_dg_ohlc where s="9984") as b
on a.s = b.s and a.d > b.d group by b.s,b.d ;
select
a.s, a.d, a.m, b.o, b.h, b.l, b.c, b.v
from
(select b.s as s ,b.d as d,max(b.m) as m
from (select s,d,max(v) as v
from tmp2 where s="1332" group by s,d ) as a,
tmp2 as b
where a.s = b.s and a.d=b.d and a.v = b.v
group by s, d
) as a,
tmp2 as b
where a.s = b.s and a.d=b.d and a.m=b.m;
create table tmp like tbl_dg_ohlc;
alter table tmp PARTITION BY RANGE COLUMNS(s) (
PARTITION p1500 VALUES LESS THAN('1500'),
PARTITION p2000 VALUES LESS THAN('2000'),
PARTITION p2500 VALUES LESS THAN('2500'),
PARTITION p3000 VALUES LESS THAN('3000'),
PARTITION p3500 VALUES LESS THAN('3500'),
PARTITION p4000 VALUES LESS THAN('4000'),
PARTITION p4500 VALUES LESS THAN('4500'),
PARTITION p5000 VALUES LESS THAN('5000'),
PARTITION p5500 VALUES LESS THAN('5500'),
PARTITION p6000 VALUES LESS THAN('6000'),
PARTITION p6500 VALUES LESS THAN('6500'),
PARTITION p7000 VALUES LESS THAN('7000'),
PARTITION p7500 VALUES LESS THAN('7500'),
PARTITION p8000 VALUES LESS THAN('8000'),
PARTITION p8500 VALUES LESS THAN('8500'),
PARTITION p9000 VALUES LESS THAN('9000'),
PARTITION p9500 VALUES LESS THAN('9500'),
PARTITION p9999 VALUES LESS THAN('9999'),
PARTITION pMAX VALUES LESS THAN(MAXVALUE)
);
insert into tmp select * from tbl_dg_ohlc;
select * from tmp where s="9984"; ... | 20160331 | 9984 | t1 | 5454 | 5466 | 5362 | 5366 | 6804900 | +----------+------+----+--------+--------+--------+--------+-----------+ 5332 rows in set (50.46 sec)もとは8分以上かかっていたが、50秒に短縮され 8倍以上は速くなった。 けどやっぱり十分遅いっちゃあ遅いな、RPIなんで仕方ない部分はあるけどパーティションギチギチに切って 日付とセキュリティIDを入れ替えたり、セキュリティIDやマーケットIDをもっと小さなサイズの数値にしたりという事は可能かもしれない。 でもあんまり複雑にすると面倒なのでどうしたもんか
alter table tmp modify d char(8) after m;
alter table tmp drop primary key; alter table tmp add primary key(s,d,m); select * from tmp where s="9984" 略） | 9984 | t1 | 20160331 | 5454 | 5466 | 5362 | 5366 | 6804900 | +------+----+----------+--------+--------+--------+--------+-----------+ 5332 rows in set (0.39 sec)
select * from tbl_dg_ohlc where s="9984";
| 20160331 | 9984 | t1 | 5454 | 5466 | 5362 | 5366 | 6804900 | +----------+------+----+--------+--------+--------+--------+-----------+ 5332 rows in set (8 min 16.92 sec) mysql>
./1990 ./1990/199001.zip ./1990/199002.zip ./1990/199003.zip ./1990/199004.zip ./1990/199005.zip ./1990/199006.zip ./1990/199007.zip ./1990/199008.zip ./1990/199009.zip ./1990/199010.zip ./1990/199011.zip ./1990/199012.zip ./1991 ./1991/199101.zip ./1991/199102.zip ./1991/199103.zip…
cd 株価データ/ for i in `ls -1 */*.zip`; do funzip $i >> ~/all.txt; done cd ~
load data local infile 'all.txt' replace into table tbl_dg_ohlc fields terminated by ',' lines terminated by '\r\n';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mv all.txt tbl_dg_ohlc mysqlimport -u root -L --lines-terminated-by="\r\n" --fields-terminated-by="," db_finance tbl_dg_ohlc -pload data local infile でダメだったからダメいかもと思いつつ。 寝てみる。起きてきて終わってたら超嬉しい。 もやすみ
create table tbl_dg_ohlc( d CHAR(8), s CHAR(4), m CHAR(2), o float, h float, l float, c float, v float, primary key(d,s,m) );
load data local infile '201601.txt' replace into table tbl_dg_ohlc fields terminated by ',' lines terminated by "\r\n";
select * from tbl_dg_ohlc where s="9984";な感じでソフトバンクだけの時系列データをとれるようになった ワイ♪ あと解凍するときはunzipをインストールしたら入ってくる funzip が便利。 標準出力に出るのでコマンドラインにまとめやすい。
#!/usr/bin/perl
use strict;
use warnings;
my @lines = <>;
my $adj=1;
my $adj2=1;
my $spltday="";
for my $line (@lines){
if ($line=~/\d\d\d\d\/\d\d?\/\d\d?,\D*(\d+\.?\d*)[^0-9\.,]+(\d+\.?\d*)\D*/){
$adj = $adj2;
$adj2 = $adj * ((0+$1)/(0+$2));
$line =~/^(\d+\/\d+\/\d+)\D/;
$spltday = $1;
next;
}
my @cols = split(/,/,$line);
if ($cols[0] ne $spltday){
$adj = $adj2;
}
map{ $_ *= $adj;}@cols[1..4];
if ($adj > 0){
$cols[5]/= $adj;
}
print "".join(",",@cols[0..6]);
#print $line;
}
#アドじゃスタ
sed -e "s/\/\([0-9]\)/\/0\1/g" -e "s/\/0\([0-9][0-9]\)/\/\1/g"
#!/bin/bash url="http://info.finance.yahoo.co.jp/history/?code=$1&sy=$2&sm=$3&sd=$4&ey=$5&em=$6&ed=$7&tm=d&p=" i=1 while wget -O- $url$i |sed -n -e '/^<\/tr><tr[^>]*><td[^>]*>....年/p' |sed -e "s/[,日]//g" -e "s/[ 年月]/\//g" -e "s/<tr[^>]*><td[^>]*>/\n/g" -e "s/<\/\?[^>]\+>/,/g" -e "s/,\+/,/g" -e "s/,\+\s/\n/g" -e "s/,\+$/\n/g" | sed -e "/^$/d" |grep "," do i=`expr $i + 1` done
C60M1@C60M1-PC ~/tmpdir $ ./yget 1570.t 1983 1 1 2016 3 18 >1570t.csv
[....] Starting Postfix Mail Transport Agent: postfixpostfix: fatal: bad string length 0 < 1: sendmail_path = Failed
apt-get remove postfix cd /etc/postfix/ rm -r * apt-get install postfix
# vi /etc/postfix/sasl_auth -- [smtp.gmail.com]:587 example@gmail.com:password
# mail hoge@hoge Subject: hoge Hoge .hoge@hoge 部分はケータイアドレスにでもかえとく
ケースで学ぶマーケティングの教科書 死ぬほどつまらん。事例に一切共感できない。 脳内劇場を無理矢理みさされているような。わずかに内容っぽい部分は既知であり。エグザンプルはすべてステマかパブ記事の類のようであった。
何かも利用しないといけない。