| |
|
|
| tdbSQL - The first SQL Server based on tdbengine - tdbSQL changelog |
|
tdbSQL CHANGELOG
================ 2004-09-09: Friebel, Thomas <tf@tdb.de> + Extended the syntax of SHOW TABLES to: SHOW [TEMPORARY] TABLES [FROM database] [LIKE pattern] [IN DETAIL] The keywords "IN DETAIL" cause the list to be expanted by 4 more columns containing the same information as returned by an SHOW DETAILS FROM [TABLE] tablename -Query 2004-09-08: Friebel, Thomas <tf@tdb.de> + new syntax for table(index) optimizing: OPTIMIZE [{FULLTEXT [INDEX]| INDEX} [name] FROM] [TABLE] table [PASSWORD=pw] [SCRAMBLE=key] [FROM database] This causes the tdbSQL server to update the indexes, the fulltext indexes, or one certain of them. + introduced new module p_diverse.mod. This module will cover special syntax elements, not part of "normal" SQL 2004-09-07: Friebel, Thomas <tf@tdb.de> + introduced p_truncate.mod which holds the following syntax TRUNCATE TABLE <table> + FULLTEXT handling has been improved massively: ALTER TABLE tab FULLTEXT [INDEX] [<ftname>] [({cols | COMPLETE})] [ExtABC = "extabc"] where [ExtABC = "extabc"] means a string that contains special characters that are to be indexed, too. Useful for getting numbers indexed (eg ExtABC="0123456789.$%") + Error handling is improved at many different places. 2004-09-02: Friebel, Thomas <tf@tdb.de> + ALTER TABLE syntax now allows ADD [COLUMN] ... [FIRST | AFTER existing colname] statements + Tokenizer now does not divide on : (colon) anymore (important for time values) + SHOW DETAILS FROM table [FROM database] syntax introduced 2004-08-31: Friebel, Thomas <tf@tdb.de> + The table alias defintion in a SELECT clause now can be done two ways: 1) SELECT * FROM customer AS { 'C' | C } ... 2) SELECT * FROM customer { 'C' | C } ... + A new right has been introduced: may_SHOW This right will be checked for every SHOW... query. 2004-08-24: Friebel, Thomas <tf@tdb.de> + UPDATE got a syntax extension UPDATE [TEMPORARY [TABLE]] .... + new syntax for the INSERT statement introduced INSERT INTO [TEMPORARY [TABLE]] <xy> SELECT ... The columns from the SELECT that match the columns of <xy> will be filled. + new syntax for the SELECT statement to access data in temporary tables SELECT * FROM [TEMPORARY [TABLE]] <xy> [JOIN] [TEMPORARY [TABLE]] <yz> ... The access to temporarly created tables MUST be signed by the keyword "TEMPORARY" or "TEMPORARY TABLE" in front of each table's name. + new Syntax for the CREATE statement introduced CREATE [TEMPORARY] TABLE <xy> SELECT ... This allows a selection's result set to be stored as the new (temporary) table <xy> for later re-use. The meta information file to the table is not yet created. CREATE [TEMPORARY] TABLE <xy> USING '...' The USING keyword allows the definition to be directly given as INI file. eg.: CREATE TABLE users USING ' [STRUCTURE] field_1=Name,STRING,20 field_1=ID,AUTO ' This is for tdbengine professionals only :) 2004-08-20: Friebel, Thomas <tf@tdb.de> + Introduced the keyword TEMPORARY to DROP and CREATE statements. To make temporary tables not collide by name the complete handling of temporary files has been changed. Now each login produces a session ID which cascades the temporary files. When (re)starting the server all previously "forgotten" temporary files and on disconnecting the own temporary files will be removed. All temporary files, data or not, are located in the same place now. + A positive SELECT result now consists of 3 lines (column names, column origin, column types) and the data selected. 2004-08-19: Friebel, Thomas <tf@tdb.de> + The output format has slightly changed: now there are two kind of positive returns: OKAY and OKAYDATA which is telling the client, that some data is coming after. Clients need to be updated. + Errors in code generation concerning aliases and non-table-SELECTs have been fixed + GROUP BY and HAVING statements are now accepted by the parser but do not affect a queries result (yet). 2004-08-06: Friebel, Thomas <tf@tdb.de> + Introduction of DISTINCT/DISTINCTROW keyword for SELECT statements. Syntax: SELECT [DISTINCT|DISTINCTROW] * FROM... The very first column now is indexed if the DISTINCTROW keyword follows the SELECT. The output is then unique by this column. In case of a LIMIT 'n' the limit is reached when 'n' UNIQUE records have been found. + The SELECT parser and code generator has been massively optimized. Now a temporary DAT file is generated and filled. This is the base for a DISCTINCT/DISCTINCTROW usage because now an index can be used + The output routine has been extended, so it writes out the content of the temporary table instead of a 2d-array 2004-08-02: Friebel, Thomas <tf@tdb.de> + Bug fix: more than 10 SELECTs after each other now work (file was not closed before) + Uses semaphores for each table that is accessed in write-mode (semaphore = "<DB>/<TABLE>/<TABLE>.sema") 2004-07-30: Friebel, Thomas <tf@tdb.de> + Improved the rights management: three levels: user, database and table 2004-03-03: Friebel, Thomas <tf@tdb.de> + Table aliases now work in SELECT statements + Improved the error handling for missing tables, databases or wrong table password submissions + Expressions that return a numeric result now are detected and automatically converted to fit into the result (string) array 2004-02-27: Friebel, Thomas <tf@tdb.de> + Enhanced the tdbsqlwizard (and thereby also tdbsql.mod and easysql.mod). Now it has a couple of features it just had to have: "execute, login, status, system, prompt, etc." 2004-02-26: Friebel, Thomas <tf@tdb.de> + Introduction of a new client called "tdbsqlwizard" which is a command line based tool to access and query your tdbSQL server It uses the easysql.mod interface and is already kind of useful + Re-Activated the direct-command USEDB, which internally simply calls the parser with a constructed USE-statement. 2004-02-24: Friebel, Thomas <tf@tdb.de> + p_update.mod providing the basic UPDATE-syntax to tdbsql now. Syntax: UPDATE <table> [PASSWORD] [SCRAMBLE] SET (field1=expr1, [field2=expr2], ..) [WHERE] [LIMIT] + Support for the new field type UTIME added + Changed parser for INSERT-syntax: numerical expressions now don't need to be converted to string when set in numerical field + Extended server output to handle "unlimited" longtexts (by caching them on disk). Needs be optimized in future! 2003-09-05: Scholz, Thomas <god.p@gmx.de> + Added new php guestbook to demonstrate some features of the tdbSQL / PHP interface Enter clients/php/guestbook.php?install=start to create a new table or to reset an existing one. To add new entries enter your text an press the save button. 2003-09-05: Friebel, Thomas <tf@tdb.de> + improved the tdbsqladmin.mod in some points. by default the database root now for example is ../dbroot/ + Long strings now can be read into memos in all 3 syntax variants of INSERT INTO 2003-09-03: Friebel, Thomas <tf@tdb.de> + New function FLUSHRIGHTS reloads the user database of tdbsql and makes changes made to it taking effect So it's possible for an administrator user to change user rights or add/delete accounts and make those changes taking effect first when he wants them to. The global rights are to be set directly within the user table. Administrator by default has all rights, user guest has only the right to SELECT. 2003-09-03: Scholz, Thomas <god.p@gmx.de> + Introduced PHP interface class. The class gives you several functions to access to tdbSQL-Server via PHP First create an object: $tdbsql=new tdbSQL('host','port','user','pw','db'); Then access to the functions: $tdbsql->version(); etc. Look at testclient.php for more examples 2003-09-02: Friebel, Thomas <tf@tdb.de> + Password-Protection of tables is now possible. This brings along an extention to several statements that always looks similar: Syntax: ...<table> [PASSWORD = 'pstring'] ... CREATE TABLE addresses (Name STRING(20)) PASSWORD = 'mypw' COMMENT='Protected table'; SELECT * FROM addresses PASSWORD = 'mypw' WHERE ... INSERT INTO addresses PASSWORD = 'mypw' (Name... DELETE FROM addresses PASSWORD = 'mypw' (Name... + introduced new module ./server/tdbsqladmin.mod which for the moment is kind of a setup routine for tdbSQL only. Its job is to ensure that the most important settings are made correctly. It also creates a system database called "tdbsql' in database root. This database "tdbsql' contains one table 'user'. 'user' replaces the access.conf file introduced earlier and is a much more comfortable way to manage user accounts and permissions. tdbSQL from now on checks sent login information with 'user' and refuses access if the data is not correct. By default the users "administrator" and "guest" are created by tdbsqladmin. When tdbSQL is started it needs a system password to open the protected user table. You can put it as a commandline paramter "-r<Password>" or place it inside tdbsql.conf [databases].password . 2003-09-01: Friebel, Thomas <tf@tdb.de> + SELECT now understands statements like SELECT tableA.*, tableB.Name, TimeStr(Now,3) FROM... or SELECT Today, * FROM... + again improved the Tokenizer. No ";" will make problems, when attached to a keyword (eg: "SHOW TABLES;") 2003-08-29: Friebel, Thomas <tf@tdb.de> + INSERT INTO ... SET ... now can handle long texts (more than 255 chars) as parameter (only usefull for memo fields) + SELECT memofield FROM now gives you long texts back, but the tdbengine only can handle about 80 ramtexts so it will crash if you have more memos to be returned I would NOT bet on those functions working correctly with long texts, but it looks something good... + access.conf now can hold a list of users and their passwords - only the right combinations can connect by default there is the user "guest" with the password "guest" This method is very unsecure and is only meant as a placeholder for the first + In case of an query error the server now has a changed return behavior + easysql.mod is capable to handle the server's new error management 2003-08-28: Friebel, Thomas <tf@tdb.de> + introduced EASY interface to tdbSQL (/clients/easy/easysql.mod) for easy access new functions: tdbsql_connect(),tdbsql_disconnect(),tdbsql_query(),tdbsql_datetime() and tdbsql_serverinfo() + the Server now tracks some more or less useful information (uptime, connections, hostname, etc.) which are accessible via a generic query (no SQL statement) + SHOW INDEX FROM table [FROM database] now gives you a list of (almost) all indices belonging to that table. 2003-08-27: Friebel, Thomas <tf@tdb.de> + introduced new module p_show.mod: SHOW DATABASES [LIKE pattern] lists all known databases as one column SHOW TABLES [FROM database] [LIKE pattern] lists all tables from the current or from the requested database [LIKE pattern] enables selections within the complete list. e.g. SHOW TABLES LIKE 'tdb_*' + smaller improvements (prevented the tokanizer of producing endless loops, when chars below chr(32) appear) + Fulltext indexing is now supported! ALTER TABLE table ADD FULLTEXT [INDEX] indname (column1,column2,...) adds a fulltext index to table ALTER TABLE table DROP FULLTEXT [index] indname; removes an existing fulltext and all its refernces With a construction like the following you can use the ft index for selecting SELECT * FROM table FULLTEXT [MATCH] indname AGAINST ('searchpattern'); Fulltext search is only possible in the primary table but can be combined with a WHERE selection INSERT INTO and DELETE FROM automatically refresh any existing fulltext index when ever a record is inserted or deleted 2003-08-26: Friebel, Thomas <tf@tdb.de> + INSERT statement now should work pretty faster than before, but it is still slow (10000 records with 3 fields still took about 14 seconds) + when using a JOIN statement it is no longer necessary to use the []-brackets for the condition after ON; simply use ()-brackets now therfore its now possible to access array elements via [] (e.g. Name[1,6] to get the first 6 letters of the Name) + added new function GetANYEXPRESSION() which is responsible now for getting the output definitions for SELECT supports really complex statements now supports aliases after AS (syntax SELECT expression AS Othername ...) supports references to the aliases within other expressions and the WHERE clause (but not in ORDER BY!!!) Important: When referencing an alias within an expression that itself has an alias always declare them from left to right! e.g. SELECT A AS fA, B + fA AS fB, fB[1,2]... not SELECT A AS fA, fB[1,2], B + fA AS fb Because fB was not yet set when fB[1,2] is evalueted you will get odd results for that column. Cross-Referencing is NOT allowed. + improvements to p_insert-mod: Its now possible to insert several records with one INSERT statement Syntax: INSERT INTO table [(fieldlist)] VALUES (expressionlist)[,(expressionlist),...]; e.g.: INSERT INTO articles (Name,Price) VALUES ('Tea','1,99'),('Coffee','3,99'),('Cakes','1,59'); Syntax: INSERT INTO table (fieldlist) SELECT (fieldlist) FROM table2...; e.g.: INSERT INTO articles (Name,Price) SELECT ArtName, Price FROM boissons WHERE ArtName has 'tea' ; 2003-08-25: Friebel, Thomas <tf@tdb.de> + when using ALTER TABLE or CREATE TABLE the definition that is read and written now is kept in two versions: a permanent one which is stored in the table dir and a work def that is only used for the scope of the query This shall prevent the definition to be trashed by temporary statements like the "counting start of AUTO fields" + the socket functions for reading now work with BUFFERED, too + improved the test client, so propably more out standing people are able to get a picture of what is happening there It's source is cleaned and documentated (in short) and it does the following simple: Creates a new database, creates a new table, fills in one record and then SELECTs * from that table. + ALTER TABLE table DROP [COLUMN|PRIMARY KEY|INDEX] name added and tested + ALTER TABLE automatically adds or removes the needed INR-Index when ever a AUTO-increment field is added or dropped 2003-08-23: Friebel, Thomas <tf@tdb.de> + improved DELETE Statement: now ORDER BY and LIMIT clauses are supported, too. Example: DELETE FROM persons WHERE Birthday < 1.1.1970 ORDER BY Birthday ASC LIMIT 50 + Syntax DROP TABLE [IF EXISTS] 'table' added - deletes a table + Syntax DROP DATABASE [IF EXISTS] 'database' added - deletes a complete database and all its tables + introduced new module p_drop.mod 2003-08-22: Friebel, Thomas <tf@tdb.de> + ALTER DATABASE RENAME AS enables renaming a complete database + ALTER TABLE RENAME AS allows you to rename a table (and what belongs to it) + Added sql_filesystemusage.txt for describing the structure the databases are organized on the disc. + introduced new module p_alter; ALTER TABLE already works for ADD command + improved CREATE TABLE: TABLE_OPTIONS (PASSWORD, SCRAMBLE and COMMENT) introduced introduction of "pseudo field types" : BYTE (NUMBER,1), INTEGER (NUMBER,2), LONGINT (NUMBER,4), REAL (NUMBER,6,x) , DOUBLE (NUMBER,8,x) [[NOT] NULL] can be set after any numeric field (internally sets the ",U" flag in definiton file 2003-08-21: Friebel, Thomas <tf@tdb.de> + Implemented CREATE TABLE command which can create a table and user defined indices Working example: CREATE TABLE staff (Name string(30),Vorname string(20),ChangeDate DATE,ChangeTime TIME, INDEX person (Name(10),Vorname(5)),INDEX tstamp (ChangeDate, ChangeTime)); + Added the parser-module p_create.mod. This module manages the sql directives combined with CREATE (TABLE,DATABASE,INDEX...) + First implementation of "CREATE DATABASE [IF NOT EXISTS]" 2003-08-20: Friebel, Thomas <tf@tdb.de> + command line paramters are now parsed and a default configuration is stored in tdbsql.conf. --help parameter for more info + improved the code generation in p_select when all fields (= *) are requested. Depending on the amount of data querried this might boost up to 50 % 2003-08-14: Friebel, Thomas <tf@tdb.de> + improved the tokenizer by preloading the whole query text into a char[]-array. This now gives the flexibility needed to allow backward stepping if necessary. Should boost performance, too, especially when parsing long queries. 2003-08-12: Friebel, Thomas <tf@tdb.de> + reading and writing from and to the socket can now be done buffered. sockbuf_flush() and sockbuf_fill() are two new procedures which send the buffered data over the wire. + made improvements to the socket-functions. they now separate between long and short strings. Short strings are introduced by a sign-byte and a length byte describing the length of the string Long string are introduced by another sign-byte and 4 length bytes describing the length of the string There is no support for other lengths (2 byte, 3 byte) planned so far + the server now accepts a "pseudo-login" and parses a following SQL-Statement. Result data is returned, too. + made some changes to the SELECT-Module - atm it has a very mysterious bug, when processing SELECTs with JOIN inside. Have no idea yet, how to fix this. But therefore now its possible to query "*" (as the very first argument behind SELECT) to get ALL fields. Also the very first line of the result-Array now always contains either the <table.field> if "*" was querried or the query arguments themself as field headline. 2003-08-11: Friebel, Thomas <tf@tdb.de> + Added the basic implementation of the DELETE FROM -Statement in p_delete.mod + Optimized GetSET(); now gets complex expressions after the = correctly + GetVALUES() now give SELECT the ability to return calculated values. When no table reference (behind a FROM) is given, SELECT now returns exactly one single row with the results of the given expressions. e.g.: "SELECT DateStr(Today), TimeStr(Now);" is now a valid statement that works very well. + the new GetVALUES() function now is very flexible and can handle quite complex expressions. This makes it possible to let tdbSQL calculate values it shall save with INSERT() and UPDATE() + introduced first implementation of INSERT command in p_insert.mod + cleaned the code, added procedure comments and added p_insert.mod 2003-08-08: Friebel, Thomas <tf@tdb.de> + Splitted parser.mod into parser.mod and p_select.mod (easier development for other commands) + Added the obligatory files LICENSE, README and CHANGELOG to the cvs + Splitted the existing tdbsql.mod into tdbsql.mod and lib/parser.mod |
