[Dbix-class] Logging all SQL calls
I think I asked about this a while ago on IRC, but I lost track of the discussion I have a large Catalyst app that uses DBIx::Class. We've had a few issues where we need to see an audit log. In some cases we can re-run an action on a staging server with DBIC_TRACE enabled, but in other cases we really need to see what happened on the production machine. Is there an easy way to log either all the database-changing statements, or all SQL period? Obviously the latter would be huge, but might be worth it. I've seen DBIx::Class::AuditLog, which requires that relevant statements be wrapped in a transaction, and DBIx::Class::QueryLog, which seems to be intended for debugging purposes only. I need something that just takes everything and logs it to a text file, without having to rewrite the entire codebase. How do I accomplish this? ___ 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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Tue, Jan 15, 2013 at 05:18:06AM +1100, Peter Rabbitson wrote: On Mon, Jan 14, 2013 at 11:07:34AM -0500, Jesse Sheidlower wrote: So apart from the mystery around the nonsensical exception - does this help? local $DBIx::Class::ResultSourceHandle::thaw_schema = $c-model(...)-schema; Yes! Putting that before the unless seems to make everything work correctly Will be testing further this afternoon. Thanks! Please let me know if wider testing is succesful. In one sense, wider testing was indeed successful. The RS was cached properly, it worked in several different environments, etc. However, it didn't work in one particularly crucial way, which in retrospect I'm surprised I didn't think about: the resultset may have been cached, but once I get it back, it's still just a resultset, so when I use it again in the template, it hits the database again. Every time. This is exactly what I wanted to avoid by this whole escapade. ___ 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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Tue, Jan 15, 2013 at 02:15:02PM +, David Cantrell wrote: On Sun, Jan 13, 2013 at 11:35:06PM -0500, Jesse Sheidlower wrote: I have a Catalyst app that very frequently (pretty much every request) requires several DBIC resultsets that return a small number of values, that very rarely change. I'm trying to cache this, so I can update the cache when the values change and not have to hit my DB a half-dozen times on every request for data that is effectively static. To solve this problem at the BBC in the guts of iPlayer, we used memcache. We *didn't* bother updating the cache if the DB contents changed though, as we didn't mind the user's view being a few minutes out of date. While it wouldn't matter much if things were a little out of date, my site is relatively low traffic, so caching something for a few minutes wouldn't make much difference. So I need to cache it long-term, and I do need to update on a change. I've spent quite a bit of time thinking about how to solve this in the general case, and not come up with anything that works yet - not even anything that works in theory and goes arse-over-tit in practice. Can you at least reduce it to one query with a left right in out shake it all about join? Not really. The problem is that I have a number of separate, unrelated tables, each having category-like data (category, status, region, other-status, etc.), and each being used in different ways, so I have to get each table separately. ___ 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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Mon, Jan 14, 2013 at 10:13:44PM +1100, Peter Rabbitson wrote:
On Sun, Jan 13, 2013 at 11:35:06PM -0500, Jesse Sheidlower wrote:
I have a Catalyst app that very frequently (pretty much every request)
requires several DBIC resultsets that return a small number of values,
that very rarely change. I'm trying to cache this, so I can update the
cache when the values change and not have to hit my DB a half-dozen
times on every request for data that is effectively static.
Originally the relevant line was along the lines of:
$c-stash-{subjects} = $c-model('WordsDB::Subject')-search();
I replaced this, following the C::P::Cache docs, with
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
However, this dies (on a second run, when it's actually hitting the
cache) with undef error - Can't call method select on an undefined
value at /usr/share/perl5/DBIx/Class/ResultSet.pm line 957.
This is a crappy error message. Please tell us which DBIC version are
you running exactly so that we can figure out what is on this line.
It's version 0.08196. This is in the cursor() method.
So I'm assuming that I can't just stuff a RS into the cache and expect
it to work.
Things should just work, at least for Storable-based serialization. Need
more details to diagnose this.
What would you like to know?
Jesse Sheidlower
___
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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Mon, Jan 14, 2013 at 11:06:56PM +1100, Peter Rabbitson wrote:
On Mon, Jan 14, 2013 at 06:30:42AM -0500, Jesse Sheidlower wrote:
On Mon, Jan 14, 2013 at 10:13:44PM +1100, Peter Rabbitson wrote:
On Sun, Jan 13, 2013 at 11:35:06PM -0500, Jesse Sheidlower wrote:
I have a Catalyst app that very frequently (pretty much every request)
requires several DBIC resultsets that return a small number of values,
that very rarely change. I'm trying to cache this, so I can update the
cache when the values change and not have to hit my DB a half-dozen
times on every request for data that is effectively static.
Originally the relevant line was along the lines of:
$c-stash-{subjects} = $c-model('WordsDB::Subject')-search();
I replaced this, following the C::P::Cache docs, with
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
What is $cache being serialized via? Storable? Or some other scheme?
I'm using Cache::FastMmap, which uses Storable (unless you flip a switch
to have it store values as raw binary data; I didn't flip this switch).
However, this dies (on a second run, when it's actually hitting the
cache) with undef error - Can't call method select on an undefined
value at /usr/share/perl5/DBIx/Class/ResultSet.pm line 957.
What is the exact code from get()ing the cache value to the error (or
what is the smallest piece of code you can reduce it to)?
It's in a Catalyst context, so there's a ton of stuff in between, but
the core is that I put the RS into the Catalyst stash as indicated
above, and then in a template I have:
[% WHILE ( subject = subjects.next ) %]
option value=[% subject.id %][% subject %]/option
[% END %]
Thanks.
___
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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Mon, Jan 14, 2013 at 09:41:45AM -0600, will trillich wrote:
One problem I see with the exact code posted here is a typo referring to
singular-vs-plural on the hash key:
On Sun, Jan 13, 2013 at 10:35 PM, Jesse Sheidlower jes...@panix.com wrote:
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
Note 'subject' vs 'subjects':
$cache-set( 'subject', $c-stash-{subjects} );
That was my typo re-keying the actual code for the mailing list, the
original (which presents it differently, in a way not relevant for this
purpose) is correct. Sorry for the misleading example.
Jesse
___
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@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Tue, Jan 15, 2013 at 01:30:55AM +1100, Peter Rabbitson wrote:
On Sun, Jan 13, 2013 at 11:35:06PM -0500, Jesse Sheidlower wrote:
I have a Catalyst app that very frequently (pretty much every request)
requires several DBIC resultsets that return a small number of values,
that very rarely change. I'm trying to cache this, so I can update the
cache when the values change and not have to hit my DB a half-dozen
times on every request for data that is effectively static.
Originally the relevant line was along the lines of:
$c-stash-{subjects} = $c-model('WordsDB::Subject')-search();
I replaced this, following the C::P::Cache docs, with
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
However, this dies (on a second run, when it's actually hitting the
cache) with undef error - Can't call method select on an undefined
value at /usr/share/perl5/DBIx/Class/ResultSet.pm line 957. So I'm
assuming that I can't just stuff a RS into the cache and expect it to
work. Is there an easy way around it? (I know I could retrieve the
actual data and put that into the cache, but then I'd have to rewrite a
whole bunch of templates, that are expecting a resultset.)
Thanks.
So apart from the mystery around the nonsensical exception - does this help?
local $DBIx::Class::ResultSourceHandle::thaw_schema = $c-model(...)-schema;
Yes! Putting that before the unless seems to make everything work
correctly Will be testing further this afternoon. Thanks!
Does this need to be documented somewhere, or does something need to be
fixed, or...?
Jesse
___
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@lists.scsys.co.uk
[Dbix-class] Caching a resultset?
I have a Catalyst app that very frequently (pretty much every request)
requires several DBIC resultsets that return a small number of values,
that very rarely change. I'm trying to cache this, so I can update the
cache when the values change and not have to hit my DB a half-dozen
times on every request for data that is effectively static.
Originally the relevant line was along the lines of:
$c-stash-{subjects} = $c-model('WordsDB::Subject')-search();
I replaced this, following the C::P::Cache docs, with
unless ( $c-stash-{subjects} = $cache-get( 'subjects' ) ) {
$c-stash-{subjects} =
$c-model('WordsDB::Subject')-search();
$cache-set( 'subject', $c-stash-{subjects} );
}
However, this dies (on a second run, when it's actually hitting the
cache) with undef error - Can't call method select on an undefined
value at /usr/share/perl5/DBIx/Class/ResultSet.pm line 957. So I'm
assuming that I can't just stuff a RS into the cache and expect it to
work. Is there an easy way around it? (I know I could retrieve the
actual data and put that into the cache, but then I'd have to rewrite a
whole bunch of templates, that are expecting a resultset.)
Thanks.
Jesse Sheidlower
___
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@lists.scsys.co.uk
[Dbix-class] Disable cascading delete, but then...?
I'm working on a new app, where I have a main table called word, that belongs_to some other tables, including status. While testing a proof-of-concept version of this app, a colleague made the obvious discovery that if you delete a status, you'll also (cascadingly) delete all the words having that status. This is bad. However, if I just set cascade_delete = 0 for the relationship, deleting the status will result in these words having a status_id column that points to a nonexistent row. Are there standard ways of dealing with this automatically, so that, for example, deleting the status will instead set the status_id column of all related words to NULL? Or what else are the usual solutions? Jesse Sheidlower ___ 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@lists.scsys.co.uk
[Dbix-class] Unicode conversion problems
Summary: I have a MySQL database with data in an unknown character set,
or mixture thereof (I thought it was Unicode, but it's not). It displays
correctly when used with MySQL commandline tools under certain
configurations, but I need to figure out how to convert it to proper
Unicode. After muddling through lots of Catalyst/DBIx::Class issues, I
now realize it's at a lower level than that, and I have various test
scripts, and have asked some people for advice, but no one is sure
what's going on or how to fix it.
In my database I have this string:
Pérez-Reverte, Arturo КириÌллица ქáƒáƒ თული æ±‰å— / æ¼¢
On my UTF-8 terminal now, this begins with a capital P, a capital A
with a tilde, and a copyright sign.
This is supposed to look like:
Pérez-Reverte, Arturo Кири́ллица ქართული 汉字 / 漢
The second character is an e with an acute accent; after
Arturo there are characters in Cyrillic, then in Georgian,
then in Chinese.
A MySQL script to test this:
--- foo-load.sql ---
/*!40101 SET NAMES utf8 */;
DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
`author` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET='utf8';
INSERT INTO `foo` VALUES ('Pérez-Reverte, Arturo КириÌллица
ქáƒáƒ თული æ±‰å— / æ¼¢');
--- end foo-load.sql ---
If I load this into a MySQL database, and then select it on the
commandline, it works, i.e. it looks like I want it to:
---
mysql select * from foo\G
*** 1. row ***
author: Pérez-Reverte, Arturo Кири́ллица ქართული 汉字 / 漢
1 row in set (0.00 sec)
---
Note that if I _don't_ have the SET NAMES command, which indicates
what character set the client uses to send messages to the server, it
fails, even though I have the server character set as utf8 in the my.cnf
file, and I specify utf8 as the default charset in the CREATE statement.
I thought that this represented double-encoded Unicode, but
when I wrote a script to fix this with
Encode::DoubleEncodedUTF8, it did not work, or, rather, it
only partially worked:
--- foo-test.pl ---
#!/usr/bin/perl
use strict;
use warnings;
use Encode qw(:all);
use Encode::DoubleEncodedUTF8;
my $data = 'Pérez-Reverte, Arturo КириÌллица ქáƒáƒ თული
æ±‰å— / æ¼¢';
my $fixed = decode(utf-8-de, $data); # Fix it
if (is_utf8($data)) { print Original is Unicode!\n; }
else { print Original is not Unicode!\n; }
if (is_utf8($fixed)) { print Fixed data is Unicode!\n; }
else { print Fixed data is not Unicode!\n; }
print Data is: $data\n;
print Fixed data is: $fixed\n;
--- end foo-test.pl ---
Output:
---
$ perl foo-test.pl
Original is not Unicode!
Fixed data is Unicode!
Data is: Pérez-Reverte, Arturo КириÌллица ქáƒáƒ თული
æ±‰å— / æ¼¢
Wide character in print at foo-test.pl line 20.
Fixed data is: Pérez-Reverte, Arturo Ки�€и́лл�¸Ñ†а ქáƒáƒ თული
æ±‰å— / 漢
$
---
Note that it gets the e acute right in the second character,
and some other random characters, but the rest is glop.
Clearly _something_ can figure this out, because the MySQL
commandline query can retrieve the data in the form I think
it's supposed to look.
I'd be very grateful if anyone could tell me what's going on, and more
importantly, how to fix it. I have several databases that are affected
by this, and they're failing in production.
Thanks. Would also be happy for pointers to other places to ask, since I
now know this is not a DBIC issue.
Jesse Sheidlower
___
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@lists.scsys.co.uk
Re: [Dbix-class] Unicode conversion problems
On Mon, Jul 05, 2010 at 05:45:11PM +0200, Matias E. Fernandez wrote:
Hello Jesse
Please try the following using the table 'foo' you described earlier:
mysql set names utf8;
mysql insert into foo (author) values('Pérez-Reverte, Arturo Кири́ллица
ქართული 汉字 / 漢');
[my mailer is still horking on that first Chinese character; sorry]
then try this script, notice the attributes which in this case are very
important:
[snipped]
What do you get there?
This gives the correct results (i.e. what comes out is what
went in, with the e-acute, Russian, Georgian, and Chinese
characters.
Jesse Sheidlower
___
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@lists.scsys.co.uk
Re: [Dbix-class] Unicode conversion problems
On Mon, Jul 05, 2010 at 05:49:30PM -0400, Jesse Sheidlower wrote:
On Mon, Jul 05, 2010 at 05:45:11PM +0200, Matias E. Fernandez wrote:
Hello Jesse
Please try the following using the table 'foo' you described earlier:
mysql set names utf8;
mysql insert into foo (author) values('Pérez-Reverte, Arturo Кири́ллица
ქართული 汉字 / 漢');
[my mailer is still horking on that first Chinese character; sorry]
then try this script, notice the attributes which in this case are very
important:
[snipped]
What do you get there?
This gives the correct results (i.e. what comes out is what
went in, with the e-acute, Russian, Georgian, and Chinese
characters.
Sorry, let me revise that slightly: I do get the correct
results, but preceded by Wide character in print at
foo-test2.pl line 22.
That doesn't seem good.
Jesse Sheidlower
___
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@lists.scsys.co.uk
Re: [Dbix-class] Unicode conversion problems
On Mon, Jul 05, 2010 at 11:02:02PM +0200, Matias E. Fernandez wrote:
Hello Jesse
I'm pretty sure your data has been UTF-8 encoded twice. Consider this example:
use strict;
use warnings;
use Encode;
# $string is UTF-8, but Perl doesn't know
my $string = 'Pérez-Reverte, Arturo Кири́ллица ქართული 汉字 / 漢';
# $double_utf8 contains the double UTF-8 encoded string
# note that this is an implicit ISO-8859-1 to UTF-8 conversion
my $double_utf8 = Encode::encode('UTF-8', $string);
print double encoded UTF-8:\n, $double_utf8\n\n;
# let Perl believe that $double_utf8 is UTF-8
Encode::_utf8_on($double_utf8);
# run $double_utf8 through a UTF-8 to ISO-8859-1 conversion
my $double_utf8_to_latin1 = Encode::decode('ISO-8859-1', $double_utf8);
print double UTF-8 to ISO-8859-1:\n, $double_utf8_to_latin1\n\n;
Right, that looks correct. But this is latin1, not UTF-8,
so...
So why is your data in the database double encoded UTF-8?
The problem is that you're not using the mysql_enable_utf8
option (see the DBD::mysql documentation). If you don't use
that option as a part to the call to 'connect()', DBD::mysql
will the configure the connection in a way that MySQL
believes it's being sent ISO-8859-1. Because you're table is
configured to store character data as UTF-8, MySQL converts
the received data from ISO-8859-1 to UTF-8. There you have
double encoded UTF-8!
I am now, but there was a point when I hadn't been, or these
tables were first set up as latin-1, or some other screwup.
The problem is, the tables do exist now.
The solution is simply to use mysql_enable_utf8 as part of
the call to 'connect()'. If you're using DBIx::Class I
recommend also disabling the mysql_auto_reconnect option,
this will save you a lot of headache.
But that doesn't help me right now, it only helps me for the
future.
That is, I currently have data in the database, some of which
is double-encoded UTF-8. If I try to retrieve this, setting
mysql_enable_utf8 doesn't help. That is if I take my existing
data (e.g. the example I originally posted), connect to MySQL
with mysql_enable_utf8, and pull the data with a Perl script,
I still get junk.
In your above example you show how to un-double-encode the
data I have, but only by turning it into latin1, right? How do
I take my existing data and turn it into proper UTF-8, at
which point I can make sure everything is set correctly so
that I never have this problem again?
Thanks for looking at this so closely.
Jesse Sheidlower
___
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@lists.scsys.co.uk
[Dbix-class] ResultSet chaining help
A few days ago, I asked a question on #dbix_class about forcing a particular join to be an inner join (as was specified in the schema definition), instead of a left join (as happened in the generated query); my app had started to fail because of a change in DBIC, but apparently my technique was flawed in the first place. I got some helpful suggestions from ribasushi and mst, but I'd like to ask again more formally here, because in order to rewrite what is currently some very complicated code, I want to make sure I fully understand it going in. The main difficulty now is that I use a number of subroutines to build data structures from which my search is constructed. I've adopted various tricks to construct things properly, and while I'm sure that there are better tricks (or that these tricks aren't even necessary), I'm not sure what these are. In particular, constructing the join clause has proven very difficult. My basic table structure is like this (excuse the poor diagramming, and these are has-many's going down and out): Region - Source - Subject | ^ Title - Part - Author | ^ Quotation - Quotation_lookup (from Sphinx) | ^ Region - cwGroup - Subject There are more details, but this will do. Most searches need to return an RS of cwGroups. Some searches need to return an RS of sources. A cwGroup RS will _always_ need to prefetch Part. A Source RS only needs Source. The general use case here is that I have a web form that has fields representing columns in various of these tables. Which tables are joined will depend on what's entered. In the most common and simplest case, someone will search based on a field in the cwGroup table, that table can be queried, joined through to the Part table (the results are also always sorted by a field in the Part table), and that's it. In a more complicated, but by no means uncommon, case, a search will be based on values in the Source table, perhaps even a source-subject thing, as well as things in the cwGroup table, and we'll have to join all the way through from source-subject down through cwGroup (even though there might not be anything being searched in Part or Quotation). The Quotation_lookup thing is a temporary table generated from the results of a query in the Sphinx search engine; if someone wants to do a full-text search of the Quotation table, I do it in Sphinx, return a list of Quotation ID's, and put these into Quotation_lookup. In this case, and in the case of Author, Title, Subject, or Region searches, these all need to be inner joins, not left joins--I want to get only those results where there _is_ a matching row in the quotation-lookup, author, etc. table--if it doesn't match, I don't want a result returned. Obviously. I had previously declared these with a join_type of 'INNER' in my schema declaration, but I now get the impression that I just have to create the query differently. What I've been doing is keeping track of what tables are used for a query, and then having a _get_cwGroup_join or _get_source_join routine that has a big if-else table going through various possibilities for which tables need to be joined in, and return a data structure for the join. Finally--the database is large enough that I do want to execute these as single joined queries, not multiple queries. Thanks for reading this far. Jesse Sheidlower ___ 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@lists.scsys.co.uk
[Dbix-class] Example of AJAXified DBIC::Ordered?
I have an app where I have a small list of items that can be arranged in various ways. I figured that the best way of doing this would be to use DBIx::Class::Ordered along with some AJAX-y thing, perhaps jQuery UI's Sortables widget, to rearrange these in a browser and renumber them in the database. Before I bother, does anyone have some code for this that I can steal? Thanks. Jesse ___ 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@lists.scsys.co.uk
Re: [Dbix-class] soft deletion
On Mon, Feb 16, 2009 at 01:49:08PM -0600, fREW Schmidt wrote:
Hello friends!
I am sure there is a better term for this, I just don't know what it is. I
would like for my database to have some type of removal field that would
effectively mark a row as deleted without deleting it. I've seen ORMs that
do this before and I was wondering if DBIC did something or had support for
something like this.
To make myself more clear, I'd like either a binary flag or a date which
would probably be called deleted or date_deleted or is_deleted (feel free to
give input in this) and I'd like it not to show up in regular resultsets
unless I explicitly say I want to see the deleted rows.
Thoughts?
Other people have made what are probably more robust
suggestions. However, what I do in my apps is very similar to
what you're asking for here:
In any table to which this applies, I have a field called
deleted defined as datetime default NULL. When I want to
delete a field, I have this in my do_delete() routine:
my $record = $c-stash-{model}-find($id); # add error-checking
$record-deleted(\'now()');
$record-update;
Then, in my ResultSet class I just have this:
# only find non-deleted records
__PACKAGE__-resultset_attributes({ where = { deleted = undef } });
Presto, any normal search, find, etc. operation will always
only find the non-deleted records; I don't have to think about
the fact that the deleted ones are still in the database,
just with a flag set. It's transparent to the user.
I could have sworn that I wrote this into a cookbook entry,
but I can't find it now.
Jesse Sheidlower
___
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@lists.scsys.co.uk
[Dbix-class] Prefetch problem
I'm working on something using prefetch, and though I thought
I was using it correctly, the SQL that it's generating is
dying with a syntax error, and since I can't even see the
error in the SQL, I figured I'd ask here, perhaps so that the
mere act of typing it will reveal what I'm doing wrong.
I have a table _lessons_ that had previously had columns
group and performer in it, but I normalized the database
by putting these columns into separate tables. So now part
of my _lesson_ schema looks like this:
__PACKAGE__-belongs_to( group = 'MyDB::Schema::Main::LessonGroup', 'group_id'
);
__PACKAGE__-belongs_to( performer = 'MyDB::Schema::Main::Performer',
'performer_id' );
__PACKAGE__-resultset_attributes({ order_by = 'me.title', prefetch =
[qw/performer group/ ]} );
When I run a query on this table from a Catalyst app like so:
my $lesson = $c-model-find($id);
I get the following generated SQL according to DBIC_TRACE
(reformatted and with the id value filled in so I can paste it
into the MySQL commandline):
SELECT me.id, me.title, me.performer_id, me.number, me.capo, me.tuning,
me.tab_id, me.audio_id, me.group_id, performer.id, performer.name, group.id,
group.name
FROM lesson me
JOIN performer performer ON ( performer.id = me.performer_id )
JOIN lesson_group group ON ( group.id = me.group_id )
WHERE ( ( me.id = 35 ) )
ORDER BY me.title;
This gives me the error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ON (
group.id = me.group_id )
WHERE ( ( me.id = 35 ) )
ORDER BY me.title' at line 4
Since I can't even see the error in the SQL, let alone in the
DBIC stuff, I must assume that I'm doing something
horrendously wrong. Can someone please tell me what it is?
BTW dropping the prefetch from the attributes, so I just have
__PACKAGE__-resultset_attributes({ order_by = 'me.title'});
works fine, except that I have two extra queries.
Thanks.
Jesse Sheidlower
___
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/[EMAIL PROTECTED]
