public function board_statistics($output, $shortname = null)
{
$boards = $this->radix_coll->getAll();
$available = $this->board_stats->getAvailableStats();
while (true) {
// Obtain all of the statistics already stored on the database to check for update frequency.
$stats = $this->dc->qb()->select('board_id, name, timestamp')->from($this->dc->p('plugin_fu_board_statistics'), 'bs')->orderBy('timestamp', 'desc')->execute()->fetchAll();
// Obtain the list of all statistics enabled.
$avail = [];
foreach ($available as $k => $a) {
// get only the non-realtime ones
if (isset($available['frequency'])) {
$avail[] = $k;
}
}
foreach ($boards as $board) {
if (!is_null($shortname) && $shortname != $board->shortname) {
continue;
}
// Update all statistics for the specified board or current board.
$output->writeln($board->shortname . ' (' . $board->id . ')');
foreach ($available as $k => $a) {
$output->writeln(' ' . $k . ' ');
$found = false;
$skip = false;
foreach ($stats as $r) {
// Determine if the statistics already exists or that the information is outdated.
if ($r['board_id'] == $board->id && $r['name'] == $k) {
// This statistics report has run once already.
$found = true;
if (!isset($a['frequency'])) {
$skip = true;
continue;
}
// This statistics report has not reached its frequency EOL.
if (time() - $r['timestamp'] <= $a['frequency']) {
$skip = true;
continue;
}
break;
}
}
// racing conditions with our cron.
if ($found === false) {
$this->board_stats->saveStat($board->id, $k, time() + 600, '');
}
// We were able to obtain a LOCK on the statistics report and has already reached the
// targeted frequency time.
if ($skip === false) {
$output->writeln('* Processing...');
$process = 'process' . $a['function'];
$result = $this->board_stats->{$process}($board);
// Save the statistics report in a JSON array.
$this->board_stats->saveStat($board->id, $k, time(), $result);
}
}
}
sleep(10);
}
}
public function saveStat($board_id, $name, $timestamp, $data = '')
{
$count = $this->dc->qb()->select('COUNT(*) as count')->from($this->dc->p('plugin_fu_board_statistics'), 'bs')->where('board_id = :board_id')->andWhere('name = :name')->setParameter(':board_id', $board_id)->setParameter(':name', $name)->execute()->fetch()['count'];
if (!$count) {
$this->dc->getConnection()->insert($this->dc->p('plugin_fu_board_statistics'), ['board_id' => $board_id, 'name' => $name, 'timestamp' => $timestamp, 'data' => json_encode($data)]);
} else {
$this->dc->qb()->update($this->dc->p('plugin_fu_board_statistics'))->where('board_id = :board_id')->andWhere('name = :name')->set('timestamp', ':timestamp')->set('data', ':data')->setParameter(':board_id', $board_id)->setParameter(':name', $name)->setParameter(':timestamp', $timestamp)->setParameter(':data', json_encode($data))->execute();
}
}
/**
* Adds a new ban
*
* @param string $ip_decimal The IP of the banned user in decimal format
* @param string $reason The reason for the ban
* @param int $length The length of the ban in seconds
* @param array $board_ids The array of board IDs, global ban if left empty
*
* @return \Foolz\Foolfuuka\Model\Ban
* @throws \Foolz\Foolfuuka\Model\BanException
*/
public function add($ip_decimal, $reason, $length, $board_ids = [])
{
// 0 is a global ban
if (empty($board_ids)) {
$board_ids = [0];
} else {
// check that all ids are existing boards
$valid_board_ids = [];
foreach ($this->radix_coll->getAll() as $board) {
$valid_board_ids[] = $board->id;
}
foreach ($board_ids as $id) {
if (!in_array($id, $valid_board_ids)) {
throw new BanException(_i('You entered a non-existent board ID.'));
}
}
}
if (!ctype_digit((string) $ip_decimal)) {
throw new BanException(_i('You entered an invalid IP.'));
}
if (mb_strlen($reason, 'utf-8') > 10000) {
throw new BanException(_i('You entered a too long reason for the ban.'));
}
if (!ctype_digit($length)) {
throw new BanException(_i('You entered an invalid length for the ban.'));
}
$time = time();
$objects = [];
try {
$old = $this->getByIp($ip_decimal);
} catch (BanNotFoundException $e) {
$old = false;
}
foreach ($board_ids as $board_id) {
$new = new Ban($this->getContext());
$new->ip = $ip_decimal;
$new->reason = $reason;
$new->start = $time;
$new->length = $length;
$new->board_id = $board_id;
$new->creator_id = $this->getAuth()->getUser()->getId();
$new->appeal = '';
if (isset($old[$new->board_id])) {
if ($new->length < $old[$new->board_id]->length) {
$new->length = $old[$new->board_id]->length;
}
$this->dc->qb()->update($this->dc->p('banned_posters'))->where('id = :id')->set('start', $new->start)->set('length', $new->length)->set('creator_id', $new->creator_id)->setParameter(':id', $old[$new->board_id]->id)->execute();
} else {
$this->dc->getConnection()->insert($this->dc->p('banned_posters'), ['ip' => $new->ip, 'reason' => $new->reason, 'start' => $new->start, 'length' => $new->length, 'board_id' => $new->board_id, 'creator_id' => $new->creator_id, 'appeal' => $new->appeal]);
}
$objects[] = $new;
}
return $objects;
}
public function deleteAccount($id, $key)
{
$row = $this->dc->qb()->select('*')->from($this->dc->p('users'), 'l')->where('id = :id')->andWhere('deletion_time > :deletion_time')->setParameter(':id', $id)->setParameter(':deletion_time', time() - 900)->execute()->fetch();
if (!$row || !password_verify($key, $row['deletion_key'])) {
throw new WrongKeyException();
}
$affected_rows = $this->dc->qb()->delete($this->dc->p('users'))->where('id = :id')->setParameter(':id', $id)->execute();
if (!$affected_rows) {
throw new WrongKeyException();
}
return true;
}
public function set($setting, $value, $reload = true)
{
// if array, serialize value
if (is_array($value)) {
$value = serialize($value);
}
$count = $this->dc->qb()->select('COUNT(*) as count')->from($this->dc->p('preferences'), 'p')->where('p.name = :name')->setParameter(':name', $setting)->execute()->fetch()['count'];
if ($count > 0) {
$this->dc->qb()->update($this->dc->p('preferences'))->set('value', ':value')->where('name = :name')->setParameters([':value' => $value, ':name' => $setting])->execute();
} else {
$this->dc->getConnection()->insert($this->dc->p('preferences'), ['name' => $setting, 'value' => $value]);
}
if ($reload) {
return $this->load(true);
}
return $this->preferences;
}
public function install($slug)
{
$plugin = $this->loader->get($slug);
$plugin->install();
$this->dc->getConnection()->insert($this->dc->p('plugins'), ['slug' => $slug, 'enabled' => 1]);
$this->clearCache();
// run the schema update
$sm = \Foolz\FoolFrame\Model\SchemaManager::forge($this->dc->getConnection(), $this->dc->getPrefix() . 'plugin_');
foreach ($this->getInstalled() as $enabled) {
try {
$plug = $this->loader->get($enabled['slug']);
if (!$plug->isBootstrapped()) {
$plug->bootstrap();
}
\Foolz\Plugin\Hook::forge('Foolz\\FoolFrame\\Model\\Plugin::install#' . $plug->getConfig('name'))->setParam('context', $this->getContext())->setParam('schema', $sm->getCodedSchema())->execute();
} catch (\OutOfBoundsException $e) {
}
}
$sm->commit();
$this->clearCache();
}
/**
* Delete the post and eventually the entire thread if it's OP
* Also deletes the images when it's the only post with that image
*
* @param null $password
* @param bool $force
* @param bool $thread
* @throws CommentSendingDatabaseException
* @throws CommentDeleteWrongPassException
* @return array|bool
*/
protected function p_delete($password = null, $force = false, $thread = false)
{
if (!$this->getAuth()->hasAccess('comment.passwordless_deletion') && $force !== true) {
if (!password_verify($password, $this->comment->getDelpass())) {
throw new CommentDeleteWrongPassException(_i('You did not provide the correct deletion password.'));
}
}
try {
$this->dc->getConnection()->beginTransaction();
// check that the post isn't already in deleted
$has_deleted = $this->dc->qb()->select('COUNT(*) as found')->from($this->radix->getTable('_deleted'), 'd')->where('doc_id = :doc_id')->setParameter(':doc_id', $this->comment->doc_id)->execute()->fetch();
if (!$has_deleted['found']) {
// throw into _deleted table
$this->dc->getConnection()->executeUpdate('INSERT INTO ' . $this->radix->getTable('_deleted') . ' ' . $this->dc->qb()->select('*')->from($this->radix->getTable(), 't')->where('doc_id = ' . $this->dc->getConnection()->quote($this->comment->doc_id))->getSQL());
}
// delete post
$this->dc->qb()->delete($this->radix->getTable())->where('doc_id = :doc_id')->setParameter(':doc_id', $this->comment->doc_id)->execute();
// purge reports
$this->dc->qb()->delete($this->dc->p('reports'))->where('board_id = :board_id')->andWhere('doc_id = :doc_id')->setParameter(':board_id', $this->radix->id)->setParameter(':doc_id', $this->comment->doc_id)->execute();
// clear cache
$this->radix_coll->clearCache();
// remove image file
if (isset($this->media)) {
$media_sql = $this->dc->qb()->select('COUNT(*)')->from($this->radix->getTable(), 't')->where('media_id = :media_id')->setParameter(':media_id', $this->media->media_id)->getSQL();
$this->dc->qb()->update($this->radix->getTable('_images'))->set('total', '(' . $media_sql . ')')->where('media_id = :media_id')->setParameter(':media_id', $this->media->media_id)->execute();
$has_image = $this->dc->qb()->select('total')->from($this->radix->getTable('_images'), 'ti')->where('media_id = :media_id')->setParameter(':media_id', $this->media->media_id)->execute()->fetch();
if (!$has_image || !$has_image['total']) {
$media = new Media($this->getContext(), $this->bulk);
$media->delete();
}
}
// if this is OP, delete replies too
if ($this->comment->op) {
// delete thread data
$this->dc->qb()->delete($this->radix->getTable('_threads'))->where('thread_num = :thread_num')->setParameter(':thread_num', $this->comment->thread_num)->execute();
// process each comment
$comments = $this->dc->qb()->select('doc_id')->from($this->radix->getTable(), 'b')->where('thread_num = :thread_num')->setParameter(':thread_num', $this->comment->thread_num)->execute()->fetchAll();
foreach ($comments as $comment) {
$post = Board::forge($this->getContext())->getPost()->setOptions('doc_id', $comment['doc_id'])->setRadix($this->radix)->getComments();
$post = current($post);
$post = new Comment($this->getContext(), $post);
$post->delete(null, true, true);
}
} else {
// if this is not triggered by a thread deletion, update the thread table
if ($thread === false && !$this->radix->archive) {
$time_last = '
(
COALESCE(GREATEST(
time_op,
(
SELECT MAX(timestamp) FROM ' . $this->radix->getTable() . ' xr
WHERE thread_num = ' . $this->dc->getConnection()->quote($this->comment->thread_num) . ' AND subnum = 0
)
), time_op)
)';
$time_bump = '
(
COALESCE(GREATEST(
time_op,
(
SELECT MAX(timestamp) FROM ' . $this->radix->getTable() . ' xr
WHERE thread_num = ' . $this->dc->getConnection()->quote($this->comment->thread_num) . ' AND subnum = 0
AND (email <> \'sage\' OR email IS NULL)
)
), time_op)
)';
$time_ghost = '
(
SELECT MAX(timestamp) FROM ' . $this->radix->getTable() . ' xr
WHERE thread_num = ' . $this->dc->getConnection()->quote($this->comment->thread_num) . ' AND subnum <> 0
)';
$time_ghost_bump = '
(
SELECT MAX(timestamp) FROM ' . $this->radix->getTable() . ' xr
WHERE thread_num = ' . $this->dc->getConnection()->quote($this->comment->thread_num) . ' AND subnum <> 0
AND (email <> \'sage\' OR email IS NULL)
)';
// update thread information
$this->dc->qb()->update($this->radix->getTable('_threads'))->set('time_last', $time_last)->set('time_bump', $time_bump)->set('time_ghost', $time_ghost)->set('time_ghost_bump', $time_ghost_bump)->set('time_last_modified', ':time')->set('nreplies', 'nreplies - 1')->set('nimages', $this->media === null ? 'nimages' : 'nimages - 1')->where('thread_num = :thread_num')->setParameter(':time', $this->getRadixTime())->setParameter(':thread_num', $this->comment->thread_num)->execute();
}
}
$this->dc->getConnection()->commit();
$this->clearCache();
if ($thread === false) {
$this->audit->log(Audit::AUDIT_DEL_POST, ['radix' => $this->radix->id, 'doc_id' => $this->comment->doc_id, 'thread_num' => $this->comment->thread_num, 'num' => $this->comment->num, 'subnum' => $this->comment->subnum]);
}
} catch (\Doctrine\DBAL\DBALException $e) {
$this->logger->error('\\Foolz\\FoolFuuka\\Model\\CommentInsert: ' . $e->getMessage());
$this->dc->getConnection()->rollBack();
throw new CommentSendingDatabaseException(_i('Something went wrong when deleting the post in the database. Try again.'));
}
return $this;
}
/**
* Puts the table in readily available variables
*/
public function preload()
{
$this->profiler->log('Radix::preload Start');
try {
$result = Cache::item('foolfuuka.model.radix.preload')->get();
} catch (\OutOfBoundsException $e) {
$result = $this->dc->qb()->select('*')->from($this->dc->p('boards'), 'b')->orderBy('shortname', 'ASC')->execute()->fetchAll();
Cache::item('foolfuuka.model.radix.preload')->set($result, 900);
}
if (!is_array($result) || empty($result)) {
$this->preloaded_radixes = [];
return false;
}
$result_object = [];
foreach ($result as $item) {
// don't process hidden boards
if (!$this->getAuth()->hasAccess('boards.see_hidden') && $item['hidden'] == 1) {
continue;
}
$structure = $this->structure($item);
$result_object[$item['id']] = new Radix($this->getContext(), $this);
// set the plain database data as keys
foreach ($item as $k => $i) {
$result_object[$item['id']]->{$k} = $i;
// we set it also in the values so we can just use it from there as commodity
$result_object[$item['id']]->setValue($k, $i);
}
// url values for commodity
$result_object[$item['id']]->setValue('formatted_title', $item['name'] ? '/' . $item['shortname'] . '/ - ' . $item['name'] : '/' . $item['shortname'] . '/');
// load the basic value of the preferences
foreach ($structure as $key => $arr) {
if (!isset($result_object[$item['id']]->{$key}) && isset($arr['boards_preferences'])) {
$result_object[$item['id']]->setValue($key, $this->config->get('foolz/foolfuuka', 'package', 'preferences.radix.' . $key, false));
}
foreach (['sub', 'sub_inverse'] as $sub) {
if (isset($arr[$sub])) {
foreach ($arr[$sub] as $k => $a) {
if (!isset($result_object[$item['id']]->{$k}) && isset($a['boards_preferences'])) {
$result_object[$item['id']]->setValue($k, $this->config->get('foolz/foolfuuka', 'package', 'preferences.radix.' . $k, false));
}
}
}
}
}
}
// load the preferences from the board_preferences table
$this->profiler->log('Radix::load_preferences Start');
try {
$preferences = Cache::item('foolfuuka.model.radix.load_preferences')->get();
} catch (\OutOfBoundsException $e) {
$preferences = $this->dc->qb()->select('*')->from($this->dc->p('boards_preferences'), 'p')->execute()->fetchAll();
Cache::item('foolfuuka.model.radix.load_preferences')->set($preferences, 900);
}
foreach ($preferences as $value) {
// in case of leftover values, it would try instantiating a new stdClass and that would trigger error
if (isset($result_object[$value['board_id']])) {
$result_object[$value['board_id']]->setValue($value['name'], $value['value']);
}
}
$this->preloaded_radixes = $result_object;
$this->profiler->logMem('Radix $this->preloaded_radixes', $this->preloaded_radixes);
$this->profiler->log('Radix::load_preferences End');
// take them all and then filter/do whatever (we use this to split the boards through various subdomains)
// only public is affected! admins and mods will see all boards at all the time
$this->preloaded_radixes = \Foolz\Plugin\Hook::forge('Foolz\\Foolfuuka\\Model\\Radix::preload.result.public')->setObject($this)->setParam('preloaded_radixes', $this->preloaded_radixes)->execute()->get($this->preloaded_radixes);
$this->profiler->log('Radix::preload End');
$this->profiler->logMem('Radix $this->preloaded_radixes w/ preferences', $this->preloaded_radixes);
}
public function log($type, $data)
{
$this->dc->getConnection()->insert($this->dc->p('audit_log'), ['timestamp' => time(), 'user' => $this->getAuth()->getUser()->getId(), 'type' => $type, 'data' => json_encode($data)]);
}
/**
* Deletes a Report
*
* @param int $id The ID of the Report
*
* @throws \Foolz\FoolFuuka\Model\ReportNotFoundException
*/
public function p_delete($id)
{
$this->dc->qb()->delete($this->dc->p('reports'))->where('id = :id')->setParameter(':id', $id)->execute();
$this->clearCache();
}
/**
* Creates the tables for the board
*/
public function createTables()
{
$config = $this->config->get('foolz/foolframe', 'db', 'default');
$config['dbname'] = $this->preferences->get('foolfuuka.boards.db') ?: $config['dbname'];
$config['prefix'] = $this->preferences->get("foolfuuka.boards.prefix");
$conn = new DoctrineConnection($this->getContext(), $config);
$charset = 'utf8mb4';
$collate = 'utf8mb4_general_ci';
$sm = $conn->getConnection()->getSchemaManager();
$schema = $sm->createSchema();
// create the main table also in _deleted flavour
foreach (['', '_deleted'] as $key) {
if (!$schema->hasTable($this->getPrefix() . $this->shortname . $key)) {
$table = $schema->createTable($this->getPrefix() . $this->shortname . $key);
if ($conn->getConnection()->getDriver()->getName() == 'pdo_mysql') {
$table->addOption('charset', $charset);
$table->addOption('collate', $collate);
}
$table->addColumn('doc_id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
$table->addColumn('media_id', 'integer', ['unsigned' => true, 'default' => 0]);
$table->addColumn('poster_ip', 'decimal', ['unsigned' => true, 'precision' => 39, 'scale' => 0, 'default' => 0]);
$table->addColumn('num', 'integer', ['unsigned' => true]);
$table->addColumn('subnum', 'integer', ['unsigned' => true]);
$table->addColumn('thread_num', 'integer', ['unsigned' => true, 'default' => 0]);
$table->addColumn('op', 'boolean', ['default' => 0]);
$table->addColumn('timestamp', 'integer', ['unsigned' => true]);
$table->addColumn('timestamp_expired', 'integer', ['unsigned' => true]);
$table->addColumn('preview_orig', 'string', ['length' => 20, 'notnull' => false]);
$table->addColumn('preview_w', 'smallint', ['unsigned' => true, 'default' => 0]);
$table->addColumn('preview_h', 'smallint', ['unsigned' => true, 'default' => 0]);
$table->addColumn('media_filename', 'text', ['length' => 65532, 'notnull' => false]);
$table->addColumn('media_w', 'smallint', ['unsigned' => true, 'default' => 0]);
$table->addColumn('media_h', 'smallint', ['unsigned' => true, 'default' => 0]);
$table->addColumn('media_size', 'integer', ['unsigned' => true, 'default' => 0]);
$table->addColumn('media_hash', 'string', ['length' => 25, 'notnull' => false]);
$table->addColumn('media_orig', 'string', ['length' => 20, 'notnull' => false]);
$table->addColumn('spoiler', 'boolean', ['default' => 0]);
$table->addColumn('deleted', 'boolean', ['default' => 0]);
$table->addColumn('capcode', 'string', ['length' => 1, 'default' => 'N']);
$table->addColumn('email', 'string', ['length' => 100, 'notnull' => false]);
$table->addColumn('name', 'string', ['length' => 100, 'notnull' => false]);
$table->addColumn('trip', 'string', ['length' => 25, 'notnull' => false]);
$table->addColumn('title', 'string', ['length' => 100, 'notnull' => false]);
$table->addColumn('comment', 'text', ['length' => 65532, 'notnull' => false]);
$table->addColumn('delpass', 'text', ['length' => 255, 'notnull' => false]);
$table->addColumn('sticky', 'boolean', ['default' => 0]);
$table->addColumn('locked', 'boolean', ['default' => 0]);
$table->addColumn('poster_hash', 'string', ['length' => 8, 'notnull' => false]);
$table->addColumn('poster_country', 'string', ['length' => 2, 'notnull' => false]);
$table->addColumn('exif', 'text', ['length' => 65532, 'notnull' => false]);
$table->setPrimaryKey(['doc_id']);
$table->addUniqueIndex(['num', 'subnum'], $this->getIndex($key, 'num_subnum_index'));
$table->addIndex(['thread_num', 'num', 'subnum'], $this->getIndex($key, 'thread_num_subnum_index'));
$table->addIndex(['subnum'], $this->getIndex($key, 'subnum_index'));
$table->addIndex(['op'], $this->getIndex($key, 'op_index'));
$table->addIndex(['media_id'], $this->getIndex($key, 'media_id_index'));
$table->addIndex(['media_hash'], $this->getIndex($key, 'media_hash_index'));
$table->addIndex(['media_orig'], $this->getIndex($key, 'media_orig_index'));
$table->addIndex(['name', 'trip'], $this->getIndex($key, 'name_trip_index'));
$table->addIndex(['trip'], $this->getIndex($key, 'trip_index'));
$table->addIndex(['email'], $this->getIndex($key, 'email_index'));
$table->addIndex(['poster_ip'], $this->getIndex($key, 'poster_ip_index'));
$table->addIndex(['timestamp'], $this->getIndex($key, 'timestamp_index'));
}
}
if (!$schema->hasTable($this->getPrefix() . $this->shortname . '_threads')) {
$table_threads = $schema->createTable($this->getPrefix() . $this->shortname . '_threads');
if ($conn->getConnection()->getDriver()->getName() == 'pdo_mysql') {
$table_threads->addOption('charset', $charset);
$table_threads->addOption('collate', $collate);
}
$table_threads->addColumn('thread_num', 'integer', ['unsigned' => true]);
$table_threads->addColumn('time_op', 'integer', ['unsigned' => true]);
$table_threads->addColumn('time_last', 'integer', ['unsigned' => true]);
$table_threads->addColumn('time_bump', 'integer', ['unsigned' => true]);
$table_threads->addColumn('time_ghost', 'integer', ['unsigned' => true, 'notnull' => false, 'default' => 'null']);
$table_threads->addColumn('time_ghost_bump', 'integer', ['unsigned' => true, 'notnull' => false, 'default' => 'null']);
$table_threads->addColumn('time_last_modified', 'integer', ['unsigned' => true]);
$table_threads->addColumn('nreplies', 'integer', ['unsigned' => true, 'default' => 0]);
$table_threads->addColumn('nimages', 'integer', ['unsigned' => true, 'default' => 0]);
$table_threads->addColumn('sticky', 'boolean', ['default' => 0]);
$table_threads->addColumn('locked', 'boolean', ['default' => 0]);
$table_threads->setPrimaryKey(['thread_num']);
$table_threads->addIndex(['locked'], $this->getIndex('_threads', 'locked_index'));
$table_threads->addIndex(['time_op'], $this->getIndex('_threads', 'time_op_index'));
$table_threads->addIndex(['time_bump'], $this->getIndex('_threads', 'time_bump_index'));
$table_threads->addIndex(['time_ghost_bump'], $this->getIndex('_threads', 'time_ghost_bump_index'));
$table_threads->addIndex(['sticky'], $this->getIndex('_threads', 'sticky_index'));
$table_threads->addIndex(['sticky', 'time_bump'], $this->getIndex('_threads', 'sticky_time_bump_index'));
$table_threads->addIndex(['sticky', 'time_ghost_bump'], $this->getIndex('_threads', 'sticky_time_ghost_bump_index'));
$table_threads->addIndex(['sticky', 'thread_num'], $this->getIndex('_threads', 'sticky_thread_num_index'));
}
if (!$schema->hasTable($this->getPrefix() . $this->shortname . '_users')) {
$table_users = $schema->createTable($this->getPrefix() . $this->shortname . '_users');
if ($conn->getConnection()->getDriver()->getName() == 'pdo_mysql') {
$table_users->addOption('charset', $charset);
$table_users->addOption('collate', $collate);
}
$table_users->addColumn('user_id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
$table_users->addColumn('name', 'string', ['length' => 100, 'default' => '']);
$table_users->addColumn('trip', 'string', ['length' => 25, 'default' => '']);
$table_users->addColumn('firstseen', 'integer', ['unsigned' => true]);
$table_users->addColumn('postcount', 'integer', ['unsigned' => true]);
$table_users->setPrimaryKey(['user_id']);
$table_users->addUniqueIndex(['name', 'trip'], $this->getIndex('_users', 'name_trip_index'));
$table_users->addIndex(['firstseen'], $this->getIndex('_users', 'firstseen_index'));
$table_users->addIndex(['postcount'], $this->getIndex('_users', 'postcount_index'));
}
if (!$schema->hasTable($this->getPrefix() . $this->shortname . '_images')) {
$table_images = $schema->createTable($this->getPrefix() . $this->shortname . '_images');
if ($conn->getConnection()->getDriver()->getName() == 'pdo_mysql') {
$table_images->addOption('charset', 'utf8');
$table_images->addOption('collate', 'utf8_general_ci');
}
$table_images->addColumn('media_id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
$table_images->addColumn('media_hash', 'string', ['length' => 25]);
$table_images->addColumn('media', 'string', ['length' => 20, 'notnull' => false]);
$table_images->addColumn('preview_op', 'string', ['length' => 20, 'notnull' => false]);
$table_images->addColumn('preview_reply', 'string', ['length' => 20, 'notnull' => false]);
$table_images->addColumn('total', 'integer', ['unsigned' => true, 'default' => 0]);
$table_images->addColumn('banned', 'smallint', ['unsigned' => true, 'default' => 0]);
$table_images->setPrimaryKey(['media_id']);
$table_images->addUniqueIndex(['media_hash'], $this->getIndex('_images', 'media_hash_index'));
$table_images->addIndex(['total'], $this->getIndex('_images', 'total_index'));
$table_images->addIndex(['banned'], $this->getIndex('_images', 'banned_index'));
}
if (!$schema->hasTable($this->getPrefix() . $this->shortname . '_daily')) {
$table_daily = $schema->createTable($this->getPrefix() . $this->shortname . '_daily');
if ($conn->getConnection()->getDriver()->getName() == 'pdo_mysql') {
$table_daily->addOption('charset', 'utf8');
$table_daily->addOption('collate', 'utf8_general_ci');
}
$table_daily->addColumn('day', 'integer', ['unsigned' => true]);
$table_daily->addColumn('posts', 'integer', ['unsigned' => true]);
$table_daily->addColumn('images', 'integer', ['unsigned' => true]);
$table_daily->addColumn('sage', 'integer', ['unsigned' => true]);
$table_daily->addColumn('anons', 'integer', ['unsigned' => true]);
$table_daily->addColumn('trips', 'integer', ['unsigned' => true]);
$table_daily->addColumn('names', 'integer', ['unsigned' => true]);
$table_daily->setPrimaryKey(['day']);
}
$conn->getConnection()->beginTransaction();
foreach ($schema->getMigrateFromSql($sm->createSchema(), $sm->getDatabasePlatform()) as $query) {
$conn->getConnection()->query($query);
}
$md5_array = $this->dc->qb()->select('md5')->from($this->dc->p('banned_md5'), 'm')->execute()->fetchAll();
// in a transaction multiple inserts are almost like a single one
foreach ($md5_array as $item) {
$conn->getConnection()->insert($this->getTable('_images'), ['md5' => $item['md5'], 'banned' => 1]);
}
$conn->getConnection()->commit();
}
