[ANN] dumpTruck beta

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

[ANN] dumpTruck beta

Bil Corry-3
I created a script called dumpTruck that will dump the schema and data of all the Lasso internal databases upon Lasso starting up.  To use it, place it into the root LassoStartup folder and restart Lasso (no configuration of the script needed).

Some things to note:

(1) It only works when placed within the root LassoStartup folder.  It won't work if placed within a LassoSite startup folder.  This is because it auto-adds the backup path to the list of paths the admin can read/write.  This means only someone with access to the root LassoStartup folder can use this script, although the dump of each site appears within the site folder, so the results are available to anyone that can reach their site folder.  If you are on a shared ISP server, you'll have to ask to have it installed.  If there's interest, I might create a second solution to address this.  There is also no way currently to exclude one or more sites, although it's trivial to hardcode certain sites to skip.


(2) It will dump the sqlite tables for every site.  You see the locations of the "SQLiteDBs_Dump" folders like this (adjust for your OS):

        C:\Program Files\OmniPilot Software\Lasso Professional 8\SQLiteDBs_DUMP\
        C:\Program Files\OmniPilot Software\Lasso Professional 8\LassoSites\default-1\SQLiteDBs_DUMP\
        C:\Program Files\OmniPilot Software\Lasso Professional 8\LassoSites\mysite-2\SQLiteDBs_DUMP\
        etc.


(3) Will dump the tables on every restart.  Remove the script to prevent further dumps.


(4) Dumps are stored based on date, if two restarts happen on the same day, it'll overwrite the previous dump.  Note that it doesn't delete anything (only overwrites), so if you have a dump for the day, then remove a table, then do another dump, the original table dump is still there.


(5) The list of databases is hardcoded.  So if you have an additional database you've added, you'll need to manually add it to the list of databases to dump.  And since the Lasso Reference database is optional for each site, the script will create a folder for it, but if the database doesn't exist, it won't write any files to the folder.


(6) The files it creates are text files containing the SQL queries needed to delete and re-create the tables and data.  The SCHEMA.sql files contain the queries to build the table and indexes.  The DATA.sql files contain the queries to populate the table.  When you want to use these SQL query scripts, you have to manually cut/paste them into the databasebrowser.lassoapp.


(7) The script doesn't delete anything, which means over time you'll have a lot of dumps.  You currently have to manually delete each dump you no longer want.  In the future I plan to auto delete all but the last five or so.


(7) I've tested it and it appears to work properly (dumping and rebuilding).  But some additional testers would be great, especially if you have UTF-8 data.  And I wouldn't rely on this as your sole backup strategy just yet until its battle-hardened a bit more.


Hopefully all that detail hasn't make it sound more complicated than it really is.  It's just a matter of putting it into the root LassoStartup folder and restarting Lasso.

You can download dumpTruck from here:

<http://lasso.pro/downloads/dumpTruck.zip>


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

stevepiercy
Hi Bil,

Would you please elaborate on why dumping the Lasso internal SQLite databases on startup is a good thing?

What can one do with the dump files?  Is a restore possible?

Under what circumstances would one want to use this backup method?

--steve


On Sunday, March 16, 2008, [hidden email] (Bil Corry) pronounced:

>I created a script called dumpTruck that will dump the schema and data of all the
>Lasso internal databases upon Lasso starting up.  To use it, place it into the root
>LassoStartup folder and restart Lasso (no configuration of the script needed).
>
>Some things to note:
>
>(1) It only works when placed within the root LassoStartup folder.  It won't work if
>placed within a LassoSite startup folder.  This is because it auto-adds the backup
>path to the list of paths the admin can read/write.  This means only someone with
>access to the root LassoStartup folder can use this script, although the dump of
>each site appears within the site folder, so the results are available to anyone
>that can reach their site folder.  If you are on a shared ISP server, you'll have to
>ask to have it installed.  If there's interest, I might create a second solution to
>address this.  There is also no way currently to exclude one or more sites, although
>it's trivial to hardcode certain sites to skip.
>
>
>(2) It will dump the sqlite tables for every site.  You see the locations of the
>"SQLiteDBs_Dump" folders like this (adjust for your OS):
>
>   C:\Program Files\OmniPilot Software\Lasso Professional 8\SQLiteDBs_DUMP\
>   C:\Program Files\OmniPilot Software\Lasso Professional
>8\LassoSites\default-1\SQLiteDBs_DUMP\
>   C:\Program Files\OmniPilot Software\Lasso Professional
>8\LassoSites\mysite-2\SQLiteDBs_DUMP\
>   etc.
>
>
>(3) Will dump the tables on every restart.  Remove the script to prevent further
>dumps.
>
>
>(4) Dumps are stored based on date, if two restarts happen on the same day, it'll
>overwrite the previous dump.  Note that it doesn't delete anything (only
>overwrites), so if you have a dump for the day, then remove a table, then do another
>dump, the original table dump is still there.
>
>
>(5) The list of databases is hardcoded.  So if you have an additional database
>you've added, you'll need to manually add it to the list of databases to dump.  And
>since the Lasso Reference database is optional for each site, the script will create
>a folder for it, but if the database doesn't exist, it won't write any files to the
>folder.
>
>
>(6) The files it creates are text files containing the SQL queries needed to delete
>and re-create the tables and data.  The SCHEMA.sql files contain the queries to
>build the table and indexes.  The DATA.sql files contain the queries to populate the
>table.  When you want to use these SQL query scripts, you have to manually cut/paste
>them into the databasebrowser.lassoapp.
>
>
>(7) The script doesn't delete anything, which means over time you'll have a lot of
>dumps.  You currently have to manually delete each dump you no longer want.  In the
>future I plan to auto delete all but the last five or so.
>
>
>(7) I've tested it and it appears to work properly (dumping and rebuilding).  But
>some additional testers would be great, especially if you have UTF-8 data.  And I
>wouldn't rely on this as your sole backup strategy just yet until its
>battle-hardened a bit more.
>
>
>Hopefully all that detail hasn't make it sound more complicated than it really is.  
>It's just a matter of putting it into the root LassoStartup folder and restarting
>Lasso.
>
>You can download dumpTruck from here:
>
><http://lasso.pro/downloads/dumpTruck.zip>
>
>
>- Bil
>
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

