HoneyHoney

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

安装Oracle Instant Client

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

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

ln -s libocci.dylib.12.1 libocci.dylib

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. 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的。

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。

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

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

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;

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; 
  }
}

<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>

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

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;
  }
}

<property name="bbb" ref="bBean" />
<property name="ccc" ref="cBean" />

<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />

creating bean B: com.xxx.B@5e5a50
creating bean C: com.xxx.C@54a328
creating bean A: com.yyy.A@a3d4cf

<context:annotation-config />
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />

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

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;
  }
}

<context:annotation-config />

<context:component-scan base-package="com.xxx" />

creating bean B: com.xxx.B@1be0f0a
creating bean C: com.xxx.C@80d1ff

<context:component-scan base-package="com.xxx,com.yyy" />

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

<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />

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

<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />

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

<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" />

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