anonymous group photoblog software
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

211 lines
11 KiB

  1. import config, config_defaults
  2. from sqlalchemy import create_engine
  3. from sqlalchemy import Table, Column, Integer, Text, String, MetaData
  4. from sqlalchemy.orm import sessionmaker, scoped_session
  5. from sqlalchemy.sql import func
  6. # needed by other modules
  7. from sqlalchemy.exc import OperationalError
  8. pool_opts = {}
  9. if config.SQL_POOLING:
  10. pool_opts = {
  11. 'pool_size': config.SQL_POOL_SIZE,
  12. 'max_overflow': config.SQL_POOL_MAX_OVERFLOW,
  13. }
  14. engine = create_engine(config.SQL_ENGINE, **pool_opts)
  15. Session = scoped_session(sessionmaker(bind=engine))
  16. metadata = MetaData()
  17. _boards = {}
  18. def board(name):
  19. '''Generates board table objects'''
  20. if name in _boards:
  21. return _boards[name]
  22. table = Table(name, metadata,
  23. Column("num", Integer, primary_key=True), # Post number, auto-increments
  24. Column("parent", Integer), # Parent post for replies in threads. For original posts, must be set to 0 (and not null)
  25. Column("timestamp", Integer), # Timestamp in seconds for when the post was created
  26. Column("lasthit", Integer), # Last activity in thread. Must be set to the same value for BOTH the original post and all replies!
  27. Column("ip", Text), # IP number of poster, in integer form!
  28. Column("date", Text), # The date, as a string
  29. Column("name", Text(convert_unicode=True)), # Name of the poster
  30. Column("trip", Text), # Tripcode (encoded)
  31. Column("email", Text(convert_unicode=True)), # Email address
  32. Column("subject", Text(convert_unicode=True)), # Subject
  33. Column("password", Text), # Deletion password (in plaintext)
  34. Column("comment", Text(convert_unicode=True)), # Comment text, HTML encoded.
  35. Column("image", Text(convert_unicode=True)), # Image filename with path and extension (IE, src/1081231233721.jpg)
  36. Column("size", Integer), # File size in bytes
  37. Column("md5", Text), # md5 sum in hex
  38. Column("width", Integer), # Width of image in pixels
  39. Column("height", Integer), # Height of image in pixels
  40. Column("thumbnail", Text), # Thumbnail filename with path and extension
  41. Column("tn_width", Text), # Thumbnail width in pixels
  42. Column("tn_height", Text), # Thumbnail height in pixels
  43. Column("lastedit", Text), # ADDED - Date of previous edit, as a string
  44. Column("lastedit_ip", Text), # ADDED - Previous editor of the post, if any
  45. Column("admin_post", Text), # ADDED - Admin post?
  46. # TODO: Probably should make this Boolean. Keeping as int for now to maintain compatibility with sorting functions.
  47. Column("stickied", Integer), # ADDED - Stickied?
  48. Column("locked", Text) # ADDED - Locked?
  49. )
  50. table.create(bind=engine, checkfirst=True)
  51. _boards[name] = table
  52. return _boards[name]
  53. admin = Table(config.SQL_ADMIN_TABLE, metadata,
  54. Column("num", Integer, primary_key=True), # Entry number, auto-increments
  55. Column("type", Text), # Type of entry (ipban, wordban, etc)
  56. Column("comment", Text(convert_unicode=True)), # Comment for the entry
  57. Column("ival1", Text), # Integer value 1 (usually IP)
  58. Column("ival2", Text), # Integer value 2 (usually netmask)
  59. Column("sval1", Text), # String value 1
  60. Column("total", Text), # ADDED - Total Ban?
  61. Column("expiration", Integer) # ADDED - Ban Expiration?
  62. )
  63. proxy = Table(config.SQL_PROXY_TABLE, metadata,
  64. Column("num", Integer, primary_key=True), # Entry number, auto-increments
  65. Column("type", Text), # Type of entry (black, white, etc)
  66. Column("ip", Text), # IP address
  67. Column("timestamp", Integer), # Age since epoch
  68. Column("date", Text) # Human-readable form of date
  69. )
  70. account = Table(config.SQL_ACCOUNT_TABLE, metadata,
  71. Column("username", String(25), primary_key=True), # Name of user--must be unique
  72. Column("account", Text, nullable=False), # Account type/class: mod, globmod, admin
  73. Column("password", Text, nullable=False), # Encrypted password
  74. Column("reign", Text), # List of board (tables) under jurisdiction: globmod and admin have global power and are exempt
  75. Column("disabled", Integer) # Disabled account?
  76. )
  77. activity = Table(config.SQL_STAFFLOG_TABLE, metadata,
  78. Column("num", Integer, primary_key=True), # ID
  79. Column("username", String(25), nullable=False), # Name of moderator involved
  80. Column("action", Text), # Action performed: post_delete, admin_post, admin_edit, ip_ban, ban_edit, ban_remove
  81. Column("info", Text), # Information
  82. Column("date", Text), # Date of action
  83. Column("ip", Text), # IP address of the moderator
  84. Column("admin_id", Integer), # For associating certain entries with the corresponding key on the admin table
  85. Column("timestamp", Integer) # Timestamp, for trimming
  86. )
  87. common = Table(config.SQL_COMMON_SITE_TABLE, metadata,
  88. Column("board", String(25), primary_key=True), # Name of comment table
  89. Column("type", Text) # Corresponding board type? (Later use)
  90. )
  91. report = Table(config.SQL_REPORT_TABLE, metadata,
  92. Column("num", Integer, primary_key=True), # Report number, auto-increments
  93. Column("board", String(25), nullable=False), # Board name
  94. Column("reporter", Text, nullable=False), # Reporter's IP address (decimal encoded)
  95. Column("offender", Text), # IP Address of the offending poster. Why the form-breaking redundancy with SQL_TABLE? If a post is deleted by the perpetrator, the trace is still logged. :)
  96. Column("postnum", Integer, nullable=False), # Post number
  97. Column("comment", Text(convert_unicode=True),
  98. nullable=False), # Mandated reason for the report.
  99. Column("timestamp", Integer), # Timestamp in seconds for when the post was created
  100. Column("date", Text), # Date of the report
  101. Column("resolved", Integer) # Is it resolved? (1: yes 0: no)
  102. )
  103. backup = Table(config.SQL_BACKUP_TABLE, metadata,
  104. Column("num", Integer, primary_key=True), # Primary key, auto-increments
  105. Column("board_name", String(25), nullable=False), # Board name
  106. Column("postnum", Integer), # Post number
  107. Column("parent", Integer), # Parent post for replies in threads. For original posts, must be set to 0 (and not null)
  108. Column("timestamp", Integer), # Timestamp in seconds for when the post was created
  109. Column("lasthit", Integer), # Last activity in thread. Must be set to the same value for BOTH the original post and all replies!
  110. Column("ip", Text), # IP number of poster, in integer form!
  111. Column("date", Text), # The date, as a string
  112. Column("name", Text(convert_unicode=True)), # Name of the poster
  113. Column("trip", Text), # Tripcode (encoded)
  114. Column("email", Text), # Email address
  115. Column("subject", Text(convert_unicode=True)), # Subject
  116. Column("password", Text), # Deletion password (in plaintext)
  117. Column("comment", Text(convert_unicode=True)), # Comment text, HTML encoded.
  118. Column("image", Text(convert_unicode=True)), # Image filename with path and extension (IE, src/1081231233721.jpg)
  119. Column("size", Integer), # File size in bytes
  120. Column("md5", Text), # md5 sum in hex
  121. Column("width", Integer), # Width of image in pixels
  122. Column("height", Integer), # Height of image in pixels
  123. Column("thumbnail", Text), # Thumbnail filename with path and extension
  124. Column("tn_width", Text), # Thumbnail width in pixels
  125. Column("tn_height", Text), # Thumbnail height in pixels
  126. Column("lastedit", Text), # ADDED - Date of previous edit, as a string
  127. Column("lastedit_ip", Text), # ADDED - Previous editor of the post, if any
  128. Column("admin_post", Text), # ADDED - Admin post?
  129. Column("stickied", Integer), # ADDED - Stickied?
  130. Column("locked", Text), # ADDED - Locked?
  131. Column("timestampofarchival", Integer) # When was this backed up?
  132. )
  133. passprompt = Table(config.SQL_PASSPROMPT_TABLE, metadata,
  134. Column("id", Integer, primary_key=True),
  135. Column("host", Text),
  136. Column("task", String(25)),
  137. Column("boardname", String(25)),
  138. Column("post", Integer),
  139. Column("timestamp", Integer),
  140. Column("passfail", Integer)
  141. )
  142. class Page(object):
  143. '''Pagination class: Given an SQL query and pagination information,
  144. produce only the relevant rows. N.B.: The board.Board class uses
  145. different pagination logic.'''
  146. def __init__(self, query, page_num, per_page):
  147. assert str(page_num).isdigit() and page_num > 0,\
  148. 'Invalid page number.'
  149. assert str(per_page).isdigit() and per_page > 0,\
  150. 'Invalid page entry count.'
  151. self.num = page_num
  152. if per_page > 200:
  153. self.per_page = 200
  154. else:
  155. self.per_page = per_page
  156. self.offset = (page_num - 1) * self.per_page
  157. session = Session()
  158. count = query.column(func.count())
  159. self.total_entries = session.execute(count).fetchone()['count_1']
  160. row_proxies = session.execute(query.limit(per_page)\
  161. .offset(self.offset))
  162. self.rows = [dict(row.items()) for row in row_proxies]
  163. self.total_pages = (self.total_entries + self.per_page - 1)\
  164. / self.per_page
  165. if self.total_pages == 0:
  166. self.total_pages = 1
  167. if self.total_pages < self.num:
  168. self.num = self.total_pages
  169. # Quick fix for 'board' -> 'board_name' column renaming.
  170. if self.rows:
  171. ren_board = 'board' in self.rows[0].keys()
  172. row_ctr = row_cycle = 1
  173. for row in self.rows:
  174. row_cycle ^= 0x3
  175. row['rowtype'] = row_cycle
  176. row['entry_number'] = row_ctr
  177. if ren_board:
  178. row['board_name'] = row['board']