Adding 'download' column to existing 'visitors' table (as requested)

[Nick the Gr33k]

I have decided to take your advice.
I wasn't able to fit those 'lists' of mine into MySQL's varchar()
datatype after converting them to long strings and that sads me.

My implementation is like the following.
I do not use an extra table of downlaods that i asoociate with table
visitors with a foreing key but decided to add an additional 'download'
column into the existant visitors table:

Here it is:

=================================================================================================================
# ~ DATABASE INSERTS ~
=================================================================================================================
try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY
UPDATE hits = hits + 1''', page )
# get the primary key value of the new added record
cID = cur.lastrowid

# add this visitor entry into database (hits && downloads are defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s)''',
(cID, ref, host, city, useros, browser, lastvisit) )

con.commit()
except pymysql.ProgrammingError as e:
print( repr(e) )
con.rollback()
sys.exit(0)

=================================================================================================================



=================================================================================================================
# ~ Presentation Time
=================================================================================================================
def coalesce( data ):
newdata = []
seen = {}
for host, refs, city, useros, browser, visits, hits, downloads in data:
# Here i have to decide how to group the rows together
# I want an html row for every unique combination of (host) and that
hits should be summed together
key = host
if key not in seen:
newdata.append( [ host, [refs], city, useros, browser, [visits],
hits, [downloads] ] )
seen[key] = len( newdata ) - 1 # Save index (for 'newdata') of this row
else: # This row is a duplicate row with a different referrer &&
visit time && torrent download
rowindex = seen[key]
newdata[rowindex][1].append( refs )
newdata[rowindex][5].append( visits )
newdata[rowindex][6] += hits
newdata[rowindex][7].append( downloads )
return newdata


cur.execute( '''SELECT host, refs, city, useros, browser, visits, hits,
downloads FROM visitors
WHERE counterID = (SELECT ID FROM counters WHERE url = %s) ORDER BY
visits DESC''', page )
data = cur.fetchall()
newdata = coalesce( data )


for row in newdata:
(host, refs, city, useros, browser, visits, hits, downloads) = row
# Note that 'refs' && 'visits' && 'downloads' are now lists

print( '<tr>' )

print( '<td><center><b><font color=white> %s </td>' % host )

print( '<td><select>' )
for ref in refs:
print( '<option> %s </option>' % ref )
print( '</select></td>' )

for item in (city, useros, browser):
print( '<td><center><b><font color=cyan> %s </td>' % item )

print( '<td><select>' )
for visit in visits:
visittime = visit.strftime('%A %e %b, %H:%M')
print( '<option> %s </option>' % visittime )
print( '</select></td>' )

print( '<td><center><b><font color=yellow size=4> %s </td>' % hits )

# populate torrent list
torrents = []
for download in downloads:
if download:
torrents.append( download )

# present visitor's movie picks if any
if torrents:
print( '<td><select>' )
for torrent in torrents:
print( '<option> %s </option>' % torrent )
print( '</select></td>' )
else:
print( '<td><center><b><font color=orange> Δεν Ï€Ïαγματοποίηθηκαν
ακόμη! </td>' )
break

print( '</tr>' )

sys.exit(0)
=================================================================================================================


At least my webpage http://superhost.gr is working now, but i look into
by lookinto into phpmyadmin whats into the database and what is being
presented somethign is worng.

This is a screenshot of my database visit sicne last night which i
decided to use your method: http://i.imgur.com/yquXO7u.png

and this is what is being presented:
http://superhost.gr/?show=log&page=index.html


In my database they are clearly shown lots of entries with counterID = 1
( 1 is related to index.html) but when i ask them to be presented it
only shows 1 hostname.

Where is the rest hostnames having counterID == 1?

Also the counterID values should have been of 1 or 2 or 3 but in
screenshot i see values of 6, 1-, 11, 12

Is there something wrong with the code i provided?
I decided to use your logic and i ask for your help

 

[Nick the Gr33k]

Ah great!!!

I just examined my other MySQL database which just stored webpages and
their corresponding visits and voila.

Someone was able to pass values into my counters table:

look:

http://superhost.gr/?show=stats

thats why it didn't had 1 or 2 or 3 as 'counterID' but more values were
present.

Someone successfully manipulated this part of my code:

if cookieID != 'nikos' and ( os.path.exists( path + page ) or
os.path.exists( cgi_path + page ) ) and re.search(
r'(amazon|google|proxy|cloud|reverse|fetch|msn|who|spider|crawl|ping)',
host ) is None:

try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY
UPDATE hits = hits + 1''', page )
.......
.......

I see no way of messing with the above statement other that tweak with
the 'page' variable but its not clear to me how.

You as more experience can you tell how the aboev code of database insertio

 

[Nick the Gr33k]

Στις 6/11/2013 9:38 πμ, ο/η Nick the Gr33k έγÏαψε:

Ah great!!!

I just examined my other MySQL database which just stored webpages and
their corresponding visits and voila.

Someone was able to pass values into my counters table:

look:

http://superhost.gr/?show=stats

thats why it didn't had 1 or 2 or 3 as 'counterID' but more values were
present.

Someone successfully manipulated this part of my code:

if cookieID != 'nikos' and ( os.path.exists( path + page ) or
os.path.exists( cgi_path + page ) ) and re.search(
r'(amazon|google|proxy|cloud|reverse|fetch|msn|who|spider|crawl|ping)',
host ) is None:

try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON
DUPLICATE KEY UPDATE hits = hits + 1''', page )
......
......

