/***************************************************************************************
*                                                                       
*N  {schobj_table_create.c}  -- demonstrates table creation, modification and indexing
*   
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*P  Purpose:
*    This sample C program demonstrates how to create a table, add/drop a column,
*	 describe a table(retrieve its column definition); and how to create/drop an index
*
*E                                                                 
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* How to compile:: 
*	 Please refer to "Build C Samples" in the SDE sample
*
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* Usage:
*     schobj_table_create {server} {instance} {database} {user} {password} {keyword}
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*X  Legalese:
*
* Copyright © 2007 ESRI
*
* All rights reserved under the copyright laws of the United States and 
* applicable international laws, treaties, and conventions.
*
* You may freely redistribute and use this sample code, with or without 
* modification, provided you include the original copyright notice and use 
* restrictions.  
*
* Disclaimer:  THE SAMPLE CODE IS PROVIDED "AS IS" AND ANY EXPRESS OR IMPLIED 
* WARRANTIES, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
* FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL ESRI OR 
* CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; 
* OR BUSINESS INTERRUPTION) SUSTAINED BY YOU OR A THIRD PARTY, HOWEVER CAUSED 
* AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
* TORT ARISING IN ANY WAY OUT OF THE USE OF THIS SAMPLE CODE, EVEN IF ADVISED 
* OF THE POSSIBILITY OF SUCH DAMAGE.
* 
* For additional information, contact:
* Environmental Systems Research Institute, Inc.
* Attn: Contracts and Legal Services Department
* 380 New York Street
* Redlands, California, 92373
* USA
* 
* email: contracts@esri.com
*
*E
**************************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sdetype.h"
#include "sdeerno.h"

/* Function macros */
#define check_rc_return_on_failure(c,s,rc,f) \
	{if(rc!= SE_SUCCESS) {check_rc_(c,s,rc,f,__LINE__,__FILE__);return SE_FAILURE; }}

/*****************************/
/*  Function Prototypes     */
/****************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) ;
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword);
static void S_get_col_type_name(LONG type,CHAR* tname);
static LONG S_describe_table(SE_CONNECTION  connection, const CHAR *table);
static LONG S_describe_indexes(SE_CONNECTION connection,const CHAR *table);

/************************************/
/*  Global variables Prototypes     */
/***********************************/
static CHAR ROWID_COL[SE_MAX_COLUMN_LEN+1];

