SQLite vs. BSDDB

classic Classic list List threaded Threaded
30 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SQLite vs. BSDDB

Patrick Gerlier
Hi all,

I started conversion of my data from BSDDB to SQLite in the hope, among
others, to be able to run SQL queries and scripts on the DB. For
instance, I'd like to find all persons with a given nickname and
incorporate it into a multiple name element.

I used Sqliteman under Linux to have a look at the schema and table.

To my susrprise, there are a lot of "blob"s hiding the intimate
structure of data. I can't run queries against blobs.

I'm presently using 5.0.2.

My question is: is this a temporary state of development? I understand
that given the backend can still be chosen between BSDDB and SQLite, the
methods of objects are implemented independently from the backend and
that the present architecture is inherited from BSDDB which is less
elaborate then SQLite. However heavy use of blobs prevents building
complex queries, such as those accessing "subrecords".

Are there plans to change the schema for a purer SQL form?

Regards,
Patrick



--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson
On 8/30/19 8:57 AM, Patrick Gerlier wrote:

> Hi all,
>
> I started conversion of my data from BSDDB to SQLite in the hope, among
> others, to be able to run SQL queries and scripts on the DB. For
> instance, I'd like to find all persons with a given nickname and
> incorporate it into a multiple name element.
>
> I used Sqliteman under Linux to have a look at the schema and table.
>
> To my susrprise, there are a lot of "blob"s hiding the intimate
> structure of data. I can't run queries against blobs.
>
> I'm presently using 5.0.2.
>
> My question is: is this a temporary state of development? I understand
> that given the backend can still be chosen between BSDDB and SQLite, the
> methods of objects are implemented independently from the backend and
> that the present architecture is inherited from BSDDB which is less
> elaborate then SQLite. However heavy use of blobs prevents building
> complex queries, such as those accessing "subrecords".

Export the tree to SQLite format, and then you can query it.  No updates,
though.


--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Patrick Gerlier

Le 30/08/2019 à 18:51, Ron Johnson a écrit :

> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No
> updates, though.
I've done it, but the table structure shows too many "blob"s. Blobs are
"opaque": they are binary blocks whose structure is hidden to SQL. I
think it is a direct "copy" of some BSDDB key-value pairs. Unfortunately
many interesting "objects", like multiple names, are stored as blobs
which defeats any attempt to peep into them.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson
In reply to this post by Ron Johnson
On 8/30/19 11:51 AM, Ron Johnson wrote:

> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs


--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson
In reply to this post by Patrick Gerlier
On 8/30/19 11:58 AM, Patrick Gerlier wrote:

Le 30/08/2019 à 18:51, Ron Johnson a écrit :
On 8/30/19 8:57 AM, Patrick Gerlier wrote:
Hi all,

I started conversion of my data from BSDDB to SQLite in the hope, among
others, to be able to run SQL queries and scripts on the DB. For
instance, I'd like to find all persons with a given nickname and
incorporate it into a multiple name element.

I used Sqliteman under Linux to have a look at the schema and table.

To my susrprise, there are a lot of "blob"s hiding the intimate
structure of data. I can't run queries against blobs.

I'm presently using 5.0.2.

My question is: is this a temporary state of development? I understand
that given the backend can still be chosen between BSDDB and SQLite, the
methods of objects are implemented independently from the backend and
that the present architecture is inherited from BSDDB which is less
elaborate then SQLite. However heavy use of blobs prevents building
complex queries, such as those accessing "subrecords".

Export the tree to SQLite format, and then you can query it.  No
updates, though.
I've done it, but the table structure shows too many "blob"s. Blobs are
"opaque": they are binary blocks whose structure is hidden to SQL. I
think it is a direct "copy" of some BSDDB key-value pairs. Unfortunately
many interesting "objects", like multiple names, are stored as blobs
which defeats any attempt to peep into them.

You're confusing the actual Gramps database with an exported SQLite database. They're definitely different.

A raw Gramps tree database:
ron@haggis:~$ sqlite3 /home/ron/.gramps/grampsdb/5c1d6859/sqlite.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
citation      gender_stats  name_group    place         source     
event         media         note          reference     tag        
family        metadata      person        repository 
sqlite>
sqlite> .schema person
CREATE TABLE person (handle VARCHAR(50) PRIMARY KEY NOT NULL, given_name TEXT, surname TEXT, blob_data BLOB, death_ref_index INTEGER, birth_ref_index INTEGER, gender INTEGER, private INTEGER, gramps_id TEXT, change INTEGER);
CREATE INDEX person_gramps_id ON person(gramps_id);
CREATE INDEX person_surname ON person(surname);
CREATE INDEX person_given_name ON person(given_name);

