0
Posted on Monday, November 21, 2016 by 醉·醉·鱼 and labeled under , ,
貌似EI Capitan和以前的版本的安装有些差别,记录一下。大体来说,你需要安装下面3个部分。

  • Oracle Instant Client
  • ruby-oci8 gem
  • activerecord-oracle_enhanced-adapter gem

安装Oracle Instant Client

去Oracle官网下载下面几个包,并按照 http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
  1. instantclient-basic-macos.x64-12.1.0.2.0.zip
  2. instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
  3. instantclient-sdk-macos.x64-12.1.0.2.0.zip
解压到/opt/oracle/instantclient_12_1

cd ~

unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
unzip instantclient-sdk-macos.x64-12.1.0.2.0.zip

3. 创建link


cd /opt/oracle/instantclient_12_1
ln -s libclntsh.dylib.12.1 libclntsh.dylib

Note: OCCI programs will additionally need:


ln -s libocci.dylib.12.1 libocci.dylib

4. 配置PATH


export ORACLE_HOME=/opt/oracle/instantclient_12_1
export OCI_DIR=/opt/oracle/instantclient_12_1
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$HOME
export NLS_LANG="AMERICAN_AMERICA.UTF8"

安装Gem

gem install 'ruby-oci8' -v '~> 2.1.0'
gem install 'activerecord-oracle_enhanced-adapter' -v '~> 1.5.0'


测试


ActiveRecord::Base.establish_connection(
    :adapter => "oracle_enhanced",
    :database => "database",
    :username => "username",
    :password => "password")

    cursor = ActiveRecord::Base.connection.execute("SELECT 1 n FROM table")

    # query data
    result_data = []

    result_data << cursor.column_metadata.map { |e| e.name }

    while row = cursor.fetch
        result_data << row
    end

引用
  1. http://stackoverflow.com/questions/36811473/ruby-oci8-installation-error-in-mac-el-capitan
  2. https://craig.io/setting-up-a-rails-development-environment-with-oracle/
0
Posted on Thursday, November 17, 2016 by 醉·醉·鱼 and labeled under


  1. ODBC。Open Database Connectivity,是很老的一个数据库连接API。现在基本上没有用了。
  2. 微软后来开发了OLE DB,算是ODBC的替代品,同时支持更多的数据源,比如spreadsheets
  3. ADO.NET是基于.NET framework来连接关系型和非关系型数据库。看上去像ADO的进化版,实际上完全是全新的东西。
  4. JDBC。同ODBC,不过是给JAVA用的。
  5. JDBC有两款driver,一个是微软自己开发的sqljdbc4,另外一个是jtds。前者不支持NamedPipe。
  6. 可以通过
    jdbc:jtds:sqlserver://./DatabaseName;instance=LOCALDB#88893A09;namedPipe=true 连接namedPipe
  7. jtds是基于FreeTds。
  8. ruby下面tiny_tds也是基于FreeTds的。





0
Posted on Tuesday, October 11, 2016 by 醉·醉·鱼 and labeled under
谨以此文纪念我已经“死”去的各种老师,我对不起你们,我都忘记完了。


首先在Excel中建立A2:B7的数据。A8,B8分别是A和B的平均数。

根据协方差公式,C列为A2-$A$8,D列为B2-$B$8,E为C2*D2。最后求和得到118.29。再除以N-1,得到协方差23.658。也可以通过=COVARIANCE.S(A2:A7, B2:B7)直接得到。

然后开始计算样本标准差。可以通过=STDEV.S(A2:A7)直接获得7.9633,这里就不一步步计算了。如下图G1和H1.


根据相关系数公式,可以得到相关系数为=F1/G1/H1 0.655979. 也可以通过=CORREL(A2:A7, B2:B7)得到。但是这里的相关系数和趋势图中的β 0.3731相差挺大的。原来,图中的β是通过=F1/G1/G1 得到的,而G1为A:A的样本标准差。因此,β = 0.3731

由于y=βx+ε,带入A和B的平均值,得到ε为1.3417。

其实,你也可以直接通过=SLOPE(B2:B7, A2:A7)计算β,=INTERCEPT(B2:B7, A2:A7)计算ε。