LONG main(int argc, char *argv[]){
	CHAR   			*server, 
       	       		*user, 
					*passwd, 
					*database,
					*instance,
					*keyword;
	SE_CONNECTION 	conn;
	SE_ERROR 		error;
	LONG			result;
	CHAR			table[SE_MAX_TABLE_LEN+1];
	SE_COLUMN_DEF	column_def;


	if(argc<7){
		printf("Usage %s <server> <instance> <database> <user> <password> <keyword>\n\n",argv[0]);
		exit(1);
	}
	server=argv[1];
	instance=argv[2];
	database=argv[3];
	user=argv[4];
	passwd=argv[5];
	keyword=argv[6];


	strcpy(ROWID_COL,"ROWID_COL");
	strcpy(table,"SAMPLE_TBLCREATE");

	/*connect to SDE server*/
	result = SE_connection_create( server, instance, database, user, passwd, &error, &conn );
	check_rc_return_on_failure (conn,NULL,result,"SE_connection_create");
	printf("Conn: connected to %s:%s as %s\n",argv[1],argv[2],argv[4]);

	/*create sample table*/
	printf("\nCreating table... \n");
	result=S_create_table(conn,table,keyword);
	if(result!=SE_SUCCESS)
		exit(1);

	/*describe the table*/
	printf("\nCheck table schema...\n");
	result=S_describe_table(conn,table);
	if(result!=SE_SUCCESS)
		return result;

	printf("\nChange the the table schema: drop the BLOB column and add a new CLOB column...\n");
	/*drop the BLOB columns:BLOB_COL*/
	result=SE_table_drop_column(conn,table,"BLOB_COL");
	check_rc_return_on_failure (conn,NULL,result,"SE_table_drop_column");
	/*Add a new CLOB columns:CLOB_COL*/
	strcpy (column_def.column_name,"CLOB_COL");
	column_def.sde_type = SE_CLOB_TYPE;
	column_def.size = 0;
	column_def.decimal_digits = 0;
	column_def.nulls_allowed = TRUE;
	result= SE_table_add_column(conn,table,&column_def);
	check_rc_return_on_failure (conn,NULL,result,"SE_table_drop_column");

	/*describe the table after table schema changed*/
	printf("\nCheck table schema after schema changes...\n");
	result=S_describe_table(conn,table);
	if(result!=SE_SUCCESS)
		return result;

	/*Add a index for INT16_COL column: unique,ascending*/
	printf("\nAdd an index for the INT16_COL column...\n");
	result=SE_table_create_index(conn,table,"INT16_COL",TRUE,TRUE,"INT16_IDX",keyword);
	check_rc_return_on_failure (conn,NULL,result,"SE_table_create_index");

	/*describe the indexes on the table*/
	/*You will see two indexes: one for INT16_COL; and one for the ROWID colmun */
	printf("\nCheck indexes on the table...\n");
	result=S_describe_indexes(conn,table);
	if(result!=SE_SUCCESS)
		return result;

	/*Drop index INT16_IDX on column INT16_COL*/
	printf("\nDrop index INT16_IDX on column INT16_COL...\n");
	result=SE_table_delete_index(conn,table,"INT16_IDX");
	check_rc_return_on_failure (conn,NULL,result,"SE_table_delete_index");

	/*describe the indexes on the table again*/
	/*You will see only one index for the ROWID colmun */
	printf("\nCheck indexes on the table again ...\n");
	result=S_describe_indexes(conn,table);
	if(result!=SE_SUCCESS)
		return result;

	/*delete the sample table*/
	printf("\nDropping the sample table ...\n");
	result=SE_table_delete(conn,table);
	check_rc_return_on_failure( conn, NULL, result, "SE_table_delete" );
	printf("\nTable dropped.\n");

	SE_connection_free(conn);

	return SE_SUCCESS;

}


