1/* 2 * Copyright (C) 2022 Huawei Device Co., Ltd. 3 * Licensed under the Apache License, Version 2.0 (the "License"); 4 * you may not use this file except in compliance with the License. 5 * You may obtain a copy of the License at 6 * 7 * http://www.apache.org/licenses/LICENSE-2.0 8 * 9 * Unless required by applicable law or agreed to in writing, software 10 * distributed under the License is distributed on an "AS IS" BASIS, 11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 * See the License for the specific language governing permissions and 13 * limitations under the License. 14 */ 15 16import { BaseElement, element } from '../../base-ui/BaseElement.js'; 17import { queryCustomizeSelect } from '../database/SqlLite.js'; 18import { LitTable } from '../../base-ui/table/lit-table.js'; 19import '../../base-ui/table/lit-table.js'; 20import { LitTableColumn } from '../../base-ui/table/lit-table-column.js'; 21import { info } from '../../log/Log.js'; 22import { LitProgressBar } from '../../base-ui/progress-bar/LitProgressBar.js'; 23import { PageNation } from '../../base-ui/chart/pagenation/PageNation.js'; 24import { PaginationBox } from '../../base-ui/chart/pagenation/PaginationBox.js'; 25import { SpStatisticsHttpUtil } from '../../statistics/util/SpStatisticsHttpUtil.js'; 26 27@element('sp-query-sql') 28export class SpQuerySQL extends BaseElement { 29 private queryTableEl: LitTable | undefined; 30 private notSupportList: Array<string> | undefined = []; 31 private querySize: HTMLElement | undefined; 32 private keyList: Array<string> | undefined; 33 private selector: HTMLTextAreaElement | undefined; 34 private isSupportSql: boolean = true; 35 private response: HTMLDivElement | undefined; 36 private statDataArray: unknown[] = []; 37 private sliceData: unknown[] = []; 38 private querySqlErrorText: string = ''; 39 private progressLoad: LitProgressBar | undefined; 40 private pagination: PaginationBox | undefined; 41 42 initElements(): void { 43 this.progressLoad = this.shadowRoot?.querySelector('.load-query-sql') as LitProgressBar; 44 this.selector = this.shadowRoot?.querySelector('.sql-select') as HTMLTextAreaElement; 45 this.queryTableEl = new LitTable(); 46 this.querySize = this.shadowRoot?.querySelector('.query_size') as HTMLElement; 47 this.response = this.shadowRoot?.querySelector('#dataResult') as HTMLDivElement; 48 this.pagination = this.shadowRoot?.querySelector('.pagination-box') as PaginationBox; 49 this.notSupportList?.push('insert', 'delete', 'update', 'drop', 'alter', 'truncate', 'create'); 50 let htmlDivElement = this.queryTableEl.shadowRoot?.querySelector('.table') as HTMLDivElement; 51 htmlDivElement.style.overflowX = 'scroll'; 52 window.addEventListener('resize', () => { 53 this.freshTableHeadResizeStyle(); 54 }); 55 let copyButtonEl = this.shadowRoot?.querySelector('#copy-button') as HTMLButtonElement; 56 copyButtonEl.addEventListener('click', () => { 57 this.copyTableData(); 58 }); 59 let closeButtonEl = this.shadowRoot?.querySelector('#close-button') as HTMLButtonElement; 60 closeButtonEl.addEventListener('click', () => { 61 this.querySize!.textContent = 'Query result - 0 counts.'; 62 this.queryTableEl!.dataSource = []; 63 this.response!.innerHTML = ''; 64 }); 65 new ResizeObserver(() => { 66 if (this.parentElement?.clientHeight !== 0) { 67 this.queryTableEl!.style.height = '100%'; 68 this.queryTableEl!.reMeauseHeight(); 69 } 70 }).observe(this.parentElement!); 71 } 72 73 private freshTableHeadResizeStyle(): void { 74 let th = this.queryTableEl!.shadowRoot?.querySelector<HTMLDivElement>('.th'); 75 if (th) { 76 let td = th.querySelectorAll<HTMLDivElement>('.td'); 77 let firstChild = this.queryTableEl!.shadowRoot?.querySelector<HTMLDivElement>('.body')!.firstElementChild; 78 if (firstChild) { 79 let bodyList = firstChild.querySelectorAll<HTMLDivElement>('.td'); 80 for (let index = 0; index < bodyList.length; index++) { 81 td[index].style.width = `${bodyList[index].offsetWidth}px`; 82 td[index].style.overflow = 'hidden'; 83 } 84 } 85 } 86 let tableHeadStyle: HTMLDivElement | undefined | null = this.queryTableEl?.shadowRoot?.querySelector( 87 'div.th' 88 ) as HTMLDivElement; 89 if (tableHeadStyle && tableHeadStyle.hasChildNodes()) { 90 for (let index = 0; index < tableHeadStyle.children.length; index++) { 91 // @ts-ignore 92 tableHeadStyle.children[index].style.gridArea = null; 93 } 94 } 95 this.queryTableEl!.style.height = '100%'; 96 } 97 98 private async copyTableData(): Promise<void> { 99 let copyResult = ''; 100 for (let keyListKey of this.keyList!) { 101 copyResult += `${keyListKey}\t`; 102 } 103 copyResult += '\n'; 104 let copyData: unknown[]; 105 if (this.statDataArray.length > maxPageSize) { 106 copyData = this.sliceData; 107 } else { 108 copyData = this.statDataArray; 109 } 110 for (const value of copyData) { 111 this.keyList?.forEach((key) => { 112 // @ts-ignore 113 copyResult += `${value[key]}\t`; 114 }); 115 copyResult += '\n'; 116 } 117 await navigator.clipboard.writeText(copyResult); 118 } 119 120 selectEventListener = (event: KeyboardEvent): void => { 121 let enterKey = 13; 122 if (event.ctrlKey && event.keyCode === enterKey) { 123 SpStatisticsHttpUtil.addOrdinaryVisitAction({ 124 event: 'query', 125 action: 'query', 126 }); 127 this.statDataArray = []; 128 this.keyList = []; 129 this.response!.innerHTML = ''; 130 this.queryTableEl!.innerHTML = ''; 131 if (this.isSupportSql) { 132 this.progressLoad!.loading = true; 133 queryCustomizeSelect(this.selector!.value).then((resultList): void => { 134 if (resultList && resultList.length > 0) { 135 this.statDataArray = resultList; 136 this.keyList = Object.keys(resultList[0]); 137 this.querySize!.textContent = `Query result - ${this.statDataArray.length} counts.`; 138 this.initDataElement(); 139 this.response!.appendChild(this.queryTableEl!); 140 this.setPageNationTableEl(); 141 } else { 142 this.querySize!.textContent = `Query result - ${this.statDataArray.length} counts.`; 143 this.progressLoad!.loading = false; 144 } 145 }); 146 } else { 147 this.querySize!.textContent = this.querySqlErrorText; 148 this.queryTableEl!.dataSource = []; 149 this.response!.innerHTML = ''; 150 return; 151 } 152 } 153 }; 154 155 private setPageNationTableEl(): void { 156 let that = this; 157 let timeOutTs: number = 200; 158 let indexNumber = 1; 159 setTimeout(() => { 160 let total = this.statDataArray.length; 161 if (total > maxPageSize) { 162 that.pagination!.style.opacity = '1'; 163 new PageNation(this.pagination, { 164 current: 1, 165 total: total, 166 pageSize: pageSize, 167 change(num: number): void { 168 that.sliceData = that.statDataArray!.slice((num - indexNumber) * pageSize, num * pageSize); 169 that.queryTableEl!.recycleDataSource = that.sliceData; 170 }, 171 }); 172 } else { 173 that.pagination!.style.opacity = '0'; 174 this.queryTableEl!.recycleDataSource = this.statDataArray; 175 } 176 this.freshTableHeadResizeStyle(); 177 this.progressLoad!.loading = false; 178 }, timeOutTs); 179 } 180 181 reset(): void { 182 this.pagination!.style.opacity = '0'; 183 this.response!.innerHTML = ''; 184 this.keyList = []; 185 this.statDataArray = []; 186 this.selector!.value = ''; 187 this.querySize!.textContent = 'Please enter a query.'; 188 this.resizeSqlHeight().then(); 189 } 190 191 private checkSafetySelectSql(): boolean { 192 if (this.selector?.value.trim() === '') { 193 this.querySqlErrorText = 'Please enter a query.'; 194 this.querySize!.textContent = this.querySqlErrorText; 195 return false; 196 } else { 197 let queryNormalLength = 15; 198 if (this.selector!.value.length < queryNormalLength || 199 !this.selector?.value.toLowerCase().trim().startsWith('select') 200 ) { 201 this.querySqlErrorText = `Query result - (Error): 202 ${this.selector!.value}.`; 203 return false; 204 } 205 if (this.notSupportList && this.notSupportList.length > 0) { 206 for (let index = 0; index < this.notSupportList.length; index++) { 207 let regexStr = new RegExp(this.notSupportList[index], 'i'); 208 if (regexStr.test(this.selector!.value)) { 209 this.querySqlErrorText = `Query result - (Error): 210 ${this.selector!.value}.`; 211 return false; 212 } 213 } 214 } 215 } 216 return true; 217 } 218 219 private initDataElement(): void { 220 if (this.keyList) { 221 info('Metric query Table Colum size is: ', this.keyList.length); 222 this.keyList.forEach((item) => { 223 let htmlElement = document.createElement('lit-table-column') as LitTableColumn; 224 htmlElement.setAttribute('title', item); 225 htmlElement.setAttribute('data-index', item); 226 htmlElement.setAttribute('key', item); 227 htmlElement.setAttribute('align', 'flex-start'); 228 htmlElement.setAttribute('height', '32px'); 229 this.queryTableEl!.appendChild(htmlElement); 230 }); 231 } 232 } 233 234 connectedCallback(): void { 235 // Listen to the sql execution of the query 236 this.addEventListener('keydown', this.selectEventListener); 237 this.selector!.addEventListener('input', this.inputSqlListener); 238 this.selector!.addEventListener('change', this.inputSqlListener); 239 this.selector!.addEventListener('keydown', this.deleteSqlListener); 240 } 241 242 private deleteSqlListener = (event: KeyboardEvent): void => { 243 if (event.key === 'Backspace') { 244 this.resizeSqlHeight().then(); 245 this.isSupportSql = this.checkSafetySelectSql(); 246 } 247 }; 248 249 private async resizeSqlHeight(): Promise<void> { 250 let minRowNumber = 10; 251 let indexNumber = 1; 252 let multipleNumber = 1.2; 253 let paddingNumber = 2; 254 let valueLength = this.selector?.value.split('\n').length; 255 let rowNumber = Number(valueLength) - indexNumber; 256 let selectHeight = '3.2em'; 257 if (rowNumber > 0) { 258 if (rowNumber <= minRowNumber) { 259 let allLength = multipleNumber * rowNumber + paddingNumber; 260 selectHeight = `${allLength}em`; 261 } else { 262 selectHeight = '14em'; 263 } 264 } 265 // @ts-ignore 266 this.selector?.style.height = selectHeight; 267 } 268 269 private inputSqlListener = async (): Promise<void> => { 270 this.resizeSqlHeight().then(); 271 this.isSupportSql = this.checkSafetySelectSql(); 272 }; 273 274 disconnectedCallback(): void { 275 this.removeEventListener('keydown', this.selectEventListener); 276 this.selector!.removeEventListener('input', this.inputSqlListener); 277 this.selector!.removeEventListener('change', this.inputSqlListener); 278 this.selector!.removeEventListener('keydown', this.deleteSqlListener); 279 } 280 281 attributeChangedCallback(name: string, oldValue: string, newValue: string): void { 282 let queryDataSty: HTMLDivElement | undefined | null = this.queryTableEl?.shadowRoot?.querySelector( 283 'div.tbody' 284 ) as HTMLDivElement; 285 if (queryDataSty && queryDataSty.hasChildNodes()) { 286 for (let index = 0; index < queryDataSty.children.length; index++) { 287 // @ts-ignore 288 queryDataSty.children[index].style.backgroundColor = 'var(--dark-background5,#F6F6F6)'; 289 } 290 } 291 } 292 293 initHtml(): string { 294 return ` 295 <style> 296 :host{ 297 width: 100%; 298 height: 100%; 299 font-size: 16px; 300 background-color: var(--dark-background5,#F6F6F6); 301 margin: 0; 302 padding: 0; 303 } 304 .sql-select{ 305 box-sizing: border-box; 306 width: 95%; 307 font-family: Helvetica,serif; 308 font-size: inherit; 309 color: var(--dark-color1,#212121); 310 text-align: left; 311 line-height: 1.2em; 312 font-weight: 400; 313 height: 3.2em; 314 margin-left: 10px; 315 resize: vertical; 316 border-width: 2px; 317 } 318 .query{ 319 display: flex; 320 flex-direction: column; 321 background-color: var(--dark-background5,#F6F6F6); 322 position: absolute; 323 top: 0; 324 bottom: 0; 325 left: 0; 326 right: 0; 327 } 328 .query-message{ 329 background-color: var(--dark-background3,#FFFFFF); 330 padding: 1% 2%; 331 margin: 2% 2.5% 0 2.5%; 332 border-radius: 16px; 333 width: 90%; 334 } 335 .request{ 336 display: flex; 337 flex-direction: column; 338 position: relative; 339 } 340 .response{ 341 flex-grow: 1; 342 margin-bottom: 1%; 343 display: flex; 344 flex-direction: column; 345 min-height: inherit; 346 max-height: 70vh; 347 } 348 #dataResult{ 349 flex-grow: 1; 350 overflow-y: auto; 351 overflow-x: visible; 352 margin-bottom: 1%; 353 border-radius: 16px; 354 } 355 p{ 356 display: table-cell; 357 padding: 7px 10px; 358 font-size:0.875em; 359 line-height: 20px; 360 font-weight: 400; 361 text-align: left; 362 } 363 #response-json{ 364 margin-top: 20px; 365 background-color: var(--dark-background5,#F6F6F6); 366 margin-left: 10px; 367 flex-grow: 1; 368 scroll-y: visible; 369 } 370 .sql-select{ 371 background-color: var(--dark-background5, #F6F6F6); 372 } 373 ::-webkit-scrollbar{ 374 width: 8px; 375 background-color: var(--dark-background3,#FFFFFF); 376 } 377 ::-webkit-scrollbar-thumb{ 378 border-radius: 6px; 379 background-color: var(--dark-background7,rgba(0,0,0,0.1)); 380 } 381 .load-query-sql{ 382 width: 95%; 383 bottom: 0; 384 } 385 #copy-button{ 386 margin-right: 10%; 387 cursor:pointer; 388 opacity: 0.6; 389 } 390 #close-button{ 391 margin-right: 5%; 392 cursor:pointer; 393 opacity: 0.6; 394 } 395 .button-option{ 396 border-radius: 15px; 397 background-color: #0A59F7; 398 width: 120px; 399 height: 25px; 400 font-family: Helvetica-Bold; 401 color: var(--dark-background3,#FFFFFF); 402 text-align: center; 403 line-height: 20px; 404 font-weight: 400; 405 border:0 solid; 406 } 407 .pagination-box { 408 opacity: 0; 409 } 410 </style> 411 <div class="query"> 412 <div class="query-message request"> 413 <p class="query_select" style="color: #999999">Enter query and press cmd/ctrl + Enter</p> 414 <textarea class="sql-select"></textarea> 415 <lit-progress-bar class="load-query-sql"></lit-progress-bar> 416 </div> 417 <div class="query-message response"> 418 <div style="display: flex;justify-content: space-between"> 419 <p class="query_size" style="color: #999999">Query result - 0 counts</p> 420 <div style="display: flex; align-items: center"> 421 <button id="copy-button" class="button-option">Copy as.tsv</button> 422 <button id="close-button" class="button-option">Close</button> 423 </div> 424 </div> 425 <div id="dataResult"></div> 426 <pagination-box class="pagination-box"></pagination-box> 427 </div> 428 </div> 429 `; 430 } 431} 432 433const pageSize: number = 200000; 434const maxPageSize: number = 500000; 435