fba9bfa8-37e0-4c2f-a74c-4b466a2f4251

B

benthecarmanOP•9/24/23, 4:42 AM

it's down again

C

conqr ᯅ•9/24/23, 11:05 AM

Is there any way to rollback a transaction (aka batch) if a select clause finds no results? Something like

BEGIN;

-- Check if a record with the specified car_id and owner_id exists in the cars table
DECLARE @car_matches INTEGER;
SET @car_matches = (SELECT COUNT(*) FROM cars WHERE car_id = 'car1' AND owner_id = 'bob');

-- Only insert into the tickets table if we find a match for that car and owner
IF @car_matches > 0 THEN
    -- Insert data into the tickets table
    INSERT INTO tickets (ticket_description, owner_id, car_id)
      VALUES ('Speeding', 'bob', 'car1');
ELSE
    -- The car does not exist, or the owner has changed
    ROLLBACK;
END IF;

COMMIT;

BEGIN;

-- Check if a record with the specified car_id and owner_id exists in the cars table
DECLARE @car_matches INTEGER;
SET @car_matches = (SELECT COUNT(*) FROM cars WHERE car_id = 'car1' AND owner_id = 'bob');

-- Only insert into the tickets table if we find a match for that car and owner
IF @car_matches > 0 THEN
    -- Insert data into the tickets table
    INSERT INTO tickets (ticket_description, owner_id, car_id)
      VALUES ('Speeding', 'bob', 'car1');
ELSE
    -- The car does not exist, or the owner has changed
    ROLLBACK;
END IF;

COMMIT;

BEGIN;

-- Check if a record with the specified car_id and owner_id exists in the cars table
DECLARE @car_matches INTEGER;
SET @car_matches = (SELECT COUNT(*) FROM cars WHERE car_id = 'car1' AND owner_id = 'bob');

-- Only insert into the tickets table if we find a match for that car and owner
IF @car_matches > 0 THEN
    -- Insert data into the tickets table
    INSERT INTO tickets (ticket_description, owner_id, car_id)
      VALUES ('Speeding', 'bob', 'car1');
ELSE
    -- The car does not exist, or the owner has changed
    ROLLBACK;
END IF;

COMMIT;

BEGIN;

-- Check if a record with the specified car_id and owner_id exists in the cars table
DECLARE @car_matches INTEGER;
SET @car_matches = (SELECT COUNT(*) FROM cars WHERE car_id = 'car1' AND owner_id = 'bob');

-- Only insert into the tickets table if we find a match for that car and owner
IF @car_matches > 0 THEN
    -- Insert data into the tickets table
    INSERT INTO tickets (ticket_description, owner_id, car_id)
      VALUES ('Speeding', 'bob', 'car1');
ELSE
    -- The car does not exist, or the owner has changed
    ROLLBACK;
END IF;

COMMIT;

C

conqr ᯅ•9/24/23, 11:05 AM

I know you can batch queries, but I don't believe there is a way to actually rollback the entire transaction outside of a SQL error

Cconqr ᯅ I know you can batch queries, but I don't believe there is a way to actually rol...

I

Isaac McFadyen•9/24/23, 11:05 AM

No. D1 doesn't support manual transactions, only the automatic transactions that occur when you send batch operations.

I

Isaac McFadyen•9/24/23, 11:06 AM

So no ROLLBACK aside from throwing an error inside a batch operation.

IIsaac McFadyen So no ROLLBACK aside from throwing an error inside a batch operation.

C

conqr ᯅ•9/24/23, 11:08 AM

Is there any way to use a conditional and then throw an error based off of that, or is that not supported

Bbenthecarman fba9bfa8-37e0-4c2f-a74c-4b466a2f4251

M

Matt Silverlock•9/24/23, 11:20 AM

Thanks: looking into why it's sticking.

Cconqr ᯅ Is there any way to use a conditional and then throw an error based off of that,...

M

Matt Silverlock•9/24/23, 11:21 AM

https://www.sqlite.org/lang_conflict.html can be very useful here

MMatt Silverlock https://www.sqlite.org/lang_conflict.html can be very useful here

C

conqr ᯅ•9/24/23, 11:38 AM

Are those statements supported though?

C

conqr ᯅ•9/24/23, 11:39 AM

I've resorted to using a combination

INSERT INTO ... SELECT

INSERT INTO ... SELECT

with a bunch of

JOIN

JOIN

s

Cconqr ᯅ Are those statements supported though?

M

Matt Silverlock•9/24/23, 11:40 AM

Yes. INSERT / UPSERT … ON CONFLICT work.

C

conqr ᯅ•9/24/23, 1:40 PM

Is the LIMIT modifier not allowed on DELETE commands?

DELETE FROM offers WHERE price=5 LIMIT 1

near "LIMIT": syntax error

K

kian•9/24/23, 1:43 PM

correct

C

conqr ᯅ•9/24/23, 1:43 PM

Seems to not work on UPDATE commands too

UPDATE users SET balance = balance + 10 WHERE id = 'test' LIMIT 1

near "LIMIT": syntax error

K

kian•9/24/23, 1:43 PM

It's an optional feature in SQLite and isn't enabled in D1.

C

conqr ᯅ•9/24/23, 1:44 PM

It works for other commands though

K

kian•9/24/23, 1:44 PM

The feature is about UPDATE and DELETE statements.

K

kian•9/24/23, 1:44 PM

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

https://www.sqlite.org/compile.html

C

conqr ᯅ•9/24/23, 1:46 PM

C

captain•9/24/23, 3:33 PM

I'm noticing in the dasboard for d1 its only text, integer, real and blob when setting up a table. Does tha mean if I run something like:

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

That the VARCHAR(10) is really just text and I don't need to state VARCHAR(10) because there will be no performance or size settings by setting that limitation behind the scenes?

