Cookie DB

[Destramic]

hey guys im trying to create a cookie storage database...i want to update a cookie/row, but if the cookie doesnt exist i want to insert it a row, like so:

 

UPDATE cookies SET value = 1
                 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND)
WHERE user_agent_checksum = 'fffff'
AND name = 'test'
AND domain = 'test'
AND path = '/'
AND expiry > NOW()
AND NOT EXISTS(
INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_addess, ip_address_checksum)
             VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1)
)

 

but i get a error:

 

Error Code: 1064. 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 'INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_c' at line 8

 

so my question is how do i update a cookie/row, but if it doesnt exist then to create a new one please?

 

here is the table structure:

CREATE TABLE `cookies` (
  `cookie_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` blob NOT NULL,
  `value` blob NOT NULL,
  `path` varchar(45) NOT NULL,
  `domain` varchar(45) NOT NULL,
  `ip_address` blob NOT NULL
  `ip_address_checksum` blob NOT NULL,
  `user_agent` blob NOT NULL,
  `user_agent_checksum` blob NOT NULL,
  `expiry` timestamp NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cookie_id`)
)

 

thank you

[Barand]

You are doing it backwards.

You need to insert but if it does exist then update instead.
 

INSERT INTO tablename ( ... ) VALUES (...)
ON DUPLICATE KEY UPDATE ....

https://dev.mysql.com/doc/refman/5.7/en/insert.html

[Destramic]

13 minutes ago, Barand said:

You are doing it backwards.

You need to insert but if it does exist then update instead.
 

INSERT INTO tablename ( ... ) VALUES (...)
ON DUPLICATE KEY UPDATE ....

https://dev.mysql.com/doc/refman/5.7/en/insert.html

 

thank you for your reply barand, but....

cookie id is the only key in the table which isnt present during the insert causing there never to be a duplicate key and just inserts and not update.

INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_address, ip_address_checksum)
             VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1)
ON DUPLICATE KEY
UPDATE value = 1
	 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND)

 

i could just insert a new cookie/row regardless if it cookie exists...and the cookies like so?

 

SELECT DISTINCT(name)
	 , value
FROM cookies
WHERE expiry > NOW()
ORDER BY created DESC

 

unless you have a better way

 

[Barand]

Which field/s determine if a cookie exists or not?

I would guess at domain/name

[Destramic]

i suppose these fields, would overall determine if a cookie exists:

 

SELECT DISTINCT(name)
	 , value
FROM cookies
WHERE user_agent_checksum = '...'
AND domain = 'http://mysite.uk'
AND path = '/'
AND expiry > NOW()
ORDER BY created DESC

 

[Barand]

Define the combination of fields as UNIQUE, for example to ensure domain/name are unique

ALTER TABLE `cookies`  
ADD UNIQUE INDEX (domain, name);

 

[Destramic]

now that is smart way of doing it :), i wouldt even need to create a cookie garbage collector

 

thank you for helping me, again, barand

[Destramic]

one lastthing, i want to make user_agent_checksum a unique index, but cant as its a blob, is there away around this...or will i have to make it a text type.

 

[Barand]

Quote

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 8.3.4, “Column Indexes”.

 

https://dev.mysql.com/doc/refman/5.7/en/blob.html

[Destramic]

after my last post, i tried text type and got the same issue....varchar it is

thank you for your time barand