SQL for Blitz Custom Types

In thinking of a TYPE collection as a database, I began to see the wonderful power of Types. Then I thought to myself: "wouldn't it be really cool if one could use SQL?" Using ID 'Keys' one can link information of one Type to another very similar to Primary/Foriegn Keys used in Relational Databases.

Example:

Type Employee
	Field ID%
	Field LastName$
	Field FirstName$
	Field Position$
End Type

Type Project
	Field ID%
	Field Name%
End Type

Type Work
	Field EmployeeID%
	Field ProjectID%
	Field HoursWorked%
End Type

Now the current dilema is Random Access to Blitz Types cause their designed for Sequential Access using BEFORE, AFTER, and FOR ... EACH Commands. However, the problem is easily resolved by using a Array Of Types.

Example:

Dim EmployeeID.Employee(100)

With a lil more code one could quickly create a means of assigning Keys to a Type Instance and storing the type in the array for instant access.

Example:

Function EmployeeNew.Employee(ID)
	this.Employee = New Employee
	this\ID=ID
	EmployeeID(ID)=this
	Return this
End Function

An Array Of Types would be used for each Type Collection (Records). One can devise various systems to assign IDs. This method provides an easily means link different Types together by ID Keys.

Example:

Employee.Employee=EmployeeNew(1) ;New Employee Record #1;
Employee\LastName = "Taylor"
Employee\FirstName = "Frank"

Project.Project=ProjectNew(7) ;New Project Record #7;
Project\Name$="SQL For Types"

Work.Work = WorkNew(619) ;New Work Record #619;
Work\EmployeeID= 1
Work\ProjectID = 7
Work\HoursWorked = 8

Now heres were SQL could come into play. Instead of using FOR..EACH to access and assign data to the collection, you could Query the collection and manipulate data that meets the conditions.

Example:

SELECT EmployeeID FROM ActiveWork.Work WHERE EmployeeID = 1
	Employee.Employee=EmployeeID(ActiveWork\EmployeeID)
	Employee\Position = "Promotion To Lead"
END SELECT

Blitz already provides a native means to UPDATE, INSERT, and DELETE records from the collection. So now all that is needed is a means to Sort by Conditions. Most of the SQL Commands could be used: ORDER BY, IN, BETWEEN, JOIN, UNION, CREATE, DROP, ALTER, GROUP BY, INTO.

Just for thought.

Using SQL queries would be pretty cool, but why would you need id's when you can do the relations between types like this :

Type Category
    Field catName$
End Type

Type Item
    Field cat.Category
    Field itemName
End Type

c1.Category=new Category
c1\catName$="First Category"

i1.Item=new Item
i1\itemName="An Item"
i1\cat=c1

If you use an ID, you will need to loop every category to find the one maching your item, like this you can just access it directly. Or am I missing something?

ID Key will basically serve the same purpose as they do in Relational Databases.

You don't loop through each category using an Array of Types.

Ok, I see. Still I don't understand how this would be done (I'm fairly new to blitz so I may be missing some things).
So every "table" will be stored in an array, right? How can you program your function that retreives the data so that it knows which array to use based on the correct tables? I mean, sure you can parse the string and get the name of the table, but if you know the name of the table is "Employee", how can you make it select the right array, and make all your code work with arrays of different types?
I'm sure you could do it, but I just don't understand how :)

The one draw back to SQL will be speed, Im fairly sure you can loop through fairly large type lists faster than you could access a database using SQL to get the same response. :)

I don't think he meant SQL Server, but the Sequential Query Language, from within Blitz.

That is correct. It would be a set of Commands that operate like Structured Query Language. However, I doubt we will see a native SQL Command Set in Blitz.

In reality you dont really need SQL. You can get the job done with IF...ENDIF Conditions and Sorting Algos. But, Built in SQL would be much cleaner and potentially faster.

