{
runAfterPGLiteIsReady;
tableNames;
return await db.query(`
SELECT * FROM wallpaper
`);
}
{
runAfterPGLiteIsReady;
tableNames;
return await db.query(`
SELECT * FROM color
`);
}
{
runAfterPGLiteIsReady;
tableNames
return await db.query(`
SELECT * FROM tag
`);
}
/**
* Joint query
**/
{
runAfterPGLiteIsReady;
tableNames
return await db.query(`
SELECT * FROM color
INNER JOIN wallpaper ON color.uid = wallpaper.uid
WHERE color.hex='#660000'
`);
}
/**
* Joint query-2
**/
{
runAfterPGLiteIsReady;
tableNames
return await db.query(`
SELECT hex, title, level, COUNT(hex) FROM color
INNER JOIN tag ON tag.uid = color.uid
WHERE title='Art & Design'
GROUP BY color.hex, tag.title, tag.level
ORDER BY COUNT(hex) DESC
`);
}
/**
* Count the unique hex values
**/
{
runAfterPGLiteIsReady;
tableNames
return await db.query(`
SELECT hex, COUNT(hex)
FROM color
GROUP BY hex
`);
}
/**
* Count the unique tag values
**/
{
runAfterPGLiteIsReady;
tableNames;
return await db.query(`
SELECT title, level, COUNT(title)
FROM tag
GROUP BY title, level
ORDER BY level, title
`);
}
tableNames = {
try {
await initWallPaperTable();
} catch (error) {
console.error(error);
}
console.log(1);
try {
await initColorTable();
} catch (error) {
console.error(error);
}
console.log(2);
try {
await initTagTable();
} catch (error) {
console.error(error);
}
console.log(3);
var res = await allTableNames();
console.log(res);
return res;
}
await allTableNames()
async function allTableNames() {
var res = await db.query(`
SELECT relname FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relkind = 'r'
AND relname NOT LIKE 'pg_%'
`);
return res;
}
async function initTagTable() {
var { uid, tag_title, tag_id } = tag_table,
tid = tag_id,
title = tag_title;
/**
* Create the table
**/
async function createTable() {
await db.query(`
CREATE TABLE Tag(
uid VARCHAR(16)
, title VARCHAR(200)
, level INT
, PRIMARY KEY (uid, title)
)
`);
}
/**
* Insert new record
**/
async function insert(uid, title, level) {
return await db.query(`
INSERT INTO Tag(uid, title, level)
VALUES('${uid}', '${title}', ${level})
`);
}
await createTable();
var split;
for (let i in uid) {
split = safeComm(title[i])
.split(">")
.map((d) => d.trim());
for (let j = 1; j < split.length + 1; ++j) {
try {
// Automatically prevent the same top levels being repeatedly inserted
await insert(uid[i], split.slice(0, j).join(" > "), j);
} catch (error) {}
}
}
}
async function initColorTable() {
var { uid, color_hex } = color_table;
/**
* Create the table
**/
async function createTable() {
await db.query(`
CREATE TABLE Color(
uid VARCHAR(16)
, hex VARCHAR(16)
, PRIMARY KEY (uid, hex)
)
`);
}
/**
* Insert new record
**/
async function insert(uid, hex) {
return await db.query(`
INSERT INTO Color(uid, hex)
VALUES('${uid}', '${hex}')
`);
}
await createTable();
for (let i in uid) {
await insert(uid[i], color_hex[i]);
}
}
async function initWallPaperTable() {
var { uid, alt, src, width, height } = wallpaper_table;
/**
* Create the table
**/
async function createTable() {
await db.query(`
CREATE TABLE WallPaper(
uid VARCHAR(16)
, alt TEXT
, src VARCHAR(100)
, width INT
, height INT
, PRIMARY KEY (uid)
)
`);
}
/**
* Insert new record
**/
async function insert(uid, alt, src, width, height) {
return await db.query(`
INSERT INTO WallPaper(uid, alt, src, width, height)
VALUES('${uid}', '${alt}', '${src}', ${width}, ${height})
`);
}
await createTable();
for (let i in uid) {
await insert(uid[i], safeComm(alt[i]), src[i], width[i], height[i]);
}
}
safeComm = (s) => {
return s.replace("'", " ");
}
db = new PGlite()
PGlite = import ("https://cdn.jsdelivr.net/npm/@electric-sql/pglite/dist/index.js").then(d => d.PGlite)
wallpaper_table = FileAttachment("wallpaper_table.json").json()
color_table = FileAttachment("color_table.json").json()
figures_table = FileAttachment("figures_table.json").json()
tag_table = FileAttachment("tag_table.json").json()
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
