Recent

Author Topic: TreeView and SQLite?  (Read 5049 times)

KarenT

  • Full Member
  • ***
  • Posts: 120
TreeView and SQLite?
« on: November 20, 2018, 07:32:16 pm »
Hi, OK, my "simple and little," backup program is suffering severe feature-creep. :)

Currently my backup has an SQLite DB of main folders and then at run-time, it uses recursion to gather the folders and files within. I'd like to be able to select a few files not to be backed up and for that I need a tree-view of folders and files.

Can the TreeView auto-generate the Nodes from the Paths?

Has anyone used a TreeView with SQLite as the source of data that is made up of File-Paths?

Anyone care to give me a kick-start on how to go about it? I have not yet found any code-snippets loading a TreeView from a database of Paths. Pretty sure I am not the first one to want to do it.

balazsszekely

  • Guest
Re: TreeView and SQLite?
« Reply #1 on: November 21, 2018, 06:24:05 am »
So you basically wish to recursively search a directory and display the result in a treeview structure with check support? If yes then you should go wit VirtualStringTree. It's installed by default on Lazarus 2.0.

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: TreeView and SQLite?
« Reply #2 on: November 21, 2018, 07:15:27 am »
The database components of JVCL (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/jvcllaz/) contain a TJvDBTreeView which reads a database table or query and displays it as a tree.  There must be at least three fields: the name of the path element (let's call it "Caption"), some kind of numerical "ID" to identify the node and a "ParentID" with the value of the "ID" field of the node to which the record belongs. Open your SQLite file in the LazDataDesktop (its sources are in (lazarus)/tools/lazdatadesktop) or some other tool to learn the structure of the dataset.  In the JvDBTreeView the name of the field "Caption" must be specified in property "ItemField", the field "ID" must be specified in "MasterField", and the field "ParentID" in "DetailField". Open the demo in examples/JvDBTreeView to see a working example.

The easiest way to install the dbtreeview is using the Online Package Manager: Find the entry "JVCLLaz", either check this node to get all JVCL components (which is maybe overkill), or check on the node "JvDBLazD" to get the database components only. Click "Install" > "From repository" and confirm to load dependent packages, too. (You probably won't get the demo in the latter case, though).

Another possibility is VirtualDBTreeEx (https://github.com/adem0x/virtualdbtreeex) which depends on VirtualTreeView mentioned by GetMem. (I don't know whether it works with the version of VTV package with Lazarus). I did not use it, but it should require more or less the same three fields.
« Last Edit: November 21, 2018, 07:25:24 am by wp »

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1260
Re: TreeView and SQLite?
« Reply #3 on: November 21, 2018, 12:10:38 pm »
The following may help.   In my world, I have a heirarchy of assets that I display in the tree.  My database essentially has a single field called FULL_LOCATION that looks like 'Component1\Component2\Component3'.   I may or may not have records that define 'Component1' or 'Component1\Component2' so the following code populates those missing parents utilising ExtractField, and using '\' as the delimiter

EDIT
*Embarrased cough* Apologies to those who saw the original post - I hadn't realised that the code contained helper functions from my support units...

Here's a simplified version of the original.  All you need to do is cycle over your database, calling AddLocation for each record.

Code: Pascal  [Select][+][-]
  1. Procedure TForm1.Button1Click(Sender: TObject);
  2.  
  3.   Function ExtractField(Const sValue: String; cSeparator: Char; iIndex: Integer): String;
  4.   Begin
  5.     Result := ExtractWord(iIndex + 1, sValue, [cSeparator]);
  6.   End;
  7.  
  8.   Function FindNextString(Const sStringToSearch, sSubstr: String; iStart: Integer): Integer;
  9.   Begin
  10.     If iStart > Length(sStringToSearch) Then
  11.       Result := 0
  12.     Else
  13.     Begin
  14.       Result := Pos(sSubstr, PChar(@sStringToSearch[iStart]));
  15.  
  16.       If Result > 0 Then
  17.         Inc(Result, iStart - 1);
  18.     End;
  19.   End;
  20.  
  21.   Function Count(Const sSubstr, sStringToSearch: String): Integer;
  22.   Var
  23.     iPos: Integer;
  24.   Begin
  25.     Result := 0;
  26.  
  27.     iPos := FindNextString(sStringToSearch, sSubstr, 1);
  28.     While (iPos > 0) Do
  29.     Begin
  30.       Inc(iPos, Length(sSubstr));
  31.  
  32.       iPos := FindNextString(sStringToSearch, sSubstr, iPos);
  33.  
  34.       Inc(Result);
  35.     End;
  36.   End;
  37.  
  38.   Function AddNode(AParent: TTreeNode; AComponent: String): TTreeNode;
  39.   Begin
  40.     If Assigned(AParent) Then
  41.       Result := tvComponents.Items.AddChild(AParent, AComponent)
  42.     Else
  43.       Result := tvComponents.Items.Add(nil, AComponent);
  44.   End;
  45.  
  46.   Function AddLocation(sLocation: String): TTreeNode;
  47.   Var
  48.     sComponent: String;
  49.     iField: Integer;
  50.     iCount: Integer;
  51.     oParent, oNode: TTreeNode;
  52.  
  53.   Begin
  54.     iCount := Count('\', sLocation) + 1;
  55.    // Number of components within Location string
  56.     iField := 0; // and lets start with the very first one.
  57.     If iCount > 1 Then
  58.       sComponent := Trim(ExtractField(sLocation, '\', iField))
  59.     Else
  60.       sComponent := sLocation;
  61.  
  62.     oNode := nil;
  63.     oParent := nil;
  64.  
  65.     While iField < iCount Do
  66.     Begin
  67.       If Not Assigned(oParent) Then
  68.         oNode := tvComponents.Items.FindTopLvlNode(sComponent)
  69.       Else
  70.         oNode := oParent.FindNode(sComponent);
  71.  
  72.       If Not Assigned(oNode) Then
  73.         oNode := AddNode(oParent, sComponent);
  74.  
  75.       oParent := oNode;
  76.       iField := iField + 1;
  77.  
  78.       If iField < iCount Then
  79.         sComponent := Trim(ExtractField(sLocation, '\', iField));
  80.     End;
  81.  
  82.     Result := oNode;
  83.   End;
  84.  
  85. Begin
  86.   AddLocation('Parent\Child1\Child2');
  87. End;
« Last Edit: November 21, 2018, 12:28:57 pm by Mike.Cornflake »
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: TreeView and SQLite?
« Reply #4 on: November 21, 2018, 05:11:18 pm »
Mike's post motivated me to try it without a ready-made dbtreeview. The attached demo uses a standard TTreeView and a few event handlers to display the database records in a tree.

When the demo runs for the first time it creates a dummy sqlite3 database containing the elements of paths and the corresponding IDs and ParentIDs as records. For example, the filename "c:\windows\fonts\Arial.ttf' is stored like this:
  • At the top level there is the node for the drive "c:". It has ParentID -1 indicating that its parent is nil (root) - this is a convention within the program. The ID of the node is set to some arbitrary value, here 0.
  • "windows" is a child of node "c:", i.e. its ParentID is the ID of "c:", 0, and its own ID is given some other value, 2.
  • The next node "fonts" is a child of "windows" and has the ID of "windows" as ParentID (2) and gets some other ID value, here 3.
  • "Arial.ttf", finally, is a child of "fonts" and has ParentID 3 and gets an ID 4.
The dataset stores also a number for the "FileType": 0 is a drive, 1 is a directory, 2 is a file. This value is used as the image index into a provided imagelist.

The method "PopulateNode" is called whenever a node expands, it has the expanding node as a parameter. Since the ID is stored in the Data field of each TreeNode the procedure determines the parentID from the node and uses it in a query to filter all records for which the ParentID field is the same as the ID of the expanding node. For all these records a node is added to the expanding node.

Whenever a node collapses the child nodes are deleted to keep the memory load of the program at minimum.
« Last Edit: November 22, 2018, 09:36:51 am by wp »

KarenT

  • Full Member
  • ***
  • Posts: 120
Re: TreeView and SQLite?
« Reply #5 on: November 21, 2018, 10:11:19 pm »
Wow, talk about "above and beyond the call ..." Thanks so much to you both for those efforts.

I have grabbed copies and will spend some time with them. Just reinstating Ubuntu 16.04 after a foolish "Restore" where I didn't read the prompt. {grin}  Arrgggghhh!

KarenT

  • Full Member
  • ***
  • Posts: 120
Re: TreeView and SQLite?
« Reply #6 on: November 23, 2018, 06:15:08 pm »
Thanks guys, I am working on this, some of the SQL stuff in wp's attachment is new to me so I need the understand what I should be doing with the Transactions.

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: TreeView and SQLite?
« Reply #7 on: November 23, 2018, 06:26:15 pm »
I should be doing with the Transactions.
This has nothing to do with the initial question of a treeview representation of a dataset. The transactions came in because I used in the demo TSQLQuery to access SQLite3, and this component depends on transactions. How do you access the SQLite3 table?

 

TinyPortal © 2005-2018