จากการที่สามารถสลับตารางแนวนอนและตารางแนวตั้งได้แล้ว จะจำลองการ insert ลง table ใน database
table_Matrix_ reshaping_SQL_script_generator.html
<!DOCTYPE html>
<html lang="th">
<head> <meta charset="UTF-8"> <title>RBAC Matrix Reshaper & SQL Export</title> <style> body { font-family: 'Chakra Petch', sans-serif; padding: 20px; background-color: #f4f7f6; } .container { max-width: 1200px; margin: auto; background: white; padding: 25px; border-radius: 15px; box-shadow: 0 10px 30px rgba (0,0,0,0.1) ; } .panel { background: #2c3e50; color: white; padding: 20px; border-radius: 10px; margin-bottom: 20px; display: flex; gap: 20px; flex-wrap: wrap; align-items: flex-end; } .input-group { display: flex; flex-direction: column; gap: 5px; } input { padding: 8px; border-radius: 4px; border: 1px solid #ddd; } button { padding: 12px 25px; cursor: pointer; border: none; border-radius: 5px; font-weight: bold; transition: 0.2s; } .btn-execute { background: #27ae60; color: white; } .btn-sql { background: #f39c12; color: white; } button:hover { filter: brightness (1.2) ; } table { border-collapse: collapse; width: 100%; margin-top: 15px; font-size: 11px; } th { background: #ecf0f1; padding: 8px; border: 1px solid #bdc3c7; } td { border: 1px solid #dee2e6; padding: 6px; text-align: center; background: white; } .static-col { background: #f8f9fa !important; font-weight: bold; color: #2980b9; } .scroll-box { overflow-x: auto; max-height: 250px; border: 1px solid #ddd; margin-top: 10px; } #sqlOutput { width: 100%; height: 180px; margin-top: 15px; font-family: monospace; font-size: 13px; background: #1e1e1e; color: #dcdcdc; padding: 15px; border-radius: 5px; } </style>
</head>
<body> <div class="container"> <h2>Database Matrix Reshaper & SQL Export</h2> <div class="panel"> <div class="input-group"> <label>1. Static Columns</label> <input type="text" id="staticCols" value="id, program_id" oninput="initSource () "> </div> <div class="input-group"> <label>2. Source (R x P_Cols) </label> <div style="display:flex; gap:5px;"> <input type="number" id="sRow" value="3" style="width: 50px;" oninput="initSource () "> x <input type="number" id="sCol" value="24" style="width: 50px;" oninput="initSource () "> </div> </div> <div class="input-group"> <label>3. Target (R x New_Cols) </label> <div style="display:flex; gap:5px;"> <input type="number" id="tRow" value="72" style="width: 60px;"> x <input type="number" id="tCol" value="1" style="width: 60px;"> </div> </div> <button class="btn-execute" onclick="runReshape () ">Execute Reshape</button> <button class="btn-sql" onclick="generateSQL () ">Generate SQL Script</button> </div> <h4>Source Table (Raw Data) :</h4> <div class="scroll-box"><table id="sourceTable"></table></div> <h4>Target Table (Result) :</h4> <div class="scroll-box"><table id="targetTable"></table></div> <h4>SQL Script (INSERT Only) :</h4> <textarea id="sqlOutput" readonly placeholder="กดปุ่มเพื่อสร้างสคริปต์ SQL..."></textarea>
</div> <script> let sourceData = []; let finalResult = []; function initSource () { const rows = parseInt (document.getElementById ('sRow') .value) || 0; const cols = parseInt (document.getElementById ('sCol') .value) || 0; const staticNames = document.getElementById ('staticCols') .value.split (',') .map (s => s.trim ()) .filter (s => s) ; const table = document.getElementById ('sourceTable') ; sourceData = []; let html = "<thead><tr>"; staticNames.forEach (name => html += `<th class="static-col">${name}</th>`) ; for (let i=1; i<=cols; i++) html += `<th>P_${i}</th>`; html += "</tr></thead><tbody>"; for (let i=0; i<rows; i++) { let rowObj = { static: {}, perms: [] }; html += "<tr>"; staticNames.forEach (name => { const val = `${name}_${i+1}`; html += `<td class="static-col">${val}</td>`; rowObj.static[name] = val; }) ; for (let j=1; j<=cols; j++) { const pVal = `P_${i+1}_${j}`; html += `<td>${pVal}</td>`; rowObj.perms.push (pVal) ; } html += "</tr>"; sourceData.push (rowObj) ; } table.innerHTML = html + "</tbody>"; } function runReshape () { const tR = parseInt (document.getElementById ('tRow') .value) || 1; const tC = parseInt (document.getElementById ('tCol') .value) || 1; const staticNames = document.getElementById ('staticCols') .value.split (',') .map (s => s.trim ()) .filter (s => s) ; finalResult = []; let flatItems = []; sourceData.forEach (row => { row.perms.forEach (p => flatItems.push ({ pVal: p, static: row.static })) ; }) ; let html = "<thead><tr>"; staticNames.forEach (name => html += `<th>${name}</th>`) ; for (let i=1; i<=tC; i++) html += `<th>New_P_${i}</th>`; html += "</tr></thead><tbody>"; let k = 0; for (let r=0; r<tR; r++) { if (k >= flatItems.length) break; let rowData = { static: flatItems[k].static, permissions: [] }; html += "<tr>"; staticNames.forEach (name => html += `<td class="static-col">${rowData.static[name]}</td>`) ; for (let c=0; c<tC; c++) { if (k < flatItems.length) { html += `<td style="background:#d4edda">${flatItems[k].pVal}</td>`; rowData.permissions.push (flatItems[k].pVal) ; k++; } } html += "</tr>"; finalResult.push (rowData) ; } document.getElementById ('targetTable') .innerHTML = html + "</tbody>"; } function generateSQL () { if (finalResult.length === 0) { runReshape () ; } const staticNames = document.getElementById ('staticCols') .value.split (',') .map (s => s.trim ()) .filter (s => s) ; const tC = finalResult[0].permissions.length; let sql = "-- SQL Script Export\nINSERT INTO [Target_Table] (" + staticNames.join (", ") ; for (let i=1; i<=tC; i++) sql += `, New_Perm_${i}`; sql += ") VALUES \n"; const values = finalResult.map (row => { let rowValues = staticNames.map (name => `'${row.static[name]}'`) ; row.permissions.forEach (p => rowValues.push (`'${p}'`)) ; return ` (${rowValues.join (", ") }) `; }) ; sql += values.join (",\n") + ";"; document.getElementById ('sqlOutput') .value = sql; } window.onload = initSource;
</script>
</body>
</html>
จะเห็นได้ว่าสามารถทำให้ insert data ลงใน table ได้จริง ๆ
แต่ไม่ควรทำแบบนี้เพราะ ไม่ควรมาสร้าง SQL ที่เกี่ยวกับการ insert, update, delete ในฝั่ง client โดยเฉพาะ query เต็ม ๆ ที่สามารถ run ได้จริง ๆ ใช้เพื่อการ DEMO เท่านั้น
อ่านเพิ่มเติม