那最后就是这个决定系数R²。切记,不要和相关系数r混淆了。这里的R²是用来衡量前面相关系数β的准确性的,取值为0到1。数值越大,表示β越准确。计算公式为根据线性公式算出的期望方差除以样本方差。

将A列的各值带入公式y=βx+ε,得到J列,再减去平均值$B$8得到K列。将D列和K列分别平方求和,在用M8/L8即可得到决定系数0.430308。也可以通过=RSQ(B2:B7, A2:A7)得到。

0
Posted on Monday, September 19, 2016 by 醉·醉·鱼 and labeled under ,
Kalen最近参加了24 hours of PASS,主题是《Locking, Blocking, Versions: Concurrency for Maximum Performance》。但实际上只讲了locking的一些基本概念,还有很多没有讲到。不过作为回顾,也是不错的。






  1. 在SQL SERVER,最基本的两种lock是shared lock(S)和exclusive lock(X)
  2. UPDATE lock是一个混合模式,出现在UPDATE/DELETE的查询过程中,可以和shared lock兼容,但是与其他U和X锁不兼容。
  3. 对数据进行修改的时候,U锁会升级成为X锁
  4. 一般情况下,我们讨论的lock是TRANSACTION lock,但除此之外,还有SHARED_TRANSACTION_WORKSPACE (Resource = DATABASE)、
    EXCLUSIVE_TRANSACTION_WORKSPACE (Resource = DATABASE)、游标锁、Session Locks (Resource = DATABASE)。
  5. 对于lock的粒度,可以是ROW(RID or KEY)、PAGE、TABLE、PARTITION、EXTENT、DATABASE
  6. SQL SERVER会在多层上放置lock。比如,修改一条记录,会在TABLE 和 PAGE上方式IX锁,在ROW上放置X锁
  7. sys.dm_tran_locks可以用来查看当前的所有lock
  8. ROW锁会升级为更高级别的锁。遇到过一个案例就是锁升级为page锁,进而导致deadlock。
0
Posted on Tuesday, September 13, 2016 by 醉·醉·鱼 and labeled under

拜读完 https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/,快快记录一些东西,方便以后回忆。
  1. READ_COMMITTED_SNAPSHOT 和 SNAPSHOT都是基于snapshot的隔离级别
  2. 两种机制都会复制数据一个version到tempdb
  3. 在物理存储上,每条数据都会增加长度为14bytes的pointer和XSN
  4. pointer会指向之前的version,之前的version又会指向更早的version,直到最早的version。有点想HEAP里出现page split一样。
  5. SNAPSHOT机制减少了lock,增加了tempdb开销,间接增加UPDATE和DELETE的代价
  6. READ_COMMITTED_SNAPSHOT 可以避免脏读。是statement level的snapshot isolation。第二次读是可以读到另外TRAN里提交的改动。
  7. SNAPSHOT 可以避免脏读,不可重复读和幻读。是transaction level的snapshot isolation。第二次读到的和第一次读到的一致。
  8. 由于基于version,reader和writer互不block,但是writer还是会block writer。
  9. 正是由于SNAPSHOT可以重复读,会导致UPDATE CONFLICT。即UPDATE的时候其他session已经提交了改动,这个时候就会UPDATE CONFLICT。
  10. 开启READ_COMMITTED_SNAPSHOT需要关闭所有ACTIVE SESSION。
  11. 开启READ_COMMITTED_SNAPSHOT需要将代码里面的NOLOCK抹掉,并默认为READ COMMITTED隔离级别。
0
Posted on Tuesday, September 13, 2016 by 醉·醉·鱼 and labeled under
项目是用SQLCMD加载文件进行schema部署的,如果部署中间出问题了,会是部分提交,还是全部回滚呢?

创建下面的文件

PRINT 'YES'
GO
update test
set someValue = 987
where id = 1
GO
THROW 51000, 'The record does not exist.', 1;  
GO
PRINT 'YES AGAIN'
GO

测试

sqlcmd -S .\MSSQLSERVER2012 -d event_service -i ./sqlcmd_test.sql -m-1 -r -I -b