An exported tree database:

ron@haggis:~$ sqlite3 Johnson-Gilbert.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
address         event           markup          person_ref      source       
attribute       event_ref       media           place           surname      
child_ref       family          media_ref       place_name      tag          
citation        lds             name            place_ref       url          
datamap         link            note            repository   
date            location        person          repository_ref
sqlite>
sqlite>
sqlite> .schema person
CREATE TABLE person (
                  handle CHARACTER(25) PRIMARY KEY,
                  gid CHARACTER(25),
                  gender INTEGER,
                  death_ref_handle TEXT,
                  birth_ref_handle TEXT,
                  change INTEGER,
                  private BOOLEAN);
sqlite>
sqlite>
sqlite> .schema person_ref
CREATE TABLE person_ref (
                 handle CHARACTER(25) PRIMARY KEY,
                 description TEXT,
                 private BOOLEAN);


--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Patrick Gerlier
I've done it, but the table structure shows too many "blob"s. Blobs are
"opaque": they are binary blocks whose structure is hidden to SQL. I
think it is a direct "copy" of some BSDDB key-value pairs. Unfortunately
many interesting "objects", like multiple names, are stored as blobs
which defeats any attempt to peep into them.

You're confusing the actual Gramps database with an exported SQLite database. They're definitely different.
Thanks for the clarification. I'd like to work directly on the raw tree DB so that my updates are then reflected in the Gramps application. (I'm careful enough to experiment on a copy before jumping on the wild beast)

A raw Gramps tree database:
ron@haggis:~$ sqlite3 /home/ron/.gramps/grampsdb/5c1d6859/sqlite.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
citation      gender_stats  name_group    place         source     
event         media         note          reference     tag        
family        metadata      person        repository 
sqlite>
sqlite> .schema person
CREATE TABLE person (handle VARCHAR(50) PRIMARY KEY NOT NULL, given_name TEXT, surname TEXT, blob_data BLOB, death_ref_index INTEGER, birth_ref_index INTEGER, gender INTEGER, private INTEGER, gramps_id TEXT, change INTEGER);
CREATE INDEX person_gramps_id ON person(gramps_id);
CREATE INDEX person_surname ON person(surname);
CREATE INDEX person_given_name ON person(given_name);

An exported tree database:

ron@haggis:~$ sqlite3 Johnson-Gilbert.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
address         event           markup          person_ref      source       
attribute       event_ref       media           place           surname      
child_ref       family          media_ref       place_name      tag          
citation        lds             name            place_ref       url          
datamap         link            note            repository   
date            location        person          repository_ref
sqlite>
sqlite>
sqlite> .schema person
CREATE TABLE person (
                  handle CHARACTER(25) PRIMARY KEY,
                  gid CHARACTER(25),
                  gender INTEGER,
                  death_ref_handle TEXT,
                  birth_ref_handle TEXT,
                  change INTEGER,
                  private BOOLEAN);
sqlite>
sqlite>
sqlite> .schema person_ref
CREATE TABLE person_ref (
                 handle CHARACTER(25) PRIMARY KEY,
                 description TEXT,
                 private BOOLEAN);


--
Angular momentum makes the world go 'round.




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Nick Hall
In reply to this post by Patrick Gerlier
On 30/08/2019 14:57, Patrick Gerlier wrote:
> My question is: is this a temporary state of development? I understand
> that given the backend can still be chosen between BSDDB and SQLite, the
> methods of objects are implemented independently from the backend and
> that the present architecture is inherited from BSDDB which is less
> elaborate then SQLite. However heavy use of blobs prevents building
> complex queries, such as those accessing "subrecords".
>
> Are there plans to change the schema for a purer SQL form?

There are no plans to change to a relational schema.  We use the
database as an object store for objects that are hierarchical rather
than relational in nature.

However, we are considering storing objects as JSON rather than pickled
blobs.  See:

9392: Do we need to pickle DB-API blobs?

https://gramps-project.org/bugs/view.php?id=9392

