Thanks. Does the query do what you are looking for?
On Tue, Dec 21, 2021 at 12:47 PM benengbers@dds.nl wrote:
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:
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.
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,
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)
basex-talk@mailman.uni-konstanz.de
-
Ben Engbers -
Christian Grün