SQL for Blitz Custom Types
Blitz3D Forums/Blitz3D Programming/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 TypeNow 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 FunctionAn 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 = 8Now 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 SELECTBlitz 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? |
| ||
but why would you need id's when you can do the relations between types like this: ID Key will basically serve the same purpose as they do in Relational Databases. 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? 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. |
| ||
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 NextEven 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.EmployeeBut, Dim EmployeeID.Employee() made more sense for concept of using ID Keys. Automating the assignment of IDs can be real fun too. |