T rolodex

    # - -- --- ----- -------- -------------
    return
}

proc ::m::db::SETUP-201810092200 {} {
    debug.m/db {}
    # Added github VCS manager

    D m::db
    # - -- --- ----- -------- -------------
    T^ version_control_system
    >+ 'github' 'GitHub'

    return
}

proc ::m::db::SETUP-201810111600 {} {
    debug.m/db {}
    # Added column `created` to `store_times`
    #
    # Notes on the recorded times:
    #
    # - Invariant: changed <= updated
    #   Because not every update causes a change.
    #
    # - Invariant: created <= changed
    #   Because a change can happen only after we have store
    #
    # - (created == changed)
    #   -> Never seen any change for this store.
    #
    # Overall
    #		created <= changed <= updated

    D m::db
    # - -- --- ----- -------- -------------
    C store    INTEGER  NOT NULL  ^store PRIMARY KEY
    C created  INTEGER  NOT NULL
    C updated  INTEGER  NOT NULL
    C changed  INTEGER  NOT NULL
    < store_times  store updated updated changed
    #                    ^ use last update as fake creation

    return
}

proc ::m::db::SETUP-201810121600 {} {
    debug.m/db {}
    # Added mail configuration to the general state table

    set h {This is a semi-automated mail by @cmd@, on behalf of @sender@.}

    D m::db
    # - -- --- ----- -------- -------------
    T^ state

    #                           -- Debugging
    > 'mail-debug'  '0'         ;# Bool. Activates low-level debugging in smtp/mime

    #                           -- SMTP configuration
    > 'mail-host'   'localhost' ;# Name of the mail-relay host to talk to
    > 'mail-port'   '25'        ;# Port where the mail-relay host accepts SMTP
    > 'mail-user'   'undefined' ;# account accepted by the mail-relay host

    #                            # Note: Template processing happens after the content
    #                            # is assembled, i.e. affects header and footer.

    return
}

proc ::m::db::SETUP-201810131603 {} {
    debug.m/db {}
    # Add tables for rejection mail content
    # (submission processing)

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C name      TEXT    NOT NULL UNIQUE
    C automail  INTEGER NOT NULL
    C isdefault INTEGER NOT NULL
    C text      TEXT    NOT NULL
    T reply

    set sm "It is spam"
    set om "It is off-topic here"
    set rm "It was intentionally removed before and we will not add it again"

    >+ 'spam'     0 1 '$sm' ;# default reason
    >+ 'offtopic' 1 0 '$om'
    >+ 'removed'  1 0 '$rm'

    return
}

proc ::m::db::SETUP-201810141600 {} {
    debug.m/db {}
    # Add tables for external submissions
    # - submissions
    # - rejected submissions (for easy auto-rejection on replication)

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C url       TEXT NOT NULL UNIQUE
    C email     TEXT NOT NULL
    C submitter TEXT
    C sdate     INTEGER NOT NULL
    T submission
    X sdate

    I+
    C url    TEXT NOT NULL UNIQUE
    C reason TEXT NOT NULL
    T rejected

    return
}

proc ::m::db::SETUP-201810311600 {} {
    debug.m/db {}
    # Added column `size_kb` for store size to `store`.

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C vcs     INTEGER  NOT NULL  ^version_control_system
    C mset    INTEGER  NOT NULL  ^mirror_set
    C size_kb INTEGER  NOT NULL
    U vcs mset
    < store  id vcs mset '0'

    package require m::store
    m::store::InitialSizes
    return
}

proc ::m::db::SETUP-201811152300 {} {
    debug.m/db {}
    # Added site configuration to the general state table

    D m::db
    # - -- --- ----- -------- -------------
    T^ state

    #                           -- Debugging
    > 'site-active'   '0'              ;# Site status (active or not)
    > 'site-store'    '~/.mirror/site' ;# Location where website is generated
    > 'site-mgr-mail' ''               ;# Mail address of the site manager
    > 'site-mgr-name' ''               ;# Name of the site manager
    > 'site-title'    'Mirror'         ;# Name of the site
    > 'site-url'      ''               ;# The url the site will be published at

    return
}

proc ::m::db::SETUP-201811162301 {} {
    debug.m/db {}
    # Added more site configuration to the general state table

    D m::db
    # - -- --- ----- -------- -------------
    T^ state
    #                           -- Debugging
    > 'site-logo' '' ;# Path or url to the site logo.

    return
}

