<%@ Page Language="c#" %>

<%@ Register TagPrefix="uc1" TagName="Trailer" Src="Trailer.ascx" %>
<%@ Register TagPrefix="uc2" TagName="Header" Src="top.ascx" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="edu.jhu.Cas.Util" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <style type="text/css" media="screen">
        #righty
        {
            position: absolute;
            top: 95;
            left: 182px;
            text-align: left;
            width: 500;
        }
        #lefty
        {
            position: absolute;
            left: 5px;
            top: 95;
            width: 158px;
        }
    </style>
    <link href="cjobs.css" rel="stylesheet" />
    <title>CasJobs Guide</title>
</head>
<body>
    <uc2:Header ID="Header" runat="server" />
    <div id="lefty">
        <h4>
            Help</h4>
        <table cellpadding="3">
            <tr>
                <td>
                    <img src="img/helppic.jpg"></img>
                </td>
            </tr>
            <tr>
                <td>
                    <a href="about.aspx">About</a>
                </td>
            </tr>
            <tr>
                <td>
                    <a href="faq.aspx">FAQ</a>
                </td>
            </tr>
            <!--
            <tr>
                <td>
                    <a href="changelog.aspx">Recent Updates</a></td>
            </tr>
            <tr>
                <td>
                    <a href="<%=OUT_LINKS.SDSS_SQL_TUTORIAL%>">SQL Tutorial</a></td>
            </tr>
            <tr>
                <td>
                    <a href="<%=OUT_LINKS.SDSS_SQL_OPTIMIZE%>">Optimizing Queries</a></td>
            </tr>
