Help me on my SQL emulation project!!

tprayush · February 17, 2010, 2:24pm

hello all!!

I am a newbie to shell scripting and to learn it I took a project in which I m facing a tremendous amount
problems....I Need Your HELP!! the Details of project are as follows:

while learning shell scripting, i came up with an idea to make a 'Database/Record management system' as
my major project in this semester
with a specific query language to interact with it. what i wanted was...
as when you type like

SELECT */EMPNO,ENAME,JOB,SAL FROM EMP

in Oracle , you get the result of
desired fields..i wanted the same thing to happen when i invoke a command in terminal with
column names as arguments... it should get the output as we get in SQL Prompt.

the whole thing i wanted to create a package/utility for unix/linux that could emulate SQL.. i.e, to create
CREATE,SELECT,INSERT, etc all DML, DDL,TCL commands. I named this 'GQL'...
now the whole process should go as follows:

when you type gql in terminal you should get a restricted environment with GQL prompt,

$ gql           //for entering in the package where other commands & Databases(Directories) stored
//after a while GQL prompt Should appear as
GQL>

Now use the existing database as:
GQL>use <Database_name>    //choosing one of the database(Directory)
//OR
Create a Database as:   // we should not be able to Create table(file) before 'use' command.

GQL>Create database <Database_name>

//Now you can execute DDL  Statments as:


GQL>create table <table_name>
                Fields     Type      Constraint(if any)                    
Field1:
Field2:                                                           #press ctrl+c when done

*******************************************************************************
in simple meaning ..a Database means collection of tables...so creating a database would simply mean creating
a directory and creating a table means creating file in particular directory...
all the record is in the form of text delimited by colon !!

the problem i m facing is how to implement the 'Datatype' & 'Constraints' over the fields..as they are mere the text of a file!!

i have written the 'gql' script which is mere a prompt changing $ path setting script but i wanted that when GQL prompt appears no other shell commands like 'cd', 'mkdir' etc could run it should be a separate environment. plz tell me how can i achieve this.

I have also written the 'project' script which resembles the SQL , SELECT Statement without WHERE Clause....now i want to add the Where clause functionality to it but i cudn't find any approach for it ...plz help!!

********************
i know u'd say that this is of no worth as we already have Oracle, PostgreSQl, MySql....the openoffice Databases..and hundreds of utility of this kind.... which are a thousands times better than this. but its my college project and by the end of semester i have to submit it...and I've already started!! so plz guide me!!!

I want ur reviews , comments, suggestions over the project...its feasibility that what i m thinking of is possible or not....its enhancement and scope.. other alternatives how can i make it better and significance...

I m attaching the screen sccreenschots of my work along with the scripts....
plz Do hav look over it...GUIDE me to optimize the scripts..

NOTE: plz dont over my scripts .. as i m a newbie!!

regards,
prayush

jgt · February 17, 2010, 11:10pm

You should be able to write a script that validates each field before it is added to the database, and at anytime that it is modified.
Some field types would have generic solutions (date/time), others might be specific to the table/field (dept number, salary), others might have to belong to a list.
Either use exit values from the validation script, or test the output from the validation against the input, to decide how to proceed.
Are you planning on having one record per file, and using the directory structure as the indexes?

tprayush · February 18, 2010, 10:59am

thanx Jgt for your suggestions!!

what i have planned... consider directory named 'employee'.. now directory contains files related to employee information...just as the the default 'employee' databse of oracle contains tables like emp, dept,location salgrade etc...the directory 'employee' would contain files - emp, dept,location, salgrade etc.

so the a directory is analogous to a database and files are analogous to the tables of any database..

and the information within file is organized as below;
the content of the file 'emp' is:

empno:ename:job:mgr:sal:comm:deptno
7839:king:president:7839:5000::10
7698:blake:manager:7839:2850::30
7782:clark:manager:7839:2450::10
7654:martin:salesman:7698:1250:1400:20
7788:scott:analyst:7566:3000::20
7900:james:clerk:7698:950::30
7521:ward:salesman:7698:1250:500:30

I have written a script named 'project'..if U see the attached screenshot,, to project the specified columns in arguments to STDOUT. now i want to add up a filtering functionality based on conditions ..as WHERE clause of SQL..please suggest me any approach to accomplish this!!

now i m planning to write a script to insert the values in the file in the form as:

$insert emp:7788 ward salesman 7689 3000 140 10

now the problem is to validate that while entering the values for 'empno' it shud be integer and for 'ename' it must be characters..so on...please guide me to accomplish this ......
can you please elaborate how can do this using exit values...should i have to make a separate validation script or it shud be a function in the script!!

thanx..
regards.

jgt · February 18, 2010, 8:44pm

If you increase the number of directories, you can reduce the number of records per physical file.
For instance
$EMPLMASTER/$DEPT/$EMPLNUMBER/
then in this directory have multiple files but only one record per file
For instance file1 contains employee#, name, address, hire date, date of birth,etc
A second file, file2 contains work history, one field of indeterminate length.
A third file, file3, contains salary information

To SELECT * FROM EMPLMASTER
cat $EMPLMASTER/*/*/file1 >>result

TO SELECT DEPT=1 from EMPLMASTER
cat $EMPLMASTER/1/*/file1 >>result

When you want to validate a field, can we assume for expedience that any invalid value will be replaced by the default value.

So to insert the hire month we will write a simple script that accepts one parameter (month, Jan thru Dec) and checks to see if it is in a table, if not, the current month is used.

#!/bin/ksh
#validatemonth.ksh
grep $1 monthtable.en
if [ $? -eq 0 ]   #if found 
   echo -e "$1\c"
else
   mon=`date +"%b"`
   echo -e "$mon\c"
fi

monthtable.en is a file containing

Jan
Feb
Mar
Apr
May
...
Dec

And to insert a new record

echo -e $EMPLNUMBER:$NAME:$TITLE:`validatemonth.ksh $month`:........>$EMPLMASTER/$DEPT/$EMPLNUMBER/file1

Jack