Re: [SQL] SHA-1 vs MD5
I really don't have the pgcrypto. It could be a nice alternative. Could you
tell me the steps to install it ?
I am very concerned about security in my application becouse we are going to
moviment a large ammount of information and money. As much i take care of it
as good.
I know some problem of MD5 and know it is very good too. If someone could
tell me where MD5 is used I could be more relaxed.
My best regards to all
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
2007/3/7, Chad Wagner <[EMAIL PROTECTED]>:
On 3/7/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
>
> I know that there is a md5 internal function on postgresql, but I
> noticed that it isn't the more secure today. I would like to know if there
> is a SHA-1 function implemented yet of, if not, if the team has plan to
> introduce it on PostgreSQL.
You are probably looking for the pgcrypto contribution, look in the
contrib directory for it.
test=# select encode(digest('blahblah', 'sha256'), 'hex');
encode
--
40b1bbb5445fc021a312315379f4633284851e14d1db83fb0730f58872d6033b
(1 row)
--
Chad
http://www.postgresqlforums.com/
Re: [SQL] SHA-1 vs MD5
On 3/8/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: I really don't have the pgcrypto. It could be a nice alternative. Could you tell me the steps to install it ? This should help you out: http://www.postgresql.org/docs/8.2/static/external-extensions.html http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/README?rev=1.91;content-type=text%2Fplain I am very concerned about security in my application becouse we are going to moviment a large ammount of information and money. As much i take care of it as good. SHA1 and MD5 are hashing algorithms, they are typically used for passwords and in conjunction with public key encryption or over-the-wire encryption to sign the message. If you are really concerned about security, you may want to hire an experienced person in the security engineering field. Especially if you are talking about financial information. I know some problem of MD5 and know it is very good too. If someone could tell me where MD5 is used I could be more relaxed. The impression I get is that SHA-256, SHA-384, or SHA-512 are the preferred hashing algorithms, but I really don't keep up on it. Many many password systems use MD5, I think it is reasonably safe.
[SQL] A form of inheritance with PostgreSQL
Hello. I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange I want to design the foreign key scheme such that there are relations between fruit and apple, and fruit and orange, that imply that apple is a fruit, and orange is a fruit. I don't want to eliminate the existence of Apple and Orange tables, because there will be columns specific to both Apple and Orange; if I include these columns in Fruit, then if Fruit is an Orange, the Apple columns will be needlessly present in Apple rows. The different ways of implementing this scheme that I've thought of (some uglier than others): - Have Fruit contain foreign keys to both Apple and Orange, and write a check constraint in Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be non-null. The disadvantage of this method is that it isn't exactly loosely coupled. For every other fruit type table I implemented I'd have to go back and add a foreign key in Fruit. - Have a foreign key in Apple to Fruit, and in Orange to Fruit; then somehow create a constraint that imposes uniqueness on the union of foreign keys in both Apple and Orange. To figure out what type of fruit a Fruit row is, run a query for foreign keys in Orange and Apple matching the primary key of Fruit. You'd also want to somehow create a constraint that the result of this query should always return exactly one row (perhaps with a trigger?) Any advice will be appreciated! As I'm relatively new to Postgre, I might need some help with the actual implementation as well. Thank you. - Greg
Re: [SQL] A form of inheritance with PostgreSQL
Greg Toombs wrote: Hello. I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following: Don't do a lot of this myself, but I do know that there are several object-relational mappers that do this sort of stuff for you. Might be worth a bit of googling. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] A form of inheritance with PostgreSQL
> I want to design the foreign key scheme such that there are relationsbetween > fruit and apple, and fruit and orange, that imply that apple isa fruit, > and orange is a fruit. Sometime in the future, you will be able to achieve this beautifully and easily using postgresql's feature known as table-inheritance. Right now it can already support hierchey between different types of fruit, however it does not allow for foreign key relationships. However, I figured out a work-around using seperate a table for each entity of the heirchey. And then I rolled up these entities using views made update-able with the Postgresql rule system. Here is a link were I present how it is done. http://archives.postgresql.org/pgsql-general/2006-12/msg00913.php Let me know if you have any questions. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] inheritance
> --- Greg Toombs <[EMAIL PROTECTED]> wrote: > > > I'm trying to figure out how to nicely implement a C++ class-likesystem > > with PostgreSQL. Consider the following: > > Tables Fruit, Apple, Orange you can do this traditionally or through pg inheritance, although I do not think inheritance is well supported before 8.2. from years of experience the easiest approach and aesthetically the least satisfying approach is to put everything into the fruit table create table fruit( fruit_id integer primary key, fruit_tp varchar(12), ... ); with this approach you simply deal with whatever column's your interested in - with apples the orange specific columns are dead wood - they don't get in the way and take up no storage - you just need to learn to ignore them, maybe using views to help. you can have a fruit table plus apple and orange tables. create table fruit( fruit_id integer primary key, ) create table apple( apple_id integer primary key, fruit_id integer not null references fruit, ) you then need to build views to join fruit with apple and oranges, because some of the apple attributes are in the fruit table. lastly you need to handle dml. for example, when you insert an apple you need to insert into the fruit and the apple table. this can be done either through your application bracketing your dml with a begin and commit, or can be done through rules (much, much cooler) (the doc on rules will hold your hand through this). Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] inheritance
chester c young wrote: --- Greg Toombs <[EMAIL PROTECTED]> wrote: I'm trying to figure out how to nicely implement a C++ class-likesystem > > with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange you can do this traditionally or through pg inheritance, although I do not think inheritance is well supported before 8.2. Inheritance will most likely fit your C++ class-like system better and will not need foreign keys. Inheritance has been available in postgresql for many years and I think you will find it quite stable. (Not sure if pg6.x had it but it was available in 7.x) Historically a Table in postgresql was called a class (pre-sql) which is still reflected in the system catalogs with pg_class containing the list of tables and other classes like indexes http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html You would then have table fruit as your base class and table oranges that inherits from fruit, giving it all the columns that fruit has plus any that are added to table oranges as well. selecting from table fruit will allow you to get all rows from table apples and table oranges but not the columns unique to the apples or oranges tables. Maybe then you'll add a table basket that has a foreign key to the fruit table... ;-) -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] inheritance
> > Maybe then you'll add a table basket that has a foreign key to the fruit > table... ;-) >From the inheritance link: ... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. ... You can create a foreign key to the fruit table to a table basket, but this foreign key will only work for fruit that was directly inserted into the fruit table. Any fruit inserted into the Apples or Oranges table can not be referenced by the table basket. I believe that this limitation in table inheritance will not work for Greg's requirements. Having said this, it would make me very happy if I am wrong. I hate modeling data the hard way when there is a better way of doing it. ;) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] inheritance
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > > > Maybe then you'll add a table basket that has a foreign key to the > fruit > > table... ;-) > > From the inheritance link: > ... > A serious limitation of the inheritance feature is that ... it's my understanding that inheritance has become much stronger in 8.2, although it still only inherits parts of the table. when primary keys and triggers are inherited then I'll be in heaven - until then rules rule. Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] inheritance
> > it's my understanding that inheritance has become much stronger in 8.2, > although it still only inherits parts of the table. True. But from what I understand, the only new feature that was added to table-inheritance was the ability to ALTER a table so that it inherits another table. Before this, I think table inheritance could only be created from the CREATE TABLE statement. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[Re: [SQL] PRIMARY KEY]
If you actually need to know the value of N_GEN in your ASP application,
you will need to query the database first and select the NEXTVAL from
the sequence that the "serial" data type will create, then use that
returned value in your insert - ie, DON'T exclude it from the insert,
otherwise it will default to NEXTVAL again and return a different value.
The only catch with this is that you most likely won't end up with
contiguous values in this column - ie, if a user cancels after you seect
nextval, but before you insert - the value of the sequence has already
increased, and will be increased again before returning a value when you
next select nextval
Cheers,
~p
On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:
> Hello,
>
> Is it possible to redesign your table as follows:
>
> create table Mod48_00_2007 (
> IDtext,
> N_GEN serial not null,
> FORMSTORE text,
> COD_NOTATIO text,
> PA_COGNOMEtext,
> constraint pk_Mod48_00_2007 primary key (N_GEN)
> );
>
> Your insert simply becomes:
> INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME)
> VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')
>
> Do note that you do not refer to the N_GEN column, it will use the
> next value, please refer to
> http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
> for more information.
>
> Mario
>
> Shavonne Marietta Wijesinghe wrote:
>
> > Hello
> >
> > I have created a table
> > CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY,
> > FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
> >
> > And i insert the rows via a form in ASP. When the form loads i have
> > a functin that goes and gets the value of the field N_GEN adds 1 to
> > it and shows it to the user.
> > The problem is when i have 2 users working at the same time.
> >
> > For example the last value in my field N_GEN is 2
> > When both the users A and B loads the form (ASP page) it sees N_GEN
> > = 3 :)
> >
> > So they fill in the form and user A clicks on the button OK and the
> > record has been inserted with N_GEN = 3. But when the user B clicks
> > on the button the record is not inserted because it has the same key
> > "3"
> >
> > INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO,
> > PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE
> > MARTINIS')
> >
> > Is there any way i can do this automatically? i mean maybe i have to
> > use someother property instead of "Primary Key" ??
> >
> > Thanks
> >
> > Shavonne Wijesinghe
> >
>
>
[SQL] View Vs. Table
Hi, I have two tables which currently are being aggregated into a third table. I am proposing eliminating or drastically shortening the 3rd aggregation table, and instead just using a View. Which brings me to the question, which is better? Reading from a table or a view or is there a difference in performance? >From the application's perspective the View is much better as data is being stored in one place, reducing errors and also storage. But from the db point of view, is there a bigger performace hit when I query a view Vs a table or is there no difference. Thanks, Radhika -- It is all a matter of perspective. You choose your view by choosing where to stand. Larry Wall --- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Select when table have missing data
I have a table that contains historical exchange rates: date_time | timestamp xrate | real There is a maximum of one entry per day, but data are missing on weekends and holidays. For these missing dates I must use the value from the previous day (e.g. since data for a Sunday is missing I must use the value from the Friday just before the weekend). I have two questions: 1) Since historical exchange rates are not supposed to change I thought about creating a new table with data for all the missing dates (calculated using some procedural language). However, I would be much happier if there was a way to do this using SQL in a SELECT statement. Do you have any hints for this? 2) I have a number of other tables where data may be missing for different reasons. These data may be on a daily or an hourly basis. When a user selects a range of data from e.g. the 1st of January to the 1st of February I would like to be able to return a full set of data where all missing entries are returned as NULL. Is there a smart way to do this using SQL? Any hints or references you may have on the subject of handling missing data in time series data are very welcome. If there is a smarter way to set up tables for handling this type of data then please enlighten me. Thanks Lars ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] inheritance
Richard Broersma Jr wrote: Maybe then you'll add a table basket that has a foreign key to the fruit table... ;-) From the inheritance link: ... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. ... You can create a foreign key to the fruit table to a table basket, but this foreign key will only work for fruit that was directly inserted into the fruit table. Any fruit inserted into the Apples or Oranges table can not be referenced by the table basket. I believe that this limitation in table inheritance will not work for Greg's requirements. Having said this, it would make me very happy if I am wrong. I hate modeling data the hard way when there is a better way of doing it. ;) You can get and store related records but the issue is that you need to maintain referential integrity yourself instead of postgresql doing it for you. So currently your right, next release or two maybe not. There is a current discussion (on hackers list) on partitioning that has been going over ways to tackle the primary / unique constraints across multiple child tables and that could lead to a solution that can be applied to this as well. Partitioning is using inheritance to spread data across multiple tables. eg you may have one table for each month's worth of data. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Select when table have missing data
On 2 Mar 2007 01:17:33 -0800, Lars Gregersen <[EMAIL PROTECTED]> wrote:
I have a table that contains historical exchange rates:
date_time | timestamp
xrate | real
There is a maximum of one entry per day, but data are missing on
weekends and holidays. For these missing dates I must use the value
from the previous day (e.g. since data for a Sunday is missing I must
use the value from the Friday just before the weekend).
I have two questions:
1) Since historical exchange rates are not supposed to change I
thought about creating a new table with data for all the missing dates
(calculated using some procedural language). However, I would be much
happier if there was a way to do this using SQL in a SELECT statement.
Do you have any hints for this?
2)
I have a number of other tables where data may be missing for
different reasons. These data may be on a daily or an hourly basis.
When a user selects a range of data from e.g. the 1st of January to
the 1st of February I would like to be able to return a full set of
data where all missing entries are returned as NULL. Is there a smart
way to do this using SQL?
Any hints or references you may have on the subject of handling
missing data in time series data are very welcome.
If there is a smarter way to set up tables for handling this type of
data then please enlighten me.
Thanks
Lars
generate_series() is your friend:
--
create table t(
date_time timestamp
, xrate real
);
insert into t values ('2007-3-7',0.23);
insert into t values ('2007-3-8',0.1);
insert into t values ('2007-3-9',0.2);
-- no '2007-3-10'
-- no '2007-3-11'
insert into t values ('2007-3-12',0.3);
insert into t values ('2007-3-13',0.4);
-- no '2007-3-14'
insert into t values ('2007-3-15',0.99);
-- no '2007-3-16'
select d.*, (
select xrate
from t
where date_time = (
select max(date_time)
from t
where date_time <= d.ddate
)
) as xrate
from (
select ('2007-3-7'::date+s.x)::timestamp as ddate
from generate_series(0,9) s(x)
) d
--
Extrapolate for case 2.
Regards.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Creating views
Hi all, Is it possible to create views that take parameters? Basically, I have to create some very complex historical reports, and as of now I am using temporary tables to store intermediate query results. I calculate values based on the content of these temporary tables and use them in the final result. I would like to eliminate the need for temporary tables and use views instead, as the data copy between the temp tables is taking quite some time. Thanks, Kashmira ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
