Local DB Mirror

[fREW Schmidt]

Hey guys,

I was thinking it would be cool to do more local development, and the only thing tethering me to the VPN is our database server (SQL Server,) fortunately for this plan the only really special thing I do in the database is FK constraints and maybe a few non-standard defaults (now() etc.)  But I think for the most part that stuff will be fine to ignore for non-local development.  Anyway, does anyone have any idea how I could use DBIC to mirror a server into an sqlite database?  Thanks!

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ashley-2]

On Jul 20, 2009, at 7:07 PM, fREW Schmidt wrote:
> I was thinking it would be cool to do more local development, and  
> the only thing tethering me to the VPN is our database server (SQL  
> Server,) fortunately for this plan the only really special thing I  
> do in the database is FK constraints and maybe a few non-standard  
> defaults (now() etc.)  But I think for the most part that stuff  
> will be fine to ignore for non-local development.  Anyway, does  
> anyone have any idea how I could use DBIC to mirror a server into  
> an sqlite database?  Thanks!

You should be able to mirror the data into SQLite pretty easily with  
SQL tools. SQLite doesn't care about column contents so it would  
happily load anything, I think. WRT the DBIC compatibility, I was  
playing with code for a shim for this between MySQL last week. I  
didn't have much time to do anything fancy but I did have some  
success; don't have it in front of me though.

The thrust of it is you can define functions in SQLite via the  
wonderful DBD::SQLite engine so you just go through and define the  
compatibility level functions like "NOW()" which was the one I did to  
see if it would work. I was toying with releasing it to the CPAN as a  
skeleton with the hopes of getting contributions from everyone to  
build it out so that you could use schema->deploy( SQLite ) with code  
that does functions etc from PostgreSQL or MySQL or...

http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm

Look at these:
  # $dbh->sqlite_create_function( $name, $argc, $code_ref )
  # $dbh->sqlite_create_collation( $name, $code_ref )
  # $dbh->sqlite_create_aggregate( $name, $argc, $pkg )

-Ashley

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[fREW Schmidt]

Very interesting!  How did you generate the table structure for the SQLite database?

On Mon, Jul 20, 2009 at 9:26 PM, Ashley <[hidden email]> wrote:

On Jul 20, 2009, at 7:07 PM, fREW Schmidt wrote:

I was thinking it would be cool to do more local development, and the only thing tethering me to the VPN is our database server (SQL Server,) fortunately for this plan the only really special thing I do in the database is FK constraints and maybe a few non-standard defaults (now() etc.)  But I think for the most part that stuff will be fine to ignore for non-local development.  Anyway, does anyone have any idea how I could use DBIC to mirror a server into an sqlite database?  Thanks!

You should be able to mirror the data into SQLite pretty easily with SQL tools. SQLite doesn't care about column contents so it would happily load anything, I think. WRT the DBIC compatibility, I was playing with code for a shim for this between MySQL last week. I didn't have much time to do anything fancy but I did have some success; don't have it in front of me though.

The thrust of it is you can define functions in SQLite via the wonderful DBD::SQLite engine so you just go through and define the compatibility level functions like "NOW()" which was the one I did to see if it would work. I was toying with releasing it to the CPAN as a skeleton with the hopes of getting contributions from everyone to build it out so that you could use schema->deploy( SQLite ) with code that does functions etc from PostgreSQL or MySQL or...

http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm

Look at these:
 # $dbh->sqlite_create_function( $name, $argc, $code_ref )
 # $dbh->sqlite_create_collation( $name, $code_ref )
 # $dbh->sqlite_create_aggregate( $name, $argc, $pkg )

-Ashley

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ashley-2]

On Jul 20, 2009, at 7:52 PM, fREW Schmidt wrote:
> Very interesting!  How did you generate the table structure for the  
> SQLite database?

Check out http://search.cpan.org/perldoc?
DBIx::Class::Manual::Cookbook and any mention of the method "deploy"  
in the docs and list archive. DBIC::Schema will try to deploy your  
database (perhaps just from classes) anywhere you define. SQLite  
rules for this and allows, for example, destructive or dangerous  
tests: deleting users, creating orders, etc to be run against a real  
test DB and not just mocked.

