Commits

Kirill Simonov committed f4447d5

Updated SQLite regression schema to match PostgreSQL changes.

  • Participants
  • Parent commits 5a8e54b

Comments (0)

Files changed (2)

File test/output/sqlite.yaml

         -+--------+------------------------+--------+-
          | acc    | Accounting             | bus    |
          | arthis | Art History            | art    |
-         | artstd | Studio Art             | art    |
          | astro  | Astronomy              | ns     |
          | be     | Bioengineering         | egn    |
+         | bursar | Bursar's Office        |        |
          | capmrk | Capital Markets        | bus    |
+         | career | Career Development     |        |
          | chem   | Chemistry              | ns     |
          | comp   | Computer Science       | egn    |
          | corpfi | Corporate Finance      | bus    |
          | lang   | Foreign Languages      | la     |
          | me     | Mechanical Engineering | egn    |
          | mth    | Mathematics            | ns     |
+         | parent | Parents & Alumni       |        |
          | phys   | Physics                | ns     |
          | pia    | Piano                  | mus    |
          | poli   | Political Science      | la     |
          | psych  | Psychology             | la     |
+         | stdart | Studio Art             | art    |
          | str    | Strings                | mus    |
          | tched  | Teacher Education      | edu    |
          | voc    | Vocals                 | mus    |
          | win    | Wind                   | mus    |
-                                            (24 rows)
+                                            (27 rows)
 
          ----
          /department
         -+-----------------------------------------------------------------------------------------------------+-
          | department | number | title                            | credits | description                      |
         -+------------+--------+----------------------------------+---------+----------------------------------+-
-         | acc        |    100 | Practical Bookkeeping            |       2 | A introduction to business with  |
-         :            :        :                                  :         : practical bookkeeping            :
-         :            :        :                                  :         : application.                     :
+         | acc        |    100 | Practical Bookkeeping            |       2 |                                  |
          | acc        |    200 | Principles of Accounting I       |       3 | The initial course in the theory |
          :            :        :                                  :         : and practice of financial        :
          :            :        :                                  :         : accounting. Topics emphasized    :
          :            :        : European Art                     :         : materials and manufacturing      :
          :            :        :                                  :         : techniques employed in Europe    :
          :            :        :                                  :         : over the last two centuries.     :
-         | artstd     |    119 | Spring Basket Weaving Workshop   |         | A just-for-fun chance to learn   |
-         :            :        :                                  :         : the basics of basket weaving.    :
-         | artstd     |    333 | Drawing                          |       3 | Exploration of the structure and |
-         :            :        :                                  :         : interrelationships of visual     :
-         :            :        :                                  :         : form in drawing, painting, and   :
-         :            :        :                                  :         : sculpture. Principal historical  :
-         :            :        :                                  :         : modes of drawing are examined.   :
-         | artstd     |    411 | Underwater Basket Weaving        |       4 | This course provides a novel     |
-         :            :        :                                  :         : perspective on the traditional   :
-         :            :        :                                  :         : art of basketry as it is         :
-         :            :        :                                  :         : experienced in reduced gravity   :
-         :            :        :                                  :         : and in the context of fluid      :
-         :            :        :                                  :         : dynamics. Requires instructor    :
-         :            :        :                                  :         : permission and a valid c-card.   :
-         | artstd     |    509 | Twentieth Century Printmaking    |       4 | Development of personalized      |
-         :            :        :                                  :         : concepts and individual          :
-         :            :        :                                  :         : aesthetic expression in          :
-         :            :        :                                  :         : printmaking with reference to    :
-         :            :        :                                  :         : various styles and trends in     :
-         :            :        :                                  :         : Twentieth Century printmaking.   :
-         | artstd     |    512 | Art in Therapy                   |       3 | Surveys methods and results of   |
-         :            :        :                                  :         : using art and craft therapy with :
-         :            :        :                                  :         : developmentally disabled adults. :
-         | artstd     |    614 | Drawing Master Class             |       5 | For fine arts majors only, an    |
-         :            :        :                                  :         : intensive studio study including :
-         :            :        :                                  :         : field trips to local parks and   :
-         :            :        :                                  :         : museums and a final group art    :
-         :            :        :                                  :         : show.                            :
-         | artstd     |    714 | Peer Portfolio Review            |       0 | An opportunity to practice       |
-         :            :        :                                  :         : giving and receiving             :
-         :            :        :                                  :         : constructive criticism.          :
          | astro      |    137 | The Solar System                 |       3 | Introductory survey of the solar |
          :            :        :                                  :         : system, including structure and  :
          :            :        :                                  :         : motion of the planets,           :
          :            :        :                                  :         : multi-axis machining set-up, gas :
          :            :        :                                  :         : tungsten arc welding, and gas    :
          :            :        :                                  :         : metal arch welding.              :
