# Course (Sqlite, Session)

###### tags: `web` `deno` `sqlite`

course 3
https://www.facebook.com/ccckmit/videos/3328236040831796?idorvanity=266037214366597

course 5
https://www.facebook.com/ccckmit/videos/4174488749430618?idorvanity=266037214366597

## Sqlite
Document: https://deno.land/x/sqlite@v3.5.0

The wrapper is targeted at [Deno](https://deno.land/) and uses a version of SQLite3 compiled to WebAssembly (WASM)

---

**Modify from https://deno.land/x/sqlite@v3.5.0**

import { DB } from "https://deno.land/x/sqlite/mod.ts";


//User data

interface userAssociatArray{
    id:any,
    name:string,
    password:string
}

const userAssociatArray:any =[
    {id:"1",name:"meowhecker",password:"test123"},
    {id:"2", name:"meowmeow", password:"123"}
]


//instance sqlite database 

const sqliteDB = new DB("app.db")

sqliteDB.execute(`
    create table if not exists
    users(
        id integer primary key autoincrement, 
        name text, 
        password text
    )
`)

// Insert 

const userdata:any = {} 

for (const userObject of userAssociatArray){
    // console.log(userObject.id)
    // console.log(userObject.name)
    // console.log(userObject.password)

    sqliteDB.query("insert into users (name,password) values (?,?)" ,
    [userObject.name,userObject.password])

    }


//Query

for (const userdata of sqliteDB.query("select * from users")){
    console.log(userdata)
}

export {userAssociatArray}


Sqlite 2 (without the dictionary version)

import { Application, Router } from "https://deno.land/x/oak/mod.ts";
import * as view from "./View.ts"
import { DB } from "https://deno.land/x/sqlite/mod.ts";



const port:number = 8200

const sqliteDB = new DB("app.db")

sqliteDB.execute(`
    create table if not exists
    users(
        id integer primary key autoincrement, 
        name text, 
        password text
    )
`)


const router:any = new Router
router.get("/",listings)
       .get("/create",create)
       .get("/listing/:id",show)
       .post("/store",store)



const app = new Application();
app.use(router.routes());
app.use(router.allowedMethods());
      


//Controller Meow

    function query(sqlCommend){
        let list:any = []
        for (const [id, name, password] of sqliteDB.query(sqlCommend)){
            list.push({id,name,password})
        }
        return list
    }



    async function listings(ctx:any){
        const UserData = query("select * from users ")
        console.log("userDatas="+ UserData)
        ctx.response.body = await view.listings(UserData)
    }


    async function show(ctx:any){
        //fetch URL parameter 
            const parameterID = ctx.params.id
            let users = sqliteDB.query(`select * from users where id = ${parameterID}`)
            const singleUserData = [URLid]
            if(!singleUserData){
                ctx.throw(404,"invalid ID")
            }else{
                ctx.response.body = await view.listingPage(singleUserData)
            }
    }

    async function create(ctx:any){
        ctx.response.body = await view.CreatePage()
    }

    async function store(ctx:any){

        // console.log(body)
        // console.log(body.value)
        // console.log(body.type)

        const body = ctx.request.body()
        const parse = await body.value

        
        const newUserObject:any={}

        for( const [key,value] of parse ) {
            newUserObject[key] = value
        }
        console.log("FormValue =",newUserObject)

        sqliteDB.query("insert into users (name, password)values (?, ?)",[newUserObject.name,newUserObject.password])

        ctx.response.redirect("/")
    }


  console.log('start at:http://127.0.0.1:'+port)
  await app.listen({port:port})


//.push
    // Definition and Usage
    // The push() method adds new items to the end of an array.
    // The push() method changes the length of the array.
    // The push() method returns the new length.

## Session

https://deno.land/x/oak_sessions@v4.0.5

The Session is stored at the sever end and it could record information such as the variable.
So. we could use it to login into the website automatically.

import { Session } from "https://deno.land/x/oak_sessions/mod.ts";

Notice: Before we new the router, we must new the app and enable the session, otherwise, it can't read the property from the session module.

const app = new Application();
  app.use(Session.initMiddleware()); //enable session 

const router:any = new Router()
  app.use(router.routes());
  app.use(router.allowedMethods());
        

As soon as we get in the website, we try to get the session.

Determine whether to log in

async function listings(ctx:any){

    const UserData = query("select * from userListings ")
    console.log("userDatas="+ UserData)
    let user:any = await ctx.state.session.get('user')
    console.log('user=', user)
    ctx.response.body = await view.listings(UserData)
}

### Listings Controller

store (authorization)

async function store(ctx:any){

    //check session stauts 
    var user = await ctx.state.session.get('user')

    const body = ctx.request.body()

    if(body.type === 'form'){
        const parse = await body.value
         console.log(user)
            
         //parse Form 
            const newUserObject:any={}
            for( const [key,value] of parse ) {
                newUserObject[key] = value
            }

        console.log("FormValue =",newUserObject)
        sqliteDB.query("insert into userListings (name, createAt, description)values (?, ?, ?)",[user.userName, newUserObject.createAt, newUserObject.description])
        ctx.response.redirect("/")
    }
   
}

### UsersController

logout

 async function logout(ctx){
    await ctx.state.session.set('user',null)
    ctx.response.redirect("/")

 }

### Security

Password should be stored as hashes

### Hacking


乾