;SQL
SELECT EmployeeID FROM ActiveWork.Work WHERE EmployeeID = 1
	Employee.Employee=EmployeeID(ActiveWork\EmployeeID)
	Employee\Position = "Promotion To Lead"
END SELECT

;Blitz Hardcode
For ActiveWork.Work = Each Work
	If ActiveWork\EmployeeID=1
		Employee.Employee=EmployeeID(ActiveWork\EmployeeID)
		Employee\Position = "Promotion To Lead"
	EndIf
Next

Even without SQL, using ID Keys is a great way to manage Types.

Frank,


"However, I doubt we will see a native SQL Command Set in Blitz."

I was just skipping through the thread...you know about Kanati's SQL capable Blitz solution I guess?

http://www.blitzbasic.co.nz/toolbox/toolbox.php?tool=34

IPete2.

IPete2,

Thanks. I've read about Kanati's SQL Blitz solution, however, I brought up the discussion to stir some braincells on the application of Types.

Being a better SQL user than I am with Blitz, I think thatit would be quicker (if a little around-the-houses code-wise), to just stick with Blitz command set to select and retrieve data from within a Type database.

Unless the database was unfeasably large, with a huge number of virtual tables each with many virtual fields. (by which time Blitz may have given up), otherwise, SQL is wasted on it, I think.

Well SQL would be more easy on the eyes than some huge if then or select case trees, but to be honast I think Id be happy with a simple Hash Table like that of PERL or JAVA for storing and retrieving data by name referance.

I realize this does not address the logic of assosiation or relitivity of the data, which is what SQL does, but it would make it very easy to build simple database like logic structures if we had the Hash Table to build from.

I like the idea of Blitz having some SQL-like features added to the existing FOR EACH...NEXT command structure. A few modifiers are all that's really needed, I think:

Function Sack_All_PartTimers()
	For Employee.WorkerData = Each Worker Where Employee\PartTime=True OrderBy Employee\LastName
		Employee\Status = "Fired"
	Next
End Function

Although, I can't think why you'd want to fire staff in alphabetical order by last name... :D

"Where" and "OrderBy" are the only keywords I can think of that would really make sense. "Where" is easy enough to immitate with the existing command set, but "OrderBy" is a pain. ...Oh! Just thought of one... "Desc" for descending order. So my suggested syntax would be:

FOR MyVar.MyType = EACH MyType WHERE Condition ORDERBY Field [optional: DESC], Field, Field, ...
NEXT

It's hardly SQL this way, but I think it gives us a great deal--particularly in combination with your Record ID Field idea, Frank--But that's down to the programmer.

...Although, what might be a more sensible approach would be to simply introduce a new function to sort the contents of a Type. You could still immitate the above SQL-like syntax, as well as having the flexibility of all the other type navigation commands in Blitz.

Barliesque

A implementation with FOR...EACH SQL doesnt look bad at all.

The problem with Coding a SQL function is handling Types. Not sure how you would go about making a general set of SQL functions that could work any Type. It appears that you would have to write a specific SQL Function to deal with a specific Type.

If I may interject here, I think that Frank's method of managing keys and creating records can be simplified a little further:

Type Employee
	Field LastName$
	Field FirstName$
	Field Position$
End Type

Dim Employees.Employee(100)

Employees(20) = New Employee
Employees(20)\LastName$ = "Smith"
Employees(20)\FirstName$ = "Joe"
Employees(20)\Position$ = "Manager"

Print Employees(20)\Position$ + ": " + Employees(20)\FirstName$ + " " + Employees(20)\LastName$
; Prints "Manager: Joe Smith"

PrismaticRealms,,

Yeah, you can do that too. hehe. I normally reserve the label for generic use like for a For...Each. I've have used various labels in the past such as:

Dim EmployeeObject.Employee
Dim EmployeePTR.Employee
Dim EmployeeRef.Employee

But, Dim EmployeeID.Employee() made more sense for concept of using ID Keys. Automating the assignment of IDs can be real fun too.