Converting a Project from TDbf to SQLite

[1HuntnMan]

Has anyone accomplished this before and recommend the best way to approach this.

I'm thinking to just write the code to create the database.  Then get that accomplished. Then start educating myself on changing all the forms?????

I'm researching and finding:

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

1. DestTable.FieldByName(s).Value := SourceTable.FieldByName(s).Value;
2.  

I'm thinking to just create the tables one by one. Trash the test data and modify the forms for SQLite3.

[egsuh]

I'd rather think of dbexport functions. The first that comes to mind is exporting existing file to csv file first, and then loading that csv file. You'd better check with TSQLiteDataSet of Lazarus.

--- I think I have been importing CSV files directly to other DB formats, but need to check.

[cdbc]

Hi
I'd write a little utility, that takes a '.dbf' file (a table) as input param and then

1) Connect to a new 'sqlite3' database, e.g.: named like the project,
   or connect to an existing database.
2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will
   create a similar(copy of) table in the sqlite3 db and execute it.
3) Runs through the 'dbf' file, reads the data and insert it into the newly
   created sqlite3 table.
4) Closes the database and the 'dbf' table/file ...and we're done.

5) "Rinse and repeat" for every 'dbf' table you have.

This is called a /datapump/ utility.

When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/ 
Have fun
Benny

[egsuh]

I found the simplest method (conceptually, at least).

You can call TSQLQuery.CopyFromDataSet, and then its ApplyUpdates.
You would have to CreateDataSet of SQLite tables, but I think it would be also possible to copy the structure of DBF tables. In this way you don't have to write any codes that process each field and each record by yourself.

[Zvoni]

Hi
I'd write a little utility, that takes a '.dbf' file (a table) as input param and then

1) Connect to a new 'sqlite3' database, e.g.: named like the project,
   or connect to an existing database.
2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will
   create a similar(copy of) table in the sqlite3 db and execute it.
3) Runs through the 'dbf' file, reads the data and insert it into the newly
   created sqlite3 table.
4) Closes the database and the 'dbf' table/file ...and we're done.

5) "Rinse and repeat" for every 'dbf' table you have.

This is called a /datapump/ utility.

When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/ 
Have fun
Benny

Be careful with this approach.
We don't know anything about Parent/Child-Tables/Relations (PrimaryKey/ForeignKey) of his design.

That said: Parent-Table before Child-Table
Meaning: Even the order in which you "export/import" any DBF-Tables to SQLite is important.
(Yes, i know there is a way in which you can ignore the order, but ... seriously???)

Might even be worth to check your design/layout of the Database-Structure and rework it if neccessary

EDIT: My Approach.
Create your SQLite-Database with an external tool (DBeaver, DB Browser for SQlite et al).
Create your Tables, Columns, Primary Keys, Foreign Keys, Constraints, Indexes whatever according to the Datatypes used for each Column.
Then use the CSV-Approach mentioned by egsuh exporting the Data in the DBF-Tables (IIRC DBeaver should be able to do that, if not event directly migrating it to SQLite)
and use the "Import CSV" which is available in DB Browser for SQlite.

That way you would be able to catch any "malformed" original Data.

Special care has to be provided for the "difficult" Datatypes, specifically DateTime

[jcmontherock]

I found an export example. I do not remember where, but it seems to work.

[1HuntnMan]

Thanks all, on the way. But, will try the export example to see how that works from jcmontherock.

[1HuntnMan]

Instead of tackling my larger Photographer's Mgt. System, I designed a contact mgt. system for myself when I was first learning Lazarus for my Windows PC since Winders doesn't provide a Contacts App. like Apple.  This way I'm just converting 1 Contacts data table and 4 lookup tables.
Thanks ...

[1HuntnMan]

Instead of tackling my larger Photographer's Mgt. System, I designed a contact mgt. system for myself when I was first learning Lazarus for my Windows PC since Winders doesn't provide a Contacts App. like Apple.  This way I'm just converting 1 Contacts data table and 4 lookup tables.
Thanks ...

