incubator-jspwiki-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Glen Mazza <glen.ma...@gmail.com>
Subject Re: database script simplifications?
Date Sat, 18 May 2013 21:09:12 GMT
Users can do all the filtering they want -- tables and columns (although 
just tables should be good enough as stated below) -- anyway, just 
removed the filtering from the DDL scripts.

Glen

On 05/18/2013 12:59 PM, Glen Mazza wrote:
> Aha!, now I understand -- I didn't realize that the filtering is also 
> being done in JDBCUserDatabase.java and JDBCGroupDatabase.java, which 
> reads the column and table names from jspwiki.properties to determine 
> which table name and which columns to query/update.
>
> The *only* thing I was seeing that filtering being used for was in the 
> SQL for the PostgreSQL and HSQLDB scripts, and I was thinking all that 
> complexity just for that?!?
>
> As you're saying, it would be an extremely rare use case for JSPWiki 
> to be able to share another table due to password hashing--but even if 
> that weren't the case, I think it's sufficient to just provide aliases 
> for *table* names and not column names, because a DBA can create a 
> database view with the column names we require allowing JSPWiki access 
> to their special table, and then plug in view names for 
> jspwiki.userdatabase.table, jspwiki.userdatabase.roleTable and 
> jspwiki.groupdatabase.table properties.  This provides a potential 
> opportunity for the user to use their own table without maintaining 
> umpteen column variables while taking into account the general 
> rareness of the situation (and database views are cleaner IMO anyway). 
> So I'm now proposing to just get rid of the column aliases from [1], 
> we'll keep the table ones.  Also, I'll keep the PostgreSQL scripts 
> however remove from them the test rows[2] as we just test on HSQLDB 
> (we're testing security, not whether databases work).  The PostgreSQL 
> scripts will be kept for those who want to use that database for their 
> tables in production (indeed, we may end up creating a few more 
> scripts over time for MySQL, Derby, whatever.)  Any objections from 
> anyone?
>
> Regards,
> Glen
>
> [1] 
> http://svn.apache.org/viewvc/incubator/jspwiki/trunk/src/test/resources/jspwiki.properties?revision=1479697&view=markup#l99
> [2] 
> http://svn.apache.org/viewvc/incubator/jspwiki/trunk/etc/db/postgresql/userdb-setup.ddl?revision=1400875&view=markup#l61
>
> On 05/18/2013 07:22 AM, Andrew Jaquith wrote:
>> Some context:
>>
>> I created the Postgres scripts mostly because that was the database I 
>> used in production, not so much because of a surge in requests for 
>> Postgres support. :)
>>
>> The filtering scripts were made so that, in theory, one could easily 
>> map into an existing database where user and group info was kept.
>>
>> In practice I think our implementation of password hashing, for 
>> example, makes it unlikely that JSPWiki would ever share a database 
>> with another application. It's too specific to JSPWiki.
>>
>> So, +1 on both of Glen's recommendations. I agree with his logic.
>>
>> Andrew
>>
>> On May 17, 2013, at 10:22 PM, Glen Mazza <glen.mazza@gmail.com> wrote:
>>
>>> Are you sure?  The filtering is used only with "CREATE TABLE" and 
>>> subsequent INSERT INTO statements, so there's no adapting to 
>>> existing tables that could be occurring. All I can see with the 
>>> filtering, is, as I mentioned earlier, if we decide to change a 
>>> table or column name in jspwiki.properties so it propagates to both 
>>> the PostgreSQL and the HSQLDB scripts, a use case that in addition 
>>> to never happening doesn't help much as it would be easy to update 
>>> two files anyway.  i.e., the added complexity seems to be well 
>>> failing cost/benefit analysis. Further, an external user won't be 
>>> able to use the scripts as-is without running Ant to populate the 
>>> table and column names, so it's not helping external users--they 
>>> would benefit from no filtering because it can give them scripts 
>>> they can use OOTB without messing with Maven/Ant.
>>>
>>> All we ever test on personally in hsqldb today, which indicates we 
>>> (ourselves) don't need the postgresql scripts (I'm guessing JSPWiki 
>>> first used PostgreSQL while HSQLDB was a newcomer, but now the 
>>> latter is much more common than the former--the scripts themselves 
>>> date to 2006 judging from the timestamps in the insert statements); 
>>> further, for external users, these are very simple create table 
>>> statements, easily adaptable as anyone would like to 
>>> Oracle/MySQL/Derby/SQLServer whatever, they won't be benefitting 
>>> from a second set of PostgreSQL SQL statements.  I mean if we had 
>>> HSQL and MySQL, or Derby or Oracle scripts that might make sense to 
>>> retain, but not that many care about PostgreSQL today.
>>>
>>> While I can activate filtering in Maven as well, that's doable 
>>> (we're doing enough of it already), without any further information 
>>> to the contrary I'd +1 on getting rid of the filtering and the 
>>> postgresql scripts, let's simplify here and we can easily bring it 
>>> back later should we get requests for it.
>>>
>>> Glen
>>>
>>>
>>> On 05/17/2013 07:22 PM, Juan Pablo Santos Rodríguez wrote:
>>>> Hi (again :D),
>>>>
>>>> the filtering script idea is just to be able to adapt to existing 
>>>> tables &
>>>> data. I'm not 100% sure right now, but I think that the HSQL & 
>>>> PostgreSQL
>>>> scripts are db specific (could be wrong). Also, they can be used for
>>>> production, although in the build they're used for testing. To enable
>>>> specific Postgre testing, a manual download of the driver and 
>>>> tweaking the
>>>> build script slightly was needed, if I recall correctly.
>>>>
>>>>
>>>> br,
>>>> juan pablo
>>>>
>>>> On Fri, May 17, 2013 at 3:26 AM, Glen Mazza <glen.mazza@gmail.com>

>>>> wrote:
>>>>
>>>>> Hi Team,
>>>>>
>>>>> Unsure, but I'm inclined to simplify our database creation scripts,
>>>>> presently we use a bunch of symbolic terms instead of the actual 
>>>>> table and
>>>>> column names:
>>>>> http://svn.apache.org/viewvc/**incubator/jspwiki/trunk/etc/**
>>>>> db/hsql/userdb-setup.ddl?**revision=1426919&view=markup<http://svn.apache.org/viewvc/incubator/jspwiki/trunk/etc/db/hsql/userdb-setup.ddl?revision=1426919&view=markup>

>>>>>
>>>>>
>>>>> The real table and column names are replaced/filtered from the
>>>>> jspwiki.properties file here:
>>>>> http://svn.apache.org/viewvc/**incubator/jspwiki/trunk/src/**
>>>>> test/resources/jspwiki.**properties?revision=1479697&**view=markup#l99<http://svn.apache.org/viewvc/incubator/jspwiki/trunk/src/test/resources/jspwiki.properties?revision=1479697&view=markup#l99>

>>>>>
>>>>>
>>>>> The idea is that we can change a table or column name in one place
>>>>> (jspwiki.properties) and it will cascade to the hsql and postgresql
>>>>> scripts.  However, we only have a few tables and we almost never 
>>>>> (probably
>>>>> never) change table and column names anyway.  I think it might be 
>>>>> good to
>>>>> remove the filtering and just hardcode the table and column names 
>>>>> in the
>>>>> create scripts.  That will simplify the Maven pom and 
>>>>> jspwiki.properties
>>>>> files a bit as well as make the system easier to understand. WDYT?
>>>>>
>>>>> Also, do we need the PostgreSQL scripts today?
>>>>> http://svn.apache.org/viewvc/**incubator/jspwiki/trunk/etc/**db/<http://svn.apache.org/viewvc/incubator/jspwiki/trunk/etc/db/>First

>>>>> of all I'm unsure if these database scripts (hsql and postgresql) are
>>>>> for production also or only test, if the latter, we only test with 
>>>>> hsql
>>>>> anyway so maybe we can delete PostgreSQL.  If this is also for 
>>>>> production,
>>>>> I would guess 90% are happy with hsql and those not happy with 
>>>>> hsql aren't
>>>>> going to be any happier with PostgreSQL (i.e., people leaving hsql 
>>>>> would
>>>>> rather switch to MySQL, Derby, Oracle, etc...)  Then again, maybe we
>>>>> provide two database options to make sure we're not coding in a 
>>>>> specific
>>>>> database-dependent manner.
>>>>>
>>>>> Regards,
>>>>> Glen
>>>>>
>>>>>
>


Mime
View raw message