primary key in another column

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

primary key in another column

Roman Fleysher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 [[hidden email]] on behalf of Roman Fleysher [[hidden email]]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Igor Tandetnik-2
In reply to this post by Roman Fleysher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 [[hidden email]] on behalf of Igor Tandetnik [[hidden email]]
Sent: Friday, January 26, 2018 5:33 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 [[hidden email]] on behalf of Igor Tandetnik [[hidden email]]
Sent: Friday, January 26, 2018 5:56 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 [[hidden email]] on behalf of Igor Tandetnik [[hidden email]]
Sent: Friday, January 26, 2018 6:10 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Richard Damon
In reply to this post by Roman Fleysher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

J Decker
or add a user function 'filename' which does the work and select
filename(ID)


On Fri, Jan 26, 2018 at 3:26 PM, Richard Damon <[hidden email]>
wrote:

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
In reply to this post by Richard Damon
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 [[hidden email]] on behalf of Richard Damon [[hidden email]]
Sent: Friday, January 26, 2018 6:26 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Richard Damon
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 [[hidden email]] on behalf of Richard Damon [[hidden email]]
> Sent: Friday, January 26, 2018 6:26 PM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 [[hidden email]] on behalf of Richard Damon [[hidden email]]
Sent: Friday, January 26, 2018 6:50 PM
To: [hidden email]
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 [[hidden email]] on behalf of Richard Damon [[hidden email]]
> Sent: Friday, January 26, 2018 6:26 PM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Igor Tandetnik-2
In reply to this post by Roman Fleysher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: primary key in another column

Roman Fleysher
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 <[hidden email]>
Date: 1/26/18 9:03 PM (GMT-05:00)
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users