I see no way of messing with the above statement other that tweak with
the 'page' variable but its not clear to me how.

You as more experience can you tell how the aboev code of database insertio

Here is more insight on how i initiate the 'page' variable:

==========================================
# define how the .html or .python pages are called
path = '/home/nikos/public_html/'
cgi_path = '/home/nikos/public_html/cgi-bin/'

file = form.getfirst('file', 'forbidden') # this value should come only
from .htaccess and not as http://superhost.gr/~nikos/cgi-bin/metrites.py
page = form.getvalue('page') # this value comes from 'index.html' or
from within 'metrites.py'

if os.path.exists( file ) and not page:
# it is an html template
page = file.replace( path, '' )
==========================================


Any ideas please on how the hacker manages to pass arbitrary values into
the 'page' var since i explicitly define it and before database
insertion i check for:

if cookieID != 'nikos' and ( os.path.exists( path + page ) or
os.path.exists( cgi_path + page ) )

?!?!

 

[Denis McMahon]

I have decided to take your advice.

No you haven't. You only think you have, but really you either haven't
understood the advice at all.

My implementation is like the following.
I do not use an extra table of downlaods that i asoociate with table
visitors with a foreing key but decided to add an additional 'download'
column into the existant visitors table:

No no no no no no no no no no nononononono no!

That's *NOT* the right way to do it.

And this is where I finally and terminally give up trying to help you.
I've had enough. You refuse to learn the right way to do it. You won't
listen to the opinions and suggestions of people with a great deal more
experience than you have in such matters. It's not going to work properly
in the end. I refuse to be associated with it any further.

 

[rusi]

No you haven't. You only think you have, but really you either haven't…

No, you think that he thinks that he has.
Of course more correctly, I think that you think that he thinks that he haswhereas in fact (I think) that he doesn't think that he has.

