model.py 15 KB

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