Re: [sqlite] How to manage Table inside Table scenarios
Guillaume Saumure wrote: I have to create a small program to track changes done on multiple machines. The data structure look like : Structure Notice PrimaryKeyID.l Type.l Date.l ExecutedBy.s RequestedBy.s Description.s EndStructure Structure Machine PrimaryKeyID.l Name.s Location.s List Notices.Notice() EndStructure To make a long story short, I have a Linked list nested inside Linked list but I really don't know how to manage a scenarios like this using Database recording system. Any suggestions ? If I understand your "Linked list" and "nested" terminology as you intend, it could be recast in data modeling terms this way: 1. There is a set of Machine objects. 2. With each Machine object there is an associated set, possibly empty, of Notice objects. The way to represent this in a database is to have a foreign key in the table representing Notice objects, where its value for each row refers to the Machine object, by its ID, with which it is associated. So, your Notice table definition would have a column definition such as: MachineID as integer references Machines(ID) Your List structure has ordering in addition to defining sets. If you wish to retain the order of the Notice object in the database, there will need to be a column in the Notices table giving its sequence number within the set which can be used to recreate the ordering in a query. Regards, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple outstanding sqlite3_stmt objects
On 19 Dec 2012, at 1:21am, Larry Brasfield wrote: > Is there any reason not to do this? Don't do it if one statement is a SELECT and another statement interferes with the normal way that _step() would step through the results. Although the API will not immediately return an error when you do this you can get an error later on (but you will not be able to make SQLite corrupt the database this way*). Having multiple prepared statements which make changes is not a problem. Simon. * unless there's a bug we don't know about ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to manage Table inside Table scenarios
If you're talking about multiple Notices for each Machine, then your schema would be something like this: create table Machine ( ID integer primary key not null , Name text , Location text ) ; create table Notice ( ID integer primary key not null , Machine integer not null references Machine(ID) on delete cascade on update cascade , Type integer , Date integer , ExecutedBy text , RequestedBy text , Description text ) ; Make sure that you enable foreign keys for each session (which isn't on by default, weirdly): pragma foreign_keys = ON Then, you just populate using inserts. Hope this helps, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ Like us on Facebook: http://www.facebook.com/BareFeetWare On 19/12/2012, at 12:37 PM, Guillaume Saumure wrote: > Hello everyone, > > I have to create a small program to track changes done on multiple machines. > The data structure look like : > > Structure Notice >PrimaryKeyID.l >Type.l >Date.l >ExecutedBy.s >RequestedBy.s >Description.s > EndStructure > > Structure Machine >PrimaryKeyID.l >Name.s >Location.s >List Notices.Notice() > EndStructure > > Structure Assets >... >... >DatabaseFileName.s >List Machines.Machine() > EndStructure > > To make a long story short, I have a Linked list nested inside Linked list > but I really don't know how to manage a scenarios like this using Database > recording system. Any suggestions ? > > Thanks beforehand and best regards. > Guillaume Saumure ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to manage Table inside Table scenarios
Hello everyone, I have to create a small program to track changes done on multiple machines. The data structure look like : Structure Notice PrimaryKeyID.l Type.l Date.l ExecutedBy.s RequestedBy.s Description.s EndStructure Structure Machine PrimaryKeyID.l Name.s Location.s List Notices.Notice() EndStructure Structure Assets ... ... DatabaseFileName.s List Machines.Machine() EndStructure To make a long story short, I have a Linked list nested inside Linked list but I really don't know how to manage a scenarios like this using Database recording system. Any suggestions ? Thanks beforehand and best regards. Guillaume Saumure ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple outstanding sqlite3_stmt objects
On 12/18/2012 8:21 PM, Larry Brasfield wrote: At http://www.sqlite.org/c3ref/stmt.html , there is a weak implication that SQLite may expect single sqlite3_stmt objects to exist for a connection. Not sure what led you to believe this. You definitely may have multiple prepared statements on one connection. They are largely independent of each other. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple outstanding sqlite3_stmt objects
At http://www.sqlite.org/c3ref/stmt.html , there is a weak implication that SQLite may expect single sqlite3_stmt objects to exist for a connection. The pattern I intend to follow instead of the one shown as "life of a statement object goes something like this" is: 1. Create sqlite3_stmt #1 2. Create sqlite3_stmt #2 3. Bind values to sqlite3_stmt #1 4. Execute sqlite3_stmt #1 5. Reset sqlite3_stmt #1 6. Bind values to sqlite3_stmt #2 7. Execute sqlite3_stmt #2 8. Reset sqlite3_stmt #2 9. Repeat steps 6-8 as needed 10. Repeat steps 3-9 as needed 11. Destroy sqlite3_stmt #2 12. Destroy sqlite3_stmt #1 Is there any reason not to do this? (I would not be asking if I could see any hint in the API docs that sqlite3_stmt objects can be used independently, in arbitrary order, during their lifetimes.) Thanks, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
I believe only WAL mode is sticky. Not using the pragma seems to generate deletions as normal. The missing data was from after the file was closed. I am still looking into rollback, but why would journal mode matter? Marc On Dec 18, 2012, at 12:02 PM, "Simon Slavin" wrote: > > On 18 Dec 2012, at 3:04pm, Marc L. Allen wrote: > >> I also have no additional information as to why having PERSIST mode on >> prevents the database from being updated/correct. I did check the >> sqlite3_close command, and I'm passing it the connection received from >> sqlite3_open. It returns SQLITE_OK. > > Well, it seems you're doing it right. It's possible that your operating > system doesn't show updates until the file is closed. > > The other thing is that journal mode is meant to be sticky with each database > file. It may be that when you issue 'PRAGMA journal_mode', even though > you're setting the same mode that's already active, SQLite reinitialises the > journal. Now you have the correct journal mode set for your database files > you shouldn't need to use that PRAGMA again. Does that fix things ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] complex update
On 12/18/2012 12:27 PM, Adam DeVita wrote: There is a table products where has a location id. Unfortunately duplicate dictionary names got added to list of locations products haslocationid and a bunch of other stuff I can easily get the max (bad) and min (good) location ids associated with each name (I know I should have made the name field UNIQUE... mistakes were made years ago) how do I write an update that essentially says update products set locationid = good where locationid = bad , but do it for each good & bad pair ? update Products set locationid = ( select min(locationid) from Locations where name = (select name from Locations L where L.locationid = Products.locationId) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] complex update
Good day, I'm attempting to fix some bad data: There is a table products where has a location id. Unfortunately duplicate dictionary names got added to list of locations products haslocationid and a bunch of other stuff I can easily get the max (bad) and min (good) location ids associated with each name (I know I should have made the name field UNIQUE... mistakes were made years ago) how do I write an update that essentially says update products set locationid = good where locationid = bad , but do it for each good & bad pair ? I can see how to do it with insert or replace, but is there a way to do it with UPDATE? regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
On 18 Dec 2012, at 3:04pm, Marc L. Allen wrote: > I also have no additional information as to why having PERSIST mode on > prevents the database from being updated/correct. I did check the > sqlite3_close command, and I'm passing it the connection received from > sqlite3_open. It returns SQLITE_OK. Well, it seems you're doing it right. It's possible that your operating system doesn't show updates until the file is closed. The other thing is that journal mode is meant to be sticky with each database file. It may be that when you issue 'PRAGMA journal_mode', even though you're setting the same mode that's already active, SQLite reinitialises the journal. Now you have the correct journal mode set for your database files you shouldn't need to use that PRAGMA again. Does that fix things ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Entity Framework 6.0 alpha 2
There is an open source version of entity framework version (EF, version 6.0). I want to try it out with mono to see how it works but sadly I have this ADO.netprovider problem here (sqlite 1.0.82.0). I get the following error with the new EF 6.0: No Entity Framework provider found for 'System.Data.SQLite' ADO.NET provider. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information. so I checked out that link and it says: What if the provider I need isn’t available? If the provider is available for previous versions of EF, then we encourage you to contact the owner of the provider and ask them to create an EF6 version. You should include a reference to the documentation on how to rebuild providers for EF6. I can see that system.data.sqlite is not mentioned on that list. So I'm wondering if there is a beta or alpha version that i can use? If not, when can I expect one? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
Some additional information. It appears that setting the journal_mode is not inherited by DBs that are attached after the PRAGMA journal_mode is executed. So, I added one after the ATTACH. It also appears that, as part of the PRAGMA journal_mode execution, the current journal file is deleted. My SQL activity tends to be a lot of "open DB, do work, close DB". That is, I don't open the database and leave it open. When I run multiple transactions, the journal is not deleted between each one. Am I right in assuming that upon opening a database (or the first real command thereafter), journals are deleted as a matter of course? I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. I'll check to see if something is being rolled back when that happens. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, December 17, 2012 5:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PERSIST Journal Mode On 17 Dec 2012, at 8:35pm, "Marc L. Allen" wrote: > Another item.. when having Journal Mode = PERSIST, DBA (in the example below) > was not being physically updated. DBB was. I can think of a reason you might not be able to see an update until you have executed _close(). So are you looking to see an update before or after your code has executed _close() ? How are you looking for an update ? The file's modification timestamp ? Also, are you sure you are executing _close() with the right parameter, and can you check the result returned from _close() to make sure it returns SQLITE_OK ? > Simply taking out the PRAGMA fixed things. Any ideas where I should look? Clip from your earlier post: > open(DBA) > PRAGMA journal_mode = PERSIST; > ATTACH DBB > SQL ... > close() > > If the DBA.journal file exists, it's deleted before the attach (though I > haven't identified exactly where. I can if important.) During the close, the > DBA.mjx is deleted as is the DBB.journal file. > The DBA.journal file is not deleted until the next time I open it. One situation I think might cause this is if the database isn't closed properly. When SQLite reopens the database it realises it is corrupt. It restores the database to a usable condition, then (this is just a guess) it deletes the old journal so it can make a new uncorrupted one. However, I can't answer your basic problem. I see no reason why these things should change just because you're using PERSIST mode. But there are people who understand SQLite internals better than I do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and Java - Trend Analysis and Graphing
Hi, I am developing a client-only android app using the standard Android SDK and SQLite. I would like to perform trend analysis (line graphs, pie charts etc) on the data inputed by the user in the database, is it possible to do this without building my own content provider? The inputs would be based on user profile and user inputs to the system. Thank you very much, Cecilia ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] found a Malware , when download sqlite-shell-win32-x86-3071500.zip
oh,sorry, i hate 360safe. 在 2012-12-18 下午3:44,"Roy Tam" 写道: > Hello, > > 2012/12/18 Miles Liu : > > sqlite-shell-win32-x86-3071500 > > when download it , anti-virus said found a Malware. > > False positive. VirusTotal report here: > > https://www.virustotal.com/file/85fa29a391d7ab8af4736dfd36222a651a9b3af56c5f81bb58c9b106eaa63533/analysis/1355816443/ > > HTH, > Roy > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users