Click here to Skip to main content
15,893,266 members
Search within:


Articles / Database Development / SQL Server
 

A scripted SQL query generation framework with IDE: SQLpp (v1.4)

Rate me:
Please Sign up or sign in to vote.
4.98/5 (47 votes)
12 Sep 200311 min read 414.1K   5.4K   133  
A helper framework for generation of SQL queries in C++ and Lua
 
#include <iostream>

#include <sqlpp/sqlpp.hpp>
#include <sqlpp/test/test_predicate.hpp>
#include <sqlpp/test/test_db.hpp>

namespace sqlpp{
namespace test{

using namespace sqlpp::queries;
using namespace sqlpp::expressions;

std::ostream& test_field_value_comp(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_field_value_comp:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	
		// adding fields
		expression_shared_ptr qcln = q->add_field( qc->get_field("ClientLastName") );

		// set where predicate
		q->set_where( qcln == "Elvis ' Escapes" );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( qcln > 10 );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( qcln < 1.0 );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( qcln >= 10 );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( qcln <= 1.0 );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( qcln != "..." );
		return out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;

	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_field_like(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_field_value_comp:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	
		// adding fields
		expression_shared_ptr qcln = q->add_field( qc->get_field("ClientLastName") );
		// set where predicate
		q->set_where( like( qcln, "El%") );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_field_between(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_field_value_comp:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	
		// adding fields
		expression_shared_ptr qcln = q->add_field(qc->get_field("ClientLastName") );
		// set where predicate
		q->set_where( between( qcln )->set(10, 50) );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;

		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_binary_op(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_binary_op:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	
		// adding fields
		expression_shared_ptr qcln = q->add_field(qc->get_field("ClientLastName") );
		// set where predicate
		q->set_where( and( qcln == "Elvis Presley", qcln != "Johnny Begood") );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( or( qcln == "Elvis Presley", qcln != "Johnny Begood") );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;

		q->set_where( and( not( qcln == "Elvis Presley" ), and( qcln != "Johnny Begood", qcln > 10) ));
		return out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_field_field_comp(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_field_value_comp:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	
		query_table_shared_ptr qo = q->add_table( db_->get_table("Orders"), "O" );	
		// adding fields
		// set where predicate
		q->set_where( equal( qo->get_field("ClientID"), qc->get_field("ClientID") ) );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where( not_equal( qo->get_field("ClientID"), qc->get_field("ClientID") ) );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
/*		q->set_where(  qo->get_field("ClientID") < qc->get_field("ClientID")  );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where(  qo->get_field("ClientID") <= qc->get_field("ClientID")  );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where(  qo->get_field("ClientID") > qc->get_field("ClientID")  );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
		q->set_where(  qo->get_field("ClientID") >=qc->get_field("ClientID")  );
		out_ <<q->get_sql()<<std::endl
			 <<"----------"<<std::endl<<std::endl;
*/
		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_is_null(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_is_null:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	

		q->set_where( is_not_null( qc->get_field("ClientID") ) );		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;

		q->set_where( is_null( qc->get_field("ClientID") ) );		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;

		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_in(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_in:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	

		std::vector<string_type> v_s;
		v_s.push_back("John");
		v_s.push_back("Jim");
		v_s.push_back("Martin");

		q->set_where( in(qc->get_field("ClientFirstName"))->set(v_s.begin(), v_s.end() ) );		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;

		select_query_shared_ptr q2=db_->create_query();
		query_table_shared_ptr qe2 = q2->add_table( db_->get_table("Employees"), "E" );
		q2->set_where(qe2->get_field("EmployeeID") == 10 );

/*		q->set_where( in(qc->get_field("ClientFirstName"), to_expression(q2) ) );		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;	
*/
		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_set(std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_sets:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
		query_table_shared_ptr qo = q->add_table( db_->get_table("Orders"), "O" );	
		q->set_where(qo->get_field("OrderDate") > "10-09-2002" );

		select_query_shared_ptr q2=db_->create_query();
		query_table_shared_ptr qo2 = q2->add_table( db_->get_table("Orders"), "O2" );
		q2->set_where(qo2->get_field("OrderID") == 10 );

		select_query_base_shared_ptr q_set;

		q_set = except( q, q2 );
		out_<<q_set->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;	

		q_set = intersect( q, q2 );
		out_<<q_set->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;	

		q_set = union_( q, q2, true );
		out_<<q_set->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;	

		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
};

std::ostream& test_case( std::ostream& out_, database_shared_ptr db_)
{
	try
	{
		if (!db_)
			throw std::exception("database pointer is null");

		out_<<"test_case:"<<std::endl
			<<"----------"<<std::endl;

		select_query_shared_ptr q=db_->create_query();
	
		// adding a table
		query_table_shared_ptr qc = q->add_table( db_->get_table("Clients"), "C" );	

		q->set_where( 
			case_( qc->get_field("ClientID") )
			->when( to_expression(10), qc->get_field("ClientID") ) 
			->else_( qc->get_field("ClientLastName") )
			);		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;

		q->set_where( 
			case_()
			->when( is_null( qc->get_field("ClientID") ), qc->get_field("ClientID") ) 
			->when( is_null( qc->get_field("ClientLastName") ), qc->get_field("ClientFirstName") ) 
			->else_( qc->get_field("ClientLastName") )
			);		
		out_<<q->get_sql()<<std::endl
			<<"----------"<<std::endl<<std::endl;

		return out_;
	}
	catch(std::exception& e)
	{
		std::cout<<"exception: "<<e.what()<<std::endl;
		return out_;
	}
}


};
};

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Jonathan de Halleux
Engineer
United States United States
Jonathan de Halleux is Civil Engineer in Applied Mathematics. He finished his PhD in 2004 in the rainy country of Belgium. After 2 years in the Common Language Runtime (i.e. .net), he is now working at Microsoft Research on Pex (http://research.microsoft.com/pex).

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 22 Jul 2003

Article Copyright 2003 by Jonathan de Halleux
Everything else Copyright © CodeProject, 1999-2024

Web03 2.8:2024-04-02:1