Re: [sqlite] Latin-1 characters cannot be supported for Unicode
On 15 Jun 2016, at 3:44am, Wang, Wei wrote: > Under the ANSI encoding environment, I created a table named TEST_PRODUÇÃO in > the database. All strings handled by SQLite, including the strings that make up SQL commands like "CREATE TABLE ...", are Unicode strings. If you are constructing an ANSI string and passing that to sqlite3_exec() or sqlite3_prepare(), then you are doing the wrong thing. You must convert to Unicode before passing the string to any sqlite3 API call. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 2nd Call For Papers - 23rd Annual Tcl/Tk Conference (Tcl'2016)
Hello SQLite Users, fyi ... 23rd Annual Tcl/Tk Conference (Tcl'2016) http://www.tcl.tk/community/tcl2016/ November 14 - 18, 2016 Crowne Plaza Houston River Oaks 2712 Southwest Freeway, 77098 Houston, Texas, USA Important Dates: [[ Attention! Registration is open. Please have a look at http://www.tcl.tk/community/tcl2016/register.html The tutorials are known. See http://www.tcl.tk/community/tcl2016/tutorials.html ]] Abstracts and proposals due September 12, 2016 Notification to authors September 19, 2016 WIP and BOF reservations open August 22, 2016 Author materials due October 24, 2016 Tutorials Start November 14, 2016 Conference starts November 16, 2016 Email Contact:tclconfere...@googlegroups.com Submission of Summaries Tcl/Tk 2016 will be held in Houston, Texas, USA from November 14, 2016 to November 18, 2016. The program committee is asking for papers and presentation proposals from anyone using or developing with Tcl/Tk (and extensions). Past conferences have seen submissions covering a wide variety of topics including: * Scientific and engineering applications * Industrial controls * Distributed applications and Network Managment * Object oriented extensions to Tcl/Tk * New widgets for Tk * Simulation and application steering with Tcl/Tk * Tcl/Tk-centric operating environments * Tcl/Tk on small and embedded devices * Medical applications and visualization * Use of different programming paradigms in Tcl/Tk and proposals for new directions. * New areas of exploration for the Tcl/Tk language Submissions should consist of an abstract of about 100 words and a summary of not more than two pages, and should be sent as plain text to tclconfere...@googlegroups.com no later than September 12, 2016. Authors of accepted abstracts will have until October 24, 2016 to submit their final paper for the inclusion in the conference proceedings. The proceedings will be made available on digital media, so extra materials such as presentation slides, code examples, code for extensions etc. are encouraged. Printed proceedings will be produced as an on-demand book at lulu.com The authors will have 30 minutes to present their paper at the conference. The program committee will review and evaluate papers according to the following criteria: * Quantity and quality of novel content * Relevance and interest to the Tcl/Tk community * Suitability of content for presentation at the conference Proposals may report on commercial or non-commercial systems, but those with only blatant marketing content will not be accepted. Application and experience papers need to strike a balance between background on the application domain and the relevance of Tcl/Tk to the application. Application and experience papers should clearly explain how the application or experience illustrates a novel use of Tcl/Tk, and what lessons the Tcl/Tk community can derive from the application or experience to apply to their own development efforts. Papers accompanied by non-disclosure agreements will be returned to the author(s) unread. All submissions are held in the highest confidentiality prior to publication in the Proceedings, both as a matter of policy and in accord with the U. S. Copyright Act of 1976. The primary author for each accepted paper will receive registration to the Technical Sessions portion of the conference at a reduced rate. Other Forms of Participation The program committee also welcomes proposals for panel discussions of up to 90 minutes. Proposals should include a list of confirmed panelists, a title and format, and a panel description with position statements from each panelist. Panels should have no more than four speakers, including the panel moderator, and should allow time for substantial interaction with attendees. Panels are not presentations of related research papers. Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather sessions (BOFs) are available on a first-come, first-served basis starting in August 22, 2016. Specific instructions for reserving WIP and BOF time slots will be provided in the registration information available in August 22, 2016. Some WIP and BOF time slots will be held open for on-site reservation. All attendees with an interesting work in progress should consider reserving a WIP slot. Registration Information More information on the conference is available the conference Web site (http://www.tcl.tk/community/tcl2016/) and will be published on various Tcl/Tk-related information channels. To keep in touch with news regarding the conference and Tcl events in general, subscribe to the tcl-announce list. See: http://code.activestate.com/lists/tcl-announce to subscribe to the tcl-announce mailing list. Conference Committee * Andreas Kupries Hewlett Packard Enterprise * Arjen MarkusDeltares * Brian Griffin Mentor Graphics * Clif Flynt Noumena Corp * Gerald Les
Re: [sqlite] Latin-1 characters cannot be supported for Unicode
Under the ANSI encoding environment, I created a table named TEST_PRODUÇÃO in the database. Then I opened this database with sqlite-tool. I ran the sql statement to query all the tables and found the new created table was shown as TEST_PRODU??O. Also this table could not be queried out using the table name TEST_PRODUÇÃO. It seemed that this issue was caused by encoding mismatch. Best Regards, Wang Wei -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Brody Sent: Wednesday, June 08, 2016 4:20 PM To: SQLite mailing list Subject: Re: [sqlite] Latin-1 characters cannot be supported for Unicode Hi Wei Wang, Did you populate the database from the sqlite3 CLI tool, your own C program, or from another language? Do you see this when you create a database from scratch, if you use a database created by another program, or in both cases? If you populated the database from the sqlite3 CLI tool, can you post the commands you used to populate the database? If you populated the database from your own C program, can you post a simple test program that populates the database? If you populated the database from another language, can you post a test snippet that shows how you populated the database along with a pointer to which library you are using? What kind of system, CPU, and operating system(s) do you see this behavior on? It should be no problem for sqlite3 to deal with the Latin-1 characters you are using if you do it right. The trick is that sqlite3 is designed to deal with both UTF-8 and UTF-16 (le or be). SQLite stores which encoding is used in the database. The API allows you to use both UTF-8 and UTF-16 encoding, regardless of which encoding is actually used to store the data. I think this is documented properly in sqlite.org, and I found an excellent writeup (though 5 years old) at: http://www.mimec.org/node/297 I also like the Unicode link from Igor. Chris On Wed, Jun 8, 2016 at 3:49 AM, Wang, Wei wrote: > Thanks for your reply! But I found the Latin-1 encoded characters are > listed in the Unicode chart. http://unicode.org/charts/PDF/U0080.pdf > > > Best Regards, > Wang Wei > > -Original Message- > From: sqlite-users-boun...@mailinglists.sqlite.org > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of > Igor Tandetnik > Sent: Tuesday, June 07, 2016 10:20 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Latin-1 characters cannot be supported for > Unicode > > On 6/7/2016 3:43 AM, Wang, Wei wrote: >> I met a problem that was maybe caused by the encoding of SQLite. I inserted >> a item which including some Latin1 characters like Ç and à into a table. >> Then I opened the database with SQLite Developer. After I setting the >> encoding to ANSI, the display and the query result for that table were OK. >> However after I setting the encoding to Unicode, these Latin1 characters >> could not be displayed normally, and could not be queried out. Please see >> the attached pictures for the details. > > A byte sequence containing Latin-1-encoded characters Ç or à is not in > fact a valid byte sequence in any Unicode encoding - neither UTF-8 nor > UTF-16 nor any other. If you want Unicode data in your database, then store > Unicode data, and not ANSI, in your database. > -- > 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 ___ 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] Managing SQLite indices.
sqlite supports 'natural join' which allows you to omit the on clause for well structured databases. My database model is really a C# DataSet ( which contains DataTable, which contain DataColumn and DataRow, (and this is superfluous, but row has a reference to it's table and therefore the columns to relate to the array of elements it contains) DataRelations are in DataSets between DataTables, and they are foriegn key definitions; with OnUpdate, OnDelete, et al behaviors definable. I have a module that's open source that takes a data table and many common operators and generates create table and constratint thigns for DataSets. The DataSet CAN be designed using the dataset designer (it's OK; nothing that you couldn't find better of in the 90's). table1 table1 + _id --automatic primary key; I use object in the datatable now as the type so the key can be GUID or int autoincrement. table1 + _name -- common text field that this ID might be printable in part as... could be _description, _text, _ToString ? table2 table2 + _id table1 + _id DataRelation( table2, table1.TableName+"_id", table1, table2.TableName ).onDelete = (?rule enum).Cascade. *shrug* too specific I know which can be wrapped in something ilke makeDataRelation( table2, table1 ); automatically and consistently. The problem becomes self-recursive keys which should themselves have just tablename_id, but would conflict with themselves... map1 map1_id int auto_increment PRIMARY KEY, parent_map1_id int node_info_id int where it joins ( parent_map1_id = map1_id ) ... --- I did learn in school something like how to make words plural public static string StripPlural( string s ) { if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3, "ies", 0, 3 ) == 0 ) return s.Substring( 0, s.Length - 3 ) + "y"; if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3, "ses", 0, 3 ) == 0 ) return s.Substring( 0, s.Length - 3 ) + "s"; if ( ( s.Length > 1 ) && s[s.Length - 1] == 's' ) return s.Substring( 0, s.Length - 1 ); return s; } So I did add rules so I could take a plural table name like "Players" and make Player_id, Player_name, etc... And many tables had an ID that was an _info record or a _decription of something and "member_info" to be member_id, etc... public static string StripInfo( string s ) { int trim = s.IndexOf( "_info" ); if( trim > 0 ) return s.Substring( 0, trim ); trim = s.IndexOf( "_description" ); if( trim > 0 ) return s.Substring( 0, trim ); return s; } We did try to suffix tables with sort of a data type; kind of the table name is it's object-type maybe there's no justification for chopping it, and in correctness the full tablename shoudl be used so players and player_info and player_desc don't all collide at some point; I could blame it on having to conform to an existing system? The new system was certainly easy to automate and even work with by hand. prefixes can be more useful a DataSet can have a prefix that gets applied to all tables in it, so if you had a small cluster of information like user_permissions, which has a grouping of tables, they could all share a similar prefix and be located together when browsing. (there are no relations between datasets, but table names would never include their prefix when referencing them... ) Please do feel free to rip giant holes in anything I've said :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble coding conditional UNIQUE
On Tue, 14 Jun 2016 16:27:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > Once the part has been grouped into a set (Matched=1) it receives a > unique permanent serial number and the temporary serial number can be > reused, so (Model, TemporarySerialNumber) doesn't have to be unique > anymore. Information about what parts belong to what set is stored in > a different table. > > So is using a conditional constraint in this case okay, or is there a > better way? It's OK, Bill; it was OK before I posted. I'm only suggesting an alternative you might like better. You pasted your particulars into my generic description, and they fit perfectly. You have two sets: 1. Parts with a unique, permanent serial number. 2. Parts with a temporary, reusable serial number. You could remove the latter set to a new table, perhaps "UnmatchedParts", having the characteristics you want, namely a primary key or unique constraint on (Model, TemporarySerialNumber). Then you don't need the Matched bolean column in either table, solving your original problem (a conditional constraint). Then you have a view, create view vParts as select 1 as Matched, * from Parts UNION select 0, * from UnmatchedParts ; I think there's a natural tendency to put similar things in one table when they share common properties, sometimes by adding a discriminator column. I've seen lots of tables like that, and designed some myself upon a time. SQL encourages it, because that way you can write just one INSERT, etc., and just set the flag right. When you push hard on such a table, though, by trying to do things right, you wind up with little conundrums (conundra?) like the one you posted. They grow out of the fact that the things aren't the same. They're just a smidgen different, so they need to be tweaked just so, and before you know it you either have to lean on some oddball feature of the DBMS, or punt. If you separate them, the you might have more query text, but each one will be simpler and easier to understand. HTH. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managing SQLite indices.
One thing I would add is to try to populate your example database with representative data - in fact, try hard to figure out what representative data looks like, it informs many decisions. My experience is that sometimes people assume that because something is fast enough on their workstation, it's fast enough for production, but in production their joins involving multiple full table scans are suddenly generating tens of thousands of rows for their SORT to order and their WHERE to filter, instead of the small constant number of rows in their simple test database. -scott On Mon, Jun 13, 2016 at 5:04 PM, Simon Slavin wrote: > > On 14 Jun 2016, at 12:27am, Smith, Randall wrote: > >> the info from EXPLAIN QUERY PLAN and from reading the query itself don't >> always lead to an obvious statement of what indices are needed. > > I don't think this can be done well by software. Of course, I haven't tried > it. > >> Has anyone figured out a good system for managing indices in a smooth, >> efficient, and reliable way in a non-trivial SQLite application? > > Sure. But you're not going to like it. > > General principles: > > A) Plan your schema properly. Tables represent things. Think through > COLLATE for every column, especially key columns. Use foreign keys rather > than copying data into other tables. No need to be obsessive about it but > "that's how it was done when I started here" is not good enough. Work out > your tables, your primary keys and your views and your indexes will take care > of themselves. > > B) If a query runs fast enough, it runs fast enough. Don't mess with "as > fast as possible". That way lies madness. > > C) Don't index a column just because it looks important. You create an > index, when you create an index, for a particular statement. You look at the > "WHERE" and "ORDER BY" clauses and figure it out from there. It's always > possible to create the best possible index for a statement by inspecting > those two clauses and thinking about how "chunky" each column is. You may > not need the full index -- the rightmost column(s) may be unnecessary -- but > it's a good starting point. > > Got the principles ? Right. Now here's the procedure: > > 1) Delete all indexes. > 2) Run ANALYZE. > 3) Run your application. > 4) Note the SQLite command which takes the most annoyingly long time. > 5) Work out a good index which will fix the problem. > 6) Create the index. > 7) Repeat from step 2. > > When your application runs fast enough not to annoy you, you're done. If > you're not willing to do step (1), don't bother with anything else. > > Simon. > ___ > 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] Managing SQLite indices.
On 14 Jun 2016, at 9:54pm, Smith, Randall wrote: > Thanks for the ideas, Simon. Already good on the general principles. The > approach of just periodically deleting all the indices and starting over from > scratch with a massive, comprehensive re-profiling effort might work on a > small project, an overstaffed one, one that doesn't change much, or one that > is not in a high-pressure environment, but is definitely not practical on > mine where none of these conditions apply. In a situation like that you do not work on the production database on production hardware, but a test copy on test hardware. But the principles are unchanged. As I wrote, changing the data shouldn't change the indexes needed. That happens only if you stop executing some SQL commands or start executing some new ones. If you have a huge complicated schema with many different SQL commands executed perhaps it would be acceptable to do the process just for each new command: 1) For each new SQLite command, does it execute in acceptable time ? 2) If not, figure out an index which is suited to the new command. Compare this new index and see if it's sufficiently different from existing ones to be worth creating just as it is. 3) If not, replace an existing index with one which incorporates elements of both. An experienced SQL programmer would know a few indexes to create before even running their first test. But doing the process I outlined will teach you how indexes work and let you gain enough experience to do that. > o Some kind of "gee, I sure wish I had this index" info from the query > planner. [and other suggestions which follow from those] Given the above sequence, why not go the whole way and have the software automatically create the new indexes itself ? Or have the SQL engine just make every temporary index it uses while running permanent ? Then you wouldn't need the human at all. Using the automated system outlined above just leads to databases with far too many indexes. Consulting tables takes little time but making changes takes far more because for every row created/deleted many indexes need to be updated. Another problem with the above procedure is that that new indexes made can make old indexes pointless. You need a human to realise when that happens and weed out the old ones. Or to start from a situation where you have no indexes at all. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managing SQLite indices.
Thanks for the ideas, Simon. Already good on the general principles. The approach of just periodically deleting all the indices and starting over from scratch with a massive, comprehensive re-profiling effort might work on a small project, an overstaffed one, one that doesn't change much, or one that is not in a high-pressure environment, but is definitely not practical on mine where none of these conditions apply. One thing I've been curious about is why SQLite doesn't provide more powerful help here. A few things I can think of that would be useful would be: o Some kind of "gee, I sure wish I had this index" info from the query planner. o Index utilization statistics, so little-used or unused indices could be identified and eliminated. o Timing info for each step of the query plan (and for that matter the overall query), so query profiling would be easier and you could quickly identify the problem spot in a complicated query instead of having to constantly play find-the-peanut. I'm no database engineer, so I have no idea how easy or hard these would be! Randall. > From: Simon Slavin > Has anyone figured out a good system for managing indices in a smooth, > efficient, and reliable way in a non-trivial SQLite application? Sure. But you're not going to like it. General principles: A) Plan your schema properly. Tables represent things. Think through COLLATE for every column, especially key columns. Use foreign keys rather than copying data into other tables. No need to be obsessive about it but "that's how it was done when I started here" is not good enough. Work out your tables, your primary keys and your views and your indexes will take care of themselves. B) If a query runs fast enough, it runs fast enough. Don't mess with "as fast as possible". That way lies madness. C) Don't index a column just because it looks important. You create an index, when you create an index, for a particular statement. You look at the "WHERE" and "ORDER BY" clauses and figure it out from there. It's always possible to create the best possible index for a statement by inspecting those two clauses and thinking about how "chunky" each column is. You may not need the full index -- the rightmost column(s) may be unnecessary -- but it's a good starting point. Got the principles ? Right. Now here's the procedure: 1) Delete all indexes. 2) Run ANALYZE. 3) Run your application. 4) Note the SQLite command which takes the most annoyingly long time. 5) Work out a good index which will fix the problem. 6) Create the index. 7) Repeat from step 2. When your application runs fast enough not to annoy you, you're done. If you're not willing to do step (1), don't bother with anything else. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble coding conditional UNIQUE
> -Original Message- > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users- > boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden > Sent: Tuesday, June 14, 2016 9:48 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Trouble coding conditional UNIQUE > > On Mon, 13 Jun 2016 19:11:29 + > "Drago, William @ CSG - NARDA-MITEQ" > wrote: > > > I need UNIQUE(B, C) only when E=0. > > A conditional constraint is evidence that you have two kinds of things > represented in one table: those E=0 types that are identified by {B,C}, and > the rest. They're represented in a single table because they seem to have > the same columns, although the E=0 types don't need an E column. > > A better solution might be to separate the two types into to two tables, each > with its own constraints, and use a UNION to represent them as one. I'm grouping parts with temporary, reusable serial numbers into matched sets of 4. The temporary serial numbers are stick-on labels with alphanumeric text like red5, blu7, grn2. There are duplicates within this pool and the colors don't mean anything. Before the parts are matched I can't allow more than one part to have the same temporary serial number, so as long as Matched=0 (Model, TemporarySerialNumber) must be unique. Once the part has been grouped into a set (Matched=1) it receives a unique permanent serial number and the temporary serial number can be reused, so (Model, TemporarySerialNumber) doesn't have to be unique anymore. Information about what parts belong to what set is stored in a different table. So is using a conditional constraint in this case okay, or is there a better way? Thanks, -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managing SQLite indices.
On 14 Jun 2016, at 2:29pm, John Found wrote: > you missed one small but very important > detail: > > "When your application runs fast enough... > **on the slowest possible computer you can run it** > ...you are done." A fair point. Thanks for the niggle. I might put it differently but certainly it's pointless to test for speed on your own custom-assembled must-compile-linux-in-20-minutes desktop. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble coding conditional UNIQUE
On Tue, Jun 14, 2016 at 8:47 AM, James K. Lowden wrote: > On Mon, 13 Jun 2016 19:11:29 + > "Drago, William @ CSG - NARDA-MITEQ" wrote: > > > I need UNIQUE(B, C) only when E=0. > > A conditional constraint is evidence that you have two kinds of things > represented in one table: those E=0 types that are identified by {B,C}, > and the rest. They're represented in a single table because they > seem to have the same columns, although the E=0 types don't need an E > column. > > A better solution might be to separate the two types into to two > tables, each with its own constraints, and use a UNION to represent > them as one. > > --jkl > I was thinking the same thing, but couldn't phrase it as well as you did. But I have this unusual(?) habit of liking to do things "by the book", which in this case tends to be "Database Design & Relational Theory" and "SQL and Relational Theory", both by Dr. C. J. Date . Also tend to be more a theorist than an actual "real world" practitioner. I.e. I favor design over performance more than I should. -- "Pessimism is a admirable quality in an engineer. Pessimistic people check their work three times, because they're sure that something won't be right. Optimistic people check once, trust in Solis-de to keep the ship safe, then blow everyone up." "I think you're mistaking the word optimistic for inept." "They've got a similar ring to my ear." From "Star Nomad" by Lindsay Buroker: Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Apple announces new File System with better ACID support
On Tue, 14 Jun 2016 10:49:05 +0900 ?? wrote: > > On 13 Jun 2016, at 10:13pm, Richard Hipp wrote: > > > > The rename-is-atomic assumption is so wide-spread in the Linux > > world, that the linux kernel was modified to make renames closer to > > being atomic on common filesystems such as EXT4. > > http://man7.org/linux/man-pages/man2/rename.2.html rename(2) *is* atomic. That doesn't mean it's synchronous with respect to external storage. It only means that no two processes will ever see the file "in flight" in two places. If process A calls rename(N,M), at no point will process B have acceess to both N and M. Once M is available, N is extinquished. That's a useful property for a process that succeeds, and for which the OS successfully flushes the data to disk. When Richard says rename isn't atomic, he means that it's not synchronous with respect to the disk. It makes no guarantee that the directory entries were updated on disk. The rename happens in the kernel's filesystem memory structures, which *eventually* are persisted to disk. I have heard that that time lag may be measured in seconds. > I am interested to know what it would take to make linux renames > fully atomic. Reading it as is it feels like the action of rename > would be the most important piece to making rename atomic. The docs > claim this is atomic. What other aspects would be necessary? To make Linux rename fully synchronous is technically infeasible and politically impossible. On the political side, the preference in Linux is invariably for performance, often at ever-finer divisions of responsibility. As an example, Unix fsync(2) traditionally updated both the file and its metadata; Linux divided those into fsync and fdatasync, and added the requirement to call fsync on the directory. What was once a single call became 2 or 3. As a technical matter, it's really infeasible because there are too many moving parts: kernel, filesystem driver, and hardware. It is possible for a human being to know what kind of disk is installed and how configured, and to know the semantics of a given filesystem. It is not possible for the kernel to patrol all those things, and hence the kernel cannot make any guarantees about them. (To take an extreme example: NFS.) By the way, every DBMS I know anything about (and SQLite no exception), tends to eschew OS services except at the most minimal level. The internals of a DBMS carry a lot of state information unavailable to the kernel that the DBMS uses to prioritize how memory is used and when and where I/O is required. That's why every DBMS has its own logging mechnism, and some bypass the filesystem altogether. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble coding conditional UNIQUE
On Mon, 13 Jun 2016 19:11:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > I need UNIQUE(B, C) only when E=0. A conditional constraint is evidence that you have two kinds of things represented in one table: those E=0 types that are identified by {B,C}, and the rest. They're represented in a single table because they seem to have the same columns, although the E=0 types don't need an E column. A better solution might be to separate the two types into to two tables, each with its own constraints, and use a UNION to represent them as one. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managing SQLite indices.
On Tue, 14 Jun 2016 01:04:27 +0100 Simon Slavin wrote: > When your application runs fast enough not to annoy you, you're > done. If you're not willing to do step (1), don't bother with > anything else. Simon's entire post is excellent advice. To the OP: print it, and frame it. I would only add to > Plan your schema properly. the "properly" is code for BCNF or at least 3NF. It's the gift that keeps on giving. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managing SQLite indices.
On Tue, 14 Jun 2016 01:04:27 +0100 Simon Slavin wrote: > Got the principles ? Right. Now here's the procedure: > > 1) Delete all indexes. > 2) Run ANALYZE. > 3) Run your application. > 4) Note the SQLite command which takes the most annoyingly long time. > 5) Work out a good index which will fix the problem. > 6) Create the index. > 7) Repeat from step 2. > > When your application runs fast enough not to annoy you, you're done. If > you're not willing to do step (1), don't bother with anything else. > Great algorithm I use all the time. But you missed one small but very important detail: "When your application runs fast enough... **on the slowest possible computer you can run it** ...you are done." Neglecting this detail will always result yo sluggish programs on the average user computer. That is why I am using netbook class PCs and 5..10 years old desktop machines for testing my applications. -- http://fresh.flatassembler.net http://asm32.info John Found ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users