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 (
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 (
1 AS hierarchy,
FROM cte_fk cte
cte.hierarchy + 1 AS hierarchy,
FROM cte_rec cte
JOIN cte_FK cfk ON cfk.object_id = cte.referenced_object_id
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.
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 )
j_compress_ptr pcinfo = cinfo;
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 ) )
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() );
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 in Brazil. It is hard to tell, but this is another one taken with Nikon 10.5mm f/2.8 DX Fisheye.