I'm writing a series of articles about different models in Catalyst.  
Tomorrow's, coincidentally, is the first in DBIC and does this  
deployment thing but not for tests, for creating a tracking database  
for a web app from a schema. This will be the link when it's live  
sometime tomorrow (I haven't picked a time for this one because I'm  
cleaning it up still but I've been doing them around midnight) --  
Catalyst Model #7: Page view counter/tracker | http://sedition.com/a/ 
2740 -- it will get a "410: Gone" until its publish time rolls around  
tonight/tomorrow. The example code may be confusing because it's  
wrapped in some Catalyst, the db auto-deploys from a controller if  
it's not found on the first try to use it.

Still, I think deploy is a really hot feature and I'm so glad it's  
become more mature/stable lately along with the pieces it relies on  
like SQL::Translator; the folks who work on those rule. Check out  
DBIx::Class::Fixtures too. Chocolate and peanut-butter.

-Ashley


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[fREW Schmidt]

On Mon, Jul 20, 2009 at 10:10 PM, Ashley <[hidden email]> wrote:

On Jul 20, 2009, at 7:52 PM, fREW Schmidt wrote:

Very interesting!  How did you generate the table structure for the SQLite database?

Check out http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook and any mention of the method "deploy" in the docs and list archive. DBIC::Schema will try to deploy your database (perhaps just from classes) anywhere you define. SQLite rules for this and allows, for example, destructive or dangerous tests: deleting users, creating orders, etc to be run against a real test DB and not just mocked.

I'm writing a series of articles about different models in Catalyst. Tomorrow's, coincidentally, is the first in DBIC and does this deployment thing but not for tests, for creating a tracking database for a web app from a schema. This will be the link when it's live sometime tomorrow (I haven't picked a time for this one because I'm cleaning it up still but I've been doing them around midnight) -- Catalyst Model #7: Page view counter/tracker | http://sedition.com/a/2740 -- it will get a "410: Gone" until its publish time rolls around tonight/tomorrow. The example code may be confusing because it's wrapped in some Catalyst, the db auto-deploys from a controller if it's not found on the first try to use it.

Still, I think deploy is a really hot feature and I'm so glad it's become more mature/stable lately along with the pieces it relies on like SQL::Translator; the folks who work on those rule. Check out DBIx::Class::Fixtures too. Chocolate and peanut-butter.

Interesting.  I assumed that might be how you pull it off.  Our models don't have any column type information, but I doubt that will be an issue since everything in SQLite is just TEXT or BLOB or whatever.  One last question.  Did you dump the SQL statements with perl or MySQL?

--
fREW Schmidt
http://blog.afoolishmanifesto.com

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ashley-2]

Some javascript/style in this post has been disabled (why?)

On Jul 20, 2009, at 8:19 PM, fREW Schmidt wrote:

On Mon, Jul 20, 2009 at 10:10 PM, Ashley <[hidden email]> wrote:

On Jul 20, 2009, at 7:52 PM, fREW Schmidt wrote:

Very interesting!  How did you generate the table structure for the SQLite database?

Still, I think deploy is a really hot feature and I'm so glad it's become more mature/stable lately along with the pieces it relies on like SQL::Translator; the folks who work on those rule. Check out DBIx::Class::Fixtures too. Chocolate and peanut-butter.

Interesting.  I assumed that might be how you pull it off.  Our models don't have any column type information, but I doubt that will be an issue since everything in SQLite is just TEXT or BLOB or whatever.  One last question.  Did you dump the SQL statements with perl or MySQL?

I think that's a trick to answer. You don't need to dump any SQL at all. I happen to do so because I use the Loader stuff to create my schemata but I'm dumping definitions (because SQLite has no FK support yet). Your DBIC classes create the DB and from there you could--never tried it but it should work fine--use the original DB via a connected schema to populate the new DB via its own connected schema. Then just iterate through the DB that has data copying it to the one that doesn't.

If your original DB has the FKs and such you could recreate your schema dynamically from a connection to it. See http://search.cpan.org/dist/DBIx-Class-Schema-Loader/lib/DBIx/Class/Schema/Loader.pm#make_schema_at

The data transfer would be (possibly extremely) slow on a big DB and dumping SQL in some standard format and reloading would be faster and maybe easier if you can find the right docs and flags; which I couldn't help with off-hand.

-Ashley

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Eden Cardim]

On Mon, Jul 20, 2009 at 11:07 PM, fREW Schmidt<[hidden email]> wrote:
> Hey guys,
>
> I was thinking it would be cool to do more local development, and the only
> thing tethering me to the VPN is our database server (SQL Server,)
> fortunately for this plan the only really special thing I do in the database
> is FK constraints and maybe a few non-standard defaults (now() etc.)  But I
> think for the most part that stuff will be fine to ignore for non-local
> development.  Anyway, does anyone have any idea how I could use DBIC to
> mirror a server into an sqlite database?  Thanks!

If you're willing to do TDD, you can use DBIx::Class::Fixtures to dump
fixtures on the remote server, then load them onto your local database
while running tests. You can also do the inverse, if you don't depend
on the data in the remote server, deploy into a local database, dump
the fixtures, do your development, then load the fixtures on the
remote server and run the tests to fix any incompatibilities.

--
   Eden Cardim       Need help with your Catalyst or DBIx::Class project?
  Code Monkey                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://edenc.vox.com/            http://www.shadowcat.co.uk/servers/

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...