-->
            <tr>
                <td>
                    <a href="aqueries.aspx">Advanced Queries</a>
                </td>
            </tr>
            <tr>
                <td>
                    <a href="devel.aspx">Developers</a>
                </td>
            </tr>
        </table>
    </div>
    <div id="righty">
        <a name="intro"></a><h4>
            Introduction</h4>
        <p>
            Welcome to the Catalog Archive Server Jobs System, or CasJobs. This guide assumes
            you have experience with basic SQL syntax. If you'd like to learn more about SQL,
            you can follow the SkyServer link above which has a SQL Tutorial in its Help section,
            but there are also plenty of other <a href="http://www.google.com/search?hl=en&ie=UTF-8&q=sql+tutorial&btnG=Google+Search">
                tutorials on the web</a>. This guide will cover the slightly simpler varient
            of SQL that CasJobs uses.
        </p>
        <a name="register"></a><h4>
            Registration</h4>
        <p>
            Before you can query this system you'll need to <a href="createaccount.aspx">register</a>
            and then <a href="login.aspx">log</a> in. Your queries and their resulting data
            are associated with your account, so don't forget your login! But if lightning strikes,
            skies fall and you've consequently forgot your password it can be emailed to you
            from <a href="forgotpass.aspx">here.</a> Your email may be used to contact you about
            your account, as well as optionally notifing about query completion. Your UserID
            will be used to identify you to the group world; should you choose to participate
            in this please pick a 'nice' UserID. A personal database, or MyDB is also created
            and assigned to your account on registration.
        </p>
        <a name="query"></a><h4>
            Running a Query in CasJobs</h4>
        <p>
            The Query page is the first page you will see once you log
	    into CasJobs.  However, before you run a query, you need to
	    select the 'Context', i.e., the database on which your query
	    will be run.  Please read the following few sections
	    including 'Context and Queues' before you attempt to run a
	    query for the first time, else you may get unexpected errors
	    (e.g. "Invalid object name").
        </p>
        <a name="mydb"></a><h4>
            MyDB</h4>
        <p>
            Your MyDB is a personal database created just for you upon registration. You have
            full privileges in this context; you can create/alter/drop tables, functions and
            stored procedures. It is designed to be a sort of staging area where you refine
            your requested data before eventually extracting it to a local copy in CSV, FITS,
            etc. format. Please note that you can only extract data from MyDB, so any data you
            will eventually want a local copy of must first be put into MyDB.
        </p>
        <a name="into"></a><h4>
            Getting Data Into MyDB</h4>
        <p>
            Getting data into MyDB is easy. Just write your query, then click submit and it
            will automatically create a table using the name from the 'Table' field (directly
            above the query box) in MyDB containing the results of your query. Alternatively,
            you use an 'into' statement in your query; an example of this is below.
        </p>
        <p class="code">
            Select top 1000 objid,ra,dec <font class="accent">into mydb.MyFirstTable</font>
            from photoobj</p>
        <p>
            This will create a table called 'MyFirstTable' in MyDB containing the columns specified
            by the select clause. 'Into' statements are generally placed directly before the
            'from' keyword of a query. If your query doesn't have a 'from', then 'into' just
            goes at the end. An into statement in your query always takes precedence over whatever
            is in the 'Table' field, so if you have both, only the one in the query is used.
            Please note that the 'Table' field is only used when the submit button is clicked;
            queries submitted using the quick method will display results by default instead
            of automatically creating a table.
        </p>
        <a name="import"></a><h4>
            Importing Data Into MyDB</h4>
        <p>
            You can also import your own, custom data into MyDB. This is done from the 'Import'
            page, link located at the top. More info on importing tables is on that page.
        </p>
        <a name="import"></a><h4>
            Downloading Data From MyDB</h4>
        <p>
            Once you've got a table in MyDB, you can then extract and download that table in
            CSV, VOTABLE or FITS file format. This is done from the MyDB page. To do this, click
            MyDB (at the top), then click the table on the left, choose your output type then
            click extract. You can only extract tables in MyDB.
        </p>
        <a name="context"></a><h4>
            Context and Queues</h4>
        <p>
            'Context' is the database on which your query will be run. It is determined by the
            Context drop down list on the query page. So if something like 'DR1' is selected,
            then your query will be run on dr1, if 'MyDB' then MyDB, etc. The 'Queue' selection,
            also a drop down list, will determine the maximum time a query will be permitted
            to run. Every context has at least one queue. If your chosen context has more than
            one queue it is best to choose the queue that is closest to the amount of time you
            think your query will take. Queues are optimized for queries of their time limit,
            so chosing an appropriate queue is generally faster.
        </p>
        <a name="ref"></a><h4>
            Referencing Objects Outside of Context</h4>
        <p>
            For those situations where you'd like to reference data outside of your selected
            context you can prefix the foreign object. If you've been following this guide,
            you've already done this. This query uses a 'MyDB' prefix:
        </p>
        <p class="code">
            Select top 1000 objid,ra,dec into <font class="accent">mydb.</font>MyFirstTable
            from photoobj</p>
        <p>
            So assuming the context of this query was 'DR1', this means that 'MyFirstTable'
            is to be created at 'MyDB', not 'DR1'. Using prefixes, you could rewrite this query
            so it runs in 'MyDB' context, like:
        </p>
        <p class="code">
            Select top 1000 objid,ra,dec into MyFirstTable from <font class="accent">dr1.</font>photoobj</p>
        <p>
            Since the context has changed to 'MyDB', this time the prefix 'dr1' indicates that
            the photoobj table is not 'mydb', but 'dr1'. Context prefix can be used on tables
            and stored procedures, but not functions (see function limitations). Valid context
            prefixes are any that appear in the context drop down list in the query page.
        </p>
        <a name="quick"></a><h4>
            Quick Execution</h4>
        <p>
            If you head over to the <a href="submitjob.aspx">query</a> page, you'll notice there
            are two buttons at the bottom of the query space, one labelled 'submit', the other
            'quick'. These are different types of query execution. 'Quick' is a synchronous
            execution action; it will run your query right away at the currently selected context
            and then return the results right below the query window. It is the quickest way
            to run something and is ideal for quick things like creating functions, procedures,
            top 10's, etc. However there are restrictions on this form of execution; quick queries
            are limited to one minute of execution and can only return about one meg of data
            before they will be cancelled.
        </p>
        <a name="submit"><a name="into"></a>
            Submit Execution</h4>
        <p>
            For queries that exceed 'quick' limitations use the 'Submit' button. 'Submit' is
            an asynchronous execution action; it does not return the results right away. It
            will submit your query to the selected context and queue and will run it as soon
            as a slot in that queue is available. You can check the status of your queries from
            the History page, link located at the top of the page. (must be logged in)
        </p>
        <a name="groups"></a><h4>
            Sharing Data, Using Groups</h4>
        <p>
            Groups are a mechanism for allowing other people to view specified tables in your
            mydb. They are managed from the 'Groups' page, link located at the top. From there
            you can join a group, create a group, or invite other users to join your group.
            Users within a group can see and reference tables that other group members have
            published. You can publish any table in your mydb from the mydb page by clicking
            on the desired table and then clicking publish. You can remove a table from publication
            at any time, but you cannot drop or rename that table until it has been depublished.
            Group tables that you can see are listed at the mydb page under tables. You can
            reference that table in your own query by using the same name you see at the mydb
            page, specifically group.[user].[tablename]. Other group members have read only
            access to your published tables; they cannot change or drop them.
        </p>
        <a name="distributed"></a><h4>
            CasJobs Limitations due to Distributed Execution</h4>
        <p>
            One of the main purposes of the CasJobs service is to provide load-balancing, i.e.
            distribute the queries among different servers that serve the SDSS data. This is
            accomplished using the SQL Server linked server functionality. However, this does
            introduce certain limitations and differences between standard SQL capabilities
            and CasJobs which may be confusing for users. The most prominent example of this
            is the inability to run <b>DROP TABLE</b> and <b>CREATE TABLE</b> commands outside
            of the MyDB context. For example, the following commands will not work in the DR4
            context:
        </p>
        <p class="code">
            DROP TABLE mydb.mytable_21<br>
            CREATE TABLE mydb.mytable_21 (objid int, ra float, dec float)
        </p>
        <p>
            CasJobs will return an error message indicating that there are too many prefixes
            in front of the table name. This error is in fact coming from SQL Server which does
            not allow tables to be dropped or created across linked servers (remotely). The
            difference between <b>INSERT</b> and <b>SELECT ... INTO</b> commands and <b>DROP/CREATE
                TABLE</b> commands is that <u>SQL Server actually permits inserting into a table remotely,
                    but not dropping or creating it</u>.
        </p>
        <p>
            The same applies to calling functions remotely - <u>SQL server does not allow calling
                functions remotely</u>. Hence you cannot call functions outside your current
            context, e.g., you cannot run the command</p>
        <p class="code">
            SELECT DR4.fGetNearestObjidEq(180,-0.5,1.0)
        </p>
        <p>
            in the MyDB context.
        </p>
        <a name="syntax"></a><h4>
            CasJobs Syntax vs. SkyServer/sdssQA Syntax</h4>
        <p>
            If you're used to joining against the target db through the skyserver interface
            with something like TARGDR2..PhotoObj, CasJobs works very much the same way. The
            difference is the prefix you use for CasJobs must be a valid context (listed in
            the context drop down menu). So, if 'dr1' was your current context and you were
            interested in joining with a context called 'targdr2', you could say 'targdr2.photobj',
            assuming you were running from within 'dr1' context. CasJobs will also accept the
            '..' construct. See 'Referencing Objects Outside of Context' for more info.
        </p>
        <h4>
            CasJobs vs. SQL</h4>
        <p>
            CasJobs uses a virtual prefix system rather than the three part prefix system used
            by sql server. A virtual prefix is simply a context name, such as "DR1" or "MyDB".
            So instead of using a multi-part prefix, like:</p>
        <p class="code">
            Select x,y,z from <font class="accent">dr1.dbo.</font>sometable</p>
        or
        <p class="code">
            Select x,y,z from <font class="accent">dr1..</font>sometable</p>
        <p>
            You would simply use the context as the prefix, like this:</p>
        <p class="code">
            Select x,y,z from <font class="accent">dr1.</font>sometable</p>
        <p>
            Just like SQL, you would use prefixes whenever you are referencing something that
            is outside of your current selected context.
        </p>
        <uc1:Trailer ID="Trailer1" runat="server" cvsTag="$Name$" cvsRev="$Revision: 130580 $">
        </uc1:Trailer>
    </div>
</body>
</html>
