Re: [sqlite] primary key in another column
You are right, Igor. Clear case of XY problem. I will remove trigger. Roman Sent from my T-Mobile 4G LTE Device Original message From: Igor Tandetnik <i...@tandetnik.org> Date: 1/26/18 9:03 PM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 6:20 PM, Roman Fleysher wrote: > I think I effectively did as you suggested using triggers. I insert NULL into > the ID column to create a row. This triggers the trigger to run update on the > table to populate the columns based on the just created ID. Is this what you > suggested? Roughly, though running a single statement at the end seems simpler, and likely goes faster, than setting up a trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 6:20 PM, Roman Fleysher wrote: I think I effectively did as you suggested using triggers. I insert NULL into the ID column to create a row. This triggers the trigger to run update on the table to populate the columns based on the just created ID. Is this what you suggested? Roughly, though running a single statement at the end seems simpler, and likely goes faster, than setting up a trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
yes, I can use a view. forEachRow also records what failed. Updating a view requires a trigger, but I can compose one with the view. Thank you for suggestion! Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Richard Damon [rich...@damon-family.org] Sent: Friday, January 26, 2018 6:50 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column Couldn't you have it access a view which adds the columns by calculation rather than the raw table? (and if you have some tables that don't need such a view, create a simple pass through view). On 1/26/18 6:30 PM, Roman Fleysher wrote: > No, I can not compute inside forEachRow. ForEachRow is now universal, can be > applied to any table. If I modify SELECT inside it to fit specific purpose, > forEachRow will use universality. > > Roman > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf > of Richard Damon [rich...@damon-family.org] > Sent: Friday, January 26, 2018 6:26 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] primary key in another column > > One question I have, couldn't you just omit the fileName column from the > able, and compute it in the select query that is getting the data? > > On 1/26/18 6:03 PM, Roman Fleysher wrote: >> My implementation of "for Each row" requires all columns to be populated. >> It is a dumb thing: >> >> forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns >> >> The files are images. Example: >> >> forEachRow addImages outputColumn column1 column2 >> >> ForEachRow will loop over the rows (in parallel batches if it can) and apply >> the command given to it with its arguments. Image processing is then a >> sequence of these "forEach" commands. >> >> >> Roman >> >> On 1/26/2018 5:47 PM, Roman Fleysher wrote: >>> I will use this table as a manager. There will be multiple columns holding >>> various file names. The names can be random, but I want humans to be able >>> to easily inspect. After table is filled, an operation "for each row" will >>> get files in some columns and produce files in other columns. This is done >>> outside of SQLite. "For each row" will process several rows in parallel >>> because they are independent. Some operations might fail and will be >>> recored in the proper columns. After all the work is done, the manager >>> table is discarded. >> I'm still not sure I understand, but: while you are building out this >> manager table, can't you leave fileName column blank, and then right before >> processing, run UPDATE A SET fileName='prefix_'||ID; on it? >> -- >> Igor Tandetnik > -- > Richard Damon > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
Couldn't you have it access a view which adds the columns by calculation rather than the raw table? (and if you have some tables that don't need such a view, create a simple pass through view). On 1/26/18 6:30 PM, Roman Fleysher wrote: No, I can not compute inside forEachRow. ForEachRow is now universal, can be applied to any table. If I modify SELECT inside it to fit specific purpose, forEachRow will use universality. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Richard Damon [rich...@damon-family.org] Sent: Friday, January 26, 2018 6:26 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column One question I have, couldn't you just omit the fileName column from the able, and compute it in the select query that is getting the data? On 1/26/18 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns The files are images. Example: forEachRow addImages outputColumn column1 column2 ForEachRow will loop over the rows (in parallel batches if it can) and apply the command given to it with its arguments. Image processing is then a sequence of these "forEach" commands. Roman On 1/26/2018 5:47 PM, Roman Fleysher wrote: I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns and produce files in other columns. This is done outside of SQLite. "For each row" will process several rows in parallel because they are independent. Some operations might fail and will be recored in the proper columns. After all the work is done, the manager table is discarded. I'm still not sure I understand, but: while you are building out this manager table, can't you leave fileName column blank, and then right before processing, run UPDATE A SET fileName='prefix_'||ID; on it? -- Igor Tandetnik -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
No, I can not compute inside forEachRow. ForEachRow is now universal, can be applied to any table. If I modify SELECT inside it to fit specific purpose, forEachRow will use universality. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Richard Damon [rich...@damon-family.org] Sent: Friday, January 26, 2018 6:26 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column One question I have, couldn't you just omit the fileName column from the able, and compute it in the select query that is getting the data? On 1/26/18 6:03 PM, Roman Fleysher wrote: > My implementation of "for Each row" requires all columns to be populated. It > is a dumb thing: > > forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns > > The files are images. Example: > > forEachRow addImages outputColumn column1 column2 > > ForEachRow will loop over the rows (in parallel batches if it can) and apply > the command given to it with its arguments. Image processing is then a > sequence of these "forEach" commands. > > > Roman > > On 1/26/2018 5:47 PM, Roman Fleysher wrote: >> I will use this table as a manager. There will be multiple columns holding >> various file names. The names can be random, but I want humans to be able to >> easily inspect. After table is filled, an operation "for each row" will get >> files in some columns and produce files in other columns. This is done >> outside of SQLite. "For each row" will process several rows in parallel >> because they are independent. Some operations might fail and will be recored >> in the proper columns. After all the work is done, the manager table is >> discarded. > I'm still not sure I understand, but: while you are building out this manager > table, can't you leave fileName column blank, and then right before > processing, run UPDATE A SET fileName='prefix_'||ID; on it? > -- > Igor Tandetnik -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
One question I have, couldn't you just omit the fileName column from the able, and compute it in the select query that is getting the data? On 1/26/18 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns The files are images. Example: forEachRow addImages outputColumn column1 column2 ForEachRow will loop over the rows (in parallel batches if it can) and apply the command given to it with its arguments. Image processing is then a sequence of these "forEach" commands. Roman On 1/26/2018 5:47 PM, Roman Fleysher wrote: I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns and produce files in other columns. This is done outside of SQLite. "For each row" will process several rows in parallel because they are independent. Some operations might fail and will be recored in the proper columns. After all the work is done, the manager table is discarded. I'm still not sure I understand, but: while you are building out this manager table, can't you leave fileName column blank, and then right before processing, run UPDATE A SET fileName='prefix_'||ID; on it? -- Igor Tandetnik -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
I think I effectively did as you suggested using triggers. I insert NULL into the ID column to create a row. This triggers the trigger to run update on the table to populate the columns based on the just created ID. Is this what you suggested? Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 6:10 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 6:03 PM, Roman Fleysher wrote: > My implementation of "for Each row" requires all columns to be populated. It > is a dumb thing: You said: After table is filled, an operation "for each row" will... I suggest running this UPDATE statement at the end of "table is filled", before "an operation will..." part. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: You said: After table is filled, an operation "for each row" will... I suggest running this UPDATE statement at the end of "table is filled", before "an operation will..." part. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns The files are images. Example: forEachRow addImages outputColumn column1 column2 ForEachRow will loop over the rows (in parallel batches if it can) and apply the command given to it with its arguments. Image processing is then a sequence of these "forEach" commands. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 5:56 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 5:47 PM, Roman Fleysher wrote: > I will use this table as a manager. There will be multiple columns holding > various file names. The names can be random, but I want humans to be able to > easily inspect. After table is filled, an operation "for each row" will get > files in some columns and produce files in other columns. This is done > outside of SQLite. "For each row" will process several rows in parallel > because they are independent. Some operations might fail and will be recored > in the proper columns. After all the work is done, the manager table is > discarded. I'm still not sure I understand, but: while you are building out this manager table, can't you leave fileName column blank, and then right before processing, run UPDATE A SET fileName='prefix_'||ID; on it? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 5:47 PM, Roman Fleysher wrote: I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns and produce files in other columns. This is done outside of SQLite. "For each row" will process several rows in parallel because they are independent. Some operations might fail and will be recored in the proper columns. After all the work is done, the manager table is discarded. I'm still not sure I understand, but: while you are building out this manager table, can't you leave fileName column blank, and then right before processing, run UPDATE A SET fileName='prefix_'||ID; on it? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
Igor, you are absolutely right. But I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns and produce files in other columns. This is done outside of SQLite. "For each row" will process several rows in parallel because they are independent. Some operations might fail and will be recored in the proper columns. After all the work is done, the manager table is discarded. "For each row" is equivalent to SELECT, but it operates on the files themselves. This can be implemented within SQLIte by loading extension. I investigated this route (and even asked questions on this list) and eventually concluded that it is better to do outside because of the way parallel execution is done (sometimes sent to a compute cluster grid engine for queueing.) This makes no sense from the database point of view: No reason to hold redundant data with such a simple algorithm to generate it. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 5:33 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 4:43 PM, Roman Fleysher wrote: > I would like to use primary key as a way to create unique column entry: > > CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL) > > such that file name is always prefix followed by the ID for the content to be: > > ID fileName > > 1 prefix_1 > 2 prefix_2 Why do you want to store redundant data? What's the actual problem this is supposed to help you solve? As stated, this looks like an XY problem ( http://xyproblem.info/ ) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 4:43 PM, Roman Fleysher wrote: I would like to use primary key as a way to create unique column entry: CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL) such that file name is always prefix followed by the ID for the content to be: ID fileName 1 prefix_1 2 prefix_2 Why do you want to store redundant data? What's the actual problem this is supposed to help you solve? As stated, this looks like an XY problem ( http://xyproblem.info/ ) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
Solved with trigger, but I can not use NOT NULL for the fileName column: CREATE TRIGGER AAA AFTER INSERT ON A BEGIN UPDATE A SET fileName = 'prefix'||NEW.id WHERE id=NEW.id; END; INSERT INTO A (id) VALUES (NULL); INSERT INTO A (id) VALUES (NULL); INSERT INTO A (id) VALUES (NULL); ... Is that a right solution? Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleys...@einstein.yu.edu] Sent: Friday, January 26, 2018 4:43 PM To: General Discussion of SQLite Database Subject: [sqlite] primary key in another column Dear SQLiters, I would like to use primary key as a way to create unique column entry: CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL) such that file name is always prefix followed by the ID for the content to be: ID fileName 1 prefix_1 2 prefix_2 That is when I insert a row into the table, id is already auto generated by SQLite. I want the filename to be auto generated too. This idea looks strange to me because then I do not have to insert anything, everything will be auto filled: INSERT INTO A; INSERT INTO A; will insert first two rows. Roman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
