[sqlite] Best way to handle time slicing of SQL data.
Hi, I'm wanting to store data in a way such that I can choose a time in the past and view records as they were at that dateTime. Therefore (in my mind), all updates are really inserts with a timestamp. Ids are suddenly no longer primary keys, as many records can have the same id, but a new entry must be given a unique id (otherwise it will look like an update for an existing entry). Here's a very simplified view of what I'm currently doing: CREATE TABLE data ( UID INTEGER PRIMARY KEY AUTOINCREMENT, Id INTEGER NOT NULL DEFAULT 0, -- shared by updates of the same data entry Data TEXT, User INT, -- who made this update At INT, -- when the update was made Remove INT NOT NULL DEFAULT 0 -- set to 1 if data entry has been deleted ); My initial questions are: 1) Do I need UID? I'm currently using it to ensure a unique Id for when the user is creating a new entry. This however means two operations, an Insert, then an Update to set the record's Id to match its UID. It also has the overhead of autoincrement, ensuring that UID is never ever reused and only goes upwards (I'm currently using MAX(UID) to get the latest record <= the time I'm interested in. I guess there's no extra storage as rowid would exist anyway. 2) Can I auto fill Id to UID on insert instead of having to do two operations? 3) Am I on track or is there a better way to approach this problem? Thanks in advance for any feedback. -- Richard Warburton
[sqlite] Best way to handle time slicing of SQL data.
On 2 Jun 2015, at 12:34pm, Richard Warburton wrote: > I'm wanting to store data in a way such that I can choose a time in the > past and view records as they were at that dateTime. Therefore (in my > mind), all updates are really inserts with a timestamp. Ids are suddenly > no longer primary keys, as many records can have the same id, but a new > entry must be given a unique id (otherwise it will look like an update for > an existing entry). This is an area I used to do a lot of work in, and there are some complicated security implications I'm not going to try to explore in one post. If you have a specific question I'll try to answer it, but here are my general recommendations: Keep your existing tables as they are, modify them as you have been, and create a new table or tables for the historical information. That way, parts of your code which don't care about historical information can continue to look at the 'normal' tables as they did previously, and you have the same uncomplicated 'id' system that would be used in a normal database. When you have commands which modify the logged table, use an AFTER TRIGGER to make a copy of the new row into this new table, with a timestamp on it. The primary key of this new table becomes (truerowid, timestamp). Of course, DELETE FROM doesn't leave a 'new row', so you will invent a way to record DELETEs. As an alternative, rather than logging copies of the actual data in this new table, you can keep copies of the commands used to change the table. If you do this, then you can create a copy of your database at any time merely by replaying all the commands executed up to that time. One advantage of this is that all your reconstruction information can be stored in one table. Which of these you do depends on why you're keeping the historical data: whether you're trying to keep an 'changes list' for audit/security purposes or whether you expect to actually be working on the data in the same way as you work on current data. Simon.
[sqlite] sqlite3 compound index usage for LIKE and GROUP BY
On 1 Jun 2015, at 7:37pm, Amol Kandurwar wrote: > SELECT foo, boo FROM mytable WHERE foo LIKE 'hi%' GROUP BY boo; Try the following: CREATE INDEX m_fb ON mytable (foo, boo); CREATE INDEX m_bf ON mytable (boo, foo); ANALYZE; SELECT foo, boo FROM mytable WHERE foo BETWEEN 'hi' AND 'hizzz' GROUP BY boo; See what happens. Use the results of that to work to wards a more convenient solution. Simon.
[sqlite] How to get length of all columns in a table
All, I just want to make sure everyone understands that I know how to get the length of a column and that I was only asking for a wildcard technique to get the lengths of all columns in a table without having to list all the columns (e.g. SELECT Length(*) FROM myTable). Obviously that doesn't work, but that's the kind of thing I was looking for. I already wrote a little code snippet to automatically generate a "SELECT Length(col_1), Length(col_2...)" string from the table def, and I'm happy with that solution. I am using this on BLOBs and Length() is returning the number of bytes, which is exactly what I need. All is well. Thanks for all your input, -- Bill Drago Senior Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp > Sent: Tuesday, June 02, 2015 9:15 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] How to get length of all columns in a table > > On 6/2/15, Igor Tandetnik wrote: > > On 6/2/2015 2:28 AM, Hick Gunter wrote: > >> Sqlite3_column_bytes will convert numeric values to strings and > >> return the length of that "string representation" (excluding the > >> terminating \0), not the byte size required to store the numeric > value itself. > > > > So will length() SQL function, I'm pretty sure. > > Yes, both length() and sqlite3_column_bytes() will convert numbers to > strings before computing the length. However, they are not equivalent. > > The length() SQL function returns the number of *characters*. The > sqlite3_column_bytes() C function returns the number of *bytes*. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] How to get length of all columns in a table
True. Both are documented to convert their argument to string (unless it is already a string or blob). -Urspr?ngliche Nachricht- Von: Igor Tandetnik [mailto:igor at tandetnik.org] Gesendet: Dienstag, 02. Juni 2015 14:21 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] How to get length of all columns in a table On 6/2/2015 2:28 AM, Hick Gunter wrote: > Sqlite3_column_bytes will convert numeric values to strings and return the > length of that "string representation" (excluding the terminating \0), not > the byte size required to store the numeric value itself. So will length() SQL function, I'm pretty sure. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Best way to handle time slicing of SQL data.
What is the reason for wanting the id of a record to be fixed at the unique record number of the original insertion? Do you need to access historical data regularly or only for specific inquiries? For rarely required historical data, you could use a "history table" to hold historic copies of records with fields valid_from and valid_to. Moving record contents to the history table should be doable via triggers, with the additional advantage that the rowid never needs to change. For regular access to historic data, you would need to include valid_from and valid_to fields. These fields need to be present in every index and queried in every select. -Urspr?ngliche Nachricht- Von: Richard Warburton [mailto:richard at skagerraksoftware.com] Gesendet: Dienstag, 02. Juni 2015 13:34 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Best way to handle time slicing of SQL data. Hi, I'm wanting to store data in a way such that I can choose a time in the past and view records as they were at that dateTime. Therefore (in my mind), all updates are really inserts with a timestamp. Ids are suddenly no longer primary keys, as many records can have the same id, but a new entry must be given a unique id (otherwise it will look like an update for an existing entry). Here's a very simplified view of what I'm currently doing: CREATE TABLE data ( UID INTEGER PRIMARY KEY AUTOINCREMENT, Id INTEGER NOT NULL DEFAULT 0, -- shared by updates of the same data entry Data TEXT, User INT, -- who made this update At INT, -- when the update was made Remove INT NOT NULL DEFAULT 0 -- set to 1 if data entry has been deleted ); My initial questions are: 1) Do I need UID? I'm currently using it to ensure a unique Id for when the user is creating a new entry. This however means two operations, an Insert, then an Update to set the record's Id to match its UID. It also has the overhead of autoincrement, ensuring that UID is never ever reused and only goes upwards (I'm currently using MAX(UID) to get the latest record <= the time I'm interested in. I guess there's no extra storage as rowid would exist anyway. 2) Can I auto fill Id to UID on insert instead of having to do two operations? 3) Am I on track or is there a better way to approach this problem? Thanks in advance for any feedback. -- Richard Warburton ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Best way to handle time slicing of SQL data.
On Tue, Jun 2, 2015 at 7:34 AM, Richard Warburton < richard at skagerraksoftware.com> wrote: > 1) Do I need UID? I'm currently using it to ensure a unique Id for when > the user is creating a new entry. This however means two operations, an > Insert, then an Update to set the record's Id to match its UID. It also has > the overhead of autoincrement, ensuring that UID is never ever reused and > only goes upwards (I'm currently using MAX(UID) to get the latest record > <= the time I'm interested in. I guess there's no extra storage as rowid > would exist anyway. > This is basic Parent/Child relationship, or, One-To-Many relationships. Your ID field can have many references to a parent table, so it is absolutely 100% valid to have multiple IDs of the same value in one field, but as I mention later, rather confusing to have multiple fields treated as "primary information". To answer your question for this particular example, no, you might not need UID, but that'll depend on what you plan on doing with the information you're inserting into this table. If you plan on showing this record to the user somehow, and they have the option to interact with the data (Double-click to show more information on what was changed for this parent record) then you'll need to provide your user interface with that UID. In Delphi/Pascal/Lazarus, every item within a list box or dropdown/combo box contains an "object" pointer, which is just an integer. I typically use this "object" pointer to refer to whatever ID I pulled from the database query when I'm populating the list. If you're just displaying the data out as a dump of text, then no, UID wouldn't be needed since you don't need to go and reference that data back ever. An example query to get the oldest record for a particular client would be select * from data where ID=? order by At desc limit 1 In a multi-threaded/multi-client environment, I'd NEVER query against the maximum identifier as that can change between the time you make the query when when you actually want to do something with the result. I want to find the maximum value of relevant data I need, not something used internally by the database to maintain referential integrity. It "might" BE the same result, but, at a code review, it doesn't LOOK like it is doing the right thing. Basically, your query doesn't tell the developer WHY you're even making this query. >From what I understand of your reasoning, you're "kind of" on the right track. You're looking to store historical record of changes on data for any relevant child information to some sort of parent entity. Say for instance a change of address for a company or person, or recording the change of the color of a car. What you're missing out on is that your ID field should already be known by the application to whatever the parent record ID is, or in other words, your application should already know you're changing the address of a particular person. In this particular table, you can probably keep the UID, but for all intents and purposes for this example, if you ever make an update anything stored in this table, your two relevant unique identifying keys would probably be the ID linking to the parent record and the "At" field. Unless the "At" value is being changed/added multiple times in a second. > > 2) Can I auto fill Id to UID on insert instead of having to do two > operations? > In my mind, having two "primary identifiers that mean two different things, for one piece of information" indicates a bit more normalization needs to happen. Using cars as an example, it is confusing to say that that a vehicle VIN contains information about the color of the car. 'Primary' information should never be considered as something that can change later, and, paint shops would be out of business in this particular scenario. A single vehicle has a single VIN, which has identifiable information that CAN'T change at time of manufacturing, such as who made it, what location, body trim, etc. Sure, you can take every component from a Camaro SS and put it into the body of a Camaro LT, but, as far as the VIN for that LT is concerned, it will be just an LT with SS parts. The other thing is that, as I mentioned, you already should have ID available when you're actually updating the record. You shouldn't need to have the application insert data, call the data back only to have to update the record later. In order to keep a paper trail on what has to be changed, you obviously already know what is to be, or has changed, or what new values are being entered into this table. Even if you're adding a brand new vehicle to the database, you need to add the VIN to a parent table first and either get the LastID value after the insert, or retain the VIN, then you add a new record to your "data" table referencing the ID field to the VIN value. If you're adding a new change record to the vehicle (Brown paint to chrome paint anyone?) you already know the subjects ID
[sqlite] How to get length of all columns in a table
On 6/2/15, Igor Tandetnik wrote: > On 6/2/2015 2:28 AM, Hick Gunter wrote: >> Sqlite3_column_bytes will convert numeric values to strings and return the >> length of that "string representation" (excluding the terminating \0), not >> the byte size required to store the numeric value itself. > > So will length() SQL function, I'm pretty sure. Yes, both length() and sqlite3_column_bytes() will convert numbers to strings before computing the length. However, they are not equivalent. The length() SQL function returns the number of *characters*. The sqlite3_column_bytes() C function returns the number of *bytes*. -- D. Richard Hipp drh at sqlite.org
[sqlite] Best way to handle time slicing of SQL data.
On 6/2/2015 7:34 AM, Richard Warburton wrote: > 2) Can I auto fill Id to UID on insert instead of having to do two > operations? Yes, with an AFTER INSERT trigger -- Igor Tandetnik
[sqlite] How to get length of all columns in a table
On 6/2/2015 2:28 AM, Hick Gunter wrote: > Sqlite3_column_bytes will convert numeric values to strings and return the > length of that "string representation" (excluding the terminating \0), not > the byte size required to store the numeric value itself. So will length() SQL function, I'm pretty sure. -- Igor Tandetnik
[sqlite] Best way to handle time slicing of SQL data.
On Tue, Jun 2, 2015 at 4:34 AM, Richard Warburton < richard at skagerraksoftware.com> wrote: > Hi, > > I'm wanting to store data in a way such that I can choose a time in the > past and view records as they were at that dateTime. Therefore (in my > mind), all updates are really inserts with a timestamp. Ids are suddenly > no longer primary keys, as many records can have the same id, but a new > entry must be given a unique id (otherwise it will look like an update for > an existing entry). > > Here's a very simplified view of what I'm currently doing: > CREATE TABLE data ( > UID INTEGER PRIMARY KEY AUTOINCREMENT, > Id INTEGER NOT NULL DEFAULT 0, -- shared by updates of the same data entry > > Data TEXT, > User INT, -- who made this update > At INT, -- when the update was made > Remove INT NOT NULL DEFAULT 0 -- set to 1 if data entry has been deleted > ); > > My initial questions are: > 1) Do I need UID? I'm currently using it to ensure a unique Id for when > the user is creating a new entry. This however means two operations, an > Insert, then an Update to set the record's Id to match its UID. It also has > the overhead of autoincrement, ensuring that UID is never ever reused and > only goes upwards (I'm currently using MAX(UID) to get the latest record > <= the time I'm interested in. I guess there's no extra storage as rowid > would exist anyway. > if Id == UID always; the question is really, do you need Id? > 2) Can I auto fill Id to UID on insert instead of having to do two > operations? > 3) Am I on track or is there a better way to approach this problem? > > Using a GUID key can solve it also... but again if Id is always the same a UID do you need Id? Thanks in advance for any feedback. > > -- > Richard Warburton > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] How to get length of all columns in a table
Sqlite3_column_bytes will convert numeric values to strings and return the length of that "string representation" (excluding the terminating \0), not the byte size required to store the numeric value itself. -Urspr?ngliche Nachricht- Von: J Decker [mailto:d3ck0r at gmail.com] Gesendet: Montag, 01. Juni 2015 20:48 An: General Discussion of SQLite Database Betreff: Re: [sqlite] How to get length of all columns in a table you get the length in the result set? sqlite3_column_bytes( statement, idx ); On Mon, Jun 1, 2015 at 10:28 AM, Drago, William @ CSG - NARDA-MITEQ < William.Drago at l-3com.com> wrote: > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > > users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > > Sent: Monday, June 01, 2015 12:59 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] How to get length of all columns in a table > > > > > > On 1 Jun 2015, at 4:45pm, Drago, William @ CSG - NARDA-MITEQ > > wrote: > > > > Just to confirm, the following is the only practical way to get the > > length of each column in a table: > > > > > > Select Length(col_1) > > > ,Length(col_2) > > > ,Length(col_3) > > > . > > > . > > > . > > > ,Length(col_n) > > > > Martin's other answer will probably give you the answer faster: > > > > SELECT length(col_1||col2||col_3 ... col_n) FROM MyTable > > That returns the sum of the column lengths. I was looking for the > length of each individual column. > > Thanks anyway, > > -Bill > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and > any attachments are solely for the use of the addressee and may > contain information that is privileged or confidential. Any > disclosure, use or distribution of the information contained herein is > prohibited. In the event this e-mail contains technical data within > the definition of the International Traffic in Arms Regulations or > Export Administration Regulations, it is subject to the export control > laws of the U.S.Government. The recipient should check this e-mail and > any attachments for the presence of viruses as L-3 does not accept any > liability associated with the transmission of this e-mail. If you have > received this communication in error, please notify the sender by > reply e-mail and immediately delete this message and any attachments. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
