H5 WebSQL每日成语
有一天看到同事发的类似
这种成语填空一样的内容。
================
惟利( )视
为德( )终
质非文( )
( )追耗子
================
烂漫天( )
( )则改之,无则嘉勉
得( )之作
哀( )如潮
================
于是就去网上找成语数据库表的 SQL... 哈哈哈 很容易就找到了.
本来打算写个前后端,想了想可以但没必要。
于是就... 有了这篇 WebSQL 示例
效果
WebSQL
打开数据库
openDatabase(数据库名称,版本号,描述文本,数据库大小,创建回调)
例:
var db = openDatabase("idiom-data", "1.0", "这是一个存放成语的数据库", 20 * 1024 * 1024);
新建事务
db.transaction(创建回调)
执行 SQL
tx.executeSql(SQL 语句,SQL 语句需要的参数,回调函数,出错回调函数)
源码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>每日成语</title>
<style>
body {
margin: 0;
}
.idiom-detail,
.idiom-card {
/* display: inline-block; */
position: absolute;
left: 10px;
top: 10px;
display: none;
padding: 5px 10px;
background-color: #fff;
box-shadow: 0 2px 12px 0 rgba(0, 0, 0, 0.3);
max-width: 200px;
z-index: 10;
}
.idiom-detail {
top: 0;
}
.idiom-detail>div {
pointer-events: none;
}
.title {
display: inline-block;
margin-top: 10px;
margin-left: -10px;
padding: 0 20px;
color: #fff;
background-color: skyblue;
}
.name {
font-weight: bold;
font-size: 20px;
margin-top: 4px;
}
.spell,
.content,
.derivation,
.samples {
margin-top: 2px;
font-size: 14px;
color: #888;
}
.search-card {
margin: 10px;
width: 30vw;
margin: 20vh auto;
position: relative;
}
.search-input {
box-shadow: 0 2px 12px 0 rgba(0, 0, 0, 0.3);
background-color: #fff;
padding: 10px 10px;
outline: none;
font-size: 20px;
border: none;
width: 100%;
box-sizing: border-box;
}
.search-deduct,
.search-list {
margin-top: 20px;
display: inline-block;
width: 100%;
box-shadow: 0 2px 12px 0 rgba(0, 0, 0, 0.3);
}
.search-deduct {
position: absolute;
left: calc(100% + 20px);
display: inline-block;
max-width: 200px;
}
.search-deduct .title {
margin-left: 0;
}
.search-deduct .search-item {
width: 100%;
}
.search-item {
font-size: 14px;
display: block;
padding: 5px 10px;
}
.search-list .search-item:hover {
background-color: skyblue;
font-weight: bold;
}
</style>
</head>
<body>
<div class="idiom-card">
<div class="title">每日成语</div>
<div class="name"></div>
<div class="spell"></div>
<div class="content"></div>
<div class="derivation"></div>
<div class="samples"></div>
</div>
<div class="idiom-detail">
<div class="title">成语详情</div>
<div class="name"></div>
<div class="spell"></div>
<div class="content"></div>
<div class="derivation"></div>
<div class="samples"></div>
</div>
<div class="search-card">
<input class="search-input" type="text" placeholder="有意思的搜索框~" maxlength="10">
<div class="search-deduct"></div>
<div class="search-list"></div>
</div>
<script>
var db = openDatabase('idiom-data', '1.0', '这是一个存放成语的数据库', 20 * 1024 * 1024);
/**
* 执行SQL语句 random() 函数
*/
function executeSql(tx, sql, data = []) {
return new Promise((resolve, reject) => {
tx.executeSql(sql, data, function (tx, results) {
resolve(results)
}, function (tx, err) {
console.log('错误信息: ', err, sql);
});
})
}
/**
* 新建一个数据库事务
*/
function transaction() {
return new Promise((resolve, reject) => {
db.transaction(function (tx) {
resolve(tx)
});
})
}
/**
* 读取SQL
*/
function readSQL(sqlTxt) {
return new Promise((resolve, reject) => {
let tx, sqls, count
transaction().then((res) => {
tx = res;
sqls = sqlTxt.includes('\r\n') ? sqlTxt.split('\r\n') : sqlTxt.split('\n')
console.log(sqls);
sqls = sqls.filter(a => a);
let createTable = sqls.shift()
console.log(createTable);
return executeSql(tx, createTable)
}).then((_) => {
console.log(_);
return executeSql(tx, "SELECT COUNT(*) As count FROM idiom;")
}).then((_) => {
count = _.rows[0].count
if (count === 0) {
return Promise.all(sqls.map(sql => executeSql(tx, sql)))
}
}).then((_) => {
// 返回总数量
return executeSql(tx, "SELECT COUNT(*) As count FROM idiom;")
}).then((res) => {
count = res.rows[0].count
// 加载错误
if (count < sqls.length) throw { errMsg: 'LOAD_ERROR' }
// 随机一条 每日成语
const randomID = Math.round(Math.random() * count) + 1
return executeSql(tx, `SELECT * FROM idiom Where ID=${randomID};`)
}).then((res) => {
if (res.rows[0]) {
let idiomCardDom = document.querySelector('.idiom-card')
let nameDom = document.querySelector('.idiom-card > .name')
let spellDom = document.querySelector('.idiom-card > .spell')
let contentDom = document.querySelector('.idiom-card > .content')
let derivationDom = document.querySelector('.idiom-card > .derivation')
let samplesDom = document.querySelector('.idiom-card > .samples')
let { content, derivation, name, samples, spell } = res.rows[0]
nameDom.textContent = name;
contentDom.textContent = content;
derivationDom.textContent = derivation;
spellDom.textContent = spell;
samplesDom.textContent = samples;
idiomCardDom.setAttribute('style', 'display: inline-block;')
}
}).catch(reject)
})
}
// 加载SQL文件
function loadSQL() {
fetch("./assets/idiom.sql", {
"referrerPolicy": "strict-origin-when-cross-origin",
"method": "GET",
"mode": "cors",
"credentials": "omit"
}).then((res) => {
return res.text()
}).then((res) => {
return readSQL(res)
}).catch((err) => {
console.log(err);
if (err.errMsg === 'LOAD_ERROR') {
// 移除表
transaction().then((tx) => executeSql(tx, 'DROP TABLE idiom;')).then(console.log)
console.log('加载错误10s后重新加载...');
setTimeout(loadSQL, 10000);
}
})
};
loadSQL()
let searchInput = document.querySelector('.search-input')
let timeoutId = null
let lastText = ''
searchInput.addEventListener('input', (ev) => {
// 节流
if (timeoutId) clearTimeout(timeoutId)
timeoutId = setTimeout(() => {
find(ev)
clearTimeout(timeoutId)
timeoutId = null
}, 100);
})
/**
* 获取元素偏移位置
* @param {HTMLDivElement} ele
*/
function getOffset(ele, offset) {
if (ele.tagName === 'HTML') return offset
if (offset) {
offset.offsetLeft += ele.offsetLeft
offset.offsetTop += ele.offsetTop
}
else {
offset = {
offsetLeft: ele.offsetLeft,
offsetTop: ele.offsetTop,
}
}
return getOffset(ele.parentElement, offset)
}
function find(ev) {
let searchText = ev.target.value;
let searchZH = searchText.match(/[\u4e00-\u9fa5]/g)
searchZH = searchZH && searchZH.join(',').split(',')
// 避免重复搜索
if (!searchZH || lastText === searchZH.join('')) return
lastText = searchZH.join('')
let searchListDom = document.querySelector('.search-list')
let searchDeductDom = document.querySelector('.search-deduct')
if (searchZH && searchZH.length) {
db.transaction(function (tx) {
Promise.all([...searchZH.map(char => executeSql(tx, `SELECT * FROM idiom WHERE name LIKE '%${char}%';`)), executeSql(tx, `SELECT * FROM idiom WHERE name LIKE '%${searchZH.join('')}%';`)]).then((res) => {
let dist = {}
res.map(({ rows }) => {
Object.values(rows).map((row) => {
if (!dist[row.ID]) dist[row.ID] = row
})
})
console.log(dist);
// 匹配搜索文字的正则
let matchReg = new RegExp(`[${searchZH.join('')}]`, 'g')
let searchList = Object.values(dist).sort((a, b) => b.name.match(matchReg).length - a.name.match(matchReg).length)
// 清除之前残余
searchListDom.innerHTML = ''
searchDeductDom.innerHTML = ''
// 一刀下去只留前十个
searchList.slice(0, 10).forEach((item) => {
let searchItemDom = document.createElement('div')
searchItemDom.className = 'search-item'
searchItemDom.textContent = item.name
// 详情浮框
searchItemDom.addEventListener('mouseenter', function (ev) {
let idiomDetailDom = document.querySelector('.idiom-detail')
let nameDom = document.querySelector('.idiom-detail > .name')
let spellDom = document.querySelector('.idiom-detail > .spell')
let contentDom = document.querySelector('.idiom-detail > .content')
let derivationDom = document.querySelector('.idiom-detail > .derivation')
let samplesDom = document.querySelector('.idiom-detail > .samples')
let { content, derivation, name, samples, spell } = item
nameDom.textContent = name;
contentDom.textContent = content;
derivationDom.textContent = derivation;
spellDom.textContent = spell;
samplesDom.textContent = samples;
let { offsetLeft, offsetTop } = getOffset(ev.target)
// 不知道哪里样式出问题了
let tmpOffsetTop = 35
idiomDetailDom.setAttribute('style', `display: inline-block;left:${offsetLeft + ev.target.offsetWidth - 220}px;top:${offsetTop - ev.target.offsetHeight - tmpOffsetTop}px`)
})
searchListDom.appendChild(searchItemDom)
})
// 随机产生一组挖空词组
let searchDeduct = searchZH.reduce((targ, curr, index) => {
let tmpArr = searchList.filter(s => s.name.includes(curr) && !targ[s.name])
let tmp = null;
// 增加随机
if (tmpArr.length) {
tmp = tmpArr[Math.round(Math.random() * tmpArr.length)]
}
// 唯一判断
if (tmp && !targ[tmp.name]) {
targ[tmp.name] = JSON.parse(JSON.stringify(tmp));
targ[tmp.name].name = targ[tmp.name].name.replace(curr, '( )')
targ[tmp.name].index = index
}
return targ;
}, {})
let searchDeductTitleDom = document.createElement('div')
searchDeductTitleDom.className = 'title'
searchDeductTitleDom.textContent = '缺省补全'
searchDeductDom.appendChild(searchDeductTitleDom)
Object.values(searchDeduct).sort((a, b) => a.index - b.index).forEach((item) => {
let searchItemDom = document.createElement('div')
searchItemDom.className = 'search-item'
searchItemDom.textContent = item.name
searchDeductDom.appendChild(searchItemDom)
})
})
})
}
}
document.addEventListener("mousemove", (ev) => {
if (!['idiom-detail', 'search-item'].includes(ev.target.className)) {
let idiomDetailDom = document.querySelector('.idiom-detail')
idiomDetailDom.setAttribute('style', `display: none;`)
}
})
</script>
</body>
</html>