上篇講述到 bottle運行的主文件，這麼講述job_action這個操作。
<code>
class JobAction(DBSql):
\tdef __init__(self):
\t\tself.log = logging.getLogger("JobAction")
\t\tlogging.config.dictConfig(yaml.load(open('logging.yaml', 'r')))
\t
\t@staticmethod
\tdef get_project_name_sql(name):
\t\tif name == 'All':
\t\t\treturn " where 1=1 "
\t\telse:
\t\t\treturn " where project_name='%s' " % name
\t
\t@staticmethod
\tdef get_keyword_sql(keyword):
\t\tif keyword and keyword.strip() != '':
\t\t\tkeyword = keyword.upper().strip()
\t\t\treturn " (upper(source_code) like '%%%s%%' or upper(remark) like '%%%s%%' or upper(job_name) like '%%%s%%' or upper(table_name) like '%%%s%%' )" % (
\t\t\t\tkeyword, keyword, keyword, keyword)
\t\telse:
\t\t\treturn " 1=1 "
\t
\tdef get_connection_by_ip(self, oracle_dbs):
\t\t\treturn oracle_dbs['DABAO_T']
\t
\tdef execute_action(self, oracle_dbs, login_name, sub_url, params):
\t\treturn_result = {'success': True}
\t\t
\t\tuse_db_connection = self.get_connection_by_ip(oracle_dbs)
\t\ttry:
\t\t\tif sub_url == 'search_job':
\t\t\t\t
\t\t\t\tkeyword = params['keyword']
\t\t\t\tsort_and_sort_order = params.get('sort', 'job_name') + ' ' + params['sortOrder']
\t\t\t\t
\t\t\t\tif params['projectName'] is None or str(params['projectName']).strip() == '':
\t\t\t\t\treturn_result['errorMsg'] = 'projectName is null'
\t\t\t\telse:
\t\t\t\t\twhere_sql = ' and '.join(
\t\t\t\t\t\t[self.get_project_name_sql(params['projectName']), self.get_keyword_sql(keyword)])
\t\t\t\t\t
\t\t\t\t\tbase_query_sql = "select created_time,email_address,project_name,job_name,source_code,remark,is_use,TABLE_NAME,WB_TYPE,job_location,db_type,enable,comment_message,unit_test,updated_time,rowid as row_id from source_code " + where_sql
\t\t\t\t\tcount_query_sql = "select count(*) as cnt from source_code " + where_sql
\t\t\t\t\t
\t\t\t\t\tif 'is_use' not in sort_and_sort_order:
\t\t\t\t\t\tsort_and_sort_order += ' ,is_use desc '
\t\t\t\t\t
\t\t\t\t\tquery_sql = self.get_page_sort_sql(base_query_sql, params['page'], params.get('rows', 0),
\t\t\t\t\t\t\t\t\t\t\t\t\t sort_and_sort_order)
\t\t\t\t\treturn_result['rows'] = use_db_connection.db_query(query_sql, True)
\t\t\t\t\treturn_result['total'] = use_db_connection.db_query(count_query_sql)[0][0]
\t\t\t\t\t
\t\t\t\t\treturn_result['unit_info'] = use_db_connection.db_query('''
\t\t\t\t\t\tselect
\t\t\t\t\t\t\t\tsum(unit_test) as unit_test_cnt,
\t\t\t\t\t\t\t\tsum(case when table_name like 'G%' and is_use=1 then 1 else 0 end) as unit_cnt,
\t\t\t\t\t\t\t\tsum(is_use) as use_cnt,
\t\t\t\t\t\t\t\tcount(*) as cnt
\t\t\t\t\t\t\t\tfrom source_code
\t\t\t\t\t\t''' + self.get_project_name_sql(params['projectName']))[0]
\t\t\t\t\tif keyword and keyword.strip() != '':
\t\t\t\t\t\tuse_db_connection.db_execute('''insert into source_code_log (login_name,user_action,query_content,project_name)
\t\t\t\t\t\t\t\t\t\t\t\t\tvalues(:1,:2,:3,:4)
\t\t\t\t\t\t\t\t\t\t''', (login_name, sub_url, keyword, params['projectName']))
\t\t\t
\t\t\t
\t\t\t# update not source_code
\t\t\telif sub_url == 'update_job_other':
\t\t\t\t
\t\t\t\tif 'rowid' not in params:
\t\t\t\t\treturn_result['errorMsg'] = 'Data Error,rowid not found'
\t\t\t\telse:
\t\t\t\t\trowid = params['rowid']
\t\t\t\t\tdel params['rowid']
\t\t\t\t\tindex = 1
\t\t\t\t\tupdate_key = []
\t\t\t\t\tupdate_val = []
\t\t\t\t\tfor k, v in params.items():
\t\t\t\t\t\tif k == 'email_address':
\t\t\t\t\t\t\tv = v.replace('\\n', '')
\t\t\t\t\t\tupdate_key.append(k + "=:" + str(index))
\t\t\t\t\t\tupdate_val.append(v)
\t\t\t\t\t\tindex += 1
\t\t\t\t\tupdate_val.append(rowid)
\t\t\t\t\tupdate_sql = "update source_code set " + ','.join(update_key) + " where rowid=:%d" % (index)
\t\t\t\t\tuse_db_connection.db_execute(update_sql, update_val)
\t\t\t
\t\t\telif sub_url == 'update_job':
\t\t\t\tsource_code = params['sourceCode']
\t\t\t\trowid = params['update_rowid']
\t\t\t\tcomment = params['update_comment']
\t\t\t\tpre_update_time = params['update_time']
\t\t\t\tif 'update_project_name' not in params or params['update_project_name'] is None:
\t\t\t\t\treturn_result['errorMsg'] = '項目名稱不能為空'
\t\t\t\telif comment.strip() == '':
\t\t\t\t\treturn_result['errorMsg'] = '評論信息不能為空'
\t\t\t\telse:
\t\t\t\t\tpre_value = use_db_connection.db_query(
\t\t\t\t\t\t"select to_char(updated_time,'yyyy-mm-dd hh24:mi:ss'),updated_person from source_code where rowid=:1",
\t\t\t\t\t\tpara=(rowid,))[0]
\t\t\t\t\tif pre_value[0] != pre_update_time:
\t\t\t\t\t\treturn_result['errorMsg'] = '請先保存自己編寫的代碼到本地，然後重新刷新頁面，因為這部分代碼已經被%s在%s更改過了' % (
\t\t\t\t\t\t\tpre_value[1], pre_value[0])
\t\t\t\t
\t\t\t\tif 'errorMsg' not in return_result:
\t\t\t\t\t
\t\t\t\t\tsource_lines = source_code.split('\\n')
\t\t\t\t\t
\t\t\t\t\tfor i in range(0, len(source_lines)):
\t\t\t\t\t\tif source_lines[i].strip() != '' and '),' == source_lines[i].strip()[-2:]:
\t\t\t\t\t\t\tif i + 1 <= len(source_lines) and source_lines[i + 1].strip()[0:1] == ')':
\t\t\t\t\t\t\t\treturn_result['errorMsg'] = str(i + 1) + ' lines ==' + source_lines[
\t\t\t\t\t\t\t\t\ti] + ' \\n 逗號後面不能直接加 ' + source_lines[i + 1]
\t\t\t\t\t\t\t\tbreak
\t\t\t\t\t\t
\t\t\t\t\t\tif 'when ' in source_lines[i]:
\t\t\t\t\t\t\tif 'then' not in source_lines[i].lower()[source_lines[i].index('when'):]:
\t\t\t\t\t\t\t\treturn_result['errorMsg'] = str(i + 1) + ' lines == ' + source_lines[
\t\t\t\t\t\t\t\t\ti] + ' \\n case when 中 必須有then '
\t\t\t\t\t\t\t\tbreak
\t\t\t\t\tuse_db_connection.db_execute('''update source_code
\t\t\t\t\t\t\t\tset udf1=source_code,udf2=udf1,source_code=:1 , updated_time=sysdate , updated_person=:2,comment_message=:3
\t\t\t\t\t\t\t\twhere rowid=:4
\t\t\t\t\t''', (source_code, login_name, comment, rowid))
\t\t\t\t\t
\t\t\t\t\tuse_db_connection.db_execute('''insert into source_code_log (login_name,user_action,source_code,project_name,job_name,remark)
\t\t\t\t\t\t\t\t\t\t\t\tvalues(:1,:2,:3,:4,:5,:6)
\t\t\t\t\t\t\t\t\t''', (
\t\t\t\t\t\tlogin_name, sub_url, source_code, params['update_project_name'], params['update_job_name'],
\t\t\t\t\t\tcomment))
\t\t\t\t\treturn_result['pre_updated_time'] = use_db_connection.db_query(
\t\t\t\t\t\t"select to_char(updated_time,'yyyy-mm-dd hh24:mi:ss'),updated_person from source_code where rowid=:1",
\t\t\t\t\t\tpara=(rowid,))[0][0]
\t\t\t
\t\t\t
\t\t\t
\t\t\telif sub_url == 'add_one_reord':
\t\t\t\t
\t\t\t\tassert params['add_project_name'] and params[
\t\t\t\t\t\t\t\t\t\t\t\t\t\t 'add_project_name'].strip() != '', 'project_name must not be empty'
\t\t\t\tassert params['add_table_name'] and params[
\t\t\t\t\t\t\t\t\t\t\t\t\t\t'add_table_name'].strip() != '', 'table_name must not be empty'
\t\t\t\tassert params['add_job_name'] and params['add_job_name'].strip() != '', 'job_name must not be empty'
\t\t\t\tassert params['add_job_location'] and params[
\t\t\t\t\t\t\t\t\t\t\t\t\t\t 'add_job_location'].strip() != '', 'job_location must not be empty'
\t\t\t\t
\t\t\t\thas_exist = use_db_connection.db_exist_record(
\t\t\t\t\t"select * from source_code where project_name='%s' and job_name='%s' " % (
\t\t\t\t\t\tparams['add_project_name'], params['add_job_name']))
\t\t\t\t
\t\t\t\tif has_exist:
\t\t\t\t\treturn_result['success'] = False
\t\t\t\t\treturn_result['errorMsg'] = params['add_project_name'] + ':' + params[
\t\t\t\t\t\t'add_job_name'] + " has existis.!!!"
\t\t\t\t
\t\t\t\telse:
\t\t\t\t\tif (str(params['add_table_name']).upper()[0:1] == 'G'):
\t\t\t\t\t\tparams['add_table_name'] = str(params['add_table_name']).upper()
\t\t\t\t\t
\t\t\t\t\tinsert_sql = '''insert into source_code(
\t\t\t\t\t\tproject_name,
\t\t\t\t\t\tjob_name,
\t\t\t\t\t\tremark,
\t\t\t\t\t\tis_use,
\t\t\t\t\t\tenable,
\t\t\t\t\t\twb_type,
\t\t\t\t\t\tdb_type,
\t\t\t\t\t\ttable_name,
\t\t\t\t\t\tjob_location,
\t\t\t\t\t\tcreated_person,
\t\t\t\t\t\tupdated_time )
\t\t\t\t\t\t values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,sysdate)'''
\t\t\t\t\tuse_db_connection.db_execute(insert_sql, (
\t\t\t\t\t\tparams['add_project_name'].strip(), params['add_job_name'], params['add_remark'],
\t\t\t\t\t\tparams['add_is_use'], params['add_enable'], params['add_wb_type'], params['add_db_type'],
\t\t\t\t\t\tparams['add_table_name'], params['add_job_location'], login_name))
\t\t\t
\t\t\telse:
\t\t\t\treturn_result['errorMsg'] = 'not found ' + sub_url
\t\t\t
\t\t\tif 'errorMsg' in return_result:
\t\t\t\treturn_result['success'] = False
\t\t\t
\t\t\treturn json.dumps(return_result, ensure_ascii=False, cls=DateEncoder)
\t\t
\t\texcept Exception as ie:
\t\t\tself.log.error('except:==========', ie)
\t\t\traise ie
\t\t\ttraceback.print_exc()
\t\t\treturn json.dumps({'success': False, 'errorMsg': str(ie) + ' ,,,,'}, ensure_ascii=False)
/<code>
先添加了代碼，然後講下 這個 jobaction 主要做了哪些事情。
第一：肯定要有查詢，根據用戶傳入的URL判斷，如果是 search_job 那麼就是查詢的操作。
獲取輸入的入參，然後拼接成sql 語句，去數據庫查詢，然後返回結果。
要主要的是這裡為了獲取總量。
這裡顯示的總量是3
所以 這裡每次查詢的時候，需要兩句sql語句，一個是總量，而是明細結果，而且支持分頁。
還有，為了記錄用戶的行為，這裡還有一個插入操作。記錄用戶 查詢輸入的參數，用於管理分析。
那麼除了查詢，肯定還要有修改，修改就是判斷URL=update_job，
此時修改就需要傳入 的主要參數 就是 rowid 和 source_code，因為根據rowid查詢數據庫是最快的方法了。直接update即可。這裡還返回了修改時間，這裡的修改時間是為了校驗，萬一存在兩個人在不同的地方 通過這個網站修改同一份代碼，那麼沒有這個修改時間的檢驗，代碼會出現紊亂。
例如
A 讀取了代碼C，修改成D 保存。
B 讀取了代碼C，修改成E 保存。
兩個都修改了，沒有感知。而加入了修改時間後，A修改成D保存後，C這份代碼的修改時間就是D的時間。而B改成E去保存的時候，發現此時的修改時間 和 讀取C 的修改時間不一致，就會保存失敗，並提醒用戶 請先保存最新的代碼到本地，然後刷新下頁面，因為此時的代碼已經被A修改過了。
最後，自然就是新增記錄了，新增記錄的條件就是 URL=add_one_reord，簡單的insert操作，並沒有什麼值得講解的地方。
明天 我看看閱讀的人多不，人多再稍微講解下 前端的代碼，哎，讓我這個大數據開發程序員 講解 後臺、前臺的代碼，不專業啊，大家看的可能是雲裡霧裡了。
分享到:
閱讀更多 豆豆杭哲 的文章