-         | me         |    627 | Advanced Heating and Air         |       4 | Open to juniors and seniors.     |
-         :            :        : Conditioning                     :         : Requires permission of           :
-         :            :        :                                  :         : instructor.                      :
+         | me         |    627 | Advanced Heating and Air         |       4 | ""                               |
+         :            :        : Conditioning                     :         :                                  :
          | me         |    712 | Graphic Communication and Design |       3 | Sketching and orthographic       |
          :            :        :                                  :         : projection. Covers detail and    :
          :            :        :                                  :         : assembly working drawings,       :
          :            :        :                                  :         : group, and family) will be       :
          :            :        :                                  :         : reviewed along with research on  :
          :            :        :                                  :         : their efficacy.                  :
+         | stdart     |    119 | Spring Basket Weaving Workshop   |         | A just-for-fun chance to learn   |
+         :            :        :                                  :         : the basics of basket weaving.    :
+         | stdart     |    333 | Drawing                          |       3 | Exploration of the structure and |
+         :            :        :                                  :         : interrelationships of visual     :
+         :            :        :                                  :         : form in drawing, painting, and   :
+         :            :        :                                  :         : sculpture. Principal historical  :
+         :            :        :                                  :         : modes of drawing are examined.   :
+         | stdart     |    411 | Underwater Basket Weaving        |       4 | This course provides a novel     |
+         :            :        :                                  :         : perspective on the traditional   :
+         :            :        :                                  :         : art of basketry as it is         :
+         :            :        :                                  :         : experienced in reduced gravity   :
+         :            :        :                                  :         : and in the context of fluid      :
+         :            :        :                                  :         : dynamics. Requires instructor    :
+         :            :        :                                  :         : permission and a valid c-card.   :
+         | stdart     |    509 | Twentieth Century Printmaking    |       4 | Development of personalized      |
+         :            :        :                                  :         : concepts and individual          :
+         :            :        :                                  :         : aesthetic expression in          :
+         :            :        :                                  :         : printmaking with reference to    :
+         :            :        :                                  :         : various styles and trends in     :
+         :            :        :                                  :         : Twentieth Century printmaking.   :
+         | stdart     |    512 | Art in Therapy                   |       3 | Surveys methods and results of   |
+         :            :        :                                  :         : using art and craft therapy with :
+         :            :        :                                  :         : developmentally disabled adults. :
+         | stdart     |    614 | Drawing Master Class             |       5 | For fine arts majors only, an    |
+         :            :        :                                  :         : intensive studio study including :
+         :            :        :                                  :         : field trips to local parks and   :
+         :            :        :                                  :         : museums and a final group art    :
+         :            :        :                                  :         : show.                            :
+         | stdart     |    714 | Peer Portfolio Review            |       0 | An opportunity to practice       |
+         :            :        :                                  :         : giving and receiving             :
+         :            :        :                                  :         : constructive criticism.          :
          | tched      |    122 | Theory and Practice of Early     |       3 | Emphasis on the skills and       |
          :            :        : Childhood Education              :         : processes needed for the design  :
          :            :        :                                  :         : and implementation of optimal    :
                 "department"."name",
                 "department"."school"
          FROM "department" AS "department"
