DBF<-->SQLite Exporter

Utilities like DBU, Make, IDE written in HMG/ used to create HMG based applications

Moderator: Rathinagiri

User avatar
mol
Posts: 2842
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 119 times
Been thanked: 65 times
Contact:

Re: DBF<-->SQLite Exporter

Post by mol » Wed Oct 03, 2012 6:00 pm

I worry about autoincrement fields - for example in MS SQL Server you can't put data in these fields - they are filled by sql server.
I don't know, how it works in sqlite - is it possible to write data to autoincrement fields??? I'll test it.

User avatar
esgici
Posts: 4337
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 221 times
Been thanked: 77 times
Contact:

Post by esgici » Wed Oct 03, 2012 6:07 pm

Hi Marek
mol wrote:I worry about autoincrement fields - for example in MS SQL Server you can't put data in these fields - they are filled by sql server.
I don't know, how it works in sqlite - is it possible to write data to autoincrement fields??? I'll test it.
This is normal and foreseen behavior. I don't know this or that servers, but in Harbour auto-increment fields are non-replaceable.

Regards.
Viva INTERNATIONAL HMG :D

User avatar
Rathinagiri
Posts: 5166
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 129 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Wed Oct 03, 2012 6:14 pm

is it possible to write data to autoincrement fields???
Yes you can, provided if it is unique.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2842
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 119 times
Been thanked: 65 times
Contact:

Post by mol » Wed Oct 03, 2012 6:20 pm

Yes - it works!
Some days ago, I've tried to repair MS SQL Database and it was not possible to do it. Or maybe my knowledge is too small :lol:

But - whole life we are learning and stay stupid to death :lol: :lol: :lol:

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Thu Oct 04, 2012 1:09 pm

rathinagiri wrote:Thanks a lot for the detailed explanation Qatan.

I didn't use DBF autoincrement fields. So I didn't know about that. I will dig into it and make use of it.

1. Regarding ICU, unfortunately HMG is not ready for Unicode implementation even though Harbour is. There are many places to be changed for unicode implementation.

2. SQLCipher is possible now. You can encrypt/decrypt the whole database on the fly without any hustle. You can download the library and details from here.

viewtopic.php?f=7&t=2416

3. Will FTS be possible without ICU?

Regarding encryption and decryption I have coded a small utility if you have installed the SQLCipher properly.
encryptsqlite.zip
Hello Rathinagiri,

Thanks for answering.

FTS is independent of ICU. ICU is an external component that you can add to SQLite (or Harbour DBF).
FTS is useful to look on the database for a certain word(s) or code(s). Has many uses and makes the application very fast. Imagine that you have a car factory and you need to know what is the engine part on a database that has millions of rows and the only information you have is part of the code and the brand then with FTS you will find it very quick.

It would be nice to have ICU working with SQLite and I belive you do not need to have HMG UTF8 ready... Well as far as I understand you need to have your database set to UTF8 and your application just needs to have hb_strtoutf8() / hb_utf8tostr() convertions when necessary.

I was looking the Changelog and found this:
------
2010-11-01 00:05 UTC+0200 Petr Chornyj (myorg63 at mail.ru)
* harbour/contrib/hbsqlit3/hbsqlit3.c
* Changed syntax of sqlite3_exec() to
sqlite3_exec( db, cSQLTEXT, [pCallbackFunc]|[cCallbackFunc],
[lConvertSQLTextToUTF8], "cCDP" ) -> nResultCode
From now we can replace
cSQLTEXT := hb_strToUTF8( cSQLTEXT, "RU1251" )
sqlite3_exec( db, cSQLTEXT )
with
sqlite3_exec( db, cSQLTEXT, , .t., "RU1251" )
* Changed syntax of sqlite3_bind_text(), sqlite3_column_text()
in such way
* Changed sqlite3_bind_blob() according to sqlite3 docs.
------

So I think you can have your database in UTF8 and you program can be any other codepage.
What do you think?
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Rathinagiri
Posts: 5166
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 129 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Thu Oct 04, 2012 1:13 pm

It is so nice to know. I will try.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Thu Oct 04, 2012 3:54 pm

I am happy you are interested.
I am interested also but I do not know how to activate FTS or how to integrate ICU component...
Seems a bit over complicated to me (like SQLCypher) but I belive it is important and I know you can do it because I think SQLCypher was tricky to build and you did so ICU shouldn't be that difficult (maybe I am underestimating it)
Well for me is very difficult... I don't know where to start.

I do not know what people think but I like SQLite and I belive it can replace DBF with some advantages. I do not know about any disavantage. Do you know any?
I remember Marek had a problem with record locking issue but I don't think it is a problem that in SQLite you lock the whole file (or table) because it is even safer to me, you can select the type of update (you have 3 options) and the bugs you find in Windows and *nix that may cause corruption on your database can happen in DBF as well (or even worse...)
I am not comparing SQLite to PostgreSQL, but to DBF... Do you understand what I mean? I belive SQLite is a very nice option.
If someone needs a database for heavy concurrency they should go directly to PostgreSQL that is the best option (the most powerful available that is free) but difficult to install and manage. (or go to MariaDB) and think about BerkleyDB also (http://en.wikipedia.org/wiki/Berkeley_DB)

What do you think? I would like to hear from you because I did read that you are confortable with it and that you did use in real life applications (I didn't yet...)

Thanks for your help and for sharing your knowledge.
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Rathinagiri
Posts: 5166
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 129 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Thu Oct 04, 2012 4:30 pm

Hi Qatan,

Regarding FTS and ICU I will try this weekend.

I am very much comfortable with SQLite and especially SQLCipher. I use SQLite in many projects including Ware-soft. All of them are single user systems. For big networks, I use MySQL/MariaDB. As of now, I don't use dbf in any of my projects. I have converted all my business applications to either SQLite/MySQL and am in the process of converting an old and huge application into SQL based.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Fri Oct 05, 2012 10:31 am

That's very good.

I would suggest you to offer it to the Harbour developers... as a Contrib maybe or an addon to the SQLite.
It's pretty possible the main developers/admins (Viktor / Przemek) would accept it...
Maybe with detailed instructions like you did to port it to Windows (I believe to Linux is easier).
It would be for sure a nice add to the Harbour-Project and you could be part of the contributors. Just an idea...
Of course with some examples but without HMG stuff because one should be able to build the examples/tests without HMG.

What do you think?

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Rathinagiri
Posts: 5166
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 129 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Fri Oct 05, 2012 12:00 pm

Hi Qatan,

Are you talking about SQLCipher?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

Post Reply