Jump to content
Sign in to follow this  
jarto

Database development

Recommended Posts

I've been doing some work to create data aware controls for SMS. I'm aiming to do a versatile solution, where you can access data from a TDataset by using a TDataLink, no matter what the data comes from. So it could be a cvs-table, json, sql query etc.

I started by building a TW3DataSet (and fixing some bugs) and writing a simple TW3DataSource and TW3DataLink. Then a data-aware Label, which shows the data properly. It's pretty much the same mechanism as with Delphi and Lazarus.

Today I started then adding SQLite. I started by downloading a database file with TW3HttpRequest and loading it from a stream. Again, a few bugs and improvements had to be made, but now it works and I have a nice mechanism for loading any binary data from a file.

But now then... Making it possible to read the results of a SQLite query through a dataset.... Here I hit a little road block and don't know how to proceed. It's simple to make a select-query and access that data for results, but what's the best way for me to know what the fields are? Their names and datatypes?

Share this post


Link to post
Share on other sites

I guess there are a couple of approaches :

- Have access to the meta information when parsing results. Most SQL based rdbms's have type information stored in meta tables, which is accessible by a normal query. Unfortunately this is not standardised, so MySQL, Oracle, DB2, SQL server etc have differently named tables for that purpose. For SQLite see this link.

- Let the developer define the meta data in the designer while setting up the data link, and store it somewhere persistently (TDatalink ?). Then parse the results with this stored meta data.

- If you only want to know if a field is numeric or not (I don't think that's your question, but anyway) then there is the poor mans solution : just read the first 100 records or so, and if a field only contains 0-9 then assume that field to be numeric across the whole result set. Not recommended (but I did it once to figure out the left or right alignment of fields in a grid)

Share this post


Link to post
Share on other sites

@lynkfs Thank you. It's possible to get the table fields with a query in SQLite as well. For example:

select sql from sqlite_master where tbl_name="Employee";

That returns:

CREATE TABLE [Employee] (
[ID] INTEGER  PRIMARY KEY NULL,
[Name] TEXT  NULL,
[Department] text  NULL,
[Seniority] INTEGER  NULL
)

So, if the query selects all fields from that table (select * from Employee....), it's quite simple to find the field name. However, when only some fields are selected (select Name, Department from Employee...), I'd need to analyze also, which fields are selected.

Problem is, this can get pretty complicated. I have no control over what the sql-query is. There can possibly be joins, functions etc. For example:

select Name,lower(Department) from Employee;
select Name,lower(Department) as Position from Employee;

When I test those, I notice that Lazarus' TDBGrid does add the second column as "lower(Department)" or "Position". So it seems that it analyzes the sql-query itself and creates the fields from there. Makes sense.

Then, the next big question is editing data. In a simple "select *" it's easy, but I wonder if the other data aware controls simply prevent editing, when the field name is a function or an alias.

Share this post


Link to post
Share on other sites

Seems like DBGrid in Lazarus can parse this properly as well:

select lower(customers.FirstName) as FirstName,lower(customers.LastName) as LastName, lower(employees.FirstName) as Representative from customers,employees where customers.SupportRepId=employees.EmployeeId;

The dataset used is chinook.db from here: https://www.sqlitetutorial.net/sqlite-sample-database/

Share this post


Link to post
Share on other sites

They must have some sort of an sql parser.

Some links :

Commercial vcl (delphi tokyo) parser :

http://sqlparser.com/download.php
http://sqlparser.com/dlaction.php?fid=gspdxe10_2_tokyo_trial&ftitle=General%20SQL%20Parser%20VCL%20for%20delphi%20XE10.2%20tokyo%20trial%20version

and some really old ones

https://torry.net/quicksearchd.php?String=sql+parser&Title=Yes
http://www.felix-colibri.com/papers/db/sql_parser/sql_parser.html

to write a complete sql parser from scratch might be a big job

Share this post


Link to post
Share on other sites

Reading a bit of Lazarus source, it seems they don't have a full blown sql parser. For example, they don't allow updates if the select is from multiple tables or derived tables. So it's probably a good idea to aim at being able to show a dbgrid from a select-query and try to retrieve and parse the field names. I'd better add a possibility to provide or adjust field names as well. After all, if you use complicated SQL, you do know what your columns are and what you want to view.

Share this post


Link to post
Share on other sites

Heh, well, this actually looks now a lot easier. Sqllite actually returns the parsed column names in the results. It even works with field aliases:

lower(employees.FirstName) as Representative

That was correctly returned as Representative

Share this post


Link to post
Share on other sites

> Today I started then adding SQLite. I started by downloading a database file with TW3HttpRequest and loading it from a stream. Again, a few

> bugs and improvements had to be made, but now it works and I have a nice mechanism for loading any binary data from a file.

Is that for readonly sqlite databases? Or do you mean to store updated one in localstorage?

> But now then... Making it possible to read the results of a SQLite query through a dataset.... Here I hit a little road block and don't know how to proceed. It's

> simple to make a select-query and access that data for results, but what's the best way for me to know what the fields are? Their names and datatypes

SQLite on desktop returns both fieldname and datatype, I guess that JS SQLite version doesn't return all that, so I think best is just to use Variant for all fields, and leave to user of Dataset to access it AsStr, AsInt, AsFloat...

After all writer of that query should know what columns he'se selecting in query and what are datatypes.

 

 

Share this post


Link to post
Share on other sites
2 hours ago, IgorSavkic said:

Is that for readonly sqlite databases? Or do you mean to store updated one in localstorage?

What I am now using for testing is to read the db-file to memory and accessing it there. After that, the db can be saved to a stream.

However, when doing this infrastucture, it has to be versatile enough, so I don't restrict it only to a memory based sql database. It needs to be extendable so, that the sql-queries could just as well be sent to a remote server or a cloud based database.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×