#ifndef __MiscSQL_h_ #define __MiscSQL_h_ #include #include #include extern const std::string sqlTrue; extern const std::string sqlFalse; std::string sqlAnd(std::vector< std::string > const &expressionList); std::string sqlOr(std::vector< std::string > const &expressionList); std::string sqlIn(std::string left, std::set< std::string > const &right); // There must be at least one query. // // Notes on parentheses: // // Do not put parentheses around the top level query. The following will // fail: // (SELECT * FROM a) // ((SELECT * FROM a) UNION (SELECT * FROM b)) // // They will work just fine if you remove the outermost parentheses. e.g. // SELECT * FROM a // (SELECT * FROM a) UNION (SELECT * FROM b) // // The individual queries in a UNION don't always need parentheses around // them but it doean't hurt. The following show an ambiguous case. // Parentheses would tell you if the limit goes with the second subquery, or // with the entire union query. // SELECT * FROM a UNION SELECT * FROM b LIMIT 10. // // The inputs to this function should not have parentheses around them. // This function will add the parentheses around each query if required. // This function will not add the parentheses if there is only one query, // because that would cause an error, like the first example shown above. // // The outermost query in the result from this function will not be // surrounded by parentheses. It will be suitable for running as is. If you // plan to use the result of this function (or any valid query) in an // EXISTS clause, you need to add parentheses around it. std::string sqlUnion(std::vector< std::string > const &queries); std::string timeTToMysql(time_t time); // Creates only the date portion, like 2014-01-27. If round=false, we truncate // to show the date associated with this date and time. If round=true we round // to midnight. // // If you really are talking about a date and time, set round=false. The date // changes as midnight, period. If you wake up at 6:30am, and you go to bed // at 11:30pm, you will see the same date all day long. // // When you want to store a date it's common to set the time to midnight. // Ideally you could just print the date part and ignore the time part. But // rounding to the nearest midnight before that can help in some cases. For // example, if you say date1 - 7*24*60*60 you probably mean 1 week before // date1. But daylight savings time could break that sometimes. std::string dateToMysql(time_t date, bool round); // The first input is the number of seconds since midnight. This value should // only specify the time, not the date. Optionally, a date can be provided. // The date should be in the standard "yyyy-mm-dd" format. std::string secondsToMysql(int seconds, std::string date = ""); // This converts a time in mysql format to the number of seconds since // midnight. The input can contain "hh:mm:ss" or "yyyy-mm-dd hh:mm:ss". // In the later case, the date is ignored. int mysqlToSeconds(std::string mysqlTime); time_t mysqlToTimeT(std::string const &mysql); #endif