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.
62 lines
2.6 KiB
62 lines
2.6 KiB
CREATE TABLE `card_images` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`card_id` integer NOT NULL,
|
|
`url` text(500) NOT NULL,
|
|
`sort_order` integer DEFAULT 0 NOT NULL,
|
|
FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_card_image_card` ON `card_images` (`card_id`);--> statement-breakpoint
|
|
CREATE TABLE `card_tags` (
|
|
`card_id` integer NOT NULL,
|
|
`tag_id` integer NOT NULL,
|
|
PRIMARY KEY(`card_id`, `tag_id`),
|
|
FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_card_tag_card` ON `card_tags` (`card_id`);--> statement-breakpoint
|
|
CREATE INDEX `idx_card_tag_tag` ON `card_tags` (`tag_id`);--> statement-breakpoint
|
|
CREATE TABLE `cards` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`type` text NOT NULL,
|
|
`title` text(255) NOT NULL,
|
|
`description` text,
|
|
`aspect_ratio` real,
|
|
`category_id` text,
|
|
`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_card_category` ON `cards` (`category_id`);--> statement-breakpoint
|
|
CREATE INDEX `idx_card_type` ON `cards` (`type`);--> statement-breakpoint
|
|
CREATE INDEX `idx_card_created` ON `cards` (`created_at`);--> statement-breakpoint
|
|
CREATE TABLE `categories` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`name` text(100) NOT NULL,
|
|
`slug` text(100) NOT NULL,
|
|
`image` text(500),
|
|
`parent_id` text,
|
|
`sort_order` integer DEFAULT 0 NOT NULL,
|
|
`count` integer DEFAULT 0 NOT NULL,
|
|
`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_category_slug` ON `categories` (`slug`);--> statement-breakpoint
|
|
CREATE INDEX `idx_category_parent` ON `categories` (`parent_id`);--> statement-breakpoint
|
|
CREATE TABLE `tags` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text(50) NOT NULL,
|
|
`slug` text(50) NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_tag_slug` ON `tags` (`slug`);--> statement-breakpoint
|
|
CREATE TABLE `tools` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`name` text(50) NOT NULL,
|
|
`slug` text(50) NOT NULL,
|
|
`icon` text(100),
|
|
`sort_order` integer DEFAULT 0 NOT NULL
|
|
);
|
|
|