Repository: ploomber/jupysql-plugin (language: python)

Issue:
add more databases to the connector widget
the templates are defined here: https://github.com/ploomber/jupysql-plugin/blob/2eb8f7a43cbf9a83dd9a106ccbfa286fd7edb0ed/jupysql_plugin/widgets/db_templates.py#L10

we need to add:

- Oracle
- Microsoft SQL server
- Redshift


Discussion / hints:
Acceptance criteria:
1. Add entries for `Oracle`, `Microsoft SQL server`, `Redshift` in `db_templates.py`
2. Add test cases in `test_db_templates.py`
3. Changelog entry

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 6b6d11e8..49f3d6de 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,9 @@
 # CHANGELOG
 
+## 0.2.2
+
+* Added Oracle, Microsoft SQLServer, Redshift in DB templates (#72)
+
 ## 0.2.1
 
 * Connector widget creates parent directories if needed
@@ -7,6 +11,7 @@
 * Connector widget does not modify `.ini` file if the connection fails (#68)
 * Connector widget allows editing connections (#61)
 
+
 ## 0.2.0
 
 * Updates "Deploy Notebook" endpoint
diff --git a/jupysql_plugin/widgets/db_templates.py b/jupysql_plugin/widgets/db_templates.py
index b414c852..3633af9e 100644
--- a/jupysql_plugin/widgets/db_templates.py
+++ b/jupysql_plugin/widgets/db_templates.py
@@ -5,6 +5,9 @@
     "mysql+pymysql": "MySQL",
     "snowflake": "Snowflake",
     "mariadb": "MariaDB",
+    "oracle+oracledb": "Oracle",
+    "mssql+pyodbc": "MSSQL",
+    "redshift+redshift_connector": "Redshift",
 }
 
 CONNECTIONS_TEMPLATES = dict(
@@ -191,5 +194,116 @@
                 },
             ],
         },
+        "Oracle": {
+            "driver": "oracle+oracledb",
+            "fields": [
+                {
+                    "id": "connectionName",
+                    "label": "Connection alias",
+                    "type": "text",
+                    "default": "oracle",
+                },
+                {
+                    "id": "username",
+                    "label": "Username",
+                    "type": "text",
+                },
+                {
+                    "id": "password",
+                    "label": "Password",
+                    "type": "password",
+                },
+                {
+                    "id": "host",
+                    "label": "Host",
+                    "type": "text",
+                },
+                {
+                    "id": "database",
+                    "label": "Database",
+                    "type": "text",
+                },
+                {
+                    "id": "port",
+                    "label": "Port",
+                    "type": "number",
+                    "default": 1521,
+                },
+            ],
+        },
+        "MSSQL": {
+            "driver": "mssql+pyodbc",
+            "fields": [
+                {
+                    "id": "connectionName",
+                    "label": "Connection alias",
+                    "type": "text",
+                    "default": "mssql",
+                },
+                {
+                    "id": "username",
+                    "label": "Username",
+                    "type": "text",
+                },
+                {
+                    "id": "password",
+                    "label": "Password",
+                    "type": "password",
+                },
+                {
+                    "id": "host",
+                    "label": "Host",
+                    "type": "text",
+                },
+                {
+                    "id": "database",
+                    "label": "Database",
+                    "type": "text",
+                },
+                {
+                    "id": "port",
+                    "label": "Port",
+                    "type": "number",
+                    "default": 1433,
+                },
+            ],
+        },
+        "Redshift": {
+            "driver": "redshift+redshift_connector",
+            "fields": [
+                {
+                    "id": "connectionName",
+                    "label": "Connection alias",
+                    "type": "text",
+                    "default": "redshift",
+                },
+                {
+                    "id": "username",
+                    "label": "Username",
+                    "type": "text",
+                },
+                {
+                    "id": "password",
+                    "label": "Password",
+                    "type": "password",
+                },
+                {
+                    "id": "host",
+                    "label": "Host",
+                    "type": "text",
+                },
+                {
+                    "id": "database",
+                    "label": "Database",
+                    "type": "text",
+                },
+                {
+                    "id": "port",
+                    "label": "Port",
+                    "type": "number",
+                    "default": 5439,
+                },
+            ],
+        },
     }
 )


