Commits

Kirill Simonov committed 9805e12

Added tests on a SQLAlchemy-generated schema.

Comments (0)

Files changed (9)

test/code/test_sqlalchemy.py

-
-from sqlalchemy import (create_engine, MetaData, Table, Column, ForeignKey,
-                        ForeignKeyConstraint, Integer, String, Text, Enum)
-
-metadata = MetaData()
-
-schema = None
-prefix = ''
-if demo.engine in ['pgsql', 'mssql']:
-    schema = 'ad'
-    prefix = schema+'.'
-
-Table('school', metadata,
-      Column('code', String(16), primary_key=True),
-      Column('name', String(64), nullable=False, unique=True),
-      Column('campus', Enum('old', 'north', 'south')),
-      schema=schema)
-
-Table('department', metadata,
-      Column('code', String(16), primary_key=True),
-      Column('name', String(64), nullable=False, unique=True),
-      Column('school_code', String(16), ForeignKey(prefix+'school.code')),
-      schema=schema)
-
-Table('program', metadata,
-      Column('school_code', String(16), ForeignKey(prefix+'school.code'),
-             primary_key=True),
-      Column('code', String(16), primary_key=True),
-      Column('title', String(64), nullable=False, unique=True),
-      Column('degree', Enum('ba', 'bs', 'ct', 'ma', 'ms', 'ph')),
-      Column('part_of_code', String(16)),
-      ForeignKeyConstraint(['school_code',
-                            'part_of_code'],
-                           [prefix+'program.school_code',
-                            prefix+'program.code']),
-      schema=schema)
-
-Table('course', metadata,
-      Column('department_code', String(16),
-             ForeignKey(prefix+'department.code'), primary_key=True),
-      Column('no', Integer, primary_key=True),
-      Column('title', String(64), nullable=False, unique=True),
-      Column('credits', Integer),
-      Column('description', Text),
-      schema=schema)
-
-uri = None
-if demo.engine == 'sqlite':
-    uri = "sqlite:///%s" % demo.database
-else:
-    uri = ""
-    if demo.host is not None:
-        uri += demo.host
-        if demo.port is not None:
-            uri += ":"+str(demo.port)
-    if demo.username is not None:
-        uri = "@"+uri
-        if demo.password is not None:
-            uri = ":"+demo.password+uri
-        uri = demo.username+uri
-    scheme = ""
-    if demo.engine == 'pgsql':
-        scheme = "postgresql"
-    elif demo.engine == 'mysql':
-        scheme = "mysql"
-    elif demo.engine == 'oracle':
-        scheme = "oracle"
-    elif demo.engine == 'mssql':
-        scheme = "mssql+pymssql"
-    uri = "%s://%s/%s" % (scheme, uri, demo.database)
-
-engine = create_engine(uri)
-metadata.bind = engine
-
-

test/code/test_sqlalchemy_demo.py

+
+from sqlalchemy import (create_engine, MetaData, Table, Column, ForeignKey,
+                        ForeignKeyConstraint, Integer, String, Text, Enum)
+
+uri = None
+if demo.engine == 'sqlite':
+    uri = "sqlite:///%s" % demo.database
+else:
+    uri = ""
+    if demo.host is not None:
+        uri += demo.host
+        if demo.port is not None:
+            uri += ":"+str(demo.port)
+    if demo.username is not None:
+        uri = "@"+uri
+        if demo.password is not None:
+            uri = ":"+demo.password+uri
+        uri = demo.username+uri
+    scheme = {
+            'pgsql': "postgresql",
+            'mssql': "mssql+pymssql",
+    }.get(demo.engine, demo.engine)
+    uri = "%s://%s/%s" % (scheme, uri, demo.database)
+
+engine = create_engine(uri)
+
+metadata = MetaData(engine)
+
+schema = None
+prefix = ''
+if demo.engine in ['pgsql', 'mssql']:
+    schema = 'ad'
+    prefix = schema+'.'
+
+Table('school', metadata,
+      Column('code', String(16), primary_key=True),
+      Column('name', String(64), nullable=False, unique=True),
+      Column('campus', Enum('old', 'north', 'south')),
+      schema=schema)
+
+Table('department', metadata,
+      Column('code', String(16), primary_key=True),
+      Column('name', String(64), nullable=False, unique=True),
+      Column('school_code', String(16), ForeignKey(prefix+'school.code')),
+      schema=schema)
+
+Table('program', metadata,
+      Column('school_code', String(16), ForeignKey(prefix+'school.code'),
+             primary_key=True),
+      Column('code', String(16), primary_key=True),
+      Column('title', String(64), nullable=False, unique=True),
+      Column('degree', Enum('ba', 'bs', 'ct', 'ma', 'ms', 'ph')),
+      Column('part_of_code', String(16)),
+      ForeignKeyConstraint(['school_code',
+                            'part_of_code'],
+                           [prefix+'program.school_code',
+                            prefix+'program.code']),
+      schema=schema)
+
+Table('course', metadata,
+      Column('department_code', String(16),
+             ForeignKey(prefix+'department.code'), primary_key=True),
+      Column('no', Integer, primary_key=True),
+      Column('title', String(64), nullable=False, unique=True),
+      Column('credits', Integer),
+      Column('description', Text),
+      schema=schema)
+
+

