LoadingSDSS

Input Data:

• The set of SQL scripts defining the SDSS schema
• The set of CSV files of some sample data

The set of SQL sripts defining the tables which I ported:

• FrameTables.sql
• NeighborTables.sql
• RegionTables.sql
• TilingTables.sql
• MetadataTables.sql (not ported since we use different metadata structure)
• PhotoTables.sql
• SpectroTables.sql
• Views.sql

The notices which I created when porting:

• the procedure spSetDefaultFileGroup is defined in FileGroups.sql and is used for the data partionining (probably it should translated to the tablespace)
• I'm removing every case where there is a check whether the table exists and dropping of it.
• convert tinyint → smallint
• in the table ObjMask the columns "cmin" , "cmax" were translated to "_cmin", "_cmax" to not collide with Postgres system column names
• Remove GO and put ';' at the ends of the statements
• Change PRINT to \echo
• Remove the square brackets from some column names
• Translate the type 'varbinary(1000)' to 'bytea'
• Created the special type "image" to allow loading of MS SQL "image" types. For that purpose I also have changed the default value of the image columns from the MSSQL 0x1111 to PG '1111'
• Change the 'identity' and identity(1,1) type to 'serial'
• In the table definitions, for bit columns, we should replace 'default(0)' by 'default(0::bit)' since Pg do not cast int to bit … ?
• In the FrameTables.sql I commented out several procedures…, but some of them can probably be enabled
• The MetaDataTables.sql should not probably changed (we need our own metadata, but probably we'll need at least get the data for these tables to convert in our format)
• in Views.sql I temporary commented out the "Columns" view since we don't need DBColumn table ?

---

The image type C reading functions:

#include "postgres.h"
#include "executor/spi.h"
#include "funcapi.h"      
#include "utils/lsyscache.h"
#include "fmgr.h"

typedef struct 
{
        int4 length;
        unsigned char data[1];
} image;

PG_FUNCTION_INFO_V1(image_in);
Datum image_in(PG_FUNCTION_ARGS)
{
        unsigned char *in = PG_GETARG_CSTRING(0);//"AABBCCDDEE1122";//
        int i = 0, out_len;
        const unsigned char convert[256] = {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,0,10,11,12,13,14,15,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
        
        //elog(WARNING,"IN_STRING: %s", in);
        /* Compute the length */
        while (in[++i]);
        out_len = i / 2;
        //elog(WARNING,"IN_LENGTH: %d", i);

        image *im = (image *) palloc(VARHDRSZ + out_len);
        memset(im, 0, VARHDRSZ + out_len);
        unsigned char *out_data = im->data;
        im->length = out_len + VARHDRSZ;

        for(i = 0; i < out_len; i++)
        {
                out_data[i] = convert[in[i * 2]] * ((unsigned char) 16) +
                        convert[in[i * 2 + 1]];
//              elog(WARNING,"%d",out_data[i]);
        }
        
        PG_RETURN_POINTER(im);
}


PG_FUNCTION_INFO_V1(image_out);
Datum image_out(PG_FUNCTION_ARGS)
{
        const char convert[] = {48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70};
        int i;
        image *im = (image *) PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
        unsigned char *data_in = im->data;
        int len = im->length - VARHDRSZ;

        //elog(WARNING, "OUT_LENGTH: %d", len);

        unsigned char *data_out = palloc(2 * len * sizeof(char) + 1);

        for(i = 0; i < len ; i++)
        {
                data_out[i * 2] = convert[data_in[i] / 16];
                data_out[i * 2 + 1] = convert[data_in[i] % 16];
        }
        data_out[2 * len ] = 0;
        PG_FREE_IF_COPY(im, 0);
        PG_RETURN_CSTRING(data_out);
}

The image type SQL definition:

DROP TYPE image CASCADE;

CREATE or REPLACE FUNCTION image_in(cstring)
        RETURNS image
        AS '/home/math/sdss_db/image_type/image_type.so'
        LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION image_out(image)
        RETURNS cstring
        AS '/home/math/sdss_db/image_type/image_type.so'
        LANGUAGE C IMMUTABLE STRICT;
CREATE TYPE image (
        INPUT = image_in,
        OUTPUT = image_out,
        INTERNALLENGTH = -1,
        STORAGE = external
);