api.spaceplanner.app

Spaceplanner API
git clone git://jacobedwards.org/api.spaceplanner.app
Log | Files | Refs

2024-09-02T02:12:53.sql (2937B)

      1 BEGIN;
      2 
      3 CREATE FUNCTION spaceplanner.in (int)
      4         RETURNS bigint
      5         LANGUAGE sql
      6         STABLE
      7         RETURNS NULL ON NULL INPUT
      8         RETURN (SELECT $1 * 96);
      9 
     10 CREATE FUNCTION spaceplanner.ft (int)
     11         RETURNS bigint
     12         LANGUAGE sql
     13         STABLE
     14         RETURNS NULL ON NULL INPUT
     15         RETURN (SELECT spaceplanner.in($1) * 12);
     16 
     17 -- NOTE: A lot of these tables put a floorplan id in them
     18 -- to provide some check of correctness, but it's not enough.
     19 --   What needs to happen is first, for example with pointmaps,
     20 -- both a and b need to be verified as the same floorplan,
     21 -- and also it would be nice to eliminate floorplan ids in these
     22 -- tables that reference others (such as point or furniture maps)
     23 -- and use their references to points/furniture_defs to specify
     24 -- their floorplan
     25 
     26 -- Types of furniture
     27 -- (I don't intend to allow users to create types, who knows)
     28 -- There will probably be a varieties table with specs for
     29 -- certain varieties of furniture, such as a king bed
     30 -- Guess I'd have to consider localization too though.
     31 CREATE TABLE spaceplanner.furniture_types (
     32 	name varchar PRIMARY KEY
     33 );
     34 
     35 -- Add some basic types
     36 INSERT INTO spaceplanner.furniture_types (name)
     37 	VALUES ('bed'), ('table');
     38 
     39 CREATE TABLE spaceplanner.furniture_varieties (
     40 	name varchar PRIMARY KEY,
     41 	type varchar
     42 		REFERENCES spaceplanner.furniture_types(name)
     43 		NOT NULL,
     44 	width int NOT NULL,
     45 	depth int NOT NULL,
     46 	CONSTRAINT varieties_unique_name_within_type UNIQUE (name, type)
     47 );
     48 
     49 -- Taken from <https://en.wikipedia.org/wiki/Bed_size#North_America>
     50 INSERT INTO spaceplanner.furniture_varieties (name, type, width, depth)
     51 VALUES (
     52 	'King', 'bed', spaceplanner.in(76), spaceplanner.in(80)
     53 ), (
     54 	'Queen', 'bed', spaceplanner.in(60), spaceplanner.in(80)
     55 ), (
     56 	'Double', 'bed', spaceplanner.in(54), spaceplanner.in(75)
     57 ), (
     58 	'Twin', 'bed', spaceplanner.in(39), spaceplanner.in(75)
     59 );
     60 
     61 CREATE TABLE spaceplanner.furniture (
     62 	id bigserial PRIMARY KEY,
     63 	floorplan bigint
     64 		REFERENCES spaceplanner.floorplans(id)
     65 		ON DELETE CASCADE
     66 		NOT NULL,
     67 	-- This DOES NOT cascade
     68 	type varchar REFERENCES spaceplanner.furniture_types(name)
     69 		NOT NULL,
     70 	name varchar,
     71 	width int NOT NULL,
     72 	-- Was going to use 'height', but thought it might be confusing
     73 	depth int NOT NULL,
     74 	CONSTRAINT unique_furniture_name_within_floorplan UNIQUE(floorplan, name)
     75 );
     76 
     77 -- Furnature placements, etc.
     78 CREATE TABLE spaceplanner.furniture_maps (
     79 	id bigserial PRIMARY KEY,
     80 	floorplan bigint
     81 		REFERENCES spaceplanner.floorplans(id)
     82 		ON DELETE CASCADE
     83 		NOT NULL,
     84 	furniture_id bigint
     85 		REFERENCES spaceplanner.furniture(id)
     86 		ON DELETE CASCADE
     87 		NOT NULL,
     88 	layout varchar NOT NULL DEFAULT '1',
     89 	-- Probably degree's since I'm simple, but maybe radians?
     90 	x int NOT NULL,
     91 	y int NOT NULL,
     92 	angle int NOT NULL,
     93 	CONSTRAINT angle_is_valid_degree CHECK (angle >= 0 AND angle < 360),
     94 	CONSTRAINT no_furniture_clone UNIQUE(furniture_id, layout)
     95 );
     96 	
     97 COMMIT;