/***********************************************************************
*
*N  {S_create_table}  -  create a test table with provided specification
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create a sample table with provided specification
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*E
***********************************************************************/
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword)
{
	SHORT               number_of_columns;
	SE_COLUMN_DEF       column_definitions[8];
	LONG                result;
	SE_REGINFO          registration;
	CHAR				reg_desc[128];

	/* Create the base table. */
	number_of_columns = 7;
	strcpy(column_definitions[0].column_name,"STR_COL");
	column_definitions[0].sde_type = SE_STRING_TYPE;
	column_definitions[0].size = 60;
	column_definitions[0].decimal_digits = -1;
	column_definitions[0].nulls_allowed = FALSE;

	strcpy(column_definitions[1].column_name,"INT16_COL");
	column_definitions[1].sde_type = SE_INT16_TYPE;
	column_definitions[1].size = 0;
	column_definitions[1].decimal_digits = 0;
	column_definitions[1].nulls_allowed = TRUE;

	strcpy (column_definitions[2].column_name,"INT32_COL");
	column_definitions[2].sde_type = SE_INT32_TYPE;
	column_definitions[2].size = 0;
	column_definitions[2].decimal_digits = 0;
	column_definitions[2].nulls_allowed = TRUE;

	strcpy (column_definitions[3].column_name,"FLOAT32_COL");
	column_definitions[3].sde_type = SE_FLOAT32_TYPE;
	column_definitions[3].size = 0;
	column_definitions[3].decimal_digits = 0;
	column_definitions[3].nulls_allowed = TRUE;

	strcpy (column_definitions[4].column_name,"FLOAT64_COL");
	column_definitions[4].sde_type = SE_FLOAT64_TYPE;
	column_definitions[4].size = 0;
	column_definitions[4].decimal_digits = 0;
	column_definitions[4].nulls_allowed = TRUE;

	strcpy (column_definitions[5].column_name,"DATE_COL");
	column_definitions[5].sde_type = SE_DATE_TYPE;
	column_definitions[5].size = 0;
	column_definitions[5].decimal_digits = 0;
	column_definitions[5].nulls_allowed = TRUE;

	strcpy (column_definitions[6].column_name,"BLOB_COL");
	column_definitions[6].sde_type = SE_BLOB_TYPE;
	column_definitions[6].size = 0;
	column_definitions[6].decimal_digits = 0;
	column_definitions[6].nulls_allowed = TRUE;

	//delete the table if already existed
	result = SE_table_delete(connection,table);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");
	//create the base table
	result = SE_table_create (connection,table,number_of_columns,
							column_definitions,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create");

	/* Add an SDE maintained rowid */
	result = SE_reginfo_create (&registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_create");
	result = SE_registration_get_info (connection,table,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_get_info");
	result = SE_reginfo_set_creation_keyword (registration,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_creation_keyword");
	strcpy(reg_desc,"BASE TABLE FOR qa_layer_data");
	result = SE_reginfo_set_description (registration,reg_desc );
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_description");
	result = SE_reginfo_set_rowid_column (registration,ROWID_COL,
							   SE_REGISTRATION_ROW_ID_COLUMN_TYPE_SDE);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_rowid_column");
	result = SE_registration_alter (connection,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_alter");

	SE_reginfo_free(registration);

	return(SE_SUCCESS);
}           

static void S_get_col_type_name(LONG type,CHAR* tname )
{
	switch(type){
		case SE_INT16_TYPE:
			strcpy(tname,"SE_INT16_TYPE");
			break;
		case SE_INT32_TYPE:
			strcpy(tname,"SE_INT32_TYPE");
			break;
		case SE_FLOAT32_TYPE:
			strcpy(tname,"SE_FLOAT32_TYPE");
			break;
		case SE_FLOAT64_TYPE:
			strcpy(tname,"SE_FLOAT64_TYPE");
			break;
		case SE_STRING_TYPE:
			strcpy(tname,"SE_STRING_TYPE");
			break;
		case SE_BLOB_TYPE:
			strcpy(tname,"SE_BLOB_TYPE");
			break;
		case SE_DATE_TYPE:
			strcpy(tname,"SE_DATE_TYPE");
			break;
		case SE_SHAPE_TYPE:
			strcpy(tname,"SE_SHAPE_TYPE");
			break;
		case SE_RASTER_TYPE:
			strcpy(tname,"SE_RASTER_TYPE");
			break;
		case SE_XML_TYPE:
			strcpy(tname,"SE_XML_TYPE");
			break;
		case SE_INT64_TYPE:
			strcpy(tname,"SE_INT64_TYPE");
			break;
		case SE_UUID_TYPE:
			strcpy(tname,"SE_UUID_TYPE");
			break;
		case SE_CLOB_TYPE:
			strcpy(tname,"SE_CLOB_TYPE");
			break;
		case SE_NSTRING_TYPE:
			strcpy(tname,"SE_NSTRING_TYPE");
			break;
		case SE_NCLOB_TYPE:
			strcpy(tname,"SE_NCLOB_TYPE");
			break;
	}
}

/***********************************************************************
*
*N  {S_describe_table}  - Describe table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     List the column defintions of a table
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>    ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*E
***********************************************************************/
static LONG S_describe_table(SE_CONNECTION  connection, const CHAR *table){

	LONG			  result,i;
	SHORT			  num_columns;
	SE_COLUMN_DEF	  *column_defs;
	CHAR			  coltype[128];

	result=SE_table_describe(connection,table,&num_columns,&column_defs);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_describe");
	printf("There are %d columns in the table:\n\n",num_columns);
	for(i=0;i<num_columns;i++)
	{
		printf("\t**********************************************************************\n");
		printf("\t%-25s = %s\n","NAME",column_defs[i].column_name);
		S_get_col_type_name(column_defs[i].sde_type,coltype);
		printf("\t%-25s = %s\n","TYPE",coltype);
		printf("\t%-25s = %d\n","SIZE",column_defs[i].size);
		printf("\t%-25s = %d\n","DECIMAL_DIGITS",column_defs[i].decimal_digits);
		printf("\t%-25s = %s\n","NULL_ALLOWED",column_defs[i].nulls_allowed?"YES":"NO");
		if(column_defs[i].row_id_type==SE_REGISTRATION_ROW_ID_COLUMN_TYPE_SDE)
			printf("\t%-25s = %s\n","ROWID_TYPE","SE_REGISTRATION_ROW_ID_COLUMN_TYPE_SDE");
		else if(column_defs[i].row_id_type==SE_REGISTRATION_ROW_ID_COLUMN_TYPE_USER)
			printf("\t%-25s = %s\n","ROWID_TYPE","SE_REGISTRATION_ROW_ID_COLUMN_TYPE_USER");
		else
			printf("\t%-25s = %s\n","ROWID+TYPE","NOT A ROWID");
	}
	SE_table_free_descriptions(column_defs);

	return (SE_SUCCESS);

}

/***********************************************************************
*
*N  {S_describe_indexes}  - Describe the indexes of a table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     List the index properties of a table
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>    ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*E
***********************************************************************/
static LONG S_describe_indexes(SE_CONNECTION  connection, const CHAR *table){

	LONG			  result,i,j;
	SHORT			  num_indexes;
	SE_INDEX_DEF	  *idx_defs;

	result=SE_table_describe_indexes(connection,table,&num_indexes,&idx_defs);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_describe_indexes");
	printf("There are %d indexes in the table:\n\n",num_indexes);
	for(i=0;i<num_indexes;i++)
	{
		printf("\t**********************************************************************\n");
		printf("\t%-25s = %s\n","INDEX_NAME",idx_defs[i].index_name);
		printf("\t%-25s = ","COLUMNS");
		for(j=0;j<idx_defs[i].num_columns;j++)
			printf("%s[%s] ",idx_defs[i].column_name[j],idx_defs[i].ascending[j]?"ASC":"DESC");
		printf("\n");
		printf("\t%-25s = %s\n","UNIQUE",idx_defs[i].unique?"YES":"NO");
	}
	SE_table_free_index_descriptions(num_indexes,idx_defs);

	return (SE_SUCCESS);

}

/***********************************************************************
*
*N  {check_rc_}  -  check the returned code
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Check returned sde error code and print the error messages.
***********************************************************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) 
{

	SE_ERROR	error;
	CHAR		error_string [SE_MAX_MESSAGE_LENGTH];
	LONG		temp_rc=SE_FAILURE;


	if ((rc != SE_SUCCESS) && (rc != SE_FINISHED)) 
	{
		error_string[0] = '\0';
		SE_error_get_string (rc, error_string);

	    printf ("%s encountered a %d error:\"%s\" \nat line %ld in file %s\n",comment, rc,error_string, line_no, file_name);

		/*Print extended error info, if any */
		if ((SE_DB_IO_ERROR == rc) | (SE_INVALID_WHERE == rc) | (SE_SSA_FUNCTION_ERROR == rc)) 
		{
			if (NULL != Stream) 
			{
				/* Assume this is a stream error */
				temp_rc = SE_stream_get_ext_error (Stream, &error);
			}else if (NULL != Connection) {
				/*Assume this is a connection error */
				temp_rc = SE_connection_get_ext_error (Connection, &error);
			}
			if (SE_SUCCESS == temp_rc)
			{
					printf("Extended error code: %d, extended error string:\n%s\n",
					error.ext_error, error.err_msg1);
			}
		} /*End SE_DB_IO_ERROR */

	} 
}