Test patch:
diff --git a/tests/test_db_templates.py b/tests/test_db_templates.py
index 44b3ff61..e35d19a8 100644
--- a/tests/test_db_templates.py
+++ b/tests/test_db_templates.py
@@ -65,7 +65,128 @@
                 ],
             },
         ),
+        (
+            "Oracle",
+            {
+                "driver": "oracle+oracledb",
+                "fields": [
+                    {
+                        "id": "connectionName",
+                        "label": "Connection alias",
+                        "type": "text",
+                        "default": "oracle",
+                    },
+                    {
+                        "id": "username",
+                        "label": "Username",
+                        "type": "text",
+                    },
+                    {
+                        "id": "password",
+                        "label": "Password",
+                        "type": "password",
+                    },
+                    {
+                        "id": "host",
+                        "label": "Host",
+                        "type": "text",
+                    },
+                    {
+                        "id": "database",
+                        "label": "Database",
+                        "type": "text",
+                    },
+                    {
+                        "id": "port",
+                        "label": "Port",
+                        "type": "number",
+                        "default": 1521,
+                    },
+                ],
+            },
+        ),
+        (
+            "MSSQL",
+            {
+                "driver": "mssql+pyodbc",
+                "fields": [
+                    {
+                        "id": "connectionName",
+                        "label": "Connection alias",
+                        "type": "text",
+                        "default": "mssql",
+                    },
+                    {
+                        "id": "username",
+                        "label": "Username",
+                        "type": "text",
+                    },
+                    {
+                        "id": "password",
+                        "label": "Password",
+                        "type": "password",
+                    },
+                    {
+                        "id": "host",
+                        "label": "Host",
+                        "type": "text",
+                    },
+                    {
+                        "id": "database",
+                        "label": "Database",
+                        "type": "text",
+                    },
+                    {
+                        "id": "port",
+                        "label": "Port",
+                        "type": "number",
+                        "default": 1433,
+                    },
+                ],
+            },
+        ),
+        (
+            "Redshift",
+            {
+                "driver": "redshift+redshift_connector",
+                "fields": [
+                    {
+                        "id": "connectionName",
+                        "label": "Connection alias",
+                        "type": "text",
+                        "default": "redshift",
+                    },
+                    {
+                        "id": "username",
+                        "label": "Username",
+                        "type": "text",
+                    },
+                    {
+                        "id": "password",
+                        "label": "Password",
+                        "type": "password",
+                    },
+                    {
+                        "id": "host",
+                        "label": "Host",
+                        "type": "text",
+                    },
+                    {
+                        "id": "database",
+                        "label": "Database",
+                        "type": "text",
+                    },
+                    {
+                        "id": "port",
+                        "label": "Port",
+                        "type": "number",
+                        "default": 5439,
+                    },
+                ],
+            },
+        ),
     ],
+    ids=["duckdb", "sqlite", "postgresql", "oracle", "mssql", "redshift"],
 )
 def test_templates(key, expected):
     assert CONNECTIONS_TEMPLATES[key] == expected
diff --git a/ui-tests/tests/widget.test.ts b/ui-tests/tests/widget.test.ts
index 72445fbd..bded7d63 100644
--- a/ui-tests/tests/widget.test.ts
+++ b/ui-tests/tests/widget.test.ts
@@ -79,6 +79,106 @@ test('test create new connection', async ({ page }) => {
 
 });
 
