[sqlite] SQLite server using execnet ?
hello, knowing that SQllite is not a client/server database, still want to see if a simple client/server setup would solve my current problems for the moment (because I love the simplicity of SQLlite, and planned to go to a client / server database in the future) Now I wonder if anyone has considered to use Python execnet-module to realize a simple SLQlite client / server application. If I look at the documentation of execnet, (and I realize that I'm a great optimist) it would take between 20 and 50 lines of Python code. thanks very much for your opinions. cheers, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what could be the reason that natural join stops working ?
On 15-09-2010 20:12, Nicolas Williams wrote: > On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote: >> On 15-09-2010 11:36, Benoit Mortgat wrote: >>> Are you sure that after altering your tables adding columns, natural >>> join still only joins on vlid? >>> >> no, very stupid of me !! >> I added a column to each of the tables, with the same name, they >> should have the same value, but due to another programming error, the >> values were different. again, thank all very much !! >> >> That brings another issue to my mind, which I still don't understand: >> "why can we so easily manipulate complex pages of text, by adding / >> deleting / copying / pasting etc, and is manipulating of a few tables >> in a database so difficult?" > You shouldn't select * and you shouldn't natural joins precisely to > avoid these sorts of surprises. Select * is handy for manual queries > from a shell; ditto natural joins. > > Of course, you can always change your schema in such a way as to break > existing statements. But the point here is to write statements such > that they will be stable in the face of a schema that evolves in a > backwards compatible way. When you have to radically change your schema > you know you have to update your code; when you're merely adding columns > that don't contribute to primary keys then you shouldn't have to go > change any statements other than the ones where you specifically need > the new columns. SQL shortcuts like '*' and natural join are just not > compatible with such a schema evolution model. > thanks Nico, I think these are very valuable tips. cheers, Stef > Nico ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what could be the reason that natural join stops working ?
On 15-09-2010 11:36, Benoit Mortgat wrote: > On Tue, Sep 14, 2010 at 23:41, Stef Mientki wrote: > >> until a few moments ago, this worked perfectly >> >> select Header from vraag >> natural join vraaglist >> where Nr = 0 and vraaglist.Name = 'eortc_br23' >> >> but now it returns an empty string (while the string shouldn't be empty). >> >> I test that by doing an explicit join >> >> select Header from vraag >> join vraaglist on vraag.vlid = vraaglist.vlid >> where Nr = 0 and vraaglist.Name = 'eortc_br23' >> > Are you sure that after altering your tables adding columns, natural join > still only joins on vlid? > no, very stupid of me !! I added a column to each of the tables, with the same name, they should have the same value, but due to another programming error, the values were different. again, thank all very much !! That brings another issue to my mind, which I still don't understand: "why can we so easily manipulate complex pages of text, by adding / deleting / copying / pasting etc, and is manipulating of a few tables in a database so difficult?" cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what could be the reason that natural join stops working ?
On 15-09-2010 03:42, Pavel Ivanov wrote: > Did you by any chance introduced some unique constraint or unique > index on a set of columns one of which is primary key? AFAIK, there > was a problem in SQLite until some recent versions in processing of > redundant unique constraints in conjunction with natural joins. thanks, that's a good idea, I indeed, removed a unique constraint of 1 of the columns (and also renamed that column). So I'll try to replicate the transformations and let you know. cheers, Stef > But the best idea would be to post the full definition of those two > tables along with their indexes and constraints. > > Pavel > > On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki wrote: >> On 14-09-2010 23:50, Oliver Peters wrote: >>> your version of sqlite? >> I used several programs >> SQLiteSpy, >> SQLcc, >> Python-programs, >> so I don't know which versions of sqlite I used, >> might that be the problem ? >>> your OS? >> windows XP >>> your backend? >> several, see above >>> What exactly have you done before the Natural Join stopped working? >> converted tables from string to unicode, >> column rename + columns added + changed column constraints ( through copy >> table / drop table / >> rename table ) >> >> thanks, >> Stef >>> Oliver >>> >>> >>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki: >>>> hello, >>>> >>>> after modifying some tables, natural join stopped working ??? >>>> >>>> I've 2 tables, each with a field named "vlid", which is the primary key in >>>> one of the tables >>>> >>>> until a few moments ago, this worked perfectly >>>> >>>> select Header from vraag >>>> natural join vraaglist >>>> where Nr = 0 and vraaglist.Name = 'eortc_br23' >>>> >>>> but now it returns an empty string (while the string shouldn't be empty). >>>> >>>> I test that by doing an explicit join >>>> >>>> select Header from vraag >>>> join vraaglist on vraag.vlid = vraaglist.vlid >>>> where Nr = 0 and vraaglist.Name = 'eortc_br23' >>>> >>>> which gives me the correct string of the field Header. >>>> >>>> exchanging the 2 tables in the above statements, gives exactly the same >>>> results. >>>> >>>> Is my database corrupt ? >>>> >>>> thanks, >>>> Stef Mientki >>>> ___ >>>> 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 >> > ___ > 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] what could be the reason that natural join stops working ?
On 14-09-2010 23:50, Oliver Peters wrote: > your version of sqlite? I used several programs SQLiteSpy, SQLcc, Python-programs, so I don't know which versions of sqlite I used, might that be the problem ? > your OS? windows XP > your backend? several, see above > What exactly have you done before the Natural Join stopped working? converted tables from string to unicode, column rename + columns added + changed column constraints ( through copy table / drop table / rename table ) thanks, Stef > Oliver > > > Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki: >> hello, >> >> after modifying some tables, natural join stopped working ??? >> >> I've 2 tables, each with a field named "vlid", which is the primary key in >> one of the tables >> >> until a few moments ago, this worked perfectly >> >> select Header from vraag >> natural join vraaglist >> where Nr = 0 and vraaglist.Name = 'eortc_br23' >> >> but now it returns an empty string (while the string shouldn't be empty). >> >> I test that by doing an explicit join >> >> select Header from vraag >> join vraaglist on vraag.vlid = vraaglist.vlid >> where Nr = 0 and vraaglist.Name = 'eortc_br23' >> >> which gives me the correct string of the field Header. >> >> exchanging the 2 tables in the above statements, gives exactly the same >> results. >> >> Is my database corrupt ? >> >> thanks, >> Stef Mientki >> ___ >> 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
[sqlite] what could be the reason that natural join stops working ?
hello, after modifying some tables, natural join stopped working ??? I've 2 tables, each with a field named "vlid", which is the primary key in one of the tables until a few moments ago, this worked perfectly select Header from vraag natural join vraaglist where Nr = 0 and vraaglist.Name = 'eortc_br23' but now it returns an empty string (while the string shouldn't be empty). I test that by doing an explicit join select Header from vraag join vraaglist on vraag.vlid = vraaglist.vlid where Nr = 0 and vraaglist.Name = 'eortc_br23' which gives me the correct string of the field Header. exchanging the 2 tables in the above statements, gives exactly the same results. Is my database corrupt ? thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?
thanks Ralf, nice db-browser, and conversion seems to work. cheers, Stef On 13-09-2010 11:03, Ralf Junker wrote: > On 11.09.2010 20:42, Stef Mientki wrote: > >> is there a program that converts sqlite database from windows-1252 to utf-8? > Yes, SQLiteSpy does this: > > * Open database file > * Menu Execute -> Text to Unicode Conversion. > * Select tables and columns to convert. > * Choose database codepage. > * Press OK to start the conversion. > > After conversion, browse the database contents to verify the result. As > SQLiteSpy supports Unicode text display and entry, you can do it right > there. > > If you like what you see, execute "COMMIT" to store the conversion > changes. "ROLLBACK" in case of conversion errors and try again with a > different codepage. > > Download: http://yunqa.de/delphi/doku.php/products/sqlitespy/index > > Ralf > ___ > 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] is there a program that converts sqlite database from windows-1252 to utf-8 ?
thanks Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create a GUI for interacting with a test questions sqlite db
On 05-09-2010 10:47, Arthur Avramiea wrote: > I would like to use sqlite to create a question db for test generation. It > will be organized in a couple of questions cathegories. A random function > will generate a test by selecting a couple of questions from each section. > The sqlite db will be encrypted with http://www.zetetic.net/code/sqlcipher > so that the users of the application without the proper credentials will not > be able to see or modify them. > > I would like to create a graphical interface which will allow me (after > using the proper pass), to add or modify questions, that will allow the > generation of the tests themselves,of the formatted test pages ready to be > printed, and of a page with the answers for correction. I do not want to use > a web interface for that, to make it more portable. It will run on Windows > XP/Vista. > > Is C++ a proper language for creating this interface? If not, which > programming language do you recommend for the task? Which libraries should I > use? Thank you very much. take a look at web2py http://www.web2py.com/ cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is there a way to extend the field information ?
hello, I want to extend the field information of a field in a table, e.g. I want to add a "description" to each field. The only way that came up in my head was the following: - create a "special" table, containing the additional field information: create table _Field_Ex ( Tabel Text Not Null, Field Text Not Null, Description Text ) - for visualizing the total information, the information of the pragma table info Pragma table_info ( ) - and the records from the table _Field_Ex select * from _Field_Ex where Tabel = - as I can't think of a way to combine these result in SQL (e.g. the pragma statement doesn't generate a header, the select does) I combine the results in python. Now I wonder if this combine can also be done in pure SQL. And are there other (better) ways to extend the metadata of fields ? thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feature request - field exclusion list in select list
Stephan Wehner wrote: > On Sun, Sep 27, 2009 at 5:19 PM, Darren Duncan > wrote: > >> Following my previous message to the sqlite-users list, I've done a bit more >> research and decided to escalate my reply to a formal feature request. >> >> I was initially going to file a ticket, but it seems that non-registered >> SQLite >> developers can't do that anymore, and we're supposed to do it on sqlite-users >> where a non-registered developer would then distill list chatter to real >> tickets. So here I go ... >> >> - >> >> I propose that SQLite (and other DBMSs, and the SQL standard) add support for >> explicitly naming a list of result fields that they do *not* want, intended >> for >> use in situations where users do want most of the fields from a source but >> don't >> want a few. It would make for much more robust code if users can explicitly >> encode their intentions, meaning say that they want all fields except field >> foo, >> and have them get exactly that (with the expectation that if non-foo fields >> are >> added or removed to the source, they get or don't get those automatically, >> same >> as if they did "SELECT *"). I have seen evidence from both first hand >> experience and from many other developers, that this feature would be very >> helpful to them. And arguably it shouldn't be too terribly complicated for a >> DBMS to implement. >> > > Its a nice idea, but I feel if you use some other interface to SQLite you can > get the same effect. > > On the one hand, I think if you are writing a script/program to fetch > the results, then your > script can assemble the effective columns in the SELECT list (all > columns minus the ones > you want to leave out). Meaning you can write your own C function/Perl > method or what-have-you to > get that list, given the database connection, table name and the > columns-to-leave-out. > I'm not an expert in databases, but as possibly my question triggered this feature request, I can give you the view from a simple user. Indeed I can get the same functionality from the Python script I'm using. But then I'm beginning to wonder, why I need a database at all ;-) To give another example, a couple of weeks ago I needed to convert one column into a number of columns ( I think it's called pivotting ?), I got a perfect answer from this group, but when I implemented it in the real situation I got an error, not more than 64 tables in a query allowed. So I finaly wrote it myself. Doing a full outer join, is ( in my opinion) more than 50 % redundant information, so I'll probably write a wrapper. These extension works good in a script, but makes the script less readable. > On the other hand I am not sure, but you seem to be interested in > having this feature > so that you get the functionality at the sqlite3 prompt. In which case > I think you can > use a script that has the columns you're interested in, and save > typing that way (type script once, > load many times / type script once, load, change script, load etc.) > > I feel there is great value in not changing software. > Even if the software stays fully backwards compatible ? Now I'm beginning to understand, why (please forgive me to have a judgement as a totally non database expert) such an ugly definition as SQL is still thé database language ;-) cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT ALL BUT syntax (was Re: less typing possible ?)
thanks Darren, Darren Duncan wrote: > Stef Mientki wrote: > >> hello, >> >> I often want to see most of the columns of a table / view / query, but a >> few I don't want to see. >> So I now create a huge list of fields, >> but isn't there a more typo-frindly way, like : >> >> select * - field33 from table >> > > A syntax option introduced in Date and Darwen's Tutorial D language, an > analogy > of which I've included in my Muldis D language, is the ALL BUT modifier. > > > Adapted into SQL with that same spelling, if you wanted all the fields except > for "field33", it might be spelled like: > >SELECT ALL BUT field33 FROM table > > Or, since SQL already tends to use ALL as an implicit alternative to DISTINCT > to > mean "include duplicates", we could use the * instead: > >SELECT * BUT field33 FROM table > > Or if you don't like how that looks, maybe EXCEPT: > >SELECT * EXCEPT field33 FROM table > > This could be generalized so you could have any field list on the left of the > BUT/EXCEPT, so then you have the full flexibility of what you have now; eg: > >SELECT foo.*, bar.field20 EXCEPT foo.field5 FROM foo INNER JOIN bar USING > (id) > > Its nonstandard (unless the SQL standard has a feature like this which I'm > not > aware of), but I think very useful. > > For example, often users want to get all the result fields except for the > artificial fields just used to join the tables. > Exact that's one of the major purposes. I saw you even made a feature request, excellent. For the moment, I'll make a Python wrapper with the same functionality. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] less typing possible ?
hello, I often want to see most of the columns of a table / view / query, but a few I don't want to see. So I now create a huge list of fields, but isn't there a more typo-frindly way, like : select * - field33 from table thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
Pavel Ivanov wrote: >> select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... >> > > I find function ifnull() more readable in such cases. ;-) > > thanks guys, both (of course ;-) works perfectly. I have to study the functions in SQLite some more !! cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
thanks Pavel, and sorry for mixing the wikipedia example with the real situation. create table RT1 ( PID integer, V1 text ); insert into RT1 values ( '684', 'aap' ); insert into RT1 values ( '685', 'other empty' ); create table RT2 ( PID integer, V2 text ); insert into RT2 values ( '684', 'beer' ); insert into RT2 values ( '686', 'other empty' ); select RT1.*, RT2.* from RT1 left join RT2 on RT1.PID = RT2.PID union select RT1.*, RT2.* from RT2 left join RT1 on RT1.PID = RT2.PID where RT1.PID IS NULL; ok I tried in sqlite3 command line ( terrible job ;-) and as you said, both "union" and "union all" works correct (only a different order). And I also can add the "order by" clause without affecting the result (except ordening). So the problem is in the IDE I use. I tried another IDE which worked also correct. Now I still have one question: The result of the above query is: PID V1PID V2 686 from RT2 684 from RT1684 from RT2 685 from RT1 Now I want to combine the columns PID, so the result would look like PID V1 V2 686from RT2 684 from RT1 from RT2 685 from RT1 Is that possible with SQL ? cheers, Stef So let's try again: This following code in the sqlite command line utility: create table RT1 ( PID integer, V1 text ); insert into RT1 values ( '684', 'aap' ); insert into RT1 values ( '685', 'other empty' ); create table RT2 ( PID integer, V2 text ); insert into RT2 values ( '684', 'beer' ); insert into RT2 values ( '686', 'other empty' ); select RT1.*, RT2.* from RT1 left join RT2 on RT1.PID = RT2.PID union select RT1.*, RT2.* from RT2 left join RT1 on RT1.PID = RT2.PID where RT1.PID IS NULL; gives the following output ( I hope I don't make typo's because I don't know how to copy from the command line window) 684|from RT1|684|from RT2 685|from RT1|| ||684|from RT2 which is as (I) expected. If "union all" is replaced by Pavel Ivanov wrote: >> Now the strange thing is that this query returns the correct number of rows, >> but all the columns from the employee-table are empty. >> > > There's no "employee-table" in your query. > > >> If I change "UNION" to "UNION ALL" the join works as expected. >> Is there an explanation for this behavior ? >> > > As we don't see your query we can't explain what's going on there. > > >> Also when I add an order clause, the left columns are all made empty ? >> > > Justing adding ORDER BY changes all "left" (from what?) columns in all > rows to NULL? No way! Show the actual output from sqlite3 command line > utility please. > > >> Is it uberhaupt possible to order the resulting table on the column PID >> and preferable get just 1 PID column ? >> > > Sure, why not? But again no query - no advice on how to correct it to > achieve what you want. > > Pavel > > On Thu, Sep 17, 2009 at 6:39 PM, Stef Mientki wrote: > >> hello, >> >> I'm trying to join 2 tables, >> so I guess I need to perform a full outer join. >> >> On wikipedia, I found this solution for sqlite3: >> http://en.wikipedia.org/wiki/Join_%28SQL%29 >> >> select * >> from RT0 >>left join RT1 on RT1.PID = RT0.PID >> union >>select RT0.*, RT1.* >> from RT1 >>left join RT0 on RT1.PID = RT0.PID >>where RT0.PID IS NULL >> >> Now the strange thing is that this query returns the correct number of rows, >> but all the columns from the employee-table are empty. >> >> If I change "UNION" to "UNION ALL" the join works as expected. >> Is there an explanation for this behavior ? >> >> Also when I add an order clause, the left columns are all made empty ? >> >> Is it uberhaupt possible to order the resulting table on the column PID >> and preferable get just 1 PID column ? >> >> thanks, >> Stef >> ___ >> 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
[sqlite] full outer join questions
hello, I'm trying to join 2 tables, so I guess I need to perform a full outer join. On wikipedia, I found this solution for sqlite3: http://en.wikipedia.org/wiki/Join_%28SQL%29 select * from RT0 left join RT1 on RT1.PID = RT0.PID union select RT0.*, RT1.* from RT1 left join RT0 on RT1.PID = RT0.PID where RT0.PID IS NULL Now the strange thing is that this query returns the correct number of rows, but all the columns from the employee-table are empty. If I change "UNION" to "UNION ALL" the join works as expected. Is there an explanation for this behavior ? Also when I add an order clause, the left columns are all made empty ? Is it uberhaupt possible to order the resulting table on the column PID and preferable get just 1 PID column ? thanks, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite
the answer of Massimo (the web2py author): I apologize and I conclude my statement is not correct. I have experienced this problem once (out of disk space and sqlite database corruption) but I made the mistake of confusing correlation with causality. Except in that occasion I never had any problem with sqlite and I do use it on production. On my web site (web2py.com) I only run sqlite. Massimo Now I'm happy again ;-) Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite
D. Richard Hipp wrote: > On Sep 9, 2009, at 5:35 PM, Stef Mientki wrote: > >> >> The worse thing about sqlite is that if the file grows and you run out >> of memory, the file gets corrupted and you lose data. >> >> > > The web2py author seems to be misinformed. Perhaps you could direct > him to the description of SQLite's Anomaly and OOM testing at > http://www.sqlite.org/testing.html > and if that explanation does not suffice, to the description of why > SQLite is proof against such database corruption in > http://www.sqlite.org/atomiccommit.html > > > done, keep you informed. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite
The worse thing about sqlite is that if the file grows and you run out of memory, the file gets corrupted and you lose data. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get "One to many" in 1 row of a table ?
Shaun Seckman (Firaxis) wrote: > It seems like the more ideal solution would be to create a third table for > ownership that contains a person ID and a vehicle ID. thanks Shaun, but that's what I (i'm not a database expert) call the Bind table. I read some articles about normalization, and I think I got with this database somewhere to the 5-order? cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get "One to many" in 1 row of a table ?
Simon Slavin wrote: > On 9 Sep 2009, at 5:15pm, Stef Mientki wrote: > > >> The situation is like this (simplified): >> >> I've a table with persons: name, address, phone, etc. >> I've another table with vehicles: car_or_bike, brand, type, build- >> year, etc. >> The 2 tables are bounded by bind table: person_ID, vehicle_ID >> > > Well you cannot have one vehicle owned by more than one person. So > you do not need your 'bind' table at all. You just need to add a > column to the vehicle table which contains the person_ID. > > Sorry Simon, I expressed myself not well enough: 1 type of car can (and will) be owned by many persons. But that always the problem with simplifications. The real problem is about psychological / medical tests and questionnaires, where a patient for each test scores on a number of scales. As all scales are of the same type of data, they are all put in one table. >> I know that every person has 1 car and 1 bike >> > > Do you mean exactly one car ? Or up to but no more than one car ? Or > at least one car ? > > so yes exactly one car, or in reality 1 score on every scale. >> I want to show up a table, with 3 columns: >> persons.name vehicle.brand[car] >> vehicle.brand[bike] >> >> >> I can get the result in the following way: >> >> 1. create view Cars: persons join vehicle, where car_or_bike = car >> 2. create view Bikes: persons join vehicle, where car_or_bike = bike >> (and change the relevant colum names) >> 3. join the above view 1 and 2 >> >> The first problem is that the above approach is quiet slow. >> Both starting tables (persons and vehicles are also views), >> and maybe it would be faster to create (temporary) tables ? >> >> The second problem is that in the real situation >> I'm not converting 1 column to 2 columns, >> but it can be as large as 1 column to 10 columns. >> >> btw I'm running SQLite from Python, so maybe I should do some data >> handling outside SQL (in Python) ? >> > > Yes, you are trying to get SQL to do your programming work for you. > Just do two SELECT commands in Python: one to find the car for the > person, and another to find the bike for the person. No need to > create any temporary tables, create any views, do any joins, or keep a > huge table with all the results in. Much smaller and faster. > Yes but that's not the goal, the goal is to create a very easy environment, where even a doctor or nurse can ask any question to the system. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get "One to many" in 1 row of a table ?
Igor Tandetnik wrote: > Stef Mientki wrote: > >> I'm just an amateur struggling with databases, >> so forgive if I ask a question every database user should know. >> >> The situation is like this (simplified): >> >> I've a table with persons: name, address, phone, etc. >> I've another table with vehicles: car_or_bike, brand, type, >> build-year, etc. The 2 tables are bounded by bind table: person_ID, >> vehicle_ID >> >> I know that every person has 1 car and 1 bike >> >> I want to show up a table, with 3 columns: >> persons.name vehicle.brand[car] >> vehicle.brand[bike] >> > > select p.name, v1.brand, v2.brand > from persons p join bind b1 on (p.person_ID = b1.person_ID) > join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and > v1.car_or_bike = 'car') > join bind b2 on (p.person_ID = b2.person_ID) > join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and > v2.car_or_bike = 'bike'); > > Igor Tandetnik > > thanks Igor, That works very good and fast, and still very readable code. I just tried it with 1 to 5 columns on the same database and it's much faster then mine 1 to 2 columns. I tried some code quiet similar to yours, but got errors, now I see an essental part is to use aliases for the tables used more than once. thanks! cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to get "One to many" in 1 row of a table ?
hello, I'm just an amateur struggling with databases, so forgive if I ask a question every database user should know. The situation is like this (simplified): I've a table with persons: name, address, phone, etc. I've another table with vehicles: car_or_bike, brand, type, build-year, etc. The 2 tables are bounded by bind table: person_ID, vehicle_ID I know that every person has 1 car and 1 bike I want to show up a table, with 3 columns: persons.name vehicle.brand[car] vehicle.brand[bike] I can get the result in the following way: 1. create view Cars: persons join vehicle, where car_or_bike = car 2. create view Bikes: persons join vehicle, where car_or_bike = bike (and change the relevant colum names) 3. join the above view 1 and 2 The first problem is that the above approach is quiet slow. Both starting tables (persons and vehicles are also views), and maybe it would be faster to create (temporary) tables ? The second problem is that in the real situation I'm not converting 1 column to 2 columns, but it can be as large as 1 column to 10 columns. btw I'm running SQLite from Python, so maybe I should do some data handling outside SQL (in Python) ? Any suggestions to make it faster and /or more structural will be greatly appreciated. thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there an easy way to concatenate 2 views ?
thanks Woody ( Harold ?), that works perfect. cheers, Stef Harold Wood & Meyuni Gani wrote: > Concantenate or join? > > To concantenate use the > Select * from a > Union all > Select * from b > > Only use * if columns all columns in a are identical to all columns in b, if > not then build a column list and use that indtead of * > > Woody > > -Original Message- > From: Stef Mientki > Sent: Saturday, July 25, 2009 3:19 PM > To: SQLite List > Subject: [sqlite] Is there an easy way to concatenate 2 views ? > > hello, > > I've 2 views ( or maybe tables) with same columns, > is there an easy way to concatenate those 2 views to 1 new view ? > > thanks, > Stef Mientki > ___ > 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
[sqlite] Is there an easy way to concatenate 2 views ?
hello, I've 2 views ( or maybe tables) with same columns, is there an easy way to concatenate those 2 views to 1 new view ? thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use the result of a query, as a variable name in another query ?
hello, to test complex queries, I want to use the command line utility ( or an equivalent that remembers what I type). Now I've a table which contains the names of a set of other tables. In Python I can easily create the SQL string (containing the field name) and commit it to the database. Is there a way realize this in the command line ? So I need to get the result of a query into a variable and then use that variable in a new query. thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] one or many tables, what gives the best performance ?
Hello, Sorry I'm not very familiar with databases, so I probably don't use the right terms. I wonder what's the best approach in my specific case: I have a table "A", in this table "A", each row is connected to about 50 rows of other information. These 50 rows of other information have an identical structure for all rows in table "A". The probability that one of these 50 rows is identical to another row in other sets of 50 rows is approximately zero. So I have 2 choices: 1. create 1 table with all the sets of 50 rows, by adding a reference in each row to the corresponding line in table "A" 2. create a separate table for each set of 50 rows (3. I could flatten the data, so I only will have a huge table "A", but that doesn't sound very efficient to me) Maybe there's one other issue, that might influence the choice, and that's how I use the data: when I extract data from this database, - I search for a row in table "A" - I read all the 50 extra rows of other information thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is slowing down, caused by Sophos
hello, No question, just some information that might be useful to someone. We have a number of small SQLite databases, which are located on a network drive. Users are complaining the last months that these databases becomes slower every week. After investigating the case, we found that Sophos anti-virus software is causing this problem. The standard setting of Sophos is to scan on each file read, so every simple query to the database loads the whole database file to the local system for virus scanning. The data transport when Sophos is on is about 300 times larger than in normal use. After disabling Sophos for the SQLite database files, the system runs like a speer. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete, sometimes doesn't seem to work ...
thanks Simon and Igor Simon wrote: > Difficult to say for sure, but it's possible the Indx of 0 were > inserted with another type (ie, the string "0" and of course, 0 != > "0") > I guess that might have happened. Igor, I can't try it anymore, because I already deleted the records, I'll try to rmemeber that for the next time. cheers, Stef > Simon > > On Sat, Feb 7, 2009 at 4:28 PM, Stef Mientki wrote: > >> hello, >> >> I'm a very happy user of sqlite for about 2 years. >> And as I'm happy for a long time, >> I forgot all tiny details and funny things. >> >> Besides that, I changed from Delphi to Python, >> and now I'm in trouble :-( >> So I might have version problems, >> but that's not the case here, >> as I've the same problem executing the statement below from within my >> Delphi application. >> >> The following statement doesn't seem to work (rows are not removed) >> DELETE FROM [_1_aap] WHERE Indx=0 >> >> But selecting another value than zero, does work (rows are indeed removed) >> DELETE FROM [_1_aap] WHERE Indx=1 >> >> Indx is declared as an integer field. >> In Delphi I'm using SQLite 3.3.8 >> >> One other point, those rows with Indx=0 were inserted by a wrong >> statement (can't remember exactly what) >> >> Anyone has a clue ? >> >> btw. for the moment I solved the problem by recreating the whole >> database again. >> >> thanks, >> Stef Mientki >> >> ___ >> 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] Delete, sometimes doesn't seem to work ...
hello, I'm a very happy user of sqlite for about 2 years. And as I'm happy for a long time, I forgot all tiny details and funny things. Besides that, I changed from Delphi to Python, and now I'm in trouble :-( So I might have version problems, but that's not the case here, as I've the same problem executing the statement below from within my Delphi application. The following statement doesn't seem to work (rows are not removed) DELETE FROM [_1_aap] WHERE Indx=0 But selecting another value than zero, does work (rows are indeed removed) DELETE FROM [_1_aap] WHERE Indx=1 Indx is declared as an integer field. In Delphi I'm using SQLite 3.3.8 One other point, those rows with Indx=0 were inserted by a wrong statement (can't remember exactly what) Anyone has a clue ? btw. for the moment I solved the problem by recreating the whole database again. thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended wrapper for Python?
Gilles Ganault wrote: Hello I browsed through the archives at Gname, but most threads regarding wrappers for Python date back to 2005. There are several wrappers listed in the wiki (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers), so I'd like some feedback about which you would recommend to use SQLite from Python (2.4). I've no experience whatsover, but if I see the list, the top one is the best choice, because it'll be integrated in the standard Python. cheers, Stef Thank you GG. - To unsubscribe, send email to [EMAIL PROTECTED] - Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Found the problem: Re: [sqlite] Is this a valid syntax
I found the problem and have a workaround now. The problem is caused by a selection field, which is a text field, that might be null. This field can either be - null - empty string (don't know if this is different from null) -'0' - 0 (don't know if this is different from the string '0') I'ld consider this as a bug, but I'm not sure who's to blame ;-), SQLite or the Delphi wrapper or my own program I'll study the datatype page again: http://www.sqlite.org/datatype3.html Thank you all for your suggestions, which finally solved my problem, cheers, Stef Mientki Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. Stef, Oh... I though Igor had solved your problem so I didn't give it much thought. My reading of your query would suggest that you should try this: SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') The last two conditions should select the result rows and not affect the join operation. Only the comparisons of the fields from both tables should affect the join. Thanks Dennis, but I tried that already :-( Tomorrow is a new day, with new possibilities. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. hi Dennis, Paul and Stef, Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html Aha, that was the page I was looking for !! even though it is non standard. If you want your SQL code to be portable to other database engines you should use the standard syntax that Paul suggested. I agree, thanks. But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Is this a valid syntax
Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') If I leave the last line out, I get 16 records (which might be ok, I can't check it) Left join would produce records with all NULLs in the PO half where no record in PO matches that in Koppel. Thanks Igor, I think you hit the nail on it's head. Because the tables were quit large, I imported some of tables just partially. I'll check tomorrow. The test of (PO.ALL_answered == '0') then filters out those records where PO.ALL_answered is NULL. Make it SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') Now if I only invert the last line, and suposing the above results where ok (which isn't), I should get 16-7= 9 records back. When PO.ALL_answered is NULL, both (PO.ALL_answered == '0') and (PO.ALL_answered != '0') are false. But this just returns 0 records Apparently, in all rows PO.ALL_answered is either '0' or NULL. That's indeed the case , all '0' ;-) cheers, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is this a valid syntax
I don't understand this behaviour, is this too complex ? or am I doing something wrong ? I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') If I leave the last line out, I get 16 records (which might be ok, I can't check it) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') Now if I only invert the last line, and suposing the above results where ok (which isn't), I should get 16-7= 9 records back. SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered != '0') But this just returns 0 records I tried all kinds of combinations, nested select statements etc, but most of them (may be all, can't remember anymore), returns 16-0-7 records. Sorry, I must be doing something terrible wrong, but I don't see the clue. thanks, Stef Mientki Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] trim available ??
Dennis Cote wrote: Stef Mientki wrote: I don't know which version I'm running, how can I detect that ? Stef, You can call sqlite3_libversion (see http://www.sqlite.org/capi3ref.html#sqlite3_libversion for details). Thanks Dennis, Yes, I see now, I've 3.3.8 ;-) cheers, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] trim available ??
Liam Healy wrote: An option for versions that do not have trim is http://sqlite.org/contrib//download/extension-functions.tgz?get=17 thanks Liam, but I think it's better to recreate the database, with trimmed itesm. cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] trim available ??
The TRIM function was added on 2007-03-17 17:52:42. Thanks, that explains .. cheers, Stef -- D. Richard Hipp <[EMAIL PROTECTED]> Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] trim available ??
Mohd Radzi Ibrahim wrote: Hi, I'm running 3.3.15. No problem at all. Maybe your version does not have this implemented yet... I don't know which version I'm running, how can I detect that ? I use the SQLITE3.DLL dated 8 october 2006, but windows can't find the version info in that file. cheers, Stef Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] trim available ??
hello, As I read here http://www.sqlite.org/lang_expr.html there are trim functions availabel as part of an expression. But when I try to execute the next statement: SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ Afzuigapparaat } ')) I get the following error / ERROR ** Error executing SQL "SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ Afzuigapparaat } ')) " :: no such function: TRIM **/ What am I doing wrong ? thanks, Stef Mientki Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ANN: SQLcc another open source Database manager
Jim Dodgen wrote: Server not found I get "Firefox can't find the server at oase.uci.kun.nl." when I click on: http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html very weird, it's a server directly connected to the European backbone, so please try again. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ANN: SQLcc another open source Database manager
Although there are already a lot of database manager, I couldn't find a free (preferable open source) one, that fitted my needs. So I wrote another one with a lot of help of this list (Thank you all !!), and I'm returning the result now as an open sources alternative. Compared to others it might have some rather distinctive characteristics. - open source (BSD license) written in Delphi - actions can be performed with less than half the clicks, compared to the most user friendly db-manager - plain view of combination of database, SQL-query and SQL-query result - can handle automatically most common image types, including PNG and animated GIF - visual query design (much easier than M$ Access) - all SQL statements caused by clicking, drag&drop can be visualized, resulting in 3 learning levels - logging of all commands per database - user templates - local storage of the settings of each database An overview of the possibilities can be viewed here: http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html A windows install program, with execuatble, help, demo-database and sources can be found here (2MB) http://oase.uci.ru.nl/~mientki/download/SQLcc_v1_0.exe have fun ! -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: to quote or not ?
Thanks very much Igor, that explains it, cheers, Stef Mientki Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: So I would expect that I now can always double quote the selection fields, but unfortunately that doesn't seem to be true. From a graphical design, I get for instance: SELECT "Opnamen.PatNr", "Opnamen.Datum" Opnamen, PatNr and Datum are three separate identifiers, and have to be quoted separatedly (if at all), as in "Opnamen"."PatNr". "Opnamen.PatNr" is a single identifier, distinct from Opnamen.PatNr (which is two identifiers separated by period). To illustrate, consider these valid SQL statements: create table Opnamen (PatNr, "Opnamen.PatNr"); insert into Opnamen values (1, 2); select Opnamen.PatNr, "Opnamen"."PatNr", "Opnamen.PatNr", Opnamen."Opnamen.PatNr" from Opnamen; The last query should return a single row with values (1, 1, 2, 2) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - KvK: 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] to quote or not ?
I've a problem with finding the correct algorithm for quoting. When I've a view in the database, where 2 tables have the same field names "PatNr", doing a pragma to get the fields of the view, I get: PatNr PatNr:1 For quering the data from that view, I don't ask for the view, but generate my own normal select (that is because I want to reorder the fields, enable/disable fields etc) and in that case the PatNr:1 is causing a problem. So I thought I solved that, by double-quoting PatNr:1, and indeed it works. So I would expect that I now can always double quote the selection fields, but unfortunately that doesn't seem to be true. From a graphical design, I get for instance: SELECT "Opnamen.PatNr", "Opnamen.Datum" FROM Patient LEFT JOIN Opnamen ON Opnamen.PatNr = Patient.PatNr which doesn't work, removing the double quotes here does work. So I can think of the following solutions: 1. never double quote selection fields, unless there's a ':' or a space in the name 2. always double quote selection fields, unless there's a '.' in it Which one is the most general, or are there even better ways ? Thanks, -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Meta Information: How to retrieve the column names of a table ?
How can I get all the names of a table without doing a query against the table ? PRAGMA table_info(table-name); And it also works for views, as just found out by trial and error ;-) Is this standard SQL behavior ? -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT
COS wrote: Hi, - Original Message - From: "Stef Mientki" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 13, 2007 3:47 PM Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT You should also consider how your change might effect backwards compatibility. The last time that table_info() was modified, the Ruby-On-Rails community got really upset. I'm rather of a mind to leave table_info() alone. Forgive my ignorance, I'm just a beginner in databases, but what about TABLE_INFO2 ( ) ? (with the explicit restriction that it can be extended in the future when needed, so Ruby users leave it alone ;-) I really can't imagine that extension of a function can cause serious compatibility issues) The alternative is now that I've to - build a table from table_info() - query sqlite_master, to get the SQL string with which the table was generated - parse the SQL string from sqlite_master - and add it to my edit grid form table_info or are there simpler ways ? -- cheers, Stef Mientki http://pic.flappie.nl A small opinion on that matter: what I would really like to see is something like system tables. Today sqlite uses only sqlite_master to keep information about its objects and parsing is required to getter better info of each object (if one needs to). Using other system tables to keep information about each object seems very appropriated since most RDBMS already implement that and it is compatible with SQL ANSI. I think it shouldn't be much of a problem since sqlite engine already parses each object when it opens a database. This would remove the need for PRAGMA commands and would make life much simpler. If it's a standard habbit or at least a posibility to hold in the metadata in standard table, that would be indeed a much better solution. Just a thought. But in my rather limited view a rather good one ;-) cheers, Stef Mientki KvK: 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT
You should also consider how your change might effect backwards compatibility. The last time that table_info() was modified, the Ruby-On-Rails community got really upset. I'm rather of a mind to leave table_info() alone. Forgive my ignorance, I'm just a beginner in databases, but what about TABLE_INFO2 ( ) ? (with the explicit restriction that it can be extended in the future when needed, so Ruby users leave it alone ;-) I really can't imagine that extension of a function can cause serious compatibility issues) The alternative is now that I've to - build a table from table_info() - query sqlite_master, to get the SQL string with which the table was generated - parse the SQL string from sqlite_master - and add it to my edit grid form table_info or are there simpler ways ? -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT
If ask the table sturcture, with pragma table_info() I get of course the basic fields, like: CID,Name,Type, And also SOME special values, like Null, DefaultValue, PrimaryKey But NOT the following special values (and probably a lot more) Unique, AutoIncrement Is this due to the DLL I use, or is it (deliberately) left out of the pragma ? thanks, Stef Mientki KvK: 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] are nested joins possible ?
Stef Mientki wrote: thanks guys, for the fast and different responses, I really appreciate that. ... now I need a couple hours to study all your ideas. After studying all your suggestions, I must conclude that my knowledge of SQL is too low to create a real fool proof solution. For the moment I've it working for simple visual designs, with the following algorithm - find all connected tables - find all links between tables - order the table-boxes, the most left table will get the highest number - search all top tables, i.e.tables that have no left join to another table - put all top tables in the "FROM" clause - walk to the tree of tables and put them in a "JOIN" clause - check if there are joins between top tables, if found, connect them through the "WHERE" clause - add the user "WHERE" to the "WHERE" clause You can see an example here http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html#visual_link_design (btw this page, although messy, gives an idea of the db-manager I'm working on) thanks again for all your support, -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] are nested joins possible ?
thanks guys, for the fast and different responses, I really appreciate that. ... now I need a couple hours to study all your ideas. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] are nested joins possible ?
I thought this would work, SELECT * FROM Patient_Text INNER JOIN ( SELECT * FROM Patient INNER JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr ) ON Patient.PatNr = Patient_Text.PatNr But I get an error on the second use of Patient.PatNr. Is there a way to get such a nested statement working ?? I'm looking for some construct that can used to generated code from a visual design, through recursion of the links, so it must be simple. thanks, -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Script Language
Cesar Rodas wrote: I am planning to develop a interpreted language with LEMON && FLEX. The main goal is to provide a easy way to script commons actions, and to teach algorithm in my University (National University of Asuncion - Paraguay). The language have syntax as PHP and Python I wonder why you're not using one of these ? Looks to me you're reinventing the wheel, while with a lot less effort you could give a better wheel a nice color ;-) cheers, Stef KvK: 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Argh, this must be a very stupid question ...
Yeah. Or, if you are alergic to typing, drag the "mydb.db" icon over top of the "sqlite3.exe" icon and let it go. thanks for the tip, and indeed I'm alergic to typing, I want to run it from a GUI ;-) btw. if I wasn't by accident bounced into your video, I would now still be manipulating ini-files, thanks for SQLite! -- D. Richard Hipp <[EMAIL PROTECTED]> -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Argh, this must be a very stupid question ...
thanks Igor, so it was indeed a stupid question ! -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Argh, this must be a very stupid question ...
In the (windows) commandline version of sqlite: how do I open / connect an existing database ? -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
thanks Dennis, Single quote delimit a string literal. Double quotes delimit a quoted identifier (i.e a column or table name etc that contains special characters such as a space). I just checked Sybase Manual, and there it's exactly as you say. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
thanks Dennis, Marco, And the standard SQL syntax should work from any standard compliant database program. SELECT * FROM "Order Details" I'm just a novice, and although both suggested solutions work, I thought SINGLE QUOTES were thé standard ? -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
Did anyone test this database ? With 3 out of 4 programs I'm not able to read the table "Order Details", does anyone what's the problem here ? thanks, Mikey C wrote: Here is a database http://www.nabble.com/file/6997/Northwind.db Northwind.db It is an exact SQLite implementation of the well known Microsoft Northwind sample that can be found for MS Access and SQL Server. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: A few (probably) simple questions ...
Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: Q2: Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ? SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen AS O WHERE Patient.PatNr = Patient_text.PatNr ANDPatient.PatNr = '1' What exactly do you believe is not supported? Do you get an error with this statement? It looks good to me. I get the following error ":: no such table: Opnamen" (running from Delphi) Well, a logical follow up question is, do you actually have a table named Opnamen in your database? Yes, and "INNER JOIN Patient_text, Opnamen works as expected. Also, I'm somewhat surprised you don't mention Opnamen in the WHERE clause. Do you really want a full cartesian product? I don't remember what a full cartesian product is (too long ago), but you're completely right, without the "Opnamen" in the WHERE clause I get much too much ;-) (I left it out to simplify) Yes, and the above quote is from http://www.sqlite.org/lang_select.html where nothing is said about not supporting "RIGHT", That's why I pointed you to http://www.sqlite.org/omitted.html. Yes, but I'm a "positive" guy, interesting in what's in SQLite, not what's not in it ;-) -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A few (probably) simple questions ...
unfortunately it only supports sqlite 2 :-( I wondered if the source for the database interface of http://OpenOffice.org might help, since they appear to implement exactly that -- even including an sqlite driver. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A few (probably) simple questions ...
thanks Donald, I should be ashamed, as OpenOffice is my standard office package, didn't know OpenOffice supported SQLite (MS-Access didn't ), so I'll take a look. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: A few (probably) simple questions ...
thanks Igor, Q2: Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ? SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen AS O WHERE Patient.PatNr = Patient_text.PatNr ANDPatient.PatNr = '1' What exactly do you believe is not supported? Do you get an error with this statement? It looks good to me. I get the following error ":: no such table: Opnamen" (running from Delphi) Q3: In the SQL help on the web, I read: "/join-op/ ::= *, *|* *[*NATURAL*]* *[*LEFT *|* RIGHT *|* FULL*]* *[*OUTER *|* INNER *|* CROSS*]* JOIN*" But when I try a RIGHT JOIN, I get an error message ??? SQLite doesn't support right outer joins at this time, only left joins. By the way, SQL syntax supported by SQLite is documented here http://www.sqlite.org/lang.html Yes, and the above quote is from http://www.sqlite.org/lang_select.html where nothing is said about not supporting "RIGHT", and now I assume this page lists a lot more that's not supported (yet) ;-) -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] A few (probably) simple questions ...
Q1: What's the difference between ON and WHERE, the 2 statements below return exactly the same ? SELECT Patient_text.*, Opnamen.* FROM Patient as P INNER JOIN Patient_text, Opnamen ON P.PatNr = Patient_text.PatNr WHERE P.PatNr = '1' SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen WHERE Patient.PatNr = Patient_text.PatNr AND Patient.PatNr = '1' (I'm interested in this differences, because I'm trying to generate SQL statements from a visual design, like in MS-Access. If anyone knows good literature about generating SQL statements from graphical designs, I'ld be much obliged.) Q2: Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ? SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen AS O WHERE Patient.PatNr = Patient_text.PatNr ANDPatient.PatNr = '1' Q3: In the SQL help on the web, I read: "/join-op/ ::= *, *|* *[*NATURAL*]* *[*LEFT *|* RIGHT *|* FULL*]* *[*OUTER *|* INNER *|* CROSS*]* JOIN*" But when I try a RIGHT JOIN, I get an error message ??? thanks, -- cheers, Stef Mientki http://pic.flappie.nl
Re: [sqlite] I Need database for some test
lordphoenix wrote: Le Sun, 04 Mar 2007 22:01:30 +0100, Stef Mientki <[EMAIL PROTECTED]> a écrit : lordphoenix wrote: I'm developing an SQlite database administration tool (web site is there : http://litemanager.tuxamily.org) this link doesn't seem to work :-( Sorry for mistake here is the good one : http://litemanager.tuxfamily.org/ thanks "lordphoenix" (and Gerry too, for the lucky guess) looks promising, I hope to see it in English one day, because my French is much much worse than your English !! I think good open source database managers are very welcome. Tested about 7 free and open source database managers (for windows), and none of them was satisfactory. ( So I'm too building an opensource database manager (probably windows only, or wine), maybe it would be nice to exchange some ideas) -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database for some test
lordphoenix wrote: I'm developing an SQlite database administration tool (web site is there : http://litemanager.tuxamily.org) this link doesn't seem to work :-( cheers, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: How to change or add fields to a table ?
On the other hand I fear that I can only change the name of the Table. If the later is true, why isn't possible to change the name of a column (shouldn't be difficult to implement) ? Please feel free to submit a patch. Thank you, but I'm just a Newbie ;-) It was certainly not to criticize SQLite, (It's the first database I found without difficult couplings / logins, so I really love it), but I wondered if there was a special nifty reason to omit the renaming of columns. thanks again, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to change or add fields to a table ?
thanks Igor, for the fast answer. (should have found that myself ;-) But there's a strange thing here I don't understand: somewhere on the ALTER TABLE documentation it says: "to rename or add a new column ..." My English isn't too well, but from the rest of the text I don't understand how I can change a columns name. On the other hand I fear that I can only change the name of the Table. If the later is true, why isn't possible to change the name of a column (shouldn't be difficult to implement) ? cheers, Stef Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: Are there SQL commands to add or change the fields of an existing table, or should the table completely be rebuild ? Some limited changes may be done with ALTER TABLE statement: http://www.sqlite.org/lang_altertable.html For anything else, you would need to create a new table and migrate data. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to change or add fields to a table ?
With the pragma table_info I can get the complete table structure Pragma table_info(Patient) Are there SQL commands to add or change the fields of an existing table, or should the table completely be rebuild ? -- thanks, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Quotes in SQLite ?
Dennis, Igor thanks very much, I think I'm getting the picture. cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Quotes in SQLite ?
Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: This might be a stupid question, but how do you store quotes in textstrings ? Is it really so that you have to replace, both single and double quotes, each time you read or write something ? You need to escape single quotes if you insist on putting string literals directly into queries. No other characters need to be escaped. I've to translate doublequotes too, possibly because I'm using double quotes around textfields ??, Is that wrong ? btw, I'm writing (or maybe I should say "trying to write") another raw database manager (open source), so I must be able to do everything that's legal. See also sqlite3_mprintf. where can I find that (looked in the SQL wiki, but couldn't find it) However, I recommend using parameterized queries and binding your strings to parameters. Then you don't need to worry about escaping at all. Sorry don't know what "parameterized queries" and "binding strings to parametrs" are, can you give me link where I can find some more information. thanks, Stef Mientki Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Quotes in SQLite ?
This might be a stupid question, but how do you store quotes in textstrings ? Is it really so that you have to replace, both single and double quotes, each time you read or write something ? Are there other such kind of special characters ? thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] The best way to delete a column ?
hello, I just read it's not possible to delete a column in an existing table. Now what would be the best way to remove the column indirect (from Delphi code), would it be something like this: start transaction create new table with the same structure, but without the column to delete (how?) copy the data from old table to new table delete the old table rename the new table to the old name vacuum-clean the database end transaction I this the best way, or are there any other ? Sorry I'm just starting SQL, so I just used human SQL. (and therefor a complete code section would be welcome ;-) thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] (newbie) pictures in tables, what's the best way ?
Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stef Mientki wrote: Uptill now the dbase is small so no performance problems yet. Wait until you have performance problems and then solve them :-) It is almost always the case that performance issues don't crop up where you think they will. Yes, that might be the right approach ... ... but if I read somewhere that you can't delete columns ... ... I want to take some pre-cautions. I think I'm getting more insight by now. I'm using a Delphi wrapper SQLiteTable3 from Tim Anderson, which always grabs all data of a query in a table, so also all pictures from blobfields, while I only need the first 2 bytes, so I'll modify that. I can think of several solutions, but as my experience with databases is almost zero, I can't decide One nice feature SQLite has is virtual tables. You don't need to do anything now, but if you have performance issues in the future, you can use a virtual table for your data and behind the scenes do whatever you want including storing the data as files. Thanks Roger, I didn't know that. cheers, Stef Mientki
Re: [sqlite] Re: Re: how to get field names of empty tables ?
Scott Hess wrote: I think only the dot commands are special (.help, etc). Everything else is fair game. Best reference for what you can feed a prepare or exec is http://www.sqlite.org/lang.html . Scott, thanks for this valuable information. Now how beautiful it would be if it was all in 1 pdf file ;-) cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: how to get field names of empty tables ?
Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: is there an SQL statement to get the field-names of empty tables ? PRAGMA table_info(tableName); Igor Tandetnik Thanks Igor, I had seen that command, but I wrote something about that these commands could only be run from the command line. cheers, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to get field names of empty tables ?
is there an SQL statement to get the field-names of empty tables ? thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] (newbie) pictures in tables, what's the best way ?
Cesar Rodas wrote: Save into a blob. thanks but that's not what I meant. I'll try to explain with an example: I want to create this database Persons - Name - Birthday - Picture Now when I want to view this database with a general database browser / manager, I'll first find out what tables there are in this database, Secondly I'm going to ask for an overview of each table by, that will be displayed in a flat table (2-dimensional matrix) SELECT * FROM Persons Now the browser sees the blob field at displays is something like "{blob}" So I assume, that the browser get's all the data, including all the bits and bytes of each image in each record, so a whole lot of not-needed data, because the database browser / manager can con display 1 picture from a selected record. So I think, it would be more efficient to create 2 tables: Persons - Name - Birthday - ID ("this is a picture") Blobs - Name - Picture Now if I ask for the main table "Persons", I just get an ID (some integer), that tells me that there is a picture. The browser / manager can ask for just one record from the table Blobs, to show me the picture of the selected line. I've never seen such a construct in examples, so maybe I see ghosts where there are none. forgive me my ignorance. cheers, Stef Mientki here is a C example http://www.sqlite.org/cvstrac/wiki?p=BlobExample On 23/02/07, Stef Mientki <[EMAIL PROTECTED]> wrote: hello, I've started with SQLite, because it's one of the first dbases with a local filessytem without authenciation. It looks great, compared to ini-files. Now I've a basic problem: I've a table, with 10 simple fields and 1 blob field, containing a (possibly large) image. Uptill now the dbase is small so no performance problems yet. But what if it grows ? I want to look at the table in a table overview, where the blob-field just displays the type of image (identified by the fisrt 2 bytes SOI-code). When I ask for the table through a query, I get all the blob-filed information, while I only need the first 2 bytes, and only the full picture of 1 selected record. I can think of several solutions, but as my experience with databases is almost zero, I can't decide (and maybe I'm overlooking some better solutions) solution 1: add a extra field for each blob field, filled with the first 2 bytes of a blob solution 2: put the blob fields in a separate table, with a link to the main table any suggestions would be appreciated. thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] (newbie) pictures in tables, what's the best way ?
hello, I've started with SQLite, because it's one of the first dbases with a local filessytem without authenciation. It looks great, compared to ini-files. Now I've a basic problem: I've a table, with 10 simple fields and 1 blob field, containing a (possibly large) image. Uptill now the dbase is small so no performance problems yet. But what if it grows ? I want to look at the table in a table overview, where the blob-field just displays the type of image (identified by the fisrt 2 bytes SOI-code). When I ask for the table through a query, I get all the blob-filed information, while I only need the first 2 bytes, and only the full picture of 1 selected record. I can think of several solutions, but as my experience with databases is almost zero, I can't decide (and maybe I'm overlooking some better solutions) solution 1: add a extra field for each blob field, filled with the first 2 bytes of a blob solution 2: put the blob fields in a separate table, with a link to the main table any suggestions would be appreciated. thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -