Main Page » Mailing List » December 2021

Copy data from MariaDB into BaseX

Monday, 20 December 2021 · 8 messages · last reply 23 Dec 2021

Ben Engbers20 Dec 2021 21:41 #

Hi, After completing my work on the R-client, I started working on a Prolog-client. Long ago I wrote an application in SWI-Prolog which operated on data from a MySQL-database. (In the meantime I changed from MySQL to MariaDb). My goal is to write a new version of that application but now based on data which is stored in Basex. In the basexgui, I created an empty database "MariaBases" The following code can be used to select data in MariaDb:

sql:init("org.mariadb.jdbc.Driver"),
let $con := sql:connect('jdbc:mariadb://localhost:3306/<DB-name>',
'<user>', '<password>')
return sql:execute($con, "select * from Mdw_Wens")

returns:

<sql:row xmlns:sql="http://basex.org/modules/sql">
   <sql:column name="ID">1</sql:column>
   <sql:column name="Medewerker_ID">5</sql:column>
   <sql:column name="Wens_ID">1</sql:column>
</sql:row>

Is it possible to change the query-statement in such a way that the results are added to MariaBases/<DB-schema>/<table-name>?

-- Ben Engbers

Christian Grün21 Dec 2021 10:18 #

Hi Ben,

Quoted text · Ben Engbers · 2 lines

> Is it possible to change the query-statement in such a way that the > results are added to MariaBases/<DB-schema>/<table-name>?

If I get you right, you would like to read your tabular MariaDB data via SQL select statements and add the contents to your new BaseX database, right? You might want to do something as follows:

sql:init('org.mariadb.jdbc.Driver'),

(: connect to the database, read table contents :)
let $db := '<DB-name>'
let $table := 'Mdw_Wens'
let $con := sql:connect(
  'jdbc:mariadb://localhost:3306/' || $db,
  '<user>', '<password>'
)
let $rows := sql:execute($con, 'select * from ' || $table)
(: create new document with transformed table contents :)
let $doc := element { $table } {
  for $row in $rows
  return element row {
    for $col in $row/sql:column
    return element { $col/@name } { $col/data() }
  }
}
(: add new document to your BaseX database :)
return db:add($db, $doc, $table || '.xml')

Could you give us little examples for <DB-name>, <DB-schema> and <table-name> ? Best, Christian

Christian Grün21 Dec 2021 13:20 #

Thanks. Does the query do what you are looking for?

Quoted text · PM · 32 lines

On Tue, Dec 21, 2021 at 12:47 PM <benengbers at dds.nl> wrote: > > Christian Grün schreef op 21-12-2021 10:18: > > Hi Ben, > > > return db:add($db, $doc, $table || '.xml') > > > > Could you give us little examples for <DB-name>, <DB-schema> and > > <table-name> ? > > > > Best, > > Christian > > To the best of my knowledge in MySQL and/or MariaDB DB-name and > DB-schema are identical? The schema-name I use is 'Innovate'. > Table-names are > +--------------------+ > | Tables_in_Innovate | > +--------------------+ > | Dienst | > | Mdw_Probleem | > | Mdw_Wens | > | Medewerker | > | Medewerker_dienst | > | Probleem | > | Wens | > +--------------------+ > > Ben > PS.I hope you'll see this reply. Since a few days all mail from > basex-talk is refused by Thunderbird. At least I don't see them anymore > ....

Ben Engbers21 Dec 2021 15:10 #

At least this is a very good start! I'll see if I can manage to transfer all the tables in on nested command. But first I'll have to refresh my XPath or XQuery knowledge. I'll let you know about the results. Have a nice holiday, Ben

Quoted text · Christian Grün · 2 lines

Op 21-12-2021 om 13:20 schreef Christian Grün: > Thanks. Does the query do what you are looking for?

Ben Engbersin reply to Christian Grün, 21 Dec 2021, 13:2022 Dec 2021 16:09 #

Hi Christian, For the time being, I ended up with this:

sql:init("org.mariadb.jdbc.Driver"),

let $MariaBase := 'Relational'
let $db        := 'Innovate'
let $user      := '<user>'
let $pass      := '<pass'

let $con := sql:connect('jdbc:mariadb://localhost:3306/' || $db, $user,
$pass)
let $tables := sql:execute($con, 'show tables')/sql:column/text()

let $doc := element { $db } {
   for $table in $tables
     return element { $table } {
       let $rows := sql:execute($con, 'select * from ' || $table)
       for $row in $rows
       return element row {
         for $col in $row/sql:column
         return element { $col/@name } { $col/data() }
       }
     }
}

(: return db:add($MariaBase, $doc, $db). :)
return $doc gives

------------ return $doc gives:

<Innovate>
   <Dienst>
     <row>
       <Dienst_ID>1</Dienst_ID>
       <Dienst>CIO Office</Dienst>
     </row>

But return db:add($MariaBase, $doc, $db) results in my database in Relational -> Innovate/1 -> Innovate/1 -> Dienst/1 -> row/n (1 and n indicate parity) I expected that return db:add($MariaBase, $doc) would add $doc at the top-level, resulting in Relational -> Innovate/1 -> Dienst/1 -> row/n but this results in an error (path is missing) According to the documentation ommitting the third parameter in db:add should be allowed, or did I misinterpret something? Cheers, Ben

Quoted text · Christian Grün · 35 lines

