www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - Automatic minimally sized SELECT clauses in SQL using D templates.

reply Chad J <chadjoan __spam.is.bad__gmail.com> writes:
I'd almost be sort of surprised if someone here hasn't thought of this 
already, but I'll drop it out here anyways.

This is a proof-of-concept program I wrote to do a query with the 
convenience of a "SELECT * FROM ...", but without actually pulling every 
column in the database.

Additionally, it generates this "Entity" type that has a minimal amount 
of memory allocated for holding the data.  I can imagine passing this 
entity type into templated functions to do nontrivial calculations. 
Perhaps use of metaprogramming like this can be used to dodge some of 
the object-relational impedance mismatch problems?

(I'm pretty sure I ran into a bunch of compiler bugs while doing this. 
Unfortunately it is difficult for me to isolate them right now.)

Here's the code:

import std.stdio;
import std.array;

class Query(string queryText)
{
	struct Entity(string queryText)
	{
		private static size_t bufSize = 0;
		private static size_t[string] m_offsets;
		private static size_t registerField(T)(string varName)
		{
			auto offset = bufSize;
			m_offsets[varName] = offset;
			bufSize += T.sizeof;
			return offset;
		}
		
		static  property const(size_t[string]) offsets()
		{
			return m_offsets;
		}
		
		private void[] buf;
		
		private struct registrator(T,string varName)
		{
			static offset = 0;
			
			static this()
			{
				offset = registerField!T(varName);
			}
		}
		
		private struct Transformationizer(T)
		{
			union
			{
				ubyte[T.sizeof] generic;
				T specific;
			}
		}
		
		T get(T,string varName)()
		{
			Transformationizer!(T) t;
			registrator!(T,varName) r;
			auto i1 = r.offset;
			
			t.generic = cast(ubyte[])
				buf[i1 .. i1 + T.sizeof];
			return t.specific;
		}
		
		
		static opCall()
		{
			Entity e;
			e.buf = new void[bufSize];
			//writefln("m_offsets = %s\n", e.m_offsets);
			return e;
		}
		
		string toString()
		{
			return std.string.format(
				"Entity.offsets = %s", offsets);
		}
		
		~this()
		{
			delete buf;
		}
	}
	
	alias Entity!queryText EntityT;
	string finalQuery;
	
	// This is very stub-like.
	int opApply(int delegate(ref EntityT) dg)
	{
		auto e = EntityT();
		dg(e);
		return 0;
	}
	
	this()
	{
		char[] columns = new char[0];
		foreach( fieldName; EntityT.offsets.keys )
		{
			columns ~= fieldName ~ ",";
		}
		columns = columns[0..$-1];
		
		finalQuery = std.array.replace(
			queryText,
			"ONLY_NEEDED_COLUMNS",
			columns);
	}
}

void someNonTrivialCalculation(T)(T contact)
{
	auto country = contact.get!(string,"country");
}

void main()
{
	scope query = new Query!(
		"SELECT ONLY_NEEDED_COLUMNS "
		"FROM contacts c "
		"WHERE c.country = 'US' "
		"AND   c.sales > 10000;")();
	
	foreach(entity; query)
	{
		writefln("%s",entity);
		auto email_addy = entity.get!(string,"email");
		auto full_name  = entity.get!(string,"name");
		someNonTrivialCalculation(entity);
		//Email.spam(email_addy, full_name,
			"We have this great new offer for you!");
	}
	
	writefln("Final query:\n  %s", query.finalQuery);
	writeln("done.");
}

-------------------------------------------------------------
My output:

Entity.offsets = [email:0,country:16,name:8]
Final query:
   SELECT email,country,name FROM contacts c WHERE c.country = 'US' AND 
   c.sales > 10000;
done.
Mar 04 2012
parent reply "Marco Leise" <Marco.Leise gmx.de> writes:
Am 04.03.2012, 19:58 Uhr, schrieb Chad J <chadjoan __spam.is.bad__gmail.com>:

 I'd almost be sort of surprised if someone here hasn't thought of this
 already, but I'll drop it out here anyways.

 This is a proof-of-concept program I wrote to do a query with the
 convenience of a "SELECT * FROM ...", but without actually pulling every
 column in the database.

Yes, there is an existing D library that use CTFE to generate lazy evaluated data structures from SQL queries. And although I was very impressed by the solution and how it integrated with foreach I forgot the name and URL. (Haven't been using SQL since a while.) Maybe someone else remembers. I usually find it helpful to look at other peoples' solution to the same problem, since D offers so many concepts and you easily miss some good stuff.
Mar 08 2012
parent Chad J <chadjoan __spam.is.bad__gmail.com> writes:
On 03/08/2012 07:00 AM, Marco Leise wrote:
 Am 04.03.2012, 19:58 Uhr, schrieb Chad J
 <chadjoan __spam.is.bad__gmail.com>:

 I'd almost be sort of surprised if someone here hasn't thought of this
 already, but I'll drop it out here anyways.

 This is a proof-of-concept program I wrote to do a query with the
 convenience of a "SELECT * FROM ...", but without actually pulling every
 column in the database.

Yes, there is an existing D library that use CTFE to generate lazy evaluated data structures from SQL queries. And although I was very impressed by the solution and how it integrated with foreach I forgot the name and URL. (Haven't been using SQL since a while.) Maybe someone else remembers. I usually find it helpful to look at other peoples' solution to the same problem, since D offers so many concepts and you easily miss some good stuff.

Cool, thanks for pointing that out.
Mar 08 2012