search

oracle / quicksql

A library for generating DDL SQL and entity-relationship-diagrams from Quick SQL code
Universal Permissive License v1.0
53 stars 11 forks source link

Sample Data Model (Departments and Employees) produces bad SQL. Missing semi-colon, and bad BLOB insert. #67

Open neilfernandez opened 6 months ago

neilfernandez commented 6 months ago

Although this is uncoupled from APEX, there are a few basic data models we have preloaded. Testing, and realized that some do not work. Here is one that is broken:

departments /insert 4
   name /nn
   location
   country
   employees /insert 14
      name /nn vc50
      email /lower
      cost center num
      date hired
      job vc255

view emp_v departments employees

This is a simple departments and employees table with a view and some fake rows.

Here is the script it generates:

-- create tables

create table departments (
    id          number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                constraint departments_id_pk primary key,
    name        varchar2(255 char) not null,
    location    varchar2(4000 char),
    country     varchar2(4000 char)
);

create table employees (
    id               number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                     constraint employees_id_pk primary key,
    department_id    number                     constraint employees_department_id_fk
                     references departments,
    name             varchar2(50 char) not null,
    email            varchar2(255 char),
    cost_center      number,
    the_start        date,
    job              varchar2(255 char),
    image            blob
);

-- table index
create index employees_i1 on employees (department_id);

-- triggers
create or replace trigger employees_biu
    before insert or update
    on employees
    for each row
begin
    :new.email := lower(:new.email);
end employees_biu;
/

-- create views
create or replace view emp_v as
select
    departments.id           department_id,
    departments.name         department_name,
    departments.location     location,
    departments.country      country,
    employees.id             employee_id,
    employees.name           employee_name,
    employees.email          email,
    employees.cost_center    cost_center,
    employees.the_start      the_start,
    employees.job            job,
    employees.image          image
from
    departments,
    employees
where
    employees.department_id(+) = departments.id/

-- load data

insert into departments (
    id,
    name,
    location,
    country
) values (
    1,
    'Delivery',
    'Garukme',
    'IL'
);
insert into departments (
    id,
    name,
    location,
    country
) values (
    2,
    'Manufacturing',
    'Covdiiku',
    'MH'
);
insert into departments (
    id,
    name,
    location,
    country
) values (
    3,
    'Sales',
    'Imaerosed',
    'VU'
);
insert into departments (
    id,
    name,
    location,
    country
) values (
    4,
    'Manufacturing',
    'Cugewpap',
    'CR'
);

commit;

insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    1,
    4,
    'Elnora Payne',
    'ketbeun@sim.gf',
    84,
    sysdate-86,
    'Analyst',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    2,
    1,
    'Katie Anderson',
    'binotse@wo.ec',
    78,
    sysdate-9,
    'Architect',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    3,
    3,
    'Myrtie Maldonado',
    'zovba@uf.kz',
    7,
    sysdate-87,
    'Salesman',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    4,
    1,
    'Carrie Carlson',
    'pihaw@zamuneb.ws',
    12,
    sysdate-77,
    'Manager',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    5,
    3,
    'Lucas Larson',
    'pok@zavig.cy',
    48,
    sysdate-79,
    'Consultant',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    6,
    2,
    'Leo Vargas',
    'pes@duche.qa',
    58,
    sysdate-75,
    'Engineer',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    7,
    3,
    'Verna Greene',
    'lepfo@kozhonmi.bo',
    68,
    sysdate-62,
    'Architect',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    8,
    1,
    'Walter Hodges',
    'zi@ti.gov',
    82,
    sysdate-17,
    'Consultant',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    9,
    3,
    'Franklin Nunez',
    'ewuwip@redfecuh.sk',
    68,
    sysdate-95,
    'Consultant',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    10,
    4,
    'Seth Tran',
    'ofosid@daej.mp',
    78,
    sysdate-25,
    'Engineer',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    11,
    1,
    'Della Page',
    'toosku@bakibhi.cf',
    88,
    sysdate-73,
    'Manager',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    12,
    2,
    'Nicholas Harrison',
    'hate@fu.ve',
    25,
    sysdate-29,
    'Architect',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    13,
    2,
    'Walter Lane',
    'laum@gu.de',
    10,
    sysdate-44,
    'Manager',
    'N/A'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    the_start,
    job,
    image
) values (
    14,
    3,
    'Edgar Little',
    'nulcun@gelet.cr',
    72,
    sysdate-38,
    'Salesman',
    'N/A'
);

commit;

-- Generated by Quick SQL 1.2.12 5/3/2024, 9:38:24 AM

/*
departments /insert 4
   name /nn
   location
   country
   employees /insert 14
      name /nn vc50
      email /lower
      cost center num
      start date date
      job vc255
      image blob
view emp_v departments employees

 Non-default options:
# settings = {}

*/

You can see the that view here is missing a ; and if you try to run this whole script, it fails with Error at line 22/1: ORA-00936: missing expression

Also the inserts ALL fail with : ORA-01465: invalid hex number

This is due to the image column being a blob and trying to insert 'N/A' into it. BLOB columns rather should just get null.

vadim-tropashko commented 6 months ago

The blob issue has been already reported as #65