TDDB - The Distributed DataBase

SQL Statements currently supported by TDDB.

• CREATE TABLE [db_name.]table_name(list of fields [UNIQUE]); Foreign keys or primary keys are not supported as of now.


• DROP TABLE table_name; DROPs a table(deletes it) from the database.


• SELECT field1, field2, field3 AS aliasFN | * FROM table1, table2 AS aliasTN WHERE condition1 [AND|OR] condition2 [AND|OR] conditionN ORDER BY [alias/field](s) [ASC|DESC] LIMIT N; Currently, GROUP BY, HAVING, aggregates, and complex projections such as sum of 2 fields in table(s) is not supported.


• UPDATE table1, table2, tableN SET field1=[value/field], field2=[value/field], fieldN=[value/field] WHERE condition(s); Here, [value/field] can be a constant, a field name, or an arithmetic expression involving multiple fields.


• DELETE FROM table_name WHERE condition(s); Deletes Rows from a table without deleting the table itself.


• SET TRANSACTION ISOLATION LEVEL [READ COMMITTED|REPEATABLE READ|SERIALIZABLE]; Sets the Transaction Isolation Level for the Current Transaction ONLY. If you COMMIT or ROLLBACK the Transaction, then the new Transaction will get the default REPEATABLE READ Transaction isolation Level. Hence, you must Explicitly set the Transaction Isolation Level for each New Transaction. This statement also implicitly COMMITS the currently running Transaction(if possible).


• SET AUTOCOMMIT [ON/OFF]; Sets the AUTOCOMMIT mode to ON(enabled) or OFF(disabled) ONLY for the currently running Transaction. Executing this statement will implicitly COMMIT any previous Transactions(if possible) and start a new Transaction. Note: The default behaviour for the Cleint is to start with AUTOCOMMIT Disabled. This feature(AUTOCOMMIT) is implemented at the client end, and the server does not recognize this statement. If AUTOCOMMIT is enabled, then the client implicitly sends a COMMIT after executing any SQL statement.


• LOCK TABLE(S) table1, table2, tableN IN [READ|INSERT|UPDATE|SERIALIZABLE|EXCLUSIVE] MODE;.


• UNLOCK TABLE(S) table1, table2, tableN;.


• USE db_name; Where db_name is the default DB to use. This command can be used ONLY at the client end. The TDDB server doesn't recognize this command.


• COMMIT;


• ROLLBACK;

Data types currently supported by TDDB.

• INT/INTEGER
• CHAR(N) Max(N) is 248
• VARCHAR(N) Max(N) is 8100
• CLOB

Known Limitations/Features/Bugs

• The INTEGER/INT data type doesn't support negative numbers. Yes, only non-negative numbers can be used. Furthermore, decrementing an INT beyond 0 results in the INT getting the value NULL.


• You can view the list of tables, and the commands used to create them by selecting from the table 'global.tabtab' as: 'SELECT * FROM global.tabtab;'. This command is treated as a DDL statement for compatibility reasons.


• DDL Commands are also Transaction oriented!! Suppose you CREATE or DROP a Table by mistake, you can revert your action by doing a ROLLBACK. However, if you want to make the changes permanant, use COMMIT.


• You can't Mix DML & DDL commands in a single Transaction. Suppose you CREATE a table, you need to COMMIT or ROLLBACK before you issue a DML Command. You may however, issue another DDL Command in the same Transaction such as creating or dropping another table. Don't try to CREATE & DROP the same table in the same Transaction though.


• Maximum Row Size = 8100 Bytes. That is the Sum of all data types in the Row should be not greater than 8100 Bytes. The sizes of the various data types for this calculation are mentioned below:

  Data Type	Size in Bytes
  INTEGER	8
  CHAR(N)	N+4
  VARCHAR(N)	32
  CLOB	16



• There is currently no support for Indices, and so each Query processed needs to do a Full Table Scan. Even JOINs are performed using the Nested Loops JOIN algorithm. We are working on getting more efficient JOIN algorithms which don't need Indices or create the Indices dynamically into place, so that JOIN processing can be made faster.


• There is currently NO support for Nested Queries.


• The PRIMARY KEY or the FOREIGN KEY integrity constraints are not currently supported.


• The type checking of the parser is quite weak. That needs to be fixed. For example, certain type conversions are performed silently by the engine without the user ever being notified about them. If the user passes a string in case of an integer, then the string is parsed as an int, and the first few digit characters(if any) are parsed as the integer. For the reverse case, the integer parsed as the string is entered as a string with the representation of the integer.

Wish list/TODO

Advantages of TDDB:

• TDDB has been a great learning experience for all of us involved in it's making, and we got to learn a lot from it. It is a fairly simple system, and since it has been designed keeping in mind that we would want to add more features to it, it would aid in adding possibly non-standard extensions to it too.


• There is just one file involved, so it is fairly simple to take backups. That one file contains the data as well as the logs, so you don't have to worry about backing up both, and then making sure that they both belong to the same version(in time) of the Database so as to speak. Even if the server shut down incorrectly, it will detect that at startup(since all the information is stored in that single file), and replay the LOGs if required.