Page 3 of 4

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 3:26 am
by josh
I think the ability to use object oriented design in your query building far outweighs any portability concerns, you can name your functions the same name as the SQL commands so that your abstraction is not hard to learn. Having the ability to make your queries extensible, inherit from each other, and take advantage of different design patterns far outweighs having the ability to switch to a different RDBMS, that being said there is a lot of stuff SQL does that you would need to abstract to make an implementation that would be considered usable for real projects, its probably best to just use plain SQL and slowly abstract things out as you start noticing the duplication, rather then just go in and try to abstract everything as far as possible for no reason

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 4:25 am
by onion2k
kaisellgren wrote:Let's say you have over 5000 lines of Oracle. Then you need to switch to use PostgreSQL. What happens? (Don't ask why this would happen please... thanks :) )
You copy the 5000 lines of SQL and modify them to work with Postgres.

Are you suggesting that you'd prefer to write those 5000 lines in an intermediate language that's interpreted by a PHP object, and trust that the Oracle and Postgres code that the interpreter produces is clean, fast, and bug free? For a start, the interpreted language wouldn't help with stored procedures or triggers, and in an app with 5000 lines of Oracle SQL I would sincerely hope you're using them, so you'd have to recode those anyway. The intermediate query code in the PHP would mostly be calling the database functions, so what are you really saving by using it?

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 4:28 am
by josh
Plus more then just the SQL syntax differs, if you're switching DBMS you're probably going to do so to take advantage of a feature your old platform did not provide, point is you're going to probably end up re-writing code anyways

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 4:34 am
by onion2k
jshpro2 wrote:Plus more then just the SQL syntax differs, if you're switching DBMS you're probably going to do so to take advantage of a feature your old platform did not provide, point is you're going to probably end up re-writing code anyways
To be fair to the original poster, I think the idea is that the class takes care of that and implements the unavailable stuff in the object. Somehow. I wouldn't want to be the one to code that... 8O

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 6:41 am
by crazycoders
The original idea is to have a single language that allows implementation of all versions of SQL... What that means is to create a new version of SQL like SQL08... that would implement all latest feature on every language in a common way so that the system can then parse the string, build an object view of it and present the appropriate version to the RDBMS in question. That is the original idea (I have been able to chat with Kai for several hours yesterday about this)

The problem is, why provide a string representation of SQL to then parse it into objects (or an array structure) and recreate a valid statement for the RDBMS in use? Simply use a type safe object model at first to save the parsing complexity and the parsing process speed and directly offer your user with a simple object oriented approach to constructing queries.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 6:46 am
by kaisellgren
crazycoders wrote:If you only use cursors, there are no problems. But i wouldnt want to start crunching all the records myself to make a group by (which is anyway SQL92 or SQL99 compliant but for the sake of example) myself or an average. Thats why query builders like LINQ in VS.NET are going to be more and more of a standard.
Do you think any LINQ for PHP out in the Internet is suitable for production level?

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:00 am
by crazycoders
Anything is possible, first is the creation of the technology, next, optimization and extendability of it to several other scenarios and RDBMS. Then, another round of optimization.

My first implementation of LINQ for php (PLUQ) was successful, fast and worked very well and i put it into production in a facebook application. I was able to achieve operations that would require several lines of code in one simple line. Problem is, creation of the queries bloated the screen badly and it was not nice to see, understand or extend.

My goal for PSQLx 2.0 is to be able to:
- Generate a complete object model on the fly
- Provide a type safe development environment
- Provide a memory/sql query component working on the chainability pattern
- Provide a set of extensible data access classes such as recordset, record, stream, database, table, field, view, procedure, function
- Provide a set of DML functions accessible from the previously mentionned classes
- Be able to extend the datamodel classses generated on the fly so that we can create a strong business object layer from it
- Make sure the query builder / ORM generator is RDBMS independant or swapable

It is a long and hard to achieve checklist but if made will make several people completly rethink the way we access data.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:11 am
by onion2k
crazycoders wrote:What that means is to create a new version of SQL like SQL08... that would implement all latest feature on every language in a common way so that the system can then parse the string, build an object view of it and present the appropriate version to the RDBMS in question.
Even just taking MySQL as an example, that is a MASSIVE undertaking. Frankly, I imagine it'd be just too big.. here's a list of the functions:

