侃一下关于程序的“柔性”

zhangweicai74 · 发表于 2011-9-6 08:22

原帖由〇〇于 2011-9-6 06:34 发表

中间那个S是拼SQL。。。
干脆再办个比赛好了，双方商量数据库表结构和数据生成规律...

这个必须支持。

昨夜袜子 · 发表于 2011-9-6 10:03

原帖由〇〇于 2011-9-6 06:34 发表

中间那个S是拼SQL。。。
干脆再办个比赛好了，双方商量数据库表结构和数据生成规律...

支持。文无第一武无第二。比划比划就知道了。

3833020 · 发表于 2011-9-6 12:46

原帖由 昨夜袜子 于 2011-9-6 10:03 发表

支持。文无第一武无第二。比划比划就知道了。

这不成看热闹了么。

newkid · 发表于 2011-9-6 21:53

原帖由〇〇于 2011-9-6 06:34 发表

中间那个S是拼SQL。。。
干脆再办个比赛好了，双方商量数据库表结构和数据生成规律...

记得我从两年前就向他叫板了。不过我要求的是实现业务需求，而不是来做一个什么通用报表工具、装载工具什么的，PLSQL处理文件和字符串没有优势，它的优势在于和SQL的无缝集成。

yulihua49 · 发表于 2011-9-9 17:16

原帖由 newkid 于 2011-8-25 01:24 发表

“将来表改了”假如是新增了列，你这个做法就会自动SELECT新列；假如减少列，则自动去除。
由此说明你这个应用根本就是透明的传输而已，它不是数据的消费者。新增的列要体现到业务中的，减少的列也要把原来的使用这个列的代码去掉。
再次重复我前面说过的话：这种透明的包装、传输只对你自己有用，PLSQL根本用不着。

这个功能根本用不着PL/SQL。自己解决了。
你的那个程序，我已经测试了。单例的。没做压力。节后再说。
跟我的程序比了一下。在客户端，从发送命令到接收结果的时间。单位是微秒。

你的存储过程的执行时间：
TIMEVAL=58606
TIMEVAL=23585
TIMEVAL=20988
TIMEVAL=11657
TIMEVAL=24134
TIMEVAL=10919

我的：
TIMEVAL=30372
TIMEVAL=16913
TIMEVAL=9121
TIMEVAL=9655
TIMEVAL=8766
TIMEVAL=16947

[ 本帖最后由 yulihua49 于 2011-9-9 17:24 编辑 ]

yulihua49 · 发表于 2011-9-9 17:28

只把app层贴出来：不用DAU，看看怎么样。

//app层
static int getpage(CTX *ctx,JSON_OBJECT cmd,JSON_OBJECT result)
{
int ret,cursor;
INT64 now;
struct msg_stu app;
JSON_OBJECT val;
char msg[4096],bindval[1024],*p;
now=now_sec();
*msg=0;
data_init(&app, msg_stu_tpl);
//对传入的JSON对象进行拆包
val=json_object_object_get(cmd,"values");
ret=json_to_struct(&app,cmd,msg_stu_tpl);
if(!val||!ret){
sprintf(msg,"getpage:transfer json error!");
json_object_object_add(result,"error",jerr(-1,msg));
return -1;
}
//你的val不是JSON串，是他的一部分，比较麻烦
p=bindval;
struct lh_entry *entry ;
for(entry = json_object_get_object(val)->head;entry;entry = entry->next ) {
*p++ = '\"';
p=stpcpy(p,entry->k); //KEY
p=stpcpy(p,"\":\"");
p=stpcpy(p,(char *)json_object_get_string((struct json_object *)entry->v)); //Value
p=stpcpy(p,"\",");
}
*(--p)=0;
sprintf(msg,"get_data.read(:cursor,'%s','%s',%d,%d,'%s')",
app.tablename,app.where,app.page_size,
app.page_idx,bindval);
p=NULL;
ShowLog(5,"%s:RPC stmt=%s",__FUNCTION__,msg);
ret=ORA_Rpc(ctx->SQL_Connect,msg,&p);
ShowLog(5,"%s:RPC ret=%d,p=%s",__FUNCTION__,ret,p);
if(ret<0||!p) {
ShowLog(1,"%s:RPC fault ret=%d,err=%d,%s",__FUNCTION__,
ret,
ctx->SQL_Connect->Errno,
ctx->SQL_Connect->ErrMsg);
json_object_object_add(result,"error",
jerr(ctx->SQL_Connect->Errno,ctx->SQL_Connect->ErrMsg));
return -1;
}
/* 如果是裸数据
int i,nc;
char *cp,*cp1;
CONST char **n = sqlo_ocol_names(cursor, &nc);//取列名
*/
cursor=atoi(p);
free(p);
val=json_object_new_array();
while(NULL!=(p=___SQL_Fetch(ctx->SQL_Connect,cursor,NULL))){
JSON_OBJECT json;
//结果集打成JSON包
json=json_tokener_parse(p);
if(!json) {
ShowLog(1,"%s:%s:not a JSON",__FUNCTION__,p);
free(p);
continue;
}
/* 如果是裸数据
JSON_NEW(json);
cp=strtok_r(p,"|",&cp1);
for(i=0;i<nc;i++) {
add_string_to_json(json,n[i],cp);
cp=strtok_r(NULL,"|",&cp1);
}
*/
free(p);
json_object_array_add(val,json);
}
json_object_object_add(result,app.tablename,val);
___SQL_Close__(ctx->SQL_Connect,cursor);
return 0;
}

