[sqlite] Javascript API for SQLite?
Hello, I work on an application that uses an SQLite database as it's binary document format (with a custom extension). Now we want to develop a Web App variation of that application, that should be able to read and write those documents/databases. Since the Web App is written in Javascript, I am now looking for a Javascript implementation of the SQLite library. I have used the C SQLite library in the past, so I know about using SQLite from C. However, I am just starting with Javascript and Web Apps and I am quite a newbie on that side (Other people on the team are experienced, but I have been asked to work on the SQLite integration). What would be my options? Thanks for any help. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to write line feed \n\r when output a txt file
On 14 mai 2012, at 21:03, Kees Nuyt wrote: On Mon, 14 May 2012 05:41:08 +, YAN HONG YE yanhong...@mpsa.com wrote: […] By the way, common lineendings are platform dependent MS Windows: \r\n = 0x0D 0x0A = cr lf Unix/Linux: \n= 0x0A = lf Apple Mac: \r= 0x0D = cr This is incorrect. First off, Apple Mac is hardware, and as such, it doesn't use *any* line ending. It will use CR-LF (Windows) line endings by default if you install Windows, and LF (Unix) line endings by default if you install Unix or Linux - or Mac OS X which is Unix. Apple's Operating System has been Mac OS X - certified Unix - for over a decade, and has been using LF line endings by default ever since. The Mac hasn't been using CR line endings for over 10 years. 10 years is a very long time in this industry. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How point numbers are they stored in sqlite?
On 17 avr. 2012, at 11:35, Richard Hipp wrote: On Tue, Apr 17, 2012 at 5:12 AM, Paxdo Presse pa...@mac.commailto:pa...@mac.com wrote: Hi, How point numbers are they stored in sqlite? In a field with REAL affinity: round(11.578767 / 2 , 4) is displayed 5.7894 in SQLite/Navicat :-), but 5,78939997 in the cursor of my development language when I get it to sqlite. Internally, SQLite works with 5.7894 or 5,78939997? Neither. SQLite uses double-precision IEEE floating point numbers. And the value 5.7894 is not representable as an IEEE float. The closest you can get is 5.789399965751840136363171041011810302734375. SQLite only attempts to preserve the first 15 significant digits of a floating point value, so SQLite is likely to print that number as 5.7894. But if your development language tries to show 16 digits of precision (as apparently it does) then you might see the second value. Key point: Floating point numbers are approximations. This is an inherent property of IEEE floating point numbers, not a limitation of SQLite. If you need an exact answer, use integers. Perhaps it's worth mentioning again David Goldberg's seminal paper What Every Computer Scientist Should Know About Floating-Point Arithmetic. That paper is so well known it's been reprinted many times and is available in many places on the internet. For example Oracle put a reprint in its documentation: http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html You could do worse than reading it. Twice. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select null values in python
On 22 mars 2012, at 16:41, Igor Tandetnik wrote: On 3/22/2012 11:32 AM, Fabio Spadaro wrote: If I want to run a query like select * from tablename where field1 = Null select * from tablename where field1 is null; NULL is never equal any value, not even another NULL. -- Igor Tandetnik Welcome to Igor's style. He never answers the question you don't ask :-) I have come to learn and appreciate his socratic style. Thanks Igor. In the meantime, you might be interested in something like select * from tablename where field1 IS Null Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select null values in python
On 22 mars 2012, at 17:26, Igor Tandetnik wrote: On 3/22/2012 12:18 PM, Jean-Denis MUYS wrote: On 22 mars 2012, at 16:41, Igor Tandetnik wrote: On 3/22/2012 11:32 AM, Fabio Spadaro wrote: If I want to run a query like select * from tablename where field1 = Null select * from tablename where field1 is null; NULL is never equal any value, not even another NULL. -- Igor Tandetnik Welcome to Igor's style. He never answers the question you don't ask :-) I have come to learn and appreciate his socratic style. Thanks Igor. In the meantime, you might be interested in something like select * from tablename where field1 IS Null I did show this very example, except lacking the whitespace in front and differing in capitalization. I assume you feel those distinct characteristics render your example more interesting than mine. -- Igor Tandetnik Well, my apology. I didn't read your example carefully enough. Shame on me. Let me retract my answer (except my praise for your Socratic style, which I will not retract even if it doesn't quite apply here). Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 5 mars 2012, at 14:50, Rob Richardson wrote: Our company typically uses SQLite Spy for managing SQLite databases. I keep hoping to find something better, because SQLite Spy does not offer the ability to edit a table inside a grid. The only way to update data is to use an SQL UPDATE statement. But nothing else offers the speed of displaying data for a decent-sized table. The table we most often have to show contains three columns, and data taken once a minute and saved for thirty days. I've seen a couple of announcements recently about SQLite Root, and it looks like it may be worth trying next time I get a little time. But in the meantime, I'm wondering what others think. Is it a good product? RobR I use MesaSQLite, which I find nice enough (http://www.desertsandsoftware.com/?realmesa_home) Regards, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Found it
On 25 janv. 2012, at 22:30, John Elrick wrote: I have to say this has been a fascinating learning experience. It has been enlightening reading the SQLite code and beginning to understand its inner workings. John ___ What a thriller! I was holding my breath. Did you think about selling the rights to this story to a movie studio? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
On 20 janv. 2012, at 18:08, Simon Slavin wrote: On 20 Jan 2012, at 3:42pm, Don V Nielsen wrote: Recent conversations by users has convinced me that I would be better off using the amalgamation file instead. However, I don't know what steps in VS to do to specify the source and header files so that it compiles into my application. Can someone walk through steps necessary for specifying properties, references, and source code. And once it's done can we add it to the documentation or something ? A page for each of the most common uses would be excellent: one for VS, one for common Linux compilations, one for Xcode on Macs, would be really useful. Simon. I am a Mac developer, and I haven't even tried to look for such documentation on Xcode. This is because the steps to follow couldn't be more obvious. Here they are: Step 1: drag and drop the amalgamation files to your Xcode project Step 2: there is no step 2 Anything beyond that concerns your project, not SQLite. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# amalgamation hand holding requested
On 23 janv. 2012, at 11:18, Simon Slavin wrote: On 23 Jan 2012, at 8:59am, Jean-Denis MUYS wrote: I am a Mac developer, and I haven't even tried to look for such documentation on Xcode. This is because the steps to follow couldn't be more obvious. Here they are: Step 1: drag and drop the amalgamation files to your Xcode project Step 2: there is no step 2 Have you tried that recently ? Depends on what you mean by recently. Last time for me was around last summer. It was definitely with Xcode 4. I do that on perhaps a weekly basis with all kinds of source code however. Where in the project ? Wherever you like. I suspect you organize your project with groups for each subsystem. I put it in either in the subsystem that uses SQLite, or in a more general 3rd party libraries group. The Xcode icon in the dock ? If you do that, Xcode will open the file(s) as is, and will not add it(them) to any of your open projects. Part of an Xcode window ? The project navigator as always. The project icon in the window ? Possibly. That icon is in the project navigator, and if drop a file on it, it ends up at the top level, outside of all groups. One of the folders in the window ? Make a new folder for them ? Your choice. How do you answer the dialog that pops up ? Well, nothing special here. You answer it exactly as for any other set of source files: you check the targets that will use SQLite (probably all of them if you have more than one), you create a group if you dropped a directory rather than the source files themselves, and you check/or not the option to copy the files in the group folder (up to you). Should the 'Target Membership' checkbox be checked for both files ? It doesn't matter. In fact, if you drag and drop both files at the same time, you will not have the opportunity to decide on file by file basis. In any case, Xcode 4 is smart enough to know that header files are never added to any target. While all those questions are legitimate, they are beginners questions regarding Xcode and none of them is related to SQLite. I would contend that SQLite documentation is not the right place where to put basic level 1 documentation about any or all IDEs that can be used to develop with it. Perhaps more interesting would be to propose Xcode project templates for projects that use SQLite with the Amalgamation distribution (as opposed to the SQLite versions that Apple ships with its OS'es). Though the added value would slight indeed. Simon. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: How about a proper forum rather than an e-mail list
On 18 oct. 2011, at 11:47, Frank Missel wrote: I see several advantages to having a forum: 1. Several subject forums as mentioned Mail can have as many subjects as desired 2. Better view of threads with several levels being immediately displayed My mail client threads far better than most forums (fora?) 3. Preview of entries and editing of them even after they are posted (by the author) Which can be seen as a liability 4. Formatted rather than plain text Which mail is capable of 5. No need for e-mail-addresses to be exposed Couldn't a mailing list hide email addresses too? On the other hand, I find mailing lists much better: I can read them off line, I can also answer them off line (my client will send my answers as soon as it gets online), I can archive any and/or all posts that I find valuable… In fact, when fora (forums?) propose a mailing list interface (i.e. google groups), I prefer subscribing to them as a mailing list. Even better than mailing lists: newsgroups. Except my company only lets us use port 80 in addition to the mail gateway, so I can't use newsgroups. JDM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] splitting a line to produce several lines?
I am migrating data from a database which has a table that describes items by kinds. Instances of those kinds are stored in one column of this table, listing each instance's id, with all ids separated by a slash /. Of course, this is a very poor design. The target system has a proper table for instances, with a one-to-many relationship between kinds and instances. My question is: is there a pure SQL way to split the instances string, and generate the instance lines from the instance list string? contrived example: Table Kind: id nameinstances 1 Bird'eagle/seagull/hen' 2 Mammal 'dog/cat/cow/rabbit' 3 Fish'tuna/shark/cod' Possible outcome: Table Instances: id namekind_id 1 eagle 1 2 seagull 1 3 hen 1 4 dog 2 5 cat 2 6 cow 2 7 rabbit 2 8 tuna3 9 shark 3 10 cod 3 (the instance id is not especially important. it could as well be the concatenation of the kind id/name and the instance name) Of course, I could write a program to do that, but if I could do it in a few SQL statements, it would happen earlier… Thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] splitting a line to produce several lines?
On 12 sept. 2011, at 13:26, Black, Michael (IS) wrote: Since SQL is designed to return rows I believe the answer is no since you're data is not stored in rows. I suspected such a limitation. Thanks for the confirmation. (though I question the justification: SQL is designed to return rows is precisely the reason why one could hope it could return rows from the results of a [split] function). You need to do what's referred to as normalizing your data. That's one goal of the migration. Back to programming. JD […] Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.orgmailto:sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.orgmailto:sqlite-users-boun...@sqlite.org] on behalf of Jean-Denis MUYS [jdm...@kleegroup.commailto:jdm...@kleegroup.com] Sent: Monday, September 12, 2011 3:28 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] splitting a line to produce several lines? I am migrating data from a database which has a table that describes items by kinds. Instances of those kinds are stored in one column of this table, listing each instance's id, with all ids separated by a slash /. Of course, this is a very poor design. The target system has a proper table for instances, with a one-to-many relationship between kinds and instances. My question is: is there a pure SQL way to split the instances string, and generate the instance lines from the instance list string? contrived example: Table Kind: id nameinstances 1 Bird'eagle/seagull/hen' 2 Mammal 'dog/cat/cow/rabbit' 3 Fish'tuna/shark/cod' Possible outcome: Table Instances: id namekind_id 1 eagle 1 2 seagull 1 3 hen 1 4 dog 2 5 cat 2 6 cow 2 7 rabbit 2 8 tuna3 9 shark 3 10 cod 3 (the instance id is not especially important. it could as well be the concatenation of the kind id/name and the instance name) Of course, I could write a program to do that, but if I could do it in a few SQL statements, it would happen earlier… Thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...
On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote: On Jun 23, 2011, at 10:18 AM, Stephan Beal sgb...@googlemail.com wrote: Hi, all! Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db and the db file is only 400kb. HTF can that possibly be? After poking around i found that the wiki files actually total 360kb (when i added up their sizes manually, as opposed to using 'df' to get it), and the extra 80kb were from the hard drive's large block size (slack space reported by 'df'). Kinda funny, though, that sqlite3 actually decreases the amount of storage required in this case. Lots of small files will take up more space because of the fixed minimum block size. For large corpuses this won't matter. Putting them all in one db makes logistical management easier, but you will lose the ability to update just a single file individually. I used to store all my wiki files (punkish.org) in one SQLite db, but now I have them as separate files which allows me to just ssh and edit a single file easily. Six of one, and all that. Let me add two other drawbacks as well: - incremental backups: now everytime you change one small file, the whole database needs to be backed up, increasing needlessly storage size, and backup time. This applies to system that do versioning as well as backups (such as Time Machine). - system level indexing: it now becomes much more difficult, if not impossible, to do system level indexing and searching (as eg in Spotlight). This is the reason why Apple stopped using a monolithic database for its email application, now storing each mail individually: so that system-wide user search can hit emails too. These two drawbacks may or may not apply to your situation. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Garbled data after binding
On 20 juin 2011, at 08:42, CodeBoy DVM wrote:
NSEnumerator *enumerator = [ward objectEnumerator];
id element;
while(element = [enumerator nextObject])
{
You might want to modernize that code a bit (though this is likely not your
problem here):
for (NSDictionary *element in ward) {
}
Also try to avoid id as a type for variables you work with as id will
prevent the compiler from doing any static type checking. Better to use the
real class name, with perhaps one type cast.
sqlite select * from members where db_id =1127;
1127|2202|?? ?D??||0|
sqlite select * from members where db_id =1177;
1177|2015|h?? ?f??||0|
Select * is not very informative, though we can try to guess. Please select
only the mentioned columns. Also I would inspect your database with a GUI tool
(eg MesaSQLite) to double check those columns.
I have installed NSLog statements to try to hunt down the problem (those
NSLogs are in the method shown above), and every time I execute the code, the
NSLog seems to show the correct information, compared to the CSV:
I would put a breakpoint in the code just before your bind statement, to check
the data you are going to send them is indeed what you expect.
2011-06-19 22:50:24.534 iClerk[4292:a0f] Processing Person Moody, Isaiah
Terrell with Birth:1991-09-28 and ID: 2202
2011-06-19 22:50:24.534 iClerk[4292:a0f] Trying to bind
2011-06-19 22:50:24.591 iClerk[4292:a0f] Processing Person White, Lonnie Jay
with Birth:1973-12-12 and ID: 2015
2011-06-19 22:50:24.591 iClerk[4292:a0f] Trying to bind
Perhaps set the values to bind in their own variables, and NSLog those
variables right there. I would even log them *after* the call to sqlite3_step,
just to make sure it's not a weird memory allocation issue.
This particular update contains an array of 105 NSDictionaries to be added,
and every time, it is only these two that create problems. Everyone else's
data writes perfectly to the table, and I can SELECT them all I want, no
hiccups.
I should also mention that there were originally about 5 or 6 other persons
creating difficulty, but when I narrowed down the desired data to just Full
Name and Birthdate, it is only these two. (The original routine collects
about a dozen pieces of information for each person, but my list only needs
name and birthdate, so I tried to just make it work by only dealing with
those.)
But did you find out *why* those 5 or 6 persons had problems? If not, by
discarding those 5 or 6 persons, you also discarded information that may have
lead to the reason for the problem.
I am sorry I don't have a better suggestion, I'm pretty much a SQLite newbie
too.
Jean-Denis
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite in Xcode
On 26 mai 2011, at 16:49, john darnell wrote: Hello everyone. I am still trying to get SQLite to work on my Mac. I want to make sure I am doing what I should be doing. Here are the steps I have taken: 1.) I downloaded and uncompressed sqlite-autoconf-3070602.tar.gz from the SQLite download page. 2.) I moved SQLite3.c and SQLite3.h into my source folder and added them to the project. I made no changes to the code nor did I do anything special when I added them to my project (i.e. I did not set any special compile flags-I simply added the two files to the project). 3.) I compiled and received 1200+ errors. Since then I have tried using the sqlite.dylib file that comes with OSX but in doing so, some important SQLite functions (such as the prepare function) were not found during the link process. This shouldn't happen. My app links just fine. You must add libsqlite3.dylib to the Link binary with libraries section of the Build Phases pane of your target settings. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite in Xcode
On 26 mai 2011, at 17:23, john darnell wrote: On 26 mai 2011, at 16:49, john darnell wrote: Since then I have tried using the sqlite.dylib file that comes with OSX but in doing so, some important SQLite functions (such as the prepare function) were not found during the link process. This shouldn't happen. My app links just fine. You must add libsqlite3.dylib to the Link binary with libraries section of the Build Phases pane of your target settings. Jean-Denis Thank you, Jean-Denis for this information. I initially added the libsqlite3.dylib into the Libraries-API-Debug group. In so doing, Xcode automatically added the same dylib to the Link Binary with Libraries phase of my debug version. Are you suggesting that I should have added it directly? I suppose that as long as that library appears in the Link Binary with Libraries list, it should be OK, but I only ever did it the way I described. Now I don't get it that you mention your debug version. Debug is a configuration, and when I add that library, it gets added to my target whatever the configuration. I suppose being able to specify to include a library only for some configurations could be useful, but I don't even know how to do that. Also I don't have a Libraries-API-Debug group. My project, created from a standard Apple template, only has a Frameworks groups where I put all my libraries. Since that group is not associated with a directory, it should normally be irrelevant which group you use. Maybe you could copy/paste the exact link error you get to the list. I can also setup a dummy project with SQLite if you like. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6
On 25 mai 2011, at 10:40, Jan Hudec wrote: Since integer primary key is faster than primary key (object_id, side) and since the rows are mostly handled independenty (and have many other things refer to them), I construct a primary key with: object_id | (side 63) [...] Regards, Jan Interesting. I too use an integer primary key as a bitfield. Specifically, I need to identify a data record in a three-dimensional space (a data cube), and my key is: (z 16) + (y 8) + x This works for me because the three dimensions I use (x, y, z here) are positive integral values that can never exceed 255. Clearly, I won't be affected by the overflow issue you discovered. I hope there aren't other pitfalls when using a primary key as a bitfield. JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OSX building in xcode leading to crashing lib.
On 19 mai 2011, at 17:32, Chris Dillman wrote: On 5/19/11 10:26 AM, Richard Hipp d...@sqlite.org wrote: On Wed, May 18, 2011 at 6:52 PM, Chris Dillman chris.dill...@zenimaxonline.com wrote: Im having a lot of trouble getting a working build up and running. You are building from the canonical source code, consisting of about 100 separate source files? No The SQLite amalgamation would probably better serve your purpose. It's just two files: sqlite3.c and sqlite3.h. There is no configure script or makefile. No parameters to set. Nothing to go wrong. You just add sqlite3.[ch] to your application and compile. Interesting. Since the down load I have of that does come with config and make and make is setting a number of flags. Either way I believe we originally built from the amalgamation code with now flags set just simple drag the code in. It still always throw errors in mutex code or originally in the malloc code on open. Which is the same error reported some months ago on OS 10.4 His issues went away on 10.6 tho... So its not help. I am on 10.6.x now. Anecdotally, I had no issue including SQLite in my Xcode project. The amalgamation distribution I downloaded contained four files: - shell.c: I don't use that. I suppose it is the command-line sqlite3 executable. - sqlite3ext.h: I don't use that. I suppose it is a header that SQLite extension authors will need to use - sqlite3.c - sqlite3.h The last two are the two files I included in my project. That's it. Working like a charm using Xcode 4. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to test or create sqlite database on iPad.
On 19 mai 2011, at 11:21, irfan khan wrote:
Hi,
We got a new iPad and have to test sqllite database.
Could you please guide me to test.
Basically I want to create database, create tables insert some
records, and fetch records from iPad itself.
Is there any way to create database on iPad for preinstalled sqlite.
SQLite is a C library.
On the iPad as on any other machine, you create an SQLite database using that
library. Here is the routine in my iPad application that creates a database:
- (MyClass *) initWithObject:(MyObject*) objectToManage
{
if ((self = (MyClass*)[super initWithObject: objectToManage])) {
NSString *dirPath = objectToManage.absoluteDirPath;
NSString *sourceFilePath = [dirPath stringByAppendingPathComponent:
objectToManage.fileName];
NSString *basePath = [sourceFilePath stringByDeletingPathExtension];
NSString *tilePath = [basePath stringByAppendingPathExtension:@SQL];
NSLog(@Opening SQLite file %@, tilePath);
sqlite3 *db;
int rc = sqlite3_open([tilePath cStringUsingEncoding:NSUTF8StringEncoding],
db);
if (rc != SQLITE_OK) {
NSLog(@Can't open database: %s, sqlite3_errmsg(db));
sqlite3_close(db);
[self release];
return nil;
}
self.db = db;
[self createTablesIfNeeded];
sqlite3_stmt *compiledStatement = NULL;
rc = sqlite3_prepare_v2(db, select max(thoroughness) from Table;, -1,
compiledStatement, NULL);
rc = sqlite3_step(compiledStatement);
self.levelsOfThoroughness = sqlite3_column_int(compiledStatement, 0);
NSLog(@This file has %d levels of thoroughness, self.levelsOfThoroughness);
}
return self;
}
In there you can see:
- how to open a database
- how to close a database
- how to execute a SQL statement
- how to check for error
Which should be a good start.
Jean-Denis Muys
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install and configure SQLite on ipad.
On 17 mai 2011, at 19:16, irfan khan wrote: Thanks a lot for your replay. But we are developing new application for ipad for some buisseness need and need to configure sqllite database for the same. I am MS SQL database administrator and my developer team asked me to configure sqlite for ipad. please send me any related documents or details. To reiterate: - SQLite is very different from MS SQL and most other databases in that it requires ZERO *runtime* configuration - SQLite is used as a C library that provides a handful of C functions. To simplify, those functions eventually lead you to execute SQL statements provided in C strings. - As a C library, it can be compiled with a few optional variations that you are unlikely to need to be aware of as you learn using it. - SQLite is already installed in iOS by Apple. For most uses, you will not need to use a different version. - If it eventually turns out you need one of the optional compile-time variations mentioned above that were not included by Apple, it's simple to add your own version, and compile it with whatever options you need. You can worry about that later however and ask again if you need help. - Regarding this and your request to be sent documentation: http://lmgtfy.com/?q=Sqlite+documentationl=1 Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite with R*Tree and FTS3 support in iOS
On 16 mai 2011, at 17:01, Tito Ciuro wrote: Hello, I have a question about SQLite running on iOS. If I'm not mistaken, SQLite on iOS is not compiled with R*Tree and FTS3. Compiling a static library of SQLite's amalgamated version weighs at about 4.3 MB, which represents almost 25% of the 20 MB-per-app allowed on the App Store. For many, this is a major setback because many apps can easily reach this limit. My question is: since a light version of SQLite is already included in iOS, would it be too complicated to build a static library with only R*Tree and FTS3 support? The idea being of course that the app would link against iOS' SQLite and the app's R*Tree/FTS3 library, thus reducing the app's footprint considerably. Are there dependencies that would make this attempt a nightmare? Has anyone gone through this? I have considered this, but not done it as it turned out that I could do without R-Tree after all for the time being. But my initial investigation suggested that it would be possible to do so. I simply went through the amalgamation source code where it registers the R-Tree extension, and I realize that I could simply do the same thing, but using the R-Tree source file from the canonic source code. I didn't check for FTS3, but I suppose it's handled similarly. I haven't done it, so I can't be sure there is not hidden trap, but I can think of at least one pitfall: version compatibility. you must register with the iOS SQLite a R-Tree extension that is compatible with - if not exactly the same as - the iOS version of SQLite. It's unclear to me whether the *current* version of R-Tree is compatible with the *current* iOS SQLite version. you could think that the best idea is to go back to the same R-Tree version as iOS's. But even that might be problematic, as iOS is a moving target, and you risk your app breaking on a future iOS version... if it is not rejected by Apple in the first place for registering an extension to the system SQLite (I don't know that as I haven't tried). I am not knowledgeable enough with SQLite to understand what is the best way to minimize compatibility risks between different versions of the core Library and its standard extension. But saving 4.3 MB of app size doesn't seem valuable enough to me to run those risks. Even if those 4.3 MB make you break the 20 MB threshold. Sorry for not being more helpful. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Out of memory? How could that be on a 32G iPhone?
On 4 mai 2011, at 23:22, Rolf Marsh wrote:
Prior to getting this error, I opened the d/b and inserted one (1) very
small record...
Where do I start looking? I am using FMDB, ZBarSDK (used to read
barcodes), but I can't imagine that's using all of my memory... and I
have the d/b set to be a singleton, as indicated by the NSLog entries...
How do I tell how much active memory I'm using? Where do I start
looking (I'm a newbie, as you can probably tell by now) :-P
a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's
vastly different.
How much RAM an iOS device has is not published by Apple. Of course, as a
developer, it's easy to find out. And the answer is:
iPhone: 128 MB
iPhone 3G: can't remember
iPhone 3GS: can't remember (and too lazy to lmgtfy.comhttp://lmgtfy.com that).
iPhone 4: 256 MB
iPad: 256 MB
iPad 2: 512 MB
Regarding how much free RAM you still have at any one time, the function
commonly suggested looks like:
natural_t freeMemory(void) {
mach_port_t host_port = mach_host_self();
mach_msg_type_number_t host_size = sizeof(vm_statistics_data_t) /
sizeof(integer_t);
vm_size_t pagesize;
vm_statistics_data_t vm_stat;
host_page_size(host_port, pagesize);
if (host_statistics(host_port, HOST_VM_INFO, (host_info_t)vm_stat,
host_size) != KERN_SUCCESS) NSLog(@Failed to fetch vm statistics);
natural_t mem_used = (vm_stat.active_count + vm_stat.inactive_count +
vm_stat.wire_count) * pagesize;
natural_t mem_free = vm_stat.free_count * pagesize;
natural_t mem_total = mem_used + mem_free;
return mem_free;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Out of memory? How could that be on a 32G iPhone?
On 6 mai 2011, at 15:09, Rolf Marsh wrote: I figured this out... it was a pilot-error on my part...but I do have an additional question (see below). On 5/5/11 9:45 AM, Jean-Denis Muys wrote: On 4 mai 2011, at 23:22, Rolf Marsh wrote: a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's vastly different. Storage space as in SQLite d/b? Storage space as in space available on your hard disk. If you run your app in the simulator, the available storage is literally the available disk space on the OS X volume where your user account is stored. You can find your application somewhere at ~/Library/Application Support/iPhone Simulator/4.3.2/Applications. And your sqlite file is probably in the Documents subdirectory there. You can examine it from the terminal, look up its size, whatever. When you run your app on the device, the available storage is basically what iTunes tells you is available in the nice bar graph at the bottom of the summary screen. Also the *total* storage space is displayed by Xcode's Organizer window under the Capacity designation. JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice to choose an index for quad tree?
I found the R-Tree idea fascinating. Conceptually, this is exactly what I need. But indeed, my case is very simple: my dataset is not sparse, my tiles never overlap (for a given zoom factor), the number of tiles is still rather small, they are all rectangular and the same size (modulo edge effects). So while the asymptotic behavior of the R-Tree is likely to be much better, I am more concerned with the behavior towards small data sizes... The other important criterion is ease of implementation. Thanks to the SQLite R-Tree extension, both ways seem equally easy. I guess I'll start with my initial idea of a 3 column index, and experiment with R-Tree a bit later. Thanks for your suggestions. Jean-Denis On 3 mai 2011, at 19:42, David Garfield wrote: Actually, for what he wants, you don't need anything fancy. A simple multi-column index is enough. The R-Tree is to allow queries of a sparse dataset, that might also have overlaps. So: A simple index for your background imagery. An R-Tree index for the features added on top of your background imagery. --David Garfield Enrico Thierbach writes: Hi, I think an R Tree is what you are after. http://www.sqlite.org/rtree.html /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Advice to choose an index for quad tree?
Hi, My application displays very large images at different zoom factors. I have tiled my images into individual tiles of a much smaller size. Each image has hundreds of tiles. The tiles will be used to interactively display the image and the speed of the zooming and panning is very important. That's why I am thinking storing the tiles into an SQLite database. I am soliciting some advice on how to setup the index. The main column for a Tile table will be its pixel blob. I can setup the rest of its structure as I wish. For example: left: integer horizontal offset of the tile top: integer vertical pixel offset of the tile width: integer horizontal size of the tile height: integer vertical size of the tile shrink ratio: a power of two. How much the resolution of the tile has been reduced from the image resolution width and height are typically constant, equal, and a power of two. The exception is at the right and bottom edges for the right-most and bottom-most rows of tiles for images that have non-power of 2 dimensions. In memory, my data structure is a quad tree. The leaves of the quad tree point to elementary tiles: tiles that holds exact pixels from the image at the same resolution. At he next tree level, I take 4 tiles (arranged in a square) and make a new tile from them. This new tile is shrunk in resolution from its 4 source tiles by a factor of 2 in each direction. That is, each of its pixels represent 4 pixels from the source tiles. I iterate at the next level. Conceptually, this process stops when level n has only one tile, which can be considered as a thumbnail of the original image. When I need a tile, I know the topleft of the tile and I know the current zoom factor. The zoom factor can be 1.0 (actual pixels) or smaller (zooming out). From the zoom factor, I can easily compute the shrink ratio to use: I want to use those tiles that have just enough resolution, not better, as that would be wasteful and I am memory constrained (I can't load the full image in memory at any time). To find out the shrink ratio, it's basically 1/zoom factor, rounded to the correct adjacent power of two. The set of tiles needed at any given time is those that cover (at least partially) the displayed rectangle. The number of needed tiles is constant for a given window size, since tiles have a constant size (not quite constant: edge and zoom rounding effects can affect slightly that number. at least there is a known upper bound to that number). That number is small, much smaller than the number of elementary tiles. The required tiles are adjacent: they form a connected rectangle. Note that I can set up my Tile table differently if I want. For example, I can use row and column numbers rather than left and top since the pixel sizes are known. My question is: is there any smart way to set up my table index? My naive idea is to have a composite index: shrink ratio/left/top (or ratio/row/column). Is there any better? For example is there any reason to prefer either of ratio/left/top and ratio/top/left? Is there any reason to consider smart encodings similar to geo hashing where each additional bit in an identifier encodes a smaller area? I realize that since I have only hundreds - not millions - of tiles, this may sound like premature optimization. But I am not after optimization here (yet), only after sensible design. Thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: long insert statement failing on iPhone
On 1 mars 2011, at 21:11, Mickey Mestel wrote: hi all, i've just discovered that the bug is there when compiling with the LLVM compiler 1.6, which comes as a standard compiler with the iOS SDK, and is the direction that Apple is moving towards. the problem is NOT there when compiling with GCC 4.2. the problem is NOT there when compiling with LLVM GCC 4.2, which is described as GCC 4.2 front-end with LLVM code generator. so for the moment we are going to try going ahead with the LLVM GCC 4.2 path. but there is something down in the parser code that LLVM doesn't like, even though it doesn't come up with a warning or error. we found earlier that when compiling with LLVM, there were issues with the following statement: rc = sqlite3PagerSetPagesize(pBt-pPager, pBt-pageSize); in the function SQLITE_PRIVATE int sqlite3BtreeOpen. pBt-pageSize was a null value when entering the function it was passed to. we ended up simply doing: u16 *pPageSize = (pBt-pageSize); rc = sqlite3PagerSetPagesize(pBt-pPager, pPageSize ); so there are some issues with the LLVM compiler. will post again when i see what works, and what path we are going. it would obviously be nice to find out where in the sqlite code this is happening, but we just don't have the time to devote to that, unfortunately. mickm I strongly suggest you use Clang (LLVM C compiler) version 2.0. You can download it from llvm.org and build it up yourself, or you can simply switch to Xcode 4.0 which has it built in - if you have access to it. LLVM 1.6 is getting rather old now and plenty of bugs have been fixed in LLVM and Clang since then. Technically, Clang 1.6 doesn't come with the iOS SDK, it's rather that both come with Xcode 3.2.x. Indeed you can download Xcode without the iOS SDK, in which case you get the former without the latter. Also, similar to the Valgrind idea, I strongly suggest that you run your project under LLVM static analyzer if you haven't already. It's rather smart and it may uncover a bug. Again, the Clang 2.0 version is better than the old one. Finally, if you can reduce the issue to a small project, I'm ready to test it here and report what I can find. Regards, Jean-Denis Muys ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress
On 18 févr. 2011, at 15:12, Philip Graham Willoughby wrote: Adding casts to get rid of warnings is usually the wrong answer in my experience. Certainly you should never cast the return value of a function call because that hides the problems you get when it's implicitly returning int because a required header is missing. I used to see a lot of C code (usually from Windows programmers - does MSVC encourage this?) which has stuff like this all over the place: struct something *fred = (struct something *)malloc(sizeof(struct something)); Casting the return from malloc is never necessary - void * is assignable to any non-function pointer type by definition. It becomes an extremely bad idea when you're building on a platform with 64-bit pointers and a 32-bit int (such as everything I use) and you have the optimiser turned on – in this case if stdlib.h isn't included none of your allocations will work properly and your program will crash. Best Regards, Phil Willoughby -- My pure C must be rusted. Could you please elaborate why this line breaks with 64-bit pointers and 32-bit ints? fred is a 64 bit pointer of type struct something * malloc returns a 64 bit pointer of type void * casting that pointer to struct something * makes it a 64-bit pointer of type struct something *, which is then assigned to fred. I can't see how 64-bitness of pointers can hurt. Similarly, malloc takes one argument of type size_t. On such a platform size_t is probably defined as long (or long long), but that is irrelevant, because the sizeof intrinsic is defined as returning a size_t as well. Now even if size_t is defined as int, this would only have an impact for structs larger than 2^31 bytes, which are few are far between, and that impact would be unrelated to the typecast. I can't see how 32-bitness of ints can hurt. What did I miss? JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress
On 18 févr. 2011, at 16:51, Philip Graham Willoughby wrote: malloc returns a 64 bit pointer of type void * No, from the caller's point of view it returns an int if you haven't included stdlib.h. Indeed. I assumed the programmer had included the standard headers. On my system, any implicit declaration is punished by a warning, or even an error depending on the strictness of the settings. Since we were in the context of casting to shut up one warning, I assumed no implicit declaration warning, so no implicit declaration at all. I guess that's where my assumption fails: I suppose implicit declarations elicit no warning on your system. Thanks for the clarification. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Propose minor incompatible API change
On 11 janv. 2011, at 01:54, Richard Hipp wrote: So the question to you, gentle reader, is should we make this change, and break backwards compatibility, albeit in a very obscure way, or should we be hard-nosed and force hundreds or perhaps thousands of smartphone application developers fix their programs? -- I have very little sympathy for poorly written apps, and for their developers. Accommodating them by increasing the complexity of SQLite may seem the right investment in the short term, but is almost certainly a losing proposition in the long term (as your question here shows already). I vote for keeping the API clean. The lazy developers will have to fix their code or be driven out of the market (for the benefit of everybody). Perhaps make the topic more visible in the documentation. Nothing more. Don't be accommodationist. You stand to lose more than to gain. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Propose minor incompatible API change
On 11 janv. 2011, at 13:15, Richard Hipp wrote: On Tue, Jan 11, 2011 at 6:59 AM, Jean-Christophe Deschamps j...@q-e-d.orgwrote: My rationale is that either it's technically or economically feasible for the offending applications' developpers to change their code to use the library correctly (and that doesn't seem to be the case), OR very simply avoid upgrading to the new SQLite versions. I don't get what good reason they had to switch to new SQLite versions. I don't think I explained the problem clearly. The proposed change is for the benefit of the applications customers, not the application developers. An end user (think: your mom) wants to upgrade her smartphone to the latest OS release. That new OS release includes the latest shared library for SQLite. But in so doing, some percentage of the apps she has downloaded cease to work. Sure, the problem really is that the apps were incorrectly coded. But does your mom really care about that? They worked before. Do we really want thousand, perhaps millions, of moms screaming that SQLite broke their phone when they upgraded? moms won't know that SQLite is involved. They can blame either the crashing app or the OS. And they will probably blame the app. An OS upgrade almost always lead to incompatibilities for some app, for a number of reasons. This is the least of the reasons for app developers to update their app when an OS is upgraded. There is absolutely no reason to think that the app cannot be revised (fixed) at this point in time (or any other point in time). Unless the software is abandonware. Then, a bug in its SQLite code is the least of mom's problem. Don't encumber SQLite with workarounds and special cases to cater to bugs in client software. If so many moms are using it, the software developers will have enough incentives to fix their problems on their side. Making the API more complex to use or document increases the cognitive load of *all* users of SQLite, for the sake of a few programmers who don't bother. Don't do it. If anything, make sure to crash early and often when the API is misused. In that way, the client software can't avoid noticing the bug and fixing it. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing AVCHD files
On 6/9/10 13:26 , Pavel Ivanov paiva...@gmail.com wrote: So i assume i will have to split my AVCHD file into small files. Ok.. i am fine with that, but how will i maintain the folder structure of these split files.? If maximum supported blob size is 1Gb and you want to store a bigger data chunk then you split your data into several *blobs* and store them in separate rows in the database. But SQLite database is one file, so there's no folder structure to maintain. Pavel On Wed, Jun 9, 2010 at 7:18 AM, Navaneeth Sen B navanee...@tataelxsi.co.in wrote: Hi All, I would like to know how i can store an AVCHD file(It has a folder structure) having size greater than 4GB. The reason behind this question is like, from the documentation i found that the maximum supported BLOB size is 1GB. So i assume i will have to split my AVCHD file into small files. Ok.. i am fine with that, but how will i maintain the folder structure of these split files.? You don't tell us about your use case, but in many (most?) use cases, it's a good idea to keep your AVCHD files outside the database, and store only a reference to that file in the database. Benefits: - simplicity - no need to split anything - files are still there even if the database gets corrupted - some/most of the data can be salvaged more easily in case of media failure - files can be played by an outside player (eg VLC), whether directed by your program or through an external workflow (including, but not limited to manual user intervention). - friendly to OS-level incremental backup schemes (such as Time Machine). - friendly to OS-level indexing and searching mechanisms (though for video, this is probably limited to metadata). - makes it possible to distribute your video files across several hard disks. - makes it easier to let the user work on light (eg thumbnails) videofiles in a constrained environment (eg mobile), only to switch back to full definition video when back at the office. Drawback: - possibility for the user to tamper with the files behind your application's back (though if you are using SQLite, s/he can do so in the monolithic case as well). Regards, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing AVCHD files
On 6/9/10 13:55 , Navaneeth Sen B navanee...@tataelxsi.co.in wrote: Thanks Simon. Hi Jean, I dint understand the below given statement. Could you please explain this to me? possibility for the user to tamper with the files behind your application's back (though if you are using SQLite, s/he can do so in the monolithic case as well). What I mean is that the user can manipulate the video file herself, rather than letting your application do it. Depending on the said user action, your application might then misbehave, unless it's programmed defensively (which is always a good idea). Examples could be: 1- the user deletes the video file. Your database then contains a dangling reference. You as the programmer must be sure the be ready to handle a missing file situation. 2- different action, same result: the user put off line the hard disk with some video file(s). The file is missing to the application, and the reference is dangling. 3- the user edits the video file on its own with a movie editor. Your application sees the file, but it's been changed. For example, its duration is now different. If you store and use the duration, your application must be ready to have an out of date duration. 4- the user moves or renames a video file. Good OS'es have file reference that don't care about those user actions. They will still point to the correct file even after it's moved/renamed (even to a different volume). But you might not be running on such an OS. In that case, your application will see this situation as a missing media file. 5- The user moved the database from her office machine to her laptop, but forgot to copy the media file as well. Again, your app will now face massive missing media files. Note: it's possible for the app to differentiate between the different file missing situations, and possibly to act differently (at least in how it alerts the user and suggest a correction). I hope that clarified the point. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing AVCHD files
On 6/9/10 14:37 , Simon Slavin slav...@bigfraud.org wrote: On 9 Jun 2010, at 12:18pm, Navaneeth Sen B wrote: I would like to know how i can store an AVCHD file(It has a folder structure) having size greater than 4GB. It is unlikely that whatever filesystem you're using will allow any file to be this big. Therefore you cannot have a database file this big either. Leave the folder the way it is, and put the filenames into your database. Modern file systems allow files with sizes weighing in TB. HFS+ for example, which we have been using for a number of years already, has a single file size limit of 8 Exbibytes. Now your suggestion is sound, as I argued previously. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What languages can include SQLite statically?
Well the answer is: any language with an external interface to the C ABI can link to the SQLite compiled C object code (on my planet they don't have .OBJ nor .LIB extensions. Perhaps we are not on the same planet) to yield an executable (I guess that's what you mean by 'EXE'). That include any C-family language of course: C, C++, Objective-C. Or even Go, though it's difficult to classify Go in the C family. It should also be possible in Fortran for example, not that I can think of a good use case :-). Perhaps more usefuly, Etoilé is a Smalltalk dialect using the GNU-Step ABI, which was designed for Objective-C, so should be able to link with SQLite directly. I can do it with MacRuby too, since MacRuby is compiled to native code using the standard Mac OS X ABI. In MacRuby's tracks, it's become much easier to do so with any language, using LLVM. Of course, the other half of the equation is how do you call a SQLite function from the other language. At least, that implies creating an 'interface' file from the header. In fact it's not so much the language that's relevant here, rather the run-time architecture your particular implementation of that language uses: if that run-time supports a standard-compliant C compiler, then you should be good to go. Jean-Denis On 5/21/10 11:31 , Gilles Ganault gilles.gana...@free.fr wrote: Hello My C skills are very basic. I was wondering: After compiling SQLite into an .OBJ or .LIB file, what languages can be used to include this output into a main program, so we end up with a single EXE. I assume we have the choice of: - C - C++ - Delphi (?) - Other? Thank you. ___ 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] Fw: What languages can include SQLite statically?
I use Xcode, which is a front end to either clang or gcc. But I usually don't compile SQLite myself as it's a standard part of the OS. I link my code, written in Objective-C, compiled with either compiler, with the OS-provided libraries. The result is a native application. No muss no fuss. Jean-Denis On 5/21/10 14:26 , Gilles Ganault gilles.gana...@free.fr wrote: On Fri, 21 May 2010 14:23:04 +0200, A.J.Millan a...@zator.com wrote: Due the fact that you already know C and as my 2 cents to the question, depending on your requirements, perhaps would have a look to PHP. As far as I know, you can use directly SQLite from that language and perhaps you find it simple to use; easy to port between platforms and direct and easy to debug. Thanks but I'd like to pack the main application and SQLite into a single executable, so languages like Python, PHP, etc. aren't good solutions. Is MS Visual Studio the recommended solution to compile present-day SQLite, or are there lighter, open-source compilers that I could use instead? ___ 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] SQLite template files
On 4/6/10 18:50 , BareFeet list@tandb.com.au wrote: I understand that in many cases, the SQLite database developed are intrinsically tied to and designed for the application framework in which they reside. However, even those SQLite database files can be opened and manipulated by a SQLite GUI tool or the sqlite3 command line tool. In fact, the developer of those probably highly specialized database files no doubt created the databases using a GUI or command line tool. In theory it's possible. In practice, it's often of limited value. Often, much of the domain-specific behaviour is encoded in the real application rather than in the SQLite file. The app itself is often a lot more than a shallow GUI on top of a database. In fact, when wanting to transfer much of that domain-specific behaviour, I found that when it was possible, it was also cumbersome and error prone. For me SQLite is most of the time a high-level document file format. The fact that behind the curtain it's also a SQL database is nice but not essential. To be more specific, my applications usually use Core Data as the data persistence layer. My data models are designed using Xcode's graphical entity-relationship design tool which generates specific design documents ( xxx.xcdatamodel) and the corresponding SQLite databases automatically. Out of curiosity, I sometimes opened the resulting SQLite files with general-purpose SQLite tools, and this might occasionally be useful for debugging purposes, but certainly not for educational purposes. I suspect this would generally be the case, and the reason why no sharing of SQLite structure has emerged. For sharing purposes, the Xcode entity-relationship document (or some such) would be more interesting. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On 4/1/10 4:12 , Neville Franks sql...@surfulater.com wrote: Thursday, April 1, 2010, 12:16:13 PM, you wrote: JJD On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski JJD bardzotajneko...@interia.pl wrote: There were many problems with that approach: ... (3) Each table and index is in a separate file so your database was a directory full of files instead of a single file This one is not a problem. Actually I don't see how 1 file is better than 1 directory. For example mac application is a directory not a file and no one complains. And with several files database would be faster (for example dropping a table is instant or fragmentation is handled by OS without need for vacuuming whole database). Also with current SQLite implementation only tables would be locked by a transation not a whole database (a few years ago there were even document on SQLite website listing splittnig database to several files as one way to implement table level locks in SQLite). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users JJD Two reasons I prefer the single file approach: JJD 1. Simpler copy, tables and indexes don't get lost or mismatched. Also from the end user perspective it is so much easier for them to backup or copy a single file. These two answers are void in the context of the Wiktor's point who said For example mac application is a directory not a file and no one complains. The reason no one complains is that an app directory is seen by the user as a single entity (a bundle). S/he doesn't have the option to copy/backup parts of it. (except through geeky, explicit actions). Of course, the other operating systems don't care much about such details, nor about users in general, so your points are valid in general, just not in Wiktor's context. I think an interesting point can be made that splitting the database in several files (or forks, such as provided by HFS or NTFS) really means deferring parts of the DBMS's job to the file system. It could even be argued that using files in the first place is already deferring to the OS! So at one end of the spectrum the DBMS defers at little as possible to the OS, while at the other end of the spectrum, every table, or even maybe every row is in its own file. There are advantages to storing every row in its own file: the database becomes compatible by system-wide search facilities for example. This is why Apple went from a single-file mail database to a scheme where every mail is in its own file. Now you can use spotlight to search for emails. And to be clear, I agree that a single-file data format was the right choice for SQLite :-) Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On 3/29/10 23:10 , Tim Romano tim.rom...@yahoo.com wrote: On 3/29/2010 4:19 PM, Kevin M. wrote: I have a C/C++ application in which I want to store data from a struct into a table (using SQLite 3.6.23) and later retrieve data from the table and store it back in the struct. But, I need a general interface for this as there are many types of structs used. Couldn't you convert the structs to JSON format and store them in a TEXT field? There are a number of C++ JSON libraries listed here: http://www.json.org/ Since it's C++, an another option (a good one IMHO) is to use the Boost serialization facility. See http://www.boost.org and more specifically: http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html I quote: Here, we use the term serialization to mean the reversible deconstruction of an arbitrary set of C++ data structures to a sequence of bytes. Such a system can be used to reconstitute an equivalent structure in another program context. Depending on the context, this might used implement object persistence, remote parameter passing or other facility. In this system we use the term archive to refer to a specific rendering of this stream of bytes. This could be a file of binary data, text data, XML, or some other created by the user of this library. \quote some other could be a SQLite repository. FWIW, Boost is a widely established C++ general library with enough influence the next C++ standard library will borrow a lot from it. JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close == library routine called out of sequence
On 2/24/10 19:12 , Mike Shal mar...@gmail.com wrote: On 2/24/10, Jean-Denis Muys jdm...@kleegroup.com wrote: On 2/24/10 9:36 , Simon Davies simon.james.dav...@googlemail.com wrote: Rogue semicolon on line if( sqlite3_close( db ) != SQLITE_OK ); Regards, Simon I have been bitten by such silly mistakes often enough! This is why I have switched to a compiler with sanity checks. On this line of code, it warns: if statement has empty body This compiler is llvm. Check it out at http://llvm.org You can test for this mistake on the demo page at http://llvm.org/demo What compiler were you using before? Gcc warns about this too, though you have to explicitly enable the warning using -W (or -Wempty-body for just that particular warning). I suppose you could argue whether or not it's right to have that warning on by default, but I've found warnings are generally a matter of personal preference. If you don't make spurious semicolon mistakes, you probably don't need that warning on. I make tons of such mistakes, so I use a variant of the BDECFLAGS. That helps catch most of the stupid things I do :) I yes I switched from GCC, but not for that particular warning. The three features of LLVM/Clang technology that won me over are the generally much better error messages, link-time optimization and the static code analyzer. In any case, though I did not make any claim regarding its handling of this issue, I stand corrected on GCC. And yes I do try to keep warnings on as much as possible. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close == library routine called out of sequence
On 2/24/10 19:19 , D. Richard Hipp d...@hwaci.com wrote: On Feb 24, 2010, at 1:12 PM, Mike Shal wrote: This compiler is llvm. Check it out at http://llvm.org You can test for this mistake on the demo page at http://llvm.org/ demo FWIW, there are currently two places in SQLite where we have had to complicate the code in order to work around bugs in LLVM. D. Richard Hipp d...@hwaci.com Yes LLVM and especially Clang are rather young. It's not surprising they do have bugs. Hopefully, the issues that you encountered with SQLite will let them fix those bugs and improve. Overall however, I am very impressed by what the LLVM has been able to accomplish in just a a few years. For example, LLVM is behind: - the latest Objective C 2.0 Mac OS X compiler - the Arm compiler with Objective-C support for the iPhone - The MacRuby interpreter and native compiler for the Ruby language - The Smalltalk system part of the Étoilé project. The main lacking point (for me) now is C++ support. It's planned for the end of this year. I hope they can make it. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close == library routine called out of sequence
On 2/24/10 9:36 , Simon Davies simon.james.dav...@googlemail.com wrote: On 23 February 2010 17:44, newlog newlog2...@yahoo.fr wrote: OK, I really don't understand Rogue semicolon on line if( sqlite3_close( db ) != SQLITE_OK ); Regards, Simon I have been bitten by such silly mistakes often enough! This is why I have switched to a compiler with sanity checks. On this line of code, it warns: if statement has empty body This compiler is llvm. Check it out at http://llvm.org You can test for this mistake on the demo page at http://llvm.org/demo For example insert a spurious ; in this line in the proposed factorial demo if (X == 0); return 1; Llvm is quite amazing for a number of reasons I won't go into here. Check it out. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] overwritten iphone contacts
On 1/30/10 19:21 , Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ishak kalkavan wrote: file I could see phone numbers and contact names but in a very complex way. I couldnt find any way to match those data. The file format is documented at http://www.sqlite.org/fileformat.html Hmm, Apple defines an official API to access the address book data. Rather than the hacks suggested here, I'd use that instead (short of normally using a back up). The API is all freely documented on line. For example, you can start at: http://developer.apple.com/iphone/library/documentation/ContactData/Conceptu al/AddressBookProgrammingGuideforiPhone/100-Introduction/Introduction.html Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On 12/15/09 13:16 , Florian Schricker fschric...@gmail.com wrote: Please excuse me for being so blunt: You have no idea what I'm supposed to do here nor do you have any idea for whatever historic reason the database or table is designed as is here nor (and finally) do you have no idea about my education on databases or SQL or SQLite in general or in detail so please(!) stop a) implying I have no idea whatsoever on what I'm doing b) suggesting me to go read a book. These actions of yours are rude, not helping in any way and imply I'm a fool in what I'm doing and a fool to ask. Well, if the kind people on this list took the time to answer a question *you* asked while having no idea what you're supposed to do, maybe the wrong is with *you*. What kind of business have you asking questions here and then going after people because they don't have any idea what *you* are supposed to do? Why didn't it cross *your* mind that maybe *you* could have provided the necessary background for *your* question? Why didn't it cross *your* mind that if people give *you* suggestions to read a beginners book, then maybe it's because *your* question didn't demonstrate any knowledge that may let them infer otherwise? So please come down from your high horses. People here are a lot more knowledgeable than I am, and as far as I can see from *your* prose alone, than you as well. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: The sqlite3 shell does not call setlocale
On 12/13/09 16:34 , Jay A. Kreibich j...@kreibi.ch wrote: On Sun, Dec 13, 2009 at 01:47:43PM +0300, Alexey Pechnikov scratched on the wall: On Sunday 13 December 2009 02:09:48 Roger Binns wrote: The libsqlite is locale-independent but sqlite3 shell linked with readline and it's locale-dependent. That's a user choice. It does not link with readline by default. The rest of this topic is way over my head, but I don't get this argument. Basically it seems to me it goes like that: Alexei: SQLite has a bug when use with optional feature X Jay: that's a user choice. Feature X is not on by default. So Jay, what you are saying is that SQLite doesn't have to provide bug-free features when those features are optional? Did I read you correctly? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: The sqlite3 shell does not call setlocale
On 12/14/09 16:56 , Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Dec 14, 2009 at 10:41:04AM +0100, Jean-Denis Muys scratched on the wall: So Jay, what you are saying is that SQLite doesn't have to provide bug-free features when those features are optional? [snip] So, yes, I'm saying, and consider it acceptable, that the SQLite team does not have to provide bug-free features when those features are optional-- at least if they state the fact that the features are optional and unsupported. That is, in effect, what unsupported means. Ok, I think I get your position better, and I agree with it, about unsupported features. So the question is not so much whether readline support is optional, than whether it's supported or not. (I don't know). Regarding Alexei's request, would their be any downside to the addition of the single line he submitted? If not, then, rather than a bug fix, it could be considered an improvement. What I'm getting at is that I've seen arguments that the requested addition might in some (many?, most?) cases not be necessary, but not that it would be harmful. So would it? If no harm is expected, beside some changes to an optional, unsupported feature, then perhaps it should be considered. The fact that the shell is out of the main library should possibly make it easier to improve without going through an extensive process. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running out of space
On 11/25/09 10:50 , Simon Slavin slav...@bigfraud.org wrote: The message is that if you are short of space it is already too late for any software to cope with the problem. I disagree. It all depends on where you set the threshold for short of space. To give you a trivial example, if I set the threshold to 2GB on *my* machine for *my* application, then I will never be at risk. But the correct threshold depends on several factors: - you host platform, mainly its OS, but also any services running on it. - your application usage patterns, which I cannot speculate on. - the internal needs of SQLite during space scavenging, which are knowable though I don't know them. They themselves might depend on your application usage patterns. The threshold might be difficult to determine, and might not even be a constant, but a function of your current data set size. For example, setting the threshold to the triple of your current database size might be enough - or totally overkill. Here is how I would tackle this issue, through experimenting. I would start by determining what kind of function of the dataset size the threshold it. Whatever it is, it can be approximated by an affine function for small dataset sizes: T(N) = a+b*N Where T is the threshold, and N is the dataset size. We first need an upper bound for the constant a. Note that all we need is an upper bound. More generally speaking, you can always write: T(N) = T(0)+T'(N) where T'(0)=0, where a=T(0). 1- To assess a=T(0) first build a small -but not empty- dataset, and let your system and your application run*. Then artificially deplete the available disk space to zero, for example by storing on it a dummy file. Then run your scavenging procedure. If it runs OK, then you can use a = 0 (though I'd still use some value 0). But it's possible (likely?) it will fail because it hasn't got enough disk space. So release some increasing disk space until your scavenging procedure succeeds (you can use a dichotomy procedure for example). When it succeeds, the initial free disk space you had to set aside can be used as T0, an upper bound for T(0). (*) this procedure assumes your application is the only one depleting disk space. If that's not the case, then you need to take the other consumers into consideration. 2- Of course experimenting with a small data set is not good enough since you want to handle a situation that occurs with large data sets by definition! So repeat step 1 with a series of datasets of increasing sizes: N1, N2, N3, N4... (I would typically double the size at each step). Using N1, you will get an upper bound T1 for T(N1). Using N2, you will get an upper bound T2 for T(N2). And so on. Eventually, you'll have a set of points for a working T(N). You can fit a function to that set of points: - T1 will let you determine whether the threshold can be constant or whether it needs to have some multiple of the dataset size. - T2 will let you determine whether an affine function is enough to represent T(N) or whether you need something more sophisticated. - T3 and the others will let you see whether a low-order polynomial function is enough or whether you need to go to an exponential function. I refuse to consider the possibility that an exponential function would not even be enough :-) Of course how far you want to work through all this depends on how certain you want to be of your system resilience under low storage space conditions. This is how I would do it. Did I miss anything? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data converting
On 11/24/09 14:08 , Igor Tandetnik itandet...@mvps.org wrote: Renato Oliveira wrote: Hello, I'm new to SQLite, and need a query to convert a number of int format to format date and date format to format int. In PostGres the query is as follows: SELECT 14652:: bit (16)) 9): int + 1980) | | '-' | | ((14652:: bit (16) B'0000 ') 5):: int | |' - '| | (14652:: bit (16) B'0001 '):: int):: date; select ((t 9) + 1980) || '-' || ((t 5) 15) || '-' || (t 4) from (select 14652 as t); Shouldn't the last mask be 31 instead of 4? JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
On 11/19/09 14:55 , Tim Romano tim.rom...@yahoo.com wrote: The app was written in .NET against MS-Access; my Macintosh colleagues couldn't use it. They outnumbered the Windows users. But I didn't own a Mac and had never programmed on a Mac. But now Adobe Flex/AIR with SQLite is available, and it offers cross-platform deployment. So I've rewritten the app in Flex ActionScript using SQLite as the back-end. Tim, I have absolutely no opinion on flip(), neither for nor against. However, I do have an opinion on using Adobe Flex or Microsoft Silverlight as cross-platform tools to bring software to the Mac. In one word: bad. These tools will not let you easily write an app that feels native on the Mac. Your colleagues might use it if forced to, exactly as they could use your previous .net app using a virtual machine, but they are likely to drag their feet. Additionally, there are very serious efficiency/resource consumption issues with the Mac version of Flash/Air on the Mac. I don't think there is a satisfactory answer to cross platform development of native GUI apps. The best you can do is possibly a web app. With such tools as client-side databases, HTML 5 and Capuccino, you might end-up not too far from a native look and feel. Also modern Javascript JIT compilers will let your write your own fast flip() function. All this IMHO of course. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is ticket 4037 not a bug?
On 11/17/09 6:10 , Bogdan Ureche bogdan...@gmail.com wrote: 1. Is this a bug or not? If not, any reason why not? 2. Are other databases supporting this syntax? 3. Is this an invalid syntax according to the SQL standard? 4. Is there a workaround? I would appreciate any help. Bogdan Ureche I haven't got the slightest idea :-) However let me waste a little bandwidth to express that I am really impressed by how well written you question is, all the more so if English is a third language (but this is largely not about English). I'll probably keep it as an example on how to ask questions. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 11/13/09 6:31 , Dan Bishop danbisho...@gmail.com wrote: Microsoft Excel has a similar problem. I ran into it back when I was working in a credit union and tried to import a CSV file containing credit card numbers. Wouldn't have noticed except that credit card numbers are 16 digits long and double only has 15 digits of precision. Well in that case and for once, I must say the problem is not Excel's, it's yours: credit card numbers are not numbers at all, but strings, that just happen to use digits only. You should have imported them as text, not as numbers. Excel had no way to guess. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some clarification needed about Unicode
On 10/29/09 10:51 , John Crenshaw johncrens...@priacta.com wrote: 2. UTF-8 is NOT the same as ASCII for values greater than 127. ASCII only uses 7 bits values, so no larger representation can be the same as ASCII for values greater than 127. This may be seen as nit picking, but when discussing character encodings and representations, the issues can become so subtil and confusing, that it's very important to be as rigorous as possible. I've been bitten too many times, and sometimes I'm not 100% sure whether I really know what I'm talking about. Or possibly,I don't know what I'm talking about at all, and I'm the only one to be confused. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some clarification needed about Unicode
On 10/29/09 12:55 , A.J.Millan a...@zator.com wrote: Now, do you know about some library to conver to and from UTF-8 or UTF-16 to UCS-2? [4-1b] convert with WideCharToMultiByte(CP_UTF8) On 10/29/09 12:51 , Igor Tandetnik itandet...@mvps.org wrote: You can use WideCharToMultiByte(CP_UTF8) - I don't quite see why you find it so offensive. You can use a third-party library if you are so inclined, e.g. ICU. You can roll your own - a conversion between UTF-8 and UTF-16 is pretty straightforward. Or, you can simply use SQLite's 16-bit API (e.g. sqlite3_bind_text16) and have it convert to UTF-8 for you automatically. I don't use WideCharToMultiByte: it's windows only. If you don't want to go to the full ICU, I found that the Unicode.org implementation is: - correct (AFAICS) - light weight (much more so than ICU) - efficient - simple to use - although not very well known/documented/publicized It's at: http://www.unicode.org/Public/PROGRAMS/CVTUTF/ You want the two files ConvertUTF.h and ConvertUTF.c The documentation is in the comments. So your step 4-1b becomes: [4-1b] convert with ConvertUTF16toUTF8() Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New open source data synchronization tool
On 10/9/09 10:54 , Cjkalbente jkalbe...@yahoo.com wrote: If you need an ETL open source program, best thing is to check Talend Open Studio. It is a practical and easily learnable program for ETL, data migration and synchronization. The bug reporting, the community and the forum are active. Go look at: http://www.talend.com/ http://www.talend.com/ Except that the install procedure is sooo bad on the Mac it makes you wonder whether *that* level of software quality pertains to the rest of the product. On the other hand, I don't know of any other free ETL solution. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Synchronising multiple copies of a database
On 10/7/09 21:35 , Adam DeVita adev...@verifeye.com wrote: One can also get a mess if Mr. Red and Mr Black both get new customers, and enter them and they both get the same ID because the auto-generated int happens to be the same. Both copies get updated with the other guy's data, they then get annoyed and enter the stuff again and it happens over again, but now there are N entries of the other guy's customer in the database depending on how many times they do it. I solve this problem the following way: New records ID is not set by the local client who creates the new record, but by the central database. Upon new record creation a trigger is run that sends the new record to the central database. The central database then allocates the new id (and revision stamp) to the new record and returns them to the trigger which inserts them back into to local repository. This only works in an online scenario clearly. An alternative is to guarantee uniqueness across clients by making sure the newly allocated id is a composite that includes the creator client identity. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Retrieved]Re: DB Corruption
On 10/6/09 15:47 , Simon Slavin slav...@hearsay.demon.co.uk wrote: On 5 Oct 2009, at 2:59pm, Reusche, Andrew wrote: There are very many problems associated with synchronising two copies of the same database. I'm serious: entire books are written about just this one subject. I am currently facing a similar problem, and I was interested by this. Unfortunately a cursory search of Amazon or Google did not uncover such a book. Would you maybe suggest a reference? The problem I am currently tackling is the following: 1- I have p=1 (will always stays 1) central server running under mySQL v5.x. The use of mySQL is currently a given, but the server might in the future migrate to something else. I have no visibility or control over that, so let's keep mySQL in this context. 2- I have n (currently n=1, but it is likely to grow soon so assume n1) core clients applications using a SQLite database to store their data. 3- I have p (currently p=0, it may or may not grow) secondary clients that might modify the data on the central server directly. The foreseen use case is a web application using PHP or some such. The central server is supposed to be the master reference. All clients are responsible for keeping up to date with the master server. I control: the core client application and I can impose any set of rules to any future secondary client. I also control the data models both on the core clients and on the server to the extent that I can add tables and columns to the client's domain data. I also may be able to impose constraints on the client's domain data if there are good reasons. So far my solution (currently about 80% done, but I expect the devil to show up in the last few %) basically does: - add two columns to all domain tables in the client. One, central_id is storing a central ID that is set by the server. Any record where central_id=0 is thus only local and needs to be sent to the server. The second, stamp is a modification stamp maintained by the server. Given two instances of the same record (where central_id is equal), the current one is the one with the higher stamp. Stamp can be implemented with a revision count or a time stamp or whatever. - replicate the domain tables to the central server with the same columns. An additional boolean column is added on the server only to be used as a deleted marker. - upon insert of a new record locally in one client my code sends it to the central database where both the remote_id and the stamp are set and returned to the local client, who stores them locally also. This is conceptually an on insert trigger. - upon modification of an existing record locally, the modifications are pushed to the central database where the stamp is incremented and returned to the client where is modified too. This is conceptually an on update trigger. - upon deletion of an existing record locally, the record is actually deleted from the client data, but it is only marked as deleted in the server. Its central_id is not made available. The data stays there but becomes invisible to the normal client behaviour since all queries in effect have an and not deleted condition. This is conceptually an on delete trigger. Management of central_id and stamp on the server is done purely on the server using mySQL autoincrement and triggers. Their management on the client is done by my code. - on a regular basis by polling AND before any action started by the user which might use any existing data (basically everything except a pure creation from scratch), bring the local database up to date with in essence a query similar to: SELECT * FROM aTable WHERE stamp last_sync_stamp. Of course, last_sync_stamp is maintained locally per table. That's it and it's all very naïve. I'd appreciate some feedback here or pointers to litterature. Many thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Synchronising multiple copies of a database
On 10/7/09 11:50 , Simon Slavin slav...@hearsay.demon.co.uk wrote: On 7 Oct 2009, at 10:13am, Jean-Denis Muys wrote: [setup description] I'd appreciate some feedback here or pointers to litterature. Try really really hard just to have all sites access your MySQL database remotely. Unfortunately this approach is not possible in the short term. The client applications are legacy applications, porting them to that scheme is a major undertaking. I need to insert my code within the legacy applications only in a way that as little invasive as possible. This trigger/polling approach was deemed a fair compromise. Overtime, new appls will probably be developped that work directly against the central database, and the legacy apps will be phased out progressively. Also those legacy apps also work when offline, which is a strong incentive to keep them (and I know this opens a new kind of can of worms). Finaly the central server is one or two stellar systems away, with bad latency and throughput. The local data repository in a way acts as a cache to keep the apps responsive. Keep a journal. Keep an unaltered central copy of the data. As each site contacts the central site, play that sites journal back against the unaltered central copy. The post-journal central copy of the database becomes the new copy for distribution. Interesting idea, that makes a lot of sense in the offline scenario. [snip scary scenarios] If your system deals with those, it's most of the way there. I'll have to handle those cases very carefuly. Clearly, when two clients modify the global state in an incompatible way, some kind of conflict resolution must happen (similar to what Version Control Systems do). The proviso here is to make sure these conflicts are at least detected. I need to think about all this some more. Thanks a lot for your very valuable feedback. In any case, any book reference on this topic? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Passing all columns as arguments to a function?
Hi, Is it possible to pass all columns of a table as arguments to a function in a implicit way? I tried this but it did not work: Create table studentMarks (French, English, Maths, Physics); Insert into studentMarks values (12, 9, 15, 14); Insert into studentMarks values (14, 13, 12, 8); Select max(*) from studentMarks where French 13; I could always iterate from my code and build an explicit argument list for the max function, but I'm lazy and I'm intrigued... Is there a way to do this? * is a strange animal in SQL. Sometimes it means all rows as in: Select count(*)... Sometimes it means all columns as in: Select * from table where... Thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite - text files are not really portable
On 9/8/09 22:24 , various people wrote: Unfortunately, the 3 main families of small computer operating systems have 3 different definitions of what a text file is... DOS/Windows (PC): lines are terminated with CR+LF Unix: lines are terminated with LF Macintosh: lines are terminated with CR [...] FYI: Mac excel does not separate rows with \r, but inserts a ^M instead. [...] From: Kavita Raghunathan kavita.raghunat...@skyfiber.com Yes, this works. Must have been my original csv file. I was using mac based excel and I'll now try using the windows based excel. For the record, the Mac has not been using CR line terminations for many years now (2001). Microsoft, in its not very high wisdom, proposes in the latest Mac version of Excel, 4 export formats that might correspond, with useless names: 1- the main format at the top of the menu is named Comma Separated Values (.csv) It's a comma -separated, CR-terminated format, with characters encoded in MacRoman ( http://en.wikipedia.org/wiki/Mac_OS_Roman). As both line-termination and character-encoding conventions have stopped being used for many years, it's despicable from Micros__t to continue to name it as they do, and to propose it as the main choice. 2- secondary format named Tab delimited text (.txt) Same as 1-, except the separator is now a tab character. Totally useless format 3- secondary format named Windows Comma-separated (.csv) Line terminations: CR-LF (Windows convention) Character encoding: CP1502 (Windows extension to ISO-8859-1) This is the most useful format, as it's likely to work for interoperability with the Windows world. 4- secondary format named MS-Dos Comma-separated (.csv) Line terminations: CR Character encoding: CP850 ( http://en.wikipedia.org/wiki/CP850) A totally obsolete variant. Notably missing would be for example the *current* version: line terminated with LF and UTF8 encoding. I will stop there. Ranting on MS is bad for my nerves. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] having problem in creating new database in MAC OS
On 8/21/09 14:18 , shakeeb khan khanshak...@msn.com wrote: tell me what is a proper way of creating database in sqllite Here is a sample terminal session on my Mac (possibly very poorly reformatted by my mail client): jdmbook:~ klee$ sqlite3 mydb.db SQLite version 3.6.12 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .databases seq name file --- --- -- 0main /Volumes/Prune/Users/klee/mydb.db sqlite create table test (id integer, name char(256), tel char(32)); sqlite select * from test; sqlite insert into test (id, name, tel) values (1, 'John Doe', '555-1234-5678'); sqlite select * from test; 1|John Doe|555-1234-5678 sqlite I hope that helps, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad CPU type in executable?
On Aug 7, 2009, at 1:26 PM, Hamish Allan wrote: On Wed, Aug 5, 2009 at 8:20 PM, Jean-Denis Muysjdmuys...@free.fr wrote: No I don't (even though I don't speak English natively). If you google top posting evil you will realize that evil has been associated with top posting for longer than I can remember. And Natalie Portman has been associated with hot grits for longer than I care to remember. An appeal to the intarwebz does not a point make. Hamish The point is made many times by many of those references found with Google. and the appeal to the web clearly invalidates the claim that I don't know the meaning of evil, at least in this context. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad CPU type in executable?
On Aug 5, 2009, at 8:32 PM, Hamish Allan wrote: On Wed, Aug 5, 2009 at 5:50 PM, Jean-Denis Muysjdm...@kleegroup.com wrote: You're top-posting, it's evil, the thread is becoming messy. You need to look up the word evil sometime. No I don't (even though I don't speak English natively). If you google top posting evil you will realize that evil has been associated with top posting for longer than I can remember. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad CPU type in executable?
On 8/5/09 17:48 , Jimmy Verner jver...@vernerlegal.com wrote: A developer built an iPhone app for me. I am exploring using it as a template for another app. The data file is called libsqlite3.0.dylib. When I try to open the file, I receive this message: /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS3.0.sdk/ usr/lib/libsqlite3.dylib ; exit; [76-209-121-92:~] jimmyverner% /Developer/Platforms/iPhoneOS.platform/ Developer/SDKs/iPhoneOS3.0.sdk/usr/lib/libsqlite3.dylib ; exit; /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS3.0.sdk/ usr/lib/libsqlite3.dylib: Bad CPU type in executable. logout [Process completed] How do I open this file? Running OS X 10.5.7, Xcode 3.1.3. A dylib file is not a data file, but a dynamic library, similar to a .dll file under Windows. It must be linked against in you Xcode project. The path you give suggests that dynamic library is coming with the standard iPhone SDK. How did you try to open it, as you said? If indeed you need to open the data (ie database) file, then you need another file, possibly with a .db extension. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad CPU type in executable?
On 8/5/09 18:40 , Jimmy Verner jver...@vernerlegal.com wrote: Thx. for your note. The dylib file is visible only when the app is is opened with xcode. I then clicked on the file. The Terminal window opened and I received the message set forth below. There is a db3 file within the app. When I try to open the db3 file in xcode, nothing happens. The db3 file is visible in Finder. When I try to open it there, Finder says there is no default app with which to open it. I am able to open it with Text Wrangler where there is much gibberish but also the data. The file begins with SQ Lite format 3 and then includes setup commands to set up a table. I now understand that a db3 file is a dBase III file. Perhaps you could point me to some resources so I can learn about dBase III files and how they interact with dylib files? Thx. again. Jimmy Verner www.vernerlegal.com On Aug 5, 2009, at 11:22 AM, Jean-Denis Muys wrote: On 8/5/09 17:48 , Jimmy Verner jver...@vernerlegal.com wrote: A developer built an iPhone app for me. I am exploring using it as a template for another app. The data file is called libsqlite3.0.dylib. When I try to open the file, I receive this message: /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/ iPhoneOS3.0.sdk/ usr/lib/libsqlite3.dylib ; exit; [76-209-121-92:~] jimmyverner% /Developer/Platforms/ iPhoneOS.platform/ Developer/SDKs/iPhoneOS3.0.sdk/usr/lib/libsqlite3.dylib ; exit; /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/ iPhoneOS3.0.sdk/ usr/lib/libsqlite3.dylib: Bad CPU type in executable. logout [Process completed] How do I open this file? Running OS X 10.5.7, Xcode 3.1.3. A dylib file is not a data file, but a dynamic library, similar to a .dll file under Windows. It must be linked against in you Xcode project. The path you give suggests that dynamic library is coming with the standard iPhone SDK. How did you try to open it, as you said? If indeed you need to open the data (ie database) file, then you need another file, possibly with a .db extension. Jean-Denis You're top-posting, it's evil, the thread is becoming messy. That said... It's apparent your DB3 file is the SQLite file you are looking for, not a Dbase III file. To open it, you can use any utility that can open a SQLite file. I personnaly use MesaSQLite. Or you can use the SQLite3 command line utility that's included with your Mac. Open a terminal and type something like sqlite3 /path/to/mydb.db3 Then you can use all the SQLite commands. I hope that let's you get started. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Relation?
On 7/27/09 16:33 , CityDev nab...@recitel.net wrote: it's not easy to track the origin of the term in mathematics. For what it's worth (ie probably not much), my formal mathematics training in set theory taught me that a relation from a set A to a set B is a subset R of the cartesian product AxB. The special cases on A, B and R lead to further definitions and properties, eventually leading to group and field theory. I would have to go back to Evariste Galois' work to check whether he already used the term relation though. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
On 7/9/09 6:21 , Rick Ratchford r...@amazingaccuracy.com wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 While this returned the correct answers: 1988 2008 What I don't understand is why it didn't simply return: 1988 1988 Since there is at least 15 or more days in Month=1 (Jan). Anyone? Warning Noobie here /Warning I think the min and max functions by design return only one value. However, I'm afraid this query might not work in the general case. For example, if your data starts on january 20, 1988, then this query will think 1988 is a full year. Ditto if the data ends early in december. So it seems to me that it works for you by luck, because your data set doesn't start in january, nor ends in december. Unless of course if I am totally wrong, which happens often enough. Jean-Denis Muys ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?
On 7/7/09 8:47 , Nicolas Williams nicolas.willi...@sun.com wrote: On Tue, Jul 07, 2009 at 12:17:36AM +0100, Simon Slavin wrote: [...] This is way off-topic now, but, to be fair, RFC1855 is not a standard, it is an Informational status RFC -- it provides information. It possibly is never off-topic to educate and to tirelessly explain why top-posting is evil. Somebody on the this list has a nice signature going a bit like: A Because we read top to bottom Q Why is top posting evil? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting offending constraint
Wow, I have been on the internet since 1986, and I had never realized that this could be a problem. I am often guilty of the same, for lazyness reasons, as this is a convenient way to avoid having to reenter the to, cc, and bcc fields. I went back to James' original message, which my mail client definitely does NOT show as a followup to anything. But when I examine its raw headers, I found this one: In-reply-to: b537e3a60907011151q563194e5g184c8f2cbd68d...@mail.gmail.com Is this the header that made you point your finger? In any case, I learned something today, and though this is off-topic here, it may be worth going to the bottom of this. Jean-Denis On 7/1/09 22:41 , P Kishor punk.k...@gmail.com wrote: On Wed, Jul 1, 2009 at 3:39 PM, James Gregurichbayouben...@mac.com wrote: How would I have hijacked a thread? I changed the subject and removed the original text. ... that is exactly how a thread is hijacked... changing the subject is not enough. Every message has a unique id that is used by the mail programs to keep track of threading. ___ 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] sqlite programmed in C++
This is funny how language trends get interesting and sometimes a bit emotional too. We all have an opinion on them, and here is mine :-) The basic idea going around in this thread is a very conservative C is best because 1) it's universally portable, and/or 2) it's very close to the machine (ie low level). These two criteria clearly apply to library software (such as SQLite), while for client (in a very general sense) software a totally different trend has been going on for a number of years: a trend towards higher level, farther from the machine, languages. This started back in the night of times, but C++ as a better C than C is certainly part of that trend. Today that trend is more or less at a stage where languages must manage memory: Objective-C is now garbage collected, as are Ruby, Python and Lua, as has been Java since some earlier time. Lua was the language used to program [most of] Adobe Lightroom, and [a large part of] WoW. MacRuby is looking to become a first class Cocoa citizen. .Net is part of that trend too. Portability has at times been an issue there too. Java tackled it, and to some extent, so have Ruby and Python which have become largely universally portable, at least to machines with a minimum level of resources (excluding a number of embedded systems). These trends have thus far not been converging very much. I am convinced though that over time, the trend for higher level languages will also include library software. From this perspective, I would be of the opinion, to some extent tongue-in-cheek, that C++ is far too low-level a language to warrant a rewrite of SQLite. Jean-Denis On 6/3/09 17:23 , John Stanton jo...@viacognis.com wrote: Indeed. Very good reply. To Sylvain, once again: speculating on what went into the minds of the developers, when they set out to develop SQLite, they chose the best, most concise, most portable, most universally compilable, mother of almost all languages. Once they developed something that was free, fast and cheap, there was no reason to change. Case closed. If you thing C++ can do a better job at doing what SQLite does on all the variety of platforms that it runs on flawlessly, well, the source code is available in public domain -- go ahead and create SQLite++ by transcribing each function into the language of your choice. May the better plan win. This is something of a digression but is pertinent. Colleagues who worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was disillusioned with C++ and its wide deployment and would encourage people not to use it unless there were clear advantages. In our own company we came to the same conclusion as Dr Hipp and used ANSI C for our compilers and database software. C can be anything you want it to be. For example you can ensure portability by incorporating your own memory management system and tightly manage your use of foreign libraries. for quality assurance You have access to highly optimizing compilers which can produce executables as good as those written by a skilled Assembler programmer. ___ 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] error in documentation of SELECT?
On 5/19/09 1:57 PM, Igor Tandetnik itandet...@mvps.org wrote: John Machin sjmac...@lexicon.net wrote in message news:4a129cb4.2090...@lexicon.net It's handy for checking how things work e.g. sqlite select (-1) % 7; -1 sqlite -- it's not a real modulo operator :-( What do you feel is wrong with this result? What should a real modulo operator return, in your opinion? Before you answer, note that you very likely want this equality to hold for all a, b!=0 : a = q*b + r where q = a/b, r = a%b Igor Tanetnik My math courses taught me a long time ago, that the remainder r in Euclidian division of a by b is defined by: There exists unique natural numbers q and r such as: a = b*q+r 0 = r b q is defined as the quotient, r is defined as the remainder. So if the % operator wants to match that math definition, its results should never be negative. In the example given, (-1)%7 should therefore be 6. But I will not argue that the % operator needs to match the math definition, though I would probably prefer it to. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
My memory failed me on a detail if I want to be rigorous. In the definition of q and r, r is a natural number, but q is a relative number, not a natural. On 5/19/09 2:06 PM, Jean-Denis Muys jdm...@kleegroup.com wrote: On 5/19/09 1:57 PM, Igor Tandetnik itandet...@mvps.org wrote: John Machin sjmac...@lexicon.net wrote in message news:4a129cb4.2090...@lexicon.net It's handy for checking how things work e.g. sqlite select (-1) % 7; -1 sqlite -- it's not a real modulo operator :-( What do you feel is wrong with this result? What should a real modulo operator return, in your opinion? Before you answer, note that you very likely want this equality to hold for all a, b!=0 : a = q*b + r where q = a/b, r = a%b Igor Tanetnik My math courses taught me a long time ago, that the remainder r in Euclidian division of a by b is defined by: There exists unique natural numbers q and r such as: a = b*q+r 0 = r b q is defined as the quotient, r is defined as the remainder. So if the % operator wants to match that math definition, its results should never be negative. In the example given, (-1)%7 should therefore be 6. But I will not argue that the % operator needs to match the math definition, though I would probably prefer it to. Jean-Denis ___ 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] error in documentation of SELECT?
On 5/19/09 2:44 PM, Igor Tandetnik itandet...@mvps.org wrote: Well then, for the equality to hold, (-1)/7 should be -1. Would you be happy with such an outcome? Yep Wikipedia gives a definition different from yours, for what it's worth: http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers http://en.wikipedia.org/wiki/Modulo_operation Well, I contend it doesn't. It says: −42 = 9×(−5) + 3 as is usual for mathematicians Which was my point. But as was pointed out, there are two choices, both equally valid. Once you decide that division truncates towards zero (as is the long-standing tradition in C and many other languages, for better or worse), you have no choice but to let the remainder take the sign of the dividend. True enough. It seems Computer Science has most of the time made the opposite choice than mathematicians. I can live with that too. Igor Tandetnik Jean-Denis Muys ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
On 5/19/09 2:44 PM, Igor Tandetnik itandet...@mvps.org wrote: Wikipedia gives a definition different from yours, for what it's worth: http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers Also to support my version, the same article says a bit later: Usually, in number theory, we always choose the positive remainder. While programming languages seems to make rather different choices: C99 and Pascal choose the remainder with the same sign as the dividend a. (Before C99, the C language allowed either choice.) Perl and Python choose the remainder with the same sign as the divisor d. Rejoice ! Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?
On 5/18/09 9:19 AM, Robert Villanoa robertvilla...@yahoo.com wrote: Hi everyone, I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2. Now I want to upgrade it to the latest version, 3.6.14, but I don't know how to do that. Here are the steps I have done (after reading another thread about this issue): 1. Remove the default version using 'sudo apt-get remove sqlite3'. 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package, go to the sqlite3 directory and run: - ./configure - make - sudo make install Although I did not encounter any error after executing these above commands, it seemed that I did not install sqlite3 3.6.14 successfully. Whenever I type 'sqlite3' into the GNOME terminal, it shows that the version is 3.4.2. What's wrong with me? Please help! Thanks in advanced! PATH problem? When you type which sqlite3 in the terminal, what is the result? Does it match your install location? JD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?
On 5/18/09 10:33 AM, Robert Villanoa robertvilla...@yahoo.com wrote: Thank you for your answer, Jean-Denis. When I type 'which sqlite3', I get the following location: /usr/local/bin/sqlite3 And the value of my PATH variable is: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games So I think the executable file sqlite3 is seen by PATH. Could you please tell me any more suggestions? My understanding is that /usr/local/bin/sqlite3 is the old version of sqlite, which obviously is reachable from your PATH. Now the interesting question is, where was the new version installed? Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Syntax for column names?
Hello, My create table statement (program-generated from a text file) below yields a syntax error. I went to SQL syntax diagrams, and as far as I could see, the table-name box is not detailed in its own diagram. I suspect SQLite doesn't like column names with two periods. It this it? Here is my statement: create table document (TC1.DOCUMENT.SORT TEXT,TC1.DOCUMENT.CLE_CATAL TEXT,TC1.DOCUMENT.DORIS_SUBKEY TEXT,TC1.DOCUMENT.DATE_CREAT TEXT,TC1.DOCUMENT.LIEN_CATAL TEXT,TC1.DOCUMENT.FICHIER TEXT,TC1.DOCUMENT.DATE_MAJ TEXT,TC1.DOCUMENT.DORIS_STAMP TEXT,TC1.DOCUMENT.DIRDOC TEXT,TC1.DOCUMENT.UTIL_CREAT TEXT,TC1.DOCUMENT.DRAWER TEXT,TC1.DOCUMENT.DORIS_KEY TEXT PRIMARY KEY,TC1.DOCUMENT.FOLDER TEXT,TC1.DOCUMENT.CLE_NUMPER TEXT,TC1.DOCUMENT.TYPE TEXT,TC1.DOCUMENT.DISPLAY TEXT,TC1.DOCUMENT.CATTABKEY TEXT); Thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
On 4/20/09 2:35 PM, J. King jk...@jkingweb.ca wrote: On Mon, 20 Apr 2009 06:53:37 -0400, D. Richard Hipp d...@hwaci.com wrote: http://wiki.tcl.tk/21708 It's a shame: I far preferred the BNF: more compact, not to mention you could copy and paste as well as search the text of the syntax itself. shame? Are you sure that's the word you wanted to use? I personally think those syntax diagrams are *better* than BNF (though no shame either way). - compactness: very weak argument, maybe even a strawman. - copy paste: the last time I wanted to copy paste the formal syntax of any language was... about right around when dinosaurs went extinct. - search: I don't think there is any valid or interesting search of BNF except for the keywords of the language, which *are* searchable with the syntax diagrams. Now *what are* the actual real-life uses of a formal specification of SQLite's grammar? Realistically, there are few: - look up the features of the language, typically to get a quick grasp of the features, or to compare with another dialect of SQL - check whether some [more or less obscure] construct is supported in the grammar - understand why some statement is rejected by SQLite (debugging). In all these read-only situations, you want the grammar to be very fast to read and understand. This is where syntax diagrams excel: the cognitive load to understand them is far less than BNF (for the majority of people). I personally am rather experienced with formal grammars and BNF, but a relative newbie with SQL and SQLite. I welcome the syntax diagrams. Regards, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with file locks on USB keys
On 4/3/09 5:20 AM, Dave Brown key8...@gmail.com wrote: Our software often runs into problems when run on USB keys. Specifically, calls to sqlite to read/write to the database hang infinitely, or return SQLITE_BUSY after even 10 seconds of waiting. The problem seems to be that the database file gets locked and remains locked, presumably by some problem with the usb key filesystem in conjunction with sqlite. Has anyone seen this before, or know of a workaround? I definitely haven't seen that, under MacOS X. I would assume this kind of issue to be highly OS and file-system dependent. You might want to tell us about your environment. MacOS X is a Unix OS, where external volumes such as USB drives are mounted within the / hierarchy (ditto for Linux). From SQLite point of view, there is really no way to tell whether the database is stored on a USB drive or elsewhere. But File System limitations remain. Most likely but not necessarilly (depending on its capacity), your USB thumb drives uses a FAT32 file system. You might want to try and reformat it to a more modern file system, such as HFS or NTFS. It might help. Or not. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 11:29 PM, erfon haubenstock m...@erfon.com wrote: Hi again. Thanks for trying to help me by the way : D I tried relauching terminal and even restarting my computer, but to no avail. I'm not totally sure I understand when you say: One common pitfall here is that you must open a new shell for the port command to be recognised (or re-export the correct PATH). This seems likely as the default location for the port command is /opt/local/bin/port, which is not in your PATH. Do I need to change my PATH? thanks! erfon OK. The short answer is STFW, namely http://lmgtfy.com/?q=macports+and+the+shell . Now let me expand on it for you. In the terminal, the 'which' command followed by another command will tell you *which* file is executed when you run it. For example for me: JDMBook:~ $ which sqlite3 /usr/bin/sqlite3 JDMBook:~ $ which port /opt/local/bin/port As you can see, sqlite3 is in /usr/bin/, while port is elsewhere, namely /opt/local/bin. Let's check with ls: JDMBook:~ $ ls -la /usr/bin/sq* -rwxr-xr-x 1 root wheel 94144 Dec 9 2007 /usr/bin/sqlite3 JDMBook:~ $ ls -la /opt/local/bin/p* -r-xr-xr-x 1 root admin 106140 Dec 14 03:42 /opt/local/bin/port lrwxr-xr-x 1 root admin 4 Feb 13 17:22 /opt/local/bin/portf - port -r-xr-xr-x 1 root admin4497 Dec 14 03:42 /opt/local/bin/portindex -r-xr-xr-x 1 root admin2557 Dec 14 03:42 /opt/local/bin/portmirror If I type any command in the terminal without its full path, the shell will look for the executable file in a number of places, which are determined by the PATH environment variable. Here is the value of PATH for me: JDMBook:~ $ echo $PATH /opt/local/bin:/opt/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/Volumes/Prune/Us ers/klee:/usr/local/bin:/usr/X11/bin Now, why is the shell able to run sqlite3 when I type 'sqlite3'? Because its directory /usr/bin/ is listed in my PATH. In the same way, the 'port' command works because its directory /opt/local/bin/ is listed in my PATH. So my configuration looks good. What about yours? I'm not sure because you only gave us your PATH. I noticed that your PATH does not include /opt/local/bin/. So what could explain that? Here are a few possibilities: - MacPorts is not installed at all. But you said you installed it. - MacPorts is not installed in its default directory. I don't know but you did not suggest anything like that. - MacPorts is not fully installed, where fully means including altering the PATH. Since you didn't tell us how you installed MacPorts, I can only guess and give a few suggestions: 1) Do you have the port command in its default location? To check that, type the command 'ls -la /opt/local/bin/' as above. If the answer to that question is yes, then you can see the shell *cannot* execute it because its location is not in your PATH. 2) Did you install MacPorts using the pkg in the disk image as http://www.macports.org/install.php suggests? If you did so, the installer should have updated your .profile shell startup script. As documented, that may not be good enough if you have a customized shell setup. In any case, here is my .profile: JDMBook:~ $ cat .profile # MacPorts Installer addition on 2009-02-13_at_17:22:24: adding an appropriate PATH variable for use with MacPorts. export PATH=/opt/local/bin:/opt/local/sbin:$PATH # Finished adapting your PATH environment variable for use with MacPorts. # MacPorts Installer addition on 2009-02-13_at_17:22:24: adding an appropriate MANPATH variable for use with MacPorts. export MANPATH=/opt/local/share/man:$MANPATH # Finished adapting your MANPATH environment variable for use with MacPorts. If you installed MacPorts differently, then the MacPorts documentation tells you that you need to manually do something along those lines. 3) Did you customize your terminal environment in any way? This could have been for example through .profile, .bashrc, .bash_login, .bash_profile etc... If so, you might have to adjust your settings manually as described in TFM. 4) Installation Assuming you have not changed anything in your terminal environment: Quit Terminal.app, then execute the MacPorts .pkg installer, then relaunch Terminal. Using the command shown above, verify that the port command exists in its default location, and that the default location is in your PATH. If not, alter your PATH as described. 5) Don't be shy. In my previous answers I suggested you use the which command. Don't be shy. Do it. If you expect to be helped, it's better to show that you tried a few things... So if you *did* it, report the output. Cheers, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/20/09 10:41 AM, erfon haubenstock m...@erfon.com wrote: Hey Jean-Denis! I think you may not have seen my most recent post : D I used the link you sent me (i think it was yours) that explained that the local .bash_login could be overriding my .profile. I copied the contents of .profile into my .bash_login and now my port command works just fine! I also figured out that my $PATH was missing the / from $PATH=/usr/... I had $PATH=usr/... which was causing my sqlite3 --version command to check the global install of sqlite3 at usr/bin instead of the usr/loca/bin. once i added the slash it all worked great! thanks for all your help! Yes sometimes, my mail server gets out of sync. I apologize for the untimely post. I'm glad you got everything in order. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 11:35 PM, P Kishor punk.k...@gmail.com wrote: why make things so difficult for yourself? download the amalgamation source code and untar/unzip it somwhere. type ./configure followed by make sudo make install this will put your sqlite libraries under /usr/local/ type /usr/local/bin/sqlite3 -version and see if you get 3.6.11 then type sqlite3 -version. If you still get 3.4.0, your path is wrong. Google for how to set the correct path for your shell. The whole operation should take about 15 mins from start to finish (and that includes the initial download times). don't screw around with macports and fink and other such complications when they are not needed. May I humbly suggest that your way is both more difficult and less general than using MacPorts? With MacPorts installed, it's one command: sudo port install sqlite3 That's it. Granted, if MacPorts is not installed, you have to install it first. It implies modifying the PATH once, and even then, in most cases, the installer does it for you. I will not lecture on the many other advantages of MacPorts. Maybe only one thing. To keep all installed ports up to date, simply do: sudo port selfupdate I don't use Fink, but I suspect this applies to Fink too. Respectfully, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 11:07 AM, erfon haubenstock m...@erfon.com wrote: Hello Jean-Denis! I did it manually in terminal. I'm not familiar with macports or finks. Here's the exact code i used: $ curl http://www.sqlite.org/sqlite-3.6.11.tar.gz | tar zx $ cd sqlite-3.6.11 $ ./configure --prefix=/usr/local $ make $ sudo make install Then, without trying to replicate this, I suspect you have an issue with your PATH. In my path, /usr/bin precedes /usr/local/bin. Echo $PATH and which sqlite3 are your friends. Handling this kind of issues transparently is one of the benefits of package distribution facilities such as MacPorts and Fink. I personally use MacPorts. See http://www.macports.org/. It has SQLite 3.6.11 as an available port. I hope that helps. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 10:28 AM, erfon haubenstock m...@erfon.com wrote: This is really strange. I'm using a mac with leopard which comes with version 3.4.0 installed. I've installed 3.6.11, and it installs successfully as far as I can tell, but then when I check the version with sqlite3 --version in terminal, the version is shows that the installed version is STILL 3.4.0. Please help, this is driving me insane! Maybe you could tell us a bit more (see http://catb.org/~esr/faqs/smart-questions.html): - how did you install v 3.6.11? Using MacPorts, or Finks? Or manually? - what did you try to understand and fix the issue? - did you STFW? - what is the output of 'which sqlite3'? - did you check your PATH? Eager to help... Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 12:20 PM, erfon haubenstock m...@erfon.com wrote: EEESH, i installed macports and when i try to port install i get an error that says the error port: command not found maybe this has something to do with it? my $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb in:/sbin:/usr/local/bin:/usr/X11/bin One common pitfall here is that you must open a new shell for the port command to be recognised (or re-export the correct PATH). This seems likely as the default location for the port command is /opt/local/bin/port, which is not in your PATH. If it's there, it'll probably be as easy as quitting and relaunching terminal. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In-memory databases from Perl
Hello, I am quite a newbie but I already spent a significant amount of time on Google, to no avail. I have a very small Perl program which exercises the basic SQLite3 features, and it works very well. I connect from Perl to SQLite3 through DBI, because it's a no brainer. I don't know whether there are alternatives. Here is the connect statement: my $dbh = DBI-connect(dbi:SQLite:dbname=DBItest.db,, ); Now I want to use an in-memory database, so I replaced my original connect statement with the one suggested at http://www.sqlite.org/cvstrac/wiki?p=PerlNotes : my $dbh = DBI-Connect(dbi:SQLite:dbname=:memory:); However, this doesn't work, and I crash on that connect statement with the following error message: Can't locate auto/DBI/Connect.al in @INC (@INC contains: /Volumes/BLeopard/Applications/TextMate.app/Contents/SharedSupport/Bundles/P erl.tmbundle/Support /System/Library/Perl/5.8.8/darwin-thread-multi-2level /System/Library/Perl/5.8.8 /Library/Perl/5.8.8/darwin-thread-multi-2level /Library/Perl/5.8.8 /Library/Perl /Network/Library/Perl/5.8.8/darwin-thread-multi-2level /Network/Library/Perl/5.8.8 /Network/Library/Perl /System/Library/Perl/Extras/5.8.8/darwin-thread-multi-2level /System/Library/Perl/Extras/5.8.8 /Library/Perl/5.8.6 /Library/Perl/5.8.1 .) at /Volumes/Prune/DBItest.pl line 26 To me the error message is rather puzzling as the exact same code works in the case of a filename with no colons. Maybe the colons are screwing the connect statement, since the connect statement itself uses colons as a separator? Either way, everything I tried failed, including silly things like varying additional connect parameters or string delimitors. Does DBI support in-memory SQLite3 databases? If so, how? Otherwise, are there any alternative? I use SQLite3 version 3.4.0. Please note that using :memory: from the command line sqlite3 tool works fine. My configuration: MacOS X 10.5.6, Perl 5.8.8, DBI 1.6.7. Please note that I also posted a similar request to comp.lang.perl.misc, as I am quite unsure of where best to go for help. If anybody out there had any clue, I'd be very grateful. Many many thanks, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In-memory databases from Perl
On 3/16/09 3:03 PM, Jean-Denis Muys jdm...@kleegroup.com wrote: Now I want to use an in-memory database, so I replaced my original connect statement with the one suggested at http://www.sqlite.org/cvstrac/wiki?p=PerlNotes : my $dbh = DBI-Connect(dbi:SQLite:dbname=:memory:); However, this doesn't work, and I crash on that connect statement with the following error message: Silly me. I just realized that I wrote Connect instead of connect I apologize for the waste of bandwidth and of your attention. Sigh... Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In-memory databases from Perl
On 3/16/09 3:10 PM, P Kishor punk.k...@gmail.com wrote: On Mon, Mar 16, 2009 at 9:03 AM, Jean-Denis Muys jdm...@kleegroup.com wrote: Now I want to use an in-memory database, so I replaced my original connect statement with the one suggested at http://www.sqlite.org/cvstrac/wiki?p=PerlNotes : my $dbh = DBI-Connect(dbi:SQLite:dbname=:memory:); However, this doesn't work, and I crash on that connect statement with the following error message: DBI-connect() note the lowercase connect() Precisely. It may be worth noting that I was misled by the wiki entry which has the same mistake. See http://www.sqlite.org/cvstrac/wiki?p=PerlNotes Thank you very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In-memory databases from Perl
On 3/16/09 3:24 PM, P Kishor punk.k...@gmail.com wrote: Jean-Denis, do consider correcting the wiki so others after you are not similarly misled. Done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