Code: Select all

 
CASE    Case operator
IF()    If/else construct
IFNULL()    Null if/else construct
NULLIF()    Return NULL if expr1 = expr2
 
ASCII()     Return numeric value of left-most character
BIN()   Return a string representation of the argument
BIT_LENGTH()    Return length of argument in bits
CHAR_LENGTH()   Return number of characters in argument
CHAR()  Return the character for each integer passed
CHARACTER_LENGTH()  A synonym for CHAR_LENGTH()
CONCAT_WS()     Return concatenate with separator
CONCAT()    Return concatenated string
ELT()   Return string at index number
EXPORT_SET()    Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()     Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()   Return the index position of the first argument within the second argument
FORMAT()    Return a number formatted to specified number of decimal places
HEX()   Return a hexadecimal representation of a decimal or string value
INSERT()    Insert a substring at the specified position up to the specified number of characters
INSTR()     Return the index of the first occurrence of substring
LCASE()     Synonym for LOWER()
LEFT()  Return the leftmost number of characters as specified
LENGTH()    Return the length of a string in bytes
LIKE    Simple pattern matching
LOAD_FILE()     Load the named file
LOCATE()    Return the position of the first occurrence of substring
LOWER()     Return the argument in lowercase
LPAD()  Return the string argument, left-padded with the specified string
LTRIM()     Remove leading spaces
MAKE_SET()  Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH   Perform full-text search
MID()   Return a substring starting from the specified position
NOT LIKE    Negation of simple pattern matching
NOT REGEXP  Negation of REGEXP
OCTET_LENGTH()  A synonym for LENGTH()
ORD()   Return character code for leftmost character of the argument
POSITION()  A synonym for LOCATE()
QUOTE()     Escape the argument for use in an SQL statement
REGEXP  Pattern matching using regular expressions
REPEAT()    Repeat a string the specified number of times
REPLACE()   Replace occurrences of a specified string
REVERSE()   Reverse the characters in a string
RIGHT()     Return the specified rightmost number of characters
RLIKE   Synonym for REGEXP
RPAD()  Append string the specified number of times
RTRIM()     Remove trailing spaces
SOUNDEX()   Return a soundex string
SOUNDS LIKE(v4.1.0)     Compare sounds
SPACE()     Return a string of the specified number of spaces
STRCMP()    Compare two strings
SUBSTR()    Return the substring as specified
SUBSTRING_INDEX()   Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING()     Return the substring as specified
TRIM()  Remove leading and trailing spaces
UCASE()     Synonym for UPPER()
UNHEX()(v4.1.2)     Convert each pair of hexadecimal digits to a character
UPPER()     Convert to uppercase
 
NOT REGEXP  Negation of REGEXP
REGEXP  Pattern matching using regular expressions
RLIKE   Synonym for REGEXP
 
ABS()   Return the absolute value
ACOS()  Return the arc cosine
ASIN()  Return the arc sine
ATAN2(), ATAN()     Return the arc tangent of the two arguments
ATAN()  Return the arc tangent
CEIL()  Return the smallest integer value not less than the argument
CEILING()   Return the smallest integer value not less than the argument
CONV()  Convert numbers between different number bases
COS()   Return the cosine
COT()   Return the cotangent
CRC32()(v4.1.0)     Compute a cyclic redundancy check value
DEGREES()   Convert radians to degrees
EXP()   Raise to the power of
FLOOR()     Return the largest integer value not greater than the argument
LN()    Return the natural logarithm of the argument
LOG10()     Return the base-10 logarithm of the argument
LOG2()  Return the base-2 logarithm of the argument
LOG()   Return the natural logarithm of the first argument
MOD()   Return the remainder
OCT()   Return an octal representation of a decimal number
PI()    Return the value of pi
POW()   Return the argument raised to the specified power
POWER()     Return the argument raised to the specified power
RADIANS()   Return argument converted to radians
RAND()  Return a random floating-point value
ROUND()     Round the argument
SIGN()  Return the sign of the argument
SIN()   Return the sine of the argument
SQRT()  Return the square root of the argument
TAN()   Return the tangent of the argument
TRUNCATE()  Truncate to specified number of decimal places
 
