Get clear on session usage (if at all) and database structure

[Jonathan-Weinstein]

Here is something I thought of that seems simple. See my remarks about worrying about security/leaking/efficiency. Do we want to go with this, with some modifications perhaps? If not, then can someone please write some psuedo code like I have, showing the table layout we will use, and functions called on them to show why the fields are there?

I think this is important so we can all be on same page.

no_sessions.txt

[Jonathan-Weinstein]

I thought the file attatchment would preview it, Ill paste it here so we can see it easier and comment/modify it.

/ 2 main tables only. can have more tables if speed up access in some way, like one sorted by utc with pointer to entry. Im new to sql, and dont have time to properly learn now, lets not worry about efficency of queries. Do something simple that works. dont worry if security is not perfect or a story is leaked, that doesnt matter to an honest user, and Horstmman cares more that the thing appears to work, hes not gonna try and break it. /

TABLE in_progress_stories
{
    int id;
    string title;
    string body;
    inet owner;//null if not currently given out
    utc timestamp;//utc time given out, irrelevant if owner is null
}

TABLE completed_stories
{
    int id;
    string title;
    string body;
    utc time;//maybe want this to have option view newest
    int votes;//if were gonna have votes, lets not care so about manipulation
}

(int id, string title, string body) get_random_inprogress_story(conn)//called when use hits continue story (NOT submit)
{
    now = utc_now()
    s = SELECT story with oldest timestamp ALSO UPDATE (timestamp<-now, owner<-conn.peer)
    if s not null && (now - s.timestamp) > 300: //amortize freeing stories handed out that were never submitted for a long time (5 min).
        assign(conn, id: s.id)//reassign
        return s
    else:
        s = SELECT 1 random story WHERE owner == null ALSO update (timestamp<-now, owner<-conn.peer)
        if s not null:
            assign(conn, id: s.id)
            return s
        else: //in-progress table empty, or all handed out
            return null;//send error message, tell user to create a new story, href to that page
        end
    end
}

function handle_submit(conn, title, append_data, is_final)
{
    assign(conn, id: -1)
    s = SELECT story WHERE owner=conn.peer ALSO UPDATE owner<-null
    if s==null || s.id != conn.id:
        return error

    if is_final: //also validate can be final
        INSERT into completed_stories (...)
    else:
        UPDATE s (title<-title, body <cat> append_data)
}

[TylerWasniowski]

Thank you Jon for your idea and details.

We have stopped using the sessions table, and now only have one table for stories. The columns for stories are: id, title, body, locked (boolean). We are planning on adding a column locked_until (time). The locked_until column can replace the locked column (we can just set locked_until to null when a story snippet is submitted). We are also planning on adding a votes column as you suggested.