How to use knex.raw method in qawolf
Best JavaScript code snippet using qawolf
index.js
Source:index.js 
...8/////////////////////// Adds users to db if does not exist ///////////////////////9const addUser = async (username, email, password) => {10 const existingUser = await knex.select()11 .from('users')12 .where(knex.raw(`LOWER(username) = LOWER('${username}') OR LOWER(email) = LOWER('${email}')`));13 if (existingUser.length) { // checks if username or email already exists14 return 'User already exists'; // must return this exact string for passport recognition15 } else {16 return await knex('users') // insert user into the db17 .insert({18 username: username,19 email: email,20 password: password21 })22 }23}24/////////////////////// Checks user credentials ///////////////////////25const checkUserCreds = (username) => {26 return knex.select()27 .from('users')28 .where(knex.raw(`LOWER(username) = LOWER('${username}')`));29}30/////////////////////// Get user id from user table /////////////////31const getUserId = async (username, currentPlayer) => {32 if (username === 'anonymous' && currentPlayer === 'player1') {33 return [{ user_id: 1 }]; // anonymous user as player1 has a user_id in the db of 134 } else if (username === 'anonymous' && currentPlayer === 'player2') {35 return [{ user_id: 2 }]; // anonymous user as player2 has a user_id in the db of 236 } else { // user is not anon37 return await knex.select()38 .from('users')39 .where(knex.raw(`LOWER(username) = LOWER('${username}')`))40 }41}42/////////////////////// Returns current player as 'player1' or 'player2' ///////////////////////43const getUserPlayer = async (gameIndex, username) => {44 let player = await getUserId(username); // to get user id45 let game = await getGame(null, gameIndex); // to get the game46 if (game[0].player1 === player[0].user_id) { // check whether user id matches as player 1 in game47 return 'player1';48 } else if (game[0].player2 === player[0].user_id) { // or as player249 return 'player2';50 }51}52/////////////////////// Get either player1 or player2 username ///////////////////////53const getPlayerUsername = async (currentPlayer, gameIndex, room) => {54 let game = await getGame(room, gameIndex); // get current game from games table55 let playerId = game[0][currentPlayer]; // get the player id from the game56 return await knex.select()57 .from('users')58 .where(knex.raw(`${playerId} = user_id`))59}60/////////////////////// Fetches user by id ///////////////////////61const findUserById = (id) => {62 return knex('users')63 .select('user_id', 'username', 'email', 'wins', 'losses')64 .where(knex.raw(`${id} = user_id`))65}66/////////////////////// Saves new game ///////////////////////67const createGame = (room, board, gameIndex) => {68 let roomNum = room.includes('*') ? room.split('*').join('') : room;69 return knex('games')70 .insert({71 game_index: gameIndex,72 room_id: roomNum,73 player1: 1, // defaults to 1 (will be set to the actual user id after both players have joined)74 player2: 2, // defaults to 2 (will be set to the actual user id after both players have joined)75 current_player: 1 // defaults to 1 (will be set to the actual user id after both players have joined)76 })77 .returning(`game_id`)78 .then(gameId => {79 board.map(hex => {80 createHex(hex, gameId);81 });82 });83}84/////////////////////// Create board (hexes) for the new game ///////////////////////85const createHex = async (hex, gameId) => {86 let playerOnHex = await hex.player ? hex.player[hex.player.length - 1] : null;87 return await knex('hex')88 .insert({89 hex_index: hex.index,90 game_id: gameId,91 coordinate_0: hex.coordinates[0],92 coordinate_1: hex.coordinates[1],93 coordinate_2: hex.coordinates[2],94 player: playerOnHex,95 hex_owner: playerOnHex,96 has_gold: hex.hasGold,97 has_wood: hex.hasWood,98 has_metal: hex.hasMetal,99 swordsmen: hex.swordsmen,100 archers: hex.archers,101 knights: hex.knights102 })103}104/////////////////////// Fetches the game board (hexes) ///////////////////////105const getGameBoard = async (room, gameIndex) => {106 let roomNum = room.includes('*') ? room.split('*').join('') : room;107 return await knex108 .column(knex.raw(`hex.*`))109 .select()110 .from(knex.raw(`hex, games`))111 .where(knex.raw(`${roomNum} = games.room_id AND '${gameIndex}' = game_index AND hex.game_id = games.game_id`));112}113/////////////////////// Switch players after current player makes a move ///////////////////////114const switchPlayers = async (gameIndex, currentPlayer) => {115 if (currentPlayer === 'player1') { // if the current player who just made a move is player 1116 await knex('games')117 .where(knex.raw(`'${gameIndex}' = game_index`))118 .update('current_player', 2) // switch to player 2119 } else if (currentPlayer === 'player2') { // else vice versa120 await knex('games')121 .where(knex.raw(`'${gameIndex}' = game_index`))122 .update('current_player', 1)123 }124}125/////////////////////// Update origin hex & new hex when player moves ///////////////////////126const updateDbHexes = async (masterOrigin, updatedTarget, currentPlayer, updatedOrigin) => {127 let playerId = await currentPlayer[currentPlayer.length - 1];128 if (updatedOrigin.swordsmen === 0 && updatedOrigin.archers === 0 && updatedOrigin.knights === 0) { // if all units were moved from original hex, remove player as owner & remove units from hex129 await knex('hex')130 .where(knex.raw(`'${masterOrigin[0].hex_index}' = hex_index`))131 .update({132 player: null,133 hex_owner: null,134 swordsmen: 0,135 archers: 0,136 knights: 0137 })138 } else { // else update original hex with units left behind by player139 await knex('hex')140 .where(knex.raw(`${playerId} = player AND '${masterOrigin[0].hex_index}' = hex_index`))141 .update({142 swordsmen: updatedOrigin.swordsmen,143 archers: updatedOrigin.archers,144 knights: updatedOrigin.knights145 })146 .then(data => { // then update the hex owner147 updateHexOwner(masterOrigin[0].hex_index, playerId);148 })149 }150 // Updates new hex that the player has moved to with current player & units151 await knex('hex')152 .where(knex.raw(`'${updatedTarget.index}' = hex_index`))153 .update({154 player: playerId, // moves the current player to the new hex155 hex_owner: playerId, // current player also now owns the hex156 swordsmen: updatedTarget.swordsmen, // updates the new hex with the player's units157 archers: updatedTarget.archers,158 knights: updatedTarget.knights159 })160 let updatedTargetHexIndex; // Fetches the new target hex161 updatedTargetHexIndex = updatedTarget.index !== undefined ? updatedTarget.index : updatedTarget.hex_index;162 let targetHex = await getHex(updatedTargetHexIndex); // NOTE: This returns an object163 // Updates gold resource for the new origin hex164 if (targetHex[0].has_gold) { // if the new origin hex has gold165 if (playerId === '1') { // and if current player is player 1166 return await knex('games')167 .where(knex.raw(`${targetHex[0].game_id} = game_id`))168 .increment('p1_gold', 10) // increases p1 gold by 10169 .then(data => {170 removeHexResource(targetHex[0].hex_index, 'gold'); // removes resource from hex171 })172 } else if (playerId === '2') { // else if current player is player 2173 return await knex('games')174 .where(knex.raw(`${targetHex[0].game_id} = game_id`))175 .increment('p2_gold', 10) // increases p2 gold by 10176 .then(data => {177 removeHexResource(targetHex[0].hex_index, 'gold'); // removes resource from hex178 })179 }180 }181 // Updates wood resource for the new origin hex182 if (targetHex[0].has_wood) { // if the new origin hex has wood183 if (playerId === '1') { // and if current player is player 1184 return await knex('games')185 .where(knex.raw(`${targetHex[0].game_id} = game_id`))186 .increment('p1_wood', 10) // increases p1 wood by 10187 .then(data => {188 removeHexResource(targetHex[0].hex_index, 'wood'); // removes resource from hex189 })190 } else if (playerId === '2') { // else if current player is player 2191 return await knex('games')192 .where(knex.raw(`${targetHex[0].game_id} = game_id`))193 .increment('p2_wood', 10) // increases p2 wood by 10194 .then(data => {195 removeHexResource(targetHex[0].hex_index, 'wood'); // removes resource from hex196 })197 }198 }199 // Updates metal resource for the new origin hex200 if (targetHex[0].has_metal) { // if the new origin hex has metal201 if (playerId === '1') { // and if current player is player 1202 return await knex('games')203 .where(knex.raw(`${targetHex[0].game_id} = game_id`))204 .increment('p1_metal', 10) // increases p1 metal by 10205 .then(data => {206 removeHexResource(targetHex[0].hex_index, 'metal'); // removes resource from hex207 })208 } else if (playerId === '2') { // else if current player is player 2209 return await knex('games')210 .where(knex.raw(`${targetHex[0].game_id} = game_id`))211 .increment('p2_metal', 10) // increases p2 metal by 10212 .then(data => {213 removeHexResource(targetHex[0].hex_index, 'metal'); // removes resource from hex214 })215 }216 }217}218const updateHexOwner = async (hexIndex, player) => { // update hex owner (on a move only) NOTE: Player comes in as string219 let hex = await getHex(hexIndex);220 // check if the original hex user was on has units on it221 if ((hex[0].swordsmen > 0 || hex[0].archers > 0 || hex[0].knights > 0) && (Number(player) === hex[0].player)) { // set current player as owner of hex & removes player as current player222 await knex('hex')223 .where(knex.raw(`'${hexIndex}' = hex_index`))224 .update({225 hex_owner: player,226 player: null227 })228 } else { // else set the current player & owner on hex to null229 await knex('hex')230 .where(knex.raw(`'${hexIndex}' = hex_index`))231 .update({232 hex_owner: null,233 player: null234 })235 }236}237/////////////////////// Updates the units on the hex upon combat ///////////////////////238const updateHexUnits = async (hexIndex, swordsmen, archers, knights, currentPlayer) => {239 let playerId = null;240 if (currentPlayer !== 'playernull' && currentPlayer !== null) { // if current player exists from server req241 playerId = await currentPlayer[currentPlayer.length - 1];242 await knex('hex')243 .where(knex.raw(`'${hexIndex}' = hex_index AND ${Number(playerId)} = player`))244 .update({245 swordsmen: swordsmen,246 archers: archers,247 knights: knights248 })249 } else { // if player needs to be removed250 await knex('hex')251 .where(knex.raw(`'${hexIndex}' = hex_index`))252 .update({253 player: null,254 swordsmen: swordsmen,255 archers: archers,256 knights: knights257 })258 }259}260/////////////////////// Gets bank totals for the specified player from game ///////////////////////261const getPlayerBank = async (room, gameIndex, currentPlayer) => {262 let roomNum = room.includes('*') ? room.split('*').join('') : room;263 if (currentPlayer === 'player1') {264 return await knex('games')265 .select('p1_swordsmen_bank', 'p1_archers_bank', 'p1_knights_bank')266 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))267 } else if (currentPlayer === 'player2') {268 return await knex('games')269 .select('p2_swordsmen_bank', 'p2_archers_bank', 'p2_knights_bank')270 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))271 }272}273/////////////////////// Gets total units on the board for the specified player from game ///////////////////////274const getPlayerTotalUnits = async (room, gameIndex, currentPlayer) => {275 let roomNum = room.includes('*') ? room.split('*').join('') : room;276 if (currentPlayer === 'player1') {277 return await knex('games')278 .select('p1_total_units')279 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))280 } else if (currentPlayer === 'player2') {281 return await knex('games')282 .select('p2_total_units')283 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))284 }285}286/////////////////////// Increases the player bank in game upon purchase ///////////////////////287const increasePlayerBank = async (room, gameIndex, currentPlayer, type, quantity) => {288 let roomNum = room.includes('*') ? room.split('*').join('') : room;289 if (currentPlayer === 'player1') {290 if (type === 'swordsmen') {291 await knex('games')292 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))293 .increment('p1_swordsmen_bank', quantity)294 }295 if (type === 'archers') {296 await knex('games')297 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))298 .increment('p1_archers_bank', quantity)299 }300 if (type === 'knights') {301 await knex('games')302 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))303 .increment('p1_knights_bank', quantity)304 }305 } else if (currentPlayer === 'player2') {306 if (type === 'swordsmen') {307 await knex('games')308 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))309 .increment('p2_swordsmen_bank', quantity)310 }311 if (type === 'archers') {312 await knex('games')313 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))314 .increment('p2_archers_bank', quantity)315 }316 if (type === 'knights') {317 await knex('games')318 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))319 .increment('p2_knights_bank', quantity)320 }321 }322}323//////////////// Updates a player's total units in game //////////////////324const updatePlayerTotalUnits = async (room, gameIndex, currentPlayer, quantity, action) => { // action will be 'increase' or 'replace' or 'decrease'325 let roomNum = room.includes('*') ? room.split('*').join('') : room;326 if (currentPlayer === 'player1') { // if player 1327 if (action === 'increase') { // if adding to player's total units328 await knex('games')329 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))330 .increment('p1_total_units', quantity) // increases the player's units by the quantity331 } else if (action === 'decrease') { // if subtracting units from the player's total units332 await knex('games')333 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))334 .decrement('p1_total_units', quantity) // decreases the player's units by the quantity335 } else if (action === 'replace') { // replace total units336 await knex('games')337 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))338 .update('p1_total_units', quantity) // replaces the player's units by the quantity339 }340 } else if (currentPlayer === 'player2') { // else if player 2341 if (action === 'increase') { // if adding to player's total units342 await knex('games')343 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))344 .increment('p2_total_units', quantity) // increases the player's units by the quantity345 } else if (action === 'decrease') { // if subtracting units from the player's total units346 await knex('games')347 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))348 .decrement('p2_total_units', quantity) // decreases the player's units by the quantity349 } else if (action === 'replace') { // replace total units350 await knex('games')351 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))352 .update('p2_total_units', quantity) // replaces the player's units by the quantity353 }354 }355}356////////////////// Deploy units from player bank to hex //////////////////357const deployUnits = async (room, hexIndex, gameIndex, type, quantity, currentPlayer) => {358 let gameId = await getGame(room, gameIndex); // get game id359 await knex('hex')360 .where(knex.raw(`'${gameId[0].game_id}' = game_id AND '${hexIndex}' = hex_index`))361 .increment(type, quantity)362}363/////////////////////// Decreases the player bank upon deploying units ///////////////////////364const decreasePlayerBank = async (room, gameIndex, currentPlayer, type, quantity) => {365 let roomNum = room.includes('*') ? room.split('*').join('') : room;366 if (currentPlayer === 'player1') { // if decreasing bank for player 1367 if (type === 'swordsmen') { // if type is swordsmen for player 1368 await knex('games')369 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))370 .decrement('p1_swordsmen_bank', quantity)371 }372 if (type === 'archers') { // if type is archers for player 1373 await knex('games')374 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))375 .decrement('p1_archers_bank', quantity)376 }377 if (type === 'knights') { // if type is knights for player 1378 await knex('games')379 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))380 .decrement('p1_knights_bank', quantity)381 }382 } else if (currentPlayer === 'player2') { // if decreasing bank for player 2383 if (type === 'swordsmen') { // if type is swordsmen for player 2384 await knex('games')385 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))386 .decrement('p2_swordsmen_bank', quantity)387 }388 if (type === 'archers') { // if type is archers for player 2389 await knex('games')390 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))391 .decrement('p2_archers_bank', quantity)392 }393 if (type === 'knights') { // if type is knights for player 2394 await knex('games')395 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))396 .decrement('p2_knights_bank', quantity)397 }398 }399}400/////////////////////// Switches player/owner of hex during combat /////////////////////401const switchHexOwner = async (hexIndex, updatedOwner) => {402 let ownerId = null;403 if (updatedOwner) { // if there is an owner to be updated404 ownerId = await updatedOwner[updatedOwner.length - 1];405 await knex('hex')406 .where(knex.raw(`'${hexIndex}' = hex_index`))407 .update({408 player: ownerId,409 hex_owner: ownerId410 })411 } else { // else if updating to no owner412 await knex('hex')413 .where(knex.raw(`'${hexIndex}' = hex_index`))414 .update({415 hex_owner: null,416 player: null417 })418 }419}420/////////////////////// Removes resource from the hex ///////////////////////421const removeHexResource = async (hexIndex, resource) => {422 if (resource === 'gold') {423 await knex('hex')424 .where(knex.raw(`'${hexIndex}' = hex_index`))425 .update({has_gold: 0})426 }427 if (resource === 'wood') {428 await knex('hex')429 .where(knex.raw(`'${hexIndex}' = hex_index`))430 .update({has_wood: 0})431 }432 if (resource === 'metal') {433 await knex('hex')434 .where(knex.raw(`'${hexIndex}' = hex_index`))435 .update({has_metal: 0})436 }437}438/////////////////////// Gets user resources from game ///////////////////////439const getResources = async (room, gameIndex, currentPlayer) => {440 let roomNum = room.includes('*') ? room.split('*').join('') : room;441 if (currentPlayer === 'player1') {442 return await knex('games')443 .select('p1_gold', 'p1_wood', 'p1_metal')444 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))445 } else if (currentPlayer === 'player2') {446 return await knex('games')447 .select('p2_gold', 'p2_wood', 'p2_metal')448 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))449 }450}451/////////////////////// Updates user resources & units upon purchases ///////////////////////452const buySwordsmen = async (room, gameIndex, currentPlayer) => {453 let roomNum = room.includes('*') ? room.split('*').join('') : room;454 if (currentPlayer === 'player1') {455 await knex('games')456 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))457 .decrement('p1_gold', 10) // decreases the player's gold - 10458 await knex('games')459 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))460 .decrement('p1_metal', 10) // decreases the player's metal - 10461 } else if (currentPlayer === 'player2') {462 await knex('games')463 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))464 .decrement('p2_gold', 10) // decreases the player's gold - 10465 await knex('games')466 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))467 .decrement('p2_metal', 10) // decreases the player's metal - 10468 }469};470const buyArchers = async (room, gameIndex, currentPlayer) => {471 let roomNum = room.includes('*') ? room.split('*').join('') : room;472 if (currentPlayer === 'player1') {473 await knex('games')474 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))475 .decrement('p1_gold', 10) // decreases the player's gold - 10476 await knex('games')477 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))478 .decrement('p1_wood', 20) // decreases the player's wood - 20479 } else if (currentPlayer === 'player2') {480 await knex('games')481 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))482 .decrement('p2_gold', 10) // decreases the player's gold - 10483 await knex('games')484 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))485 .decrement('p2_wood', 20) // decreases the player's wood - 20486 }487};488const buyKnights = async (room, gameIndex, currentPlayer) => {489 let roomNum = room.includes('*') ? room.split('*').join('') : room;490 if (currentPlayer === 'player1') {491 await knex('games')492 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))493 .decrement('p1_gold', 20) // decreases the player's gold - 20494 await knex('games')495 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))496 .decrement('p1_wood', 20) // decreases the player's wood - 20497 await knex('games')498 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))499 .decrement('p1_metal', 20) // decreases the player's metal - 20500 } else if (currentPlayer === 'player2') {501 await knex('games')502 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))503 .decrement('p2_gold', 20) // decreases the player's gold - 20504 await knex('games')505 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))506 .decrement('p2_wood', 20) // decreases the player's wood - 20507 await knex('games')508 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`))509 .decrement('p2_metal', 20) // decreases the player's metal - 20510 }511};512/////////////////////// Gets hex based off hex index ///////////////////////513const getHex = (hexIndex) => { // NOTE: This will return an OBJECT514 return knex('hex').select()515 .where(knex.raw(`'${hexIndex}' = hex_index`))516}517/////////////////////// Get the current player on the hex ///////////////////////518const getCurrentPlayerHex = async (gameId, currentPlayer) => {519 if (currentPlayer === 'player1') {520 return await knex('hex')521 .where(knex.raw(`${gameId} = game_id AND 1 = player`));522 } else if (currentPlayer === 'player2') {523 return await knex('hex')524 .where(knex.raw(`${gameId} = game_id AND 2 = player`));525 }526}527/////////////////////// Deletes hexes if game has ended ///////////////////////528const deleteHex = async (gameId) => {529 await knex('hex')530 .where(knex.raw(`${gameId} = game_id`))531 .del()532}533/////////////////////// Updates game to completed once done ///////////////////////534const gameComplete = async (gameIndex, room, winner, loser) => {535 let roomNum = room.includes('*') ? room.split('*').join('') : room;536 let game = await getGame(room, gameIndex);537 if ((winner === 'player1') && (game[0].player1 !== 1)) { // if the winner is player1 & is not anon538 await knex('users')539 .where(knex.raw(`user_id = ${game[0].player1}`))540 .increment('wins', 1) // increase wins541 if (game[0].player2 !== 2) { // if player2 is not anonymous (id of 2 = anon in db)542 await knex('users') // if the winner is player1 & is not anonymous543 .where(knex.raw(`user_id = ${game[0].player2}`))544 .increment('losses', 1) // increase losses545 }546 } else if ((winner === 'player2') && (game[0].player2 !== 2)) { // if the winner is player2 & is not anon547 await knex('users')548 .where(knex.raw(`user_id = ${game[0].player2}`))549 .increment('wins', 1) // increase wins550 if (game[0].player1 !== 1) { // if player1 is not anonymous (id of 1 = anon in db)551 await knex('users')552 .where(knex.raw(`user_id = ${game[0].player1}`))553 .increment('losses', 1) // increase losses554 }555 }556 await deleteHex(game[0].game_id); // first delete the hexes (foreign key restraint)557 await knex('games') // then delete the game558 .where(knex.raw(`${game[0].game_id} = game_id`))559 .del()560}561/////////////////////// Retrieves game based off room and/or game index ///////////////////////562const getGame = async (room, gameIndex) => {563 let roomNum;564 if (room && gameIndex) { // if room && game index are provided565 roomNum = room.includes('*') ? room.split('*').join('') : room;566 return await knex('games').select()567 .where(knex.raw(`${roomNum} = room_id AND '${gameIndex}' = game_index`));568 }569 if (gameIndex) { // if only game index is available570 return await knex('games').select()571 .where(knex.raw(`'${gameIndex}' = game_index`));572 }573 if (room) { // if only room is available574 roomNum = room.includes('*') ? room.split('*').join('') : room;575 return await knex('games').select()576 .where(knex.raw(`${roomNum} = room_id`))577 .returning('game_id')578 }579 if (gameIndex === undefined && room === null) {580 console.log('error in getting the game from database');581 }582}583/////////////////////// Deletes game when a player leaves the room ///////////////////////584const forceEndGame = async (gameIndex, saveGame) => {585 if (saveGame !== 'saveOnly') { // if only passing in the gameindex, then the game needs to be ended586 let game = await getGame(null, gameIndex);587 if (game.length > 0) {588 await deleteHex(game[0].game_id); // first delete the hexes589 await knex('games') // then delete the game590 .where(knex.raw(`${game[0].game_id} = game_id`))591 .del();592 }593 } else { // else the game is being saved, do nothing!594 return;595 }596}597/////////////////////// Sets players for the game ///////////////////////598const setGamePlayers = async (username, currentPlayer, gameIndex, room) => {599 let roomNum = room.includes('*') ? room.split('*').join('') : room;600 let gameId = await getGame(room, gameIndex);601 let gameBoard = await getGameBoard(room, gameIndex);602 let playerOneHex = gameBoard[0];603 let playerTwoHex = gameBoard[gameBoard.length - 1];604 let playerOne;605 let playerTwo;606 if (username === 'anonymous') {607 if (currentPlayer === 'player2') {608 playerTwo = [{user_id: 2}];609 await knex('games')610 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))611 .update('player2', 2) // anon player2 user id is automatically set to 2 in db612 return;613 } else if (currentPlayer === 'player1') {614 playerOne = [{user_id: 1}];615 await knex('games')616 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))617 .update('player1', 1) // anon player1 user id is automatically set to 1 in db618 return;619 }620 } else {621 if (currentPlayer === 'player2') {622 playerTwo = await getUserId(username, 'player2'); // returns an object623 await knex('games')624 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))625 .update('player2', playerTwo[0].user_id)626 } else if (currentPlayer === 'player1') {627 playerOne = await getUserId(username, 'player1'); // returns an object628 await knex('games')629 .where(knex.raw(`'${gameIndex}' = game_index AND ${roomNum} = room_id`))630 .update('player1', playerOne[0].user_id)631 }632 }633}634/////////////////////// Gets all users from db who are not anon ///////////////////////635const getUsernames = async () => {636 return await knex('users')637 .select(knex.raw(`username, wins, losses, email`))638 .whereNot(knex.raw(`username = 'anonymous'`))639 .orderByRaw(`wins DESC`)640 .orderByRaw(`username ASC`)641}642/////////////////////// Get user rank ///////////////////////643const getUserRank = async (username) => {644 let users = await getUsernames();645 return users.findIndex(user => user.username == username);646}647/////////////////////// Updates the room number once a game is resumed ///////////////////////648const updateRoomNum = async (gameIndex, newRoom) => {649 let roomNum = newRoom.includes('*') ? newRoom.split('*').join('') : newRoom; // roomNum result will be a string650 await knex('games')651 .where(knex.raw(`'${gameIndex}' = game_index`))652 .update('room_id', Number(roomNum))653}654/////////////////////// Get the other user's info (email and what not) ///////////////////////655const getOtherUserStuff = async (gameIndex, username) => { // username = current user656 let user = await getUserId(username);657 let game = await getGame(null, gameIndex);658 if (user[0].user_id === game[0].player1) { // if current player is player1659 return knex.column(knex.raw(`users.user_id, users.username, users.email`)) // then get player2's info660 .from(knex.raw(`games, users`))661 .where(knex.raw(`games.game_index = '${gameIndex}' AND games.player2 = users.user_id`))662 } else if (user[0].user_id === game[0].player2) { // if current player is player2663 return knex.column(knex.raw(`users.user_id, users.username, users.email`)) // then get player1's info664 .from(knex.raw(`games, users`))665 .where(knex.raw(`games.game_index = '${gameIndex}' AND games.player1 = users.user_id`))666 }667}668/////////////////////// Deletes game if > 1 day has passed ///////////////////////669const deleteOldGames = async (gameId) => {670 let today = await moment(new Date()).format('YYYY-MM-DD 23:59:59');671 let yesterday = await moment(new Date()).subtract(1, 'days').format('YYYY-MM-DD 00:00:00');672 let oldGames = await knex.column(knex.raw(`games.created_at, games.game_id, hex.*`))673 .from(knex.raw(`games, hex`))674 .where(knex.raw(`games.created_at NOT BETWEEN '${yesterday}' AND '${today}'`))675 .andWhere(knex.raw(`games.game_id = hex.game_id`))676 .orderByRaw(`games.created_at DESC`)677 if (oldGames.length > 0) {678 return Promise.all(oldGames.forEach(async (hex, i, oldGames) => {679 await deleteHex(hex.game_id); // delete the hexes680 await knex('games') // then delete the game681 .where(knex.raw(`${hex.game_id} = game_id`))682 .del()683 console.log(`Games older than ${today} and ${yesterday} have been deleted`);684 }));685 } else {686 console.log('No old games to be deleted')687 return;688 }689}690/////////////////////// Gets all games to generate in the rooms list ///////////////////////691const getAllGames = async () => {692 return await knex.column(knex.raw(`game_index as 'gameIndex', game_id as 'gameId', room_id as 'room'`))693 .from('games')694 .select()695}696/////////////////////// When a user deletes an existing game from their current games list ///////////////////////697const deleteUserGame = async (gameId) => {698 await deleteHex(gameId); // first delete the hexes699 await knex('games') // then delete the game700 .where(knex.raw(`${gameId} = game_id`))701 .del();702}703/////////////////////// Gets user's existing games ///////////////////////704const retrieveUserGames = async (username) => {705 let currentUser = await getUserId(username);706 const games = await knex.column(knex.raw(`games.*, users.username as 'player2_username'`))707 .select()708 .from(knex.raw(`games, users`))709 .whereNot(knex.raw(`games.player2 = 2`)) // where player2 is not anonymous710 .andWhereNot(knex.raw(`games.player1 = 1`)) // where player1 is not anonymous711 .andWhere(knex.raw(`712 ( ${currentUser[0].user_id} = games.player2713 OR ${currentUser[0].user_id} = games.player1 )714 AND LOWER('${username}') = LOWER(users.username715 )`)) // where current user is player1 or player2 and username matches current user716 .orderByRaw(`games.created_at DESC`);717 return Promise.all(games.map(async (game, i, games) => {718 if (game.player1 === currentUser[0].user_id) { // if current user is player1719 let otherUser = await knex.select()720 .from('users')721 .where(knex.raw(`${game.player2} = user_id`));722 game.player2_username = otherUser[0].username;723 game.player1_username = currentUser[0].username;724 return game;725 } else if (game.player2 === currentUser[0].user_id) { // if current user is player2726 let otherUser = await knex.select()727 .from('users')728 .where(knex.raw(`${game.player1} = user_id`));729 game.player1_username = otherUser[0].username;730 game.player2_username = currentUser[0].username;731 return game;732 }733 }));734}735module.exports = {736 addUser,737 checkUserCreds,738 getUserId,739 findUserById,740 createGame,741 createHex,742 getGameBoard,...
20210408224048_convert_materialized_views.js
Source:20210408224048_convert_materialized_views.js
1exports.up = async function (knex) {2 await knex.raw(3 "CREATE TABLE country_grid_110km_real (LIKE country_grid_110km INCLUDING ALL)"4 );5 await knex.raw(6 "INSERT INTO country_grid_110km_real SELECT * FROM country_grid_110km"7 );8 await knex.raw("DROP MATERIALIZED VIEW country_grid_110km");9 await knex.raw(10 "ALTER TABLE country_grid_110km_real RENAME TO country_grid_110km"11 );12 await knex.raw("ALTER TABLE country_grid_110km ADD PRIMARY KEY (id)");13 await knex.raw(14 "ALTER INDEX country_grid_110km_real_centroid_location_idx RENAME TO country_grid_110km_centroid_location_index"15 );16 await knex.raw(17 "ALTER INDEX country_grid_110km_real_geom_idx RENAME TO country_grid_110km_geom_index"18 );19 await knex.raw(20 "CREATE TABLE country_grid_11km_real (LIKE country_grid_11km INCLUDING ALL)"21 );22 await knex.raw(23 "INSERT INTO country_grid_11km_real SELECT * FROM country_grid_11km"24 );25 await knex.raw("DROP MATERIALIZED VIEW country_grid_11km");26 await knex.raw(27 "ALTER TABLE country_grid_11km_real RENAME TO country_grid_11km"28 );29 await knex.raw("ALTER TABLE country_grid_11km ADD PRIMARY KEY (id)");30 await knex.raw(31 "ALTER INDEX country_grid_11km_real_centroid_location_idx RENAME TO country_grid_11km_centroid_location_index"32 );33 await knex.raw(34 "ALTER INDEX country_grid_11km_real_geom_idx RENAME TO country_grid_11km_geom_index"35 );36 await knex.raw(37 "CREATE TABLE country_grid_220km_real (LIKE country_grid_220km INCLUDING ALL)"38 );39 await knex.raw(40 "INSERT INTO country_grid_220km_real SELECT * FROM country_grid_220km"41 );42 await knex.raw("DROP MATERIALIZED VIEW country_grid_220km");43 await knex.raw(44 "ALTER TABLE country_grid_220km_real RENAME TO country_grid_220km"45 );46 await knex.raw("ALTER TABLE country_grid_220km ADD PRIMARY KEY (id)");47 await knex.raw(48 "ALTER INDEX country_grid_220km_real_centroid_location_idx RENAME TO country_grid_220km_centroid_location_index"49 );50 await knex.raw(51 "ALTER INDEX country_grid_220km_real_geom_idx RENAME TO country_grid_220km_geom_index"52 );53 await knex.raw(54 "CREATE TABLE country_grid_22km_real (LIKE country_grid_22km INCLUDING ALL)"55 );56 await knex.raw(57 "INSERT INTO country_grid_22km_real SELECT * FROM country_grid_22km"58 );59 await knex.raw("DROP MATERIALIZED VIEW country_grid_22km");60 await knex.raw(61 "ALTER TABLE country_grid_22km_real RENAME TO country_grid_22km"62 );63 await knex.raw("ALTER TABLE country_grid_22km ADD PRIMARY KEY (id)");64 await knex.raw(65 "ALTER INDEX country_grid_22km_real_centroid_location_idx RENAME TO country_grid_22km_centroid_location_index"66 );67 await knex.raw(68 "ALTER INDEX country_grid_22km_real_geom_idx RENAME TO country_grid_22km_geom_index"69 );70 await knex.raw(71 "CREATE TABLE country_grid_25km_real (LIKE country_grid_25km INCLUDING ALL)"72 );73 await knex.raw(74 "INSERT INTO country_grid_25km_real SELECT * FROM country_grid_25km"75 );76 await knex.raw("DROP MATERIALIZED VIEW country_grid_25km");77 await knex.raw(78 "ALTER TABLE country_grid_25km_real RENAME TO country_grid_25km"79 );80 await knex.raw("ALTER TABLE country_grid_25km ADD PRIMARY KEY (id)");81 await knex.raw(82 "CREATE TABLE country_grid_55km_real (LIKE country_grid_55km INCLUDING ALL)"83 );84 await knex.raw(85 "INSERT INTO country_grid_55km_real SELECT * FROM country_grid_55km"86 );87 await knex.raw("DROP MATERIALIZED VIEW country_grid_55km");88 await knex.raw(89 "ALTER TABLE country_grid_55km_real RENAME TO country_grid_55km"90 );91 await knex.raw("ALTER TABLE country_grid_55km ADD PRIMARY KEY (id)");92 await knex.raw(93 "ALTER INDEX country_grid_55km_real_centroid_location_idx RENAME TO country_grid_55km_centroid_location_index"94 );95 await knex.raw(96 "ALTER INDEX country_grid_55km_real_geom_idx RENAME TO country_grid_55km_geom_index"97 );98 await knex.raw(99 "CREATE TABLE state_grid_110km_real (LIKE state_grid_110km INCLUDING ALL)"100 );101 await knex.raw(102 "INSERT INTO state_grid_110km_real SELECT * FROM state_grid_110km"103 );104 await knex.raw("DROP MATERIALIZED VIEW state_grid_110km");105 await knex.raw(106 "ALTER TABLE state_grid_110km_real RENAME TO state_grid_110km"107 );108 await knex.raw("ALTER TABLE state_grid_110km ADD PRIMARY KEY (id)");109 await knex.raw(110 "ALTER INDEX state_grid_110km_real_centroid_location_idx RENAME TO state_grid_110km_centroid_location_index"111 );112 await knex.raw(113 "ALTER INDEX state_grid_110km_real_geom_idx RENAME TO state_grid_110km_geom_index"114 );115 await knex.raw(116 "CREATE TABLE state_grid_11km_real (LIKE state_grid_11km INCLUDING ALL)"117 );118 await knex.raw(119 "INSERT INTO state_grid_11km_real SELECT * FROM state_grid_11km"120 );121 await knex.raw("DROP MATERIALIZED VIEW state_grid_11km");122 await knex.raw("ALTER TABLE state_grid_11km_real RENAME TO state_grid_11km");123 await knex.raw("ALTER TABLE state_grid_11km ADD PRIMARY KEY (id)");124 await knex.raw(125 "ALTER INDEX state_grid_11km_real_centroid_location_idx RENAME TO state_grid_11km_centroid_location_index"126 );127 await knex.raw(128 "ALTER INDEX state_grid_11km_real_geom_idx RENAME TO state_grid_11km_geom_index"129 );130 await knex.raw(131 "CREATE TABLE state_grid_220km_real (LIKE state_grid_220km INCLUDING ALL)"132 );133 await knex.raw(134 "INSERT INTO state_grid_220km_real SELECT * FROM state_grid_220km"135 );136 await knex.raw("DROP MATERIALIZED VIEW state_grid_220km");137 await knex.raw(138 "ALTER TABLE state_grid_220km_real RENAME TO state_grid_220km"139 );140 await knex.raw("ALTER TABLE state_grid_220km ADD PRIMARY KEY (id)");141 await knex.raw(142 "ALTER INDEX state_grid_220km_real_centroid_location_idx RENAME TO state_grid_220km_centroid_location_index"143 );144 await knex.raw(145 "ALTER INDEX state_grid_220km_real_geom_idx RENAME TO state_grid_220km_geom_index"146 );147 await knex.raw(148 "CREATE TABLE state_grid_22km_real (LIKE state_grid_22km INCLUDING ALL)"149 );150 await knex.raw(151 "INSERT INTO state_grid_22km_real SELECT * FROM state_grid_22km"152 );153 await knex.raw("DROP MATERIALIZED VIEW state_grid_22km");154 await knex.raw("ALTER TABLE state_grid_22km_real RENAME TO state_grid_22km");155 await knex.raw("ALTER TABLE state_grid_22km ADD PRIMARY KEY (id)");156 await knex.raw(157 "ALTER INDEX state_grid_22km_real_centroid_location_idx RENAME TO state_grid_22km_centroid_location_index"158 );159 await knex.raw(160 "ALTER INDEX state_grid_22km_real_geom_idx RENAME TO state_grid_22km_geom_index"161 );162 await knex.raw(163 "CREATE TABLE state_grid_500k_55km_real (LIKE state_grid_500k_55km INCLUDING ALL)"164 );165 await knex.raw(166 "INSERT INTO state_grid_500k_55km_real SELECT * FROM state_grid_500k_55km"167 );168 await knex.raw("DROP MATERIALIZED VIEW state_grid_500k_55km");169 await knex.raw(170 "ALTER TABLE state_grid_500k_55km_real RENAME TO state_grid_500k_55km"171 );172 await knex.raw("ALTER TABLE state_grid_500k_55km ADD PRIMARY KEY (id)");173 await knex.raw(174 "ALTER INDEX state_grid_500k_55km_real_centroid_location_idx RENAME TO state_grid_500k_55km_centroid_location_index"175 );176 await knex.raw(177 "ALTER INDEX state_grid_500k_55km_real_geom_idx RENAME TO state_grid_500k_55km_geom_index"178 );179 await knex.raw("DROP INDEX state_grid_500k_55km_real_id_idx");180 await knex.raw(181 "CREATE TABLE state_grid_55km_real (LIKE state_grid_55km INCLUDING ALL)"182 );183 await knex.raw(184 "INSERT INTO state_grid_55km_real SELECT * FROM state_grid_55km"185 );186 await knex.raw("DROP MATERIALIZED VIEW state_grid_55km");187 await knex.raw("ALTER TABLE state_grid_55km_real RENAME TO state_grid_55km");188 await knex.raw("ALTER TABLE state_grid_55km ADD PRIMARY KEY (id)");189 await knex.raw(190 "ALTER INDEX state_grid_55km_real_centroid_location_idx RENAME TO state_grid_55km_centroid_location_index"191 );192 await knex.raw(193 "ALTER INDEX state_grid_55km_real_geom_idx RENAME TO state_grid_55km_geom_index"194 );195};...
20210111184458_event.js
Source:20210111184458_event.js
...13 * See the License for the specific language governing permissions and14 * limitations under the License.15 */16exports.up = async function(knex) {17 await knex.raw(`INSERT INTO permission(id, description) VALUES (2, 'Allow to activate user')`);18 await knex.raw(`INSERT INTO permission(id, description) VALUES (3, 'Allow to ban user')`);19 await knex.raw(`INSERT INTO permission(id, description) VALUES (4, 'Allow to add user')`);20 await knex.raw(`INSERT INTO permission(id, description) VALUES (5, 'Allow to send invite email')`);21 await knex.raw(`INSERT INTO permission(id, description) VALUES (6, 'Allow to exists users')`);22 await knex.raw(`INSERT INTO permission(id, description) VALUES (7, 'Allow to add/edit event')`);23 await knex.raw(`INSERT INTO permission(id, description) VALUES (8, 'Allow to del event')`);24 await knex.raw(`CREATE TYPE event_status_t as enum('created', 'deleted');`);25 await knex.raw(`CREATE TABLE event (26 id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),27 creator uuid references "User"(id),28 time TIMESTAMP NOT NULL,29 title varchar(128) NOT NULL CHECK (title <> ''),30 description varchar(4096) NOT NULL CHECK (description <> ''),31 image varchar(256) NULL,32 link varchar(256) NOT NULL CHECK (link <> ''),33 status event_status_t34 )`);35 await knex.raw(`CREATE TABLE event_user (36 user_id uuid references "User"(id),37 event_id uuid references event(id)38 )`);39 await knex.raw('CREATE INDEX event_user_user_id_index on event_user(user_id)');40 await knex.raw('CREATE INDEX event_user_event_id_index on event_user(event_id)');41};42exports.down = async function(knex) {43 await knex.raw('DROP INDEX event_user_user_id_index');44 await knex.raw('DROP INDEX event_user_event_id_index');45 await knex.raw('DROP TABLE event_user');46 await knex.raw('DROP TABLE event');47 await knex.raw('DELETE FROM permission WHERE id IN (2, 3, 4, 5, 6, 7, 8)');...
Using AI Code Generation
1const knex = require('knex');2const qawolf = require('qawolf');3const { launch } = qawolf;4(async () => {5 const browser = await launch();6 const context = await browser.newContext();7 const page = await context.newPage();8 const result = await knex.raw('select * from users where id = ?', [1]);9 console.log(result);10 await browser.close();11})();
Using AI Code Generation
1knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {2 console.log(resp);3});4knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {5 console.log(resp);6});7knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {8 console.log(resp);9});10knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {11 console.log(resp);12});13knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {14 console.log(resp);15});16knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {17 console.log(resp);18});19knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {20 console.log(resp);21});22knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {23 console.log(resp);24});25knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {26 console.log(resp);27});28knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {29 console.log(resp);30});31knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {32 console.log(resp);33});34knex.raw('SELECT * FROM table_name WHERE id = 1').then(function(resp) {35 console.log(resp);36});
Using AI Code Generation
1const knex = require('knex')({client: 'pg'});2const qawolf = require('qawolf');3qawolf.create();4const { browser, page } = await qawolf.launch();5await page.click('[data-testid="login-button"]');6await page.type('[data-testid="login-email"]', '
Automation Testing Tutorials
Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run qawolf automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
