Jump to content

Please read the Forum Rules before posting.

Photo

Computer Science: e-Sword and Databases. Part 2


  • Please log in to reply
20 replies to this topic

#11 Ian Oh

Ian Oh

    New to Bible Support

  • Members
  • Pip
  • 6 posts
  • LocationAustralia
Offline

Posted 15 October 2021 - 02:03 AM

Hi  all

 

I have two requests if possible.

 

First request is regarding the crockett.harx database.  I'd like to improve on it for myself... a crocket+ if I can call it that so that I can have it all in one place.  I really like the way e-Sword has been able to display the Harmony and what I'd like to do is improve on Crockett's work just a touch ... e.g. 2Ki 20:1 has the passage when Hezekiah was sick and at the point of death.  There is a parallel passage in 2Ch 32.24.  I know that Crockett's work is limited to Samuel, Kings and Chronicles.  I'd like to add in the parallel passage in Isaiah 38:1 and the chapters 36-39.  It looks easy to do if I could just edit the SQLlite database ... I've just got a few entries to make and it would be fixed.  I tried using MSAccess to link to corckett.harx but I think my ODBC driver is read-only.  Is there a read-write ODBC driver? Or, do I have to do something to add rows to the table?

 

The second request is if it is possible to convert this to an Android database for use in mysword?  I'd love to be able to carry both Robertson and Crockett on my tablet.

 

Thanks very much.

 



#12 JPG

JPG

    Jon.

  • Moderators
  • 1,665 posts
Offline

Posted 15 October 2021 - 05:52 AM

You can edit the file using an SQlite3 editor. You could try "DB Browser for SQLite" https://sqlitebrowser.org/

Or one I use is http://www.sqliteexpert.com/

The Pro edition.

 

I did a test on the Crockett and added a couple records and data, and it presented fine.

 

The second request is if it is possible to convert this to an Android database for use in mysword?  I'd love to be able to carry both Robertson and Crockett on my tablet.

I suggest you contact the developers direct at MySword and ask them.



#13 Ian Oh

Ian Oh

    New to Bible Support

  • Members
  • Pip
  • 6 posts
  • LocationAustralia
Offline

Posted 15 October 2021 - 06:38 AM

Thank you.  That worked a charm.  I can see that the database file wasn't locked.  So it must be my ODBC driver.  I'll hunt for another ODBC driver if I want it to work with MSAccess.  But for the few edits that I've got for this effort, I'll use DB BRwowser for SQLite.   

 

Thanks for pointing me in the right direction.



#14 PeanutGallery

PeanutGallery

    e-Sword Fanatic

  • Members
  • PipPipPipPipPip
  • 148 posts
Offline

Posted 16 October 2021 - 07:09 AM

You can edit the file using an SQlite3 editor. You could try "DB Browser for SQLite" https://sqlitebrowser.org/

...

Thanks JPG; I've been editing the fremrtn.bbli, the one you converted from TheWord, using the DB Browser.

I'm a complete dunce with SQL; I was wondering:

 

When I go to the tab 'Execute SQL', I can run either one of the following scripts individually; however, I can't seem to run both at the same time. Is this a limitation, or is the syntax incorrect?


select * from Bible where Book > 39 and Book < 67 ;

UPDATE Bible
SET Scripture = replace(Scripture,"Eglise","église") ;
 

 



#15 JPG

JPG

    Jon.

  • Moderators
  • 1,665 posts
Offline

Posted 16 October 2021 - 09:37 AM

I am not at all proficient with SQlite. 

I would be very reluctant to advise on replace... it is not very selective as you might find out.

Also I am not sure about the Book >39 and Book <67, won't that get everything? because of the <67 part



#16 JPG

JPG

    Jon.

  • Moderators
  • 1,665 posts
Offline

Posted 16 October 2021 - 09:38 AM

You might get lucky with your replace but beware that it could replace things in the middle of words!!



#17 PeanutGallery

PeanutGallery

    e-Sword Fanatic

  • Members
  • PipPipPipPipPip
  • 148 posts
Offline

Posted 16 October 2021 - 09:56 AM

I am not at all proficient with SQlite. 

I would be very reluctant to advise on replace... it is not very selective as you might find out.

Also I am not sure about the Book >39 and Book <67, won't that get everything? because of the <67 part

Suppose to get everything between Matt and Rev, ie. everything in NT, excluding OT.
 



#18 JPG

JPG

    Jon.

  • Moderators
  • 1,665 posts
Offline

Posted 16 October 2021 - 04:19 PM

update Bible set Scripture = replace(Scripture,"Eglise","église") where Book > 39 and Book < 67;

I see it does get 40 to 66. Also what works is 

update Bible set Scripture = replace(Scripture,"Eglise","église") where Book between 40 and 66;

The tricky part is this replace will locate any partial word Eglisexxxx and replace the Eglise part with église so you end up with églisexxxx

This might not be an issue in this case, but could be quite a problem with other words... Just saying.



#19 PeanutGallery

PeanutGallery

    e-Sword Fanatic

  • Members
  • PipPipPipPipPip
  • 148 posts
Offline

Posted 16 October 2021 - 05:30 PM

update Bible set Scripture = replace(Scripture,"Eglise","église") where Book > 39 and Book < 67;

I see it does get 40 to 66. Also what works is 

update Bible set Scripture = replace(Scripture,"Eglise","église") where Book between 40 and 66;

The tricky part is this replace will locate any partial word Eglisexxxx and replace the Eglise part with église so you end up with églisexxxx

This might not be an issue in this case, but could be quite a problem with other words... Just saying.

 

Got it, works great. I have a dummy db where I try all scripts first to make sure they work properly. Also, I am familiar with tricky partial part; learnt the hard way. That's why I always test on the dummy db.

Thanks again

 



#20 JPG

JPG

    Jon.

  • Moderators
  • 1,665 posts
Offline

Posted 17 October 2021 - 10:17 AM

I just wanted to follow up.

I spent quite a lot of time trying to get a regex extension for DB Browser... thought I got one only to find it did not really implement PCRE regex at all.

Eventually looked at some other programs I had and found one that appears to have PCRE implemented. So something like this will work correctly just getting whole words for example.

https://www.yunqa.de...sqlitespy/index

UPDATE Bible set Scripture = REGEXP_REPLACE(Scripture,'\bthe\b','xxxthisxxx') where book BETWEEN 40 and 66;

One downside to this program is that it does not do the statement in temp so you have the option to roll back.

However you can run a select statement like this and see the results, but it should not have changed the database. Then if all looks ok you can run the update statement.

SELECT REGEXP_REPLACE(Scripture,'\bthe\b','xxxthisxxx') from bible where book BETWEEN 40 and 66;

I just noted a way of not modifying the database immediately, from their website

  • The SQLite database engine does not know the idea of “save on exit”. It writes all database changes directly to disk by default. One way to prevent this is to execute BEGIN TRANSACTION; before other SQL commands. When done, and before closing SQLiteSpy, execute COMMIT TRANSACTION; to save your changes.

     

    If you see any issues you simply run the statement

    rollback;
    

    It you are happy with the replacements run the statement

    COMMIT TRANSACTION;
    

Will keep looking for something better, but I would use this at a push.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users




Similar Topics



Latest Blogs