proc ::m::db::SETUP-201811202300 {} {
    debug.m/db {}
    # Added flag 'active' to repository.
    # Default: yes.

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C url    TEXT     NOT NULL  UNIQUE
    C vcs    INTEGER  NOT NULL  ^version_control_system
    C mset   INTEGER  NOT NULL  ^mirror_set
    C active INTEGER  NOT NULL
    < repository  id url vcs mset '1'
    X vcs mset

    return
}

proc ::m::db::SETUP-201811212300 {} {
    debug.m/db {}
    # Added `hg` to the set of supported VCS.

    D m::db
    # - -- --- ----- -------- -------------
    T^ version_control_system
    >+ 'hg' 'Mercurial'

    return
}

proc ::m::db::SETUP-201811272200 {} {
    debug.m/db {}
    # Added optional columns `vcode` and `description` to the
    # submissions table. Initialized to empty. Further dropped unique
    # requirement from url, allowing multiple submissions of the same,
    # enabling fixing of description, vcode. Added index instead.

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C url         TEXT NOT NULL
    C vcode       TEXT
    C description TEXT
    C email       TEXT NOT NULL
    C submitter   TEXT
    C sdate       INTEGER NOT NULL
    < submission  id url '' '' email submitter sdate
    X sdate
    X url

    return
}

proc ::m::db::SETUP-201811282200 {} {
    debug.m/db {}
    # Added special column `session` to the submissions
    # table. Initialized to a value the other parts (cli, CGI) will
    # not generate.  Made url + session unique, i.e. primary key.  A
    # session is allowed to overwrite its submissions, but not the
    # submissions of other sessions.

    D m::db
    # - -- --- ----- -------- -------------
    I+
    C session     TEXT NOT NULL
    C url         TEXT NOT NULL
    C vcode       TEXT
    C description TEXT
    C email       TEXT NOT NULL
    C submitter   TEXT
    C sdate       INTEGER NOT NULL
    U session url
    < submission  id ':lock:' url vcode description email submitter sdate
    X sdate
    X url

    return
}

proc ::m::db::SETUP-201811282200 {} {
    debug.m/db {}
    # Added sync helper table.
    # Remember all submissions handled locally (accepted or rejected),
    # for deletion from the CGI site database on next sync. Note that
    # we only need the key information, i.e. url + session id.

    D m::db
    # - -- --- ----- -------- -------------
    C session     TEXT NOT NULL
    C url         TEXT NOT NULL
    U session url
    T submission_handled

    return
}

# # ## ### ##### ######## ############# #####################
return

    < submission  \
	id session url vcode description email submitter when_submitted
    X when_submitted
    X url

    return
}

proc ::m::site::SETUP-201812041400 {} {
    debug.m/db {}
    # Drop table `sync`, not used

    D m::site
    / sync
    
    return
}

# # ## ### ##### ######## ############# #####################
return

	FROM   rejected
	WHERE  url = :url
    }]
}

proc ::m::submission::accept {submission} {
    debug.m/submission {}
    m db eval {
	-- Phase I. Copy key information of the processed submission
	--          into the sync helper table
	INSERT
	INTO   submission_handled
	SELECT session
	,      url
	FROM   submission
	WHERE  id = :submission
	;
	--
	-- Phase II. Remove processed submission from the main table
	--
	DELETE
	FROM submission
	WHERE id = :submission
    }
    return
}

proc ::m::submission::reject {submission reason} {
    debug.m/submission {}
    set url [m db onecolum {
	SELECT url
	FROM  submission
	WHERE id = :submission
    }]
    m db eval {
	-- Phase I. Copy key information of the processed submission
	--          into the sync helper table
	INSERT
	INTO   submission_handled
	SELECT session
	,      url
	FROM   submission
	WHERE  id = :submission
	;
	--
	-- Phase II. Add rejection information to the associated table
	--
	INSERT OR REPLACE
	INTO rejected
	VALUES ( NULL, :url, :reason )
	;
	--
	-- Phase III.  Remove processed submission from the main table
	--
	DELETE
	FROM  submission
	WHERE id = :submission
    }
    return
}

debug prefix db/setup {[debug caller] | }

# # ## ### ##### ######## ############# #####################
## Definition

namespace eval db::setup {
    namespace import ::db::track::it ; rename it track
    namespace export D C U T T^ I I+ > >+ X < /
}

namespace eval db {
    namespace export setup
    namespace ensemble create
}

    append def "CREATE TABLE $table\n( "
    append def [join [Flat [PadR 1 [PadR 0 $thecols]]] "\n, "]
    append def "\n);"
    set thecols {}
    R $def
    return
}