-              INNER JOIN "school" AS "school"
-                         ON ("department"."school" = "school"."code")
+              LEFT OUTER JOIN "school" AS "school"
+                              ON ("department"."school" = "school"."code")
          WHERE ("school"."code" = 'ns')
          ORDER BY 1 ASC
     - uri: /program?school.code='ns'&code='uchem'
          :            :        :                      :         : and computational aspects of     :
          :            :        :                      :         : asset, liability and             :
          :            :        :                      :         : stockholders equity accounting.  :
-         | artstd     |    614 | Drawing Master Class |       5 | For fine arts majors only, an    |
-         :            :        :                      :         : intensive studio study including :
-         :            :        :                      :         : field trips to local parks and   :
-         :            :        :                      :         : museums and a final group art    :
-         :            :        :                      :         : show.                            :
          | mth        |    101 | College Algebra      |       5 | This course, taken mostly by     |
          :            :        :                      :         : members from the School of       :
          :            :        :                      :         : Business is a primary graduate   :
          :            :        :                      :         : Mathematics.  This is a 1000     :
          :            :        :                      :         : person lecture reviews basic     :
          :            :        :                      :         : concepts taught in high school.  :
+         | stdart     |    614 | Drawing Master Class |       5 | For fine arts majors only, an    |
+         :            :        :                      :         : intensive studio study including :
+         :            :        :                      :         : field trips to local parks and   :
+         :            :        :                      :         : museums and a final group art    :
+         :            :        :                      :         : show.                            :
                                                                                             (3 rows)
 
          ----
         -+-------------+----------------------------------+--------+------------------------+--------+-
          | bus         | School of Business               | acc    | Accounting             | bus    |
          | art         | School of Art and Design         | arthis | Art History            | art    |
-         | art         | School of Art and Design         | artstd | Studio Art             | art    |
          | ns          | School of Natural Sciences       | astro  | Astronomy              | ns     |
          | egn         | School of Engineering            | be     | Bioengineering         | egn    |
+         |             |                                  | bursar | Bursar's Office        |        |
          | bus         | School of Business               | capmrk | Capital Markets        | bus    |
+         |             |                                  | career | Career Development     |        |
          | ns          | School of Natural Sciences       | chem   | Chemistry              | ns     |
          | egn         | School of Engineering            | comp   | Computer Science       | egn    |
          | bus         | School of Business               | corpfi | Corporate Finance      | bus    |
          :             : Humanities                       :        :                        :        :
          | egn         | School of Engineering            | me     | Mechanical Engineering | egn    |
          | ns          | School of Natural Sciences       | mth    | Mathematics            | ns     |
+         |             |                                  | parent | Parents & Alumni       |        |
          | ns          | School of Natural Sciences       | phys   | Physics                | ns     |
          | mus         | Musical School                   | pia    | Piano                  | mus    |
          | la          | School of Arts, Letters, and the | poli   | Political Science      | la     |
          :             : Humanities                       :        :                        :        :
          | la          | School of Arts, Letters, and the | psych  | Psychology             | la     |
          :             : Humanities                       :        :                        :        :
+         | art         | School of Art and Design         | stdart | Studio Art             | art    |
          | mus         | Musical School                   | str    | Strings                | mus    |
          | edu         | College of Education             | tched  | Teacher Education      | edu    |
          | mus         | Musical School                   | voc    | Vocals                 | mus    |
          | mus         | Musical School                   | win    | Wind                   | mus    |
-                                                                                             (24 rows)
+                                                                                             (27 rows)
 
          ----
          /department{school.*,*}
                 "department"."name",
                 "department"."school"
          FROM "department" AS "department"