+
+const relevantFieldsEmbeddedDatabases = [
+  { label: 'DuckDB', connectionName: 'default', database: ':memory:'},
+  { label: 'SQLite', connectionName: 'default', database: ':memory:'}
+  ]
+
+for (const { label, connectionName, database } of relevantFieldsEmbeddedDatabases) {
+    test(`test only relevant fields appear in embedded database ${label}`, async ({ page }) => {
+        await displayWidget(page);
+
+        await page.locator('#createNewConnection').click();
+        await page.locator('#selectConnection').selectOption({ label: label });
+
+        expect(await page.locator('#connectionName').evaluate(select => select.value)).toBe(connectionName);
+
+        expect(await page.locator('#database').evaluate(select => select.value)).toBe(database);
+
+        const username = page.locator('#username');
+        expect(await username.count()).toBe(0);
+
+        const password = page.locator('#password');
+        expect(await password.count()).toBe(0);
+
+        const host = page.locator('#host');
+        expect(await host.count()).toBe(0);
+
+    });
+}
+
+const fieldDefaultsWhenNoExistingConnection = [
+  { label: 'PostgreSQL', connectionName: 'default', port: '5432', username: "", password: "", host: "", database: ""},
+  { label: 'MySQL', connectionName: 'default', port: '3306', username: "", password: "", host: "", database: ""},
+  { label: 'MariaDB', connectionName: 'default', port: '3306', username: "", password: "", host: "", database: ""},
+  { label: 'Snowflake', connectionName: 'default', port: '443', username: "", password: "", host: "", database: ""},
+  { label: 'Oracle', connectionName: 'default', port: '1521', username: "", password: "", host: "", database: ""},
+  { label: 'MSSQL',connectionName: 'default',  port: '1433', username: "", password: "", host: "", database: ""},
+  { label: 'Redshift', connectionName: 'default', port: '5439', username: "", password: "", host: "", database: ""},
+
+];
+
+for (const { label, connectionName, database, port, username, password, host } of fieldDefaultsWhenNoExistingConnection) {
+    test(`test field defaults appear if there is no existing connection : ${label}`, async ({ page }) => {
+        await displayWidget(page);
+
+        await page.locator('#createNewConnection').click();
+        await page.locator('#selectConnection').selectOption({ label: label });
+
+        expect(await page.locator(`#connectionName`).evaluate(select => select.value)).toBe(connectionName);
+        expect(await page.locator(`#port`).evaluate(select => select.value)).toBe(port);
+        expect(await page.locator(`#username`).evaluate(select => select.value)).toBe(username);
+        expect(await page.locator(`#password`).evaluate(select => select.value)).toBe(password);
+        expect(await page.locator(`#host`).evaluate(select => select.value)).toBe(host);
+        expect(await page.locator(`#database`).evaluate(select => select.value)).toBe(database);
+    });
+}
+
+
+const aliasDefaultsWithExistingConnection = [
+  { label: 'DuckDB', connectionName: 'duckdb'},
+  { label: 'SQLite', connectionName: 'sqlite'},
+  { label: 'PostgreSQL', connectionName: 'postgresql'},
+  { label: 'MySQL', connectionName: 'mysql'},
+  { label: 'MariaDB', connectionName: 'mariadb'},
+  { label: 'Snowflake', connectionName: 'snowflake'},
+  { label: 'Oracle', connectionName: 'oracle'},
+  { label: 'MSSQL',connectionName: 'mssql'},
+  { label: 'Redshift', connectionName: 'redshift'}
+ ]
+
+for (const { label, connectionName } of aliasDefaultsWithExistingConnection) {
+    test(`test default connection alias appears if there is an existing connection : ${label}`, async ({ page }) => {
+            await createNewNotebook(page)
+
+            await page.notebook.enterCellEditingMode(0);
+            const cell = await page.notebook.getCell(0)
+            await cell?.type(`
+from pathlib import Path
+Path('connections.ini').write_text("""
+[first]
+drivername = sqlite
+database = :memory:
+
+[second]
+drivername = sqlite
+database = :memory:
+""")
+%load_ext sql
+%config SqlMagic.dsn_filename = 'connections.ini'
+from jupysql_plugin.widgets import ConnectorWidget
+ConnectorWidget()`)
+            await page.notebook.run()
+
+            await page.locator('#createNewConnection').click();
+            await page.locator('#selectConnection').selectOption({ label: label });
+
+            expect(await page.locator(`#connectionName`).evaluate(select => select.value)).toBe(connectionName);
+        });
+}
+
+
 test('test create new connection shows error if unable to connect', async ({ page }) => {
     await displayWidget(page);
 
@@ -106,6 +206,7 @@ test('test create new connection shows error if unable to connect', async ({ pag
 
 });
 
+
 test('test delete connection', async ({ page }) => {
     await createDefaultConnection(page);