test/code/test_sqlalchemy_sandbox.py

+
+from sqlalchemy import (create_engine, MetaData, Table, Column, ForeignKey,
+                        Integer, String)
+
+uri = None
+if sandbox.engine == 'sqlite':
+    uri = "sqlite:///%s" % sandbox.database
+else:
+    uri = ""
+    if sandbox.host is not None:
+        uri += sandbox.host
+        if sandbox.port is not None:
+            uri += ":"+str(sandbox.port)
+    if sandbox.username is not None:
+        uri = "@"+uri
+        if sandbox.password is not None:
+            uri = ":"+sandbox.password+uri
+        uri = sandbox.username+uri
+    scheme = {
+            'pgsql': "postgresql",
+            'mssql': "mssql+pymssql",
+    }.get(sandbox.engine, sandbox.engine)
+    uri = "%s://%s/%s" % (scheme, uri, sandbox.database)
+
+engine = create_engine(uri)
+
+metadata = MetaData(engine)
+
+# Data and metadata are borrowed from SQLAlchemy documentation.
+
+users = Table('users', metadata,
+        Column('id', Integer, primary_key=True),
+        Column('name', String(50)),
+        Column('fullname', String(50)))
+
+addresses = Table('addresses', metadata,
+        Column('id', Integer, primary_key=True),
+        Column('user_id', None, ForeignKey('users.id'), nullable=False),
+        Column('email_address', String(50), nullable=False))
+
+def createdb():
+    metadata.create_all()
+    conn = engine.connect()
+    conn.execute(users.insert(), [
+        {'id': 1, 'name': 'jack', 'fullname': 'Jack Jones'},
+        {'id': 2, 'name': 'wendy', 'fullname': 'Wendy Williams'}])
+    conn.execute(addresses.insert(), [
+        {'id': 1, 'user_id': 1, 'email_address': 'jack@yahoo.com'},
+        {'id': 2, 'user_id': 1, 'email_address': 'jack@msn.com'},
+        {'id': 3, 'user_id': 2, 'email_address': 'www@www.org'},
+        {'id': 4, 'user_id': 2, 'email_address': 'wendy@aol.com'}])
+
+def dropdb():
+    metadata.drop_all()
+
+

test/input/addon.yaml

   # Addon description
   - ctl: [ext, tweak.sqlalchemy]
 
-  # Make sure `test_sqlalchemy` could be found
+  # Make sure `test_sqlalchemy_*` could be found
   - py: add-module-path
     code: |
       import __builtin__, sys, os, os.path
       path = os.path.join(os.getcwd(), "test/code")
       sys.path.insert(0, path)
       __builtin__.demo = state.saves['demo'][0]
+      __builtin__.sandbox = state.saves['sandbox'][0]
+      from test_sqlalchemy_sandbox import createdb
+      createdb()
 
-  # Initialize HTSQL using SQLAlchemy metadata
+  # Test SQLAlchemy integration with an existing schema
   - db: null
     extensions:
       tweak.sqlalchemy:
-        engine: test_sqlalchemy.engine
-        metadata: test_sqlalchemy.metadata
+        engine: test_sqlalchemy_demo.engine
+        metadata: test_sqlalchemy_demo.metadata
 
   # Check if columns are recognized
   - uri: /school{code, name, campus}?code='art'
   - uri: /(program?school_code='ns'&code='gmth').program_via_part_of{title}
   - uri: /(course?department_code='astro'&no=215).department{name}
 
+  # Test integration on SQLAlchemy-generated schema
+  - db: null
+    extensions:
+      tweak.sqlalchemy:
+        engine: test_sqlalchemy_sandbox.engine
+        metadata: test_sqlalchemy_sandbox.metadata
+
+  - uri: /users{name, fullname, count(addresses)}
+  - uri: /addresses{user.name, email_address}
+
   # Restore the original `sys.path`
   - py: remove-module-path
     code: |
