OceanBase使用范例

http://www.mysqlops.com/2011/08/31/oceanbase-use.html

OceanBase的使用类似于关系型数据库，需要预先创建schema,关于schema的格式，请参见schema说明。

假如我们有以下schema:

[app_name]
name=student
max_table_id=1002

[student]
table_id=1001
max_column_id=22
table_type=1
#rowkey=class_id(8bytes) + student id(8bytes)
rowkey_is_fixed_length=1
column_info=1,2,gmt_created,create_time
column_info=1,3,gmt_modified,modify_time
column_info=1,16,student_name,varchar,128
column_info=1,17,student_sex,int
column_info=1,18,student_age,int
column_info=1,19,student_addr,varchar,128
column_info=1,20,cplusplus,int
column_info=1,21,oceanbase,int
column_info=1,22,english,int

join=rowkey[7,15]%score:cplusplus$cplusplus,oceanbase$oceanbase,english$english

rowkey_max_length=16
rowkey_split=8

[score]
table_id=1002
table_type=1
max_column_id=18
#rowkey=student id(8bytes)
rowkey_is_fixed_length=1
column_info=1,2,gm_create,create_time
column_info=1,3,gm_modified,modify_time
column_info=1,16,cplusplus,int
column_info=1,17,oceanbase,int
column_info=1,18,english,int
rowkey_max_length=8
rowkey_split=0

这里一共有两张表，student和score,student表冗余了score表的一些字段，有join关系。

OceanBase目前只支持java client，源代码在svn中可以下载。客户端需要知道集群rootserver的地址和端口。

0. 关于Rowkey

在OceanBase中数据是按行存储的，每行由rowkey唯一标识，rowkey是binary stream形式，OceanBase不会对其进行解释。 rowkey可以由多段组成，应用对其进行解释，比如上两张表的rowkey为：

//student的rowkey由二个部份组成，第一部份为班级id,第三部份为学生id。
//该rowkey为定长16字节
//在查询的时候可以只给出班级id,表示查找该班级下的所有学生。
class StudentRowkey extends Rowkey {
	public StudentRowkey(long classid,long student_id) {
	    byteBuffer.putLong(classid);
	    byteBuffer.putLong(student_id);
	}

    final ByteBuffer byteBuffer = ByteBuffer.allocate(16);

    public byte[] getBytes() {
    	return byteBuffer.array();
    }
};

//score的rowkey为student id，定长8字节
class ScoreRowkey extends Rowkey {
	public ScoreRowkey(byte item_type,long item_id) {
	    byteBuffer.put(item_type);
	    byteBuffer.putLong(item_id);
	}

    final ByteBuffer byteBuffer = ByteBuffer.allocate(8);

    public byte[] getBytes() {
    	return byteBuffer.array();
    }
}

1. 初始化：

	private ClientImpl client;
	client = new ClientImpl();
	client.setIp(ip); //the ip of the rootserver
	client.setPort(port); //the port of the rootserver
	client.setTimeout(2000); //超时时间，单位毫秒
	client.init();

2. 写入

在目前的版本中，OB的update和insert语义是相同的。更新的时候需要提供表名、rowkey、以及各列的值。

	final private static String STUDENT_TABLE_NAME="student";
	final private static String STUDENT_NAME="student_name";
	final private static String STUDENT_AGE="student_age";
	final private static String STUDENT_SEX="student_sex";
	final private static String STUDENT_ADDR="student_addr";
	final private static String SCORE_CPLUSPLUS="cplusplus";
	final private static String SCORE_OCEANBASE="oceanbase";
	final private static String SCORE_ENGLISH="english";

	/**
	 * for OB semantics , update & insert is identical
	 * DB semantics is not support yet.
	 */
	void UpdateDemo() {
		List<ObMutatorBase> mutatorList = new ArrayList<ObMutatorBase>();
                for (Long i = 0L; i < 100; i++) {
                      UpdateMutator mutator = new UpdateMutator(STUDENT_TABLE_NAME, new StudentRowkey(0, i));
                      mutator.addColumn(USER_NICK, new  Value() {{ setString("YOUR_VALUE"); }},false);
                      mutator.addColumn(STUDENT_SEX, new Value() {{setNumber(1L);}},false);
                      mutator.addColumn(STUDENT_AGE, new Value() {{setNumber(10L);}},false);
                      mutator.addColumn(STUDENT_ADDR, new Value() {{setString("ADDR");}},false);
                      mutatorList.add(mutator);
		      //分数只能在分数表中更新
                }

                Result<Boolean> ret = client.update(mutatorList);
                if (ret == null || !ret.getResult()){
        	     System.err.println("update failed");
                }
	}
        void InsertDemo() {
            for (Long i = 0L; i < 100; i++) {
                InsertMutator mutator = new InsertMutator(STUDENT_TABLE_NAME, new StudentRowkey(i, (byte)0, i));
                mutator.addColumn(USER_NICK, new  Value() {{ setString("YOUR_VALUE"); }},false);
                mutator.addColumn(STUDENT_SEX, new Value() {{setNumber(1L);}},false);
                mutator.addColumn(STUDENT_AGE, new Value() {{setNumber(10L);}},false);
                mutator.addColumn(STUDENT_ADDR, new Value() {{setString("ADDR");}},false);
		//类似update,insert也可以做批量插入
                Result<Boolean> ret = client.insert(mutator);
                if (ret == null || !ret.getResult()){
            	    System.err.println("update failed " + ret.getCode());
                }
            }
	}

3. 查询

查询分为get和scan,get是指定rowkey进行查询，而scan是范围查询。

	void queryDemo() {
		QueryInfo query_info = new QueryInfo();
		query_info.setStartKey(new StudentRowkey(0L, 0L));
		query_info.setEndKey(new StudentRowkey(0L,100L));
		query_info.addColumn(STUDENT_NAME);
		query_info.addColumn(STUDENT_SEX);
		Result<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);
		System.out.println("get " + result.getResult().size() + "items");
	}
	void getDemo() {
		Set<String> columns = new HashSet<String>();
		columns.add(STUDENT_NAME);
		columns.add(STUDENT_SEX);
		Result<RowData> ret = client.get(STUDENT_TABLE_NAME, new StudentRowkey(
				0L,1L), columns);
		if (ret == null) {
			System.err.println("get failed ");
		} else {
			System.err.println("get " + ret.getResult().get(STUDENT_NAME));
		}
	}

4. orderby & where

	void queryDemoWhere() {
		QueryInfo query_info = new QueryInfo();
		query_info.setStartKey(new StudentRowkey(0L,0L));
		query_info.setEndKey(new StudentRowkey(0L,100L));
		query_info.addColumn(STUDENT_NAME);
		query_info.addColumn(STUDENT_SEX);

		ObSimpleFilter filter = new ObSimpleFilter();
		filter.addCondition(new ObSimpleCond(STUDENT_SEX,ObSimpleCond.ObLogicOperator.EQ,new Value() {{setNumber(0L);}}));
		query_info.setFilter(filter);
		Result<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);
		System.out.println("get" + result.getResult().size() + "items");
	}

	void queryDemoOrderby() {
		QueryInfo query_info = new QueryInfo();
		query_info.setStartKey(new StudentRowkey(0L, 0L));
		query_info.setEndKey(new StudentRowkey(0L, 100L));
		query_info.addColumn(STUDENT_NAME);
		query_info.addColumn(STUDENT_SEX);

		query_info.addOrderBy(STUDENT_SEX, true); //order: true -ASC false - DESC
		Result<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);
		System.out.println("get" + result.getResult().size() + "items");
	}