Paul Culley and I have started to investigate this in pull request
#800:   "Store objects as JSON rather than pickled blobs"

https://github.com/gramps-project/gramps/pull/800

One advantage of this would be that the data would no longer be opaque
to external applications.


Nick.




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Patrice Legoux
In reply to this post by Ron Johnson
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

Le ven. 30 août 2019 à 19:00, Ron Johnson <[hidden email]> a écrit :
On 8/30/19 11:51 AM, Ron Johnson wrote:
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs


--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson

Since it's failing above the SQLite code, it would be beneficial to test other export methods.

On 8/31/19 5:45 AM, Patrice Legoux wrote:
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

Le ven. 30 août 2019 à 19:00, Ron Johnson <[hidden email]> a écrit :
On 8/30/19 11:51 AM, Ron Johnson wrote:
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs



--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Patrice Legoux

Exports status:

XML: OK
JSON: KO
Prolog: KO
SQL: KO
GED2: OK
GEDCOM: OK
Geneweb: OK
CSV: OK
Web family tree: OK
VCS: Impossible to create VCS file
VCF: OK

KO exports error log:

JSON:
167782238: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 53, in exportData
    write_line(fp, "note:", note.handle, note)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 135, in write_line
    fp.write("%s %s %s\n" % (heading, handle, obj.serialize()))
  File "AIO/encodings/cp1252.py", line 19, in encode
UnicodeEncodeError: 'charmap' codec can't encode character '\u2212' in position 177: character maps to <undefined>

Prolog:
167901441: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportProlog\ExportProlog.py", line 83, in exportData
    fp.write("is_alive(%s, '%s').\n" % (gid, probably_alive(person, database)))
NameError: name 'database' is not defined



Garanti sans virus. www.avast.com

Le sam. 31 août 2019 à 14:17, Ron Johnson <[hidden email]> a écrit :

Since it's failing above the SQLite code, it would be beneficial to test other export methods.

On 8/31/19 5:45 AM, Patrice Legoux wrote:
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

Le ven. 30 août 2019 à 19:00, Ron Johnson <[hidden email]> a écrit :
On 8/30/19 11:51 AM, Ron Johnson wrote:
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs



--
Angular momentum makes the world go 'round.
--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson

I think it's time for you to file a bug report... and specify exactly what KO means.  Knot Ok???

On 9/1/19 10:44 AM, Patrice Legoux wrote:

Exports status:

XML: OK
JSON: KO
Prolog: KO
SQL: KO
GED2: OK
GEDCOM: OK
Geneweb: OK
CSV: OK
Web family tree: OK
VCS: Impossible to create VCS file
VCF: OK

KO exports error log:

JSON:
167782238: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 53, in exportData
    write_line(fp, "note:", note.handle, note)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 135, in write_line
    fp.write("%s %s %s\n" % (heading, handle, obj.serialize()))
  File "AIO/encodings/cp1252.py", line 19, in encode
UnicodeEncodeError: 'charmap' codec can't encode character '\u2212' in position 177: character maps to <undefined>

Prolog:
167901441: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportProlog\ExportProlog.py", line 83, in exportData
    fp.write("is_alive(%s, '%s').\n" % (gid, probably_alive(person, database)))
NameError: name 'database' is not defined



Garanti sans virus. www.avast.com

Le sam. 31 août 2019 à 14:17, Ron Johnson <[hidden email]> a écrit :

Since it's failing above the SQLite code, it would be beneficial to test other export methods.

On 8/31/19 5:45 AM, Patrice Legoux wrote:
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

Le ven. 30 août 2019 à 19:00, Ron Johnson <[hidden email]> a écrit :
On 8/30/19 11:51 AM, Ron Johnson wrote:
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs



--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Patrice Legoux
Report done. KO is used in french when testing programs to say it's not ok, like knockout in boxing

Le dim. 1 sept. 2019 à 17:58, Ron Johnson <[hidden email]> a écrit :

I think it's time for you to file a bug report... and specify exactly what KO means.  Knot Ok???

On 9/1/19 10:44 AM, Patrice Legoux wrote:

Exports status:

XML: OK
JSON: KO
Prolog: KO
SQL: KO
GED2: OK
GEDCOM: OK
Geneweb: OK
CSV: OK
Web family tree: OK
VCS: Impossible to create VCS file
VCF: OK

KO exports error log:

JSON:
167782238: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 53, in exportData
    write_line(fp, "note:", note.handle, note)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 135, in write_line
    fp.write("%s %s %s\n" % (heading, handle, obj.serialize()))
  File "AIO/encodings/cp1252.py", line 19, in encode
UnicodeEncodeError: 'charmap' codec can't encode character '\u2212' in position 177: character maps to <undefined>

Prolog:
167901441: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportProlog\ExportProlog.py", line 83, in exportData
    fp.write("is_alive(%s, '%s').\n" % (gid, probably_alive(person, database)))
NameError: name 'database' is not defined



Garanti sans virus. www.avast.com

Le sam. 31 août 2019 à 14:17, Ron Johnson <[hidden email]> a écrit :

Since it's failing above the SQLite code, it would be beneficial to test other export methods.

On 8/31/19 5:45 AM, Patrice Legoux wrote:
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

Le ven. 30 août 2019 à 19:00, Ron Johnson <[hidden email]> a écrit :
On 8/30/19 11:51 AM, Ron Johnson wrote:
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No updates,
> though.

https://www.gramps-project.org/wiki/index.php/SQLite_Export_Import#Using_export_with_external_programs



--
Angular momentum makes the world go 'round.
--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

enno
In reply to this post by Patrice Legoux

Hello Patrice,

JSON:
167782238: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 53, in exportData
    write_line(fp, "note:", note.handle, note)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportRaw\ExportRaw.py", line 135, in write_line
    fp.write("%s %s %s\n" % (heading, handle, obj.serialize()))
  File "AIO/encodings/cp1252.py", line 19, in encode
UnicodeEncodeError: 'charmap' codec can't encode character '\u2212' in position 177: character maps to <undefined>
I don't get this here, but it suggests that you have a node text that Gramps can't export. This looks like a bug to me, because I assume that the JSON export should allow all characters supported by Gramps, and not try to squeeze those in a Western European character set.

Prolog:
167901441: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\ExportProlog\ExportProlog.py", line 83, in exportData
    fp.write("is_alive(%s, '%s').\n" % (gid, probably_alive(person, database)))
NameError: name 'database' is not defined
I get the same here, and I don't remember needing to enter a database, so to me this looks like a bug too.

On 8/31/19 5:45 AM, Patrice Legoux wrote:
Not lucky.

On Windows 10, Gramps 5.1:

63851492: ERROR: _exportassistant.py: line 597: Erreur lors de l'exportation de votre arbre familial
Traceback (most recent call last):
  File "C:\Program Files\GrampsAIO64-5.1.0\gramps\gui\plug\export\_exportassistant.py", line 593, in save
    self.option_box_instance)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 961, in exportData
    export_person(db, person.serialize())
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 645, in export_person
    export_person_ref_list(db, "person", handle, person_ref_list)
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 470, in export_person_ref_list
    private
  File "C:\Users\1\AppData\Roaming\gramps\gramps51\plugins\Sqlite\ExportSql.py", line 390, in query
    self.cursor.execute(q, args)
sqlite3.IntegrityError: UNIQUE constraint failed: person_ref.handle

H'm, I don't get that here, and there may be a corruption in your tree. Can you run check and repair to verify? I don't know how it's named in French, but I hope that you can find it.

Regards,

Enno




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Nick Hall
On 03/09/2019 20:34, Enno Borgsteede wrote:
> This looks like a bug to me, because I assume that the JSON export
> should allow all characters supported by Gramps, and not try to
> squeeze those in a Western European character set.

The JSON standard specifies unicode encoding.

Nick.




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

prculley
The ExportRaw error is easy, the addon is opening the file for write with the default system encoding, rather that with the encoding='utf8' parameter.  I've seen this a lot as the authors familiar with Linux assume all systems have a default utf8 encoding, which is NOT true for Windows.

The second one (ExportProlog) is definitely a bug, the https://github.com/gramps-project/addons-source/commit/3620a146ad83664ac89211fa8b32446cbcedd2d9#diff-de4e9bb380d3034c71b3cc91a26f85c7 change 'database' to 'db' but missed the one on line 83.

Someone who understands SQL better will have to figure out the ExportSql issue...

Paul C.

On Tue, Sep 3, 2019 at 2:47 PM Nick Hall <[hidden email]> wrote:
On 03/09/2019 20:34, Enno Borgsteede wrote:
> This looks like a bug to me, because I assume that the JSON export
> should allow all characters supported by Gramps, and not try to
> squeeze those in a Western European character set.