+      from test_sqlalchemy_sandbox import dropdb
+      dropdb()
       import __builtin__, sys, os, os.path
       path = os.path.join(os.getcwd(), "test/code")
       sys.path.remove(path)
-      del sys.modules['test_sqlalchemy']
+      del sys.modules['test_sqlalchemy_demo']
+      del sys.modules['test_sqlalchemy_sandbox']
       del __builtin__.demo
+      del __builtin__.sandbox
 
 # TWEAK.SYSTEM - add access to system tables
 - title: tweak.system

test/output/mssql.yaml

              WHERE ([course].[department_code] = 'astro')
                    AND ([course].[no] = 215)
              ORDER BY [course].[department_code] ASC, [course].[no] ASC
+        - uri: /users{name, fullname, count(addresses)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | users                                     |
+             +-------------------------------------------+
+             | name  | fullname       | count(addresses) |
+            -+-------+----------------+------------------+-
+             | jack  | Jack Jones     |                2 |
+             | wendy | Wendy Williams |                2 |
+                                                  (2 rows)
+
+             ----
+             /users{name,fullname,count(addresses)}
+             SELECT [users].[name],
+                    [users].[fullname],
+                    COALESCE([addresses].[count], 0)
+             FROM [users]
+                  LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                          [addresses].[user_id]
+                                   FROM [addresses]
+                                   GROUP BY [addresses].[user_id]) AS [addresses]
+                                  ON ([users].[id] = [addresses].[user_id])
+             ORDER BY [users].[id] ASC
+        - uri: /addresses{user.name, email_address}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | addresses              |
+             +------------------------+
+             | name  | email_address  |
+            -+-------+----------------+-
+             | jack  | jack@yahoo.com |
+             | jack  | jack@msn.com   |
+             | wendy | www@www.org    |
+             | wendy | wendy@aol.com  |
+                               (4 rows)
+
+             ----
+             /addresses{user.name,email_address}
+             SELECT [users].[name],
+                    [addresses].[email_address]
+             FROM [addresses]
+                  INNER JOIN [users]
+                             ON ([addresses].[user_id] = [users].[id])
+             ORDER BY [addresses].[id] ASC
         - py: remove-module-path
           stdout: ''

test/output/mysql.yaml

              WHERE (`course`.`department_code` = 'astro')
                    AND (`course`.`no` = 215)
              ORDER BY `course`.`department_code` ASC, `course`.`no` ASC
+        - uri: /users{name, fullname, count(addresses)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | users                                     |
+             +-------------------------------------------+
+             | name  | fullname       | count(addresses) |
+            -+-------+----------------+------------------+-
+             | jack  | Jack Jones     |                2 |
+             | wendy | Wendy Williams |                2 |
+                                                  (2 rows)
+
+             ----
+             /users{name,fullname,count(addresses)}
+             SELECT `users`.`name`,
+                    `users`.`fullname`,
+                    COALESCE(`addresses`.`count`, 0)
+             FROM `users`
+                  LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                          `addresses`.`user_id`
+                                   FROM `addresses`
+                                   GROUP BY 2) AS `addresses`
+                                  ON (`users`.`id` = `addresses`.`user_id`)
+             ORDER BY `users`.`id` ASC
+        - uri: /addresses{user.name, email_address}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | addresses              |
+             +------------------------+
+             | name  | email_address  |
+            -+-------+----------------+-
+             | jack  | jack@yahoo.com |
+             | jack  | jack@msn.com   |
+             | wendy | www@www.org    |
+             | wendy | wendy@aol.com  |
+                               (4 rows)
+
+             ----
+             /addresses{user.name,email_address}
+             SELECT `users`.`name`,
+                    `addresses`.`email_address`
+             FROM `addresses`
+                  INNER JOIN `users`
+                             ON (`addresses`.`user_id` = `users`.`id`)
+             ORDER BY `addresses`.`id` ASC
         - py: remove-module-path
           stdout: ''

test/output/oracle.yaml

              WHERE ("COURSE"."DEPARTMENT_CODE" = 'astro')
                    AND ("COURSE"."NO" = 215)
              ORDER BY "COURSE"."DEPARTMENT_CODE" ASC, "COURSE"."NO" ASC