Bil Corry-3
Steve Piercy - Web Site Builder wrote on 3/16/2008 1:19 PM:
> Would you please elaborate on why dumping the Lasso internal SQLite databases on startup is a good thing?

It's creating a plaintext backup of your Lasso settings.  If you are already backing up your Lasso settings using another method, then this isn't going to replace your other method.  The choice to do it during startup was just to make it easy for my Proof-of-Concept.  I see this eventually leading to where during startup it assigns the permissions, then backing up on a schedule (once per day/week/month etc).


> What can one do with the dump files?  Is a restore possible?

They're plaintext; they contain the sql queries needed to rebuild a table with its data.  Restore would have to be done via Lasso admin using the sql interface (you paste the queries into the sql window and run them).  And you can probably use an external sqlite application instead to run the queries against the Lasso tables, just make sure Lasso isn't running.

Eventually, I can see an interface that allows you to specify which table(s) to restore, and/or have it auto-restore any table that is corrupted.


> Under what circumstances would one want to use this backup method?

If you currently don't backup the SQLite tables at all, then this is better than re-entering all of your settings manually.  If you export your settings from Lasso admin, then I'm told there are some settings that don't currently get exported, whereas this will export everything.  If you backup your drive, but don't stop LassoService, then this is an extra precaution in case the sqlite files are not backed up properly because they were open and in use at the time of the backup.


Anyhow, this is the first step to what I hope becomes a more robust backup tool.  I've been sitting on it long enough and wanted to get it pushed out.  If nothing else, it makes it very easy to "browse" the internal SQLite tables to see the data they contain.


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

Steve Upton
In reply to this post by Bil Corry-3
At 8:50 AM -0500 3/16/08, Bil Corry wrote:
>I created a script called dumpTruck that will dump the schema and data of all the Lasso internal databases upon Lasso starting up.  To use it, place it into the root LassoStartup folder and restart Lasso (no configuration of the script needed).

This is great Bil,

One question.. do you know the range of Lasso version numbers that this script will work with?

I have a Lasso 7 server as well as 8...

thanks, looks useful.

a short story:

- My Macbook pro's 7200 RPM drive failed the other day. It started acting odd then just died. Apple replaced the drive - but won't give me the old one as they send them back to their manufacturers for their own warranty purposes.

So, then I reinstall Leopard and plug in my external HD that I'd been doing TimeMachine backups to. I had the external drive sitting 'naked' on the table with a USB adaptor connecting it to the MacBook.

I was worried that my kitten was going to jump up on the table and 'nose' the drive and it's circuit board so I put the Leopard software box on the drive.... then I thought that it might get hot so I picked it up. As I picked it up there was a bright flash and one of the components on the drive's circuit board went up in smoke... I have not been able to resurrect this drive since - even transferred a circuit board from another, identical drive.

Thankfully I also back up using Retrospect on a daily basis - the time machine thing was an experiment.

First, time machine should really enforce multi-drive rotating backups.

Second, you can NEVER have enough backups in enough forms. This data set includes all the development I've done in the past 10 years, all my family photos, etc.

Third... mucking around with naked drives is pretty risky. (duh!)

so, anything that gives me a new and different way of backing up regularly is a very GOOD thing.

thanks Bil.

Steve


--


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

Bil Corry-3
Steve Upton wrote on 3/19/2008 2:09 PM:
> One question.. do you know the range of Lasso version numbers that this script will work with?

Nothing jumps out at me that would prevent LP7 from using it, but I haven't used LP7 in a very long time.

And note, I received a message off-list about the dump file not working correctly, but I haven't had a chance to track down the issue.  So this is definitely a beta, work-in-progress.

If you find it doesn't work with LP7, but could work with a tweak or two, please send them my way off-list.


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

Johan Solve
On Wed, Mar 19, 2008 at 7:26 PM, Bil Corry <[hidden email]> wrote:
> Steve Upton wrote on 3/19/2008 2:09 PM:
>
> > One question.. do you know the range of Lasso version numbers that this script will work with?
>
>  Nothing jumps out at me that would prevent LP7 from using it, but I haven't used LP7 in a very long time.

Except LP7 uses MySQL internally instead of SQLite...



--
Mvh
Johan Sölve
____________________________________
Montania System AB
Halmstad Stockholm Malmö
http://www.montania.se

Johan Sölve
Mobil +46 709-51 55 70
[hidden email]

Kristinebergsvägen 17, S-302 41 Halmstad, Sweden
Telefon +46 35-136800 | Fax +46 35-136801

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: [ANN] dumpTruck beta

Bil Corry-3
Johan Solve wrote on 3/19/2008 5:24 PM:
> Except LP7 uses MySQL internally instead of SQLite...

Oh yeah, that sounds familiar :)

It won't work with LP7, it'll need its own tailored version.


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/