Libresocial database creation script.

libresocial.sql

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `albums`;
CREATE TABLE IF NOT EXISTS `albums` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `title` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `desc` longtext COLLATE utf32_unicode_ci NOT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `avatars`;
CREATE TABLE IF NOT EXISTS `avatars` (
  `owner` bigint(20) NOT NULL,
  `filename` mediumtext COLLATE utf32_unicode_ci NOT NULL,
  `filename_optimized` mediumtext COLLATE utf32_unicode_ci NOT NULL,
  `filename_min` mediumtext COLLATE utf32_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `comments`;
CREATE TABLE IF NOT EXISTS `comments` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `commentable_id` bigint(255) NOT NULL,
  `commentable_type` mediumtext COLLATE utf32_unicode_ci NOT NULL,
  `liked_by` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `content` longtext COLLATE utf32_unicode_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `edited` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM AVG_ROW_LENGTH=249 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `conversations`;
CREATE TABLE IF NOT EXISTS `conversations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` enum('chat','channel') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'chat',
  `talk_type` enum('textonly','voip','video') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'textonly',
  `admins` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `participiants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `privacy_pragma` varchar(5) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'aaaaa',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `docs`;
CREATE TABLE IF NOT EXISTS `docs` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `title` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `file` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `files`;
CREATE TABLE IF NOT EXISTS `files` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` enum('document','photo','video','music','executable') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'document',
  `name` varchar(255) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'Unnamed',
  `owner` bigint(20) NOT NULL,
  `description` longtext COLLATE utf32_unicode_ci NOT NULL,
  `privacy_pragma` varchar(3) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'aaa',
  `extension` varchar(1024) COLLATE utf32_unicode_ci NOT NULL DEFAULT ' ',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `followers`;
CREATE TABLE IF NOT EXISTS `followers` (
  `follower` bigint(20) UNSIGNED NOT NULL,
  `target` bigint(20) NOT NULL
) ENGINE=MyISAM AVG_ROW_LENGTH=17 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `gifts`;
CREATE TABLE IF NOT EXISTS `gifts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `from` bigint(20) UNSIGNED NOT NULL,
  `to` bigint(20) UNSIGNED NOT NULL,
  `privacy_pragma` varchar(3) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'aaa',
  `type` enum('gift','stickerpack','money') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'gift',
  `coins` bigint(20) UNSIGNED DEFAULT NULL,
  `gift_id` bigint(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `gift_list`;
CREATE TABLE IF NOT EXISTS `gift_list` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `suggested` longtext COLLATE utf32_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `groups`;
CREATE TABLE IF NOT EXISTS `groups` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf32_unicode_ci NOT NULL,
  `info` longtext COLLATE utf32_unicode_ci NOT NULL,
  `about` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `status` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `privacy_pragma` varchar(27) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaa',
  `verified` enum('verified','no') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'verified',
  `owner` bigint(20) UNSIGNED DEFAULT NULL,
  `type` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `coadmins` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AVG_ROW_LENGTH=266 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `messages`;
CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,
  `edited` datetime NOT NULL,
  `liked_by` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `type` enum('text','invite','sticker','audio') COLLATE utf32_unicode_ci NOT NULL,
  `conversation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `content` longtext COLLATE utf32_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `music`;
CREATE TABLE IF NOT EXISTS `music` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `title` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `lyrics` longtext COLLATE utf32_unicode_ci NOT NULL,
  `nsfw` bit(1) NOT NULL,
  `file` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `photos`;
CREATE TABLE IF NOT EXISTS `photos` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `desc` longtext COLLATE utf32_unicode_ci NOT NULL,
  `nsfw` bit(1) NOT NULL,
  `file` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `posts`;
CREATE TABLE IF NOT EXISTS `posts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(20) NOT NULL,
  `liked_by` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `edited` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `content` longtext COLLATE utf32_unicode_ci NOT NULL,
  `target` bigint(255) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AVG_ROW_LENGTH=150 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `stickerpacks`;
CREATE TABLE IF NOT EXISTS `stickerpacks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `description` longtext COLLATE utf32_unicode_ci NOT NULL,
  `price` bigint(20) UNSIGNED DEFAULT NULL,
  `owner` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `stickers`;
CREATE TABLE IF NOT EXISTS `stickers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stickerpack` bigint(255) UNSIGNED NOT NULL,
  `replaces` longtext COLLATE utf32_unicode_ci DEFAULT NULL,
  `type` enum('image','video') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'image',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `tokens`;
CREATE TABLE IF NOT EXISTS `tokens` (
  `user` bigint(255) NOT NULL,
  `ip` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `token` longtext COLLATE utf32_unicode_ci NOT NULL
) ENGINE=InnoDB AVG_ROW_LENGTH=963 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'Jane',
  `last_name` varchar(50) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'Doe',
  `pseudo` varchar(50) COLLATE utf32_unicode_ci NOT NULL DEFAULT ' ',
  `info` longtext COLLATE utf32_unicode_ci DEFAULT NULL,
  `about` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `status` varchar(255) COLLATE utf32_unicode_ci NOT NULL DEFAULT ' ',
  `now_listening` bigint(20) UNSIGNED NOT NULL,
  `is_talking` enum('talking','no') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'no',
  `role` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `privacy_pragma` varchar(28) COLLATE utf32_unicode_ci NOT NULL DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaa',
  `sex` set('female','male') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'female',
  `type` enum('bot','sysent','default') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'default',
  `phone` int(11) NOT NULL,
  `email` varchar(50) COLLATE utf32_unicode_ci NOT NULL,
  `coins` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `last_action` datetime NOT NULL DEFAULT current_timestamp(),
  `last_device` enum('pc','tablet','mobile','nojs','embedded','car','gameconsole','term','tv','player') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'pc',
  `since` datetime NOT NULL DEFAULT current_timestamp(),
  `blacklist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `blocked` enum('totally','partial','no') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'no',
  `block_reason` tinytext COLLATE utf32_unicode_ci DEFAULT NULL,
  `dead` enum('dead','no') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'no',
  `verified` enum('verified','no') COLLATE utf32_unicode_ci NOT NULL DEFAULT 'no',
  `css` text COLLATE utf32_unicode_ci DEFAULT NULL,
  `achievments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `reputation` bigint(20) NOT NULL DEFAULT 1000,
  `people_reach` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `login` varchar(11) COLLATE utf32_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AVG_ROW_LENGTH=866 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `videos`;
CREATE TABLE IF NOT EXISTS `videos` (
  `id` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner` bigint(255) NOT NULL,
  `title` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  `desc` longtext COLLATE utf32_unicode_ci NOT NULL,
  `nsfw` bit(1) NOT NULL,
  `file` varchar(255) COLLATE utf32_unicode_ci NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

DROP TABLE IF EXISTS `__ov4config`;
CREATE TABLE IF NOT EXISTS `__ov4config` (
  `key` varchar(125) COLLATE utf32_unicode_ci NOT NULL,
  `value` varchar(1024) COLLATE utf32_unicode_ci NOT NULL,
  `desc` text COLLATE utf32_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;
SET FOREIGN_KEY_CHECKS=1;
COMMIT;