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

INSERT INTO ... SELECT

INSERT INTO ... SELECT with a bunch of

JOIN

JOIN

JOIN

JOINs

Cconqr ᯅ Are those statements supported though?

M

Matt SilverlockOP•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

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]

Ccaptain question, is it not possible to backup an alpha database and then restore it int...

I

Isaac McFadyen•9/24/23, 6:43 PM

I believe you need to dump it to a SQL file and reupload.

IIsaac McFadyen I believe you need to dump it to a SQL file and reupload.

C

captain•9/24/23, 7:12 PM

success!

C

captain•9/24/23, 7:44 PM

another question...did a dump from an alpha database, created a new database, importanted the dump, success. NOW to the that new database I'm running the 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, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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
);

and getting the following error:

SQL code did not contain a statement. [code: 7500]

This same statement seemed to work on the alpha database, thoughts?

Resolved, I had a comment with a semi colon which is an bug documented here: https://github.com/cloudflare/workers-sdk/issues/3892 whats interesting is this bug only applies to the beta D1 and not the alpha

GitHub

🐛 BUG: d1 execution with comment including semicolon causes SQL cod...

Which Cloudflare product(s) does this pertain to? D1 What version(s) of the tool(s) are you using? 3.6.0 What version of Node are you using? 20.5.1 What operating system are you using? Mac Describe...

MMatt Silverlock Thanks: looking into why it's sticking.

B

benthecarman•9/24/23, 9:42 PM

it's still down, any updates here?

Bbenthecarman it's still down, any updates here?

M

Matt SilverlockOP•9/24/23, 9:43 PM

We’re still chasing down the cause of the bug. I’ll provide an update when we have one.

B

benthecarman•9/24/23, 9:44 PM

Is it possible we can just get the sqlite file? We are just trying to move off of this because it is unusable

Bbenthecarman Is it possible we can just get the sqlite file? We are just trying to move off o...

M

Matt SilverlockOP•9/24/23, 11:17 PM

There isn't the concept of a 'single SQLite file' in D1 (as that wouldn't allow us to be distributed).

Bbenthecarman Is it possible we can just get the sqlite file? We are just trying to move off o...

M

Matt SilverlockOP•9/24/23, 11:18 PM

And I understand the frustration: we don't want these kind of bugs, but this is an alpha/beta.

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

Similar Threads

Similar Threads

Similar Threads