model.py 16 KB

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