Original message was deleted

C

captain•9/24/23, 3:42 PM

got it, so so I don't need to waste time worrying about varchar(10) i can set it to text. Follow up question, an epoch timestamp is bigger than an integer, should sometime saving that info be saved as text or what do you recommend for setting timestamps on entries?

Original message was deleted

C

captain•9/24/23, 3:45 PM

for instance in this thread I see somebody setting: time_stamp timestamp NOT NULL DEFAULT,

C

captain•9/24/23, 3:45 PM

how is that converted?

Original message was deleted

C

captain•9/24/23, 3:47 PM

for instance in my scenario, i have different customer ids, and I'm tracking specific log actions via their id and the time it happened. then someday i might want to go back and query the last week of action organized in newest to old, or go back to 2 months ago for only a range of a month. What would be the best way of storing that timestamp?

C

captain•9/24/23, 3:48 PM

currently i'm saving the epoch time in my key of my KV entry

C

captain•9/24/23, 3:48 PM

but listing keys and doing that sort isn't really possible

C

captain•9/24/23, 3:49 PM

got it

C

captain•9/24/23, 3:53 PM

so an epoch time generated via worker would be 1695071510081, but that is bigger than an INT, and BIGINT would probably get converted to REAL, so better to just set that value to REAL, correct?

C

captain•9/24/23, 3:53 PM

but behind the scenes does any of this matter, and could it just be stored as TEXT?

C

captain•9/24/23, 3:55 PM

or nevermind, best to do it as an integer

C

captain•9/24/23, 3:55 PM

okay well thank LEO, appreciate your time

Original message was deleted

C

captain•9/24/23, 4:01 PM

thanks Leo, appreciate your time

C

captain•9/24/23, 4:36 PM

Hello, wondering if anybody can see why this wont run?
sudo wrangler d1 execute mydevdatabase --file schemas/schema.sql

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

however getting this error:

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

I

Isaac McFadyen•9/24/23, 4:38 PM

You forgot a default on time_stamp

I

Isaac McFadyen•9/24/23, 4:39 PM

time_stamp INTEGER NOT NULL DEFAULT,

There's no value for DEFAULT. It should be

time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,

I

Isaac McFadyen•9/24/23, 4:39 PM

See section 3.2 here: https://www.sqlite.org/lang_createtable.html

IIsaac McFadyen You forgot a default on time_stamp

C

captain•9/24/23, 4:51 PM

ahh, thanks for that, it worked! I actually removed the default so that I can manually set it via epoch time stamp generated via my worker. I am reading the worker time could slightly differ than the D1 time, so thinking not setting it via the D1 is a better idea, your thoughts?

Ccaptain ahh, thanks for that, it worked! I actually removed the default so that I can ma...

I

Isaac McFadyen•9/24/23, 4:52 PM

Both D1 time and Worker time aren't exact, because of security concerns. But they're close.

I

Isaac McFadyen•9/24/23, 4:53 PM

Usually milliseconds difference between the observed time and the real time, so unless you need that precision you should be fine to set it wherever.

IIsaac McFadyen Usually milliseconds difference between the observed time and the real time, so ...

C

captain•9/24/23, 4:55 PM

my use case is tracking webhooks from stripe and then showing the non paid messages to the user and telling them to get their act together! So wanting the timestamp webhook sending to act as the time stamp that i can then sort by

Ccaptain my use case is tracking webhooks from stripe and then showing the non paid messa...

I

Isaac McFadyen•9/24/23, 4:55 PM

Ah, OK. Does Stripe provide a timestamp within the webhook message?

I

Isaac McFadyen•9/24/23, 4:55 PM

If so you could use that.

IIsaac McFadyen Ah, OK. Does Stripe provide a timestamp within the webhook message?

C

captain•9/24/23, 4:57 PM

true, but either way best not to have D1 set it i think

C

captain•9/24/23, 4:58 PM

anyways thanks for pointing out that error, D1, very exciting stuff rather than using all this crazy logic i have written around KV storage

Ccaptain true, but either way best not to have D1 set it i think

I

Isaac McFadyen•9/24/23, 4:58 PM

Yeah - D1 setting it would occur on insert, not on message received.

Ccaptain anyways thanks for pointing out that error, D1, very exciting stuff rather than ...

I

Isaac McFadyen•9/24/23, 4:58 PM

No worries, happy to help

C

captain•9/24/23, 5:14 PM

more D1 questions! I created a D1 database many many months ago, but now reading this: https://blog.cloudflare.com/d1-turning-it-up-to-11/ it looks like things have changed and running the command wrangler d1 info <DB_NAME> I can see my db is using version alpha. The question, is there a single command to move this db to the newer version? Or is the proper move to simply manually recreate the DB and update the ID's in the TOML file

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

C

captain•9/24/23, 5:24 PM

@Matt is "INSERTZ INFO" found on https://developers.cloudflare.com/d1/learning/debug-d1/ a typo? Or is this something specific for D1?

Debug D1 · Cloudflare D1 docs

D1 allows you to capture exceptions and log errors returned when querying a database. To debug D1, you will use the same tools available when …

Ccaptain @Matt is "INSERTZ INFO" found on https://developers.cloudflare.com/d1/learning/d...

I

Isaac McFadyen•9/24/23, 5:28 PM

That's an intentional typo, yup.

C

captain•9/24/23, 6:25 PM

question, is it not possible to backup an alpha database and then restore it into a beta database. When I attempt the basic method of restoring in that situation i get:
Internal error: failed to restore backup: Experimental DBs do not support backups yet:
74244d9f-afa8-4ca2-ab7f-8067f4505c97 (v3) [code: 7500]

fba9bfa8-37e0-4c2f-a74c-4b466a2f4251

Similar Threads