structure Link :> LINK =
struct

open Util Sql Init

type link = {id : int, usr : int, title : string, url : string, descr : string}

fun mkLinkRow [id, usr, title, url, descr] =
    {id = C.intFromSql id, usr = C.intFromSql usr, title = C.stringFromSql title,
     url = C.stringFromSql url, descr = C.stringFromSql descr}
  | mkLinkRow row = rowError ("link", row)

fun lookupLink id =
    mkLinkRow (C.oneRow (getDb ()) ($`SELECT id, usr, title, url, descr
				      FROM Link
			     	      WHERE id = ^(C.intToSql id)`))

fun mkLinkRow' (name :: rest) = (C.stringFromSql name, mkLinkRow rest)
  | mkLinkRow' row = Init.rowError ("user'", row)

fun listLinks () =
    C.map (getDb ()) mkLinkRow' ($`SELECT name, Link.id, usr, title, url, descr
			           FROM Link JOIN WebUser ON usr = WebUser.id
			           ORDER BY title`)

fun listUserLinks usr =
    C.map (getDb ()) mkLinkRow ($`SELECT id, usr, title, url, descr
			          FROM Link
				  WHERE usr = ^(C.intToSql usr)
			          ORDER BY title`)

fun addLink (usr, title, url, descr) =
    let
	val db = getDb ()
	val id = nextSeq (db, "LinkSeq")
    in
	C.dml db ($`INSERT INTO Link (id, usr, title, url, descr)
		    VALUES (^(C.intToSql id), ^(C.intToSql usr), ^(C.stringToSql title), ^(C.stringToSql url), ^(C.stringToSql descr))`);
	id
    end

fun modLink (link : link) =
    let
	val db = getDb ()
    in
	ignore (C.dml db ($`UPDATE Link SET
			    usr = ^(C.intToSql (#usr link)), title = ^(C.stringToSql (#title link)),
			       url = ^(C.stringToSql (#url link)), descr = ^(C.stringToSql (#descr link))
			    WHERE id = ^(C.intToSql (#id link))`))
    end

fun deleteLink id =
    ignore (C.dml (getDb ()) ($`DELETE FROM Link WHERE id = ^(C.intToSql id)`))

end