-              INNER JOIN "school" AS "school"
-                         ON ("department"."school" = "school"."code")
+              LEFT OUTER JOIN "school" AS "school"
+                              ON ("department"."school" = "school"."code")
          ORDER BY 3 ASC
     - uri: /department{school.name+' - '+name}
       status: 200 OK
          | School of Business - Accounting  |
          | School of Art and Design - Art   |
          : History                          :
-         | School of Art and Design -       |
-         : Studio Art                       :
          | School of Natural Sciences -     |
          : Astronomy                        :
          | School of Engineering -          |
          : Bioengineering                   :
+         | " - Bursar's Office"             |
          | School of Business - Capital     |
          : Markets                          :
+         | " - Career Development"          |
          | School of Natural Sciences -     |
          : Chemistry                        :
          | School of Engineering - Computer |
          : Mechanical Engineering           :
          | School of Natural Sciences -     |
          : Mathematics                      :
+         | " - Parents & Alumni"            |
          | School of Natural Sciences -     |
          : Physics                          :
          | Musical School - Piano           |
          : Humanities - Political Science   :
          | School of Arts, Letters, and the |
          : Humanities - Psychology          :
+         | School of Art and Design -       |
+         : Studio Art                       :
          | Musical School - Strings         |
          | College of Education - Teacher   |
          : Education                        :
          | Musical School - Vocals          |
          | Musical School - Wind            |
-                                    (24 rows)
+                                    (27 rows)
 
          ----
          /department{school.name+' - '+name}
-         SELECT (("school"."name" || ' - ') || "department"."name")
+         SELECT ((COALESCE("school"."name", '') || ' - ') || "department"."name")
          FROM "department" AS "department"
-              INNER JOIN "school" AS "school"
-                         ON ("department"."school" = "school"."code")
+              LEFT OUTER JOIN "school" AS "school"
+                              ON ("department"."school" = "school"."code")
          ORDER BY "department"."code" ASC

