Recent

Author Topic: MightyQuery SQL Database Management Software  (Read 12681 times)

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
MightyQuery SQL Database Management Software
« on: June 06, 2011, 09:49:35 pm »
Hello, I have written a small database management system called "MightyQuery" and I'm looking for interested contributors for further development.

You can download source code and binaries from:

http://www.4shared.com/folder/XL3EZwUR/MightyQuery.html

MightyQuery is a single user SQL database management software working with data stored as comma separated values (.csv, .txt) in plain, flat, textual files. It is serverless, self-contained, simple  SQL DBMS for querying CSV files. In contrast to most database systems, MightyQuery does not require neither server installation, neither external libraries. And is multiplatform (due to Lazarus).

It relies on JanSQL database engine , which original code was written by Jan Verhoeven (http://jansfreeware.com) in Delphi and released under MPL 1.1. license.
As far as I am aware, this might be the only existing free/open-source SQL database engine written in Pascal, thus worthy of preservation and improving.

To enable processing of external data, Mighty Query contains importing utility (due to Zeosdbo), supporting wide range of  SQL database servers, such as: PostgreSQL, Interbase, Firebird, MSSQL, Sybase, SQLite, Oracle, ado, ASA.

MightyQuery uses TDbf for storing metadata about projects (databases), tables, queries, external sources of data. Retrieved data from JanSQL is transfered to TBufDataset for viewing, filtering and searching.

I'm looking forward for your ideas, support and critics.

If someone is interested, we could port this project to SoureForge...

« Last Edit: August 04, 2011, 01:26:24 pm by tatamata »

joseme

  • Full Member
  • ***
  • Posts: 128
    • Logosoft sistemas
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #1 on: June 06, 2011, 10:41:25 pm »
Interesting. Do you know what are the practical limits of MightyQuery?
un aporte a la comunidad:
http://pascalylazarus.blogspot.com/

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #2 on: June 06, 2011, 11:04:25 pm »
Well, there are many limits at this moment.
- Some queries with joined tables and few conditions in WHERE clause are terrible slow or even freezes the program. I have found that I can overcome that issue by using ASSIGN TO statement. With ASSIGN TO you can aasign a query dataset to a variable that you can use in next SQL statement. So, I split a query in two queries and then it works.
Obviosly, one of the priorities of the MightyQuery project should be to improve JanSQL database engine.
- JanSQL uses only dot for decimal separator. There should be possibility to take over local system settings.
- JanSQL deals only with textual files, thus no binary fields.
- There are no indexes in janSQL
- MightyQuery currently does not have visual query designer.
- Currently MightyQuery process only one query at time, only one window is open
etc, etc.
There is lot of job to do.

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #3 on: June 07, 2011, 09:17:31 am »
TODO:
- improve JanSQL to be able to deal with complex joining (WHERE clause), maybe to add JOIN expression.
- Design visual query builder
- make wrapper components for JanSQL, for example: TJanDatabase, TjanTable, TJanActionQuery, TJanSelectQuery...Datasets to be TBufdataset descendants?
- SQL Query Editor: add Code Highlighting and IntelliSense
- separate procedures from the main form unit code into separate unit...

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #4 on: July 24, 2011, 10:59:35 pm »
MightyQuery version 1.0.5 is out.
You can download both source and binaries from:
http://www.4shared.com/folder/XL3EZwUR/MightyQuery.html
Changes from previous version:
  • SQL syntax colouring and highlighting
  • auto-completion in SQL editor ([Ctrl]+[Space]
Next few days I am going to clean-up whole code, so the next version will be rewritten and based on zmsql package (TZMonnection and TZMQueryDataset components). In future, these two projects (MightyQuery and zmsql) will be synchronized.
« Last Edit: July 25, 2011, 08:59:04 am by tatamata »

fredycc

  • Sr. Member
  • ****
  • Posts: 265
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #5 on: July 24, 2011, 11:41:35 pm »
Cool, very interesting project  ;D

Thanks

Regards.

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery 1.0.4 SQL Database Management Software
« Reply #6 on: July 25, 2011, 09:08:23 am »
In following weeks I am going to focus on zmsql package (http://www.lazarus.freepascal.org/index.php/topic,13821.msg72958.html#msg72958) that will be basis for future MightyQuery versions.
Namely,
- adding master/detail filtering
- enforcing refferential integrity
- improving JanSQL database engine
- enabling parameterized queries
- to enable autoincrement field type

After that I will focus on the MightyQuery:
-visual query builder
- form generation, reporting
- scripting

Yeah, a long way to go...  8)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: MightyQuery 1.0.5 SQL Database Management Software
« Reply #7 on: July 25, 2011, 09:59:05 am »
Tatamata,

Sounds good (especially a visual query designer is something I've wanted for a long time), but yes, a long way to go.

Might be a good idea if you got this and zmsql into Lazarus CCR if possible, or on Sourceforge/Google code/Github/Bitbucket...
In this way, your commits will be visible immediately using the version control system, and many of these services also provide bug trackers etc...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery SQL Database Management Software
« Reply #8 on: August 04, 2011, 04:37:19 pm »
MightyQuery 1.0.7 is released. This is bug fixes release.
In previous version there was a stupid bug that prevented proper connecting to a "database" when scrolling through "Projects".

Binaries and source code can be downloaded from:
http://www.4shared.com/folder/XL3EZwUR/MightyQuery.html

bee

  • Sr. Member
  • ****
  • Posts: 348
Re: MightyQuery SQL Database Management Software
« Reply #9 on: March 20, 2018, 09:42:02 am »
Hello, @tatamata …

Is this Mighty Query project still alive? I wonder what the latest features you have added into TjanSQL component. I might need it for a little project of mine.

Thank you.
-Bee-

A long time pascal lover.

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery SQL Database Management Software
« Reply #10 on: March 20, 2018, 10:23:01 am »
« Last Edit: March 20, 2018, 10:25:58 am by tatamata »

tatamata

  • Hero Member
  • *****
  • Posts: 656
    • ZMSQL - SQL enhanced in-memory database
Re: MightyQuery SQL Database Management Software
« Reply #11 on: March 20, 2018, 10:35:58 am »
I have another, more recen tversion, you can download here:
https://drive.google.com/file/d/1CrSqzxD4WIc-uPmx0kSo7OAEW9b6ZIOF/view?usp=sharing

bee

  • Sr. Member
  • ****
  • Posts: 348
Re: MightyQuery SQL Database Management Software
« Reply #12 on: April 16, 2018, 06:07:37 pm »
Just got the time to reopen this old library. I barely remember anything about this library. I recalled janSQL library because I want to use it as an engine to query csv files. I modified them a bit just so it can be compiled using pure FPC (v.3.0.2), removing its dependency to Lazarus units.

Here's what I did:
1. Removed all LCL units from the uses clauses. It went without any problems because they are not actually being used by janSQL library.
2. Renamed toString of TTokenOperators (in janSQLTokenizer.pas) into toText because it conflicts with new FPC's string function.

After those little modifications, the library and all of its units can be compiled just fine. But my test code failed on a simple select command. Here's my test code:

Code: Pascal  [Select]
  1. program jSQLcli;
  2.  
  3. {$MODE OBJFPC}{$H+}{$J-}
  4.  
  5. uses
  6.   janSQL;
  7.  
  8. var
  9.   sampleDB: TjanSQL;
  10.   sqlResult: integer;
  11.  
  12. procedure printResult(resultIndex: integer);
  13. var
  14.   r,c,i,j: integer;
  15. begin
  16.   c := sampleDB.RecordSets[resultIndex].FieldCount;
  17.   r := sampleDB.RecordSets[resultIndex].RecordCount;
  18.   // print column's name
  19.   for i := 0 to c-1 do
  20.     if i < c-1 then
  21.       write(sampleDB.RecordSets[resultIndex].FieldNames[i],' | ')
  22.     else
  23.       writeln(sampleDB.RecordSets[resultIndex].FieldNames[i]);
  24.   // print cell's value
  25.   for i := 0 to r-1 do
  26.     for j := 0 to c-1 do
  27.       if j < c-1 then
  28.         write(sampleDB.RecordSets[resultIndex].Records[i].Fields[j].Value,' | ')
  29.       else
  30.         writeln(sampleDB.RecordSets[resultIndex].Records[i].Fields[j].Value);
  31. end;
  32.  
  33. begin
  34.   sampleDB := TjanSQL.Create;
  35.  
  36.   sqlResult := sampleDB.SQLDirect('select * from authors');
  37.   if sqlResult <> 0 then
  38.   begin
  39.     if sqlResult > 0 then
  40.     begin
  41.       printResult(sqlResult);
  42.       // release recordset
  43.       if not sampleDB.RecordSets[sqlResult].Intermediate then
  44.         sampleDB.ReleaseRecordset(sqlResult);
  45.     end;
  46.   end
  47.   else
  48.     writeln(sampleDB.Error);
  49.  
  50.   sampleDB.Free;
  51. end.
  52.  

The error message is SELECT: can not find table authors. The authors table is from the sample database (authors.txt). I tried both Tatamata's and Theo's version, same error.

Any help? Thank you.
-Bee-

A long time pascal lover.

Soner

  • Full Member
  • ***
  • Posts: 103
Re: MightyQuery SQL Database Management Software
« Reply #13 on: April 16, 2018, 08:25:43 pm »
You must call "connect" command before:
Code: Pascal  [Select]
  1. begin
  2.   sampleDB := TjanSQL.Create;
  3.  
  4.   sampleDB.SQLDirect('CONNECT TO ''C:\MyApp\db'''); //<<-- Folder where the authors.txt exist
  5.   sqlResult := sampleDB.SQLDirect('select * from authors');
  6.   //...
  7. end.
  8.  
  9.  

bee

  • Sr. Member
  • ****
  • Posts: 348
Re: MightyQuery SQL Database Management Software
« Reply #14 on: April 17, 2018, 03:44:47 am »
Thank you. Sorry for asking a silly question like that. As I said, I barely remember anything about this library. I think I had to dig out the sample program again to know how to use it.

I'm going to experiment with it in the next few days. I assume all the queries in the samples.txt would work. I'll use it to test the library. So, expect some other questions from me again later. Thank you.

Btw… I'm on Mac. I was looking for an app to read .hlp files, to read TjanSQL documentation. Does anyone here know how to read .hlp file on Mac? Or is there an online service to convert .hlp files into .html files? Thank you.
-Bee-

A long time pascal lover.