MightyQuery SQL Database Management Software

[bee]

I've made some small modifications to janSQL library based on tatamata's version. I removed all unused LCL/VCL units so it can be compiled using only standard pascal units. I put them on my github repo here: https://github.com/git-bee/janSQL

I also have added a simple client program, mainly to test the SQL engine. I've found some problems. Feel free to contribute. Thank you.

[Soner]

...
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.

I would install Windows Xp in Virtualbox and use it as Hlp-Viewer.
This here is also good help converter http://www.helpscribble.com/.

Also I played around with it last autumn and created a gui example.
It is Lazarus port of Jans example. I
I put it as attachment.

[bee]

I would install Windows Xp in Virtualbox and use it as Hlp-Viewer.
This here is also good help converter http://www.helpscribble.com/.

I don't even have a Windows XP installer to begin with. Would any Windows users here help me convert janSQL's .hlp files into common text format such as .pdf, .hmtl, or .txt? It should be an easy job if you're a Windows user. I'd like to put them in the public repo so everyone could read it. Thank you.

Also I played around with it last autumn and created a gui example.
It is Lazarus port of Jans example. I put it as attachment.

Thank you. Is your version newer than tatamata's? Do all the query examples in the sample.txt file work correctly in your GUI example? I found some of them don't work correctly with tatamata's version. I'm gonna try them with your version. Can you provide some more complex query examples that work with your janSQL version? It'd be helpful to janSQL users, also to show janSQL features.

The original janSQL units put some VCL or LCL units that are not actually being used. They're still there in tatamata's version, I still saw them in yours as well. I would remove them in order to make janSQL doesn't require Lazarus. It's not a GUI library anyway.

[bee]

Is your version newer than tatamata's? Do all the query examples in the sample.txt file work correctly in your GUI example?

After comparing the files of tatamata's version and soner's version, I'm pretty sure that soner's is based on tatamata's version, plus some modifications and new features added.

The original janSQL units put some VCL or LCL units that are not actually being used. They're still there in tatamata's version, I still saw them in yours as well.

I was wrong, I might have opened a wrong file. Soner had done it already. So, I think it's safe to use soner's version to start enhancing this janSQL library. I'll update my github repo to use soner's version, after I clean up my mess with these files. [Update: done!]

But, documentation is still a problem as long as I have no access to a Windows machine, to convert the .hlp file.

[Soner]

I took janSQL from tatamatas TZMSQL and I made some changes then I gave my changes to tatamata. I think he integrated my changes.
I comment my modifications every time with my name to follow it.
You can take tatamatas version and when you improve this library (jansql) then you can give your changes to tatamata.

I converted the help-file to doc-file and html-file (copy/paste from original jansql.hlp).
I thought it was small file but it took over an hour to copy it.
Here is it as attachment, have fun:


@tatamata: You can use this doc in your TMZSQL when you want.

[Soner]

..
Do all the query examples in the sample.txt file work correctly in your GUI example? I found some of them don't work correctly with tatamata's version. I'm gonna try them with your version. Can you provide some more complex query examples that work with your janSQL version? It'd be helpful to janSQL users, also to show janSQL features.
..

Yes all examples are working also with tatamatas version. My Version is "same" as tatamatas i copied them only in the same directory to remove tmzsql-dependency.
Some examples like update, insert, alter has no output. You can look at statusbar at the gui example:
  Left: Count of result records.
  Middle: SQL execution time in tick difference.
  Right: Status text. 'OK' when no errors occured else errors as text.

JanSQL is very fast, i think it is good enough for small applications.
Start my example and select from [Menu > Samples > Insert in to select].
Then execute it 4 or 5 time after you get enough data rows. (Attention it can take long time to execute it)
You can count the data rows with this sql:

Code: SQL  [Select][+][-]

1. SELECT COUNT(*) FROM users
2.  

Now you can test the engine.
I had 65856 rows in users-Table and the query:

Code: SQL  [Select][+][-]

1. SELECT * FROM users WHERE productid='6'
2.  

took only 452 ticks.

When it does not work on your computer then maybe it is mac problem(especially date and time fields). Freepascal on Mac ignores your country settings. I put in my other program this at start:

Code: Pascal  [Select][+][-]

1.   {$IFDEF DARWIN} //German Dateformat
2.   FormatSettings.DateSeparator:='.';
3.   DateSeparator:='.';
4.   FormatSettings.LongDateFormat:='dddd, d. MMMM yyyy';
5.   LongDateFormat:='dddd, d. MMMM yyyy';
6.   FormatSettings.ShortDateFormat:='dd.MM.yyyy';
7.   ShortDateFormat:='dd.MM.yyyy';
8.   {$ENDIF}
9.  

[bee]

You can take tatamatas version and when you improve this library (jansql) then you can give your changes to tatamata.

I take your version and use it as the base for my next modification. All of the modification is open to public through my GitHub repo. Anybody who wants to contribute to the project could simply fork it and make a pull request. This is the new way of how open source projects work.

I converted the help-file to doc-file and html-file (copy/paste from original jansql.hlp). I thought it was small file but it took over an hour to copy it. Here is it as attachment, have fun!

Ha… thank you so much! This is what I need. I'll convert them to GitHub markdown documents and put them in the repo, so everyone could read them. Thank you!