proc db::setup::/ {table} {
    debug.db/setup {}
    R "DROP TABLE $table"
    return
}

proc db::setup::T^ {table} {
    debug.db/setup {}
    variable thetable $table
    return
}

	file rename $stage $serve
    }
    return
}

proc ::m::web::site::Sync {} {
    debug.m/web/site {}

    # Data flows
    # - Main
    #   - mset_pending			local, no sync
    #   - reply				local, no sync
    #   - rolodex			local, no sync
    #   - schema			local, no sync
    #
    #   - mirror_set			[1] join/view pushed to site
    #   - name				[1] store_index, total replacement
    #   - repository			[1]
    #   - store				[1]
    #   - store_times			[1]
    #   - version_control_system	[1]
    #
    #   - rejected			push to site rejected, total replacement
    #   - submission			pull from site (insert or update)
    #   - submission_handled		push to site, deletions in submission
    #
    # - Site
    #   - cache_desc	local, no sync
    #   - cache_url	local, no sync
    #   - schema	local, no sync
    #
    #   - rejected	pulled from main rejected, total replacement
    #   - store_index	pulled from main [1], total replacement
    #
    #   - submission	pulled deletions from main (submission_handled)
    #			push remaining to main (insert or update)
    
    FillIndex
    FillRejected
    SyncSubmissions
    return
}

proc ::m::web::site::SyncSubmissions {} {
    debug.m/web/site {}

    # Syncing the submissions is easier than originally
    # thought. Because the flow is more restricted than thought, due
    # to the use of sessions.

    # 1. Submissions from CGI flow through site to main. Only
    #    deletions flow back, as the cli handles them in main.
    
    # 2. Submissions done in main, via the cli, have their own format
    #    for session identifiers which cannot overlap with sessions
    #    from the CGI. As such there is no need to push them to site,
    #    CGI will has no use for them when looking for pre-existing
    #    submissions. Anything needed there comes into site through
    #    the index and rejection tables.
    
    DropHandledSubmissions
    GetNewSubmissions
    return
}

proc ::m::web::site::GetNewSubmissions {} {
    debug.m/web/site {}
    
    # Phase II of syncing submissions between main and site.

    # Iterate over all the submissions in site. Update the entries in
    # main, or create new entries for them. It is the same logic
    # mirror-submit uses for the site database to distinguish and
    # perform add or edit (insert / update). Except this crosses two
    # databases.

    m site eval {
	SELECT session
	,      url
	,      vcode
	,      description
	,      email
	,      submitter
	,      when_submitted
	FROM submission
    } {
	if {[m db onecolumn {
	    SELECT count (*)
	    FROM  submission
	    WHERE url     = :url
	    AND   session = :session
	}]} {
	    # exists, update
	    m db eval {
		UPDATE submission
		SET vcode       = :vcode
		,   description = :desc
		,   email       = :email
		,   submitter   = :submitter
		,   sdate       = :when_submitted
		WHERE url     = :url
		AND   session = :session
	    }
	} else {
	    # not known, insert
	    m db eval {
		INSERT
		INTO submission
		VALUES ( NULL, :session, :url, :vcode, :desc, :email, :submitter, :when_submitted )
	    }
	}
    }
    
    return
}

proc ::m::web::site::DropHandledSubmissions {} {
    debug.m/web/site {}

    # Phase I of syncing submissions between main and site.

    # Iterate over all the submissions marked as handled in main and
    # remove them from site. From the main helper table also.

    m db eval {
	SELECT url
	,      session
	FROM submission_handled
    } {
	m site eval {
	    DELETE
	    FROM submission
	    WHERE url     = :url
	    AND   session = :session
	}
    }

    m db eval {
	DELETE
	FROM submission_handled
    }
    
    return
}

proc ::m::web::site::FillRejected {} {
    debug.m/web/site {}

    # Copy current state of url rejections from main to site database.
    # Implemented as `delete all old ; insert all new`.
    
    m site eval { DELETE FROM rejected }
    
    m db eval {
	SELECT url
	,      reason
	FROM rejected
    } {
	m site eval {
	    INSERT
	    INTO rejected
	    VALUES ( NULL, :url, :reason )
	}
    }
    return
}

proc ::m::web::site::FillIndex {} {
    debug.m/web/site {}

    # Copy current state of known stores and remotes from main to site
    # database. Implemented as `delete all old ; insert all new`.

    m site eval { DELETE FROM store_index }

    # m store search '' (inlined, simply all)
    m db eval {
	SELECT S.id      AS store
	,      N.name    AS mname