[sqlite] non-integer rowid/document id
Excerpts from jeremy's message of 2015-12-14 11:09:48 -0700: > Hi all, > > I'm working with FTS5 and I'd like to guarantee that a particular document is > indexed only once. I have what I consider to be a document id, but it is not > an > integer value its a hexadecimal string, think GUID/md5/sha1. Since the fts5 > rowid column is an integer, it appears I'll need to create a mapping from my > document id to an fts5 rowid. Don't need a mapping use your key and ensure your key is unique. > > I'm thinking the best method for me to resolve this would to just use an > external content table with triggers to update FTS5 table. Pretty much exactly > like https://sqlite.org/fts5.html#section_4_4_2 and use triggers to keep the > FTS5 table in sync with the external content table. Using that solution (the best I think) just combine: -- The external table CREATE TABLE tbl (key TEXT UNIQUE, content, id INTEGER PRIMARY KEY); -- The virtual table CREATE VIRTUAL TABLE fts USING fts5(key, content, content=tbl, content_rowid=id); > > If I'm going to have to use an external table to create a rowid for the > fts5 table, I might as well use use the external content table. > > Any additional thoughts on this from the more knowledgeable? > > enjoy, > > -jeremy > > --? > > Jeremy Hinegarnder > Copious Free Time, LLC -- Marco Arthur @ (M)arco Creatives
[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?
Excerpts from Daniel Polski's message of 2015-09-24 07:38:24 -0300: > Hello! > > --Table t1 contains some "base" data > CREATE TABLE t1 ( > idINTEGER PRIMARY KEY, > name VARCHAR(10), > value INT NOT NULL > ); > > INSERT INTO t1 VALUES(1, 't1 1', 0); > INSERT INTO t1 VALUES(2, 't1 2', 0); > > --Table t2 can contain extra data for a row in table t1 > CREATE TABLE t2( > id INTEGER PRIMARY KEY, > t1_idINT NOT NULL, > dataINT NOT NULL, > CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id ) > ); > INSERT INTO t2 VALUES(1, 1, 10); > INSERT INTO t2 VALUES(2, 1, 20); > INSERT INTO t2 VALUES(3, 2, 30); > > CREATE TABLE t3 ( > idINTEGER PRIMARY KEY, > name VARCHAR(10) > ); > > CREATE TABLE t4( > t2_idINT NOT NULL, > t3_idINT NOT NULL, > CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ), > CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id ) > ); > > > > -- Here is where my real question starts. Can I fetch a list of rows in > a trigger > -- and update different tables from that data? Im looking for some kind > -- of looping over the rows found in t2, where I can insert data to t3 and > -- t4 for each row found in t2. > > CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1 > WHEN new.value = 1 > AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id) > AND (some more rules) > ... > -- For each row in t2 with a t1_id referencing the t1 id, > -- 1: insert a row into t3 > -- 2: insert a row into t4 binding the newly created row in t3 to the > corresponding row in t2 may try this to insert into t3. CREATE TRIGGER trigger_on_table_1 AFTER UPDATE OF value ON t1 FOR EACH ROW WHEN NEW.VALUE = 1 AND EXISTS (SELECT id FROM te WHERE ti_id = NEW.id) BEGIN -- SELECT THE ROWS FROM t2 to insert values INSERT INTO t3 VALUES(id, name) SELECT t2.t1_id, 'dummy' FROM t2 WHERE t2.t1_id == NEW.id; END; Then you may wish to set a trigger to t4 itself to handle the newly created t3 rows. -- Marco Arthur @ (M)arco Creatives
[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?
Excerpts from Daniel Polski's message of 2015-09-24 09:50:36 -0300: > > Hello again, > I think I can simplify the actual question: > > Can I use a SELECT returning multiple rows as input to some INSERT > logic, and in that logic INSERT into multiple tables? That was exactly what I showed you using only one table. (INSERT always insert in one Table, only exception is for views (INSTEAD OF) triggers). > > Like if I have a table t1 containing id 1, 2, 3. > > For each id in t1, do > INSERT INTO t2 (...) > INSERT INTO t3 (...) --But this depends on the last_insert_rowid() from > the insert into t2 above > > The problem for me is that I don't know how to fetch the > last_insert_rowid() needed for the t3 table. (If I didn't have to split > up the insert I could simply run something like INSERT INTO t2 SELECT id > FROM t1; ) You can't use last_insert_rowid because it only stores a single value. In order to have the list of values you have somehow to create a variable for that. One way is to use temporary tables, but I think this is not possible in a trigger (to have a CREATE statement). The other way is what I suggested you: create a new trigger for t2 itself to check on the values inserted. > > Best Regards, > Daniel -- Marco Arthur @ (M)arco Creatives
Re: [sqlite] IS there some way of viewing an SQLite Database?
> > I downloaded a program, DB commander Pro (http://www.dbcommander.com/), to > try to view an SQLite database. > > However, I need to set up a "Data Source (ODBC)" (windows control panel). > What System DNS would I need to add for SQLite? > > Alternatively, is there some other way of viewing an SQLite database? > TIA!! You can try SQLiteManager: http://www.sqlabs.net/sqlitemanager.php Regards, Marco Bambini SQLabs - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DELETE with 64bit unsigned integer key
Hello there sqlite-users, I have a quick question regarding selection and deletion of large unsigned integers. I create my table as following: [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) I then insert some row: [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED INTEGER), ...) This is just a test for 64bit unsigned, so I have the following rows: 1 2 18446744073709551615 (0x) 9223372036854775808 (0x8000) 0 3 1234 23456 654321 When I try to DELETE using: [3] DELETE FROM t1 WHERE instnum <= CAST (?1 AS BIG UNSIGNED INTEGER); I don't always get what I would expect, to me it looks like large numbers (64bit with the MSB set to 1) are treated as negative. For example if ?1 in query [3] is set to 654321, all rows are being deleted (while 18446744073709551615 and 9223372036854775808 should not be deleted) If ?1 is set to 18446744073709551615, the rows 18446744073709551615 and 9223372036854775808 are deleted, but the other ones are not. (while all of them should be gone) (I was previously using [2] and [3] without explicit cast, but the result is the same). How can I solve this issue? Thank you, Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW:
http://www.horsecenter.com.br/i4jsow.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Functions
Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() tks -- Marco Antonio J. Victor Fone: 11 6977-5406 Fax: 11 6973-9772 www.tactor.com.br
[sqlite] Questions
Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() Where I find the documentation on as I can make in agreement INSERT/UPDATE the type of column (BOOLEAN/DATE) tks -- Marco Antonio J. Victor Fone: 11 6977-5406 Fax: 11 6973-9772 www.tactor.com.br
[sqlite] Detailed error code
When I try to create a table that already exists inside a database I obtain the error message: "table xyz already exists" and the error code is 1. Is there a way to obtain a more specific error code that can programmatically inform me that the object is already inside the db? Seems like also Extended Result Codes do not cover this case. Please help. Thanks let me know. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Escape table and column names
Hello, I would like to receive a definitive answer about what is the recommended way to escape table and column names. SQLite supports single quotes, double quotes and square brackets (for mySQL compatibility) … but I haven't yet found the official or recommended way. Anyone have an answer? What about if table name or column name contains that escape character? Do I need to escape it in another way? Please let me know. Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite encryption
Anyone know which encryption algorithm is used in System.Data.SQLite? Seems like it doesn?t use the standard SEE sqlite extension. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] sqlite3session
Is the sqlite3session module still available and supported? If not is there a replacement/workaround for it? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-15 18:05 GMT+02:00 Jean Chevalier : > What I'm thinking now is to what extent the developer who put up the > Mozilla wiki page was entitled to put up opinions and statements either > expressed or implied about a third-party product on behalf of the Mozilla > Foundation (though is that the same Foundation that pays the Consortium > member fee?). The mozilla wiki contains informations useful to contributors to the mozilla codebase and more generally "mozillians", it's not intended to be a global resource of information like wikipedia, nor any kind of official documentation on how to use libraries. It's targeted to code written against the Mozilla codebase and it's not even in the official MDN. The document you pointed at was created some time ago, cause we had a problem with developers taking "the easy way" too often, when they needed a store they just used SQLite (or better mozStorage, our wrapper) because the API was nice and already available, without doing any kind of analysis of their needs. The title was (likely) chosen explicitly negative to make people read the article and clarify the point before going deep into details. And it helped, now people ask what's the best store for their kind of workflow, and clearly SQLite is still a used option. Please don't attach any kind of negative bias to a wiki article, it's just a technical article about possible pitfalls our developers will hit (and have hit) if they don't think what they are doing. Nothing more than that. SQLite is used extensively in every single Mozilla product, so what? . The contents of the page came out from actual bugs and misuses we hit in years of use and experience with it and were discussed with attention. Most also have workarounds or suggested fixes. Honestly I just think you are giving too much importance to a technical document with a clear target and very well expressed points, rather than to the fact SQLite is happily used everywhere. It should just be used properly, not randomly.
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-16 1:36 GMT+02:00 Jean-Christophe Deschamps : > While I can agree with some of the most obvious "don't be dumb" remarks, > there are many things that can't be let unchallenged. And I don't > appreciate the overall tone: "WE at MozillaBigBalls are all clever enough > to use SQLite smartly but you mere mortals are not, so don't even think to > use that fragile piece of softawre." > There's never been that kind of tone, the article has been written to point new Mozilla codebase contributors at possible pitfalls we already hit in the past, and actually help them making informed decisions. It's not intended for global consumption. Sure it's public, cause there's no reason to "hide" technical document that might be useful to contributors, just to complicate their access. Can you find any page that states Mozilla doesn't like SQLite or wants anyone to abandon it? It is used everywhere, from mercurial repositories to Firefox OS... > In it's introduction, the blurb talks about SQLite hidden complexity. Then > it avdises Mozilla pluggin developers to avoid SQLite at almost any rate, > due to "performance reasons", and recommends compressed JSON/logs instead. > It actually briefly explains in which cases a JSON log can work better and at the end it also states what to do and avoid if still SQLite is found to be the best store (and it clearly happens!). It tries to make people think before doing. > Yet this guy(s) advocate that *-every-* pluggin devs should independently > roll his own storage layer It states to evaluate alternatives. Existing alternatives like OS.File writeAtomic and JSON. Those are already used with success in both the products and add-ons, when it makes sense. Sure the developer mus think to durability, backups, coherence, but would it not be the same if he'd use SQLite? You must find the right compromise between durability and performance, you still have to handle corruption cases. Thinking about that stuff is part of normal planning for any feature, independently from the chosen store. > Then another question remains: instead of putting the onus on SQLite being > huge (footprint) and slow (CPU, the 22s "example"), why don't the author(s) > of the blurb question the real root causes of the evil they condemn and > openly recognize that the problem lies entirely elsewhere, perhaps in > Mozilla core code design itself? > Because it's clear the article is about SQLite use in the Mozilla codebase. It's clear there are issues in the Mozilla codebase itself (I'd be surprised of the opposite!). > After all, it's Mozilla devs themselves who designed pluggin APIs and let > "spurious main-thread SQL statements" be possible. If they were sooo > clever, they would never had allowed that and they also would have wrapped > SQLite interface in a strictly limited set of rules enforced by a safe API. > That, they won't tell you. > And they'd have a 100% bug free product with millions lines of code... No, that's unrealistic. The Mozilla codebase comes from the 90s Netscape codebase, at that time the most common thing was a single-threaded and single-process browser, able to show text, some images and tables. The reality evolved A LOT and the code had to evolve to cope with it. At the time mozStorage (the SQLite wrapper) was written, there was still that kind of vision, and it was written as a main-thread synchronous API. Sure, now we all know it was wrong, but at that time it was the right-ish thing to do. The API grew a purely asynchronous alternative, but when you have hundreds millions of users and thousands of add-ons using an API, you can't just say "sorry, we now break you all". You must play fairly. Sure, the Firefox add-ons are the most powerful add-ons around, they can do anything, and while this might be (as you point out) a downside, cause they can perform poorly, it is also their major selling point. There are still a lots of things you can do with a Firefox add-on that you can't do in any other browser add-on. Also, this has nothing to do with the fact SQLite can still be the wrong choice for certain data store needs (as it's the best for others), that is still the main purpose of that article. > Also, if Mozilla devs were sooo much more clever than average Joe and sooo > caring about performance, they certainly would have fixed the hundreds of > memory leaks that plague FF users > This is going off topic, I'd be happy to digress about this but it's not the right place. If you have suggestions or bugs, the codebase is open and the bug tracker is public, you can reach every single developer through mail or IRC. You have all the tools to make the difference. Marco
[sqlite] Epoch time with timestamp in ms
Hello I tried to convert an epoch time with a timestamp in milliseconds like 1393732179010 to a correct date but it seems like that the function: SELECT datetime(1393732179010, 'unixepoch', 'localtime'); is not able to detect ms resolution and incorrectly reports a negative date. How can I tell sqlite to interpret timestamp in ms? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] Query help
I have a table EnginePreference: CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, UNIQUE(engine,databasename,key)) and a table Groups: CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE COLLATE NOCASE) I need to select from EnginePreferences replacing groupid with groupname and I can do that with: SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); What I really need is ALSO to replace groupname with * if groupid is 0. Please note that groupid 0 is never written into the Groups table so my original query would return NULL as groupname with groupid is 0 instead of the required "*". Any idea? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] sqlite3_preupdate functions
Hello, I am using the sqlite3_session module amalgamated into the main sqlite3.c file (3.8.2). I defined both: #define SQLITE_ENABLE_SESSION 1 #define SQLITE_ENABLE_PREUPDATE_HOOK1 but compiler is not able to find all the sqlite3_preupdate* functions. I am missing something? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Apply a changeset
Documentation is not very clear about this point… in order to apply a change set starting from a session should just I write something like: int rc= sqlite3session_changeset(session, , ); rc = sqlite3changeset_apply (db, pnChangeset, ppChangeset, NULL, NULL, NULL); Is that all? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 03 Jan 2014, at 15:54, Richard Hipp <d...@sqlite.org> wrote: > The standard pre-built amalgamation won't work with sessions. You have to > pull source code from the "sessions" branch: > http://www.sqlite.org/src/timeline?r=sessions > > > On Fri, Jan 3, 2014 at 9:50 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> Hello, >> I am using the sqlite3_session module amalgamated into the main sqlite3.c >> file (3.8.2). >> I defined both: >> >> #define SQLITE_ENABLE_SESSION 1 >> #define SQLITE_ENABLE_PREUPDATE_HOOK1 >> >> but compiler is not able to find all the sqlite3_preupdate* functions. >> I am missing something? >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://twitter.com/sqlabs >> http://instagram.com/sqlabs >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3session_changeset API oddity
I am using sqlite3_changeset API to add undo/redo capabilities to an app. I record sqlite operations and I store all of them in a sqlite3_session object. When I need to UNDO an operation I simply do something like: rc = sqlite3session_changeset(session, , ); rc = sqlite3changeset_invert(pnChangeset, ppChangeset, , ); rc = sqlite3changeset_apply (self.db, pnInvertedChangeset, ppInvertedChangeset, NULL, fConflict, NULL); The problem is that I would like to reuse the same session object to perform the REDO operation but after executing the UNDO code above, when I call: rc = sqlite3session_changeset(session, , ); pnChangeset is always 0. Is this a bug or something not correctly documented? Please let me know. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_analyzer source code
If I remember correctly, sqlite3_analyzer is a python script... I am not able to find its source code since in the download page there is a binary app only. Any help? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID option
What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
So, is there an official recommended way? or that check should require a manual sql parsing? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 15:51, Igor Tandetnik <i...@tandetnik.org> wrote: > On 5/7/2014 9:40 AM, RSmith wrote: >> SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE >> type='table' AND tbl_name='YourTableName' >> >> Returns 1 for tables made without rowid, 0 for the rest. > > CREATE TABLE t(x text default 'WITHOUT ROWID'); > > -- > Igor Tandetnik > > ___ > 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] WITHOUT ROWID option
Thanks a lot Richard, I really appreciate. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 16:31, Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> What is the best way to know if a table has been created with the WITHOUT >> ROWID option? >> > > > (1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see > if it returns an error. This might fail on a table like "CREATE TABLE > xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID". > > (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master > WHERE tbl_name='table'". If the PRAGMA mentions an > "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you > have a WITHOUT ROWID table. This approach is more complex, but never > fails, afaik. > > >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://twitter.com/sqlabs >> http://instagram.com/sqlabs >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disable lock controls on Windows
Hi all, As I remember it is possible to disable the Sqlite locking method in the Unix compiled lib but is it possible also to disable the locking in the Windows compiled lib of Sqlite ? There are locking problems in some "exotic" hardware configuration for which I should manage the locking method from myself using a semaphone file. Any suggest ? Thanks in advance. Regards, Marco Turco IT Business Devl Manager Software XP LLP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] vacuum and rowids
Dave please take a look at this blog post: http://www.sqlabs.com/blog/?p=51 -- Marco Bambini http://www.sqlabs.com On Apr 28, 2011, at 9:36 PM, Dave Hayden wrote: > When the VACUUM feature was added I took a look at using it to keep database > file sizes down, but discovered that it changed rowids and messed up my > references between tables (or what I gather the database people call "foreign > keys"). I'm playing around with this again and it looks like rowids aren't > affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild > the existing tables if I don't have to. > > After more poking, it appears that rowids might not be changed by a vacuum if > I have an index on the table. Is this true? If so, is it something I can rely > on going forward? > > Thanks! > -Dave > > ___ > 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] Using SQLite with R*Tree and FTS3 support in iOS
4.3 MB seems really too big... you are probably building a debug version of the library. -- Marco Bambini http://www.sqlabs.com On May 16, 2011, at 5:01 PM, 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? > > Thanks in advance, > > -- Tito > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] cubeSQL
Viadana, Italy - SQLabs announced cubeSQL a fully featured and high performance relational database management system built on top of the sqlite database engine. It is the ideal database server for both developers who want to convert a single user database solution to a multiuser project and for companies looking for an affordable, easy to use and easy to maintain database management system. cubeSQL runs on Windows, Mac, Linux and it can be embedded into any iOS and Cocoa application. cubeSQL is incredibly fast, has a small footprint, is highly reliable and it offers some unique features. It can be easily accessed with any JSON client, with PHP, with the native C SDK and with an highly optimized REAL Studio plugin. Some features includes: - Multi-core and multiprocessor aware. - Strong AES encryption (128, 192 and 256 bit). - Supports unlimited connections. - Full ACID (Atomic, Consistent, Isolated, Durable) compliant. - Platform independent storage engine. - Full support of triggers and transactions. - Journal engine for crash recovery. - Supports databases of 2 terabytes. - Supports sqlite 3 databases. - Automatic logging. - Automatic compression. - Multiversion concurrency control (MVCC). - Plugins for extending the SQL language and the custom commands supported by the server. - Restore and backup support. - Mac OS X, Windows and Linux support. - Native 32bit and 64bit supports. ... and much more Minimum requirements: * MacOS X 10.5 or higher * Windows NT/XP/Vista/7/Server or higher * Linux kernel 2.6.2 or higher Pricing and Availability: cubeSQL Developer Edition is completely free of charge and enables developers to create an application based on cubeSQL without paying any fees until they are ready to deploy their application. Commercial license starts at $299 USD. For more information, please visit the SQLabs website: http://www.sqlabs.com -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SEE encryption lib and odbc
Hi all, We are successfully using the SEE module in Sqlite. All runs well but we need to provide a ODBC connection to a limited number of users. Question: is there a Sqlite odbc driver SEE compatible ? Thanks. Regards, Marco Turco IT Business Devl Manager Software XP LLP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proper way to escape table name and column name
What is the official way to escape table name that contains a space and column name that contain a spaces? Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to know the offset of a rowid inside a table?
I have a table foo with N rows and I want to know the offset of the row with rowid X inside that table. What query/strategy should I perform? I assume that a brute force algorithm should be 1. SELECT * FROM foo ORDER BY rowid; 2. loop inside the recordset until X is found incrementing a counter by 1 but I am quite sure that should be a more elegant way. Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to know the offset of a rowid inside a table?
Thanks Martin and Richard, solution was so simple that I think to need a time break today. Should I add an ORDER BY rowid clause at the end of the SELECT statement or its implicit by the WHERE clause? Thanks a lot. -- Marco Bambini http://www.sqlabs.com On Jun 8, 2011, at 4:27 PM, Richard Hipp wrote: > On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> I have a table foo with N rows and I want to know the offset of the row >> with rowid X inside that table. >> What query/strategy should I perform? >> >> I assume that a brute force algorithm should be >> 1. SELECT * FROM foo ORDER BY rowid; >> 2. loop inside the recordset until X is found incrementing a counter by 1 >> but I am quite sure that should be a more elegant way. >> >> > SELECT count(*) FROM foo WHERE rowid<=X > > The above gives an answer in linear time. It is theoretically possible to > add a little extra metadata to the btree nodes in order to compute the > offset logarithmic time. But I deliberately decided not to included that > metadata when I designed the SQLite btree file format since keeping that > metadata current slows down write performance. > > > >> Thanks a lot. >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
Hello guys,
I have a table Clients defined as (simplified version):
CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity TEXT,
ping_timeout INTEGR);
each time a client performs an operation the last_activity column is updated
with:
UPDATE Clients SET last_activity=datetime('now','localtime') WHERE id=N;
ping_timeout was a global property so in order to get a list of all clients
timedout I used a query like (C code):
snprintf(sql, sizeof(sql), "select id from Clients where last_activity <
datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
things are recently changed in my app and ping_timeout is now a client property
set inside the Clients table (and no longer a global property), so I would like
to perform the query:
snprintf(sql, sizeof(sql), "select id from Clients where last_activity <
datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
using just the ping_timeout column in the Clients table instead of the
settings.ping_timeout global property.
Any idea?
--
Marco Bambini
http://www.sqlabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
No I am sorry but I need to query the ping_timeout column from inside the same
query.
--
Marco Bambini
http://www.sqlabs.com
On Jun 12, 2011, at 2:02 PM, Roger Andersson wrote:
> On 06/12/11 01:52 PM, Marco Bambini wrote:
>> things are recently changed in my app and ping_timeout is now a client
>> property set inside the Clients table (and no longer a global property), so
>> I would like to perform the query:
>> snprintf(sql, sizeof(sql), "select id from Clients where last_activity<
>> datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
>> using just the ping_timeout column in the Clients table instead of the
>> settings.ping_timeout global property.
>>
>> Any idea?
> snprintf(sql, sizeof(sql), "select id from Clients where last_activity <
> datetime('now', '-%d.00 seconds', 'localtime');", ping_timeout);
>
> ___
> 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] Query help
Thanks a lot Igor, it's perfect now.
--
Marco Bambini
http://www.sqlabs.com
On Jun 12, 2011, at 4:46 PM, Igor Tandetnik wrote:
> Marco Bambini <ma...@sqlabs.net> wrote:
>> I have a table Clients defined as (simplified version):
>> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity
>> TEXT, ping_timeout INTEGR);
>>
>> ping_timeout was a global property so in order to get a list of all clients
>> timedout I used a query like (C code):
>> snprintf(sql, sizeof(sql), "select id from Clients where last_activity <
>> datetime('now', '-%d.00 seconds', 'localtime');",
>> settings.ping_timeout);
>>
>> things are recently changed in my app and ping_timeout is now a client
>> property set inside the Clients table (and no longer a
>> global property), so I would like to perform the query using just the
>> ping_timeout column in the Clients table instead of the
>> settings.ping_timeout global property.
>
> select id from Clients where last_activity < datetime('now', -ping_timeout ||
> ' seconds', 'localtime');
> -- or
> select id from Clients where
>(julianday('now', 'localtime') - julianday(last_activity)) * 86400 >
> ping_timeout;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] year, month & day problem
Hi all,
call me stupid but after some days of try and a lot of Googleing, im still
wondering how to solve my problem:
i need to execute a query that updates 3 integer fields (AA, MM, GG) of a
table, containing respectively today year, today month and today day:
update settings set AA=strftime('%Y', 'now'), MM=strftime('%m', 'now'),
GG=strftime('%d', 'now')
looks working well, but later, when i read that values:
const char *sql="select AA,MM,GG from settings";
sqlite3_stmt *statmentS;
if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK)
{
if (sqlite3_step(statmentS)==SQLITE_ROW) {
UserSettings *k = [UserSettings sharedUserSettings];
k.AA=sqlite3_column_int(statmentS, 1);
k.MM=sqlite3_column_int(statmentS, 12);
k.GG=sqlite3_column_int(statmentS, 13);
...
i obtain correct values, but in inverse order:
AA (year) contains the day number
MM (month) is correct
GG (day) contains the year
using SQLIte Manager addons for Firefox, this query:
select strftime('%Y', 'now'), strftime("%m", "now"), strftime("%d", "now")
from settings
returns correct values, running or loading value into XCode simulator looks
not working and i dont know why. :'(
does anyone can suggest me what to fix or check?
thx in advance, marco
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] year, month & day problem
you'r right, i made a mistake doing copy and paste writing original mail,
the real select query contains more fields and i can ensure that 1 based
indexes are correct and respected into the real code, real update query is
hardcoded, no params (0 based): 2 days checking, im sure..
at least, do u know a way to load the copied writable database running into
the XCode simulator, so i can understand if my problem is writing or
retriving data (in this way 50% my troubles are solved)?
Using Mac and windows against the same database to simulate the same query
everithing works perfectly.. im really getting crazy.
i know i can close my eyes and invert that commands, but i care my software
and i really like to know what im doing wrong..
thx for reply, marco
2011/7/17 John Deal <bassd...@yahoo.com>
> Hello,
>
> I am new and have received much information from this list so I hope I am
> not wasting bandwidth. I don't know if it is my misunderstanding or typos
> but should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12
> and 13? If this is the case, according to the docs on sqlite3_column_int()
> "...if the column index is out of range, the result is undefined."
>
> I hope I did not misunderstand the issue.
>
> --- On Sun, 7/17/11, marco bianchini <informa...@gmail.com> wrote:
>
> > From: marco bianchini <informa...@gmail.com>
> > Subject: [sqlite] year, month & day problem
> > To: sqlite-users@sqlite.org
> > Date: Sunday, July 17, 2011, 6:05 AM
> > Hi all,
> > call me stupid but after some days of try and a lot of
> > Googleing, im still
> > wondering how to solve my problem:
> > i need to execute a query that updates 3 integer fields
> > (AA, MM, GG) of a
> > table, containing respectively today year, today month and
> > today day:
> >
> > update settings set AA=strftime('%Y', 'now'),
> > MM=strftime('%m', 'now'),
> > GG=strftime('%d', 'now')
> >
> > looks working well, but later, when i read that values:
> >
> > const char *sql="select AA,MM,GG from
> > settings";
> > sqlite3_stmt *statmentS;
> > if (sqlite3_prepare_v2(database, sql
> > ,-1,, NULL)==SQLITE_OK)
> > {
> > if
> > (sqlite3_step(statmentS)==SQLITE_ROW) {
> > UserSettings *k =
> > [UserSettings sharedUserSettings];
> >
> > k.AA=sqlite3_column_int(statmentS, 1);
> >
> > k.MM=sqlite3_column_int(statmentS, 12);
> >
> > k.GG=sqlite3_column_int(statmentS, 13);
> > ...
> >
> >
> > i obtain correct values, but in inverse order:
> >
> > AA (year) contains the day number
> > MM (month) is correct
> > GG (day) contains the year
> >
> > using SQLIte Manager addons for Firefox, this query:
> > select strftime('%Y', 'now'), strftime("%m", "now"),
> > strftime("%d", "now")
> > from settings
> > returns correct values, running or loading value into XCode
> > simulator looks
> > not working and i dont know why. :'(
> >
> >
> > does anyone can suggest me what to fix or check?
> > thx in advance, marco
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] year, month & day problem
if can help, thats real code :
-- to read
-(void) readUpdateStatus{
const char *sql="select
AAArticoli,MMArticoli,GGArticoli,AAFoto,MMFoto,GGFoto,AAClienti,MMClienti,GGClienti,AAOrdini,MMOrdini,GGOrdini,AA,MM,GG
from settings";
sqlite3_stmt *statmentS;
if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK)
{
if (sqlite3_step(statmentS)==SQLITE_ROW) {
UserSettings *k = [UserSettings sharedUserSettings];
k.AAArticoli=sqlite3_column_int(statmentS, 1);
k.MMArticoli=sqlite3_column_int(statmentS, 2);
k.GGArticoli=sqlite3_column_int(statmentS, 3);
k.AAFoto=sqlite3_column_int(statmentS, 4);
k.MMFoto=sqlite3_column_int(statmentS, 5);
k.GGFoto=sqlite3_column_int(statmentS, 6);
k.AAClienti=sqlite3_column_int(statmentS, 7);
k.MMClienti=sqlite3_column_int(statmentS, 8);
k.GGClienti=sqlite3_column_int(statmentS, 9);
k.AAOrdini=sqlite3_column_int(statmentS, 10);
k.MMOrdini=sqlite3_column_int(statmentS, 11);
k.GGOrdini=sqlite3_column_int(statmentS, 12);
k.AA=sqlite3_column_int(statmentS, 13);
k.MM=sqlite3_column_int(statmentS, 14);
k.GG=sqlite3_column_int(statmentS, 15);
}
sqlite3_finalize(statmentS);
}
return;
}
-- to write
-(int) ioSQL:(NSString*)sql{
sqlite3_stmt *statment;
if (sqlite3_prepare_v2(database,[sql UTF8String],-1,,
NULL)==SQLITE_OK) {
if (sqlite3_step(statment)==SQLITE_DONE) {
sqlite3_finalize(statment);
return 1;
}
}
return 0;
}
-(int) setUpdated{
return [self ioSQL:@"update settings set AA=strftime('%Y', 'now'),
MM=strftime('%m', 'now'), GG=strftime('%d', 'now')"];
}
-(int) setArticoliUpdated{
return [self ioSQL:@"update settings set AAArticoli=strftime(\"%Y\",
\"now\"), MMArticoli=strftime(\"%m\", \"now\"), GGArticoli=strftime(\"%d\",
\"now\")"];
}
-(int) setFotoUpdated{
return [self ioSQL:@"update settings set AAFoto=strftime(\"%Y\",
\"now\"), MMFoto=strftime(\"%m\", \"now\"), GGFoto=strftime(\"%d\",
\"now\")"];
}
-(int) setClientiUpdated{
return [self ioSQL:@"update settings set AAClienti=strftime(\"%Y\",
\"now\"), MMClienti=strftime(\"%m\", \"now\"), GGClienti=strftime(\"%d\",
\"now\")"];
}
-(int) setOrdiniUpdated{
return [self ioSQL:@"update settings set AAOrdini=strftime(\"%Y\",
\"now\"), MMOrdini=strftime(\"%m\", \"now\"), GGOrdini=strftime(\"%d\",
\"now\")"];
}
2011/7/17 marco bianchini <informa...@gmail.com>
> you'r right, i made a mistake doing copy and paste writing original mail,
> the real select query contains more fields and i can ensure that 1 based
> indexes are correct and respected into the real code, real update query is
> hardcoded, no params (0 based): 2 days checking, im sure..
>
> at least, do u know a way to load the copied writable database running into
> the XCode simulator, so i can understand if my problem is writing or
> retriving data (in this way 50% my troubles are solved)?
>
> Using Mac and windows against the same database to simulate the same query
> everithing works perfectly.. im really getting crazy.
> i know i can close my eyes and invert that commands, but i care my software
> and i really like to know what im doing wrong..
>
> thx for reply, marco
>
>
> 2011/7/17 John Deal <bassd...@yahoo.com>
>
>> Hello,
>>
>> I am new and have received much information from this list so I hope I am
>> not wasting bandwidth. I don't know if it is my misunderstanding or typos
>> but should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12
>> and 13? If this is the case, according to the docs on sqlite3_column_int()
>> "...if the column index is out of range, the result is undefined."
>>
>> I hope I did not misunderstand the issue.
>>
>> --- On Sun, 7/17/11, marco bianchini <informa...@gmail.com> wrote:
>>
>> > From: marco bianchini <informa...@gmail.com>
>> > Subject: [sqlite] year, month & day problem
>> > To: sqlite-users@sqlite.org
>> > Date: Sunday, July 17, 2011, 6:05 AM
>> > Hi all,
>> > call me stupid but after some days of try and a lot of
>> > Googleing, im still
>> > wondering how to solve my problem:
>> > i need to execute a query that updates 3 integer fields
>> > (AA, MM, GG) of a
>> > table, containing respectively today year, today month and
>> > tod
Re: [sqlite] year, month & day problem
Works.. Im really sorry for my stupid question, thanks for support and
your time, i think that i need an holyday.. Marco
Il giorno domenica 17 luglio 2011, Black, Michael (IS)
<michael.bla...@ngc.com> ha scritto:
> Column numbers are zero-based, not one-based.
>
> From http://www.sqlite.org/c3ref/column_blob.html
>
> "The leftmost column of the result set has the index 0. "
>
>
>
> That's at least part of your problem.
>
>
>
> So should be:
>
> k.AAArticoli=sqlite3_column_int(statmentS, 0);
> k.MMArticoli=sqlite3_column_int(statmentS, 1);
> k.GGArticoli=sqlite3_column_int(statmentS, 2);
>
> And can't you run the command-line sqlite3 to see what's in your table?
>
>
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ____
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of marco bianchini [informa...@gmail.com]
> Sent: Sunday, July 17, 2011 7:05 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] year, month & day problem
>
> if can help, thats real code :
>
> -- to read
>
> -(void) readUpdateStatus{
> const char *sql="select
> AAArticoli,MMArticoli,GGArticoli,AAFoto,MMFoto,GGFoto,AAClienti,MMClienti,GGClienti,AAOrdini,MMOrdini,GGOrdini,AA,MM,GG
> from settings";
>
>
> sqlite3_stmt *statmentS;
> if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK)
> {
> if (sqlite3_step(statmentS)==SQLITE_ROW) {
> UserSettings *k = [UserSettings sharedUserSettings];
> k.AAArticoli=sqlite3_column_int(statmentS, 1);
> k.MMArticoli=sqlite3_column_int(statmentS, 2);
> k.GGArticoli=sqlite3_column_int(statmentS, 3);
>
> k.AAFoto=sqlite3_column_int(statmentS, 4);
> k.MMFoto=sqlite3_column_int(statmentS, 5);
> k.GGFoto=sqlite3_column_int(statmentS, 6);
>
> k.AAClienti=sqlite3_column_int(statmentS, 7);
> k.MMClienti=sqlite3_column_int(statmentS, 8);
> k.GGClienti=sqlite3_column_int(statmentS, 9);
>
> k.AAOrdini=sqlite3_column_int(statmentS, 10);
> k.MMOrdini=sqlite3_column_int(statmentS, 11);
> k.GGOrdini=sqlite3_column_int(statmentS, 12);
>
> k.AA=sqlite3_column_int(statmentS, 13);
> k.MM=sqlite3_column_int(statmentS, 14);
> k.GG=sqlite3_column_int(statmentS, 15);
>
> }
> sqlite3_finalize(statmentS);
> }
> return;
> }
>
>
> -- to write
>
> -(int) ioSQL:(NSString*)sql{
> sqlite3_stmt *statment;
> if (sqlite3_prepare_v2(database,[sql UTF8String],-1,,
> NULL)==SQLITE_OK) {
> if (sqlite3_step(statment)==SQLITE_DONE) {
> sqlite3_finalize(statment);
> return 1;
> }
> }
> return 0;
> }
>
> -(int) setUpdated{
> return [self ioSQL:@"update settings set AA=strftime('%Y', 'now'),
> MM=strftime('%m', 'now'), GG=strftime('%d', 'now')"];
> }
>
> -(int) setArticoliUpdated{
> return [self ioSQL:@"update settings set AAArticoli=strftime(\"%Y\",
> \"now\"), MMArticoli=strftime(\"%m\", \"now\"), GGArticoli=strftime(\"%d\",
> \"now\")"];
> }
> -(int) setFotoUpdated{
> return [self ioSQL:@"update settings set AAFoto=strftime(\"%Y\",
> \"now\"), MMFoto=strftime(\"%m\", \"now\"), GGFoto=strftime(\"%d\",
> \"now\")"];
> }
> -(int) setClientiUpdated{
> return [self ioSQL:@"update settings set AAClienti=strftime(\"%Y\",
> \"now\"), MMClienti=strftime(\"%m\", \"now\"), GGClienti=strftime(\"%d\",
> \"now\")"];
> }
> -(int) setOrdiniUpdated{
> return [self ioSQL:@"update settings set AAOrdini=strftime(\"%Y\",
> \"now\"), MMOrdini=strftime(\"%m\", \"now\"), GGOrdini=strftime(\"%d\",
> \"now\")"];
> }
>
>
>
>
>
>
>
> 2011/7/17 marco bianchini <informa...@gmail.com>
>
>> you'r right, i made a mistake doing copy and paste writing original mail,
>> the real select query contains more fields and i can ensure that 1 based
>> indexes are correct and respected into the real code, real update query is
>> hardcoded, no params (0 based): 2 days checking, im sure..
>>
>> at least, do u know a way to load the copied writable database running into
>> the XCode simulator, so i can understand if my problem is writing or
>> retriving data (in this way 50% my troubles are solved)?
>>
>> Using Mac and windows against
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comments
Why this valid statement: CREATE TABLE USER( id text, -- the id of the user nametext-- the name of the user ); gives me a syntax error with sqlite 3.7.6.3? Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need Help! -- SQlite database on server
Please take a look at cubeSQL: http://www.sqlabs.com/cubesql.php -- Marco Bambini http://www.sqlabs.com On Aug 4, 2011, at 7:15 PM, Vinoth raj wrote: > Dear SQLite users, > > I have been using SQlite database since three years. Suddenly I have a > requirement for client/server support for my project. > So, the requirement is to save sqlite database on a server from a C++ > application. > I explored the SQLite API with no success. Even numeours queries on the > google did not yield any result. > It would be a great help if you can shed some light on my problem. Is it > possible at all to save SQLite database on a server? > > Eagerly looking forward to your valuable advice. > > Thanks and Regards, > Vinoth > New Delhi, India > ___ > 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] Help with SQLite error message
https://discussions.apple.com/message/15712311#15712311 -- Marco Bambini http://www.sqlabs.com On Sep 5, 2011, at 10:46 PM, Lani Gonzales wrote: > Dear Technical Support: > > Please help me remove this error message: > > The procedure entry point sqlite3_wal_checkpoint could not be located in the > dynamic link library SQlite3.dll > > Please kindly send instructions on how to resolve this problem. > > Thank you, > > Lani Gonzales > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Exception with wrong password
Hi, I'm using a password protected database. When I type the wrong password in my app the resulting Exception is a SQLiteException with this message: "File opened is not a database file". The ErrorCode is set to "NotADatabase". Wouldn't it more correct if this code is set to something like SQLiteErrorCode.Auth or the Exception is more specialized? Thank you Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Exception with wrong password
On 08/10/2011 01:24, Joe Mistachkin wrote: Marco Cosentino wrote: The ErrorCode is set to "NotADatabase". Wouldn't it more correct if this code is set to something like SQLiteErrorCode.Auth or the Exception is more specialized? The exceptions thrown by System.Data.SQLite reflect the underlying error code returned from the native core SQLite library. In the case you mention, the exception is technically correct even though it may seem counterintuitive at first glance. -- Joe Mistachkin Hi Joe, thank you for the reply. I understand the point. But the encryption subsystem should be smart enough to prevent passing an encrypted stream to the native SQLite library (I am assuming that the encryption subsystem operates between the SQLite core library and the OS). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql server management studio like tool for SQLite
Take a look at my SQLiteManager app: http://www.sqlabs.com/sqlitemanager.php -- Marco Bambini http://www.sqlabs.com On Nov 6, 2011, at 7:04 PM, Pete wrote: > Opinions on the best one for OS X? > Pete > > > > > > >> Message: 6 >> Date: Sat, 05 Nov 2011 15:46:36 -0500 >> From: John Horn <pagemeis...@sbcglobal.net> >> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >> Subject: Re: [sqlite] sql server management studio like tool for >> SQLite >> Message-ID: <4eb5a0ac.8050...@sbcglobal.net> >> Content-Type: text/plain; charset=ISO-8859-1; format=flowed >> >> Kit, I've tried many of the tools listed @ >> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools. My hands-down vote >> is for SQLiteExpert Professional @ >> http://sqliteexpert.com/<http://sqliteexpert.com/>. In my opinion >> spending $59 for the Pro version is a **no-brainer** for many reasons. >> >> John >> >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlitediff
Where can I download sqlitediff and its source code? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] sqlitediff
Luuk, that's really really funny thanks. Starting from sqlite 3.8.10 sqlitediff.exe has been added to the official sqlite distribution. I downloaded both the Amalgamation version and the Alternative Source Code Formats from the official download page but I wasn't able to find the diff code, nor a link to download the executable from http://www.sqlite.org/sqldiff.html <http://www.sqlite.org/sqldiff.html>. Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 24 May 2015, at 08:59, Luuk wrote: > > On 24-5-2015 08:39, Marco Bambini wrote: >> Where can I download sqlitediff and its source code? >> > > http://lmgtfy.com/?q=sqlitediff=1 > > or, more correct: > http://lmgtfy.com/?q=sqlitediff+source > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Hi all, I'm trying to generate the sqlite3 library but there is no way with Embercadero C++ 7.00. I always receive some warnings and the first two related to the _endthreadex' and '_beginthreadex' cannot permit to link me the library. See below. k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 -Ik:\BCC70\Include;k:\XHARBOUR\Include -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero Technologies, Inc. K:\sqlite_see_2013\sqlite3.c: Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function '_endthreadex' with no prototype in function sqlite3ThreadProc Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function '_beginthreadex' with no prototype in function sqlite3ThreadCreate Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never used in function winUnfetch Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect assignment in function vdbeSorterCompareInt Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect assignment in function vdbeSorterSetupMerge Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never used in function sqlite3CodecGetKey k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 @K:\sqlite_see_2013\sqlite_see_2013.bcl , K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc. +K:\sqlite_see_2013\Obj\sqlite3.Obj Any help ? Thank you in advance Marco
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
The problem is when I link the generated library. I receive the following error related to the first two warnings so I'm unable to generate the executable file. Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero Technologies, Inc. K:\RENTMNG\Obj\mod306f.c: k:\BCC70\Bin\iLink32.Exe -Gn -aa -Tpe -s @K:\RENTMNG\RENTMNG.bcl Turbo Incremental Link 6.70 Copyright (c) 1997-2014 Embarcadero Technologies, Inc. Error: Unresolved external '__endthreadex' referenced from K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3 Error: Unresolved external '__beginthreadex' referenced from K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3 Error: Unable to perform link Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith Sent: Thursday, October 22, 2015 12:12 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 On 2015-10-22 11:01 AM, Marco Turco wrote: > Hi all, > > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > > > See below. > > > > k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW > -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF > -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 > -Ik:\BCC70\Include;k:\XHARBOUR\Include > -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c > > > > Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero > Technologies, Inc. > > K:\sqlite_see_2013\sqlite3.c: > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function > '_endthreadex' with no prototype in function sqlite3ThreadProc > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function > '_beginthreadex' with no prototype in function sqlite3ThreadCreate > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is > never used in function winUnfetch > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect > assignment in function vdbeSorterCompareInt > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect > assignment in function vdbeSorterSetupMerge > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is > never used in function sqlite3CodecGetKey > > k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 > @K:\sqlite_see_2013\sqlite_see_2013.bcl , > K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst These are just warnings... They are not important and it should still compile. If it fails to compile you should get a message like this: "Error : Failed to compile K:\sqlite_see_2013\sqlite3.c 185433: Some description of what failed" The warnings doesn't mean it did not compile. What makes you think the file failed to compile, is the actual compiled file missing or such? > > > > TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc. > > +K:\sqlite_see_2013\Obj\sqlite3.Obj > > > > Any help ? Thank you in advance > > > > Marco > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Hi, could you please give me the full Bcc32 string you are using ? thank you Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, October 22, 2015 4:22 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 Marco Turco wrote: > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW > -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF > -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 > -Ik:\BCC70\Include;k:\XHARBOUR\Include > -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c My Borland C++ 5.5.1 (the old, free version) needs -tWM for a multithreaded program; you're using -tW. Regards, Clemens ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Solved. At the end I have added the parameter -DSQLITE_THREADSAFE=0 to exclude the multithread code from the library Thank you. Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, October 22, 2015 8:51 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 Marco Turco wrote: > could you please give me the full Bcc32 string you are using ? That would not be useful for you; I'm using an incompatible calling convention. Just replace "-tW" with "-tWM". Regards, Clemens ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE TABLE parser
Hi all, I developed a CREATE TABLE parser in C that is able to extract every details about an sqlite table (table and columns constraints, foreign keys, flags and so on). So far it seems to work pretty well but I would like to stress test it before releasing it as open source on GitHub. Anyone can send me or help me find out some CREATE TABLE sql statements to add to my internal tests? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] CREATE TABLE parser
I developed the parser myself. Here you go a link to the GitHub page: https://github.com/marcobambini/sqlite-createtable-parser -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 02 Mar 2016, at 11:50, Clemens Ladisch wrote: > > Marco Bambini wrote: >> I developed a CREATE TABLE parser in C > > Did you duplicate the SQLite SQL parser? > Or is your parser supposed to have differences? > >> I would like to stress test it before releasing it as open source on GitHub. > > Without the source, finding errors would not be as easy. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE TABLE parser
> On 02 Mar 2016, at 13:32, Luca Ferrari wrote: > > On Wed, Mar 2, 2016 at 1:10 PM, Marco Bambini wrote: >> I developed the parser myself. > > Great job but...what is the aim? > Why one should use this instead of, let's say, Perl SQL::Parser > <http://search.cpan.org/~rehsack/SQL-Statement-1.407/lib/SQL/Parser.pm>? Probably because it can be easily embedded in C and is it also way faster and a lot requires less memory. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > > Luca > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count connections
Hi, How do we know how many connections a database has ? Is it possible to query it using SQL ? Is there a simple command from the sqlite3 shell client ? -- Marco Arthur @ (M)arco Creatives
[sqlite] sqlite3_rowid
I need a way to automatically have the rowid for all queries issued by our users (without modifying the original sql queries). A lot of time for some queries (COUNT(*) for example), it is simply not possible to obtain a valid rowid, so it could just be set to -1. Is this a planned feature? If no and if I would like to try to add it myself, do you an advice for me about what is the best way to proceed? A possible API could be: long long int sqlite3_rowid(sqlite3_stmt*); I know that some months ago there was an experimental code written by Robert Simpson: http://www.mail-archive.com/[EMAIL PROTECTED]/msg19341.html Anyone have tested it? Is there something else available? Thanks a lot. --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimize a query
This query on a small database sometimes takes more than 40 seconds: select _rowid, public_id, vote_count, status, summary, component, date (date_modified), quickfix from reports where public = 1 AND _rowid IN (select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count DESC Table reports contains 22,605 records Table segments contains 71,413 records I suspect that the slowdown is due to the IN clause used in the query or something else that prevents sqlite from using some optimizations. Anyone can help me to optimize this query? Details follows... TABLES: CREATE TABLE reports (_rowid integer NOT NULL PRIMARY KEY, vote_count integer DEFAULT 0, summary varchar(256), public integer DEFAULT 0, date_created date, locked_by integer DEFAULT 0, public_id varchar (16), component integer, severity varchar(32), priority integer DEFAULT 0, user_id integer, release_note_id integer DEFAULT 0, type integer DEFAULT 0, date_modified timestamp, fixed_version varchar (16), status integer DEFAULT 0, quickfix integer DEFAULT 0, easyfix integer DEFAULT 0) CREATE TABLE segments (_rowid integer not null primary key autoincrement, type integer, public integer default 0, date_created date, content varchar(4096), date_modified date, report_id integer, user_id integer) INDEXES: CREATE INDEX reports_component_idx on reports (component) CREATE UNIQUE INDEX reports_public_id_idx on reports (public_id) CREATE INDEX reports_public_idx on reports (public) CREATE INDEX reports_status_idx on reports (status) CREATE INDEX reports_summary_idx on reports (summary) CREATE INDEX reports_user_id_idx on reports (user_id) CREATE INDEX segments_content on segments (content) CREATE INDEX segments_public on segments (public) CREATE INDEX segments_report_id on segments (report_id) CREATE INDEX segments_type on segments (type) CREATE INDEX segments_user_id on segments (user_id) Thanks a lot, --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] An explanation?
As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). I repeated the test several times and results are confirmed... Anyone have an explanation? --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Yes, I know that it is faster ... I just wonder why with one index the query is slower that without any index... --- Marco Bambini On Apr 23, 2007, at 6:31 PM, P Kishor wrote: On 4/23/07, Marco Bambini <[EMAIL PROTECTED]> wrote: As a performance test I created a db with 300,000 records, table is: CREATE TABLE table1 (a INTEGER, b INTEGER) a query like: SELECT * FROM table1 WHERE a=5 AND b=11; takes 0.281 secs. if I add two indexes: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); the same query is about two times slower, it takes 0.463 secs. (I know that only one index is used by the query). Try CREATE INDEX index_ab ON table1 (a, b); and test. I repeated the test several times and results are confirmed... Anyone have an explanation? --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
I know that I can use the ANALYZE command or that I can index both columns. I was making some tests and I found that with one index the query is slower that without any index, so I just trying to understand the reason... I do not want to run it faster, I already know that it is possible. Database is uniformly distributed, I created it ad hoc just for my test (sqlite 3.3.12): CREATE TABLE table1 (a INTEGER, b INTEGER) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,10) 200 rows are inserted with: INSERT INTO table1 (a,b) VALUES (5,11) 150,000 rows are inserted with: INSERT INTO table1 (a,b) VALUES (4,11) And the query was: SELECT * FROM table1 WHERE a=5 AND b=11; New benchmarks: WITHOUT INDEX: 0.281 secs WITH TWO INDEXes: 0.463 secs WITH TWO INDEXes and the ANALYZE command: 0.480 secs INDEXes are: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); --- Marco Bambini On Apr 23, 2007, at 9:36 PM, [EMAIL PROTECTED] wrote: Marco Bambini <[EMAIL PROTECTED]> wrote: Yes, I know that it is faster ... I just wonder why with one index the query is slower that without any index... Probably because most of the entries in your table match the term being indexed. In your case, this likely means that a large fraction of the table entries have a=5. When searching from an index, SQLite first finds the index entry, then has to do a binary search for the table entry. The table entry lookup is O(logN) where N is the number of entries in the table. If the number of rows in the result set is proportional to N, then the total runtime is O(NlogN). On the other hand, the total runtime of a full table scan (which is what happens if you omit the index) is O(N). N -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] An explanation?
Thanks a lot for the explanation Dennis, I really appreciate. --- Marco On Apr 24, 2007, at 12:35 AM, Dennis Cote wrote: Marco Bambini wrote: Database is uniformly distributed, I created it ad hoc just for my test (sqlite 3.3.12): Marco, Another way to think of this is that if your database contained random numbers in the range 1-100 for both a and b, then an index on either of those values would allow sqlite to ignore all but the requested value, or 99% of the entries. It would only have to examine 1% of the records and would run in perhaps 2% of the time of a full table scan. If your data had even more distinct values, things would be even faster. Ultimately, if each data value was unique, then one index lookup would find the matching record, and the lookup time would only be about 2/300,000 or 0.0007% of the time for a full table scan. Indexes are not a magical cure all, they only speed up lookups if you enough different values to let them to reduce the search space to a small enough portion of the entire database to pay for their overhead. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Concurrency
Why you said less than 29? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jun 1, 2007, at 6:56 PM, Eduardo Morras wrote: At 11:17 01/06/2007, you wrote: Hi, I am having a scenario where I have one reader/writer and many writer threads. All writers are pretty basic (single INSERT INTO; some sort of a logging info what a thread has done). I believe I will receive many BUSY return codes and I don't like these spinlock-like retries. The problem I am having with this design is that I would like to complete the thread ASAP, so that I don't have many threads idling and consuming resources of my embedded system. I was thinking to either: a. Use mutex/semaphore before writting to the database or b. Have a (thread safe) list of INSERT INTO strings that every writer thread populates and the main reader/writer thread later executes. Is this a good approach? Does anyone have a better design? I don't want to use other database, because I think Sqlite is great for an embedded system that I am using. How many threads have you?. If threads number is low (less than 29) you can use a database for each thread. Each one will have it's own file and no write lock problems. From time to time a simple sql query can get all data from those databases, write to the main one and delete the databases. HTH - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Get the data from previous months: please, help me to optimize the query...
Dear both (Christian and Joe), (I'm the original author of the first mail, I'm just using my "usual" mail, now... :D ). Thanks for the replies: both works fine: I have no problem in adding new data to the DB, but the performance IS an issue. I tested your solutions, and I got the data in 200ms, that is really good (compared to the one before). Thanks again Marco 2007/7/13, Joe Wilson <[EMAIL PROTECTED]>: --- Christian Smith <[EMAIL PROTECTED]> wrote: > > Much faster - add 3 new fields in CustomerData which you can populate > > via SQLite's trigger mechanism, or an explicit UPDATE prior to your > > SELECT: > > > > MonthRef-- populate from Months table > > MonthRef2 -- date(Months.MonthRef, '-1 year') > > MonthRef3 -- date(Months.MonthRef, 'start of year', '-1 month') > > > > This way you can avoid several joins with the Months table > > and avoid the use of the slow view. > > > This is leaving you open to data errors. Fair enough - just use a temp table to close that loophole. This is pretty much optimal without changing the original poster's schema or any application logic concerning IDMonth and MonthRef: CREATE TABLE Months ( IDMonth INTEGER PRIMARY KEY NOT NULL, MonthRef INTEGER ); CREATE TABLE CustomerData ( IDCustomerData INTEGER PRIMARY KEY NOT NULL, IDMonth INTEGER, NdgSingolo TEXT NOT NULL DEFAULT '0', NdgCliente TEXT NOT NULL DEFAULT '0', FatturatoNdg REAL DEFAULT 0 , FatturatoGruppo REAL DEFAULT 0 , MargineIntermediazioneLordo REAL DEFAULT 0 , MargineInteresse REAL DEFAULT 0 , MargineServizi REAL DEFAULT 0 , RaccoltaDirettaSM REAL DEFAULT 0, RaccoltaIndirettaSM REAL DEFAULT 0 , ImpieghiSM REAL DEFAULT 0 , RaccoltaDirettaSP REAL DEFAULT 0 ); drop table CustomerData2 if exists; create temp table CustomerData2 as SELECT MonthRef, date(MonthRef, '-1 year') as MonthRef2, date(MonthRef, 'start of year', '-1 month') as MonthRef3, IDCustomerData, Months.IDMonth IDMonth, NdgSingolo, NdgCliente, FatturatoNdg, FatturatoGruppo, MargineIntermediazioneLordo, MargineInteresse, MargineServizi, RaccoltaDirettaSM, RaccoltaIndirettaSM, ImpieghiSM, RaccoltaDirettaSP FROM CustomerData, Months WHERE CustomerData.IDMonth = Months.IDMonth; create index CustomerData2i on CustomerData2(NdgSingolo, NdgCliente, MonthRef); explain query plan SELECT AC.*, M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1, AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap, M1.MargineInteresseAS MargineInteresse_m1, AP.MargineInteresseAS MargineInteresse_ap FROM CustomerData2 AC LEFT OUTER JOIN CustomerData2 M1 ON AC.NdgSingolo = M1.NdgSingolo AND AC.NdgCliente = M1.NdgCliente AND M1.MonthRef = AC.MonthRef2 LEFT OUTER JOIN CustomerData2 AP ON AC.NdgSingolo = AP.NdgSingolo AND AC.NdgCliente = AP.NdgCliente AND AP.MonthRef = AC.MonthRef3; -- 0|0|TABLE CustomerData2 AS AC -- 1|1|TABLE CustomerData2 AS M1 WITH INDEX CustomerData2i -- 2|2|TABLE CustomerData2 AS AP WITH INDEX CustomerData2i -- optional - temp table will be destroyed by connection anyway drop table CustomerData2; Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] fts2 in the amalgamation source?
I have modified the Makefile, so I have added: SRC += \ $(TOP)/ext/fts2/fts2.c \ $(TOP)/ext/fts2/fts2.h \ $(TOP)/ext/fts2/fts2_hash.c \ $(TOP)/ext/fts2/fts2_hash.h \ $(TOP)/ext/fts2/fts2_porter.c \ $(TOP)/ext/fts2/fts2_tokenizer.h \ $(TOP)/ext/fts2/fts2_tokenizer1.c make sqlite3.c works fine and I was able to compile it. Hope this help. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jul 26, 2007, at 4:41 PM, [EMAIL PROTECTED] wrote: "David Crawshaw" <[EMAIL PROTECTED]> wrote: Hello all, I was wondering if it would be possible to include fts2 in the amalgamated version of the source code. It looks like all that needs to be done is add tclsh $(TOP)/ext/fts2/mkfts2amal.tcl to the end of the target_source target in Makefile.in and then add fts2amal.c to the end of the "foreach file" loop in tool/mksqlite3c.tcl. I hesitate because with the scripts effectively written for this, there is probably a reason why fts2 has been omitted. The reason fts2 is omitted is that there are name collisions between internal symbols of fts2 and the SQLite core. So the two entities cannot exist in the same translation unit. I've been meaning to go in and resolve the conflicts, but have not gotten around to that yet. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed incompatible change to SQLite
We'll be more than happy with a change like that. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Aug 9, 2007, at 5:37 PM, [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Towards SQLite version 3.5.0
On Aug 28, 2007, at 4:51 PM, Dennis Cote wrote: I wonder if it might not be better to change this API to accept an empty string, in addition to a NULL pointer, to find the default VFS. It seems to me this might make life easier for those writing wrappers in languages that don't have a concept of a NULL pointer. Dennis Cote Just pass 0 in that case. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Advice about a trigger
I need to create a trigger that BEFORE a row is deleted from a table, the sql used to create that row (or a way to recreate it) should be saved to another backup table. For example: CREATE TRIGGER trigger_delete Before DELETE ON table1 BEGIN INSERT INTO backup_table(oldid, sql, tablename, operation) VALUES (old.rowid, "sql", "table1", 1); END The missing field is "sql" ... do you have a smart idea to solve my problem? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Advice about a trigger
I know John, obviously "sql" should be replaced with a way to get the original sql statement that created that row. My question was is there is some smart way to retrieve it... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 4, 2007, at 3:09 PM, John Stanton wrote: Try using the correct delimiter for SQL literals, ', not ". Marco Bambini wrote: I need to create a trigger that BEFORE a row is deleted from a table, the sql used to create that row (or a way to recreate it) should be saved to another backup table. For example: CREATE TRIGGER trigger_delete Before DELETE ON table1 BEGIN INSERT INTO backup_table(oldid, sql, tablename, operation) VALUES (old.rowid, "sql", "table1", 1); END The missing field is "sql" ... do you have a smart idea to solve my problem? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to sqlite-users- [EMAIL PROTECTED] - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook
Hi, with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Thanks a lot, --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook
Thanks Dennis for your reply. I would like to avoid triggers for performance reasons. I haven't found an official solution so I am implementing my own sqlite3_update_notify API that is executed before the operation takes place. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 4:41 PM, Dennis Cote wrote: Marco Bambini wrote: with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Marco, You can use a "before update on table" or "before delete on table" trigger to get the rowid of the row before it is deleted. You can access the value old.rowid from within the trigger and save it into another table for example. See http://www.sqlite.org/ lang_createtrigger.html for more details. HTH Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Custom functions and *
I need to create a custom function that returns all the value from that row. If the * syntax was supported then I don't need to save or retrieve all the column's name for that table. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 4:44 PM, Igor Tandetnik wrote: Marco Bambini <[EMAIL PROTECTED]> wrote: I created a custom function in sqlite, and when I try to execute it with a statement like: SELECT myfunction(col1, col2, col3) FROM table1 WHERE ... then everything works fine. The problem is that I don't know in advance the names of the columns so I tried to use it with a statement like: SELECT myfunction(*) FROM table1 WHERE ... but when myfunction is executed the argc parameter is set to 0. Is the * syntax supported in custom sqlite3 functions? Well, it _is_ supported - you didn't get a syntax error in your statement. It just doesn't do what you hoped it would. I'm not sure why you expected it to pass a list of all the fields. The only existing case of similar syntax I can think of is COUNT (*), and clearly COUNT doesn't accept a list of fields, and wouldn't know what to do with it. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Custom functions and *
Thanks a lot Joe, I'll take a look at that. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 7:05 PM, Joe Wilson wrote: --- Marco Bambini <[EMAIL PROTECTED]> wrote: I need to create a custom function that returns all the value from that row. If the * syntax was supported then I don't need to save or retrieve all the column's name for that table. Here's a simple workaround similar to the 'eval' function in scripting: Look in vacuum.c and you'll see 2 functions: execSql and execExecSql. Create sqlite function wrappers for them. Using these wrapped user functions and querying the sqlite_master table will allow you to generate the SQL query you want with an expanded arg list. This generated SQL will in turn be executed by these functions. There are many example in vacuum.c. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Strange error in sqlite 3.4.2 Win32 version
Hi guys,
I am experiencing a very strange issue in sqlite 3.4.2 (only with the
Win32 version, OSX and linux works fine).
Here it is what's happen:
// create table
CREATE TABLE One( a varchar primary key, b1 integer, b2 integer, b3
integer, z varchar )
CREATE UNIQUE INDEX idx_One ON One( b1, b2, b3 )
// insert 100 rows
// pseudo code
for i as integer = 1 to 100
db.SQLExecute( "INSERT INTO One VALUES( '" + Str(i) + "', " + Str
(i+1) + ", " + Str(i+2) + ", " + Str(i+3) + ", '" + Chr(i +Asc("A"))
+ "' )" )
next
// select case 1
// Getting one column, not in index, FAILS!
rs = db.SQLSelect( "SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
the return value should be 98, but it is 99!
// select case 2
// Getting all columns works
rs = db.SQLSelect( "SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
// select case 3
// Not using whole index works
rs = db.SQLSelect( "SELECT a FROM One WHERE b2 = 100 and b3 = 101" )
// select case 4
// Getting one column, in the index, works
rs = db.SQLSelect( "SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
I wonder if there was a bug in the 3.4.2 version that I should fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed sqlite3_initialize() interface
I think that sqlite3_initialize should be allowed to be called more than once. With the help of a static flag, only the first time it is executed the proper initialize functions will be invoked, successive calls to the sqlite3_initialize should just be a NOP operation... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 30, 2007, at 5:14 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: It is also an error to invoke sqlite3_initialize() more than once. That is a pretty nasty restriction to have. If you link multiple other libraries into your program, each of which also uses SQLite then you'd somehow have to arrange that only one of them calls sqlite3_initialize which is a serious pain. (The wxPython gui library used to have a similar issue when initializing things like cursors and colours and caused endless grief before it was fixed to allow multiple calls). In any event this is a very serious API change and really does qualify for calling it SQLite 4. Alternatively, you don't actually need the interface for 99.99% of users out there (Windows, Linux, Mac) so you could make it unnecessary for them, but do require it for the various esoteric embedded systems. That would justify still calling it SQLite version 3. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHJ1hXmOOfHg372QQRAjCHAKCdH4R/OQNY3ALUli9nRCmbFeyDfACeIHcY 7irdFT/ofCgoNK0jERTjze8= =yB1W -END PGP SIGNATURE- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
int i, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i<ncol; ++i)
{
printf(result[i]);
printf("\t\t");
}
printf("\n");
for(i=0; i<ncol*nrow; ++i)
{
printf(result[ncol+i]);
printf("\t\t");
if (i % ncol == 0) printf("\n");
}
// free table
sqlite3_free_table(result);
// close db
sqlite3_close(db);
printf("simple test finished!\n");
return 0;
abort:
printf("%s\n", sqlite3_errmsg(db));
if (db != NULL) sqlite3_close(db);
return -1;
}
On Windows (not on Mac!) it returns 99 instead of the correct 98 value.
Anyone can confirm that on Windows?
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
Marco Bambini <[EMAIL PROTECTED]> wrote:
I am experiencing a very strange issue in sqlite 3.4.2 (only with the
Win32 version, OSX and linux works fine).
I wonder if there was a bug in the 3.4.2 version that I should fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
What makes you think the bug is in SQLite and not in your
language interface wrapper? Do you still get the wrong
answer if you run the same queries from the CLI?
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Hmm ... I was using CodeWarrior for Windows ... maybe its time to upgrade --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 12:14 PM, Dan Petitt wrote: I compiled up your code and ran it on Windows using VC6 and got: a 98 Hope this helps Dan -Original Message- From: Marco Bambini [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 09:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
The problem was somewhere inside CodeWarrior because the same exact
code worked fine with Visual C.
I used CodeWarrior for Win for all my win32 sqlite compilation but it
seems time to update my Win Dev environment...
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 31, 2007, at 7:59 PM, Joe Wilson wrote:
Can you post the output of this command when you compile
sqlite 3.4.2 with code warrior for your test.sqlite database?
explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
With the sqlite 3.5.1 shell compiled with gcc 4.1.1 I see:
0|Goto|0|25|
1|Integer|0|0|# One
2|OpenRead|0|2|
3|SetNumColumns|0|4|
4|Integer|0|0|# idx_One
5|OpenRead|1|4|keyinfo(3,BINARY,BINARY)
6|Integer|99|0|
7|IsNull|-1|22|
8|Integer|100|0|
9|IsNull|-2|22|
10|Integer|101|0|
11|IsNull|-3|22|
12|MakeRecord|3|0|ddd
13|MemStore|0|0|
14|MoveGe|1|22|
15|MemLoad|0|0|
16|IdxGE|1|22|+
17|IdxRowid|1|0|
18|MoveGe|0|0|
19|Column|0|0|# One.a
20|Callback|1|0|
21|Next|1|15|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|2|
27|TableLock|0|2|One
28|Goto|0|1|
29|Noop|0|0|
Just for the heck of it, can you also provide the code warrior/3.4.2
output for these commands as well?
-- select case 2
-- Getting all columns works
explain SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
-- select case 3
-- Not using whole index works
explain SELECT a FROM One WHERE b2 = 100 and b3 = 101;
-- select case 4
-- Getting one column, in the index, works
explain SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
inti, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i<ncol; ++i)
{
printf(result[i]);
printf("\t\t");
}
printf("\n");
for(i=0; i<ncol*nrow; ++i)
{
printf(result[ncol+i]);
printf("\t\t");
if (i % ncol == 0) printf("\n");
}
// free table
sqlite3_free_table(result);
// close db
sqlite3_close(db);
printf("simple test finished!\n");
return 0;
abort:
printf("%s\n", sqlite3_errmsg(db));
if (db != NULL) sqlite3_close(db);
return -1;
}
On Windows (not on Mac!) it returns 99 instead of the correct 98
value.
Anyone can confirm that on Windows?
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
Marco Bambini <[EMAIL PROTECTED]> wrote:
I am experiencing a very strange issue in sqlite 3.4.2 (only
with the
Win32 version, OSX and linux works fine).
I wonder if there was a bug in the 3.4.2 version that I should
fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Yes sure Joe, I just needed some more time. Here it is the output of explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101; 0|Goto|0|20||1| Integer|0|0||2| OpenRead|1|4|keyinfo(3,BINARY,BINARY)|3| SetNumColumns|1|4||4| Integer|99|0||5| IsNull|-1|18||6| Integer|100|0||7| IsNull|-2|18||8| Integer|101|0||9| IsNull|-3|18||10| MakeRecord|3|0|ddd|11| MemStore|0|0||12| MoveGe|1|18||13| MemLoad|0|0||14| IdxGE|1|18|+|15| Column|1|0||16| Callback|1|0||17| Next|1|13||18| Close|1|0||19| Halt|0|0||20| Transaction|0|0||21| VerifyCookie|0|2||22| Goto|0|1||23| Noop|0|0|| result for CW is still 99... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:38 PM, Joe Wilson wrote: You're not the least bit interested in finding out what the issue in CodeWarrior was? It might be a symptom of another problem. --- Marco Bambini <[EMAIL PROTECTED]> wrote: The problem was somewhere inside CodeWarrior because the same exact code worked fine with Visual C. I used CodeWarrior for Win for all my win32 sqlite compilation but it seems time to update my Win Dev environment... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
Another solution is to design your css for standard browser and then just create a iefixes.css file to load only in IE that contains the various fixes for that browser. The trick is to add that lines in the head section: --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Nov 9, 2007, at 7:29 PM, [EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: It takes time to get all popular browsers working, but it leaves a good first impression with potential users of your software. It seems like a better solution would be to do the website without any CSS and then spend the days or weeks of frustration saved working on SQLite instead. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
I vote for (4). --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Nov 9, 2007, at 7:45 PM, [EMAIL PROTECTED] wrote: "Mark Wyszomierski" <[EMAIL PROTECTED]> wrote: Not a terribly useful comment but was just glancing through the new look and noticed a typo: http://sqlite.hwaci.com/about.html "We believe that General Electric uses SQLite in some product or another because they twice wrote the to SQLite developers ".. "wrote the to " Thanks, Mark. I am going to go through and clean all that up. I'm focused on the layout right now, though. I put up 4 variations. Please, everyone, offer your opinions: (1) http://sqlite.hwaci.com/v1/ No CSS of any kind. (2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners (3) http://sqlite.hwaci.com/v3/ CSS menus with square corners (4) http://sqlite.hwaci.com/v4/ CSS font specification only (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. That leaves me with (4). I suppose we could go with (4) now and change it later -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number 1 does not match any result column Tables are created by: CREATE TABLE a (field); CREATE TABLE b (field); Please note that the above queries worked fine with sqlite 3.2.x or 3.3.x. Any idea? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to get record count
SELECT count(*) FROM myTable; --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Dec 12, 2007, at 2:55 PM, Tom Parke wrote: How can I get a count of the number of records in a table? Sqlite3_get_table() might work, but I only need the count, not the record set. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
What will be the main benefits of the new virtual machine? I mean, it will be just faster or there will be other improvements in the library? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 13, 2008, at 3:07 AM, D. Richard Hipp wrote: On Jan 12, 2008, at 7:55 PM, Shawn Wilsher wrote: Hey all, I was wondering when you plan on releasing the next version of SQLite. Mozilla is currently using 3.5.4, but that does not include some OS/2 fixes that were checked in after the release of 3.5.4. Instead of patching our local copy of sqlite, I'd like to use a release version, but at the same time do not want to delay this fix to our OS/2 users very long. The specific checkins we are looking at are 4646, 4647, and 4648. In case you haven't been watching the timeline (http://www.sqlite.org/cvstrac/timeline) we are in the middle of some major changes. The virtual machine inside of SQLite is being transformed from a stack-based machine into a register-based machine. The whole virtual machine and the code generator is being rewritten. Slowly. Piece by piece. I haven't done an overall line change count yet, but we are looking at some pretty serious code churn. 3.5.4 to 3.5.5 is likely to be the biggest single change in the history of SQLite. So you might not want to release product with 3.5.5 embedded. All the regression tests pass, but still If you like, we can set up a special Mozilla branch off of 3.5.4 that includes the OS/2 fixes. On the other hand, if this is not for a release, but rather for general development work, then please build and test with the latest code from CVS. (This applies to *everybody* not just Mozilla.) Please report any problems. The test suite for SQLite is very thorough, but I have found that users can be very creative in stressing SQLite in ways that I would have never imagined, and have not developed tests for. D. Richard Hipp [EMAIL PROTECTED] -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.5.5 Released
What about speed? Can we expect the same performance of version 3.5.4? Thanks a lot for your continue improvements. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 31, 2008, at 6:33 PM, [EMAIL PROTECTED] wrote: SQLite version 3.5.5 is now available for download from the SQLite website: http://www.sqlite.org/ The big change from version 3.5.4 is that the internal virtual machine was reworked to use operands in registers rather than pulling operands from a stack. The virtual machine stack has now been removed. The removal of the VM stack will help prevent future stack overflow bugs and will also facilitate new optimizations in future releases. There should be no user-visible changes to the operation of SQLite in this release, except that the output of EXPLAIN looks different. In order to make this change, about 8.5% of the core SQLite code had to be reworked. We thought this might introduce instability. But we have done two weeks of intensive testing, during which time we have increased the statement test coverage to 99% and during which we have found and fixed lots of minor bugs (mostly things like leaking memory following a malloc failure). But for all of that testing, we have not detected a single bug in the new register-based VM. And for that reason, we believe the new VM, and hence version 3.5.5, is stable and ready for production use. As usual, please report any problems to this mailing list, or directly to me. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite 3.5.5 and SQLITE_ENABLE_LOCKING_STYLE=1
It is safe to define SQLITE_ENABLE_LOCKING_STYLE=1 with sqlite 3.5.5 if I am interested in opening database files on a shared volumes on Mac? As far as I know it was the only workaround ... or something is changed in recent versions? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speed of ORDER BY clause?
Have you tried to create an indexed? Have you tried to analyze your query with SQLiteManager in order to see which indexes are used? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ On Jun 14, 2006, at 5:56 PM, [EMAIL PROTECTED] wrote: I'm finding that ORDER BY is surprisingly slow, and it makes me wonder if I'm doing something wrong. Here's the situation: I need to select a large set of records out of a table, sort them by one column, and then get just a subset of the sorted list. (For example, I might want records 40-60 ordered by date, which is a completely different set than records 40-60 ordered by user ID.) I start with the full list of record IDs I want, and a query something like this: SELECT FROM WHERE recID IN ( ORDER BY dateFld I have a unique index on recID, and an index on dateFld. When my record IDs list is about 13000 items, the ORDER BY takes about 10 seconds (i.e., the query takes 10 seconds longer than the same query without the ORDER BY clause). Yet if I remove the ORDER BY, grab all the dateFld values into my own array, and sort it myself, the sort takes about 2 seconds. This has left me with the weird result that it's actually *faster* for me to query the database twice: first to get the unordered list of all records and their dates, which I then sort myself, and then query again to get just the subset of records I really want. Am I missing something here? If my own code can sort these dates in 2 seconds, why does sqlite take 10? And why did indexing the dateFld not make any difference (i.e., it took about 10 seconds before I added the index too)? Finally, can anyone see a more efficient solution to this problem? Many thanks, - Joe -- Joe Strout -- [EMAIL PROTECTED] Verified Express, LLC "Making the Internet a Better Place" http://www.verex.com/
[sqlite] Borland DbExpress SQLite 3.3.6 Driver
Finally I've found the time and courage to upgrade the code from SQLite2 to SQLite3. For Delphi 6 a new DbxExpress driver is available. For a few $s you can purchase the sources and support maintenance for newer versions. You may download at http://www.bcp-software.nl Regards, Marco.
[sqlite] SQLite DbExpress Update
For the Borland DbExpress driver I've fixed the support for turning on and off the pragma setting for FullName. Download your registered sources or binaries from the known urls. Regards, Marco Wobben. http://www.bcp-software.nl
[sqlite] [ANN] SQLiteManager 2.1.4
VIADANA, Italy (August 11, 2006) - SQLabs today announced the availability of SQLiteManager 2.1.4, their new SQLite database manager tool for MacOS X and Windows. SQLiteManager is a "next generation" GUI database manager for sqlite databases, it combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2.x, sqlite 3.x, in memory databases and REALSQL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way and it supports you arbitrary SQL commands. Some features include: - New InstantQuery technology for blazing speed queries. - Full SQLite2 and SQLite3 support. - Full REALSQL Server support. - Browse tables, views, and indexes. - Rename tables and add columns to existing tables. - Create new tables, views, indexes and triggers. - Drop tables, views, indexes, triggers. - Manage tables by inserting, editing, and deleting records. - Built-in virtual machine analyzer. - Built-in optimizer. - Show JPEG, BMP, TIFF, QuickTime from BLOB records. - Save frequently used SQL for later use. - Convert SQLite 2 databases to SQLite 3. - Powerful reports generation with flexible report templates. - Advanced import and export capabilities. - Built-in language reference. - and much more... What's new in version 2.1.4 -Fixed a bug that occurs when trying to set a column to NULL. -Improved CSV and Custom imports. -Improved ALTER TABLE command. Minimum requirements: - MacOS X 10.2.8 or higher - Windows 9.x/2000/NT/XP A SQLiteManager single license is $39 (a $14 discount coupon code is available for SQLiteManager 1.x registered users). Company and multiplatform licenses are also available. For more information, please visit the SQLabs website: http://www.sqlabs.net/sqlitemanager.php Contact Information Web: http://www.sqlabs.net Email: [EMAIL PROTECTED]
Re: [sqlite] Database on usbstick
To try is the core of each science ;) Above all it's the core of Computer Science :) --- eWobbuh <[EMAIL PROTECTED]> ha scritto: > > Havent try it yet, just wondering if its possible. > Do you know how you tell > sqlite where to find a database? havent worked > before with it.. only with > mysql > -- > View this message in context: > http://www.nabble.com/Database-on-usbstick-tf2219676.html#a6149354 > Sent from the SQLite forum at Nabble.com. > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > __ Do You Yahoo!? Poco spazio e tanto spam? Yahoo! Mail ti protegge dallo spam e ti da tanto spazio gratuito per i tuoi file e i messaggi http://mail.yahoo.it - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Select ignoring accents
I need to find out a way to search all the records inside an sqlite database ignoring accents and case. So for example with a "select" I would like to find: Aero àero Aéro Ignoring case is the easy part, what about accents? Thanks a lot for your help. --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Q about new SQLite API
This is a very good news: 1. sqlite3_compile 2. maybe with the new sqlite3_compile routine there should also be a way to retrieve the rowid of the current row (NULL is no valid rowid is found), a possible API could be sqlite3_rowid to call after each sqlite3_step only if it returns an SQLITE_ROW is returned. 3. SQLITE_SCHEMA --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Q about new SQLite API
I know that there are many times where a rowid could not be returned (this is why I suggested to return NULL or -1 for example). A lot of times we don't have access to the sql issued by end users and a way to retrieve the rowid (if there is one) could simplify a lot all our code (and I am sure this is true for a lot of other developers). --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Nov 7, 2006, at 4:14 PM, Igor Tandetnik wrote: Marco Bambini <[EMAIL PROTECTED]> wrote: 2. maybe with the new sqlite3_compile routine there should also be a way to retrieve the rowid of the current row (NULL is no valid rowid is found), a possible API could be sqlite3_rowid to call after each sqlite3_step only if it returns an SQLITE_ROW is returned. In general, there is no rowid associated with a row returned by select. A row may be constructed out of data taken from multiple rows of multiple tables, or be manufactured without reference to any table at all. If you want a rowid from a particular table, why not just retrieve it explicitly, as a column in select statement? Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] question about search string
Try
SELECT * FROM mytable where 'c:\\Program Files\\a.exe'
like '%command%'
--- Florin Serbanescu <[EMAIL PROTECTED]> ha scritto:
> Hello,
>
> I have a string that contains a full path of a file
> for example. Also I have a table in Sqlite that
> contains a column name 'command' that has only the
> filename. My question is how I can create a query so
> I
> can retrieve all rows where command field is a
> substring of my string. In MySQL for example I can
> do
> this:
>
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like concat('%',command,'%').
>
> But it seems that in Sqlite does not work. Would be
> something like:
>
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like '%'||command||'%'.
>
> In other words I need to search in a table a
> substring
> of my string. So is quite vice versa of the normal
> string search with like.
>
> Thank you for your help,
>
> Florin.
>
>
>
>
> Do you Yahoo!?
> Everyone is raving about the all-new Yahoo! Mail
> beta.
> http://new.mail.yahoo.com
>
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
>
>
__
Do You Yahoo!?
Poco spazio e tanto spam? Yahoo! Mail ti protegge dallo spam e ti da tanto
spazio gratuito per i tuoi file e i messaggi
http://mail.yahoo.it
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] question about search string
Nevermind, I'm wrong.
--- Marco Radaelli <[EMAIL PROTECTED]> ha scritto:
> Try
>
> SELECT * FROM mytable where 'c:\\Program
> Files\\a.exe'
> like '%command%'
>
>
> --- Florin Serbanescu <[EMAIL PROTECTED]> ha
> scritto:
>
> > Hello,
> >
> > I have a string that contains a full path of a
> file
> > for example. Also I have a table in Sqlite that
> > contains a column name 'command' that has only the
> > filename. My question is how I can create a query
> so
> > I
> > can retrieve all rows where command field is a
> > substring of my string. In MySQL for example I can
> > do
> > this:
> >
> > SELECT * FROM mytable where 'c:\\Program
> > Files\\a.exe'
> > like concat('%',command,'%').
> >
> > But it seems that in Sqlite does not work. Would
> be
> > something like:
> >
> > SELECT * FROM mytable where 'c:\\Program
> > Files\\a.exe'
> > like '%'||command||'%'.
> >
> > In other words I need to search in a table a
> > substring
> > of my string. So is quite vice versa of the normal
> > string search with like.
> >
> > Thank you for your help,
> >
> > Florin.
> >
> >
> >
> >
>
> > Do you Yahoo!?
> > Everyone is raving about the all-new Yahoo! Mail
> > beta.
> > http://new.mail.yahoo.com
> >
> >
>
-
> > To unsubscribe, send email to
> > [EMAIL PROTECTED]
> >
>
-
> >
> >
>
>
> __
> Do You Yahoo!?
> Poco spazio e tanto spam? Yahoo! Mail ti protegge
> dallo spam e ti da tanto spazio gratuito per i tuoi
> file e i messaggi
> http://mail.yahoo.it
>
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
>
>
__
Do You Yahoo!?
Poco spazio e tanto spam? Yahoo! Mail ti protegge dallo spam e ti da tanto
spazio gratuito per i tuoi file e i messaggi
http://mail.yahoo.it
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] When to release version 3.3.10?
in my opinion you should release it as soon as possible, a lot of people haven't yet upgraded their library to the latest 3.3.9 so it seems reasonable to me to upgrade directly to 3.3.10. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 5, 2007, at 1:09 PM, [EMAIL PROTECTED] wrote: When I fixed threadtest2.c yesterday, it quickly uncovered a bug (not thread related but a bug all the same) that was introduced by the fix to the database corruption problem of earlier this week. The new bug does not cause database corruption, but it can cause a segfault if you have a lot of sqlite3_open() calls (100s per second) to the same database from many threads or processes. See http://www.sqlite.org/cvstrac/chngview?cn=3563 The question is: should I rush out 3.3.10 to cover this important bug fix, wait a week to see if any other bugs surface, or do the usual 1-2 month release schedule and let people effected by this bug apply the patch above. What is the opinion of the user community? -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Locking support for remote databases with Mac OSX
I think that starting from version 3.3.8 there is the code in os.c that add supports for locking remote databases with Mac OSX. Inside os.c there are a couple of #defines (SQLITE_ENABLE_LOCKING_STYLE, SQLITE_FIXED_LOCKING_STYLE ) that I think should help with my request. My question is: What I should do in order to compile an sqlite library that supports locking for remote databases with Mac OSX? Thanks a lot, --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE_ENABLE_LOCKING_STYLE
I am sorry if the question is too obvious but I haven't found any documentation about the SQLITE_ENABLE_LOCKING_STYLE macro (it is in os.c). Should I set it to 0 (default) or 1? I need to be able to access database files located on shared volumes on OS X. Which are the implications of setting it to 1? Thanks a lot. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [ANN] SQLiteManager 2.5
VIADANA, Italy (January 16, 2007) - SQLabs today announced the availability of SQLiteManager 2.5, their new SQLite database manager tool for MacOS X and Windows. SQLiteManager is a "next generation" GUI database manager for sqlite databases, it combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2.x, sqlite 3.x, in memory databases and REALSQL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way and it supports you arbitrary SQL commands. Some features include: - Universal Binary. - New InstantQuery technology for blazing speed queries. - Full SQLite2 and SQLite3 support. - Full REALSQL Server support. - Full AES encrypted SQLite3 databases support. - Browse tables, views, and indexes. - Rename tables and add columns to existing tables. - Create new tables, views, indexes and triggers. - Drop tables, views, indexes, triggers. - Manage tables by inserting, editing, and deleting records. - Built-in virtual machine analyzer. - Built-in optimizer. - Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records. - Save frequently used SQL for later use. - Convert SQLite 2 databases to SQLite 3. - Powerful reports generation with flexible report templates. - Advanced import and export capabilities. - Built-in language reference. - and much more... What's new in version 2.5 - First Universal Binary version - Added FIELD.TYPE in reports - The email field in bug/crash reporter is now mandatory - The Import->Other window now contains an Encoding field that enables you to specify the encoding of the source file - Fixed a bug where under certain conditions a field in an sqlite 2 database was not updated - Fixed a bug in the Import->Other window where the latest field is the table is not displayed - Added PDF as show type in the EditRecord window - Added a shortcut to query a table (a contextual menu) directly from the Design panel - Greatly improved the CSV importer, it is now much more smart about column names - Added the "Relaxed CSV Engine" checkbox in order to workaround to a big amount of malformed or out of standard CSV files - Multiple selection is now allowed in the Query panel - Fixed a crash that occurs while converting big sqlite2 databases - Replaced the old SQLitePluginPro with the new SQLite2ProfessionalPlugin - Updated SQLite3ProfessionalPlugin to the latest 2.1.0 version - Updated sqlite library to version 3.3.10 - Updated REALSQLServer plugin to the latest version - SQLiteManager can now open databases located on OSX shared volumes - Improved error messages in import operations - Added support for full-text search and virtual tables (sft1 and stf2 are supported) - Added the ability to open encrypted (AES) sqlite 3 database file Minimum requirements: - MacOS X 10.3.9 or higher - Windows 9.x/2000/NT/XP A SQLiteManager single license is $39 (a $14 discount coupon code is available for SQLiteManager 1.x registered users). Company and multiplatform licenses are also available. For more information, please visit the SQLabs website: http://www.sqlabs.net/sqlitemanager.php Contact Information Web: http://www.sqlabs.net Email: [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Obtaining randomness on win32
I really think that this article http://eternallyconfuzzled.com/arts/jsw_art_rand.aspx worths a read. It talks about the general rand function and the problem of distribution... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 29, 2007, at 5:14 PM, [EMAIL PROTECTED] wrote: The pseudo-random number generator (PRNG) in SQLite is becoming more important so it seem good to make sure it is well seeded. On Unix this is easy - just open /dev/urandom and read out as much randomness as you need. But I do not know how to do this on win32 and wince. The current implementation seeds the random number generator on these platforms by grabbing a copy of the current system time. See the sqlite3WinRandomSeed() function in os_win.c for details. This is not a very good method for seeding a PRNG. Can someone with more knowledge of win32 and wince please suggest a better method for seeding the PRNG on those platforms? -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