Just a FYI, I already built a Database Creation procedure for this Contacts app but decided to abandon converting it SQLlite3 because it's just one table with supporting lookup tables, i.e. States, Countries, Contact Types.
Basically a larger app I'm working on already has a Database Schema creation procedure and took that as a separate app and converted all tables from Dbf to SqlLite3 and took each dbf file as input and connect to sqlite3 and wrote the sqlite code via TSQLiteDataSet from a dump from dbf to csv. Created sql statements that copies the structure into each sql table. Then, continue the march for the rest of the tables all in one procedure. A little more involved but it's doable...

[1HuntnMan]

I have just completed migrating a dBase Level 7 Database to SQLite.
The Laz Project is still dBase Level 7. I picked out a form that is just a maintenance form for StatesProvinces.  In Lazarus the only SQL Components are the SQLdb and I just see one component which is the TSQLite3Connection. The SQLite3 Database isn't password protected.  This system is a Photographers Mgt. application and probably won't be used by more than maybe 7-8 users but mostly 1-2.  So, what is HostName, KeepConnection (T/F), OpenFlags(sofCreate, sofReadWrite, etc.) don't know what to check here except Create and ReadWrite. Options (ApplyUpdates=True) ExplicitConnection=False? Don't need UserName. Once the user launches the form then and can just Select All. It's just a DBGrid just form maintaining States, Name and ISO Code.
My question is, is there a document that explains just implementing and creating forms, etc. for SQLite3?

[TRon]

My question is, is there a document that explains just implementing and creating forms, etc. for SQLite3?

Not a single document but a ton of wiki-pages starting with this but you are probably more interested in this by the looks of your question.

[1HuntnMan]

Alright TRon, looking at the SQLdb Tutorial1 now. Looks like that is just what the Doctor (Tron) Ordered! Thanks ...

[TRon]

 

As an additional advise, pleas don't try to do this on your working project in case this is your first time around the subject. It will save a lot of heartache.

Better approach would be to make a small test-project that allows to follow the wiki step-by-step in order to get a better understanding on how things work. That allows for a lot of experimenting without the risk that you mess up the project that you currently are trying to convert. In case you get stuck on a certain component and/or topic then you can ask specific questions or practice some yolo-ing in case feeling adventurous

[1HuntnMan]

Yup, I was thinking about doing this on a Contacts Mgt system I wrote for my Winders PC. iPhones, iPads and the Mac come with a Contacts Mgt app but Winders doesn't. It's database TDbf just has 4 tables. Contacts, State/Provinces, Countries and Categories. The States, Countries and Categories are just lookup tables for the contacts. But, in this Photographer's Mgt. System there are a lot of tables so I was just converting one form to SQLite3 which is where the app user maintains countries. It has just one table with a TDbf DataSet and DataSource. So, reading the tutorial it appears to use a Datasource and a SQLiteConnection to get started. But, I think I'm just going to create a small project that just has a DBGrid and strickly just Countries. I've already converted the tables to SQLite tables for all the tables in both the Contacts Mgt. app and the Photography Mgt. app. with the data using DBCopier. Thanks. TDbf is alot easier for starting out like I did 6 months ago so quite the learning curve converting to Sql.

[1HuntnMan]

TRon et. al.,
   I found a couple of demo's for SQLite named Demo and Demo2.  I loaded the Demo project into Lazarus just to see how everything was setup and worked. I compiled it and ran it but it errors, see the screen shots attached.  In Laz in the SQLdb components there TSQLiteConnection.  According to the errors, I need to install the SQLite client library(dll), correct? The errors occur from the main form if you attempt to Create a SQLite Database or Load one. The demo comes with a SampleData.sqlite file. Do I need to install the SQLite Client Library (dll)? It appears so but finding online where folks are having the same issue with the 64bit dll. I searched my computer and there are 7 sqlite3.dll's on my computer. The latest one (64bit) is located under C:\Program Files\TurboTax\Individual2024\64bit. Do I need to install or do I just need to copy the sqlite3.dll from the TurboTax folder into the Demo's folder's. I'll try that but my original question is do I need to install for Lazarus or not?