结果是,部分提交,和你在SSMS里面一样,即使你加了-b option。
0
Posted on Wednesday, August 31, 2016 by 醉·醉·鱼 and labeled under
时间长了,总能够碰见一些奇葩的case。比如有一个字段是ID,但是数据类型是VARCHAR。这里就潜在问题了。当数据比较小的时候都不会有问题,一旦数据比较大的时候,就会出现下面这个错误。

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '10000000000000' overflowed an int column.

可以通过下面一段代码来重现这个问题。


CREATE TABLE #TMP(id varchar(50))

INSERT INTO #TMP (ID) VALUES (10), (10000000000000)

SELECT * FROM #TMP WHERE ID = 10
SELECT * FROM #TMP WHERE ID = 10000000000000

错误就在第一个SELECT的WHERE ID = 10上。默认情况下,SQL SERVER会把varchar转换成为右边的数据类型INT,当试图转换10000000000000就溢出了。所以,你要么显示转换,要么老老实实用正确的数据类型。
0
Posted on Wednesday, August 31, 2016 by 醉·醉·鱼 and labeled under
碰巧遇到一个案例,是需要把一个表的部分数据复制到另外一张表,同时把新生成的ID和原来表的ID都要记录下来。

一种方法是用游标,遍历第一个表,逐条插入。5000条记录还是算快的。但是如果数据量特别大的时候,游标就真心慢了。
另外一种方法就是INSERT INTO...SELECT,再用OUTPUT输出两个ID。 我尝试如下方法:



CREATE TABLE #CUSTOMERS (
ID INT IDENTITY(1,1),
NAME VARCHAR(50),
MEMBERSHIP_ID INT
)

CREATE TABLE #MEMBERSHIPS (
ID INT IDENTITY(1,1),
NAME VARCHAR(50)
)

CREATE TABLE #TMP(
CUSTOMER_ID INT,
MEMBERSHIP_ID INT
)

INSERT INTO #CUSTOMERS(NAME) VALUES ('pHoEnIx'), ('ErIk')

INSERT INTO #MEMBERSHIPS(NAME)
OUTPUT c.ID, INSERTED.ID
INTO #TMP
SELECT c.NAME
FROM #CUSTOMERS c
 
----------------------------------------------
--你会得到下面的错误信息
--Msg 4104, Level 16, State 1, Line 5
--The multi-part identifier "c.ID" could not be bound.
--通过http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx
--你可以通过使用MERGE避免这个问题
----------------------------------------------

MERGE #MEMBERSHIPS
USING (SELECT ID, NAME FROM #CUSTOMERS) AS src
ON (1 = 0) --随意不匹配
WHEN NOT MATCHED THEN
    INSERT (NAME)
    VALUES (src.NAME)
    OUTPUT src.ID, INSERTED.ID
    INTO #TMP;



0
Posted on Tuesday, August 30, 2016 by 醉·醉·鱼 and labeled under ,
SQL SERVER 2016出了一个新功能,就是COMPRESS/DECOMPRESS。运用的是GZIP算法,针对LOB这种类型的数据很有效果。

如下图,创建3个表并插入数据。


直接查看占用的空间,可以看到有无DATA_COMPRESS并没有本质区别。因为DATA_COMPRESSION本来就不支持LOB数据。相反,运用了COMPRESS的压缩率达到了可怕的1.7%。当然实际中压缩率会比这个高。


至于性能上,由于数据压缩了,IO上面的开销很显然会更少,相应地会增加CPU的开销。总体而言,使用了压缩以后读取速度会慢一些(可能是我的CPU太差了)。

0
Posted on Wednesday, July 27, 2016 by 醉·醉·鱼 and labeled under
嘟嘟,粑粑想你了。

没有想到,这一别,你就已经10个月没有回成都了。心里一酸,不知道你还能不能够回成都了。

0
Posted on Monday, June 27, 2016 by 醉·醉·鱼 and labeled under


@Autowired annotation is picked up only if you use  or

http://stackoverflow.com/questions/7414794/difference-between-contextannotation-config-vs-contextcomponent-scan

is used to activate annotations in beans already registered in the application context (no matter if they were defined with XML or by package scanning).
can also do what does but also scans packages to find and register beans within the application context.
I'll use some examples to show the differences/similarities.
Lets start with a basic setup of three beans of type A, B and C, with B and C being injected into A.
package com.xxx;
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc; 
  }
}
With the following XML configuration :
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A">
  <property name="bbb" ref="bBean" />
  <property name="ccc" ref="cBean" />
