[sqlite] Ticket 1224: config.h
On Wed, 27 Apr 2005, Christian Smith wrote: >On Tue, 26 Apr 2005, D. Richard Hipp wrote: > >>On Tue, 2005-04-26 at 17:49 +0100, Christian Smith wrote: >>> Just created ticket #1224 to remove config.h from build, but there appears >>> to be no way to attach a patch to the ticket itself. Have I missed >>> something? >>> >> >>There is an "[Attach]" hyperlink at the top of the screen. > >Worst thing is I've attached things before to other tickets. D'oh. > >Patch is attached to ticket. I've done a Solaris 32-bit to Solaris 64-bit >target build, and come out with a "working" sqlite3 binary, but as yet >I've not run the regression tests due to lack of installed TCL libraries, >but I have bumped into the alignment problem I seem to remember running >into before. I've built with 32-bit gcc 3.3.2, using the -m64 flag to >generate a 64-bit binary. The callback trace (debugging symbols don't >appear to work with gdb or Sun Forte 6) > > [snip trace] > >Same trace as that 64-bit problem you worked on last October, check in >#2026: >http://www.sqlite.org/cvstrac/chngview?cn=2026 > >Do you have any other alignment/size assumptions, other than the ones >fixed above? Or this could just be a bug in gcc perhaps? I can retry the >build using the Sun compiler to generate the 64-bit binaries. Checkin 2455 (http://www.sqlite.org/cvstrac/chngview?cn=2455) appears to have fixed this issue as well with a 32->64bit cross built SPARC binary. > >Either way, I still think it's worth removing config.h from the build to >help with general cross building. Still think so. Has anyone tried the patch? Comments? Patch attached to ticket, and is public domain. > >Cheers, >Christian > > Ditto -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] request for additions to sqlite 3.xx
> What's the objection to reading your SQL source out of the database and > preparing it at program startup? I have no objection to reading the sql on startup as that is what we are currently doing. I just want all data access code inside of the database instead of my source code. Does anyone else think that SQLite should have stored procedures?? My assumption is that if you are creating views and triggers, why not also create stored procedures aswell. Nearly every other database that has triggers (embedded library or not) has stored procedures aswell. Anyway, as I have pointed out before, I obviously can do without as we are simulating stored procedures by puting them in resource files from one large xml file that can be edited external to the source code. We do not want out data access layer tied too much to SQLite and would prefer to have all access code in the database itself so our data access layer can accomadate (lesser obviously) databases on the market with little change to the code. We can usually work with SQL Server code independant of the application by using stored procedures as our sole access point into he database. SQLite is definitely extremely fast and we have no plans of abandoning due to lack of stored procedures but definitely would be nice to have...
[sqlite] Can I bind a sql parameter to a filed name?
when i use this code:
(Omit some code...such as sqite3_stmt)
sqlite3_prepare("select * from table1 where $fieldname = $fieldvalue")
sqlite3_bind_text("$fieldname", "id"); //the "id" is a field of table1,
this line maybe got error.
sqlite3_bind_int("$fieldvalue", 5);
sqlite3_step();
...
I wonder if these code get the SQL:
1. select * from table1 where id = 5 //this is what i want
or
2. select * from table1 where 'id' = 5//this is what i don't want!
Another question:
I use sqlite static lib in my applictiaon, and How I can get the debug
info that comes from sqlite3DebugPrintf() or other?
thanks, liigo
Re: [sqlite] Multiuser SQLITE_LOCKED error syncronization idea
On Mon, 9 May 2005, Helmut Tschemernjak wrote: Hello, sqlite3 3.2.1 gives SQLITE_LOCKED errors if one process updates the database and another accesses it. Most retries will fail if the other processes is working e.g. many inserts. Here is my idea: Keep a list of up to 32 pid_t of waiting processes in the database file. After one statement is completed send a SIG_USR1 message of the first process in the list and shift out the first PID and add yourself to the end of this list any wait for some time e.g. (20ms). The second process receives a signal and can start using the DB and will automatically wakeup the next client PID. The same could be done on Windows using events. This means there would be no polling/retry required to access the DB from a second process on the same machine. Are there any other workarounds? I will try the sqlite3_busy_handler the pids will grow stale and you'll end up sending a signal to some random proecess. other issue is that the pid may not be on the same host - we store alot of config info in nfs mounted sqlite dbs. the are however, very occasionally updated by a process that locks the whole db first. in this case the pid hold the lock is almost never on the same machine. cheers. -a -- === | email :: ara [dot] t [dot] howard [at] noaa [dot] gov | phone :: 303.497.6469 | renunciation is not getting rid of the things of this world, but accepting | that they pass away. --aitken roshi ===
[sqlite] Multiuser SQLITE_LOCKED error syncronization idea
Hello, sqlite3 3.2.1 gives SQLITE_LOCKED errors if one process updates the database and another accesses it. Most retries will fail if the other processes is working e.g. many inserts. Here is my idea: Keep a list of up to 32 pid_t of waiting processes in the database file. After one statement is completed send a SIG_USR1 message of the first process in the list and shift out the first PID and add yourself to the end of this list any wait for some time e.g. (20ms). The second process receives a signal and can start using the DB and will automatically wakeup the next client PID. The same could be done on Windows using events. This means there would be no polling/retry required to access the DB from a second process on the same machine. Are there any other workarounds? I will try the sqlite3_busy_handler best regards Helmut Tschemernjak
[sqlite] enum return type idea for sqlite3 functions
Hello,
I like to recommend to use an enum for the return code of all sqlite3
functions., E.g.:
typedef enum {
SQLITE_OK = 0,/* Successful result */
SQLITE_ERROR= 1,/* SQL error or missing database */
SQLITE_INTERNAL = 2,/* An internal logic error in SQLite */
//...
} sqlite3_retcode;
sqlite3_retcode sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
The benefit would be that the return value would be displayed in the
debugger.
best regards
Helmut Tschemernjak
Re: [sqlite] Data tipe
Sombra schrieb: There is any Datatypes for Data (year, moth,day ) in version 2.8.16 of SQLite? Thanks SB No, there isn't. you can save a time_t or even a COleDateTime::m_dt in the database. Yours Martin
Re: [sqlite] view and column naming problem in sqlite3
On Mon, May 09, 2005 at 08:20:14AM -0700, David M. Cook wrote: > CREATE VIEW flat_recording_view as select r.recording_id, c.composer_name, > SQL error: no such column: composer_name > > Aliasing the column names in the view using the 'as' syntax didn't help. I guess I mistyped my query, aliasing does work to clear up the problem. Also putting the column name in quotes as "c.composer_name" worked. Dave Cook
[sqlite] Data tipe
There is any Datatypes for Data (year, moth,day ) in version 2.8.16 of SQLite? Thanks SB
[sqlite] view and column naming problem in sqlite3
I have a view CREATE VIEW flat_recording_view as select r.recording_id, c.composer_name, w.title, r.description, l.label_name, d.catalog_no from composer c join work w on (c.composer_id=w.composer_id) join recording r on (w.work_id=r.work_id) join disc d on (r.disc_id=d.disc_id) join label l on (l.label_id=d.label_id); and I want to use this for queries like: select * from flat_recording_view where composer_name like 'haydn%'; This has always worked fine with sqlite 2.8, but with the sqlite3 that comes with Tiger (/usr/lib/libsqlite3.0.8.6.dylib and sqlite utility 3.1.3) I get: SQL error: no such column: composer_name Aliasing the column names in the view using the 'as' syntax didn't help. Any other info that I could supply that might help here? Thanks, Dave Cook
Re: [sqlite] Merging databases
> Trivial. Don't use sqlite's "integer primary key", but instead globally > unique identifiers. > > http://cr.yp.to/proto/maildir.html Nice link, thanks! > > Has good hints on where to get your globally unique identifiers. > > Or translate the original keys to globally unique identifiers within a > transaction before loading into your master database. Works fine if you don't have any overlap between products on the two systems. You might get the same product twice, each with a different global unique number.
Re: [sqlite] SQL to renumber values in a column?
On 5/8/05, Brown, Dave <[EMAIL PROTECTED]> wrote: > That's not what I want, though. I want to UPDATE the actual values in the > order_number column so they end up as 1, 2, 3 instead of 1, 3, 4. > > So I want the result of a "select * from table order by order_number" to > give me: > a, 1 > c, 2 > d, 3 Use a counter in whatever language you're retreieving the result set in. It's lots simpler.
[sqlite] avoiding triggers within triggers ?
While playing around with the extension of the order_number question Dave posted
I got stuck at some point.
I have 3 triggers on a table for INSER/DELETE and UPDATE.
all 3 of them do further UPDATEs on the same table.
How can I make sure that these UPDATEs won't fire the UPDATE trigger again ?
Can I temporaily disable triggers or can I recognize when a they were
fired by statements
withing triggers ? Maybe I should use RAISE(IGNORE); but I don't know WHEN ?
Here is the full example:
CREATE TABLE players (
name VARCHAR(10),
ordINTEGER
);
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON players
BEGIN
UPDATE players SET ord = ord-1 WHERE ord > old.ord;
END;
CREATE TRIGGER keep_in_order_insert BEFORE INSERT ON players
BEGIN
UPDATE players SET ord = ord+1 WHERE ord >= new.ord;
END;
CREATE TRIGGER keep_in_order_move BEFORE UPDATE OF ord ON players
BEGIN
UPDATE players SET ord = ord + 1 WHERE old.ord > new.ord AND ord <
old.ord AND ord >= new.ord;
UPDATE players SET ord = ord - 1 WHERE old.ord < new.ord AND ord >
old.ord AND ord <= new.ord;
END;
SELECT "Add players";
INSERT INTO players VALUES("a", 1);
INSERT INTO players VALUES("b", 2);
INSERT INTO players VALUES("c", 3);
INSERT INTO players VALUES("d", 4);
INSERT INTO players VALUES("e", 5);
SELECT * FROM players;
SELECT "Delete player in 2nd place:";
DELETE FROM players WHERE ord = 2;
SELECT * FROM players;
SELECT "Insert player in 2nd place:";
INSERT INTO players VALUES("x", 2);
SELECT * FROM players;
DROP TABLE players;
[sqlite] Kexi 0.9 beta 1 released
For original announcement with links see http://www.kexi-project.org/wiki/wikiview/index.php?0.9Beta1Announcement == Kexi 0.9 beta 1 Announcement == Kexi Team Ships Beta Release of Major Enhancements to Free Integrated Database Environment May 9, 2005 (The INTERNET). The Kexi Team today announced the immediate availability of Kexi 0.9 beta 1, codenamed "Shorthorn", the newest stable release of the integrated environment for managing data. Changes (since 0.1 beta 5 version) * Database forms are now officially supported * Many improvements in handling server databases * For users' convenience, Tabular Data View's behaviour is similar to Form Data View's behaviour * Data and project migration from existing data sources (SQLite, MySQL, PostgreSQL) * (Read only) import of Microsoft Access MDB file import available as optional plugin. * Tens of overall improvements and hundreds of bug fixes See also Detailed information on changes and Screenshots. Download Kexi and its libraries are available free of charge under flexible Open Source license (LGPL). It can be obtained as [archive] Linux/Unix source code http://download.kde.org/download.php?url=unstable/apps/KDE3.x/office/kexi-0.9beta1.tar.bz2. See also Download section on Kexi web site. More information How to compile Kexi. To learn how to compile Kexi, see this document. Support. Kexi users are invited to report bugs and wishes. This can be done by using the KDE Bug Tracking System. Unsupported features. Updated list of unsupported features and known problems is also available. News New Developers On the Board! Three new proffesional software developers joined the Kexi Project: * Martin Ellis who contributed with updated MySQL driver, KexiDB improvements and MDB (MS Access databases) support. The latter feature is highly expected by many users. * Sebastian Sauer who contributed with excellent, well designed Scripting Module (KROSS) together with Python language bindings for KROSS. These features are planned as the subject of Kexi 1.0 release. * Christian Nitschkowski who contributed with various forms' graphics effects, KInitiator (to be included in 1.0) and helper dialogs. Jobs. "Ask not what Kexi can do for you, ask what you can do for Kexi". Kexi Team is looking for developers, and package maintainers (vide supported Linux distributions). Translators, testers, end-user and development documentation writers, and any other forms of support are also welcome. Contact the Team for more information. Multiplatform Availability. Kexi on Linux (e.g. Debian) is available for many architectures, including 64-bit Intel/AMD and Apple PowerPC. Features in progress. Scripting using Python language; CSV and Clipboard data import. Sponsorship Kexi is developed by Kexi Team - an international group of independent developers, with assistance and workforce coming from the OpenOffice Polska company. About Kexi Kexi is an integrated environment for managing data. It can be used for creating database schemas; inserting data; performing queries, and processing data. Forms can be created to provide a custom interface to your data. All database objects - tables, queries and forms - are stored in the database, making it easy to share databases. As Kexi is a real member of the K Desktop Environment and KOffice projects, it integrates fluently into both on Linux/Unix. It is designed to be fully usable also without running KDE Desktop on Linux/Unix, MS Windows and Mac OS X platforms. Kexi is considered as a long awaited Open Source competitor for MS Access, Filemaker and Oracle Forms. Its development is motivated by the lack of Rapid Application Development (RAD) tools for database systems that are sufficiently powerful, inexpensive, open standards driven and portable across many OSes and hardware platforms. == End of the Announcement == -- regards / pozdrawiam, Jaroslaw Staniek / OpenOffice Polska / Kexi Team http://www.openoffice.com.pl | http://www.kexi-project.org KDElibs/Windows: http://wiki.kde.org/tiki-index.php?page=KDElibs+for+win32
Re: [sqlite] SQL to renumber values in a column?
On 5/9/05, Brown, Dave <[EMAIL PROTECTED]> wrote:
>
> The question I have is: If a row gets deleted, how do I sequentially reorder
> the remaining
> rows? For example:
>
> Start with this:
> a, 1
> b, 2
> c, 3
> d, 4
>
> now delete the row with 'b':
>
> a, 1
> c, 3
> d, 4
>
> I'm trying to figure out how to write an update statement which will reorder
> this back to:
>
> a, 1
> c, 2
> d, 3
I think this trigger will do it on table playlist
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON playlist
BEGIN
UPDATE playlist SET ord = ord-1 WHERE ord > old.ord;
END;
Full example:
CREATE TABLE x (
name VARCHAR(10),
ordINTEGER
);
INSERT INTO x VALUES("a", 1);
INSERT INTO x VALUES("b", 2);
INSERT INTO x VALUES("c", 3);
INSERT INTO x VALUES("d", 4);
SELECT * FROM x;
SELECT datetime('now');
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON x
BEGIN
UPDATE x SET ord = ord-1 WHERE ord > old.ord;
END;
DELETE FROM x WHERE ord = 2;
SELECT * FROM x;
SELECT datetime('now');
DROP TABLE x;
--
Gabor Szabo
http://www.szabgab.com/