ADDDATE()(v4.1.1)   Add dates
ADDTIME()(v4.1.1)   Add time
CONVERT_TZ()(v4.1.3)    Convert from one timezone to another
CURDATE()   Return the current date
CURRENT_DATE(), CURRENT_DATE    Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME    Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP  Synonyms for NOW()
CURTIME()   Return the current time
DATE_ADD()  Add two dates
DATE_FORMAT()   Format date as specified
DATE_SUB()  Subtract two dates
DATE()(v4.1.1)  Extract the date part of a date or datetime expression
DATEDIFF()(v4.1.1)  Subtract two dates
DAY()(v4.1.1)   Synonym for DAYOFMONTH()
DAYNAME()(v4.1.21)  Return the name of the weekday
DAYOFMONTH()    Return the day of the month (0-31)
DAYOFWEEK()     Return the weekday index of the argument
DAYOFYEAR()     Return the day of the year (1-366)
EXTRACT     Extract part of a date
FROM_DAYS()     Convert a day number to a date
FROM_UNIXTIME()     Format UNIX timestamp as a date
GET_FORMAT()(v4.1.1)    Return a date format string
HOUR()  Extract the hour
LAST_DAY(v4.1.1)    Return the last day of the month for the argument
LOCALTIME(), LOCALTIME  Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)    Synonym for NOW()
MAKEDATE()(v4.1.1)  Create a date from the year and day of year
MAKETIME(v4.1.1)    MAKETIME()
MICROSECOND()(v4.1.1)   Return the microseconds from argument
MINUTE()    Return the minute from the argument
MONTH()     Return the month from the date passed
MONTHNAME()(v4.1.21)    Return the name of the month
NOW()   Return the current date and time
PERIOD_ADD()    Add a period to a year-month
PERIOD_DIFF()   Return the number of months between periods
QUARTER()   Return the quarter from a date argument
SEC_TO_TIME()   Converts seconds to 'HH:MM:SS' format
SECOND()    Return the second (0-59)
STR_TO_DATE()(v4.1.1)   Convert a string to a date
SUBDATE()   A synonym for DATE_SUB() when invoked with three arguments
SUBTIME()(v4.1.1)   Subtract times
SYSDATE()   Return the time at which the function executes
TIME_FORMAT()   Format as time
TIME_TO_SEC()   Return the argument converted to seconds
TIME()(v4.1.1)  Extract the time portion of the expression passed
TIMEDIFF()(v4.1.1)  Subtract time
TIMESTAMP()(v4.1.1)     With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()(v5.0.0)  Add an interval to a datetime expression
TIMESTAMPDIFF()(v5.0.0)     Subtract an interval from a datetime expression
TO_DAYS()   Return the date argument converted to days
UNIX_TIMESTAMP()    Return a UNIX timestamp
UTC_DATE()(v4.1.1)  Return the current UTC date
UTC_TIME()(v4.1.1)  Return the current UTC time
UTC_TIMESTAMP()(v4.1.1)     Return the current UTC date and time
WEEK()  Return the week number
WEEKDAY()   Return the weekday index
WEEKOFYEAR()(v4.1.1)    Return the calendar week of the date (0-53)
YEAR()  Return the year
YEARWEEK()  Return the year and week
 
BINARY  Cast a string to a binary string
CAST()  Cast a value as a certain type
Convert()   Cast a value as a certain type
 
