Wouldn't I run the risk of collisions there? - Cloudflare Developers

Rabid

R

RabidOP•11/11/23, 2:50 PM

Would be pretty bad

Rabid

RRabid Wouldn't I run the risk of collisions there?

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:51 PM

Technically yes, practically? I really doubt it. Provided the hash is long enough.

Rabid

R

RabidOP•11/11/23, 2:52 PM

So just keep a third column with the hash?

Rabid

R

RabidOP•11/11/23, 2:52 PM

And index that instead of the long one?

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:52 PM

Yes, one option.

Rabid

R

RabidOP•11/11/23, 2:53 PM

Ah, that's a good idea

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:53 PM

Or just use the hash as the link

Rabid

R

RabidOP•11/11/23, 2:53 PM

Well, the hash would be bigger than I want though, I feel like 1-LnFi0Jy6sbF is a pretty nice length

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:54 PM

You can do lower lenght hashes, too.

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:54 PM

Or just use a calculated column with the hash, SQLite should support the hashing function.

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:54 PM

Oh, maybe not.

itsmatteomanf

Iitsmatteomanf Or just use a calculated column with the hash, SQLite *should* support the hashi...

Rabid

R

RabidOP•11/11/23, 2:55 PM

Hmm, not familiar with this, I'll have to look into it

Rabid

R

RabidOP•11/11/23, 2:55 PM

Thanks!

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:55 PM

I do know there are DB engines that do hash calculations automatically, SQLite may not so you might need to add it manually.

itsmatteomanf

I

itsmatteomanf•11/11/23, 2:58 PM

By the way, here are some stats on the hash collision probability, @Rabid

https://stackoverflow.com/questions/62664761/probability-of-hash-collision

Stack Overflow

Probability of hash collision

I am looking for some precise math on the likelihood of collisions for MD5, SHA1, and SHA256 based on the birthday paradox.
I am looking for something like a graph that says "If you have 10^8 ...

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:01 PM

How many options have you got on that tree? How many possibilities?

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:02 PM

I'm honestly curious to calculate the chances here...

Rabid

R

RabidOP•11/11/23, 3:02 PM

Would be hard to calculate honestly.

Rabid

R

RabidOP•11/11/23, 3:02 PM

People can choose "paragon" skills:

Rabid

R

RabidOP•11/11/23, 3:02 PM

many many possibilities

Rabid

R

RabidOP•11/11/23, 3:02 PM

58 choices for skills to allocate

Rabid

R

RabidOP•11/11/23, 3:02 PM

Plus gear choices etc

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:03 PM

I see...

Rabid

RRabid People can choose "paragon" skills:

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:03 PM

Do you know how many one can select and how many are there?

Rabid

RRabid 58 choices for skills to allocate

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:03 PM

Same here?

Rabid

RRabid Plus gear choices etc

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:03 PM

And here?

itsmatteomanf

Iitsmatteomanf Do you know how many one can select and how many are there?

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:04 PM

(Ignoring the fact some can only be chosen if the adjacent is, like total skill points given vs. total possible options)

Rabid

R

RabidOP•11/11/23, 3:04 PM

225 max selections for the paragon boards, 6 boards, up to 441 tiles per board

Rabid

R

RabidOP•11/11/23, 3:04 PM

Boards can be rotated

Rabid

R

RabidOP•11/11/23, 3:04 PM

Positioned differently lol

Rabid

R

RabidOP•11/11/23, 3:04 PM

they have slots where you can socket "glyphs" there are like 50 of those

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:04 PM

Oh, ok. It's gonna get too involed OMEGALUL

OMEGALUL

Rabid

R

RabidOP•11/11/23, 3:05 PM

Most classes have about 12 gear choices, plus gems per gear slot haha

itsmatteomanf

I

itsmatteomanf•11/11/23, 3:05 PM

If the tables were fixed positions, it might be done easily ahah

Rabid

R

RabidOP•11/11/23, 3:22 PM

Yeah hah. Well I was mostly worried about collisions if I was generating a shortened version of it that was only like 10 characters long

Rabid

R

RabidOP•11/11/23, 3:22 PM

But the full MD5 should be fine, going to make that change, thanks for the suggestion! Should help keep my table much smaller

Ɇ₦ĐɆⱤV₳₥₱łⱤɆ

Ɇ

Ɇ₦ĐɆⱤV₳₥₱łⱤɆ•11/12/23, 12:21 AM

Hey, I currently use D1 via the API. Where can I request a limit increase for my key? Starting to get 429s :P

wadefletch

W

wadefletch•11/12/23, 12:57 AM

Is there a slick way in D1 (generated columns, maybe?) to support updatedAt columns? Currently, I'm using the following.

CREATE TABLE IF NOT EXISTS project (
    id                      INTEGER     NOT NULL    PRIMARY KEY,
    name                    TEXT        NOT NULL,
    slug                    TEXT        NOT NULL    UNIQUE,
    ownerId                 TEXT        NOT NULL,
    createdAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    updatedAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP);

CREATE TRIGGER IF NOT EXISTS project_updatedAt
    AFTER UPDATE ON project
    FOR EACH ROW
    BEGIN
        UPDATE project SET updatedAt = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;

