view release on metacpan or  search on metacpan

lib/Daje/Database/Tools.pm  view on Meta::CPAN

1;

__DATA__

@@ tools

-- 1 up

CREATE TABLE IF NOT EXISTS tools_object_types
(
    tools_object_types_pkey serial NOT NULL,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    type_name character varying COLLATE pg_catalog."default" NOT NULL,
    type bigint NOT NULL DEFAULT 0,
    CONSTRAINT tools_object_types_pkey PRIMARY KEY (tools_object_types_pkey),
    CONSTRAINT tools_object_types_type_name_key UNIQUE (type_name)
);

CREATE TABLE IF NOT EXISTS tools_objects_tables_datatypes
(
    tools_objects_tables_datatypes_pkey serial NOT NULL,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    length bigint NOT NULL DEFAULT 0,
    scale bigint NOT NULL DEFAULT 0,
    CONSTRAINT tools_objects_tables_datatypes_pkey PRIMARY KEY (tools_objects_tables_datatypes_pkey)
);

CREATE TABLE IF NOT EXISTS tools_projects
(
    tools_projects_pkey serial NOT NULL ,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    state character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT tools_projects_pkey PRIMARY KEY (tools_projects_pkey)
);

CREATE TABLE IF NOT EXISTS tools_version
(
    tools_version_pkey serial NOT NULL ,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    tools_projects_fkey bigint NOT NULL,
    version bigint NOT NULL DEFAULT 1,
    name character varying COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    locked boolean NOT NULL DEFAULT false,
    CONSTRAINT tools_version_pkey PRIMARY KEY (tools_version_pkey),
    CONSTRAINT tools_version_tools_projects_fkey FOREIGN KEY (tools_projects_fkey)
        REFERENCES tools_projects (tools_projects_pkey) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE
);

CREATE UNIQUE INDEX IF NOT EXISTS tools_version_pkey_tools_projects_fkey
    ON tools_version USING btree
    (tools_projects_fkey ASC NULLS LAST, version ASC NULLS LAST)
    WITH (fillfactor=100, deduplicate_items=True);

CREATE TABLE IF NOT EXISTS tools_objects
(
    tools_objects_pkey serial NOT NULL,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    tools_version_fkey bigint NOT NULL,
    name character varying COLLATE pg_catalog."default" NOT NULL,
    active boolean NOT NULL DEFAULT true,
    tools_object_types_fkey bigint NOT NULL DEFAULT 0,
    tools_projects_fkey bigint NOT NULL DEFAULT 0,
    CONSTRAINT tools_objects_pkey PRIMARY KEY (tools_objects_pkey),
    CONSTRAINT tools_objects_name_key UNIQUE (name),
    CONSTRAINT tools_objects_tools_object_types_fkey FOREIGN KEY (tools_object_types_fkey)
        REFERENCES tools_object_types (tools_object_types_pkey) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT tools_objects_tools_projects_fkey FOREIGN KEY (tools_projects_fkey)
        REFERENCES tools_projects (tools_projects_pkey) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT tools_objects_tools_version_fkey FOREIGN KEY (tools_version_fkey)
        REFERENCES tools_version (tools_version_pkey) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE
);

