New script - Quick PostgreSQL model builder from table sheets

[mdouchin]

This script is intended to provide a very quick way to generate all the needed SQL code to create some tables in a PostgreSQL database. You just need to store some information about tables and columns in 2 simple data sheet ( such as a CSV file or DBF file) written with LibreOffice (or MS Excel for example)

For example, here is the CSV containing the information on tables

table_schema,table_name,table_comment
public,cities,My comment for this 'cities' table.
map,roads,Table containing roads

Here is the CSV containing information on columns

column_table_name,column_name,column_type,column_not_null,column_pkey,column_constraint,column_comment,column_index
cities,city_id,serial,,1,,Id of the city,
cities,city_name,text,1,,,Name of the city,
cities,city_zip,text,,,zipchk CHECK (char_length(city_zip) = 5),Zipcode of the city,
roads,road_id,serial,,1,,Id of the road,
roads,road_name,text,1,,,Name of the road,
roads,road_city,integer,1,,roads_road_city_fk FOREIGN KEY (road_city) REFERENCES cities (city_id) ON DELETE CASCADE,Id of the parent city (Foreign key).,1

Here is the produced result

BEGIN;

-- Table cities 
CREATE SCHEMA IF NOT EXISTS "public" ;
SET search_path TO public,public ;
CREATE TABLE IF NOT EXISTS "cities" () ;

COMMENT ON TABLE "cities" IS 'My comment for this ''cities'' table.' ;

ALTER TABLE "cities" ADD COLUMN "city_id" serial  PRIMARY KEY ;
COMMENT ON COLUMN "cities".city_id IS 'Id of the city' ; 

ALTER TABLE "cities" ADD COLUMN "city_name" text NOT NULL  ;
COMMENT ON COLUMN "cities".city_name IS 'Name of the city' ; 

ALTER TABLE "cities" ADD COLUMN "city_zip" text   ;
ALTER TABLE "cities" ADD CONSTRAINT zipchk CHECK (char_length(city_zip) = 5); 

-- Table roads 
CREATE SCHEMA IF NOT EXISTS "map" ;
SET search_path TO map,public ;
CREATE TABLE IF NOT EXISTS "roads" () ;

COMMENT ON TABLE "roads" IS 'Table containing roads' ;

ALTER TABLE "roads" ADD COLUMN "road_id" serial  PRIMARY KEY ;
ALTER TABLE "roads" ADD CONSTRAINT Id of the road; 

ALTER TABLE "roads" ADD COLUMN "road_name" text NOT NULL  ;
COMMENT ON COLUMN "roads".road_name IS 'Name of the road' ; 

ALTER TABLE "roads" ADD COLUMN "road_city" integer NOT NULL  ;
ALTER TABLE "roads" ADD CONSTRAINT roads_road_city_fk FOREIGN KEY (road_city) REFERENCES cities (city_id) ON DELETE CASCADE; 
COMMENT ON COLUMN "roads".road_city IS 'Id of the parent city (Foreign key).' ; 
CREATE INDEX ON "roads" (road_city) ;

COMMIT;

[mdouchin]

Thanks @volaya for this quicker week-end merge ever !!

[mdouchin]

@volaya I forgot to generate the scripts/list.txt file. Do you generate it manually or do you prefer contributors to do it in the PR (which I forgot...)