Relationships
My initial thought was to break down an SQL statement into pieces. Seems like a fairly obvious idea so I am inclined to go with it. Let us assume these basic pieces: Statement Type, SELECT expression, FROM table references, JOIN clauses, INSERT/REPLACE/UPDATE values, WHERE clauses, and lastly and ORDER/GROUP/HAVING type clause. I think, and I stress “think”, that I can break these into smaller pieces. Each can be comprised of a collection of “relationships”, or even more simply as a collection of name/value pairs. Let us see how far I can take this theory.
The following table demonstrates the idea of name/value pair making up a SELECT expression. Each name/value pair represents a “Statement Relationship”. These example relationships should represent all possible combinations of column references (column), simple expressions (expr) (like 1+1), core MySQL functions (func), and full SELECT queries (subselect) that can used within the select expression. A sub-select statement used within the SELECT expression must be a scalar value, returning only a singular value. Each has the option of using an alias for easier parsing of the resulting record set.
| SELECT EXPRESSION | |||||
|---|---|---|---|---|---|
| Type | name | operator | value | alias | EXAMPLE |
| SELECT | value | [opt] | SELECT 23; | ||
| value | * | value | [opt] | SELECT 25 = 25; | |
| column | [opt] | SELECT col1 FROM tbltest; | |||
| column | * | value | [opt] | SELECT col1 = 25 FROM tbltest; | |
| column | * | column | [opt] | SELECT col1 = col2 FROM tbltest; | |
| expr | [opt] | SELECT (1+1); | |||
| expr | * | value | [opt] | SELECT (1+1) < 5; | |
| expr | * | expr | [opt] | SELECT (1+1) < (2+1); | |
| func | [opt] | SELECT REPLACE(‘test’, ‘e’, ‘i’); | |||
| func | * | value | [opt] | SELECT REPLACE(‘test’, ‘e’, ‘i’) = ’tist’; | |
| func | * | expr | [opt] | SELECT SIN(20) < 1; | |
| func | * | func | [opt] | SELECT SIN(20) = COS(20); | |
| subselect | [opt] | SELECT (SELECT col1 FROM tbltest); | |||
| subselect | * | value | [opt] | SELECT (SELECT col1 FROM tbltest) = ‘value’; | |
| subselect | * | expr | [opt] | SELECT (SELECT col1 FROM tbltest) > (1+1); | |
| subselect | * | subselect | [opt] | SELECT (SELECT col1 FROM tbltest) = (SELECT col2 FROM tbltest); | |
[...] I have five sqlRelationship classes that can be passed to the SQL statement class. (not discussed yet) The SELECT expressions, [...]