mirror of
https://github.com/BookStackApp/BookStack.git
synced 2026-02-07 11:19:38 +03:00
Inserting directly into database #1060
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @DannyGlover-VP on GitHub (Feb 25, 2019).
Hi,
Would someone be able to give me some assistance as to how the procedure works when inserting data directly into the database.
Some background, I've created a documentation script using PoSH that audits servers and then parses that information into HTML which is inserted directly into the bookstackdb. It will check to see if a page exists for the hostname, using the code below;
`
<# MYSQL Find IF Page exists for $Hostname #>
$Pages = Query-Database -DBUser $BookstackDBUser -DBPass $BookstackDBPass -DBName $BookstackDBDatabase -DBHost $BookstackDBHost -DBQuery "SELECT id,name FROM pages"
if ($Pages.name -notcontains $Hostname) {
$PageID = $($Pages.id | Measure-Object -Maximum).Maximum
$PageID++
$DBStatus = "New"
} else {
$PageID = $(Query-Database -DBUser $BookstackDBUser -DBPass $BookstackDBPass -DBName $BookstackDBDatabase -DBHost $BookstackDBHost -DBQuery "SELECT id FROM pages WHERE name = '$hostname'").id
$DBStatus = "Update"
}
`
The $DBStatus then actions the below at the end of the script;
`
If ($DBStatus -eq "Update") {
$MysqlQuery.CommandText = 'UPDATE bookstackdb.pages SET html = "'+$HTML+'" WHERE id = "'+$PageID+'";'
$MysqlQuery.ExecuteNonQuery() | Out-Null
} Elseif ($DBStatus -eq "New") {
$MysqlQuery.CommandText = 'INSERT INTO bookstackdb.pages (name, book_id, chapter_id, slug, html, text, priority, created_by, updated_by, markdown, revision_count) VALUES ("'+$Hostname+'","11","0","'+$Hostname+'","'+$HTML+'","VARCHAR","0","7","7","VARCHAR","0");'
$MysqlQuery.ExecuteNonQuery() | Out-Null
} Else {
<# Continue #>
}
`
The data is being put into the following location correctly, bookstackdb.pages. It is being given the correct book_id which we wish for the page to fall under, book_id: 11 the slug is set correctly to $Hostname
However, when going into the book in the web interface, there are no pages under thet book, and when trying to browse to the page directly, an error for not found is returned.
Are there any functions in the DB or relationships that need to be linked to have pages that are put directly into bookstackdb.pages to appear in the web interface?
Many thanks,
Danny.
@ssddanbrown commented on GitHub (Feb 26, 2019):
Hi @DannyGlover-VP,
A number of operations run in BookStack when content is saved, Therefore direct DB insertion is not officially supported.
That said, The issue you’re facing is probably due to the permission system being out of date after manual DB operations. You’ll also find that searching may be affected since the search indexes will need to be rebuilt.
Running both of the following commands, in your BookStack install directory, should rebuild both the search and permissions indexes:
Be aware both of these commands can be quite intensive if there’s a lot of content in your system.
@DannyGlover-VP commented on GitHub (Feb 26, 2019):
Hi,
Thank you for your response. This has indexed the results. It has left us with an error still but I will investigate and try resolving this myself before bothering you further.
I will post results and any fix tomorrow on this thread.
Thank you for your help thus far.
Danny
@ezzra commented on GitHub (Feb 27, 2019):
oh didn't know, that there is a command for this, I solved it by updating the table manually. Maybe I take a look to improve the command to use it for single pages.
@DannyGlover-VP commented on GitHub (Feb 27, 2019):
Hi,
@ssddanbrown @ezzra
Apologies for the delay on this. So as per my promise, I'll try give a overview of what I changed to get this working, hopefully it can help someone in the future.
In all instances where PoSH does an 'INSERT INTO' query, on anything that handles date information, it uses the following command to insert the current time in the accepted format;
+$(get-date -Format 'yyyy-MM-dd hh:mm:ss')+
Secondly, the table bookstackdb.page_revisions has to link to a bookstackdb.page - I used some logic to do this, logic similar to;
$PageID = .. "SELECT id FROM pages WHERE name = '$hostname'"
It will then store that PageID and use similar to the below (this is truncated code)
INSERT INTO bookstackdb.page_revisions .... VALUES ("'+$PageID+'")
Finally, when the above INSERT INTO values are complete, for both bookstackdb.pages & bookstackdb.page_revisions the SSH script will launch an SSH session, navigate to the bookstack directory and run the below commands;
php artisan bookstack:regenerate-search
php artisan bookstack:regenerate-permissions
This solved the issues I was experiencing with automated documentation.
Many thanks for your help @ssddanbrown
I hope others using PoSH find this useful, hopefully it's transferable to other languages too.
Danny.
@ssddanbrown commented on GitHub (Feb 28, 2019):
@DannyGlover-VP Awesome, Thanks for the update & glad to hear you got things working. An API is on the roadmap which, once implemented and released, would probably be ideal for these kind of scenarios since you won't have to interact with the DB directly and BookStack would run any extra operations as normal so keep an eye out for that.
@ezzra Would be fairly simple to modify the existing command for specific items but, for the core repo code, I'd like to keep these maintenance commands focused and simple, With the API in the future being the main method of these kind of operations.
@ezzra commented on GitHub (Feb 28, 2019):
didn't know that an API is on the roadmap, great!
@vy120119 commented on GitHub (Jul 16, 2020):
Hello Danny
I am trying to insert the data directly to html column and i can successfully did that.
I am facing one challenge,whenever i run the insert command , a revision page should be created with the revision number.
If you can help me with this.
vikas.80@gmail.com
Thanks
Vikas Yadav