Yes all examples are working also with tatamatas version. My Version is "same" as tatamatas i copied them only in the same directory to remove tmzsql-dependency.

Yes, I've tried your app on Linux and all queries work fine. So, I simply remove tatamata's version and use your version.

JanSQL is very fast, i think it is good enough for small applications.

I know, I've used the original janSQL a very long time ago.

When it does not work on your computer then maybe it is mac problem (especially date and time fields). Freepascal on Mac ignores your country settings. I put in my other program this at start:

Code: Pascal  [Select][+][-]

1.   {$IFDEF DARWIN} //German Dateformat
2.   FormatSettings.DateSeparator:='.';
3.   DateSeparator:='.';
4.   FormatSettings.LongDateFormat:='dddd, d. MMMM yyyy';
5.   LongDateFormat:='dddd, d. MMMM yyyy';
6.   FormatSettings.ShortDateFormat:='dd.MM.yyyy';
7.   ShortDateFormat:='dd.MM.yyyy';
8.   {$ENDIF}

Yes, I think it's not right to use local data format settings in the database file because it will make the database not portable. If you bring a database that use '.' as decimal separator –for example– then when you open it on a system that use ',' as decimal separator, janSQL wouldn't able to read the data and arise an error. So, I think the correct approach is to use a consistent data format for the database, then use local data format to display the data. CMIIW.

[tatamata]

Hello everybody.
I have resurected MightyQuery program and refactored it a bit with CodeTyphon. You can download source code and compiled binaries here:
Source code (CodeTyphon v. 8.00):
https://matasoft.hr/MightyQuery.7z
Compiled binaries for Linux and Windows:
https://matasoft.hr/MightyQuery_Binary.7z
I intend to work on it in following weeks, I want to add merge/append feature, to be able to combine multiple csv files similar to what can be done in Microsft Power Query...

[tatamata]

Hello everybody.
I have resurected MightyQuery program and refactored it a bit with CodeTyphon. You can download source code and compiled binaries here:
Source code (CodeTyphon v. 8.00):
https://matasoft.hr/MightyQuery.7z
Compiled binaries for Linux and Windows:
https://matasoft.hr/MightyQuery_Binary.7z
I intend to work on it in following weeks, I want to add merge/append feature, to be able to combine multiple csv files similar to what can be done in Microsft Power Query...

Re-uploaded compiled binaries and source code.
In source I have also zipped my pretty substantial modifications of ZMSQL package, but can also be downloaded here: https://matasoft.hr/pl_ZMSQL_v7.6.2.7z

I have done many changes in both MightyQuery and ZMSQL, of which most important changes in ZMSQL are:
      *ZMQueryDataset is now descendant of TRxMemoryData by defualt, but you can always switch back to TBufDataset, by compiler directive.
       Reason for this decision is because TBufDataset cannot load data from large csv files.
      *ZMBufDataset removed from package.
      *Removed dependency on TSdfDataset. TCSVdataset is now used for importing data from CSV files.
      *When importing data from CSV files, via TCSVDataset, FieldDef.Size and Field.Size properties are now determined by inspecting actual values through all rows.
       Rason for this is to decrease memory consumption for loading large csv files.
       Note that TCSVDataset does setup Size property always to Size=255, which is not optimal...
      *CopyFromDataset is renamed to CopyFromADataset, in order not to hide CopyFromDataset coming from TBuFDataset
      *Added function DetermineSeparator(AFileName: String; var HasFieldNames: Boolean): Char; to be used for automatic determination of field delimiter in a csv file.
      *Resolved bug that was preventing multiple consecutive loading of different csv files.
      *Addded procedure GetFieldsListFromCSVFile(List: TStrings);  This is fast method to determine field names in the csv file registered as TableName.
       Corresponding change made in unit vsb_Zmsql, in order for TZMQueryBuilder can load field information fast.
      *Added procedure CopyDataFromDataset(DataSet: TDataSet);
      *Various other minor changes and bugfixes     

[tatamata]

Reuploaded. Implemented changing order of columns in schema definition and merge/append combining of multiple tables.
Next thing to do: columns renaming. This is important for merge/appending, because columns are merged only if have the same name.
I am also thinking on adding row index indicator into RxDBGrids for csv file loading and sql query resultset loading.
I also intend to work on sorting datasets in dbgrids...

[tatamata]

Reuploaded, MightyQuery version 1.2.4
Solved columns renaming, implemented sorting in RxDBGrid for csv dataset and for ZMQueryDataset, I din't have time to do the same for TDBf dataset though.
I couldn't figure out how to add row indicator in the RxDBGrid, problem is that automatic columns are removed if another column is added during runtime, so I quit for now. The cause of trouble is here:

Code: Pascal  [Select][+][-]

1. function TDBGridColumns.Add: TColumn;
2. var
3.   G: TCustomDBGrid;
4. begin
5.   {$ifdef dbgDBGrid}
6.   DebugLn('%s.Add', [ClassName]);
7.   {$endif}
8.   G := TCustomDBGrid(Grid);
9.   if G<>nil then begin
10.     // remove automatic columns before adding user columns
11.     if not (gsAddingAutoColumns in G.GridStatus) then
12.       RemoveAutoColumns;
13.   end;
14.   result := TColumn( inherited add );
15. end;
16.