T-SQL: Dropping All Tables in Correct Order (Recursively)

June 6th, 2014 No comments

If we want to delete all tables from a larger database schema which uses Declarative Referential Integrity, the order in which the tables are deleted is essential if integrity related errors need to be avoided. Tables which do not have dependencies on other tables need to be deleted first, next the remaining tables without dependencies and so on.

Instead of manually resolving dependencies or using a tool such as SSDT, we can make use of the following recursive construct to determine the correct order of deletion.

 
WITH cte_fk AS (
    SELECT 
        tbl.object_id,
        fkc.referenced_object_id
        FROM sys.TABLES tbl
        LEFT JOIN sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id
        WHERE tbl.TYPE = N'U'
), cte_rec AS (
    SELECT 
        1 AS hierarchy,
        cte.object_id,
        cte.referenced_object_id
        FROM cte_fk cte
    UNION ALL
    SELECT
        cte.hierarchy + 1 AS hierarchy,
        cte.object_id,
        cfk.referenced_object_id
        FROM cte_rec cte
        JOIN cte_FK cfk ON cfk.object_id = cte.referenced_object_id
)
SELECT 
    tbl.name,
    MAX( cte.hierarchy ) AS hierarchy,    
    N'delete from ' + quotename( tbl.name ) + N';' AS delete_statement,
    N'drop table '  + quotename( tbl.name ) + N';' AS drop_statement
    FROM cte_rec cte
    JOIN sys.TABLES tbl ON cte.object_id = tbl.object_id
    GROUP BY tbl.name
    ORDER BY hierarchy DESC, tbl.name ASC
    OPTION( maxrecursion 32767 )

The same rules appyl when referenced table content needs to be deleted.

Using Memory Destination Manager in libjpeg

January 20th, 2014 No comments

To continue my series about libjpeg, here is a full working compression sample which tages an RGB array and compresses it in JPEG format, returning the result inside a byte array.

bool SaveJPEG( unsigned char * const rgb, const unsigned int dwWidth, const unsigned int dwHeight, std::vector<unsigned char> * jpeg, const int quality )
{
    jpeg_error_mgr jerr;
    jpeg_destination_mem_mgr dst_mem;
    jpeg_compress_struct_wrapper cinfo;
    j_compress_ptr pcinfo = cinfo;
 
    jmp_buf setjmp_buffer;
    pcinfo->client_data = &setjmp_buffer;
    pcinfo->err = jpeg_std_error( &jerr );
    pcinfo->err->error_exit = error_exit_custom;
    pcinfo->err->output_message = output_message;
 
    if ( setjmp( setjmp_buffer ) )
        return false;
 
    jpeg_mem_dst( cinfo, &dst_mem );
 
    pcinfo->image_width = dwWidth;
    pcinfo->image_height = dwHeight;
    pcinfo->input_components = 3;    
    pcinfo->in_color_space = JCS_RGB;     
 
    jpeg_set_defaults( cinfo );
    jpeg_set_quality( cinfo, quality, TRUE );
 
    jpeg_start_compress( cinfo, TRUE );
 
    int row_stride = pcinfo->image_width * pcinfo->input_components;
    row_stride = ( ( row_stride + 3 ) / 4 ) * 4;
 
    while ( pcinfo->next_scanline < pcinfo->image_height ) 
    {
        JSAMPROW row_pointer = &rgb[ pcinfo->next_scanline * row_stride ];
        jpeg_write_scanlines( cinfo, &row_pointer, 1 );
    }
 
    jpeg_finish_compress( cinfo );
 
    jpeg->resize( dst_mem.data.size() );
    CopyMemory( jpeg->data(), dst_mem.data.data(), dst_mem.data.size() );
 
    return true;
}

We make use of the custom destination memory manager jpeg_destination_mem_mgr (RAII wrapper class) and the cinfo RAII wrapper jpeg_compress_struct_wrapper.
Both wrappers are used to make sure that there is no memory leakage in case of an encoder crash which results in a longjmp call.

Crab on the Beach

November 20th, 2013 No comments

Crab in Brazil. It is hard to tell, but this is another one taken with Nikon 10.5mm f/2.8 DX Fisheye.

Crab

Categories: Photography Tags: , , ,