Thread: Database Interface

I was thinking if I were to implement this instead of using an already existing library or ORM, how would it turn out in C++.

Unfortunately we don't have the luxury of creating new class members dynamically on the fly in C++ (good if you care about speed). So it looks like one libraries overload the << operator, normal query with variables in between, while another has complex syntax and iterators but more flexibility.

I want a simple, understandable interface. Yet it needs to be flexible enough for all SQL operations (and thus multiple scope levels, recursion, etc.)

So I ask, what are some decent ways of interfacing with databases you've seen in C++?

I wonder if this, as usage, would be a decent direction:

Code:

Linker linker;

Row r = linker
.Select()
	.Fields()
		.Field("*")
		.And()
		.Count("some_category").As("all")
	.End()
	.From()
		.Table("some_table")
		.And()
		.Table("another_table")
	.End()
	.Where()
		.Field("some_field").EqualTo(10)
		.Or()
		.Where()
			.Field("another_field").EqualTo(20)
			.And()
			.Field("this_field").EqualTo("abc")
			.And()
			.Where()
				.Field("some_field").EqualTo(10)
				.Or()
				.Field("another_field").EqualTo(20)
			.End()
		.End()
		.Or()
		.Where()
			.Select()
				.Fields()
					.Field("*")
				.End()
				.Where()
					.Field("this_field").BiggerThan(50)
				.End()
			.End()
		.End()
	.End()		
.Load();

Row would store the query object, the query object would have pointers to the variables so it could be updated/saved/deleted.

Reason I ask is I'm not familiar with existing methods, as I've normally used the low-level libraries and pure sql strings. As well as not wanting to waste more time on something that may be stupid.

Thanks in advance. I know it might be a waste of a topic, but it can't hurt to ask.

Wouldn't "pure SQL strings" be easier to read, and easy to say, load from file?

Originally Posted by laserlight

Wouldn't "pure SQL strings" be easier to read, and easy to say, load from file?

Yes, but that's the purpose of the .EqualTo(10) part. That 10 could be variable. A variable which could change (passed by pointer, not by value). Of course you could just use %1, %2, %3, etc. in your SQL strings (perhaps loaded from file). The interface would be designed to do both, of course. I'm just torn as to what's the best method in the long run, and if this structure is worth implementing or needs tweaks. Part of the advantage of this is also not needing to know the exact SQL syntax, or the differences between MySQL, MsSQL, Oracle, PostgresSQL, etc. and being able to switch between them without changes. You also have programming to check the validity of the query, so you don't publish bad queries (syntax or otherwise) - wasting your time or adding bugs (which was the main reason listed for some database libraries). Then again hardcoding SQL seems like bad oversight. Although you do need to know if you're simply doing an update, select, delete, etc., rows you're receiving, and what data they contain, so all in all abstracting SQL commands isn't going to increase flexibility VERY much as the C++ will usually need updates as well. It just reduces clutter for the most part. In which case you can do a simple #include.

So yeah.. maybe trash it.

Originally Posted by Dae

Yes, but that's the purpose of the .EqualTo(10) part. That 10 could be variable. A variable which could change (passed by pointer, not by value). Of course you could just use %1, %2, %3, etc. in your SQL strings (perhaps loaded from file). The interface would be designed to do both, of course. I'm just torn as to what's the best method in the long run, and if this structure is worth implementing or needs tweaks.

Have you looked into interfaces that allow for prepared statements? In such cases, placeholders exist in the SQL statement string, and then values and/or variables are bound to these placeholder parameters at runtime.

Originally Posted by bithub

Putting SQL in code is a bad idea.

Not necessarily; it depends on the situation.

This is probably where the replies will stop. It'll look like I'm being stubborn, but I'm really just looking for database interface ideas, rather than ditching the idea entirely, unless there's a better argument.

Originally Posted by bithub

Putting SQL in code is a bad idea. Use Stored Procedures.

I know putting SQL in code is a layer issue, but where you call the procedure it's essentially the same issue, and stored procedures are evil. I personally prefer coding SQL in C++ for testing, debugging, small projects, etc. and if I plan on switching database types. The fact is if you change a stored procedure, trigger, external query, hardcoded query, or whatever, you could break your application. Dynamic queries are at least a bit safer, and just as flexible. Infact you could wrap your queries in a separate file each inside its own function with placeholder arguments. Or you could code them in Lua and call them from C++. Either way I think choosing your interface wisely is warranted.

Originally Posted by laserlight

Have you looked into interfaces that allow for prepared statements? In such cases, placeholders exist in the SQL statement string, and then values and/or variables are bound to these placeholder parameters at runtime.

Sorry that's what I meant by %1, %2, %3, etc. Placeholders.

Originally Posted by bithub

For every one person you find that says stored procedures are bad, I bet I can find 1000 that say the opposite

You're probably right. At least a 1:100 ratio or more. I don't really mean to say they are bad, but I do agree with some of the arguments and that it depends on the situation. I think a library that covers both is best. I just want to know what the best usage would look like considering the ORM syntax limitations of C++ compared to other languages.

Originally Posted by bithub

There's nothing uglier than a massive function full of ifs, elses, and switch statements which are used to build a SQL string.

Does it really matter? As long as it does what it's suppose to. It's abstracted, just like all of the ugly file system code. Also note it wouldn't be one massive function with the usage above. It could be either a bunch of conditional statements, or a bunch of node objects linked together (much like a GUI).

Again, not trying to choose one or the other. Just looking at it from a syntax/features point of view.