DATABASE SYSTEMS

FINAL PROJECT REQUIREMENTS

• Create a directory called "database" immediatly within your storm home directory
• Within that directory, create a program using C++, or Python, etc. Call it any name, example "myDB" that will...
  1. Repeatedly prompt the user to enter one of the SQL commands below, and can also
  2. Accept one of the SQL commands below through the "argv" array
  
  The SQL Commands:
  
  
• CREATE TABLE tablename (colname1, colname2, colname3, etc.)
  - Do not worry about the data types or the size of columns.
  - Create a file within a subdirectory called "db" with the same name as the tablename.
  - Write your first record as the column name headers.
  - It is best if you pre-create the "db" subdirectory within your "database" directory
  - Assign the follwowing permissions to the "db" subdirectory "rwxrwxrwx" or 777
  
  
• INSERT INTO tablename VALUES( value1, 'value2', 'value3', etc.)
  - Do not worry about specifying column names.
  - Simply add a record with those values to the end of the file.
  - In the insert command, if value is numeric, no quote. If value is a string, enclose in single quotes
  - If a column value is not provided then the user must provide an extra comma - e.g. VALUES(value1, ,'value3', etc.)
  
  
• SHOW TABLES
  - List the names of all your database tables within the "db" directory
  
  
• SELECT * FROM tablename
  
• SELECT colname1, colname2, colname3, etc. FROM tablename
  - Display either all columns, or only the requested columns.
  - Make sure you display the column headers
  - When printing, columns and column headers must be either tab or comma delimited
  
  
• SELECT as above FROM tablename
  WHERE colname = 'value' (or colname = value if numeric)
  - Same as above, but now only display the rows that meet the specific value for the specific colname
  - Optional: See if you can support other comparison operators (e.g. != or > or <=, etc.)
  - Do not worry about multiple columns compare.
  
  
• SELECT as above
  FROM tablename1 JOIN tablename2
  ON tablename1.colname1 = tablename2.colname2
  WHERE as above
  - Join the two tables based on the specified "ON" condition.
  - Maximum 2 table join (Optionally, can you try for more?)
  - Do not worry about outer (left, right or full) join
  - Display all or only requested columns, and requested rows.
  
  
• Optional: DESC tablename List all columns within the table
• Optional: SHOW DATABASES If you decided to create multiple subdirectories within your "db" directory
• Optional: DB dbname Switch from one database to another database, if you create multiple subdirectories
  
  
• PS. All SQL commands are not case sensitive.
  - All table and column names are not case sensitive but should be stored as lowercase. (makes it easier)
  - All data values are case sensitive. (makes it easier)
  - Entire command must be entered on a single line. (makes it easier)
  - I can have a single space or multiple spaces between the entered command keywords.
  - It is best if you use 'regex' (regular expressions) to parse the input commands.
  - Program ends when user enters 'exit' or enters 'quit'.
  

• Learn and use regex to parse the entered SQL command (see examples below)
• Find or create a function that splits a string into an array
• Find or create a function that joins all elements of an array into a string
• Find or create a function that determines if a substring is within a longer string
• Find or create a function that determines if a string is equal to any array elements
• Partition your code into the following functions:
  • create( )
  • insert( )
  • show( )
  • select( ), then call...
    • select_single_table( )
    • select_join_tables( )
    • select_where( )
    • select_display( )
  
  
• Regex to validate and obtain the tablename for: "create table tablename". (\w+) will be the entered tablename .... ^\s*create\s+table\s+(\w+)
• Regex to validate and obtain the * or column_names and tablename for: "select * | col1, col2, etc. from tablename" .... ^\s*select\s+(\*|.+)\s+from\s+(\w+)
• Regex to validate and obtain the tablenames and column_names for: "... join table2 on table1.col1 = table2.col2" .... \sjoin\s+(\w+?)\s+on\s+(\w+\.\w+)\s*=\s*(\w+\.\w+)