CREATE TABLE IF NOT EXISTS project (
    id                      INTEGER     NOT NULL    PRIMARY KEY,
    name                    TEXT        NOT NULL,
    slug                    TEXT        NOT NULL    UNIQUE,
    ownerId                 TEXT        NOT NULL,
    createdAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    updatedAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP);

CREATE TRIGGER IF NOT EXISTS project_updatedAt
    AFTER UPDATE ON project
    FOR EACH ROW
    BEGIN
        UPDATE project SET updatedAt = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;

Ɇ₦ĐɆⱤV₳₥₱łⱤɆ

ɆɆ₦ĐɆⱤV₳₥₱łⱤɆ Hey, I currently use D1 via the API. Where can I request a limit increase for my...

Cyb3r-Jak3

C

Cyb3r-Jak3•11/12/23, 2:14 AM

Only if you are enterprise. The recommended way would be to run an HTTP proxy worker so you don’t have a rate limit

Cyb3r-Jak3

C

Cyb3r-Jak3•11/12/23, 2:15 AM

Something like https://github.com/elithrar/http-api-d1-example

GitHub

GitHub - elithrar/http-api-d1-example: An example HTTP API for Clou...

An example HTTP API for Cloudflare's D1 database: https://developers.cloudflare.com/d1/

GitHub - elithrar/http-api-d1-example: An example HTTP API for Cloudflare's D1 database: ht...

wadefletch

Wwadefletch Is there a slick way in D1 (generated columns, maybe?) to support updatedAt colu...

Isaac McFadyen

I

Isaac McFadyen•11/12/23, 2:35 AM

Generated columns are done at SELECT time not INSERT time, so there's not really a way aside from what you've done, no.

Isaac McFadyen

I

Isaac McFadyen•11/12/23, 2:36 AM

D1 is just SQLite so most things that work with SQLite will work with D1, FYI.

Isaac McFadyen

IIsaac McFadyen Generated columns are done at SELECT time not INSERT time, so there's not really...

Matt Silverlock

M

Matt Silverlock•11/12/23, 12:55 PM

There are two types of generated column: https://developers.cloudflare.com/d1/learning/generated-columns/#types-of-generated-columns

Generated columns · Cloudflare D1 docs

D1 allows you to define generated columns based on the values of one or more other columns, SQL functions, or even extracted JSON values.

Matt Silverlock

MMatt Silverlock There are two types of generated column: https://developers.cloudflare.com/d1/le...

Isaac McFadyen

I

Isaac McFadyen•11/12/23, 1:12 PM

Oh - interesting! TIL, thank you.

jiangok

J

jiangok•11/12/23, 1:57 PM

How do I use d1 with a different env? could anyone help a remix related question? thanks.

jiangok

Jjiangok https://discord.com/channels/595317990191398933/1172999291451736164/117310614809...

Matt Silverlock

M

Matt Silverlock•11/12/23, 2:54 PM

You’re better off responding/creating a new thread, but I’ve linked https://developers.cloudflare.com/d1/examples/d1-and-remix/ for you

Query D1 from Remix · Cloudflare D1 docs

Remix is a full-stack web framework that operates on both client and server. You can query your D1 database(s) easily from Remix with Remix’s data …

Ferry

FFerry While we are wainting for transactions is it possible to nest statement methods ...

GuillaumeLakano

G

GuillaumeLakano•11/12/23, 4:36 PM

db.prepare is not interactive, it's similar to a parser that format correctly your SQL statement.
Fetch your statements from your table, and push them inside an array to db.batch.

Something like:

const { results: myviewStatements } = await db.prepare('select statetement from myview where tb=?1").bind(t1).all()
await db.batch([ db.prepare("create table.."), ...myviewStatements.map(s => db.prepare(s.statement)) ]

GuillaumeLakano

G

GuillaumeLakano•11/12/23, 4:57 PM

I have a request, and it's may be a bug in the pricing calculation.

The goal of « partial indexes » ( https://www.sqlite.org/partialindex.html ) is to attach a WHERE condition to reduce the required writes / storage. So the additional write for the index is not always done.

I've done a test, and this is always counted as an extra written row, even when the WHERE condition is not true.
It's a bug ?

CREATE TABLE IF NOT EXISTS project (
    id                      INTEGER     NOT NULL    PRIMARY KEY,
    name                    TEXT        NOT NULL,
    slug                    TEXT        NOT NULL    UNIQUE,
    ownerId                 TEXT        NOT NULL,
    createdAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    updatedAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP);

CREATE TRIGGER IF NOT EXISTS project_updatedAt
    AFTER UPDATE ON project
    FOR EACH ROW
    BEGIN
        UPDATE project SET updatedAt = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;

CREATE TABLE IF NOT EXISTS project (
    id                      INTEGER     NOT NULL    PRIMARY KEY,
    name                    TEXT        NOT NULL,
    slug                    TEXT        NOT NULL    UNIQUE,
    ownerId                 TEXT        NOT NULL,
    createdAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    updatedAt               TIMESTAMP   NOT NULL    DEFAULT CURRENT_TIMESTAMP);

CREATE TRIGGER IF NOT EXISTS project_updatedAt
    AFTER UPDATE ON project
    FOR EACH ROW
    BEGIN
        UPDATE project SET updatedAt = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;