Op 21-12-2021 om 13:20 schreef Christian Grün: > Thanks. Does the query do what you are looking for? > > On Tue, Dec 21, 2021 at 12:47 PM <benengbers at dds.nl> wrote: >> >> Christian Grün schreef op 21-12-2021 10:18: >>> Hi Ben, >> >>> return db:add($db, $doc, $table || '.xml') >>> >>> Could you give us little examples for <DB-name>, <DB-schema> and >>> <table-name> ? >>> >>> Best, >>> Christian >> >> To the best of my knowledge in MySQL and/or MariaDB DB-name and >> DB-schema are identical? The schema-name I use is 'Innovate'. >> Table-names are >> +--------------------+ >> | Tables_in_Innovate | >> +--------------------+ >> | Dienst | >> | Mdw_Probleem | >> | Mdw_Wens | >> | Medewerker | >> | Medewerker_dienst | >> | Probleem | >> | Wens | >> +--------------------+ >> >> Ben >> PS.I hope you'll see this reply. Since a few days all mail from >> basex-talk is refused by Thunderbird. At least I don't see them anymore >> ....

Christian Grün22 Dec 2021 20:53 #

Quoted text · Ben Engbers · 3 lines

> return db:add($MariaBase, $doc, $db) > results in my database in Relational -> Innovate/1 -> Innovate/1 -> > Dienst/1 -> row/n (1 and n indicate parity)

Sorry, I can’t follow (e.g., I don’t understand what /1 is supposed to indicate, what you mean by "indicate parity", …). With your query, you seem to add a single document into your database that contains the contents of all tables. That’s fine in general; but is it what you are trying to achieve, or would it probably be better to represent a single table as document?

Quoted text · Ben Engbers · 2 lines

> According to the documentation ommitting the third parameter in db:add > should be allowed, or did I misinterpret something?

The third argument can only be omitted if the second argument points to a file or directory [1], as the name of the addressed file will then be used as database path. [1] https://docs.basex.org/wiki/Database_Module#db:create

Ben Engbers23 Dec 2021 12:38 #

Sorry, I should have been more precise in my question (and it would have been better not to talk about parity ;-() I have 4 schemas/databases in MariaDB which I want to copy to BaseX. The first one (schema = 'Innovate') uses 4 tables. The first table (='Dienst') has 2 attributes) and 2 rows. In total there are 6 tables in this schema. I try to copy this first schema to BaseX - "Relational" which is created as an empty database (create db Relational) The result from :

let $doc := element { $db } {
   for $table in $tables
     return element { $table } {
       let $rows := sql:execute($con, 'select * from ' || $table)
       for $row in $rows
       return element row {
         for $col in $row/sql:column
         return element { $col/@name } { $col/data() }
       }
     }
}
return $doc

is:

<Innovate>
   <Dienst>
     <row>
       <Dienst_ID>1</Dienst_ID>
       <Dienst>CIO Office</Dienst>
     </row>
     <row>
       <Dienst_ID>2</Dienst_ID>
       <Dienst>Dictu</Dienst>
     </row>
   </Dienst>
   .. other tables ..
</Innovate>

But the result from:

return db:add($MariaBase, $doc, $db)
is:
BaseX database "Relational"
<Innovate>
   <Innovate>
     <Dienst>
       <row>
         ..
       </row>
   .. other tables ..
   <Innovate>
</Innovate>

At the end It was my intention to have created: BaseX database "Relational" <Innovate> <Dienst> .. other schemas/tables .. </Innovate> <3 other schemas>

Quoted text · Christian Grün · 3 lines

Op 22-12-2021 om 20:53 schreef Christian Grün: > With your query, you seem to add a single document into your database > that contains the contents of all tables.

Correct. One document in database "Relational" should represent a complete schema in MariaDB.

Quoted text · Christian Grün · 3 lines

That’s fine in general; but > is it what you are trying to achieve, or would it probably be better > to represent a single table as document?

What would be the advantage of representing single tables as a document? Aren't both approaches equivalent? Ben

Christian Grün23 Dec 2021 13:15 #

Hi Ben,

Quoted text · Ben Engbers · 7 lines

> But the result from: > return db:add($MariaBase, $doc, $db) > is: > BaseX database "Relational" > <Innovate> > <Innovate> > <Dienst>

That’s strange indeed. I tried to reproduce this by… 1. running db:create('Relational'), 2. running the attached script and 3. running db:open('Relational'), but the last query gives me the expected XML structure (without nested Innovate elements). Could you provide me with a self-contained example?

Quoted text · Ben Engbers · 2 lines

> What would be the advantage of representing single tables as a document? > Aren't both approaches equivalent?

They are both feasible. It always depends on your use case which one is more suitable. Best, Christian

let $MariaBase := 'Relational'
let $db := 'innovate.xml' (: arbitrary :)
let $doc := <Innovate>
   <Dienst>
     <row>
       <Dienst_ID>1</Dienst_ID>
       <Dienst>CIO Office</Dienst>
     </row>
     <row>
       <Dienst_ID>2</Dienst_ID>
       <Dienst>Dictu</Dienst>
     </row>
   </Dienst>
</Innovate>
return db:add($MariaBase, $doc, $db)

Written for BaseX 9.6. BaseX may have changed since: please check the current documentation.

⚡Generated with XQuery

`J`	Select the next message
`K`	Select the previous message
`N`	Next thread
`P`	Previous thread
`U`	Go up one level
`/`	Jump to the search field