</bean>
Loading the context produces the following output:
creating bean B: com.xxx.B@c2ff5
creating bean C: com.xxx.C@1e8a1f6
creating bean A: com.yyy.A@1e152c5
setting A.bbb with com.xxx.B@c2ff5
setting A.ccc with com.xxx.C@1e8a1f6
OK, this is the expected output. But this is "old style" Spring. Now we have annotations so lets use those to simplify the XML.
First, lets autowire the bbb and ccc properties on bean A like so:
package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}
This allows me to remove the following rows from the XML:
<property name="bbb" ref="bBean" />
<property name="ccc" ref="cBean" />
My XML is now simplified to this:
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />
When I load the context I get the following output:
creating bean B: com.xxx.B@5e5a50
creating bean C: com.xxx.C@54a328
creating bean A: com.yyy.A@a3d4cf
OK, this is wrong! What happened? Why aren't my properties autowired?
Well, annotations are a nice feature but by themselves they do nothing whatsoever. They just annotate stuff. You need a processing tool to find the annotations and do something with them.
to the rescue. This activates the actions for the annotations that it finds on the beans defined in the same application context where itself is defined.
If I change my XML to this:
<context:annotation-config />
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />
when I load the application context I get the proper result:
creating bean B: com.xxx.B@15663a2
creating bean C: com.xxx.C@cd5f8b
creating bean A: com.yyy.A@157aa53
setting A.bbb with com.xxx.B@15663a2
setting A.ccc with com.xxx.C@cd5f8b
OK, this is nice, but I've removed two rows from the XML and added one. That's not a very big difference. The idea with annotations is that it's supposed to remove the XML.
So let's remove the XML definitions and replace them all with annotations:
package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.xxx.B;
import com.xxx.C;
@Component
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}
While in the XML we only keep this:
<context:annotation-config />
We load the context and the result is... Nothing. No beans are created, no beans are autowired. Nothing!
That's because, as I said in the first paragraph, the only works on beans registered within the application context. Because I removed the XML configuration for the three beans there is no bean created and has no "targets" to work on.
But that won't be a problem for which can scan a package for "targets" to work on. Let's change the content of the XML config into the following entry:
<context:component-scan base-package="com.xxx" />
When I load the context I get the following output:
creating bean B: com.xxx.B@1be0f0a
creating bean C: com.xxx.C@80d1ff
Hmmmm... something is missing. Why?
If you look closelly at the classes, class A has package com.yyy but I've specified in the to use package com.xxx so this completely missed my A class and only picked up B and C which are on the com.xxx package.
To fix this, I add this other package also:
<context:component-scan base-package="com.xxx,com.yyy" />
and now we get the expected result:
creating bean B: com.xxx.B@cd5f8b
creating bean C: com.xxx.C@15ac3c9
creating bean A: com.yyy.A@ec4a87
setting A.bbb with com.xxx.B@cd5f8b
setting A.ccc with com.xxx.C@15ac3c9
And that's it! Now you don't have XML definitions anymore, you have annotations.
As a final example, keeping the annotated classes A, B and C and adding the following to the XML, what will we get after loading the context?
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
We still get the correct result:
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
Even if the bean for class A isn't obtained by scanning, the processing tools are still applied by on all beans registered in the application context, even for A which was manually registered in the XML.
But what if we have the following XML, will we get duplicated beans because we've specified both and ?
<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
No, no duplications, We again get the expected result:
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
That's because both tags register the same processing tools ( can be omitted if is specified) but Spring takes care of running them only once.
Even if you register the processing tools yourself multiple times, Spring will still make sure they do their magic only once; this XML:
<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
<bean id="bla" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla1" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla2" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla3" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
will still generate the following result:
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@25d2b2
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
OK, that about raps it up.

I hope this information along with the responses from @Tomasz Nurkiewicz and @Sean Patrick Floyd are all you need to understand how and work.