+        - uri: /users{name, fullname, count(addresses)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | users                                     |
+             +-------------------------------------------+
+             | name  | fullname       | count(addresses) |
+            -+-------+----------------+------------------+-
+             | jack  | Jack Jones     |                2 |
+             | wendy | Wendy Williams |                2 |
+                                                  (2 rows)
+
+             ----
+             /users{name,fullname,count(addresses)}
+             SELECT "USERS"."NAME",
+                    "USERS"."FULLNAME",
+                    COALESCE("ADDRESSES"."count", 0)
+             FROM "USERS"
+                  LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                          "ADDRESSES"."USER_ID"
+                                   FROM "ADDRESSES"
+                                   GROUP BY "ADDRESSES"."USER_ID") "ADDRESSES"
+                                  ON ("USERS"."ID" = "ADDRESSES"."USER_ID")
+             ORDER BY "USERS"."ID" ASC
+        - uri: /addresses{user.name, email_address}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | addresses              |
+             +------------------------+
+             | name  | email_address  |
+            -+-------+----------------+-
+             | jack  | jack@yahoo.com |
+             | jack  | jack@msn.com   |
+             | wendy | www@www.org    |
+             | wendy | wendy@aol.com  |
+                               (4 rows)
+
+             ----
+             /addresses{user.name,email_address}
+             SELECT "USERS"."NAME",
+                    "ADDRESSES"."EMAIL_ADDRESS"
+             FROM "ADDRESSES"
+                  INNER JOIN "USERS"
+                             ON ("ADDRESSES"."USER_ID" = "USERS"."ID")
+             ORDER BY "ADDRESSES"."ID" ASC
         - py: remove-module-path
           stdout: ''

test/output/pgsql.yaml

              WHERE ("course"."department_code" = 'astro')
                    AND ("course"."no" = 215)
              ORDER BY "course"."department_code" ASC, "course"."no" ASC
+        - uri: /users{name, fullname, count(addresses)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | users                                     |
+             +-------------------------------------------+
+             | name  | fullname       | count(addresses) |
+            -+-------+----------------+------------------+-
+             | jack  | Jack Jones     |                2 |
+             | wendy | Wendy Williams |                2 |
+                                                  (2 rows)
+
+             ----
+             /users{name,fullname,count(addresses)}
+             SELECT "users"."name",
+                    "users"."fullname",
+                    COALESCE("addresses"."count", 0)
+             FROM "users"
+                  LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                          "addresses"."user_id"
+                                   FROM "addresses"
+                                   GROUP BY 2) AS "addresses"
+                                  ON ("users"."id" = "addresses"."user_id")
+             ORDER BY "users"."id" ASC
+        - uri: /addresses{user.name, email_address}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | addresses              |
+             +------------------------+
+             | name  | email_address  |
+            -+-------+----------------+-
+             | jack  | jack@yahoo.com |
+             | jack  | jack@msn.com   |
+             | wendy | www@www.org    |
+             | wendy | wendy@aol.com  |
+                               (4 rows)
+
+             ----
+             /addresses{user.name,email_address}
+             SELECT "users"."name",
+                    "addresses"."email_address"
+             FROM "addresses"
+                  INNER JOIN "users"
+                             ON ("addresses"."user_id" = "users"."id")
+             ORDER BY "addresses"."id" ASC
         - py: remove-module-path
           stdout: ''
       - id: tweak.system

test/output/sqlite.yaml

              WHERE ("course"."department_code" = 'astro')
                    AND ("course"."no" = 215)
              ORDER BY "course"."department_code" ASC, "course"."no" ASC
+        - uri: /users{name, fullname, count(addresses)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | users                                     |
+             +-------------------------------------------+
+             | name  | fullname       | count(addresses) |
+            -+-------+----------------+------------------+-
+             | jack  | Jack Jones     |                2 |
+             | wendy | Wendy Williams |                2 |
+                                                  (2 rows)
+
+             ----
+             /users{name,fullname,count(addresses)}
+             SELECT "users"."name",
+                    "users"."fullname",
+                    COALESCE("addresses"."count", 0)
+             FROM "users"
+                  LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                          "addresses"."user_id"
+                                   FROM "addresses"
+                                   GROUP BY 2) AS "addresses"
+                                  ON ("users"."id" = "addresses"."user_id")
+             ORDER BY "users"."id" ASC
+        - uri: /addresses{user.name, email_address}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | addresses              |
+             +------------------------+
+             | name  | email_address  |
+            -+-------+----------------+-
+             | jack  | jack@yahoo.com |
+             | jack  | jack@msn.com   |
+             | wendy | www@www.org    |
+             | wendy | wendy@aol.com  |
+                               (4 rows)
+
+             ----
+             /addresses{user.name,email_address}
+             SELECT "users"."name",
+                    "addresses"."email_address"
+             FROM "addresses"
+                  INNER JOIN "users"
+                             ON ("addresses"."user_id" = "users"."id")
+             ORDER BY "addresses"."id" ASC
         - py: remove-module-path
           stdout: ''