AES_DECRYPT()   Decrypt using AES
AES_ENCRYPT()   Encrypt using AES
BENCHMARK()     Repeatedly execute an expression
BIT_COUNT()     Return the number of bits that are set
&   Bitwise AND
~   Invert bits
|   Bitwise OR
^   Bitwise XOR
CHARSET()(v4.1.0)   Return the character set of the argument
COERCIBILITY()(v4.1.1)  Return the collation coercibility value of the string argument
COLLATION()(v4.1.0)     Return the collation of the string argument
COMPRESS()(v4.1.1)  Return result as a binary string
CONNECTION_ID()     Return the connection ID (thread ID) for the connection
CURRENT_USER(), CURRENT_USER    Return the username and hostname combination
DATABASE()  Return the default (current) database name
DECODE()    Decodes a string encrypted using ENCODE()
DEFAULT()   Return the default value for a table column
DES_DECRYPT()   Decrypt a string
DES_ENCRYPT()   Encrypt a string
ENCODE()    Encode a string
ENCRYPT()   Encrypt a string
FOUND_ROWS()    For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
GET_LOCK()  Get a named lock
INET_ATON()     Return the numeric value of an IP address
INET_NTOA()     Return the IP address from a numeric value
IS_FREE_LOCK()  Checks whether the named lock is free
IS_USED_LOCK()(v4.1.0)  Checks whether the named lock is in use. Return connection identifier if true.
LAST_INSERT_ID()    Value of the AUTOINCREMENT column for the last INSERT
<<  Left shift
MASTER_POS_WAIT()   Block until the slave has read and applied all updates up to the specified position
MD5()   Calculate MD5 checksum
NAME_CONST()(v5.0.12)   Causes the column to have the given name
OLD_PASSWORD()(v4.1)    Return the value of the old (pre-4.1) implementation of PASSWORD
PASSWORD()  Calculate and return a password string
RAND()  Return a random floating-point value
RELEASE_LOCK()  Releases the named lock
>>  Right shift
ROW_COUNT()(v5.0.1)     The number of rows updated
SCHEMA()(v5.0.2)    A synonym for DATABASE()
SESSION_USER()  Synonym for USER()
SHA1(), SHA()   Calculate an SHA-1 160-bit checksum
SLEEP()(v5.0.12)    Sleep for a number of seconds
SYSTEM_USER()   Synonym for USER()
UNCOMPRESS()(v4.1.1)    Uncompress a string compressed
UNCOMPRESSED_LENGTH()(v4.1.1)   Return the length of a string before compression
USER()  Return the current username and hostname
UUID()(v4.1.2)  Return a Universal Unique Identifier (UUID)
VALUES()(v4.1.1)    Defines the values to be used during an INSERT
VERSION()   Returns a string that indicates the MySQL server version
 
AVG()   Return the average value of the argument
BIT_AND()   Return bitwise and
BIT_OR()    Return bitwise or
BIT_XOR()(v4.1.1)   Return bitwise xor
COUNT(DISTINCT)     Return the count of a number of different values
COUNT()     Return a count of the number of rows returned
GROUP_CONCAT()(v4.1)    Return a concatenated string
MAX()   Return the maximum value
MIN()   Return the minimum value
STD()   Return the population standard deviation
STDDEV_POP()(v5.0.3)    Return the population standard deviation
STDDEV_SAMP()(v5.0.3)   Return the sample standard deviation
STDDEV()    Return the population standard deviation
SUM()   Return the sum
VAR_POP()(v5.0.3)   Return the population standard variance
VAR_SAMP()(v5.0.3)  Return the sample variance
VARIANCE()(v4.1)    Return the population standard variance
That doesn't include a lot of stuff like MATCH AGAINST text searching or anything, nor does it include any of the extensions like the precision maths or geospacial stuff. There's just no way you can create a library that gives people access to that stuff on every database. And if you choose to miss some stuff out then it all because a bit useless because the user might need some of that stuff later so they won't really be able to use your library just in case.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:16 am
by crazycoders
Exactly, thats why they stopped at SQL99 because it actually implement everything that a standard database access programmer needs.... CRUD operations. When you need to get special functionnality such as functions, stored procedure, i think you the execution statement part of SQL99 but i'm not sure... The language used to create the stored procedure is not SQL99 compliant because it can never be the same everywhere.

When you start going into the specifics of the databases, thats where you'll be having problems creating a unified concept.

The only thing i think is really missing in the unified SQL base is the LIMIT, JUMP/SKIP/SEEK statements. Almost all databases implement this the way they want. For the rest, C,U,D operations are all the same as long as you respect the SQL standard and SELECT operations all provide standard JOIN, COND, GROUP, HAVING, ORDER so there is no need to extend it, it's actually very complete.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:18 am
by crazycoders
Also, what would be nice i maybe thrown in the new SQL standard a couple of useful functions such as DATE management, name access syntax and maybe some other critical highly used functions such as string functions, geospatial functions and math functions.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:48 am
by kaisellgren
crazycoders wrote:Exactly, thats why they stopped at SQL99 because it actually implement everything that a standard database access programmer needs.... CRUD operations. When you need to get special functionnality such as functions, stored procedure, i think you the execution statement part of SQL99 but i'm not sure... The language used to create the stored procedure is not SQL99 compliant because it can never be the same everywhere.

When you start going into the specifics of the databases, thats where you'll be having problems creating a unified concept.