[Sorry couldn't resist!]

 

[Piet van Oostrum]

I have decided to take your advice.
I wasn't able to fit those 'lists' of mine into MySQL's varchar()
datatype after converting them to long strings and that sads me.

My implementation is like the following.
I do not use an extra table of downlaods that i asoociate with table
visitors with a foreing key but decided to add an additional 'download'
column into the existant visitors table:

Nikos, you are an excellent member of the Greek society. Listening to you makes it so much easier to understand the problems that your country has.

 

[Îίκος Αλεξόπουλος]

Στις 6/11/2013 7:59 μμ, ο/η Denis McMahon έγÏαψε:

No you haven't. You only think you have, but really you either haven't
understood the advice at all.


No no no no no no no no no no nononononono no!

That's *NOT* the right way to do it.

And this is where I finally and terminally give up trying to help you.
I've had enough. You refuse to learn the right way to do it. You won't
listen to the opinions and suggestions of people with a great deal more
experience than you have in such matters. It's not going to work properly
in the end. I refuse to be associated with it any further.

--
Denis, you may choose to not help any further, thats acceptable as you
personal choice.

I have to inform you though that my solution of adding an extra
'download' column in my 'visitors' table has the benefits of

1. refrain me for creating one more table
2. the download is remained associated with the person that made the
download since all this info is placed in the same record.

My solution works just fine and is giving no problems.
I cant overcome the urge though to try to use some database that can
hold lists to a single

 

[Joel Goldstick]

Στις 6/11/2013 7:59 μμ, ο/η DenisMcMahon έγÏαψε:



--
Denis, you may choose to not help any further, thats acceptable as you
personal choice.

I have to inform you though that my solution of adding an extra 'download'
column in my 'visitors' table has the benefits of

1. refrain me for creating one more table

refraining you is a very good thing

2. the download is remained associated with the person that made the
download since all this info is placed in the same record.

just think, all those folks who figured out databases were wrong.
Nikos has shown that you just need to put everything in a single
record. Wow! look at that record with everything in it! Its so cool
and it helped to refrain Nikos the idiot!

My solution works just fine and is giving no problems.

Great, now that you have not problems, you might consider going away
forever so as not to cause other people problems!

I cant overcome the urge though to try to use some database that can hold
lists to a single.

You need to see a therapist to help with overcoming your urges. We
are not qualified in that area.

 

[Îίκος Αλεξόπουλος]

Στις 7/11/2013 3:52 μμ, ο/η Joel Goldstick έγÏαψε:

just think, all those folks who figured out databases were wrong.
Nikos has shown that you just need to put everything in a single
record. Wow! look at that record with everything in it! Its so cool
and it helped to refrain Nikos the idiot!

--
Why create a whole new 'downloads' table and associate it with the with
a foreign key with the 'visitors' table you idiot when you can just have
an extra column at the end of the current 'visitor's table?

Both 'downloader' and 'downlaod' is associated by being in the same record.

By your logic every time we want to store an extra piece of information
we have to create an extra database table.

Too much hussle for no good reason....

 

[Chris Angelico]

Why create a whole new 'downloads' table and associate it with the with a
foreign key with the 'visitors' table you idiot when you can just have an
extra column at the end of the current 'visitor's table?

Both 'downloader' and 'downlaod' is associated by being in the same record.

By your logic every time we want to store an extra piece of information we
have to create an extra database table.

Too much hussle for no good reason....

Go to your local library and pick up a book on database design - or
possibly you'll find it on Wikipedia. There ARE good reasons for the
"hassle" of normalization. There are times when you consciously
denormalize (I often read tables into memory for a (read-only) cache,
and denormalize aggressively), but the rule of thumb is: It's normal
to normalize.

ChrisA

 

[Grant Edwards]

[nothing my newsreader cared to keep]

OK, so when posting a follow-up, Nikos is now putting his entire
posting into his signature?

This guy's a hoot-and-a-half!

 

[Joel Goldstick]

First of all Nikos, you are not qualified to determine if there is
reason to design a database in any particular way, since you don't
understand what a relational database is.
Secondly, in earlier threads I provided you with a link to a wikipedia
article about first normal form and why it is a necessary component of
data base design.
If you don't want to use a database, good for you, but if you can't
get your website to work and you ask for help, then respond that the
help is 'too much hussle', you are disrespectful.
You don't bother me because you are lazy, and arogant, and whiny. Or
that you lack skills. You are a cargo cult programmer. I know you
won't look that up. You bother me because you are disrespectful. The
most disrespectful person I have ever encountered on line.

On a side note to whoever hacks into Nick the idiots website -- why
not just take it down. Then he won't have any need to spend his 20
euros, and there will be no code to fix. Problem solved!

 

[Sibylle Koczian]

Am 07.11.2013 14:14, schrieb Piet van Oostrum:

Nikos, you are an excellent member of the Greek society. Listening to you makes it so much easier to understand the problems that your country has.

Is there any reason at all to insult all other Greek readers of this
newsgroup?

 

[Tim Chase]

Is there any reason at all to insult all other Greek readers of
this newsgroup?

Greece is no more represented by Nikos than any other nations are
represented by their ignorant. When I start to feel ill-will towards
Greece because of Nikos, I also have to remember that the country has
also produced great technologists like Lea Verou and classical art &
philosophy. Then I just wonder why Nikos doesn't take advantage of
the resources his home country provides. :-/

-tkc

 

[Îίκος Αλεξόπουλος]

Στις 7/11/2013 5:11 μμ, ο/η Joel Goldstick έγÏαψε:

First of all Nikos, you are not qualified to determine if there is
reason to design a database in any particular way, since you don't
understand what a relational database is.
Secondly, in earlier threads I provided you with a link to a wikipedia
article about first normal form and why it is a necessary component of
data base design.
If you don't want to use a database, good for you, but if you can't
get your website to work and you ask for help, then respond that the
help is 'too much hussle', you are disrespectful.
You don't bother me because you are lazy, and arogant, and whiny. Or
that you lack skills. You are a cargo cult programmer. I know you
won't look that up. You bother me because you are disrespectful. The
most disrespectful person I have ever encountered on line.

On a side note to whoever hacks into Nick the idiots website -- why
not just take it down. Then he won't have any need to spend his 20
euros, and there will be no code to fix. Problem solved!

I called you an idiot, because in your previous and current message you
called me too.

I know that splitting information across tables and maintain foreign
keys for retain relationships between them is a necessary thing but in
my case i only just an extra pieces of information to eb associated with
my visitor, a possible file download.

and i have decided just to add an extra colum to the existing 'visitors'
database and this is adequate.

I still don't know why you push me to create an extra table instead.

It may seem that i'm clue resistant sometimes and i'm but this is not
because out of arogance but as a result of failign to under

 

[Neil Cerutti]

I called you an idiot, because in your previous and current
message you called me too.

I know that splitting information across tables and maintain
foreign keys for retain relationships between them is a
necessary thing but in my case i only just an extra pieces of
information to eb associated with my visitor, a possible file
download. and i have decided just to add an extra colum to the
existing 'visitors' database and this is adequate.

Non-normalized data is sometimes a fine idea. How you plan to use
the data once it is stored will be the deciding factor.

One big win with databases is that you can query them really
easily using SQL. Non-normalized data negates that advantage.

How would you write a query to discover all the visitors who
downloaded file XYZ? With your storage scheme, you can't. So by
storing the data this way, you are promising yourself that you'll
never need to write that query, or at least, you won't need to do
it very often.

I still don't know why you push me to create an extra table
instead.

Because it's usually the right thing to do.

 

[Îίκος Αλεξόπουλος]

Στις 7/11/2013 8:08 μμ, ο/η Neil Cerutti έγÏαψε:

Non-normalized data is sometimes a fine idea. How you plan to use
the data once it is stored will be the deciding factor.
Exactly.

One big win with databases is that you can query them really
easily using SQL. Non-normalized data negates that advantage.

How would you write a query to discover all the visitors who
downloaded file XYZ? With your storage scheme, you can't. So by
storing the data this way, you are promising yourself that you'll
never need to write that query, or at least, you won't need to do
it very often.

That would be a problem yes.
But as you said above the deciding factor is the "how" we plan to use
out stored data.

And my plan is to just display the records of all visitors per webpage
with the last column being a list of this specific visitors 'downloads'
as can be seen visually here:

http://superhost.gr/?show=log&page=index.html

'Δεν Ï€Ïαγματοποίηθηκαν ακόμη!' mean that this visitor hasn't download
anything yet, if he does a drop down menu will appear in that place
displaying his file picks.

People can download files from here:'http://superhost.gr/?page=files.py

(these torrent are just for testing reasons. later i will put my own
selection of files)

 

[Piet van Oostrum]

Am 07.11.2013 14:14, schrieb Piet van Oostrum:

Is there any reason at all to insult all other Greek readers of this
newsgroup?

I was talking about the Greek nation. That doesn't imply that every single Greek is like that.

 

[Robert Kern]

I was talking about the Greek nation. That doesn't imply that every single Greek is like that.

Just a majority of Greeks? How comforting.

Please don't.

--
Robert Kern

"I have come to believe that the whole world is an enigma, a harmless enigma
that is made terrible by our own mad attempt to interpret it as though it had
an underlying truth."
-- Umberto Eco