289 lines
12 KiB
SQL
289 lines
12 KiB
SQL
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `expenses`
|
|
--
|
|
|
|
CREATE TABLE `expenses` (
|
|
`id` bigint(25) UNSIGNED NOT NULL,
|
|
`unique_url` varchar(70) DEFAULT NULL,
|
|
`title` varchar(70) NOT NULL,
|
|
`currency` varchar(3) DEFAULT NULL,
|
|
`description` varchar(150) DEFAULT NULL,
|
|
`language` varchar(3) DEFAULT NULL,
|
|
`expense_type` char(1) DEFAULT NULL,
|
|
`create_user` varchar(50) DEFAULT NULL,
|
|
`update_user` varchar(20) DEFAULT NULL,
|
|
`status` char(1) DEFAULT NULL,
|
|
`create_date` datetime DEFAULT NULL,
|
|
`update_date` int(11) DEFAULT NULL,
|
|
`default_meal` varchar(17) DEFAULT NULL,
|
|
`source` tinyint(4) UNSIGNED NOT NULL COMMENT '1=web,2=mobile,3=facebook',
|
|
`ip` varchar(25) CHARACTER SET utf16 COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`user_agent` varchar(80) CHARACTER SET utf16 COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`user_id` int(20) UNSIGNED DEFAULT NULL,
|
|
`custom_categories` varchar(2000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
|
|
`version` int(2) NOT NULL DEFAULT '0'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `expense_emails`
|
|
--
|
|
|
|
CREATE TABLE `expense_emails` (
|
|
`id` bigint(25) UNSIGNED NOT NULL,
|
|
`unique_url` varchar(70) COLLATE utf16_unicode_ci NOT NULL,
|
|
`email` varchar(50) COLLATE utf16_unicode_ci NOT NULL,
|
|
`title` varchar(70) COLLATE utf16_unicode_ci NOT NULL,
|
|
`count_email_attempt` tinyint(4) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `family_expenses`
|
|
--
|
|
|
|
CREATE TABLE `family_expenses` (
|
|
`id` varchar(40) CHARACTER SET utf8 NOT NULL,
|
|
`expense_id` bigint(20) UNSIGNED NOT NULL,
|
|
`record_type` char(1) CHARACTER SET utf8 DEFAULT '1' COMMENT '1=expense,2=income',
|
|
`what` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
|
|
`category` varchar(30) CHARACTER SET utf8 DEFAULT '1' COMMENT '1=expense,2=income',
|
|
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`expense_date` int(13) DEFAULT NULL,
|
|
`expense_date_new` varchar(11) COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`update_date` int(13) DEFAULT NULL,
|
|
`number_of_notes` int(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
|
|
`amount` double DEFAULT NULL,
|
|
`syn_add_unique_id` varchar(50) COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`is_reimbursement` char(1) CHARACTER SET utf8 DEFAULT '0' COMMENT '0=no,1=yes reimbursement',
|
|
`transaction_type` tinyint(1) DEFAULT '0' COMMENT '1=expense,2=income',
|
|
`type` char(1) CHARACTER SET utf8 DEFAULT '1',
|
|
`category_id` smallint(11) DEFAULT NULL,
|
|
`receipts` varchar(45) COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`changed_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
|
|
`version` int(2) NOT NULL DEFAULT '0',
|
|
`images` varchar(30) CHARACTER SET utf8 DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `group_expenses`
|
|
--
|
|
|
|
CREATE TABLE `group_expenses` (
|
|
`id` varchar(40) CHARACTER SET utf8 NOT NULL,
|
|
`expense_id` bigint(20) UNSIGNED NOT NULL,
|
|
`expense_type` char(1) CHARACTER SET utf8 DEFAULT NULL,
|
|
`what` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
|
|
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`expense_date` int(13) DEFAULT NULL,
|
|
`expense_date_new` varchar(11) COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`update_date` int(13) DEFAULT NULL,
|
|
`number_of_notes` int(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
|
|
`amount` double DEFAULT NULL,
|
|
`paid_by_participant_id` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
|
|
`is_reimbursement` char(1) CHARACTER SET utf8 DEFAULT NULL,
|
|
`participant_amount` text CHARACTER SET utf8 NOT NULL,
|
|
`isDetails` tinyint(1) DEFAULT '0',
|
|
`syn_add_unique_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
|
|
`type` char(1) CHARACTER SET utf8 DEFAULT '1',
|
|
`extras` varchar(5) COLLATE utf16_unicode_ci DEFAULT '',
|
|
`images` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
|
|
`category_id` smallint(11) DEFAULT NULL,
|
|
`custom_categories` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
|
|
`receipts` tinyint(2) DEFAULT NULL,
|
|
`changed_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
|
|
`version` int(2) NOT NULL DEFAULT '0'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `group_settings`
|
|
--
|
|
|
|
CREATE TABLE `group_settings` (
|
|
`id` bigint(20) NOT NULL,
|
|
`expense_id` bigint(20) NOT NULL,
|
|
`unique_url` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
|
|
`categories` text COLLATE utf8_unicode_ci,
|
|
`version` tinyint(3) NOT NULL DEFAULT '0',
|
|
`is_restricted_mode` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=off,1=on'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `logs`
|
|
--
|
|
|
|
CREATE TABLE `logs` (
|
|
`id` int(30) NOT NULL,
|
|
`expense_id` int(30) UNSIGNED DEFAULT NULL,
|
|
`previous_desc` text CHARACTER SET utf8,
|
|
`previous_paid_by` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
|
|
`changed_by` varchar(50) COLLATE utf16_unicode_ci DEFAULT NULL,
|
|
`new_desc` text CHARACTER SET utf8,
|
|
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `meals`
|
|
--
|
|
|
|
CREATE TABLE `meals` (
|
|
`id` bigint(25) UNSIGNED NOT NULL,
|
|
`expense_id` bigint(25) UNSIGNED DEFAULT '0',
|
|
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`date` int(13) UNSIGNED DEFAULT '0',
|
|
`date_new` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
|
|
`version` int(13) UNSIGNED DEFAULT '0',
|
|
`meal_amount` text COLLATE utf8_unicode_ci,
|
|
`update_date` int(13) UNSIGNED DEFAULT '0'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `participants`
|
|
--
|
|
|
|
CREATE TABLE `participants` (
|
|
`id` int(25) UNSIGNED NOT NULL,
|
|
`participant_id` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
|
|
`expense_id` int(25) UNSIGNED NOT NULL,
|
|
`facebook_id` text CHARACTER SET utf8,
|
|
`name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
|
|
`is_creator` char(1) CHARACTER SET utf8 NOT NULL DEFAULT '0',
|
|
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`version` int(2) NOT NULL DEFAULT '0'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `permissions`
|
|
--
|
|
|
|
CREATE TABLE `permissions` (
|
|
`id` bigint(20) UNSIGNED NOT NULL,
|
|
`expense_id` bigint(20) UNSIGNED NOT NULL,
|
|
`unique_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
|
|
`user_id` bigint(20) UNSIGNED NOT NULL,
|
|
`role` tinyint(4) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
--
|
|
-- Indexes for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Indexes for table `expenses`
|
|
--
|
|
ALTER TABLE `expenses`
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
--
|
|
-- Indexes for table `expense_emails`
|
|
--
|
|
ALTER TABLE `expense_emails`
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
--
|
|
-- Indexes for table `family_expenses`
|
|
--
|
|
ALTER TABLE `family_expenses`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `expense_id` (`expense_id`);
|
|
|
|
--
|
|
-- Indexes for table `group_expenses`
|
|
--
|
|
ALTER TABLE `group_expenses`
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
--
|
|
-- Indexes for table `group_settings`
|
|
--
|
|
ALTER TABLE `group_settings`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `unique_url` (`unique_url`),
|
|
ADD UNIQUE KEY `expense_id` (`expense_id`);
|
|
|
|
--
|
|
-- Indexes for table `logs`
|
|
--
|
|
ALTER TABLE `logs`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `create_date` (`create_date`);
|
|
|
|
--
|
|
-- Indexes for table `meals`
|
|
--
|
|
ALTER TABLE `meals`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `expense_id` (`expense_id`),
|
|
ADD KEY `date` (`date`);
|
|
|
|
--
|
|
-- Indexes for table `participants`
|
|
--
|
|
ALTER TABLE `participants`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `expense_id` (`expense_id`);
|
|
|
|
--
|
|
-- Indexes for table `permissions`
|
|
--
|
|
ALTER TABLE `permissions`
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
--
|
|
-- AUTO_INCREMENT for dumped tables
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `expenses`
|
|
--
|
|
ALTER TABLE `expenses`
|
|
MODIFY `id` bigint(25) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=897648;
|
|
--
|
|
-- AUTO_INCREMENT for table `expense_emails`
|
|
--
|
|
ALTER TABLE `expense_emails`
|
|
MODIFY `id` bigint(25) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9455;
|
|
--
|
|
-- AUTO_INCREMENT for table `group_settings`
|
|
--
|
|
ALTER TABLE `group_settings`
|
|
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=856;
|
|
--
|
|
-- AUTO_INCREMENT for table `logs`
|
|
--
|
|
ALTER TABLE `logs`
|
|
MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26795;
|
|
--
|
|
-- AUTO_INCREMENT for table `meals`
|
|
--
|
|
ALTER TABLE `meals`
|
|
MODIFY `id` bigint(25) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3325;
|
|
--
|
|
-- AUTO_INCREMENT for table `participants`
|
|
--
|
|
ALTER TABLE `participants`
|
|
MODIFY `id` int(25) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44905;
|
|
--
|
|
-- AUTO_INCREMENT for table `permissions`
|
|
--
|
|
ALTER TABLE `permissions`
|
|
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1521;
|
|
|