The only thing i think is really missing in the unified SQL base is the LIMIT, JUMP/SKIP/SEEK statements. Almost all databases implement this the way they want. For the rest, C,U,D operations are all the same as long as you respect the SQL standard and SELECT operations all provide standard JOIN, COND, GROUP, HAVING, ORDER so there is no need to extend it, it's actually very complete.
It depends a lot on the project you are working on. In my case, I doubt no one needsgeospacial stuff or something else. The basic SQL operation is pretty much what me and my developers would need.

@onion2k: at first, the built-in language would not even need all those functions, because PHP can itself do lots of those itself. PHP has maths functions, string functions, encryption functions, date functions, etc. So at first the very basic database features would be present in the built-in SQL language. If you were about to create a new SQL language inside your project, do you think you really would need to have all of those (lets say MySQL)'s features? Why would you need abs()? year()? sin()? floor()? pi()? Okay, sometimes in very complex queries you might need to take a floor of a specific column or a result and continue using that in your query, so you can't use PHP for it. But often you can use PHP to replace SQL functionality. Even if you can't use PHP's functions for that, all major functions are same in most RDBMS already :)

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 8:55 am
by crazycoders
One thing you need to understand though kai is that implementing those functions can be easy in an intermediate language and can be really crucial. Imagine having 1 million lines to process. Yes you could transform the SQL:

SELECT ABS(column) FROM table
into
SELECT * FROM table

And then you run ABS on all records... Thats easy, but 1 million records * abs uses a lot of PHP time that is not well invested. MySQL time is not counted towards the PHP operation time and the operation can be executed on the 1 million lines at once instead of one by one in a complex logic in php.

;)

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 9:28 am
by onion2k
kaisellgren wrote:@onion2k: at first, the built-in language would not even need all those functions, because PHP can itself do lots of those itself. PHP has maths functions, string functions, encryption functions, date functions, etc. So at first the very basic database features would be present in the built-in SQL language. If you were about to create a new SQL language inside your project, do you think you really would need to have all of those (lets say MySQL)'s features? Why would you need abs()? year()? sin()? floor()? pi()? Okay, sometimes in very complex queries you might need to take a floor of a specific column or a result and continue using that in your query, so you can't use PHP for it. But often you can use PHP to replace SQL functionality. Even if you can't use PHP's functions for that, all major functions are same in most RDBMS already :)
Wait a second.. you're suggesting that as part of using this library people abandon all the database specific functions?

So if I want to find the most common year in a few hundred thousand date records you're suggesting that rather than "SELECT YEAR(`date_column`) as year FROM `date_table` ORDER BY year DESC LIMIT 1" it would be better to return all the thousands of dates, create an array of the years by looping through those records and getting the count for each value with date() or mktime() or something, sorting the array, and getting the first value?

And that's just something trivial. PHP is never an alternative to programmatically manipulating your data in the database. It's considerably faster to do whatever calculations you need in SQL and only return the values you actually need rather than returning everything and working it all out in your script.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 9:40 am
by crazycoders
Thats not what I want to do, dont' worry... But Kai seems to want to abstract all functions to PHP. At least thats what i understood. Only need to implement basic operations, when it comes to executing complex math, why not do php side!
kaisellgren wrote:at first, the built-in language would not even need all those functions, because PHP can itself do lots of those itself. PHP has maths functions, string functions, encryption functions, date functions, etc. So at first the very basic database features would be present in the built-in SQL language. If you were about to create a new SQL language inside your project, do you think you really would need to have all of those (lets say MySQL)'s features?
Sincerely i don't agree. SQL servers are there to manipulate data in blocks, never would i let php interpret data himself unless a SQL query would make it more complex. Such as highly complex data crunching for stat reports... At that point, i don't mind crunching a bit of numbers but not all. I'm sure all of you had to face this problem once.

Re: Having a built-in own SQL language?

Posted: Wed Oct 29, 2008 12:10 pm
by josh
If you wrote your query object with an implicit interface instead of an explicit interface, you wouldn't have to know very much about SQL, just string manipulation in general.. granted you could build a bad query string with an implicit interface so you loose some control at the benefit of extra abstraction, then you treat all SQL commands the same, implement sub queries as a composite pattern. You would still need a way to make sure different parts of the query got constructed in the right order, which would still imply your abstract query class knowing at least something about the SQL implementation, since the user of your query object could call things in any order