复制代码

[ 本帖最后由 yulihua49 于 2011-9-9 17:54 编辑 ]

yulihua49 · 发表于 2011-9-9 17:32

[quote]原帖由 yulihua49 于 2011-9-9 17:28 发表
调用的过程：

5 FIRST35:127.0.0.1:140035030738704 09/09 16:54'02 getpage:RPC stmt=get_data.read(:cursor,'TI_CITY','WHERE DAIL_AREA_CODE=:DAIL_AREA_CODE AND CITY_STOP_DATE >= :CITY_STOP_DATE',10,1,'"DAIL_AREA_CODE":"0350","CITY_STOP_DATE":"20110909"')
5 FIRST35:127.0.0.1:140035030738704 09/09 16:54'02 ORA_Rpc:
BEGIN
get_data.read(:cu1,'TI_CITY','WHERE DAIL_AREA_CODE=:DAIL_AREA_CODE AND CITY_STOP_DATE >= :CITY_STOP_DATE',10,1,'"DAIL_AREA_CODE":"0350","CITY_STOP_DATE":"20110909"');
END;
5 FIRST35:127.0.0.1:140035030738704 09/09 16:54'02 getpage:RPC ret=0,p=1|

复制代码

[ 本帖最后由 yulihua49 于 2011-9-9 17:33 编辑 ]

yulihua49 · 发表于 2011-9-9 17:36

我的结果：

cmd={ "tablename": "TI_CITY", "where": "WHERE DAIL_AREA_CODE=:DAIL_AREA_CODE AND CITY_STOP_DATE >= :CITY_STOP_DATE", "page_size": "10", "page_idx": "1", "values": { "DAIL_AREA_CODE": "0350", "CITY_STOP_DATE": "20110909" } }
data={ "TI_CITY": [ { "CITY_CODE": "0403", "CITY_NAME": "忻州", "CITY_SHORTCODE": "XZH", "CITY_CLASS": "3", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0416", "CITY_NAME": "宁武", "CITY_SHORTCODE": "NWU", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0417", "CITY_NAME": "五寨", "CITY_SHORTCODE": "WZH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0418", "CITY_NAME": "岢岚", "CITY_SHORTCODE": "KLA", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0423", "CITY_NAME": "原平", "CITY_SHORTCODE": "YPI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0424", "CITY_NAME": "定襄", "CITY_SHORTCODE": "DXI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0425", "CITY_NAME": "五台", "CITY_SHORTCODE": "WTA", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0426", "CITY_NAME": "代县", "CITY_SHORTCODE": "DXI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0427", "CITY_NAME": "繁峙", "CITY_SHORTCODE": "FSH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" }, { "CITY_CODE": "0428", "CITY_NAME": "神池", "CITY_SHORTCODE": "SCH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350", "CITY_START_DATE": "19491001", "CITY_STOP_DATE": "21000101", "DISTRICT_CODE": "2", "PROVINCE_CODE": "02" } ] }

复制代码

yulihua49 · 发表于 2011-9-9 17:37

你的结果：

cmd={ "tablename": "TI_CITY", "where": "WHERE DAIL_AREA_CODE=:DAIL_AREA_CODE AND CITY_STOP_DATE >= :CITY_STOP_DATE", "page_size": "10", "page_idx": "1",
"values": { "DAIL_AREA_CODE": "0350", "CITY_STOP_DATE": "20110909" } }
data={ "TI_CITY": [ { "CITY_CODE": "0403", "CITY_NAME": "忻州", "CITY_SHORTCODE": "XZH", "CITY_CLASS": "3", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0416", "CITY_NAME": "宁武", "CITY_SHORTCODE": "NWU", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0417", "CITY_NAME": "五寨", "CITY_SHORTCODE": "WZH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0418", "CITY_NAME": "岢岚", "CITY_SHORTCODE": "KLA", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0423", "CITY_NAME": "原平", "CITY_SHORTCODE": "YPI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0424", "CITY_NAME": "定襄", "CITY_SHORTCODE": "DXI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0425", "CITY_NAME": "五台", "CITY_SHORTCODE": "WTA", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0426", "CITY_NAME": "代县", "CITY_SHORTCODE": "DXI", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0427", "CITY_NAME": "繁峙", "CITY_SHORTCODE": "FSH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" },
{ "CITY_CODE": "0428", "CITY_NAME": "神池", "CITY_SHORTCODE": "SCH", "CITY_CLASS": "4", "DAIL_AREA_CODE": "0350",
"CITY_START_DATE": "19491001000000", "CITY_STOP_DATE": "21000101000000",
"DISTRICT_CODE": "2", "PROVINCE_CODE": "02" } ] }

复制代码

这里边有些区别需要说一下：这是另外一个表，说明了程序的柔性。

日期：我的是YYYYMMDD，你是YYYYMMDDHH24MISS
我们原来要求列名小写，现在将就你的程序，大写。（我改配置就可以）。

[ 本帖最后由 yulihua49 于 2011-9-10 10:42 编辑 ]

〇〇 · 发表于 2011-9-9 17:41

只有输出结果，没有时间对比。。。

[讨论] 侃一下关于程序的“柔性”

回复 #129 yulihua49 的帖子

回复 #130 yulihua49 的帖子

浏览过的版块