File test/sql/regress-sqlite.sql

 CREATE TABLE department (
     code        VARCHAR(16) NOT NULL,
     name        VARCHAR(64) NOT NULL,
-    school      VARCHAR(16) NOT NULL,
+    school      VARCHAR(16),
     CONSTRAINT department_pk
       PRIMARY KEY (code),
     CONSTRAINT department_name_uk
 INSERT INTO department (code, name, school) VALUES
     ('arthis', 'Art History', 'art');
 INSERT INTO department (code, name, school) VALUES
-    ('artstd', 'Studio Art', 'art');
+    ('stdart', 'Studio Art', 'art');
 INSERT INTO department (code, name, school) VALUES
     ('tched', 'Teacher Education', 'edu');
 INSERT INTO department (code, name, school) VALUES
     ('win', 'Wind', 'mus');
 INSERT INTO department (code, name, school) VALUES
     ('str', 'Strings', 'mus');
+-- Non-Academic Departments
+INSERT INTO department (code, name, school) VALUES
+    ('bursar', 'Bursar''s Office', NULL);
+INSERT INTO department (code, name, school) VALUES
+    ('career', 'Career Development', NULL);
+INSERT INTO department (code, name, school) VALUES
+    ('parent', 'Parents & Alumni', NULL);
 
 INSERT INTO program (school, code, title, degree) VALUES
     ('ns', 'uastro', 'Bachelor of Science in Astronomy', 'bs');
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('me', 501, 'Advanced Welding', 3, 'Advanced applications of welding and machine tool technology. Computer numerical control, multi-axis machining set-up, gas tungsten arc welding, and gas metal arch welding.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('me', 627, 'Advanced Heating and Air Conditioning', 4, 'Open to juniors and seniors. Requires permission of instructor.');
+    ('me', 627, 'Advanced Heating and Air Conditioning', 4, '');
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('me', 712, 'Graphic Communication and Design', 3, 'Sketching and orthographic projection. Covers detail and assembly working drawings, dimensioning, tolerance specification, and design projects.');
 INSERT INTO course (department, number, title, credits, description) VALUES
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('arthis', 623, 'Contemporary Latin American Art', 3, 'A survey of the last twenty years of Latin American art with a focus on the Caribbean and Central America.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 714, 'Peer Portfolio Review', 0, 'An opportunity to practice giving and receiving constructive criticism.');
+    ('stdart', 714, 'Peer Portfolio Review', 0, 'An opportunity to practice giving and receiving constructive criticism.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 411, 'Underwater Basket Weaving', 4, 'This course provides a novel perspective on the traditional art of basketry as it is experienced in reduced gravity and in the context of fluid dynamics. Requires instructor permission and a valid c-card.');
+    ('stdart', 411, 'Underwater Basket Weaving', 4, 'This course provides a novel perspective on the traditional art of basketry as it is experienced in reduced gravity and in the context of fluid dynamics. Requires instructor permission and a valid c-card.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 512, 'Art in Therapy', 3, 'Surveys methods and results of using art and craft therapy with developmentally disabled adults.');
+    ('stdart', 512, 'Art in Therapy', 3, 'Surveys methods and results of using art and craft therapy with developmentally disabled adults.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 614, 'Drawing Master Class', 5, 'For fine arts majors only, an intensive studio study including field trips to local parks and museums and a final group art show.');
+    ('stdart', 614, 'Drawing Master Class', 5, 'For fine arts majors only, an intensive studio study including field trips to local parks and museums and a final group art show.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 509, 'Twentieth Century Printmaking', 4, 'Development of personalized concepts and individual aesthetic expression in printmaking with reference to various styles and trends in Twentieth Century printmaking.');
+    ('stdart', 509, 'Twentieth Century Printmaking', 4, 'Development of personalized concepts and individual aesthetic expression in printmaking with reference to various styles and trends in Twentieth Century printmaking.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 333, 'Drawing', 3, 'Exploration of the structure and interrelationships of visual form in drawing, painting, and sculpture. Principal historical modes of drawing are examined.');
+    ('stdart', 333, 'Drawing', 3, 'Exploration of the structure and interrelationships of visual form in drawing, painting, and sculpture. Principal historical modes of drawing are examined.');
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('tched', 122, 'Theory and Practice of Early Childhood Education', 3, 'Emphasis on the skills and processes needed for the design and implementation of optimal learning environments. Exploration of issues related to societal and cultural influences on the education of young children.');
 INSERT INTO course (department, number, title, credits, description) VALUES
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('edpol', 551, 'Classroom Visit', NULL, 'Elective visit to a local classroom for observation.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('artstd', 119, 'Spring Basket Weaving Workshop', NULL, 'A just-for-fun chance to learn the basics of basket weaving.');
+    ('stdart', 119, 'Spring Basket Weaving Workshop', NULL, 'A just-for-fun chance to learn the basics of basket weaving.');
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('edpol', 313, 'Technology, Society and Schools', 3, 'Examination of theories and history of interaction of society and technology with implications for instructional technology and schooling. Resources for constructing personal definitions of technology.');
 INSERT INTO course (department, number, title, credits, description) VALUES
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('corpfi', 601, 'Case Studies in Corporate Finance', 3, 'A course designed to use case studies and financial analysis to further knowledge and ability to make financial management decisions.');
 INSERT INTO course (department, number, title, credits, description) VALUES
-    ('acc', 100, 'Practical Bookkeeping', 2, 'A introduction to business with practical bookkeeping application.');
+    ('acc', 100, 'Practical Bookkeeping', 2, NULL);
 INSERT INTO course (department, number, title, credits, description) VALUES
     ('acc', 200, 'Principles of Accounting I', 3, 'The initial course in the theory and practice of financial accounting. Topics emphasized include the preparation, reporting, and analysis of financial data.');
 INSERT INTO course (department, number, title, credits, description) VALUES