model.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612
  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3. """
  4. demeter database
  5. name:__base__.py
  6. author:rabin
  7. """
  8. import os
  9. import uuid
  10. import short_url
  11. import json
  12. import traceback
  13. import uuid
  14. import re
  15. import math
  16. from demeter.core import *
  17. class Model(object):
  18. __table__ = ''
  19. __comment__ = ''
  20. def __init__(self, type, db, config):
  21. self.db = db
  22. self._type = type
  23. self._config = config
  24. self._table = self._config['prefix'] + '_' + self.__table__
  25. self._set = ''
  26. self._bind = {}
  27. self._attr = {}
  28. self._key = {}
  29. self.create()
  30. def cur(self):
  31. return self.db.cursor()
  32. def query(self, sql, method='select', fetch='fetchall'):
  33. cur = self.cur()
  34. bind = []
  35. if self._set:
  36. for key in self._set:
  37. if self._set[key] == 'time':
  38. self._set[key] = self.time()
  39. elif self._set[key] == 'True':
  40. self._set[key] = True
  41. elif self._set[key] == 'False':
  42. self._set[key] = False
  43. elif 'date' in key:
  44. self._set[key] = self.mktime(self._set[key])
  45. bind.append(self._set[key])
  46. for value in self._key:
  47. if value[0] in self._bind and self._bind[value[0]] != None:
  48. val = self._bind[value[0]]
  49. self._attr[value[0]].unset()
  50. if type(val) == list and val:
  51. for i in val:
  52. bind.append(i)
  53. else:
  54. bind.append(val)
  55. if method == 'select' and ';' in sql:
  56. temp = sql.split(';')
  57. sql = temp[1]
  58. totalSql = temp[0]
  59. cur.execute(totalSql, bind)
  60. Demeter.config['page']['totalNum'] = self.fetch(cur, 'fetchone', 'count')
  61. Demeter.config['page']['total'] = math.ceil(round(float(Demeter.config['page']['totalNum'])/float(Demeter.config['page']['num']),2))
  62. cur.execute(sql, bind)
  63. if method == 'select':
  64. return self.fetch(cur, fetch)
  65. id = True
  66. if method == 'insert':
  67. id = cur.fetchone()[0]
  68. self.db.commit()
  69. self._set = {}
  70. return id
  71. """
  72. try:
  73. except Exception, e:
  74. print e.message
  75. os._exit(0)
  76. """
  77. def fetch(self, cur, type, method = ''):
  78. load = getattr(cur, type)
  79. rows = load()
  80. if type == 'fetchall':
  81. result = []
  82. if rows:
  83. for key in rows:
  84. row = {}
  85. i = 0
  86. for v in key:
  87. row[self._key[i][0]] = v
  88. i = i + 1
  89. result.append(row)
  90. elif method == 'count':
  91. return rows[0]
  92. else:
  93. result = {}
  94. i = 0
  95. if rows:
  96. for key in rows:
  97. if not key:
  98. key = ''
  99. result[self._key[i][0]] = key
  100. i = i + 1
  101. return result
  102. def attr(self, method):
  103. fields = vars(self.__class__)
  104. self._attr = {}
  105. self._bind = {}
  106. self._key = {}
  107. col = (int, str, long, float, unicode, bool, uuid.UUID)
  108. for field in fields:
  109. if isinstance(fields[field], Fields):
  110. self._attr[field] = fields[field]
  111. self._key[field] = self._attr[field].getKey()
  112. insert = (method == 'insert')
  113. if insert and self._attr[field].uuid:
  114. self.setUuid(field, col)
  115. bind = False
  116. val = self._attr[field].getArgv()
  117. if val:
  118. bind = True
  119. else:
  120. val = getattr(self, field)
  121. if isinstance(val, col):
  122. setattr(self, field, self._attr[field])
  123. bind = True
  124. elif insert and self._attr[field].default:
  125. val = self._attr[field].default
  126. bind = True
  127. if val == 'time':
  128. val = self.time()
  129. elif '.' in val:
  130. temp = val.split('.')
  131. val = Demeter.config[temp[0]][temp[1]]
  132. elif method == 'select' and self._attr[field].default and field == 'state':
  133. val = self._attr[field].default
  134. bind = True
  135. if bind and val:
  136. if type(val) == list:
  137. length = len(val)
  138. if length <= 1:
  139. val = val[0]
  140. if insert and self._attr[field].md5:
  141. val = self.createMd5(val)
  142. if self._attr[field].type == 'boolean' and isinstance(val, (str, unicode)):
  143. val = Demeter.bool(val)
  144. self.check(field, val, self._attr[field])
  145. self._bind[field] = val
  146. self._attr[field].val(self._bind[field])
  147. self._attr[field].bind('%s')
  148. self._key = sorted(self._key.items(), key=lambda d:d[1], reverse = False)
  149. Counter().unset()
  150. def check(self, field, val, attr):
  151. if attr.match == 'not':
  152. if not val:
  153. Demeter.error(field + ' not exists')
  154. elif attr.match:
  155. result = re.search(attr.match, val)
  156. if not result:
  157. Demeter.error(field + ' not match:' + attr.match)
  158. def time(self):
  159. return Demeter.time()
  160. def mktime(self, value):
  161. return Demeter.mktime(value)
  162. def setUuid(self, field, col):
  163. id = getattr(self, self._attr[field].uuid)
  164. if isinstance(id, col):
  165. system = short_url.encode_url(id)
  166. else:
  167. system = self._attr[field].uuid
  168. name = system + '.' + self.__table__
  169. result = uuid.uuid5(uuid.uuid1(), name)
  170. result = str(result)
  171. setattr(self, field, result)
  172. def createMd5(self, value):
  173. return Demeter.md5(value, salt=True)
  174. def createState(self):
  175. if 'create' in self._config:
  176. create = Demeter.bool(self._config['create'])
  177. if create:
  178. return Demeter.runtime(self._type, self.__table__, json.dumps(self._key))
  179. return False
  180. def drop(self):
  181. return self.handle('drop')
  182. def create(self):
  183. return self.handle('create')
  184. def insert(self):
  185. return self.handle('insert')
  186. def update(self, *args, **kwargs):
  187. if args:
  188. self._set = args[0]
  189. else:
  190. self._set = kwargs
  191. return self.handle('update', set=self._set)
  192. def delete(self):
  193. return self.handle('delete')
  194. def select(self, type='fetchall',col = '*', order = 'cdate desc', group = '', limit = '0,100', page=False):
  195. pageConfig = {}
  196. if page and 'page' in Demeter.config:
  197. pageConfig['current'] = Demeter.config['page']['current']
  198. if page == True:
  199. pageConfig['num'] = 15
  200. elif 'num' in page:
  201. pageConfig['num'] = page['num']
  202. Demeter.config['page']['num'] = pageConfig['num']
  203. return self.handle('select', type=type, col=col, order=order, group=group, limit=limit, page=pageConfig)
  204. def manage(self):
  205. self.attr(method)
  206. return
  207. def handle(self, method='select', type='fetchall', col = '*', order = '', group = '', limit = '0,100', page=False, set = ''):
  208. self.attr(method)
  209. if method == 'create':
  210. create = self.createState()
  211. if create == False:
  212. return False
  213. if type == 'fetchone':
  214. limit = '0,1'
  215. load = getattr(Sql(self._type), method)
  216. return self.query(load(self._table, {'key':self._key, 'fields':self._attr, 'col':col, 'order':order, 'group':group, 'limit':limit, 'page':page, 'set':set, 'table_comment':self.__comment__}), method, type)
  217. class Fields(object):
  218. def __init__(self, type='', default='', primaryKey=False, autoIncrement=False, null=True, unique=False, check='', constraint='', comment='', uuid='', index=False, indexs=False, md5=False, match='', manage=''):
  219. self.type = type
  220. self.default = default
  221. self.primaryKey = primaryKey
  222. self.autoIncrement = autoIncrement
  223. self.null = null
  224. self.unique = unique
  225. self.check = check
  226. self.constraint = constraint
  227. self.comment = comment
  228. self.uuid = uuid
  229. self.index = index
  230. self.indexs = indexs
  231. self.md5 = md5
  232. self.key = Counter().inc()
  233. self.match = match
  234. self.value = ''
  235. self.argv = ''
  236. self.bindValue = ''
  237. self.expValue = '='
  238. self.logicValue = 'and'
  239. self.manage = manage
  240. def assgin(self, value, exp='=', logic='and'):
  241. self.add(value)
  242. self.exp(exp)
  243. self.logic(logic)
  244. return self
  245. def bind(self, value):
  246. self.bindValue = value
  247. return self
  248. def exp(self, value):
  249. if type(self.expValue) != list:
  250. self.expValue = []
  251. self.expValue.append(value)
  252. return self
  253. def logic(self, value):
  254. if type(self.logicValue) != list:
  255. self.logicValue = []
  256. self.logicValue.append(value)
  257. return self
  258. def val(self, value, exp='=', logic='and'):
  259. if type(value) == list:
  260. length = len(value)
  261. if length <= 1:
  262. value = value[0]
  263. self.value = value
  264. if not self.expValue:
  265. self.exp(exp)
  266. if not self.logicValue:
  267. self.logic(logic)
  268. return self
  269. def getArgv(self):
  270. return self.argv
  271. def getVal(self):
  272. return self.value
  273. def getBind(self):
  274. return self.bindValue
  275. def getExp(self):
  276. if not self.expValue:
  277. return ''
  278. if type(self.expValue) == list:
  279. length = len(self.expValue)
  280. if length <= 1:
  281. result = self.expValue[0]
  282. else:
  283. result = self.expValue
  284. else:
  285. result = self.expValue
  286. return result
  287. def getKey(self):
  288. return self.key
  289. def getLogic(self):
  290. if not self.logicValue:
  291. return ''
  292. if type(self.logicValue) == list:
  293. length = len(self.logicValue)
  294. if length <= 1:
  295. result = self.logicValue[0]
  296. else:
  297. result = self.logicValue
  298. else:
  299. result = self.logicValue
  300. return result
  301. def unset(self):
  302. self.argv = None
  303. self.value = None
  304. self.bindValue = None
  305. self.expValue = '='
  306. self.logicValue = 'and'
  307. return self
  308. def add(self, value):
  309. if not self.argv:
  310. self.argv = []
  311. self.argv.append(value)
  312. return self
  313. def ins(self, value):
  314. self.argv = value
  315. self.exp('in')
  316. return self
  317. def nq(self, value):
  318. self.argv = value
  319. self.exp('!=')
  320. return self
  321. def like(self, value):
  322. self.argv = '%' + value + '%'
  323. self.exp('like')
  324. return self
  325. def mlike(self, value):
  326. self.argv = value
  327. self.exp('~')
  328. self.logic('and')
  329. return self
  330. def time(self, value):
  331. self.add(Demeter.mktime(value))
  332. return self
  333. def start(self, value):
  334. self.time(value)
  335. self.exp('>=')
  336. self.logic('and')
  337. return self
  338. def end(self, value):
  339. self.time(value)
  340. self.exp('<=')
  341. self.logic('and')
  342. return self
  343. class Counter(object):
  344. num = 0
  345. instance = None
  346. def __new__(cls, *args, **kwd):
  347. if Counter.instance is None:
  348. Counter.instance = object.__new__(cls, *args, **kwd)
  349. return Counter.instance
  350. def inc(self):
  351. self.num = self.num + 1
  352. return self.num
  353. def dec(self):
  354. self.num = self.num - 1
  355. return self.num
  356. def unset(self):
  357. self.num = 0
  358. return self.num
  359. class Sql(object):
  360. instance = None
  361. def __new__(cls, *args, **kwd):
  362. if Sql.instance is None:
  363. Sql.instance = object.__new__(cls, *args, **kwd)
  364. return Sql.instance
  365. def __init__(self, type):
  366. self.type = type
  367. def drop(self, table, args):
  368. sql = 'DROP TABLE IF EXISTS ' + table
  369. return sql
  370. def alter(self, table, args):
  371. sql = 'ALTER TABLE ' + table + ' ADD COLUMN '
  372. return sql
  373. def create(self, table, args):
  374. create = []
  375. primary = []
  376. unique = []
  377. indexs = []
  378. index = []
  379. comment = {}
  380. for value in args['key']:
  381. key = value[0]
  382. val = args['fields'][key]
  383. if val.primaryKey:
  384. primary.append(key)
  385. if val.unique:
  386. unique.append(key)
  387. if val.index:
  388. index.append((key, val.index))
  389. if val.indexs:
  390. indexs.append(key)
  391. fields = []
  392. fields.append(key)
  393. if val.autoIncrement and self.type == 'postgresql':
  394. fields.append('SERIAL')
  395. else:
  396. fields.append(val.type)
  397. if not val.null:
  398. fields.append('NOT NULL')
  399. if val.autoIncrement and self.type == 'mysql':
  400. fields.append('AUTO_INCREMENT')
  401. #约束
  402. if val.constraint:
  403. fields.append('CONSTRAINT ' + val.constraint)
  404. if val.check:
  405. fields.append('CHECK ' + val.check)
  406. if val.default:
  407. default = val.default
  408. if val.default == 'time':
  409. default = '0'
  410. if '.' in val.default:
  411. temp = val.default.split('.')
  412. default = Demeter.config[temp[0]][temp[1]]
  413. fields.append('DEFAULT \'' + str(default) + '\'')
  414. if val.comment:
  415. if self.type == 'mysql':
  416. fields.append('COMMENT \'' + val.comment + '\'')
  417. else:
  418. comment[key] = val.comment
  419. fields = ' '.join(fields)
  420. create.append(fields)
  421. if primary:
  422. create.append('PRIMARY KEY (' + ','.join(primary) + ')')
  423. if unique:
  424. create.append('UNIQUE (' + ','.join(unique) + ')')
  425. create = ','.join(create)
  426. sql = 'CREATE TABLE ' + table + '(' + create + ')'
  427. sql = self.drop(table, args) + ';' + sql
  428. if indexs:
  429. name = '_'.join(indexs)
  430. value = ','.join(indexs)
  431. sql = sql + ';' + 'CREATE INDEX ' + table + '_' + name +' ON ' + table + '(' + value + ')'
  432. if index:
  433. for value in index:
  434. sql = sql + ';' + 'CREATE INDEX ' + table + '_' + value[0] +' ON ' + table + value[1]
  435. if comment:
  436. if args['table_comment']:
  437. sql = sql + ';' + 'COMMENT ON TABLE ' + table + ' IS \''+args['table_comment']+'\''
  438. for key in comment:
  439. sql = sql + ';' + 'COMMENT ON COLUMN ' + table + '.'+key+' IS \''+comment[key]+'\''
  440. return sql
  441. def insert(self, table, args):
  442. fields = []
  443. values = []
  444. for value in args['key']:
  445. key = value[0]
  446. val = args['fields'][key].getBind()
  447. if val:
  448. values.append(val)
  449. fields.append(key)
  450. fields = ','.join(fields)
  451. values = ','.join(values)
  452. sql = 'INSERT INTO ' + table + ' (' + fields + ') VALUES (' + values + ')'
  453. if self.type == 'postgresql':
  454. sql = sql + ' RETURNING id'
  455. return sql
  456. def update(self, table, args):
  457. fields = []
  458. for key in args['set']:
  459. fields.append(key + ' = %s')
  460. fields = ','.join(fields)
  461. sql = 'UPDATE ' + table + ' SET ' + fields + self.where(args['key'], args['fields'])
  462. return sql
  463. def delete(self, table, args):
  464. sql = 'DELETE FROM ' + table + self.where(args['fields'])
  465. return sql
  466. def select(self, table, args):
  467. string = ' FROM ' + table + self.where(args['key'], args['fields']) + ' ' + self.group(args['group'])
  468. sql = ''
  469. if args['page']:
  470. sql = 'SELECT count(1) as total' + string + ';'
  471. sql = sql + 'SELECT ' + args['col'] + string + ' ' + self.order(args['order']) + ' ' + self.limit(args['limit'], args['page'])
  472. return sql
  473. def where(self, key, fields):
  474. fields = self.fields(key, fields)
  475. if fields:
  476. return ' WHERE ' + fields
  477. return ''
  478. def fields(self, key, fields):
  479. result = ''
  480. k = 0
  481. for value in key:
  482. key = value[0]
  483. field = fields[key]
  484. bind = field.getBind()
  485. val = field.getVal()
  486. logic = field.getLogic()
  487. exp = field.getExp()
  488. if type(val) == list and val:
  489. n = 0
  490. for i in val:
  491. data = self.field(field, bind, key, k, logic[n], exp[n])
  492. n = n + 1
  493. if data:
  494. result = result + data
  495. k = 1
  496. else:
  497. data = self.field(field, bind, key, k, logic, exp)
  498. if data:
  499. result = result + data
  500. k = 1
  501. return result
  502. def field(self, field, val, key, k, logic, exp):
  503. result = ''
  504. if val:
  505. if k == 0:
  506. logic = ''
  507. else:
  508. logic = ' ' + logic
  509. result = logic + ' ' + key + ' ' + exp + ' ' + str(val)
  510. return result
  511. def order(self, value):
  512. result = ''
  513. if value:
  514. result = ' ORDER BY ' + value
  515. return result
  516. def group(self, value):
  517. result = ''
  518. if value:
  519. result = ' GROUP BY ' + value
  520. return result
  521. def limit(self, value, page):
  522. result = ''
  523. if page:
  524. value = str((int(page['current'])-1) * page['num']) + ',' + str(page['num'])
  525. if value:
  526. value = value.split(',')
  527. if self.type == 'mysql':
  528. result = ' LIMIT ' + value[0] + ',' + value[1]
  529. elif self.type == 'postgresql':
  530. result = ' LIMIT ' + value[1] + ' OFFSET ' + value[0]
  531. return result