The JSON standard specifies unicode encoding.

Nick.




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Nick Hall
On 03/09/2019 22:30, Paul Culley wrote:
> The ExportRaw error is easy, the addon is opening the file for write
> with the default system encoding, rather that with the encoding='utf8'
> parameter.  I've seen this a lot as the authors familiar with Linux
> assume all systems have a default utf8 encoding, which is NOT true for
> Windows.
>
We shouldn't assume that the locale preferred encoding is utf-8 on
Linux.  If a standard such as JSON requires an encoding then we should
specify it explicitly.

Nick.




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

enno
In reply to this post by prculley
Op 03-09-19 om 23:30 schreef Paul Culley:
Someone who understands SQL better will have to figure out the ExportSql issue...

I hope that a check and repair, or rebuild index, will weed out the duplicate ref. If it does not, we may have one constraint too many in the export database.

Regards,

Enno




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

prculley
In reply to this post by Patrick Gerlier
I have updated the Sqlite export and import addons for Gramps versions 5.1.x so that they work correctly, at least for the test cases I tried.
The method of storing SQL data in the addon export is a lot different than the 'blobs' used by the usual Gramps Sqlite db.  It uses many more tables with lots more cross-linking, not hiding any data.  So it may be more useful for experimental purposes.

I caution that if you edit the exported db elsewhere, and then import back to Gramps, it has to follow the rules or it will fail.

Paul C.

On Fri, Aug 30, 2019 at 12:01 PM Patrick Gerlier <[hidden email]> wrote:

Le 30/08/2019 à 18:51, Ron Johnson a écrit :
> On 8/30/19 8:57 AM, Patrick Gerlier wrote:
>> Hi all,
>>
>> I started conversion of my data from BSDDB to SQLite in the hope, among
>> others, to be able to run SQL queries and scripts on the DB. For
>> instance, I'd like to find all persons with a given nickname and
>> incorporate it into a multiple name element.
>>
>> I used Sqliteman under Linux to have a look at the schema and table.
>>
>> To my susrprise, there are a lot of "blob"s hiding the intimate
>> structure of data. I can't run queries against blobs.
>>
>> I'm presently using 5.0.2.
>>
>> My question is: is this a temporary state of development? I understand
>> that given the backend can still be chosen between BSDDB and SQLite, the
>> methods of objects are implemented independently from the backend and
>> that the present architecture is inherited from BSDDB which is less
>> elaborate then SQLite. However heavy use of blobs prevents building
>> complex queries, such as those accessing "subrecords".
>
> Export the tree to SQLite format, and then you can query it.  No
> updates, though.
I've done it, but the table structure shows too many "blob"s. Blobs are
"opaque": they are binary blocks whose structure is hidden to SQL. I
think it is a direct "copy" of some BSDDB key-value pairs. Unfortunately
many interesting "objects", like multiple names, are stored as blobs
which defeats any attempt to peep into them.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Peter Merchant
In reply to this post by enno
Do I need to take any special steps  to change from BSDDB to SQLite?
I am using Kubuntu Linux 18.04 and gramps 5.0.1. I have backed up all my databases and downloaded the new Deb for 5.1.1

Do I need to delete the old version of gramps and data before installing the new in order to do this change, or is it a case of install the new, delete all the old databases, and change the database format to SQLite before importing all the backups?

I had a quick look at the wiki, but couldn't see any advice about this.

Thanks for help.
Peter



On 03/09/2019 20:34, Enno Borgsteede wrote:




--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite vs. BSDDB

Ron Johnson
On 9/18/19 2:26 AM, Peter Merchant wrote:
Do I need to take any special steps  to change from BSDDB to SQLite?
I am using Kubuntu Linux 18.04 and gramps 5.0.1. I have backed up all my databases and downloaded the new Deb for 5.1.1

Do I need to delete the old version of gramps 

I do.  "sudo apt-get purge gramps". 

and data 

No.

before installing the new in order to do this change, or is it a case of install the new, delete all the old databases, and change the database format to SQLite before importing all the backups?

A "Convert" button was added to the Family Trees window in 5.0.  IOW, you can convert now to SQLite with a click of a button.


--
Angular momentum makes the world go 'round.


--
Gramps-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gramps-users
https://gramps-project.org
12