Re: [basex-talk] Copy data from MariaDB into BaseX
Thanks. Does the query do what you are looking for? On Tue, Dec 21, 2021 at 12:47 PM <benengbers@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 ....
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 Op 21-12-2021 om 13:20 schreef Christian Grün:
Thanks. Does the query do what you are looking for?
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 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@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 ....
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?
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
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> 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. 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
Hi Ben,
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?
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)
participants (2)
-
Ben Engbers -
Christian Grün