Using multiple databases when creating an html document with interactive sql examples?

Question

Using multiple databases when creating an html document with interactive sql examples?

shafayetShafee opened this issue a year ago · 7 comments

Hey thanks for making this awesome javascript widget to create interactive sql examples.

My question whether it is possible to use multiple databases when creating an html document with interactive sql examples? That is, would the following work,

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<pre class="demo">select * from employees limit 10;</pre>

<sqlime-db name="employees" path="./employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples>

<sqlime-db name="demo" path="./demo.db"></sqlime-db>
<sqlime-examples db="demo" selector="pre.demo" editable></sqlime-examples>

Note: both employees.sql and demo.db are taken from this repo

Answer 1 · 2023-05-16T15:28:40.000Z

Sure! You can connect as many databases as you want - just create a separate sqlime-db for each of them.

Answer 2 · 2023-05-16T17:20:33.000Z

Thanks for your prompt reply! But I am having multiple issues with creating a static html file with interactive SQL examples. So I would really appreciate your help. I am posting these problems here in this thread. But if it helps you to track the issues separately I can post separate ones.

[Problem One] Failed to load database from URL

Consider the following reprex,

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>sqlime</title>
	<script src="https://unpkg.com/@antonz/sqlite@3.40.0/dist/sqlite3.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-db.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-examples.js"></script>
</head>
<body>

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<sqlime-db name="employees" path="./employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples>

</body>
</html>

Now If I open this html file on my browser (Google Chrome), I get the following,

[Note: If you are wondering about the wiggly lines under desc it just due to Grammarly extension enable in my browser]

And I am getting these errors on the Chrome DevTools console,

Here's my folder structure,

\-- test
        demo.db
        employees.sql
        test.html

[Problem Two] Using multiple databases leading to errors

Consider the following reprex,

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>sqlime</title>
	<script src="https://unpkg.com/@antonz/sqlite@3.40.0/dist/sqlite3.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-db.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-examples.js"></script>
</head>
<body>

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<pre class="demo">select * from employees limit 10;</pre>

<sqlime-db name="employees" path="./employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples>

<sqlime-db name="demo" path="./demo.db"></sqlime-db>
<sqlime-examples db="demo" selector="pre.demo" editable></sqlime-examples>

</body>
</html>

Now If I open this html file on my browser, I get the following,

And I am getting these errors on the Chrome DevTools console,

Answer 3 · 2023-05-19T20:41:53.000Z

For JS to work, you must serve the page through a web server, not just open it from the file system.

Answer 4 · 2023-05-22T19:25:19.000Z

Ok, I get it. Pardon my stupidity. Thanks for letting me know!

Answer 5 · 2023-05-22T20:11:18.000Z

But can you help with the second problem? Using multiple databases is leading to the errors.

For example, consider the following reprex,

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>sqlime</title>
	<script src="https://unpkg.com/@antonz/sqlite@3.40.0/dist/sqlite3.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-db.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-examples.js"></script>
</head>
<body>

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<pre class="demo">select * from employees limit 10;</pre>

<sqlime-db name="employees" path="https://raw.githubusercontent.com/nalgeon/sqlime/main/employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples>

<sqlime-db name="emp2" path="https://raw.githubusercontent.com/nalgeon/sqlime/main/employees.sql"></sqlime-db>
<sqlime-examples db="emp2" selector="pre.demo" editable></sqlime-examples>

</body>
</html>

Now If I open this html file in my browser, I can run the 1st example, but getting errors in the second case,

And errors from the google chrome devtools console,

But if I comment out one of the sqlime-db, sqlime-examples commands pair, everything works fine.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>sqlime</title>
	<script src="https://unpkg.com/@antonz/sqlite@3.40.0/dist/sqlite3.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-db.js"></script>
	<script src="https://unpkg.com/sqlime@0.1.2/dist/sqlime-examples.js"></script>
</head>
<body>

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<pre class="demo">select * from employees limit 10;</pre>

<!-- <sqlime-db name="employees" path="https://raw.githubusercontent.com/nalgeon/sqlime/main/employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples> -->

<sqlime-db name="emp2" path="https://raw.githubusercontent.com/nalgeon/sqlime/main/employees.sql"></sqlime-db>
<sqlime-examples db="emp2" selector="pre.demo" editable></sqlime-examples>

</body>
</html>

Answer 6 · 2023-05-23T06:32:39.000Z

It's a bug, fixed in 9f33d67. Please try version 0.1.3.

Answer 7 · 2023-05-23T06:48:21.000Z

Woah! All work fine now.

Thanks for your prompt replies and quick help. Thank you so much.