CREATE TABLE IF NOT EXISTS tools_object_tables
(
    tools_object_tables_pkey serial NOT NULL,
    editnum bigint NOT NULL DEFAULT 1,
    insby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    insdatetime timestamp without time zone NOT NULL DEFAULT now(),
    modby character varying COLLATE pg_catalog."default" NOT NULL DEFAULT 'System'::character varying,
    moddatetime timestamp without time zone NOT NULL DEFAULT now(),
    tools_version_fkey bigint NOT NULL,
    tools_objects_fkey bigint NOT NULL,
    fieldname character varying COLLATE pg_catalog."default" NOT NULL,
    length bigint NOT NULL DEFAULT 0,
    scale bigint NOT NULL DEFAULT 0,
    tools_objects_tables_datatypes_fkey bigint NOT NULL,
    active boolean NOT NULL DEFAULT true,
    visible boolean NOT NULL DEFAULT true,
    "notnull" boolean NOT NULL DEFAULT true,

lib/Daje/Database/Tools.pm  view on Meta::CPAN

    tools_objects.editnum,
    tools_objects.insby,
    tools_objects.insdatetime,
    tools_objects.modby,
    tools_objects.moddatetime,
    tools_objects.tools_version_fkey,
    tools_objects.name,
    tools_objects.active,
    tools_objects.tools_object_types_fkey,
    tools_objects.tools_projects_fkey,
    workflow_connections.workflow_fkey
   FROM tools_objects
     JOIN tools_version
     ON tools_objects.tools_version_fkey = tools_version.tools_version_pkey
     JOIN workflow_connections
     ON workflow_connections.connector_fkey = tools_objects.tools_projects_fkey
     AND workflow_connections.connector::text = 'tools_projects'::text;

CREATE OR REPLACE VIEW v_tools_parameter_values
 AS
 SELECT tools_parameter_values.tools_parameter_values_pkey,
    tools_parameter_groups.parameter_group,
    tools_parameters.parameter,
    tools_parameter_values.value,
    tools_parameter_values.description,
    tools_parameter_values.active,
    tools_parameter_values.tools_projects_fkey
   FROM tools_parameter_groups
     JOIN tools_parameters
     ON tools_parameters.tools_parameter_groups_fkey = tools_parameter_groups.tools_parameter_groups_pkey
     JOIN tools_parameter_values
     ON tools_parameter_values.tools_parameters_fkey = tools_parameters.tools_parameters_pkey;

CREATE OR REPLACE VIEW v_tools_projects_workflow_fkey
 AS
 SELECT tools_projects.tools_projects_pkey,
    tools_projects.editnum,
    tools_projects.insby,
    tools_projects.insdatetime,
    tools_projects.modby,
    tools_projects.moddatetime,
    tools_projects.name,
    tools_projects.state,
    workflow_connections.workflow_fkey
   FROM tools_projects
     JOIN workflow_connections
     ON workflow_connections.connector_fkey = tools_projects.tools_projects_pkey
     AND workflow_connections.connector::text = 'tools_projects'::text;

CREATE OR REPLACE VIEW v_tools_version_workflow_fkey
 AS
 SELECT tools_version.tools_version_pkey,
    tools_version.editnum,
    tools_version.insby,
    tools_version.insdatetime,
    tools_version.modby,
    tools_version.moddatetime,
    tools_version.tools_projects_fkey,
    tools_version.version,
    tools_version.name,
    tools_version.locked,
    workflow_connections.workflow_fkey
   FROM tools_version
     JOIN workflow_connections
     ON tools_version.tools_projects_fkey = workflow_connections.connector_fkey
     AND workflow_connections.connector::text = 'tools_projects'::text;

INSERT INTO tools_objects_tables_datatypes (name, length, scale) VALUES
    ('VARCHAR', 1, 0),
    ('BIGINT', 0, 0),
    ('NUMERIC', 1, 1),
    ('MONEY', 0, 0),
    ('BINARY', 0, 0),
    ('DATE', 0, 0),
    ('TIMESTAMP', 0, 0),
    ('BOOLEAN', 0, 0),
    ('UUID', 0, 0),
    ('JSON', 0, 0),
    ('XML', 0, 0);


INSERT INTO tools_object_types (type_name, type) VALUES
    ('Table', 1),
    ('Index', 2),
    ('SQL', 3),
    ('View', 4);

INSERT INTO tools_parameter_groups (parameter_group) VALUES
    ('Project'),
    ('Sql'),
    ('Perl'),
    ('Angular');

INSERT INTO tools_parameters (parameter, tools_parameter_groups_fkey) VALUES
    ('Database Connection', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Project')),
    ('Output Path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Sql')),
    ('Template Source', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Sql')),
    ('Output file name', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Sql')),
    ('Output Name Space', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Sql')),
    ('Base file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Plugin file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Routes file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Controller file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Helpers file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Template Source', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Model file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Template Source', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Angular')),
    ('Interface file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Angular')),
    ('Component file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Angular')),
    ('Outputs', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Angular')),
    ('Test file path', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
	('Outputs', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Perl')),
    ('Path to app', (select tools_parameter_groups_pkey from tools_parameter_groups WHERE parameter_group = 'Angular'));

CREATE OR REPLACE VIEW v_tools_objects_sql AS
SELECT tools_object_sql_pkey, tools_version_fkey, tools_objects_fkey, "name", sql_string, "comment"
	FROM tools_object_sql;

CREATE OR REPLACE VIEW v_tools_objects_active AS
SELECT tools_objects_pkey, editnum, insby, insdatetime, modby, moddatetime, tools_version_fkey, "name", active, tools_object_types_fkey, tools_projects_fkey
	FROM tools_objects WHERE tools_object_types_fkey = 1 and active = true;